Ch 10 Working with Multiple Tables NOTES Important Points

Share with others

Ch 10 Working with Multiple Tables NOTES Important Points

Ch 10 Working with Multiple Tables NOTES Important Points

INTRODUCTION

After creating the table in database, we may require to edit or delete the table. Sometimes we have to setup the relations between tables to control data redundancy and inconsistency.

If you set up relations between tables, then adding or updating a record in one table reflect the changes in all the related tables.

Editing and Deleting Tables

We can copy, rename, edit and delete the table of database by right clicking on the table name and using the appropriate option from the pop up menu.

Editing a table involves the task such as

Adding a new field.
Deleting any field in a table
Modify or Alter any of the field properties.

Steps to edit a table are:

Open the Database User Interface window.
Select the Table object in Database Pane.
Right click on the table name and select Edit option from the pop menu.
The design view window of the table will be displayed.
Do the required modifications and save the table.

Ch 10 Working with Multiple Tables NOTES — **Working with Multiple Tables NOTES**

Steps to Delete a table are:

Open the Database User Interface window.
Select the Table object in Database Pane.
Right click on the table name and select Delete option from the pop menu.
A confirmation box to confirm for deletion of the table will be displayed.
Click on Yes button to finally delete the table.

Steps to Rename a table are:

right click on the table name in the Table Area.
Select Rename.. option from the pop up menu.
A cursor will appear.
Type the new name and press the Enter key.

Relationships between Tables

While working with multiple tables, we should check the redundancy and inconsistency of data. This can be done by setting relationship between the tables of a database.

Let us consider an example of a database containing following two tables–Student_Details and Student_Result

In Table 10.1 (Student_Details), Admission No is the primary key. In table 2 (Student_ Result), Roll No is the
primary key and Admission No is the foreign key.

Each record in Table 10.2 has a value of Admission No. that corresponds to a record in Table 10.1 with same
value of Admission No.

NOTE: It is important to note that the data types of the common field in both the tables must be same. If they are not same then LibreOffice Base will display an error message and will not allow to set the relationship between the two tables.

After setting the relationship between two tables, once a student’s record has been entered in the Student_Details table, only then that particular Admission No can be entered in the Student_Result table. Therefore Student_Details is called the master table and Student_Result is called the transaction table.

Types of Relationships

Three types of relationships can be set up between two tables in a relational database. These are:

(i) One-to-One
(ii) One-to-many
(iii) Many-to-Many

One-to-One relationship : In this type of relationship, one specific record of a master table has one and only one corresponding record in the transaction table.

One-to-Many relationship: In this type of relationship, one specific record of the master table has more than one corresponding records in the related transaction table. For example

Many-to-Many relationship: In this type of relationship, there will be multiple records in the master table that correspond to multiple records in the transaction table as well.

For example, a teacher in a school may hold multiple responsibilities such as class teacher, an activity incharge
or examination in-charge. For each responsibility the teacher might be attached with multiple students.

Advantages of Relating Tables in a Database

A relationship can help prevent data redundancy.
It helps prevent missing data by keeping deleted data from getting out of synch. This is called referential integrity.
Creating relationships between tables restricts the user from entering invalid data in the referenced fields.
Any updation in the master table is automatically reflected in the transaction tables.

Creating Relationships between Tables

Click on Tools > Relationships…
The Relationship Design screen will appear.
In the middle of the screen there is Add Tables dialog box (as shown below).

Select the table and click on Add table button.
Close the Add Tables dialog box.
Drag the common field from one table and drop it in another table.
A line connecting both the tables with the common field appears on the screen (as shown below).

NOTE: When One value of a table is associated with multiple values in another table is called One-to-many relationship.

Remove the Relationships

The relationships applied on the tables can be removed also with the help of Delete option. Right Click on the relationship thread and select Delete option.

Referential Integrity

Referential integrity is used to maintain accuracy and consistency of data in a relationship. In Base, data can be linked between two or more tables with the help of primary key and foreign key.

According to the principle of referential integrity if a record say Admission No as 1001 is not present or deleted in the master table, then there should be no record with same Admission no as 1001 in the transaction table.

LibreOffice Base will allow only that corresponding record to be entered in the transaction table which already exists in the master table. LibreOffice Base gives us following four options to choose from to maintain referential integrity in such cases.

No action – This is the default option. This option states that a user should not be allowed to update or delete any record in the master table if any related record exists in the transaction table.

Update cascade – This option allows the user to delete or update the referenced field but along with it all the related records in any of the transaction tables will also be deleted or updated.

Set NULL – This option assigns NULL value to all the related fields if the master record is deleted or updated.

Set default – This option assigns any fixed default value to all the related fields if the master record is deleted or updated.

Referential integrity helps to avoid:

1) Adding records to a related table if there is no associated record available in the primary key table.

2) Changing values in a primary if any dependent records are present in associated table(s).

3) Deleting records from a primary key table if there are any matching related records available in associated table(s).

SUMMARY

1. Relations are set up between the tables to control data redundancy and inconsistency.

2. The most important prerequisite for setting a relationship between the two tables is that there must be a common field(s) between the two tables. 

3. Three types of relationships can be set up between two tables in a relational database. These are One-to-One, One-to-Many and Many-to-Many.

4. In One-to-One type of relationship, one specific record of a master table has one and only one corresponding record in the transaction table.

5. In One-to-Many type of relationship, one specific record of the master table has more than one corresponding records in the related transaction table. 

6. In Many-to-Many type of relationship, there are multiple records in the master table that correspond to multiple records in the transaction table.

7. According to the principle of referential integrity, no unmatched foreign key values should exist in the database.

Important links of Class X (IT – 402)

Disclaimer : I tried to give you the simple Notes of “Ch 10 Working with Multiple Tables NOTES Important Points” , but if you feel that there is/are mistakes in the Notes of “Ch 10 Working with Multiple Tables NOTES Important Points“ given above, you can directly contact me at csiplearninghub@gmail.com. NCERT Book and Study material available on CBSE Website are used to create above article “Ch 10 Working with Multiple Tables NOTES Important Points“. All the screenshots used in above article “Ch 10 Working with Multiple Tables NOTES Important Points” are taken from NCERT book.