Learn
A3.2.6 | CONSTRUCTING DATABASES IN 3NF
01 | FROM EXPLAINING TO CONSTRUCTING
In A3.2.5 you explained why tables change during normalization. Now you must build the design yourself. A good answer shows tables, attributes, primary keys and foreign keys and explains why each fact belongs where you put it.
Start with the real-world rules. A library lends physical copies, not just book titles. A hospital records appointments, not just patient names. An online shop must remember the price charged on an order, even if the current product price changes tomorrow.
There is rarely one correct list of tables for every organization. The model is correct only for its stated assumptions. The examples below are teaching models, not complete operational systems.
Revisit keys, dependencies and normal forms if those terms are still unfamiliar.
02 | A REPEATABLE DESIGN METHOD
- Read the requirements. Identify the things, events and relationships the system must record.
- State the rules. Can a book have several authors? Can a student enrol more than once? Does each department have one office?
- Identify candidate keys. Find minimal unique identifiers. Choose primary keys; do not assume names are unique.
- Write dependencies. Ask which attributes determine other attributes. Analyze all candidate keys.
- Reach 1NF. Remove lists and repeating column groups; give each row a unique identity.
- Reach 2NF. Move non-key facts that depend on only part of a composite candidate key.
- Reach 3NF. Move non-key facts determined through another non-key attribute.
- Reconnect and test. Keep foreign keys, preserve the original information, and try inserting, updating and deleting sample records.
PK means primary key; FK means foreign key. Where two fields are marked “PK part”, together they form one composite primary key.
03 | LIBRARY: UNDERSTAND WHAT A LOAN MEANS
The library has members, book editions and physical copies. A title can have several copies and several authors. A member can borrow a copy again after returning it.
Our rules: MemberID identifies a member. BookID identifies a book edition, with one title. CopyID identifies a physical copy belonging to one BookID. LoanID identifies one borrowing event. One loan has one member, one copy, a loan date and a due date.
| MemberID | MemberName | Borrowed items |
|---|---|---|
| M01 | Alex | L01: C10, Robot Design, 2026-10-01, due 2026-10-15; L02: C20, Python Basics, 2026-10-02, due 2026-10-16 |
| M02 | Rin | L03: C11, Robot Design, 2026-10-03, due 2026-10-17 |
This structure is awkward to query. We cannot easily sort individual due dates or update one loan. First make each borrowing event a row.
04 | LIBRARY: 1NF AND THE 2NF CHECK
| LoanID (PK) | MemberID | MemberName | CopyID | BookID | Title | LoanDate | DueDate |
|---|---|---|---|---|---|---|---|
| L01 | M01 | Alex | C10 | B01 | Robot Design | 2026-10-01 | 2026-10-15 |
| L02 | M01 | Alex | C20 | B02 | Python Basics | 2026-10-02 | 2026-10-16 |
| L03 | M02 | Rin | C11 | B01 | Robot Design | 2026-10-03 | 2026-10-17 |
The cells are atomic, and LoanID uniquely identifies each row. Under these rules, assume no other candidate key. LoanID is a single-attribute key, so there can be no partial-key dependency. This 1NF table already meets 2NF.
Normalization does not always mean splitting a table at every stage. We still have transitive dependencies:
LoanID → MemberID → MemberName
LoanID → CopyID → BookID → Title
MemberName describes the member, not the loan. Title describes the book edition, not the individual borrowing event. If Alex changes name, we should not need to edit every loan.
05 | LIBRARY: CONSTRUCT THE 3NF TABLES
MEMBER(MemberID PK, MemberName)
BOOK(BookID PK, Title)
COPY(CopyID PK, BookID FK)
LOAN(LoanID PK, MemberID FK, CopyID FK, LoanDate, DueDate)
| MemberID (PK) | MemberName |
|---|---|
| M01 | Alex |
| M02 | Rin |
| BookID (PK) | Title |
|---|---|
| B01 | Robot Design |
| B02 | Python Basics |
| CopyID (PK) | BookID (FK) |
|---|---|
| C10 | B01 |
| C11 | B01 |
| C20 | B02 |
| LoanID (PK) | MemberID (FK) | CopyID (FK) | LoanDate | DueDate |
|---|---|---|---|---|
| L01 | M01 | C10 | 2026-10-01 | 2026-10-15 |
| L02 | M01 | C20 | 2026-10-02 | 2026-10-16 |
| L03 | M02 | C11 | 2026-10-03 | 2026-10-17 |
Follow one loan: L03 links to member M02 (Rin) and copy C11. C11 links to B01 (Robot Design). We can recover the original information by joining tables through those keys.
LoanDate and DueDate belong to the borrowing event. In this example, due dates can be adjusted per loan; they are not determined solely by LoanDate. A policy that makes one field determine another may require a different design.
06 | LIBRARY: MANY AUTHORS, MANY BOOKS
Do not add Author1, Author2 and Author3 columns. One book may have several authors, and one author may write several books. Model this many-to-many relationship with a junction table:
AUTHOR(AuthorID PK, AuthorName)
BOOK_AUTHOR(BookID PK part/FK, AuthorID PK part/FK)
| AuthorID (PK) | AuthorName |
|---|---|
| A01 | Taylor |
| A02 | Morgan |
| BookID (PK part/FK) | AuthorID (PK part/FK) |
|---|---|
| B01 | A01 |
| B01 | A02 |
| B02 | A01 |
The pair prevents the same author–book link being recorded twice. Neither ID is unique on its own in this table. Links are allowed to repeat an ID: that is how the relationship is represented.
07 | SCHOOL: A COMPOSITE-KEY EXAMPLE
A school records enrolments in course offerings. An OfferingID identifies a particular class in a particular period; a CourseID identifies its subject or course definition. Each offering has one teacher. Each student can enrol once per offering, with one grade.
| StudentID (PK part) | StudentName | OfferingID (PK part) | CourseID | CourseTitle | TeacherID | TeacherName | Grade |
|---|---|---|---|---|---|---|---|
| S01 | Alex | O10 | CS | Computing | T01 | Lee | A |
| S01 | Alex | O20 | MA | Mathematics | T02 | Sam | B |
| S02 | Rin | O10 | CS | Computing | T01 | Lee | B |
To reach 2NF: StudentName depends only on StudentID; the offering details depend only on OfferingID. Grade depends on the whole student–offering pair.
STUDENT(StudentID PK, StudentName)
OFFERING(OfferingID PK, CourseID, CourseTitle, TeacherID, TeacherName)
ENROLMENT(StudentID PK part/FK, OfferingID PK part/FK, Grade)
To reach 3NF: CourseID determines CourseTitle and TeacherID determines TeacherName. Move those indirectly dependent facts out of OFFERING:
STUDENT(StudentID PK, StudentName)
COURSE(CourseID PK, CourseTitle)
TEACHER(TeacherID PK, TeacherName)
OFFERING(OfferingID PK, CourseID FK, TeacherID FK)
ENROLMENT(StudentID PK part/FK, OfferingID PK part/FK, Grade)
Keep the two identifiers in ENROLMENT so we can identify whose grade belongs to which class. The school’s rules determine whether team teaching or multiple assessments need extra tables.
08 | HOSPITAL MANAGEMENT
A clinic records appointments. Each appointment has one patient and one doctor. Each doctor belongs to one department; each department has one recorded name.
Dependencies:
PatientID → PatientName
DoctorID → DoctorName, DepartmentID
DepartmentID → DepartmentName
AppointmentID → PatientID, DoctorID, AppointmentDateTime
A 3NF design under these rules:
PATIENT(PatientID PK, PatientName)
DEPARTMENT(DepartmentID PK, DepartmentName)
DOCTOR(DoctorID PK, DoctorName, DepartmentID FK)
APPOINTMENT(AppointmentID PK, PatientID FK, DoctorID FK, AppointmentDateTime)
09 | E-COMMERCE
A customer places orders. Each order has lines; the same product may occur on multiple lines, so use (OrderID, LineNumber) to identify a line. Quantity and the agreed unit price belong to that line.
Dependencies:
CustomerID → CustomerName
ProductID → ProductName, CurrentPrice
OrderID → CustomerID, OrderDate
(OrderID, LineNumber) → ProductID, Quantity, UnitPriceAtOrder
A 3NF design under these rules:
CUSTOMER(CustomerID PK, CustomerName)
PRODUCT(ProductID PK, ProductName, CurrentPrice)
SALES_ORDER(OrderID PK, CustomerID FK, OrderDate)
ORDER_LINE(OrderID PK part/FK, LineNumber PK part, ProductID FK, Quantity, UnitPriceAtOrder)
10 | EMPLOYEE MANAGEMENT
Each employee belongs to one department. Each department has one name and one office. Store those department facts separately from employee facts.
Dependencies:
EmployeeID → EmployeeName, DepartmentID
DepartmentID → DepartmentName, Office
A 3NF design under these rules:
DEPARTMENT(DepartmentID PK, DepartmentName, Office)
EMPLOYEE(EmployeeID PK, EmployeeName, DepartmentID FK)
11 | INVENTORY MANAGEMENT
A product can be stored in several warehouses. Each product–warehouse pair has one quantity on hand.
Dependencies:
ProductID → ProductName
WarehouseID → WarehouseName
(ProductID, WarehouseID) → QuantityOnHand
A 3NF design under these rules:
PRODUCT(ProductID PK, ProductName)
WAREHOUSE(WarehouseID PK, WarehouseName)
STOCK(ProductID PK part/FK, WarehouseID PK part/FK, QuantityOnHand)
12 | POLICE CRIME REPORTING
Each incident is recorded under one offence category and one reporting officer. Other officers may be assigned to it. These are role-specific links, not a list of names in the incident row.
Dependencies:
OffenceCode → OffenceDescription
OfficerID → OfficerName
IncidentID → OffenceCode, ReportedAt, LocationText, ReportingOfficerID
A 3NF design under these rules:
OFFENCE_TYPE(OffenceCode PK, OffenceDescription)
OFFICER(OfficerID PK, OfficerName)
INCIDENT(IncidentID PK, OffenceCode FK, ReportedAt, LocationText, ReportingOfficerID FK → OFFICER)
INCIDENT_OFFICER(IncidentID PK part/FK, OfficerID PK part/FK)
13 | CHECK YOUR DESIGN BEFORE YOU FINISH
- Every row has a valid key; every cell holds one value.
- Non-key fields depend on the whole of each candidate key.
- Non-key fields do not depend transitively on keys in these simple examples.
- Foreign keys refer to the intended parent tables.
- Many-to-many relationships have suitable junction tables.
- Repeated events, such as borrowing the same copy again, can be represented.
- The original facts can be reconstructed without invented combinations or missing links.
Test concrete events: add an unborrowed book, rename a teacher, change a mentor’s email, and remove one enrolment. If those changes force you to alter unrelated rows or lose unrelated facts, revisit the dependencies.
Normalizing a design is different from implementing constraints. Foreign keys, uniqueness rules and business constraints still need to be enforced in the database.