Database design practice questions.

A company database records that each department employs many members of staff, and that each member of staff works in exactly one department. Where does the foreign key belong?

• staffID is added to the DEPARTMENT table
• departmentID is added to the STAFF table
• Each table stores the other's primary key
• A separate table pairs staffID with departmentID

Answer: departmentID is added to the STAFF table

In a one-to-many relationship the foreign key sits on the many side, where each row needs only one value. Placing it on the one side would force a list of staff into a single attribute, and a link table is only needed for many-to-many.

Students may join several clubs, and each club has many students. Which change turns this many-to-many relationship into a workable relational design?

• Add clubID as a foreign key in STUDENT
• Store the club codes as a list in one STUDENT attribute
• Create MEMBERSHIP with studentID and clubID as its key
• Add studentID as a foreign key in CLUB

Answer: Create MEMBERSHIP with studentID and clubID as its key

A many-to-many relationship is resolved by a link table whose composite primary key is formed from the two foreign keys. A single foreign key on either side can record only one partner per row, and a list inside one attribute breaks first normal form.

A student table stores studentID as its primary key together with tutorID and tutorRoom, where tutorRoom is the room belonging to that tutor. Which dependency prevents the table from being in third normal form?

• studentID determines tutorID
• studentID and tutorID together form a composite key
• tutorRoom depends on tutorID, which is not a key
• tutorRoom is determined directly by studentID alone

Answer: tutorRoom depends on tutorID, which is not a key

A non-key attribute determined by another non-key attribute is a transitive dependency, and third normal form removes it by giving tutors their own table. If tutorRoom genuinely depended only on studentID the design would already satisfy 3NF.

In a booking database the venue's telephone number is stored in every booking row. A newly signed venue has no bookings yet, so its details cannot be recorded at all. Which anomaly does this design show?

• Insertion anomaly
• Update anomaly
• Deletion anomaly
• Referential anomaly

Answer: Insertion anomaly

An insertion anomaly means a fact cannot be stored because unrelated data is missing. Splitting the venue details into their own table lets a venue exist independently of any booking.

A staff table stores each employee together with their department's name and the department's location. The only employee working in one department leaves, the row is deleted, and the department's location is no longer recorded anywhere. Which anomaly is this?

• A referential integrity failure
• A deletion anomaly
• An insertion anomaly
• An update anomaly

Answer: A deletion anomaly

Facts about two different entities are stored in one row, so removing a fact about the employee also removes a fact about the department. Splitting departments into their own table removes the anomaly, which is exactly what normalisation to third normal form achieves here.

A garage database is to record vehicles and the repairs carried out on them. Which single statement about the garage's work makes the relationship between VEHICLE and REPAIR one-to-many rather than many-to-many?

• Each vehicle is owned by exactly one customer.
• Each vehicle may be brought in for repair several times.
• Each repair is recorded with the date on which it was finished.
• Each repair is carried out on exactly one vehicle.

Answer: Each repair is carried out on exactly one vehicle.

The many side is already given, because one vehicle may go through several repairs; what fixes the relationship as one-to-many is that a repair belongs to a single vehicle. If one repair could cover several vehicles at once, a link table would be needed instead.