PromoNormalisationERDNotes

← Focused guides · Main promo index

Original chapter and marked attempts · 80-mark practice · Mistake bank · Database answer frames

This is a focused mastery guide for the normalisation and ERD scope in your existing promo notes. The examples are teaching examples, not official marking schemes.

Start with your four priorities

Evidence from your work	Habit to practise
05A: AppointmentID disappeared; OwnerID was copied into Appointment	Keep an attribute inventory. Preserve the event key and only its necessary links.
05B: a linking table was added although each order had one bento type	Read each relationship in both directions before choosing tables.
05C: Quantity stayed in Orders and became a separate entity	Ask which key determines the value: one ID, or a pair?
05D: the progression instruction was unclear	Treat UNF, 1NF, 2NF and 3NF as four separate deliverables.

You already recognised some transitive dependencies, identified Customer and Bento facts, and correctly rejected a key that could collide. Build on these skills.

1. What a row means comes before choosing its key

A relation is a table; a tuple is a row; an attribute is a column. An entity type represents things or occurrences whose facts we store. A relationship can also have facts: the quantity of a particular product in a particular order.

Write: “One row represents one ______.”

Relation	One row represents	Natural key under these rules
Student	student	StudentID
Appointment	appointment occurrence	AppointmentID
OrderLine	product in an order, each product appearing once per order	(OrderID, ProductID)
Attempt	one attempt at a module; repeats allowed	AttemptID, or (StudentID, ModuleID, AttemptNo) if the rules guarantee uniqueness

Key vocabulary

• A superkey uniquely identifies a row, possibly using unnecessary attributes.
• A candidate key is a minimal superkey: removing any component destroys guaranteed uniqueness. Minimal does not mean it must have the fewest columns of all candidate keys.
• A primary key (PK) is the chosen candidate key. It must be unique and non-null.
• An alternate key is a candidate key not chosen as the PK.
• A composite key contains more than one attribute.
• A foreign key (FK) refers to a key in another relation, or sometimes the same relation. In these exercises, mark its referenced PK explicitly. FK values may repeat; a non-null FK must match an existing referenced key.

Do not infer uniqueness merely because the sample has no duplicates. Names, dates and phone numbers are not automatically unique. Use the business rules.

(CustomerID, CollectionDate) fails if a customer can place two orders for the same collection date. State the counterexample, then choose the provided OrderID. Adding a new ID does not by itself remove redundancy or transitive dependencies.

2. Functional dependencies: whose fact is this?

X → Y means: whenever two rows have the same X, they must have the same Y, for every valid database state under the rules.

It does not mean X causes Y, or that Y → X. StudentID → StudentName allows two students to share a name. One conflicting pair of rows disproves an FD; a few agreeing rows do not establish a business rule.

For every non-key attribute, ask:

1. Can one value of this ID have two different values of the attribute?
2. If yes, do I need another ID to identify the fact?
3. Is the attribute determined through another entity’s identifier?

Example: Order O1 contains 2 of Bento B1 and 5 of Bento B2.

• OrderID → Quantity is false: O1 has quantities 2 and 5.
• BentoID → Quantity is false if different orders request different quantities.
• (OrderID, BentoID) → Quantity holds when each bento appears at most once per order.

Quantity is therefore an attribute of OrderLine. It has no independent occurrence to identify in this scenario.

Dependency types

Type	Example	What to notice
Full dependency	(OrderID, ProductID) → Quantity	Neither component alone determines Quantity.
Partial dependency	(OrderID, ProductID) is a key; ProductID → ProductName	A proper subset of a candidate key determines a non-key attribute.
Transitive dependency relevant here	AppointmentID → PetID → PetName	A key determines a non-key fact through another non-key attribute.

Write the actual attributes in an exam explanation. “There is a dependency” is too vague.

3. Normal forms and how to justify them

Stage	Test	What you do
UNF	Repeating groups or multiple values in a cell	Identify the repeated group and row meaning.
1NF	Atomic values for the intended domain; no repeating groups	Represent individual occurrences as rows with a suitable key.
2NF	In 1NF; no partial dependency of non-prime attributes on a candidate key	Move facts determined by part of a composite key into their own relation.
3NF	In 2NF; no relevant transitive non-key dependency	Move the intermediate entity’s facts into its own relation and retain its ID as an FK.

For the usual questions with one stated candidate key, “non-key” means attributes outside that key. More precisely, a prime attribute belongs to at least one candidate key. If a question gives multiple candidate keys, check them all. The formal 3NF test is: for every non-trivial FD X → A, X is a superkey or A is prime. You do not need BCNF to answer the exercises here.

If all candidate keys are single attributes, a 1NF relation cannot have a partial dependency. It can still violate 3NF. A single-column chosen PK alone is not enough evidence when other composite candidate keys exist.

Atomic does not mean “cannot be split into characters”: a name can be one value for the intended use. A list of several ProductIDs in one cell represents multiple occurrences. Product1, Product2 and Product3 columns are a repeating-group design, not a scalable repair.

Highest normal form answer pattern

The relation is in 1NF because its values are atomic and there are no repeating groups. It is not in 2NF because ProductID → ProductName, where ProductID is only part of the composite key (OrderID, ProductID). Its highest normal form is therefore 1NF.

Do not skip the “in the preceding form” requirement.

4. Full progression, slowly: your 05D gap

Rules: Each order has a unique OrderID, one OrderDate and one customer. CustomerID determines CustomerName. An order has one or more products; a product appears at most once per order. ProductID determines ProductName. Quantity is recorded per order–product pair. No other dependencies are assumed.

UNF: show what repeats

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

The braces indicate repeated product occurrences, not a column to keep in the final database.

1NF: one row per order–product pair

OrderID	ProductID	OrderDate	CustomerID	CustomerName	ProductName	Quantity
O1	P1	2026-09-12	C1	Ali	Pen	2
O1	P2	2026-09-12	C1	Ali	Book	5
O2	P1	2026-09-13	C1	Ali	Pen	1

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

Neither OrderID nor ProductID alone uniquely identifies these rows. Repeating an ID across rows does not violate 1NF.

Write the dependencies before splitting:

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

OrderID also determines CustomerName indirectly. Therefore CustomerName depends on part of the 1NF composite key too; it moves with the order facts at the 2NF stage.

2NF: remove dependencies on parts of the key

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

OrderDate, CustomerID and CustomerName depend on OrderID alone. ProductName depends on ProductID alone. Quantity needs the whole pair and stays in OrderLine. Each resulting relation is in at least 2NF under the rules. Orders still has customer facts to separate.

3NF: remove the remaining transitive dependency

OrderID → CustomerID → CustomerName exists inside Orders. Keep CustomerID as a link and move CustomerName:

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

Why OrderDate stays: OrderID → OrderDate. A customer can place orders on different dates, so it is not a Customer fact. Different orders containing the same product can have different dates, so it is not a Product fact.

Why the tables reconnect: each OrderLine has its order and product IDs; each order has its customer ID. Joining through these keys retrieves the original line facts without inventing combinations. Check that every original attribute appears somewhere. IDs may appear in several tables as links; descriptive facts should remain with their determinant.

5. Anomalies: explain the concrete harm

Using the 1NF order table above:

Anomaly	Specific example	How this design repairs it
Update	Renaming P1 requires changing every P1 row; missing one leaves inconsistent product names.	ProductName is maintained once in Product.
Insertion	A new product cannot be recorded on its own without inventing an order line or leaving part of its key empty.	Insert a Product row before it is ordered.
Deletion	Deleting the only line containing P2 may erase the only stored facts about P2.	Product facts remain independently of OrderLine.

An anomaly is not simply “there are duplicates”. State the operation, duplicated or coupled fact, and consequence. Normalisation reduces unnecessary redundancy and these anomalies; it may require additional joins. It does not guarantee faster queries or eliminate every repeated value.

6. ERDs: translate rules before drawing

For each relationship, complete both sentences:

One A relates to a minimum of ___ and maximum of ___ Bs.
One B relates to a minimum of ___ and maximum of ___ As.

Cardinality concerns the maximum (1 or many); optionality concerns whether the minimum is 0 or 1. “May have many” normally allows zero; “must have at least one” means 1..many. If a question does not specify optionality, state an assumption instead of presenting it as a given fact.

Read the symbols beside B to find how many Bs relate to one A.

Endpoint	Meaning
`
`o	or
o{ or }o	Zero or many
`	{or}

1:M: put the foreign key on the many side

An owner may have many pets; each pet has exactly one owner:

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

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

Here we assume owners may be registered before they own a recorded pet, and pets before an appointment. Every appointment is for exactly one pet.

Appointment(AppointmentID PK, PetID FK → Pet.PetID, Time) preserves the event. Do not repeat OwnerID there under PetID → OwnerID: the owner is found through Pet. If a different question requires historical ownership at the appointment, re-read its rules; that is a different modelling requirement.

M:N: introduce an associative entity

If an order contains many bento types and a bento type appears in many orders:

OrderLine(OrderID PK/FK → Orders.OrderID,
          BentoID PK/FK → Bento.BentoID, Quantity)
PK = (OrderID, BentoID), only if each pair occurs at most once

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

Each line belongs to exactly one order and one bento. Here every order has at least one line; a bento may have no lines yet. The final relational ERD shows two 1:M links instead of a direct M:N link. The logical Orders–Bento association remains many-to-many through the lines.

Your 05B versus 05C decision

Business rule	Correct placement
Each order contains exactly one bento type	Put BentoID and Quantity in Orders. Bento 1:M Orders.
Each order contains several types, each at most once	Put BentoID and Quantity in OrderLine with OrderID.
Same type may appear on separate lines within an order	Use (OrderID, LineNo), or an appropriate LineID; (OrderID, BentoID) is no longer unique.

A linking table is a consequence of the rules, not a compulsory ingredient in every order question.

1:1 and repeated events

For an employee with at most one locker and a locker assigned to at most one employee, an FK needs a UNIQUE constraint to enforce the maximum of one. Without it, multiple employees could reference the same locker. Optionality helps decide which side stores the FK and whether it may be null.

For a student borrowing the same book repeatedly, (StudentID, BookID) identifies the pair but not an individual loan. Use the given LoanID, or a rule-backed occurrence key. A date alone might still collide if multiple events can occur on that date.

7. Exam workflow and answer template

1. Underline identifiers and words such as one, many, each, may, at most once and repeatedly.
2. State one-row meaning and the candidate/primary key.
3. List the given and relevant derived FDs.
4. If asked for progression, write separate UNF, 1NF, 2NF and 3NF sections. Identify the removed dependency at each split.
5. Mark every PK component and FK target. A composite PK is one key made of several columns, not several independent PKs.
6. Draw entities and read every edge in both directions. Put FKs on the many side.
7. Audit all original attributes and business rules, including repeated events.

One row represents: ...
Primary key: ... because ...
Dependencies: ...
Highest normal form: ... because ...
Violation: ... → ...
Resulting relations with PK/FK targets: ...
Relationships in both directions: ...
Optionality assumptions: ...
Attribute and rejoin check: ...

For your final 30-second check: ID kept? Pair unique? Quantity with its determinant? FK on many side? All stages shown?

8. Drawing ERDs on your computer

Use draw.io for manual exam practice. Its entity-relationship shapes and crow’s-foot connectors let you construct the design yourself. Open diagrams.net, enable the Entity Relation shape library through More Shapes, add entity boxes, label attributes with PK/FK, and connect them with the correct endpoints. Save the editable .drawio file and export a PNG to embed next to your answer. See the official entity relationship guide and crow’s-foot notation guide.

Use Mermaid for quick Obsidian diagrams. Obsidian supports Mermaid code blocks; see Obsidian’s diagram documentation. Paste this as a fenced block labelled mermaid:

```mermaid
erDiagram
    CUSTOMER ||--o{ ORDERS : places
    CUSTOMER {
        string CustomerID PK
        string CustomerName
    }
    ORDERS {
        string OrderID PK
        string CustomerID FK
    }
```

Diagram labels do not enforce database constraints: you still need a correct relational design. For practice, create the relationships yourself before checking the rendered result. Use your school’s notation if a question specifies it.

9. How to use this pack until you can do it independently

• First session: work through section 4, then close it and reproduce all four stages. Explain where each attribute moves.
• Next session: attempt practice Q1–Q4 without notes. Mark any sentence whose rule you could not translate.
• Then attempt Q5–Q8. Save diagrams or Mermaid beside each answer and ask for marking by question number.
• After feedback: explain each error in your own words and redo it without looking. Only mark a mistake-bank case mastered after a later independent correct attempt.

You are ready when you can justify keys, state exact FDs, complete the full progression, explain all three anomalies, and draw a rule-consistent ERD without relying on a memorised scenario.

Sources and scope

Based on 05 Normalisation and ER diagrams, its marked 05A–05D attempts, and the normalisation/ERD scope recorded in Sources. Tool documentation checked on 2026-09-12. The practice is original and its marks/time are suggested training values, not official HCI allocations.