05

05 — Practice solutions

← Questions

These are independently written explanations, not an official marking scheme.

05A

Dependencies include AppointmentID → PetID, Time; PetID → PetName, OwnerID; OwnerID → OwnerName. Owner details depend transitively on the appointment key through the pet and owner identifiers.

Owner(OwnerID PK, OwnerName)
Pet(PetID PK, PetName, OwnerID FK → Owner)
Appointment(AppointmentID PK, PetID FK → Pet, Time)

Owner 1:M Pet; Pet 1:M Appointment. Minimum participation on the “many” side depends on whether owners/pets may be recorded before pets/appointments exist; the supplied facts establish the maximum cardinalities.

Reasoning

AppointmentID identifies a visit, not a pet or owner. PetName repeats when a pet has several appointments, and OwnerName repeats across that owner’s pets/appointments. Separate those independent facts while retaining PetID in Appointment and OwnerID in Pet to reconnect them.

05B

Customer(CustomerID PK, Name, Phone)
Bento(BentoID PK, Name, Price)
Orders(OrderID PK, CustomerID FK → Customer,
       BentoID FK → Bento, Quantity, OrderedAt, CollectionDate)

Customer 1:M Orders and Bento 1:M Orders. The proposed pair collides when one customer makes two orders for the same collection date. OrderID independently identifies each occurrence.

Reasoning

The question gives each order exactly one bento type, so BentoID and Quantity can belong in Orders. Do not introduce a many-item order model before it is requested. Names describe entities but the provided IDs identify them. Multiple daily orders require a key that distinguishes occurrences.

05C

Remove BentoID and Quantity from Orders. Add OrderLine(OrderID PK/FK → Orders, BentoID PK/FK → Bento, Quantity). Orders 1:M OrderLine and Bento 1:M OrderLine implement Orders M:N Bento. Quantity depends on the entire (OrderID, BentoID) key, because different bento types within an order can have different quantities.

Reasoning

One order now relates to several bento types and a bento type can appear in many orders: M:N. Quantity is neither a property of the bento alone nor of the entire order; it describes one order–bento pairing. That is why it belongs on the linking relation.

05D

Stage	Relations / change
UNF	Order header includes OrderDate; the product group repeats.
1NF	OrderItem(OrderID, ProductID, CustomerID, CustomerName, OrderDate, ProductName, Quantity); PK (OrderID, ProductID).
2NF	Orders(OrderID PK, CustomerID, CustomerName, OrderDate); Product(ProductID PK, ProductName); OrderLine(OrderID PK/FK, ProductID PK/FK, Quantity).
3NF	Split Customer out: Customer(CustomerID PK, CustomerName) and Orders(OrderID PK, CustomerID FK, OrderDate); retain Product and OrderLine.

At 2NF, OrderDate and customer facts depend on OrderID alone, and ProductName on ProductID alone: partial dependencies on the composite key. At 3NF, remove OrderID → CustomerID → CustomerName. OrderDate describes the order and stays in Orders. Each OrderLine foreign key references the corresponding parent primary key.