Database Concepts — NCERT Solutions

A database is a collection of logically related records.

A foreign key may be NULL when the referencing record has no related record in the master table, or when the relationship is not applicable.

For example, in the GUARDIAN table, if a guardian does not share a phone number, then GPhone can be NULL. Similarly, a foreign key in another table can be NULL if that row does not yet refer to any record in the related table.

Database state and database schema

• Database schema: the design or skeleton of the database. It shows table names, columns, data types, constraints, and relationships.
• Database state: the current data stored in the database at a particular time; also called an instance.

Primary key and foreign key

• Primary key: the attribute or set of attributes that uniquely identifies each tuple in its own relation.
• Foreign key: an attribute whose value is taken from the primary key of another relation to create a link between tables.

Degree and cardinality of a relation

• Degree: number of attributes/columns in a relation.
• Cardinality: number of tuples/rows in a relation.

Compared to a file system, a DBMS reduces redundancy by storing related data only once and linking tables through common fields.

For example, in the school database:

• SName is not stored in the ATTENDANCE table because it already exists in STUDENT.
• Guardian details are separated into a GUARDIAN table so the same guardian data is not repeated for siblings.

Thus, a DBMS avoids repeated storage of the same data and reduces data redundancy.

The limitations of a file system that can be overcome by a relational DBMS are:

1. Difficulty in access
2. Data redundancy
3. Data inconsistency
4. Data isolation
5. Data dependence
6. Controlled data sharing

A DBMS also makes it easier to store, update, delete, and retrieve data efficiently.

The table shown has issues because:

• Roll_no 17 appears twice with different preferences.
• Roll_no NULL appears with Kabaddi.
• Roll_no 24 has preference as NULL.

These cases are used in the following sub-questions.

Yes. If the student is not interested in sports, the preference field can be NULL because it represents an unknown or non-applicable value.

The property violated is Property 2 of relations: each tuple in a relation must be distinct.

Since roll no 17 has two different preferences, it means the student has more than one value for the same field, which is not allowed if the rule is only one preference per student.

To prevent this, the Roll_no field can be made UNIQUE and NOT NULL. If one row per student is required, then Roll_no should act as the key. However, the text mainly suggests using constraints like UNIQUE and NOT NULL to ensure each roll number appears only once.

Yes. A tuple like (NULL, Kabaddi) can exist if the roll number is unknown or not given, because NULL is allowed for a value that is unknown or non-applicable. But if Roll_no is meant to be the primary key, then it cannot be NULL.

Yes, the two relations are equivalent in state because they contain the same set of tuples, only the order of rows and order of columns is different.

In relational model, the sequence of attributes is immaterial and the sequence of tuples is immaterial. So both tables represent the same relation.

A suitable design is:

• ITEM relation to store item details such as item name and price.
• BILL / PURCHASE relation to store purchased items and quantity.

The database should ensure that:

• each item is stored only once,
• bills are generated only for available items,
• and the same bill is not generated for different orders.

Use an ITEM relation.

A suitable structure can be:

• ItemID: integer
• ItemName: string
• Price: decimal/integer

Example: ITEM(ItemID, ItemName, Price)

To store each item only once, use UNIQUE on ItemName or make ItemID the primary key. Since each item should be stored once, the important restriction is UNIQUE for item identity, along with a PRIMARY KEY.

This information should be in a new relation, because bill details are separate from item details.

A suitable relation can be:

• BILL or PURCHASE
• Attributes: BillNo, ItemID, Quantity

Example structure: PURCHASE(BillNo, ItemID, Quantity)

• Primary key: BillNo if each bill is unique, or a composite key like (BillNo, ItemID) if one bill can contain multiple items.
• Foreign key: ItemID referencing the ITEM relation.

This satisfies:

1. The same bill cannot be generated for different orders → use BillNo as unique/primary key.
2. Bill can be generated only for available items → ItemID must match an existing item in ITEM.

The attribute calories should be stored in the ITEM relation, because calories are a property of the item itself, not of the purchase transaction.

The database is:

• EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)
• DEPENDENT(EmployeeID, DependentName, Relationship)

It is meant to store employee details and their dependent details.

The attributes of EMPLOYEE that can be used as candidate keys are AadharNumber and EmployeeID, because both are unique identifiers.

To retrieve dependent details of a particular employee, use the tables EMPLOYEE and DEPENDENT.

The required key is EmployeeID, which acts as a primary key in EMPLOYEE and a foreign key in DEPENDENT.

• EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID) has 5 attributes, so its degree = 5.
• DEPENDENT(EmployeeID, DependentName, Relationship) has 3 attributes, so its degree = 3.