Normalisation and ER diagrams

Priority key · Priorities guide emphasis; they do not remove taught scope.

Exam recall

1NF: atomic values. 2NF: no partial non-key dependency. 3NF: no relevant transitive non-key dependency. Show the actual dependencies and all PK/FK links.

Normalisation is about dependencies priority/high

Ask “Whose fact is this?” A student’s name describes the student, a course’s name describes the course, and an enrolment grade describes that student taking that course. Normalisation puts each fact with the key that determines it, then uses foreign keys to reconnect the entities.

A functional dependency A → B means each A value determines one B value under the rules of the data. It does not mean each B determines A. StudentID determines StudentName; many students may share a name.

Form	School-level test	Repair
1NF	Atomic values, no repeating groups; records identifiable by a key	Put repeated occurrences into separate rows/relations with appropriate keys.
2NF	In 1NF; each non-key attribute depends on the whole of every relevant candidate key, not just part	Separate facts depending on only part of a composite key.
3NF	In 2NF; no transitive dependency of non-key attributes on a key through another non-key attribute	Separate the intermediate entity and reference its key.

For ordinary school questions, state the primary key and the actual dependency showing the violation. A single-attribute key cannot have a partial dependency on a proper part of itself. It can still have a transitive dependency. Do not declare 3NF simply because “the tables look smaller”.

Partial:    (StudentID, CourseID) is the key,
             StudentID alone → StudentName.
             A proper part of the key determines a non-key fact.
 
Transitive: AppointmentID → PetID → PetName.
             The appointment key determines the pet name through PetID.

2NF removes partial dependency; 3NF removes the relevant non-key transitive dependency. Naming the form without identifying the dependency does not justify a decomposition.

A repeatable exam method priority/high

Identify what one row represents and which business rules establish uniqueness.
Write the key and dependencies, such as StudentID → StudentName.
Remove repeating groups; then partial dependencies; then transitive dependencies.
List every resulting relation, marking PKs and FKs explicitly.
Check that joining through the links recovers the intended facts and that no original required attribute was lost.

Normalisation reduces specific redundancy and anomalies. It can require more joins; it does not mean all duplicate values are forbidden. Repeating StudentID in an enrolment table is necessary to represent several enrolments.

ER diagrams and relationship mapping priority/high

An entity has attributes and a key. Cardinality states how many occurrences can be associated: 1:1, 1:M or M:N. Optionality states whether participation may be zero. Read a relationship in both directions using the scenario’s rules.

For 1:M, place the foreign key on the many side. For M:N, introduce an associative/linking entity with foreign keys to both parent entities. Its key may be the pair of foreign keys if each pair can occur only once; otherwise include an occurrence identifier. A 1:1 relationship can use a foreign key with a uniqueness constraint, choosing its side according to participation/design requirements.

Worked example — based on your diagnostic

Enrolment(StudentID, CourseID, StudentName, CourseName) has PK (StudentID, CourseID), with StudentID → StudentName and CourseID → CourseName. Each name depends on only part of the key, violating 2NF.

Result:

Student(StudentID PK, StudentName)
Course(CourseID PK, CourseName)
Enrolment(StudentID PK/FK → Student, CourseID PK/FK → Course)

Each enrolment belongs to exactly one student and one course. A student or course may have zero or many enrolments if the business rules permit storing them before enrolment.

erDiagram
    STUDENT ||--o{ ENROLMENT : makes
    COURSE ||--o{ ENROLMENT : contains

The decomposition allows inserting a course before anyone enrols and prevents deletion of its last enrolment from deleting the course name.

School diagram: resolving M:N priority/medium

School ERD showing Student–Subject linking entity

Source: School ERD, PDF p.52. The lower diagram replaces Student–Subject’s M:N relationship with two 1:M relationships. It uses the school’s crow’s-foot notation; read the endpoint beside an entity to determine the number of that entity linked to one at the other end.

Worked progression: UNF to 3NF priority/high

Scenario: each order has OrderID and one customer. CustomerID determines CustomerName. An order contains several products, each appearing at most once. ProductID determines ProductName. Quantity describes a product in an order.

An order with two products flattened to 1NF, separated at 2NF, then customer facts separated at 3NF

UNF: Order(OrderID, CustomerID, CustomerName, {ProductID, ProductName, Quantity}), where braces indicate the repeating product group.

1NF: one row per order–product pair:

OrderItem(OrderID, ProductID, CustomerID, CustomerName, ProductName, Quantity)

PK = (OrderID, ProductID). Dependencies: OrderID → CustomerID; CustomerID → CustomerName; ProductID → ProductName; (OrderID, ProductID) → Quantity.

2NF: remove dependencies on only part of the composite key:

Orders(OrderID PK, CustomerID, CustomerName)
Product(ProductID PK, ProductName)
OrderLine(OrderID PK/FK → Orders, ProductID PK/FK → Product, Quantity)

3NF: Orders still has OrderID → CustomerID → CustomerName. Separate the customer facts:

Customer(CustomerID PK, CustomerName)
Orders(OrderID PK, CustomerID FK → Customer)
Product(ProductID PK, ProductName)
OrderLine(OrderID PK/FK → Orders, ProductID PK/FK → Product, Quantity)

Check: Quantity remains attached to the entire order–product pair. Repeated foreign-key IDs are legitimate links. Deleting an order’s last line no longer destroys the only stored product name. Under the stated dependencies, the resulting relations are in 3NF.

Practice

Exam focus: normalisation and relationship design recur in HCI 2022 modified Q5, 2023 Q6,2024 Q5 and 2025 Q5. The 2025 bento question, PDF p.4 is useful for extracting entities from prose; the 2024 transport question, p.3 tests both anomalies and design changes.

Before finishing: account for every original attribute, mark every PK/FK, and read each relationship in both directions. Do not add an extra entity solely because a noun appears: decide whether it has independent facts/occurrences to store.

05A — original. Appointment(AppointmentID, PetID, PetName, OwnerID, OwnerName, Time) has unique AppointmentID. Each pet has one owner; each owner can own many pets. Identify the dependencies and normalise to 3NF, showing keys and relationships.

Transitive dependency as PetID -> OwnerID -> OwnerName
Pet(PetID, PetName, OwnerID)
Owner(OwnerID, OwnerName)
Appointment(PetID, OwnerID, Time)

Marker feedback — Partially correct

You got these parts right:

Pet contains PetID, PetName and OwnerID.

Owner contains OwnerID and OwnerName.

PetID → OwnerID → OwnerName is a valid dependency chain.

Parts to correct:

You did not state the dependencies beginning from AppointmentID.

AppointmentID was lost from your Appointment relation even though it uniquely identifies an appointment.

OwnerID should not be stored in Appointment; it can be found through the appointment’s PetID.

PKs, FKs and relationships were not marked.

Correct solution

Dependencies

AppointmentID → PetID, Time

PetID → PetName, OwnerID

OwnerID → OwnerName

Full transitive path: AppointmentID → PetID → OwnerID → OwnerName

3NF relations

Owner(OwnerID PK, OwnerName)

Pet(PetID PK, PetName, OwnerID FK → Owner)

Appointment(AppointmentID PK, PetID FK → Pet, Time)

Relationships: Owner 1:M Pet; Pet 1:M Appointment.

05A solution diagram

erDiagram
    OWNER ||--o{ PET : owns
    PET ||--o{ APPOINTMENT : has

    OWNER {
        string OwnerID PK
        string OwnerName
    }

    PET {
        string PetID PK
        string PetName
        string OwnerID FK
    }

    APPOINTMENT {
        string AppointmentID PK
        string PetID FK
        string Time
    }

05B — adapted from HCI 2025 Q5. A customer has a unique CustomerID, name and phone. A bento has unique BentoID, name and price. Each order has a unique OrderID, one customer, one bento type, a quantity, a timestamp and a collection date. A customer may place several orders daily. Design 3NF relations and state the cardinalities. Explain why (CustomerID, CollectionDate) is unsuitable as the order key.

Customer(CustomerID, CustomerName, CustomerPhoneNo)
Bento(BentoID, BentoName, Price)
Order(OrderID, CustomerID, Quantity, Time, Date)
OrderBento(OrderID, BentoID)

Customer -<Order-<OrderBento>-Bento

Why (CustomerID, CollectionDate) is unsuitable as the order key:

As a customer may place several orders in a day, both orders will have the exact same primary key, so the database cannot store both of them without violating PK being unique.

Marker feedback — Partially correct

You got these parts right:

The Customer and Bento attributes are correct.

Your explanation of why (CustomerID, CollectionDate) is unsuitable is correct.

Main misunderstanding:

05B says each order has one bento type.

Therefore, put BentoID directly in Orders as a foreign key.

OrderBento is unnecessary here. A linking relation is needed only in 05C, when one order may contain several bento types.

PKs, FKs and the two 1:M relationships must be marked.

Why the proposed key fails

flowchart LR
    A["Order 1<br/>Customer C01<br/>Collection 12 Sep"] --> C["Same proposed key:<br/>(C01, 12 Sep)"]
    B["Order 2<br/>Customer C01<br/>Collection 12 Sep"] --> C
    C --> D["Primary-key collision:<br/>second order cannot be stored"]

    classDef wrong fill:#ffebee,stroke:#d32f2f,color:#b71c1c
    class C,D wrong

Correct solution

Customer(CustomerID PK, CustomerName, CustomerPhoneNo)

Bento(BentoID PK, BentoName, Price)

Orders(OrderID PK, CustomerID FK → Customer, BentoID FK → Bento, Quantity, Timestamp, CollectionDate)

Cardinalities

One Customer can place many Orders.

Each Order belongs to one Customer.

One Bento can appear in many Orders.

Each Order contains one Bento type.

OrderID is suitable because it uniquely distinguishes each order occurrence.

05B solution diagram — no linking table needed

erDiagram
    CUSTOMER ||--o{ ORDERS : places
    BENTO ||--o{ ORDERS : appears_in

    CUSTOMER {
        string CustomerID PK
        string CustomerName
        string CustomerPhoneNo
    }

    BENTO {
        string BentoID PK
        string BentoName
        decimal Price
    }

    ORDERS {
        string OrderID PK
        string CustomerID FK
        string BentoID FK
        int Quantity
        datetime Timestamp
        date CollectionDate
    }

05C — original, extra practice. An order may now contain several bento types. Adapt 05B, assuming each bento type appears at most once per order. Explain where Quantity belongs.

Hints

05A: distinguish the appointment fact from pet and owner facts. 05C: a quantity now describes a particular order–bento pair.

Customer(CustomerID, CustomerName, CustomerPhoneNo)
Bento(BentoID, BentoName, Price)
Order(OrderID, CustomerID, Quantity, Time, Date)
OrderBento(OrderID, BentoID)
Quantity(OrderID, BentoID)

Marker feedback — Partially correct

You got this part right:

You recognised that a linking relation is now required between Orders and Bento.

Parts to correct:

Quantity should not remain in Orders, because one order can contain different quantities of different bento types.

Quantity is an attribute, not a separate entity.

Place Quantity inside the linking relation because it describes one specific order-bento pairing.

Mark the composite PK, both FKs and the cardinalities.

Why Quantity belongs in the linking relation

flowchart LR
    A["OrderID<br/>Which order?"] --> C["OrderID + BentoID<br/>One particular order-bento pair"]
    B["BentoID<br/>Which bento?"] --> C
    C --> D["Quantity<br/>How many of this bento<br/>in this order?"]

    classDef answer fill:#e8f5e9,stroke:#2e7d32,color:#1b5e20
    class C,D answer

Correct solution

Customer(CustomerID PK, CustomerName, CustomerPhoneNo)

Bento(BentoID PK, BentoName, Price)

Orders(OrderID PK, CustomerID FK → Customer, Timestamp, CollectionDate)

OrderLine(OrderID PK/FK → Orders, BentoID PK/FK → Bento, Quantity)

(OrderID, BentoID) is the composite primary key of OrderLine because each bento type appears at most once per order.

Quantity belongs in OrderLine because (OrderID, BentoID) → Quantity.

05C solution diagram — linking table required

erDiagram
    CUSTOMER ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_LINE : contains
    BENTO ||--o{ ORDER_LINE : appears_in

    CUSTOMER {
        string CustomerID PK
        string CustomerName
        string CustomerPhoneNo
    }

    ORDERS {
        string OrderID PK
        string CustomerID FK
        datetime Timestamp
        date CollectionDate
    }

    BENTO {
        string BentoID PK
        string BentoName
        decimal Price
    }

    ORDER_LINE {
        string OrderID PK, FK
        string BentoID PK, FK
        int Quantity
    }

The graph replaces the M:N relationship between Orders and Bento with two 1:M relationships:

Orders 1:M OrderLine
Bento  1:M OrderLine

05D — original, full progression. Add OrderDate to the illustrated order example, with OrderID → OrderDate. Starting from UNF, show 1NF, 2NF and 3NF relations. Mark keys and identify the dependency removed at each decomposition. Explain where OrderDate belongs.

I dont really understand the question. You can go through with me

Marker feedback — Not attempted

This asks you to repeat the illustrated order example above while adding OrderDate:

Show the original repeating product group in UNF.

Remove the repeating group to reach 1NF.

Remove partial dependencies to reach 2NF.

Remove the transitive dependency to reach 3NF.

Mark all PKs and FKs and explain where OrderDate belongs.

05D normalisation graph

flowchart LR
    A["UNF<br/>Order header + repeating product group"]
    B["1NF<br/>One row per OrderID-ProductID pair"]
    C["2NF<br/>Orders + Product + OrderLine"]
    D["3NF<br/>Customer + Orders + Product + OrderLine"]

    A -->|"Remove repeating group"| B
    B -->|"Remove partial dependencies"| C
    C -->|"Remove transitive dependency"| D

    classDef stage fill:#e8f5e9,stroke:#2e7d32,color:#1b5e20
    class A,B,C,D stage

Correct solution — UNF

Order(OrderID, CustomerID, CustomerName, OrderDate, {ProductID, ProductName, Quantity})

The product fields form a repeating group because one order may contain several products.

Correct solution — 1NF

OrderItem(OrderID, ProductID, CustomerID, CustomerName, OrderDate, ProductName, Quantity)

Composite PK: (OrderID, ProductID)

One row now represents one product in one order.

The repeating group has been removed.

Dependencies:

OrderID → CustomerID, CustomerName, OrderDate

ProductID → ProductName

(OrderID, ProductID) → Quantity

Correct solution — 2NF

Remove attributes that depend on only part of the composite key:

Orders(OrderID PK, CustomerID, CustomerName, OrderDate)

Product(ProductID PK, ProductName)

OrderLine(OrderID PK/FK → Orders, ProductID PK/FK → Product, Quantity)

This removes the partial dependencies on OrderID and ProductID.

Correct solution — 3NF

Orders still contains the transitive dependency OrderID → CustomerID → CustomerName. Separate the customer facts:

Customer(CustomerID PK, CustomerName)

Orders(OrderID PK, CustomerID FK → Customer, OrderDate)

Product(ProductID PK, ProductName)

OrderLine(OrderID PK/FK → Orders, ProductID PK/FK → Product, Quantity)

05D final 3NF diagram

erDiagram
    CUSTOMER ||--o{ ORDERS : places
    ORDERS ||--|{ ORDER_LINE : contains
    PRODUCT ||--o{ ORDER_LINE : appears_in

    CUSTOMER {
        string CustomerID PK
        string CustomerName
    }

    ORDERS {
        string OrderID PK
        string CustomerID FK
        date OrderDate
    }

    PRODUCT {
        string ProductID PK
        string ProductName
    }

    ORDER_LINE {
        string OrderID PK, FK
        string ProductID PK, FK
        int Quantity
    }

Where OrderDate belongs

OrderDate stays in Orders because OrderID → OrderDate. It describes the whole order, not a customer, product or individual order line.

Revision checklist

Visual revision mindmap

Chapter 05 revision mindmap

Open this mindmap and its text version · All 21 mindmaps

Your mindmap framework

Centre: Normalisation and ER diagrams. Build the six branches below. For each subbranch, add a short definition, a labelled sample and one exam trap from memory; then check the chapter.

flowchart LR
    C["05 • Revision map"]
    C --> B0["Read the scenario"]
    C --> B1["Functional dependencies"]
    C --> B2["Normal-form progression"]
    C --> B3["ER relationships"]
    C --> B4["Worked decomposition"]
    C --> B5["Visual checks and mistakes"]

Read the scenario
- What does one row represent?.
- Entity facts versus relationship facts.
- Business rules establish keys and cardinalities.
- Keep every required attribute through decomposition.
Functional dependencies
- Determinant → dependent.
- A determines B does not mean B determines A.
- Partial: proper part of composite key determines non-key fact.
- Transitive: key → intermediate non-key fact → another non-key fact.
Normal-form progression
- UNF: repeating group.
- 1NF: atomic values, identifiable rows.
- 2NF: remove partial non-key dependencies.
- 3NF: remove relevant transitive non-key dependencies.
ER relationships
- Read both directions.
- 1:1, 1:M, M:N; cardinality versus optionality.
- Foreign key on many side for 1:M.
- Resolve M:N with linking entity and appropriate key.
Worked decomposition
- Order header plus repeating products.
- Flatten one row per order–product pair.
- Separate Orders, Product and OrderLine.
- Separate Customer; Quantity remains on OrderLine.
Visual checks and mistakes
- Draw one diagram per normal-form stage.
- Mark PK, composite components and FK targets.
- Rejoin conceptually and check no required facts are lost.
- Avoid: smaller tables automatically mean 3NF; names automatically identify.

Close the notes and test the map: explain one branch aloud, sketch its sample, then answer a linked practice question. Mark any missing link to revisit.

Source trail

9569 §§3.3.4–3.3.5; school ERD and Normalization slides.

HCI 2022 Q5(b–d); 2023 Q6(a–b); 2024 Q5(b–d),(f); 2025 Q5(a–c).

Source guide records provenance and original-paper locations.

Quartz 5

Explorer

05 Normalisation and ER diagrams

Normalisation and ER diagrams

Normalisation is about dependencies priority/high

A repeatable exam method priority/high

ER diagrams and relationship mapping priority/high

Worked example — based on your diagnostic

School diagram: resolving M:N priority/medium

Worked progression: UNF to 3NF priority/high

Practice

Revision checklist

Visual revision mindmap

Your mindmap framework

Source trail

Graph View

Table of Contents

Backlinks