THE GOAL | Part B | Chapter 10 Notes

📖 CHAPTER 10 • SOURCE-BASED EXAM NOTES

Working with Multiple Tables

Complete exam-oriented revision notes for CBSE Class 10 Information Technology (402), based on the Domestic Data Entry Operator — Class X textbook.

✍️ Read • Revise • Recall

Source check: Chapter 10 starts on printed page 173. It covers editing and deleting tables, relationships between tables, types of relationships, advantages of relating tables, creating relationships and referential integrity.

01. Introduction

After creating tables in a database, it may be necessary to edit, rename or delete them. When multiple tables are used, relationships can be established between them to control data redundancy and inconsistency.

A relationship between tables helps maintain the database by ensuring that records are not unnecessarily duplicated and that inconsistent values for the same field are controlled.

📌 Exam Point

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

— ✦ — ✦ — ✦ —

02. Editing, Deleting & Renaming Tables

Editing a Table

Editing a table may involve adding a new field, removing a field or changing any of the field properties.

Steps to Edit a Table

Open the Database User Interface window.
Select the Table object in the Database Pane.
The list of tables will be displayed in the Table Area.
Right-click on the required table name.
Select the Edit option from the pop-up menu.
The table will open in Design View.
Make the required modifications and save the table.

📌 Important

The changes made while editing the table do not affect the previous records already entered in the table.

Deleting a Table

Right-click on the table to be deleted.
Select the Delete option from the pop-up menu.
A confirmation box will appear.
Click Yes to finally delete the table.

Renaming a Table

Right-click on the table name in the Table Area.
Select the Rename... option.
A cursor will appear.
Type the new name.
Press the Enter key.

Operation	Purpose
Edit	Add/remove fields or modify field properties.
Delete	Remove the complete table from the database.
Rename	Change the name of a table.

✏️ EXAM POINT

A table is edited in Design View.

— ✦ — ✦ — ✦ —

03. Relationships Between Tables

Why are Relationships Required?

While working with multiple tables, we need to control redundancy and inconsistency of data. A record for a particular entity should not be unnecessarily repeated, and different values should not appear for the same entity where they should remain consistent.

Most Important Prerequisite

The most important prerequisite for setting a relationship between two tables is that there must be a common field or fields between them.

⭐ MUST REMEMBER

Relationship between tables → Common field(s)

Same Data Type

The data types of the common field in both tables must be the same. If the data types are different, LibreOffice Base displays an error message and does not allow the relationship to be set.

🧠 Quick Recall

Common Field + Same Data Type = Prerequisite for Relationship

— ✦ — ✦ — ✦ —

04. Primary Key, Foreign Key & Table Roles

Example — Student Database

Consider two tables:

Student_Details	Student_Result
Admission No Name Father's Name Class DOB	Roll No Admission No Class Aggregate_Marks

Student_Details Table

In Student_Details, Admission No is the Primary Key. It uniquely identifies a student record.

Student_Result Table

In Student_Result, Roll No is the Primary Key and Admission No is the Foreign Key.

Table	Key	Role
Student_Details	Admission No	Primary Key
Student_Result	Roll No	Primary Key
Student_Result	Admission No	Foreign Key

Master Record & Transaction Record

Once a relationship is established, the record in the master table is called the master record. The corresponding record in the related table is called the transaction record.

Master Table

The table containing the main/master record is called the Master Table. Example: Student_Details

Transaction Table

The related table containing the corresponding transaction record is called the Transaction Table. Example: Student_Result

📌 Important Rule

The record in the master table must exist before the corresponding Admission No can be entered in the transaction table.

— ✦ — ✦ — ✦ —

05. Types of Relationships

There are primarily three types of relationships between two tables in a relational database.

One-to-One

→

One-to-Many

→

Many-to-Many

Relationship	Meaning
One-to-One	One master record corresponds to one transaction record.
One-to-Many	One master record corresponds to more than one transaction record.
Many-to-Many	Multiple master records correspond to multiple transaction records.

— ✦ — ✦ — ✦ —

06. One-to-One Relationship

Meaning

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

Examples

A student's Admission Number and the corresponding student record.
A person and his/her ticket number for entry into a stadium.

1 Master Record

→

1 Transaction Record

⭐ EXAM DEFINITION

One-to-One = One specific master record has one and only one corresponding transaction record.

— ✦ — ✦ — ✦ —

07. One-to-Many Relationship

Meaning

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

Examples

One teacher can teach multiple students.
One teacher can teach multiple classes.
One person can sell multiple products.

1 Master Record

→

Multiple Transaction Records

📌 Most Common Type

One-to-Many is one of the most common types of relationships between tables in a database.

⭐ EXAM DEFINITION

One-to-Many = One master record has more than one corresponding transaction record.

— ✦ — ✦ — ✦ —

08. Many-to-Many Relationship

Meaning

In a Many-to-Many relationship, multiple records in the master table correspond to multiple records in the transaction table.

Examples

A teacher may have multiple responsibilities and each responsibility may be associated with multiple students.
A shopkeeper may sell multiple products to multiple customers.
A student can study multiple subjects and a subject can be studied by multiple students.

Multiple Master Records

↔

Multiple Transaction Records

Relationship	Example
One-to-One	Person ↔ Ticket
One-to-Many	Teacher → Students
Many-to-Many	Customer ↔ Product

🧠 Memory Trick

1 → 1 = One-to-One
1 → Many = One-to-Many
Many ↔ Many = Many-to-Many

— ✦ — ✦ — ✦ —

09. Advantages of Relating Tables

Creating relationships between tables provides several important advantages.

A relationship helps prevent data redundancy.
It helps prevent missing data from getting out of sync. This is related to referential integrity.
Relationships restrict the user from entering invalid data in referenced fields.
Updation in the master table can automatically be reflected in the transaction tables according to the selected referential-integrity option.

📌 Four Key Advantages

Less Redundancy → Better Consistency → Valid Data → Controlled Updates

— ✦ — ✦ — ✦ —

10. Creating Relationships in LibreOffice Base

Example — Sports Day Database

Suppose the Sports Day database contains two tables:

Events
EventCategory

The common field between the two tables is CategoryID.

Table	Primary / Foreign Key
Events	EventID = Primary Key CategoryID = Foreign Key
EventCategory	CategoryID = Primary Key

Steps to Create Relationship

From the main menu of LibreOffice Base, click Tools → Relationships...
The Relationship Design screen appears.
The Add Tables dialog box is displayed.
Select the Events table and click Add.
Similarly, add the EventCategory table.
Click Close to close the Add Tables dialog box.
Both tables appear in the Relationship Area with their fields.
Use the common field CategoryID to establish the relationship.
Drag the common field from one table and drop it on the corresponding common field of the other table.
A connecting line appears between the two tables.

Tools

→

Relationships

→

Add Tables

→

Common Field

→

Drag & Drop

⭐ VERY IMPORTANT FOR EXAM

Relationship between two tables in the Relationship Design screen is created using the Drag and Drop operation.

— ✦ — ✦ — ✦ —

11. Referenced Field & Referencing Field

Referenced Field

The field on the Primary Key side is the Referenced Field. It contains unique values. In the Sports Day example, EventCategory.CategoryID is the Primary Key and acts as the referenced field.

Referencing Field

The field on the Foreign Key side is the Referencing Field. Its values may be repeated because multiple records can refer to the same master record. In the Sports Day example, Events.CategoryID is the Foreign Key and acts as the referencing field.

Field	Table	Role
CategoryID	EventCategory	Primary Key → Referenced Field
CategoryID	Events	Foreign Key → Referencing Field

🧠 Easy Recall

Referenced = Primary Key = Unique
Referencing = Foreign Key = May Repeat

📌 Relationship Type

In this Sports Day example, one category can have multiple events. Therefore the relationship is One-to-Many. The EventCategory table is the master table and the Events table is the transaction table.

— ✦ — ✦ — ✦ —

12. Referential Integrity

Definition

According to the principle of Referential Integrity, no unmatched foreign key values should exist in the database.

Simple Example

Suppose Admission No 1001 exists in the Student_Details master table. Then the same Admission No can be entered in the Student_Result transaction table only when the corresponding master record exists.

If the master record is deleted, the related transaction record must be handled according to the referential-integrity option selected.

⭐ MUST REMEMBER

Referential Integrity = No unmatched Foreign Key values.

Who Maintains Referential Integrity?

Once the relationship has been established, DBMS manages the integrity of the data. LibreOffice Base allows only corresponding records in the transaction table when the required master record exists.

✏️ EXAM POINT

Referential Integrity is maintained by the DBMS, not manually by the user.

— ✦ — ✦ — ✦ —

13. Referential Integrity Options

LibreOffice Base provides four options to maintain referential integrity.

Option	Meaning
No Action	Default option. A user is not allowed to update or delete a master record if a related record exists in the transaction table.
Update Cascade	Allows the referenced field to be updated or deleted along with the related records in the transaction tables being updated or deleted accordingly.
Set NULL	Assigns NULL value to all related fields if the master record is deleted or updated.
Set Default	Assigns a fixed default value to all related fields if the master record is deleted or updated.

No Action

This is the default option. If a related record exists in the transaction table, the master record cannot be updated or deleted.

Update Cascade

Changes made to the referenced field are also applied to the related records in the transaction table.

Set NULL

Related fields receive a NULL value when the master record is deleted or updated.

Set Default

Related fields receive a specified default value when the master record is deleted or updated.

🧠 Remember All Four

No Action → Stop the change
Update Cascade → Apply the change to related records
Set NULL → Put NULL
Set Default → Put fixed default value

— ✦ — ✦ — ✦ —

14. Setting Relationship Properties

Steps

Double-click the relationship line joining the two tables.
A Relations dialog box will open.
By default, No Action is selected.
Select the desired referential-integrity option.
Click OK.

Double-click Relation Line

→

Relations Dialog Box

→

Select Option

→

⭐ EXAM FOCUS

The Relations dialog box is used to set the referential-integrity options for a relationship.

— ✦ — ✦ — ✦ —

15. Relationship Comparison

Type	Master Side	Transaction Side	Example
One-to-One	One record	One corresponding record	Person and Ticket
One-to-Many	One record	Multiple records	Teacher and Students
Many-to-Many	Multiple records	Multiple records	Customer and Product

⚡ Don't Confuse

One-to-Many: One master → many transaction records.
Many-to-Many: Many master records ↔ many transaction records.

— ✦ — ✦ — ✦ —

16. Complete Relationship Workflow

The complete process of creating a relationship between tables can be remembered using the following sequence:

Create Tables

→

Common Field

→

Check Same Data Type

→

Set Primary/Foreign Key

→

Tools → Relationships

→

Add Tables

→

Drag & Drop

→

Set Integrity

🧠 Last-Minute Flow

Common Field → Same Data Type → Relationship → Referential Integrity

— ✦ — ✦ — ✦ —

17. Practical Exercise — Important

Maintenance Dues Database

A society database can contain:

Residents Details	Maintenance Dues
Flat No Owner Name Contact No Flat Category	Receipt_ID Flat No Date Amount

The common field is Flat No. The relationship can be established using the common field, with the residents information acting as the master information and dues records as related transaction information.

Hostel Database

Student_Details	Room Details
Student_Id Name DOB Course Father's Name Contact No_student Contact No_guardian Room No	Room No Floor Category Student Id DOO Monthly Rent

✏️ PRACTICAL THINKING

While creating a relationship, identify:
1. Common field
2. Primary key
3. Foreign key
4. Master table
5. Transaction table
6. Type of relationship

— ✦ — ✦ — ✦ —

18. Board Exam Focus

⭐ DEFINITIONS TO LEARN

Relationship between tables
Master Table
Transaction Table
One-to-One Relationship
One-to-Many Relationship
Many-to-Many Relationship
Referential Integrity
Referenced Field
Referencing Field

⭐ STEPS TO LEARN

Steps to edit a table.
Steps to delete a table.
Steps to rename a table.
Steps to create a relationship between tables.
Steps to set referential-integrity options.

⭐ DIFFERENCES TO LEARN

One-to-One vs One-to-Many
One-to-Many vs Many-to-Many
Referenced Field vs Referencing Field
Master Table vs Transaction Table

— ✦ — ✦ — ✦ —

19. Chapter Summary

📚 MUST REMEMBER

Tables can be edited, deleted and renamed.
Editing a table can include adding/removing fields or changing field properties.
Editing a table does not affect previously entered records.
A relationship helps control data redundancy and inconsistency.
A relationship requires a common field between tables.
The data types of the common fields must be the same.
The three types of relationships are One-to-One, One-to-Many and Many-to-Many.
One-to-One means one master record corresponds to one transaction record.
One-to-Many means one master record corresponds to multiple transaction records.
Many-to-Many means multiple master records correspond to multiple transaction records.
The master table contains the master record.
The related table is called the transaction table.
Primary Key values are unique.
Foreign Key values may repeat in a one-to-many relationship.
Relationship in LibreOffice Base is created using Tools → Relationships.
Tables are added using the Add Tables dialog box.
The relationship is created using Drag and Drop.
Referential Integrity means no unmatched foreign-key values should exist.
Referential integrity is managed by the DBMS.
Four options are No Action, Update Cascade, Set NULL and Set Default.

— ✦ — ✦ — ✦ —

⚡ CHAPTER 10 — RAPID REVISION

Edit Table: Right-click table → Edit → Design View.
Delete Table: Right-click table → Delete → Yes.
Rename Table: Right-click table → Rename → type new name → Enter.
Relationship: Used to control data redundancy and inconsistency.
Prerequisite: Common field(s) between the tables.
Common Field Data Type: Must be the same in both tables.
Master Table: Contains the master record.
Transaction Table: Contains corresponding transaction records.
One-to-One: 1 → 1
One-to-Many: 1 → Many
Many-to-Many: Many ↔ Many
Primary Key: Uniquely identifies records.
Foreign Key: Connects a transaction table with the master table.
Referenced Field: Primary-key side; unique values.
Referencing Field: Foreign-key side; values may repeat.
Relationship Menu: Tools → Relationships.
Add Tables: Add Tables dialog box.
Relationship Operation: Drag and Drop.
Referential Integrity: No unmatched foreign-key values.
Maintained By: DBMS.
No Action: Default option.
Update Cascade: Related records are updated/deleted accordingly.
Set NULL: Related fields receive NULL.
Set Default: Related fields receive a fixed default value.