Database Concepts — NCERT Solutions

Given:

Section 1 Table (ordered by Roll_no):

Section 2 Table (ordered by Sports, columns in different order):

Answer: Yes, the states of both relations are equivalent.

Justification:

In the Relational Data Model, the following two properties hold:

1. Order of rows (tuples) does not matter: A relation is defined as a set of tuples. Sets are unordered, so the order in which rows appear does not affect the state of the relation. Both tables contain the same 5 tuples — just arranged differently (Section 1 by Roll_no, Section 2 by Sports name).

2. Order of columns (attributes) does not matter: A relation's attributes can be in any order. The actual data values associated with each attribute name remain the same regardless of column order. Section 1 has (Roll_no, Sports) order while Section 2 has (Sports, Roll_no) order, but the data is identical.

Verification: Both tables contain exactly the same 5 data pairs:

• (9, Cricket), (13, Football), (17, Badminton), (21, Hockey), (24, Cricket)

Since both tables have the same set of tuples (same data, same attribute values), their database states are equivalent.

Given: A school canteen database needs to be designed to track items and generate bills.

a) Relation to store item name and price:

Relation Name: ITEM

Schema:
$$\text{ITEM(\underline{ItemID}, ItemName, Price)}$$

Restriction:

• ItemID should be defined as the Primary Key to ensure each item is stored only once (no duplicate items).
• ItemName should have a NOT NULL constraint.
• Price should have a NOT NULL constraint and a CHECK constraint (Price > 0).
• Optionally, ItemName can have a UNIQUE constraint so no two items have the same name.

b) Relation for bill generation (quantity of item purchased):

This information should be in a new relation because the quantity purchased is related to a specific order/bill, not to the item itself. Adding it to the ITEM relation would cause redundancy.

Relation Name: BILL

Schema:
$$\text{BILL(\underline{BillNo}, ItemID, Quantity, BillDate)}$$

Primary Key: BillNo — ensures each bill is unique (satisfies restriction i).

Foreign Key: ItemID references ITEM(ItemID) — ensures a bill can only be generated for items that exist in the ITEM relation (satisfies restriction ii).

Restrictions satisfied:

• (i) BillNo as Primary Key ensures the same bill number cannot be generated for different orders.
• (ii) ItemID as Foreign Key referencing ITEM ensures bills can only be generated for items available in the canteen.

c) Where should the 'calories' attribute be stored?

The attribute 'calories' should be stored in the ITEM relation.

Reason: Calories is a property of the item itself (e.g., a samosa always has a fixed caloric value), not of a particular bill or order. Since it is an attribute that describes an item, it logically belongs to the ITEM table.

Updated ITEM schema:
$$\text{ITEM(\underline{ItemID}, ItemName, Price, Calories)}$$

Given:
$$\text{EMPLOYEE(AadharNumber, Name, Address, Department, EmployeeID)}$$
$$\text{DEPENDENT(EmployeeID, DependentName, Relationship)}$$

a) Candidate Keys of EMPLOYEE relation:

A candidate key is an attribute (or set of attributes) that can uniquely identify each tuple in the relation.

• AadharNumber — Every citizen has a unique Aadhar number. It can uniquely identify each employee. ✓ (Candidate Key)
• EmployeeID — Every employee is assigned a unique Employee ID by the organisation. It can uniquely identify each employee. ✓ (Candidate Key)
• Name, Address, Department — These cannot guarantee uniqueness (two employees can have the same name, address, or department). ✗

Candidate Keys of EMPLOYEE: AadharNumber and EmployeeID

One of these (say EmployeeID) will be chosen as the Primary Key; the other (AadharNumber) becomes the Alternate Key.

b) Tables and key required to retrieve dependent details of a particular employee:

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

• Tables required: EMPLOYEE and DEPENDENT
• Key required:
• EmployeeID is the Primary Key in the EMPLOYEE table.
• EmployeeID is the Foreign Key in the DEPENDENT table (it references the primary key of EMPLOYEE).

By joining the two tables on EmployeeID, we can retrieve the dependent details (DependentName, Relationship) for any specific employee.

Summary: Tables — EMPLOYEE and DEPENDENT; Key — EmployeeID (Primary Key in EMPLOYEE, Foreign Key in DEPENDENT).

c) Degree of EMPLOYEE and DEPENDENT relations:

• Degree = Number of attributes (columns) in a relation.

• Degree of EMPLOYEE = 5

(Attributes: AadharNumber, Name, Address, Department, EmployeeID)

• Degree of DEPENDENT = 3

(Attributes: EmployeeID, DependentName, Relationship)

Given: SCHOOL_UNIFORM database with:

• UNIFORM(UCode [PK], UName [NOT NULL], UColor)
• COST(UCode, Size [Composite PK = {UCode, Size}], Price [CHECK: Price > 0])

a) Inserting tuples into UNIFORM relation:

i) (7, Handkerchief, NULL)

Answer: Yes, this tuple CAN be inserted.

Reason:

• UCode = 7 is a new value not present in the existing UNIFORM table, so the Primary Key constraint is satisfied.
• UName = 'Handkerchief' is not NULL, so the NOT NULL constraint on UName is satisfied.
• UColor = NULL is allowed because there is no NOT NULL constraint defined on the UColor column.

$$\therefore \text{Insertion is valid.}$$

ii) (4, Ribbon, Red)

Answer: No, this tuple CANNOT be inserted.

Reason:

• UCode = 4 already exists in the UNIFORM table (Tie, Blue). Since UCode is the Primary Key, it must be unique. Inserting another record with UCode = 4 would violate the Primary Key constraint.

$$\therefore \text{Insertion is NOT valid — Primary Key violation.}$$

iii) (8, NULL, White)

Answer: No, this tuple CANNOT be inserted.

Reason:

• UCode = 8 is new, so Primary Key constraint is satisfied.
• However, UName = NULL violates the NOT NULL constraint defined on the UName column.

$$\therefore \text{Insertion is NOT valid — NOT NULL constraint violation on UName.}$$

b) Inserting tuples into COST relation:

i) (7, S, 0)

Answer: No, this tuple CANNOT be inserted.

Reason:

• The composite primary key {UCode, Size} = {7, S} does not already exist in COST, so no Primary Key violation.
• However, Price = 0 violates the CHECK constraint (Price > 0). Price must be strictly greater than 0.

$$\therefore \text{Insertion is NOT valid — CHECK constraint violation (Price must be > 0).}$$

ii) (9, XL, 100)

Answer: No, this tuple CANNOT be inserted.

Reason:

• The composite primary key {UCode = 9, Size = XL} is new, so no Primary Key violation.
• Price = 100 > 0, so CHECK constraint is satisfied.
• However, UCode = 9 does not exist in the UNIFORM table. Since UCode in COST is a Foreign Key referencing UCode (Primary Key) of UNIFORM, inserting a UCode value that does not exist in UNIFORM violates the Referential Integrity (Foreign Key constraint).

$$\therefore \text{Insertion is NOT valid — Foreign Key (Referential Integrity) constraint violation.}$$

Given:
$$\text{Movie(Movie\_ID, MovieName, ReleaseDate)}$$
$$\text{Audi(AudiNo, Movie\_ID, Seats, ScreenType, TicketPrice)}$$

Context: One movie can be shown in more than one auditorium.

a) Is Movie_ID correct as Primary Key of MOVIE relation?

Answer: Yes, it is correct to assign Movie_ID as the Primary Key of the MOVIE relation.

Reason: Each movie has a unique Movie_ID assigned to it. Since Movie_ID uniquely identifies each movie record in the MOVIE table, it satisfies the requirements of a primary key (unique and not null).

$$\text{Primary Key of MOVIE} = \text{Movie\_ID}$$

b) Is AudiNo correct as Primary Key of AUDI relation?

Answer: No, AudiNo alone is NOT the correct primary key for the AUDI relation.

Reason: Since one movie can be shown in more than one auditorium, the same AudiNo can screen different movies at different times. Also, the same auditorium (AudiNo) can show different movies, meaning AudiNo alone does not uniquely identify a tuple in the AUDI table.

Suggested Primary Key: The combination of {AudiNo, Movie_ID} should be used as the Composite Primary Key. This combination uniquely identifies which movie is being screened in which auditorium.

$$\text{Composite Primary Key of AUDI} = \{\text{AudiNo, Movie\_ID}\}$$

c) Is there any foreign key in these relations?

Answer: Yes.

• Movie_ID in the AUDI relation is a Foreign Key.
• It references Movie_ID (Primary Key) in the MOVIE relation.
• This foreign key establishes the relationship between the two tables — it ensures that a movie screened in an auditorium must exist in the MOVIE table.

$$\text{Foreign Key: AUDI.Movie\_ID} \rightarrow \text{MOVIE.Movie\_ID}$$

There is no foreign key in the MOVIE relation.

Given: STUDENT-PROJECT database with three tables:

• STUDENT(Roll No, Name, Class, Section, Registration ID)
• PROJECT(ProjectNo, PName, SubmissionDate)
• PROJECT ASSIGNED(Registration ID, ProjectNo)

a) Primary Key of each table:

• STUDENT table: Registration ID is the Primary Key.
• Reason: Roll No is unique within a class/section but may not be unique across the entire school (e.g., Roll No 11 could exist in both Class XI and XII). Registration ID is unique for every student across the school.
• (Note: If Roll No is unique across the entire school, it can also serve as a candidate key.)

• PROJECT table: ProjectNo is the Primary Key.
• Reason: Each project has a unique project number that identifies it.

• PROJECT ASSIGNED table: {Registration ID, ProjectNo} is the Composite Primary Key.
• Reason: A student (Registration ID) can be assigned only one project, and a project can be assigned to multiple students. The combination uniquely identifies each assignment.

b) Foreign Keys in PROJECT-ASSIGNED table:

The PROJECT ASSIGNED table has two foreign keys:

1. Registration ID — Foreign Key referencing Registration ID (Primary Key) of the STUDENT table.
2. ProjectNo — Foreign Key referencing ProjectNo (Primary Key) of the PROJECT table.

These foreign keys ensure that a project can only be assigned to existing students and existing projects.

c) Is there any alternate key in STUDENT table?

Answer: Yes.

Justification: The STUDENT table has the following attributes: Roll No, Name, Class, Section, Registration ID.

• Registration ID is chosen as the Primary Key.
• Roll No can also uniquely identify a student (assuming Roll No is unique across the entire school or within a class). Since Roll No can uniquely identify tuples but is not chosen as the primary key, it becomes an Alternate Key.

$$\text{Alternate Key of STUDENT} = \text{Roll No}$$

(An alternate key is a candidate key that is not selected as the primary key.)

d) Can a user assign duplicate value to RollNo in STUDENT table?

Answer: No, a user cannot assign duplicate values to RollNo.

Justification: Roll No is a candidate key (alternate key) of the STUDENT table, meaning it can uniquely identify each student. Even though Registration ID is the primary key, Roll No should have a UNIQUE constraint applied to it to prevent duplicate values. If the UNIQUE constraint is defined, the DBMS will reject any attempt to insert a duplicate Roll No.

Furthermore, from the given data, each student has a distinct Roll No (11, 12, 21, 22, 23), confirming that Roll No values are unique. Assigning duplicate Roll No values would violate the uniqueness property and make it impossible to distinguish between students.

Given: STUDENT-PROJECT database:

• STUDENT(Roll No, Name, Class, Section, Registration ID) — Primary Key: Registration ID
• PROJECT(ProjectNo, PName, SubmissionDate) — Primary Key: ProjectNo
• PROJECT ASSIGNED(Registration ID, ProjectNo) — Composite Primary Key; Foreign Keys: Registration ID → STUDENT, ProjectNo → PROJECT

a) Insert a student record with missing roll number value:

Answer: Yes, this operation CAN be performed (subject to constraints).

Reason: Roll No is not the primary key of the STUDENT table; Registration ID is the primary key. If no NOT NULL constraint has been explicitly defined on the Roll No column, then a NULL value can be stored for Roll No. The record can be inserted as long as Registration ID (primary key) has a valid, unique, non-null value.

$$\therefore \text{Insertion is possible if Roll No has no NOT NULL constraint.}$$

b) Insert a student record with missing registration number value:

Answer: No, this operation CANNOT be performed.

Reason: Registration ID is the Primary Key of the STUDENT table. A primary key cannot have a NULL value (Entity Integrity Rule). Therefore, inserting a student record without a Registration ID value would violate the primary key constraint, and the DBMS will reject this operation.

$$\therefore \text{Insertion is NOT valid — Primary Key cannot be NULL.}$$

c) Insert a project detail without submission-date:

Answer: Yes, this operation CAN be performed (subject to constraints).

Reason: SubmissionDate is not the primary key of the PROJECT table; ProjectNo is the primary key. If no NOT NULL constraint has been explicitly defined on the SubmissionDate column, then a NULL value can be stored for SubmissionDate. The record can be inserted as long as ProjectNo (primary key) has a valid, unique, non-null value.

$$\therefore \text{Insertion is possible if SubmissionDate has no NOT NULL constraint.}$$

d) Insert a record (IP-101-19, 206) in PROJECT-ASSIGNED table:

Answer: No, this operation CANNOT be performed.

Reason: The PROJECT ASSIGNED table has two foreign keys:

1. Registration ID references STUDENT(Registration ID)
2. ProjectNo references PROJECT(ProjectNo)

Checking the given data:

• Registration ID = IP-101-19 does not exist in the STUDENT table (existing IDs are IP-101-15, IP-104-15, CS-103-14, CS-101-14, CS-101-10).
• ProjectNo = 206 does not exist in the PROJECT table (existing ProjectNos are 101–106).

Inserting this record would violate the Referential Integrity (Foreign Key) constraint for both foreign keys. The DBMS will reject this insertion.

$$\therefore \text{Insertion is NOT valid — Foreign Key constraint violation for both Registration ID and ProjectNo.}$$