# Computer Class 11 GSEB Solutions Chapter 10 Working with Tables

Students frequently turn to Computer Class 11 GSEB Solutions and GSEB Computer Textbook Solutions Class 11 Chapter 10 Working with Tables for practice and self-assessment.

## GSEB Computer Textbook Solutions Class 11 Chapter 10 Working with Tables

Question 1.
Explain how we can perform insertion operation in a table.
Inserting Data in the Table

• First, Open the table to Insert records into the table.
• To open the table, double click on the icon that has the required table name in the Table Pane of Database.
• Alternatively right click on the desired table and select the Open option from the sub menu visible.
• Open the Supplier table we had created in the Design View.
• The Supplier table’s structure will be displayed in the Datasheet View as shown in following figure.

• The screen displays the field names in horizontal line.
• The line consisting of field names is known as Title Line.
• To view the remaining fields, use the scroll bar given at the bottom of the screen.
• Below the Title Line, there is a row consisting of empty boxes. The job of filling all these empty boxes with appropriate data is known as Data Entry.
• There is a small box in front of the first field. This box contains a pointing arrow sign. This box is known as “Record Selector Box” and the sign in it is referred as “Record Selector Icon”.
• Some of the Record Selector Icons and their functions are as mentioned below.
1. The black pointing arrow icon is known as the record pointer that indicates a current record (current row) of the table at any given moment of time. This means that if we start typing, changes in the field value will appear in a current record.
2. The green icon with a flash is the end of the table mark. It is displayed in the next to the
last record in the table. To add new record in Datasheet View, scroll to the last row, then click. The cursor then will be positioned in the field and the icon will be changed to the black pointing arrow.
3. The pencil icon is an Edit Icon. When any field is clicked to edit the data, the black pointing arrow icon gets replaced with this pencil like icon. This icon is displayed, when we have made changes to a record that has not been saved yet. When the cursor is moved to another record, the correction is saved and if ESC key is pressed, the correction gets cancelled while restoring the original contents will be restored.

• Observe the bar at the lower left portion of above figure there is a word ‘Record’, then a numeric value ‘4’ in the box and at the end word ‘of 4’.
• This bar is known as the Navigation Bar.
• This bar contains some navigation buttons to scroll the records vertically.
• It also lets us know the current position of the record pointer.
• The meaning of “Record 4 of 4” is that the we have total 4 records in our table and at present the cursor is on the fourth record.
• Fill up the various fields with the relevant data as discussed in the previous chapter.
• The data entry in Base is controlled and restricted based on the data type and field property that has
been defined for each field.
• After entering data in the last field of the current row, the cursor automatically comes to the first field ‘Scode’ of the next row.
• In case of the Boolean type of filed, a square box will be shown in the field.
• Clicking on the box, will be marked with a tick sign.
• If the box is marked with a tick sign, it means that the value of the field is true, else false.
• After the data entry of some records, the screen will look somewhat like above figure.

Question 2.
What do you mean by relations in Base ?
Creating Relationships Between Tables in Base

• Now that all the tables have been designed, we have to assume that a record of a new customer from Goregaon, Mumbai, Maharashtra is to be entered in the Customer table.
• Till now Maharashtra is no entered in the State table, neither the Goregaon are of Mumbai city has been entered in the City table.
• Insert a record of a customer with pincode value 452001.
• Here the value 452001 is the pincode of Goregaon area, in Mumbai city, in the state of Maharashtra.
• Base will accept the record entered by you, but logically this record should not be allowed in the Customer table because there is no corresponding record in the City table that is related with this entry.
• To enforce such restriction, Relationship feature of Base should be used.
• Establishing relationship between tables will restrict the user from entering garbage data in the referenced fields.
Following is the method how to establish relationship among different tables of the database.

1. Click Relationship … option from Tools menu.
2. Then select Insert â†’ Tables.
3. Alternatively one can click on the Add TablesÂ  button as seen in following figure.
4. Add Tables dialog box with list of tables created in the active database will be displayed.
5. Select the Country table icon and click on Add button. The selected table with its entire field will be displayed in the background in Relationship window.
6. Similarly, select the State table. The Relationship window will look as shown in following figure.

• To create a relationship, just use ‘drag and drop’ operation. Click on the CountryID field (primary key) of the Country table.
• Drag the CountryID field of the Country table and drop it on the CountryID field (foreign key ) of the State table.
• A line connecting both the fields with labels l and n will be displayed as seen in following figure.
• Notice the label text l is displayed on the primary key side and label text n is displayed on the foreign key side.
• This indicates that the CountryiD field is a primary key and will hold unique values, and each unique
value stored in primary key field may be repeated n number of times in foreign key.
• This type of relationship where one value of referred table is associated with multiple values in
referencing table is called One-to-Many relationship.

• Above figure shows the relationship between the tables Country and State. Both the table shown in figure can be moved to any desired location by clicking their title bar and then dragging it.
• Here the CountryID field of the Country table is known as referenced field while the CountryID field of the State table is known as referencing field (see following figure).

Note : To create a relationship between two tables, the data types of referencing field and referenced field must be same. In case data type of both the fields do not match, error as shown in following figure would be displayed while creating relationship.

Question 3.
Explain types of relationships giving suitable example.
Editing Relationships

• We can delete or edit the relationship established between any two tables.
• For that, we just have to open the relationship screen and then click on the desired relationship line drawn between two tables.
• The line will get thickened.
• After this, right click, a popup menu will be visible as shown in above figure.
• If ‘Edit’ is selected from the popup menu than a Relationship dialog box will be shown where we can
edit the relationship attributes as per our requirement.
• Alternatively, if ‘Delete’ option can be selected to remove the relationship.
• Conceptually there can be three major types of relationship between any tables of the database :
(1) One to One
(2) One to Many
(3) Many to Many .
• Types of relationship among the data of two tables are defined based on how many corresponding records can be there in second table corresponding to the first table.

(1) One to One Relationship: This relationship indicates that one specific record of a particular table has one and only one corresponding record in the other table of the database.
For example : How many people can be seated in a theatre chair ? Obviously one. Then how many seats can be occupied by one person? Definitely only one! So the relationship between a Person
and a Chair is One-to-One Relationship. Relationship between a class and class teacher or library to a library card issued to a student is also a One-to-One Relationship.

Now observe relationships created in our sample database. The field OrderID in the Order table is related to the field OrderID in the OrderShipment able. Only one record corresponding to each order, for example, 1 would be found in OrderShipment tables. This indicates that One-to-One relationship exists between the Order and OrderShipment tables.

In Modern Electronic Database case, the OrderShipment details are not to be populated for every order. The OrderShipment details are to be recorded only when customer places order online or expects the delivery of products at home or some other address when the buyer has physically walked into the store and done the purchasing. Thus if the Order table is not decomposed, Null values will be populated in all the OrderShipment fields. Therefore it is better to decompose tables and maintain One-to-One relationship in such cases.

(2) One-to-Many Relationship : The most common type of relationship between the tables is One- to-Many relationship. In this type of relationship one specific record of a particular table may have many corresponding records in the other related table of the database. One customer can purchase multiple products. Similarly, one city belongs to single state, but one state can have multiple cities. A class teacher in a school can be only one allotted to many students of a class. That’s why the relationship between Class Teacher and Student is One-to-Many relationship.

(3) Many-to-Many Relationship : The Many-to-Many Relationship occurs in the case of specific record, which has to be stored more than one in both the tables. In the sample database of ModerElectronicStore, there is information of employees. An employee may hold multiple designations and a single designation may belong to multiple employees. Separate table that stores employee designation has to be maintained in that case. Thus there exists Many-to-Many Relationship between these two entities. Following figure shows a sample Many-to-Many Relationship scenario.

• In Above figure it can be seen that there is no direct relationship between the tables Employee and Designation.
• This is because in database Many-to-Many relationships are split into two One-to-Many relationships by creating a third table.
• In the above example, a new table named Employee Designation has been created, which has One- to-Many relationship with both, Employee table and Designation table.
• Therefore, EmployeeDesignation table acts as a junction table between the Employee and the Designation table.

Question 4.
Explain the concept of foreign key giving suitable example.
Controlling Data Redundancy

• Data Redundancy is a term used for unnecessary duplication or repetition of data.
• Based on the earlier exercises we now have four tables, Supplier, Product, Employee and Customer.
• We can now re-look at the table design from the point of view of data redundancy.
• In the Customer table there is a field called city.
• Modern Electronic Store would have many customers that belong to same city.
• Thus if 100 customers belong to city called ‘Ahmedabad’, then for each of these customers the string ‘Ahmedabad’ would be repeated.
• Data redundancy thus leads to wastage of storage space and is inefficient for several reasons.
• To eliminate redundant data from the database, special care has to be taken to organize the data stored in the tables.
• This can be done by using a technique called data normalization.
• Normalization is a process that suggests decomposition of single table into multiple tables, thus creating a parent-child relationship.
• Thus in case of Customer table in the sample database, a new table can be created to store information about city.
• We can name this table City.
• We are aware that each area within a city has a distinct pincode associated with it.
• A pincode can be used to uniquely identify city and area within city.
• Therefore pincode can be used as a primary key to identify each record in the City table. Following table shows the structure of City table and its sample records.

• We can now remove the City field from the Customer table.
• When the data of a new customer is inserted in the Customer table, one can use the picode field to get the information regarding customer’s city and area.
• Similarly, the City field should be removed from Supplier and Employee tables.
• To maintain complete postal information, State and country fields can also be added to which the city belongs.
• This information can be maintained as shown in following table.

• Now if we are given a pincode, the information pertaining to the location of customer can be searched from City Table.
• But the City table above contains same flaw called data redundancy.
• Same city and state gets repeated multiple times.
• To avoid data redundancy, again City table has to be decomposed into multiple tables.
• In the place of field StateName, a field StateID should be stored and in place of the field CountryName, a field CountryID should be stored.
Following figure shows structure and relation of these tables.

• Here we have assigned CountryID, StateID and Pincode fields as Primary keys of Country, State and City tables respectively.
• As seen in above table now city, state and country tables are related with each other.
• Also as pincode would be one of the fields in customer, employee and Supplier tables, these tables are related with City table.
• The table which contains primary key is known as master table.
• Two tables are related to each other using a common field.
• In the above example, the State and Country table is related using CountryID field.
• The field CountryID acts as a primary key in the Country table.
• The field CountryID field in the state table thus borrows the values from the field CountryID in the Country table.
• The CountryID field in the State table is known as foreign key.
• Foreign key can be defined as field or combination of fields whose values are borrowed from another table.
• When it is combination of more than one field, it is known as Composite Foreign key.
• When a master table has composite primary key, tables related with it will hold composite foreign keys.
Note: Foreign key cannot contain a value that does not exist in its related primary key field.
• In the product table, product category “Mobile” or “Camera” would be stored multiple times.
• Create a separate table named ProductCategory.
• Each product category now could be assigned a unique number.
• This unique number will be used to identify the category of a product.
• For example, Mobile could be assigned code 1.
• When a record for new mobile is inserted in the Product table, a value T is to be stored in the ProductCategory field.
• The data type of the ProductCategory field in the Product that and CategoryCode field in the ProductCategory table should be same.
• Similarly, the field SupplierName in the Product table can be replaced by the field Scode that refers to supplier code.
• Following table lists the primary key and foreigen keys of each table after modification suggested so far.
 Table Name Primary Key Foreign Key Country CountryID State StateID CountryID(referencing Country Table) City CitylD StateID(referencing State Table) Supplier Scode Pincode(referencing City Table) Customer Ccode Pincode(referencing City Table) Employer Ecode Pincode(referencing City Table) ProductCategory Category Code @ Product Pcode CategoryCode(referencing ProductCategory table) Scodefreferencing Supplier Table)

Primary and Foreign Keys of Each Table Discussed in Sample Database

• We can now design other tables to store data transactions like purchasing of a product by the customer.
• We can call it as purchase order placed by customer.
• A customer can either place order online or walk into the store and purchase a product in Modern Electronic Store.
• An order placed by customer indicates that information related to customer, employee and product involved in the transaction has to be maintained.

• We cannot miss out date of order placement.
• Again quantity of each product is equally important.
• A customer may purchase multiple piece of same model.
• Following table shows fields of the Order table.
• Here OrderID is designated as a primary key. Additional field is necessary in this case as no other field or their combination can uniquely determine records.
• All fields of Order table may have duplicates.
• Try to insert a record <1, 23-June-2012, C01, E01, P00000001, 2>
• Now assume that customer has purchased two different products, P00000001 and P00000002.
• Then a new record that needs to be inserted would be <1, 23-June-2012, C01, E01, P00000001, 1>.
• Note that details like order date, employee code and customer code are repeated.
• If a customer purchases 10 products at a time, 10 different records are to be inserted with too much of data duplications.
• OrderID cannot act as primary key in that case.
• A combination of OrderID and Pcode is to be designated as Primary key.
• Decompose the Order table into two tables, Order and OrderDetail.
• Thus fields OrderDate, Ccode and Ecode would be stored only once with OrderID uniquely identifying them in the Order table.
• The OrderDetail table would then contain fields OrderlD, Pcode, Quantity and SalePrice. Values of OrderID will be borrowed from Order Table and thus it will be foreign key field.
• The OderDetail table thus contains the details which are being repeated.
• Following table shows sample records of OrderDetail table.

• In the OrderDetail table, combination of OrderID and Pcode would be unique.
• The product code would not be repeated in a same order.
• Combination of two fields can be designated as a primary key.
• Combination of more than one field when designated, as a primary key, is also known as Composite
Primary Key. Following figure shows structure of both the Order and OrderDetails tables.

Question 5.
What do we mean by Composite primary key? Explain giving suitable example.
Composite Primary Key

• To create composite primary key OrderDetail table,
1. Select row containing OrderlD field.
2. Press CTRL key and Select Pcode row. Both the rows will be simultaneously selected. (See following figure).
3. Right click in selected area. A popup menu with option Primary Key will be displayed.
4. Click on the Primary Key option and key symbol will be displayed in the left of the two selected rows.

• Some more tables like OrderPayment and OrderShipment are also to be designed. Following figure shows the structure of both these tables.

• The OrderShipment table would contain address where the products are to be delivered.
• The ShippingStatus field is used to maintain the status of the product delivery.
• Initially its value would be set to ‘N’.
• Later as the product is received by customer, it has to be set to ‘Y’.
• The OrderPayment table would contain details of payment received from customer.
• A customer may be given option of payment installments and thus in the OrderPayment table OrderID and PaymentID are designated as composite primary key.
• For example, for OrderID “1” PaymentID could be 1, 2, 3, 4 …. and so on.
• For OrderID “2”, PaymentID would start from 1.
• Payment method could be cash, cheque, credit or debit card.
• The PaymentMethod field would contain one of these four options.
• If the payment is made by cheque, then the cheque number and name of issuing bank would also be recorded in the OrderPayment table.
• The user has to now make sure that there are now, Supplier, Employee, Customer, Product, Country, State, City, ProductCategory, Order, OrderDetail, OrderShipment, OrderPayment tables with requires changes in the database.
• They can be created using either the Wizard or Design View.

Question 6.
Explain data redundancy giving suitable example.
Controlling Data Redundancy

• Data Redundancy is a term used for unnecessary duplication or repetition of data.
• Based on the earlier exercises we now have four tables, Supplier, Product, Employee and Customer.
• We can now re-look at the table design from the point of view of data redundancy.
• In the Customer table there is a field called city.
• Modern Electronic Store would have many customers that belong to same city.
• Thus if 100 customers belong to city called ‘Ahmedabad’, then for each of these customers the string ‘Ahmedabad’ would be repeated.
• Data redundancy thus leads to wastage of storage space and is inefficient for several reasons.
• To eliminate redundant data from the database, special care has to be taken to organize the data stored in the tables.
• This can be done by using a technique called data normalization.
• Normalization is a process that suggests decomposition of single table into multiple tables, thus creating a parent-child relationship.
• Thus in case of Customer table in the sample database, a new table can be created to store information about city.
• We can name this table City.
• We are aware that each area within a city has a distinct pincode associated with it.
• A pincode can be used to uniquely identify city and area within city.
• Therefore pincode can be used as a primary key to identify each record in the City table. Following table shows the structure of City table and its sample records.

• We can now remove the City field from the Customer table.
• When the data of a new customer is inserted in the Customer table, one can use the picode field to get the information regarding customer’s city and area.
• Similarly, the City field should be removed from Supplier and Employee tables.
• To maintain complete postal information, State and country fields can also be added to which the city belongs.
• This information can be maintained as shown in following table.

• Now if we are given a pincode, the information pertaining to the location of customer can be searched from City Table.
• But the City table above contains same flaw called data redundancy.
• Same city and state gets repeated multiple times.
• To avoid data redundancy, again City table has to be decomposed into multiple tables.
• In the place of field StateName, a field StateID should be stored and in place of the field CountryName, a field CountryID should be stored.
Following figure shows structure and relation of these tables.

• Here we have assigned CountryID, StateID and Pincode fields as Primary keys of Country, State and City tables respectively.
• As seen in above table now city, state and country tables are related with each other.
• Also as pincode would be one of the fields in customer, employee and Supplier tables, these tables are related with City table.
• The table which contains primary key is known as master table.
• Two tables are related to each other using a common field.
• In the above example, the State and Country table is related using CountryID field.
• The field CountryID acts as a primary key in the Country table.
• The field CountryID field in the state table thus borrows the values from the field CountryID in the Country table.
• The CountryID field in the State table is known as foreign key.
• Foreign key can be defined as field or combination of fields whose values are borrowed from another table.
• When it is combination of more than one field, it is known as Composite Foreign key.
• When a master table has composite primary key, tables related with it will hold composite foreign keys.
Note: Foreign key cannot contain a value that does not exist in its related primary key field.
• In the product table, product category “Mobile” or “Camera” would be stored multiple times.
• Create a separate table named ProductCategory.
• Each product category now could be assigned a unique number.
• This unique number will be used to identify the category of a product.
• For example, Mobile could be assigned code 1.
• When a record for new mobile is inserted in the Product table, a value T is to be stored in the ProductCategory field.
• The data type of the ProductCategory field in the Product that and CategoryCode field in the ProductCategory table should be same.
• Similarly, the field SupplierName in the Product table can be replaced by the field Scode that refers to supplier code.
• Following table lists the primary key and foreigen keys of each table after modification suggested so far.
 Table Name Primary Key Foreign Key Country CountryID State StateID CountryID(referencing Country Table) City CitylD StateID(referencing State Table) Supplier Scode Pincode(referencing City Table) Customer Ccode Pincode(referencing City Table) Employer Ecode Pincode(referencing City Table) ProductCategory Category Code @ Product Pcode CategoryCode(referencing ProductCategory table) Scodefreferencing Supplier Table)

Primary and Foreign Keys of Each Table Discussed in Sample Database

• We can now design other tables to store data transactions like purchasing of a product by the customer.
• We can call it as purchase order placed by customer.
• A customer can either place order online or walk into the store and purchase a product in Modern Electronic Store.
• An order placed by customer indicates that information related to customer, employee and product involved in the transaction has to be maintained.

• We cannot miss out date of order placement.
• Again quantity of each product is equally important.
• A customer may purchase multiple piece of same model.
• Following table shows fields of the Order table.
• Here OrderID is designated as a primary key. Additional field is necessary in this case as no other field or their combination can uniquely determine records.
• All fields of Order table may have duplicates.
• Try to insert a record <1, 23-June-2012, C01, E01, P00000001, 2>
• Now assume that customer has purchased two different products, P00000001 and P00000002.
• Then a new record that needs to be inserted would be <1, 23-June-2012, C01, E01, P00000001, 1>.
• Note that details like order date, employee code and customer code are repeated.
• If a customer purchases 10 products at a time, 10 different records are to be inserted with too much of data duplications.
• OrderID cannot act as primary key in that case.
• A combination of OrderID and Pcode is to be designated as Primary key.
• Decompose the Order table into two tables, Order and OrderDetail.
• Thus fields OrderDate, Ccode and Ecode would be stored only once with OrderID uniquely identifying them in the Order table.
• The OrderDetail table would then contain fields OrderlD, Pcode, Quantity and SalePrice. Values of OrderID will be borrowed from Order Table and thus it will be foreign key field.
• The OderDetail table thus contains the details which are being repeated.
• Following table shows sample records of OrderDetail table.

• In the OrderDetail table, combination of OrderID and Pcode would be unique.
• The product code would not be repeated in a same order.
• Combination of two fields can be designated as a primary key.
• Combination of more than one field when designated, as a primary key, is also known as Composite
Primary Key. Following figure shows structure of both the Order and OrderDetails tables.

Question 7.
State referential integrity rule.
Referential Integrity

• Once the relationship between two tables is created, double-click the line depicting relationship.
Relations dialog box as shown in following figure will be displayed.
• We now know that the Country table and the State table are related to each other.
• A user can enter data of only those country’s details in the State table which have already been entered in the Country table.
• In figure of Records Indicating One-to-Many Relationship first ‘India’ was inserted in the Country table.
• Later three states of India are to be inserted in the State table.
• Record pertaining to India in the Country table can be considered as master or parent record, while records pertaining to India in the State table can be considered as transaction or child records.
• If the user wants to delete a parent record from the Country table, for example if there are ten records in the State table that have CountryID as 1.
• There must not be any entry in the State table without a related record in the Country table.
• This concept is called Referential Integrity.
• Referential Integrity principle can be stated as, No unmatched foreign key values should exist in the database.
• The database designer shall chose and set one of the four options shown in figure depending on the transaction requirement of the company. These options allow us to maintain referential integrity in database while performing an update or delete operation.

• No action : This option states that a user should not be allowed to delete or update any record if its
related record exists in some other table.
• Select No Action option and then try to delete the record containing India’s details.
• Base will confirm whether you want to delete records or not and then will display message as shown in figure.

• Update cascade : This option states that if user is allowed to delete or update referenced values, all
the related records should automatically be deleted or updated.
• Set null : This option states if user deletes or updates the referenced field, all the related records will
hold null value in the related field.
• The user can similarly create the relationship between the other tables of the Modern Electronic Store
database. You can add other tables by clicking on Add Tables icon in tool bar as shown in figure Add
Tables dialog box as shown previously in figure of Relationship dialog box will be displayed.

• Once the relationship is created between all tables of our sample database, relationship window will look similar to the one shown in following figure.
• Click on the Save button on the tool bar.
• In case the user forgets to save, then an alert message will be shown on the screen.
• At times, changes made in relationships are not reflected in database after saving also.
• If such a thing happens then close the database and reopen it.
• Then modifications in relationships will be implemented and visible.

Question 8.
What does required field property signify ?
Field Properties

• To define a field for any table, the following steps have to be performed:
1. Specify the Field Name.
2. Choose a proper Data Type for the field.
3. Write Description of the field. (Optional)
• In the field of information technology, the data is the raw material used to produce finished goods known as Information.
• It is a universal truth ‘We can’t expect getting best without giving better’.
• The quality of the output depends upon the quality of input.
• It is best practice to control the input data, which will then generate the correct information.
• A data entry operator can make a mistake of missing out the Name field and leaving it blank.
• To avoid such mistakes, Base provides a special facility called ‘Field Level Validation’
• It is putting some rules and check points for data to be entered for each field.
• This restricts the values entered in a particular field.
• Data validation is the important aspect of data processing.
• If the data entered in various tables is correct and accurate, then only the database will become a valuable asset to the organization.
• Base has very important and interesting feature called ‘Field Properties’.
• Field Properties determine how the values in the field are store and displayed.
• Each type of field has a particular set of properties.
• For example, in what format will the user enter data (dd-mm-yy) in the OrderDate field, how it should be displayed while printing or viewing the data and what if user enters invalid date like 12/14/12 for a date format dd-mm-yy.
• Field Properties can be decided at the time of designing the structure of the table. Select the field in Table Design Window, Base provides a Field Properties box as shown in following figure with appropriate default values according to the corresponding selected data type. :

• The various Field Properties work as field level validation for the table.
• Field Properties displayed on the screen may vary depending upon the data type.

Question 9.
Explain the importance of Default value in database.
Default Value

• Sometimes, the user wants to store some predefined value automatically to a particular field at the time of entering a new record in the table.
• For example, in the OrderDetail table there is a field named Quantity.
• If ‘1’ has to be stored as a default value for this field, then select the Quantity field and type T in the text box next to the Default value label as shown in following figure.
• Once this property has been set for a field, the specified default value will automatically be displayed when a new record is added to the table.
• A user can change the value if required at the time of data entry.

Question 10.
Explain Autovalue property.
AutoValue

• In above figure, AutoValue property has drop down box (combo box) next to it.
• Click on the drop down box and two values, “Yes” and “No” will be displayed within it.
• If the value of the AutoValue is set to “Yes” for a numeric field, Base can automatically increment the value for each new record.
• This field is particularly used for numeric fields designated as primary key.
• For example, the Country table consists of a field CountrylD as primary key.
• The data type of the field CountrylD field is integer.
• The values expected for this field are 1, 2, 3 … and so on.
• Thus AutoValue can be set to “Yes” in this case.

Question 11.
What is use of Entry required property ?
Entry required

• Out of all the fields in the database, there might be some important fields which should not be left empty.
• This can be achieved by setting the field property Entry required.
• The setting of this property can either be “Yes” or “No” (see following figure).
• This property determines whether the user must enter a value for a particular field to complete record entry.
• This property should be set to “Yes”, so that the user might not skip any entry.
Note : There is no need to set the value of Entry required property to “Yes” for the field declared as primary key.

Question 12.
What is the use of Format property ?
Format

• This property specifies the format for displaying and printing the data that we enter in the field.
• It has no effect on the way the data is stored and it does not check for any invalid entries.
• The Format property uses different settings for different data types.
• Base provides some predefined formats for Number, Date/Time, and Yes/No data types.
• For example, to change format of the field OrderDate in the Order table,
1. Open the Order table in Design View.
2. Select the OrderDate field and click on button at the end of format example label in Field Properties pane. A Field Format dialog box as shown in above figure will open.
3. Select Date option from the list under heading Category.
4. Select Dec 31, 1999 option from the list under the heading Format.
5. Click on OK button.
• Open the table and enter valid date in any format.
• Base will automatically convert all the entries in this field in the format that shows first three characters of month, a space then two digit date and two digit year values separated by a comma.

Question 13.
Choose the most appropriate option from those given below:

1) Which of the following operations cannot be performed on a record in database ?
(A) Insert
(B) Delete
(C) Hide
(D) Update
(C) Hide

2) Which of the following represents maximum levels of sorting provided by Base Wizard ?
(A) 3
(B) 4
(C) 5
(D) 6
(A) 3

3) Which of the following refers to redundancy ?
(A) Deletion of data
(B) Repetition of data
(C) Decomposition of table
(D) Relationships of tables
(B) Repetition of data

4) Given a relation, TeacherSubject (Tcode, Scode, Standard), which of the following fields can be an appropriate foreign key field?
(A) Tcode
(B) Scode
(C) Tcode, Standard
(D) Tcode, Scode
(D) Tcode, Scode

5) Which of the following Statement is incorrect ?
(A) Every table must have a primary key.
(B) Primary key can be combination of fields
(C) Data type of Primary key and Foreign key should be same.
(D) Names of Primary key and Foreign key should be same.
(D) Names of Primary key and Foreign key should be same.

6) Which of the following type of relationship exists between Student and Teacher in a school ?
(A) One to One
(B) One to Many
(C) Many to Many
(D) No relationship exists
(C) Many to Many

7) Which of the following type of relationship exists between Student and Class Teacher in a school ?
(A) One to One
(B) One to Many
(C) Many to Many
(D) No relationship exists
(B) One to Many

8) Which of the following options are possible to implement referential integrity ?
(A) No Action
(B) Set Null
(D) All of these

9) Auto Value property can be set for field with which of the following datatype ?
(A) Text
(B) Image
(C) Integer
(D) Boolean
(C) Integer

10) Which of the following statement is true for Default field property in Base ?
(A) Default value can be numeric value only.
(B) Default value once set can be changed later
(C) Default value cannot be greater than 500.
(D) Default value should be greater than value set in Length.
(B) Default value once set can be changed later

11) Which of the following property is equivalent to NOT NULL ?
(A) Length
(B) Default
(C) Required
(D) Format
(C) Required

### Computer Class 11 GSEB Notes Chapter 10 Working with Tables

Editing Records in the Table

• The data once entered may have to be edited for one of the following reasons:
1. The data entered during data entry is incorrect.
2. After correct data entry, here is a change in the value of the data. For example, one can assume that we have entered the correct address of a particular Supplier. After sometime the supplier shifts his business location. In this case though the address of that supplier was entered correctly, one will have to edit due to the change in location.
• The process of correcting the previously entered data is known as Editing.
• To Edit, one has to open the table and simply place the cursor on the field value that we want to edit and make the desired correction.

Deleting Records from the Table

• It is necessary to keep our database precise and meaningful.
• To do that it becomes important to delete unnecessary or incorrect records from the table.
• Doing this will provide a clear picture and at the same time it will free some disk space.
• To delete any record from the table, open the table and select the unwanted record.
• After selecting the record, it can be deleted in two ways:
1. By pressing DEL key from the keyboard or selecting Delete option from Edit menu.
2. By right clicking the selected record and choosing Delete Record option from the sub menu.
• When a record is deleted, Base displays a dialog box with a warning message as shown in following figure.

• If we click the Yes button from this dialog box, then the selected record will be deleted from the table permanently.
• But if No button is clicked, then the record will not be deleted from the table and it will be redisplayed in the view.

Sorting Data in the Table

• Creating and maintaining database is important because the final aim is to find the information as and when needed.
• Gradually the records in table increase; hence to get the information easily and speedily, it is desirable that the data in the table is arranged in some particular order.
• A Product Table may contain thousands of records related to different products.
• If we want to find the details of a particular product along with its price, and if the product table is arranged in order of field Pcode ie., it becomes very easy to find it.
• Sorting the table helps in arranging the Table in a particular order.
• Table can be sorted in one of the two ways mentioned below:
• (1) Select on the field one wants to sort and then click on either Sort Ascending button or Sort
Descending button from the toolbar.
• (2) Alternatively click on the Sort button and a dialog-box as shown in the following figure will
be displayed.

• Select appropriate field value under the Field name dropdown box and choose required order of sorting under the Order dropdown box.
• In this case, the Product table will be sorted based on ProductCategory in ascending order first and then the data will again be sorted in ascending order of the SellingPrice field.
• For example all Laptops would precede all Mobiles. Within Laptops and Mobiles, the records would be arranged according to their selling price with product with lesser price listed first.

Length

• Whenever Text data type is selected for a particular field, this property automatically takes some predefined value e.g. 50 for Text data type.
• Field size can be specified as per the requirement.
• Base automatically assigns some predefined field size to the various Numeric, Date/Time, Yes/No and Memo data types. That is why in such cases this property will be disabled on the screen.