PromoNormalisationERDPractice

Promo normalisation and ERD practice

← Focused guides · Main promo index

Revision guide · Mistake bank · Database answer frames

8 original questions · 80 marks · suggested time: 100 minutes, or two 50-minute sessions (Q1–Q4 / Q5–Q8). This is a focused promo-level topic practice, not a complete promo paper or an official marking scheme. The opening questions build foundations; later questions combine design and reasoning.

Answer without notes on the first attempt. Keep all workings for later marking. Show PKs, every component of composite PKs, and FK targets. For diagrams, use crow’s-foot notation or label minimum/maximum participation explicitly. Entity names may differ if the design is equivalent. Assume only the dependencies implied by the stated rules. All displayed scalar fields are atomic; names need not be unique.

Use fenced text blocks for relations and mermaid blocks or embedded PNGs for diagrams. No answers are included here. When ready, ask: “Mark PromoNormalisationERDPractice, give marks by subpart, explain lost marks, and update my mistake bank.”

Q1 — Dependencies and keys [8]

Registration(StudentID, ModuleID, StudentName, ModuleTitle, Score)

A student can register for many modules; a module can have many students. Each student registers for a given module at most once. StudentID uniquely identifies a student and determines StudentName. ModuleID uniquely identifies a module and determines ModuleTitle. Each registration has one Score. Scores and names may repeat. There are no other candidate keys or non-trivial dependencies beyond those implied here.

1. State the primary key and explain why neither component alone is suitable. [2]
2. State three functional dependencies that describe the stored facts. [3]
3. State the highest normal form and justify it using a specific dependency. [2]
4. Explain why StudentName → StudentID cannot be assumed. [1]

My answer — Q1

Q2 — Explain anomalies precisely [8]

A school keeps this 1NF table:

Booking(BookingID, RoomID, RoomName, Capacity, ClubID, ClubName, StartTime)

BookingID uniquely identifies a booking. Each booking uses exactly one room and is made by exactly one club. A room and a club can each appear in many bookings. RoomID determines RoomName and Capacity; ClubID determines ClubName. No separate room or club records exist. A booking cannot exist without a room and club.

1. Give one specific update anomaly involving a room. [2]
2. Give one specific insertion anomaly involving a room. [2]
3. Give one specific deletion anomaly involving a club. [2]
4. Explain why adding an extra unique RowID column would not solve these anomalies. [2]

My answer — Q2

Q3 — Preserve the occurrence and remove transitive facts [10]

Consultation(ConsultationID, AnimalID, AnimalName,
             KeeperID, KeeperName, ConsultationTime)

ConsultationID uniquely identifies a consultation, which is for exactly one animal at one time. Each animal has exactly one current keeper; each keeper may look after zero or many animals. AnimalID determines AnimalName and KeeperID; KeeperID determines KeeperName. Animals may have zero or many consultations, including multiple consultations on one date. Only current keeper details are required; there is no ownership-history requirement.

1. State a sufficient set of FDs, beginning with ConsultationID. [3]
2. Normalise to 3NF and show all PKs and FK targets. [4]
3. Draw the final ERD, showing minimum and maximum participation. [2]
4. Explain why KeeperID need not also be stored in Consultation. [1]

My answer — Q3

Q4 — Full UNF → 3NF progression [16]

A supplier records deliveries in this UNF structure:

Delivery(DeliveryID, DeliveryDate, ShopID, ShopName,
         {ItemID, ItemDescription, QuantityDelivered})

Each delivery has a unique DeliveryID, one date and exactly one destination shop. ShopID determines ShopName. Each delivery contains one or more items; an ItemID occurs at most once within a delivery. ItemID determines ItemDescription. QuantityDelivered is the quantity of one item in one delivery. Shops may receive many deliveries, including multiple deliveries on one date. Items can occur in many deliveries.

1. Identify the repeating group and explain why the structure is UNF. [2]
2. Write a 1NF relation, state what one row means, and identify its PK. [3]
3. State the dependencies needed for normalisation, including the dependency for QuantityDelivered. [3]
4. Show the 2NF relations with keys. Identify the partial dependencies removed. [4]
5. Show the 3NF relations with keys and FK targets. Identify the transitive dependency removed. [3]
6. Explain why DeliveryDate belongs in your chosen relation. [1]

My answer — Q4

Q5 — One product type per order [8]

A drinks stall records customers and drink types before they have any orders. Each customer has a unique CustomerID, name and phone number. Each drink type has a unique DrinkID, name and current catalogue price. Each order has a unique OrderID, exactly one customer, exactly one drink type, a quantity, an order timestamp and a collection date. A customer may place multiple orders with the same collection date. A drink type may occur in many orders. Historical prices are not required.

1. Design 3NF relations with PKs and FK targets. [4]
2. Draw an ERD showing minimum and maximum participation. [2]
3. Explain why (CustomerID, CollectionDate) is unsuitable as an order key. [1]
4. Explain whether an OrderDrink linking relation is needed under these rules. [1]

My answer — Q5

Q6 — The business rule changes [10]

Modify Q5: an order now contains one or more drink types. Each drink type appears at most once per order. A separate quantity is recorded for each drink type in each order. All other Q5 rules remain unchanged.

1. Write the complete revised 3NF design with PKs and FK targets. [4]
2. State the FD that determines quantity and use it to justify the attribute’s location. [2]
3. Draw the revised ERD, showing minimum and maximum participation. [2]
4. A student adds a separate relation Quantity(OrderID, DrinkID) and also keeps a Quantity column in Orders. Explain two problems with this proposal. [2]

My answer — Q6

Q7 — Repeated events and optional participation [10]

A library records members and physical book copies. A member has a unique MemberID and MemberName. Each copy has a unique CopyID and a ShelfLocation. A loan has a unique LoanID, exactly one member, exactly one copy, a BorrowedAt timestamp and a DueAt timestamp. Members may have no loans; copies may never have been borrowed. The same member may borrow the same copy again after returning it. The system stores all past loan occurrences. No additional title or author information is required.

1. Explain why (MemberID, CopyID) is unsuitable as the loan PK. [2]
2. Give 3NF relations with PKs and FK targets. [4]
3. Draw the ERD with minimum and maximum participation, using all historical loans. [3]
4. Explain why “one copy may be out on only one active loan at a time” would not make Copy–Loan a 1:1 relationship over the stored history. [1]

My answer — Q7

Q8 — Repair a proposed design [10]

A training centre stores:

• Each instructor has a unique InstructorID and InstructorName.
• Each course has a unique CourseID and CourseTitle and is assigned exactly one instructor. An instructor may be assigned zero or many courses.
• Each student has a unique StudentID and StudentName.
• A student may enrol in zero or many courses and a course may have zero or many students. Each student–course pair occurs at most once. Grade is stored per enrolment.
• Students, courses and instructors can be stored before any enrolments exist.

A student proposes:

Student(StudentID PK, StudentName, Grade)
Course(CourseID PK, CourseTitle, InstructorID, InstructorName)
Enrolment(StudentID FK → Student.StudentID,
          CourseID FK → Course.CourseID)

1. Identify three distinct faults or omissions in the proposed design. Explain each using the rules or dependencies. [3]
2. Give a corrected 3NF design showing all PKs and FK targets. [4]
3. Draw the corrected ERD with minimum and maximum participation. [2]
4. Explain how your design allows a course to be recorded before anyone enrols. [1]

My answer — Q8

Record after marking

Question	Maximum	My mark	Main correction	Redo date
Q1	8
Q2	8
Q3	10
Q4	16
Q5	8
Q6	10
Q7	10
Q8	10
Total	80