Relational Databases Concepts : Practice Questions

In a relational database, which type of integrity ensures that a foreign key value must either match a primary key value in a related table or be null?

In a relational database, which type of database integrity ensures that the value of a foreign key must correspond to an existing primary key value in a related table or be null?

A library database contains information about Books and Borrowers. A single book can be borrowed by multiple borrowers over time, and a single borrower can borrow multiple books. To accurately model this relationship in a relational database, which approach is most appropriate to resolve the many-to-many relationship?

In a relational database, what is the primary purpose of a foreign key?

In a relational database, an index is often created for a specific field. Which of the following is the primary purpose of creating an index in this context?

Distinguish between a primary key and a foreign key in the context of a relational database. Explain how these keys work together to ensure referential integrity.

In a relational database for a library system, the table LOAN contains a foreign key MemberID that references the primary key MemberID in the table MEMBER. If a database transaction attempts to delete a record from the MEMBER table while corresponding records still exist in the LOAN table, explain how referential integrity protects the data and describe the purpose of the rollback operation in this context.

A logistics company uses a relational database to manage its delivery operations. Two of the tables, TRUCK and DRIVER, are shown below:

TRUCK table

DRIVER table

(a) Explain the concept of referential integrity in the context of the Driver_ID field in the TRUCK table.

(b) If a database administrator attempts to delete the record for 'Bob Chan' (D105) from the DRIVER table, the system prevents the action. Suggest a reason why this occurs and identify which database concept is being enforced.

(c) The company decides to add a new column 'Driver_License_Expiry' to the DRIVER table. Specify the domain for this new attribute and state its data type.

A logistics company manages its deliveries using a relational database. The central table for tracking is defined below:

SHIPMENT (ShipmentID, ContainerID, PickupDate, Destination, Status, ShippingFee)

(a) In this table, the primary key is a composite key consisting of ShipmentID and ContainerID. Explain why a composite key is used here and identify what would qualify as a candidate key in a relational schema.

(b) The Status attribute is restricted to the domain { 'Pending', 'Shipped', 'Delivered' }. If a user attempts to update a record with the value 'Arrived', identify the specific integrity constraint violated and describe the action taken by the Database Management System (DBMS).

(c) The database administrator decides to create an index on the Destination attribute. Discuss how this index affects the performance of the system in terms of both data retrieval and data modification (e.g., adding new shipments).

(d) A transaction is initiated to increase the ShippingFee by 10% for all shipments where \( \text{Status} = 'Pending' \). If the system encounters a hardware failure after processing only half of the records, explain how the rollback operation maintains the atomicity of the transaction.