Database Concepts — NCERT Solutions

a) Database — a collection of logically related records.

b) Meta-data — the file/description of the data stored in the database.

c) Primary key — the attribute that can uniquely identify tuples in a relation.

d) NULL — the special value used when actual data is unknown.

e) Alternate key — an attribute that can uniquely identify tuples but is not chosen as the primary key.

f) DBMS — software used to create, manipulate, and maintain a relational database.

A foreign key is allowed to have NULL value when the referencing attribute is not a part of the primary key of that table. This is because the relation may not always have a matching record in the referenced table.

For example, in the STUDENT table, GUID is a foreign key referring to GUARDIAN(GUID). If a student’s guardian details are not available, then GUID can be NULL. The chapter also states that a foreign key can take NULL value if it is not part of the primary key of the foreign table.

a) Database state and database schema

• Database schema is the design of the database. It describes the structure, table names, columns, constraints, and relationships.
• Database state (or instance) is the snapshot of data present in the database at a particular time.

b) Primary key and foreign key

• Primary key uniquely identifies each tuple within its own relation.
• Foreign key is an attribute in one relation that refers to the primary key of another relation and is used to create a relationship between tables.

c) Degree and cardinality of a relation

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

A DBMS avoids redundancy by storing related data in related tables and using common fields to connect them.

In the school example, instead of repeating the student name and guardian details in the attendance file, the database keeps:

• STUDENT table for student details
• GUARDIAN table for guardian details
• ATTENDANCE table for attendance records

The RollNumber links ATTENDANCE with STUDENT, and GUID links STUDENT with GUARDIAN. Also, guardian details are stored only once in the GUARDIAN table, so the same guardian information is not repeated for siblings. This reduces redundancy and helps avoid inconsistency.

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

• Difficulty in access
• Data redundancy
• Data inconsistency
• Data isolation
• Data dependence
• Controlled data sharing

A relational DBMS helps manage related data efficiently, supports linking between tables, and allows controlled access through keys and queries.

a) No.

Because Preference is the attribute where one student gives one sports choice. A value NULL would mean the preference is unknown or not given, which goes against the rule that each student must give only one preference.

b) Property 3 of relational model is violated, because an attribute must not have many values in one tuple. Here roll no. 17 appears twice with two different preferences.

Yes, we can use a constraint to prevent such violation, for example by making Roll_no unique and ensuring only one tuple per student is allowed.

c) No.

The tuple with NULL roll number should not be accepted if Roll_no is meant to identify a student, because a relation should have distinct tuples and the identifying attribute should not be NULL.

Yes. NULL may be assigned because the student may not be interested in sports, so the preference is unknown/not applicable. The chapter states that NULL is used to represent values that are unknown or non-applicable to certain attributes.

The property of a relation violated is that each tuple must be distinct and each attribute should have a single atomic value. Here, roll number 17 has given two preferences, so one student appears in more than one tuple, which breaks the idea that one tuple should represent one distinct record.

Yes, we can use a constraint/key to check this. Since the school rule says each student should give only one preference, we can make Roll_no a primary key or at least place a UNIQUE constraint on it so that the same roll number cannot appear twice in the relation.

Yes, it is possible. A tuple can exist even if no student chose that sport, because the relation can contain data that are not referenced by another relation. The chapter does not forbid such a tuple. Since the relation is about sports preferences, Kabaddi with a roll number value can still be stored as a record, provided it follows the relation’s rules.

Yes, the states of both relations are equivalent.

• They contain the same set of tuples:
• (9, Cricket)
• (13, Football)
• (17, Badminton)
• (21, Hockey)
• (24, Cricket)
• The order of rows is different, but in a relation, sequence of tuples is immaterial.
• The order of columns is also different, but sequence of attributes is also immaterial.

So both tables represent the same relation state.

To store each item name with its price, use a relation such as ITEM(ItemID, ItemName, Price).

A suitable choice of attributes is:

• ItemID: integer, unique identifier
• ItemName: string/text
• Price: numeric/decimal

Since each item and its price should be stored only once, the ItemID should be defined as the primary key or at least UNIQUE, so duplicate item records are not inserted.

The quantity should be stored in a new relation, not in the item relation, because it belongs to a particular purchase/bill, not to the item itself.

A suitable design is:

• ITEM(ItemCode, ItemName, Price, Calories) or ITEM(ItemCode, ItemName, Price)
• BILLDETAILS(BillNo, ItemCode, Quantity)

To satisfy the given restrictions:

1. The same bill cannot be generated for different orders

• Use BillNo as the primary key of the bill relation, or use a composite key if needed with order identification.

2. Bill can be generated only for available items in the canteen

• Make ItemCode in BILLDETAILS a foreign key that refers to the primary key of the ITEM relation.

So, quantity should be in a separate relation and linked through a foreign key to the item table.

The attribute calories should be stored in the ITEM relation, because calories are a property of the item itself, not of the individual purchase. So the item master table should include it along with item name and price.

a) The attributes of EMPLOYEE that can be used as candidate keys are:

• AadharNumber
• EmployeeID

Both can uniquely identify an employee.

b) To retrieve dependent details of a particular employee, we need the tables:

• EMPLOYEE
• DEPENDENT

The required key is EmployeeID, which is the key used to relate the two tables. In DEPENDENT, EmployeeID acts as a foreign key referencing EMPLOYEE.

c) Degree means number of attributes.

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

The candidate keys are the attributes that can uniquely identify an employee. In the given relation EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID), the attributes that can be used as candidate keys are AadharNumber and EmployeeID, because both can uniquely identify tuples.

To retrieve details of a dependent of a particular employee, we need the tables:

• EMPLOYEE
• DEPENDENT

The required key is EmployeeID, because it is the common link between the two tables and is the primary key in EMPLOYEE and a foreign key in DEPENDENT.

The degree of a relation is the number of attributes.

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