The shape of the lesson
By the end of the lesson, learners can
- From UNF to 1NF: A sixth-form college records which courses each student takes.
- Removing partial dependencies: The lab walks an orders example through UNF, 1NF, 2NF and 3NF.
- Is it in 3NF?: Exam questions often give you some tables and ask you to explain why they are, or are not, in 3NF.
- 1NF: remove repeating groups; every field atomic; choose the (often composite) primary key.
- 2NF: move attributes that depend on only part of a composite key into their own table.
- 3NF: move attributes that depend on another non-key attribute into their own table, leaving a foreign key.
- Write tables as NAME(Key, Attribute, ...) and say which attributes are foreign keys.
One picture every idea returns to
Normalisation organises data into tables so that each fact is stored once, removing redundancy and the inconsistencies it causes. First normal form (1NF): no repeating groups of attributes, every attribute holds a single (atomic) value, and a primary key identifies each tuple. Second normal form (2NF): in 1NF, and no partial dependencies: every non-key attribute depends on the whole of a composite primary key. Third normal form (3NF): in 2NF, and no non-key dependencies: no non-key attribute depends on another non-key attribute.
Forty-five minutes, phase by phase
| Time | Phase | What happens in the room | Grouping |
|---|---|---|---|
| 0 to 5 min | Hook: predict | Open with the question in the title, Nothing but the key.. A spreadsheet of students, tutors and courses looks fine until a tutor changes name and you have to edit forty rows. Put the figure on the board: it is the one picture every idea in the lesson returns to. | Whole class, then pairs |
| 5 to 13 min | Teach and check: From UNF to 1NF | A sixth-form college records which courses each student takes. Model the worked example Choosing the 1NF key on the board, one line at a time. | Whole class, then pairs |
| 13 to 21 min | Teach and check: Removing partial dependencies | The lab walks an orders example through UNF, 1NF, 2NF and 3NF. Project tab 5 of the lab (Normalisation step by step) and let a student drive it while the class predicts. Model the worked example Remove the non-key dependency on the board, one line at a time. Your turn: Write the full 3NF design for the college data. | Pairs |
| 21 to 30 min | Teach and check: Is it in 3NF? | Exam questions often give you some tables and ask you to explain why they are, or are not, in 3NF. Model the worked example A hotel table on the board, one line at a time. | Jigsaw |
| 30 to 36 min | Check | The four Stage 1 questions from the student lesson, answered on mini whiteboards or by show of fingers. Re-teach the one most get wrong. | Individual, then whole class |
| 36 to 43 min | Plenary | The exam-style questions from Stage 2, one at a time: thirty seconds alone, then a random call. The feedback lines in the key give the examiner's wording. | Individual, random call |
| 43 to 45 min | Exit and homework | Set the worksheet below (6 questions, 18 marks). Learners self-assess against the objectives before they leave. | Individual |
Protect the parts that carry the learning
The sequence carries about fifty minutes of material, so the lab is driven by one student while the class predicts rather than explored freely. Times are a guide.
Protected: the check at 30 minutes. It is the only individual read on whether the key idea landed, and it is not cut.
Cut first if the build overruns: the last check in Is it in 3NF?, never the check or the plenary.
In a 60 minute block: run worksheet questions 1 to 3 in class as a Rally Coach set and model both the hardest worked example and its check live.
Jigsaw
Split the content into expert pieces. Expert groups master one piece each, then regroup so every home team has one expert per piece, and each expert teaches their piece to the team.
The full structure, with its variations and the other twelve, is on the cooperative-learning page.
What to head off, and how
| Trap learners fall into | Teaching move that pre-empts it |
|---|---|
| 1NF is not finished just because each cell holds one value. | You must also state the new primary key, and in a table like this it is a composite key. |
| "It is not in 3NF because it has repeated data" earns little. | Name the dependency: say which non-key attribute depends on which other attribute. |
Normalisation to third normal form: worksheet
6 questions, 18 marks. Written in the style of the papers; no past paper question is reproduced.
State the conditions for a table to be in (a) first normal form, (b) second normal form, (c) third normal form.
The table BOOKING(BookingID, CustomerID, CustomerEmail, EventDate) has the primary key BookingID. Explain why it is not in 3NF.
A garage records repairs in one unnormalised table:
REPAIR(RepairID, RepairDate, CarReg, CarModel, OwnerID, OwnerName, PartID, PartName, PartPrice, Quantity)
Each repair is on one car and can use several parts. Each car has one owner. Produce a design in 3NF.
Using the 1NF table STUDENTCOURSE from this lesson, explain one problem caused by data that is not normalised.
PLAYER(PlayerID, MatchID, Goals, MatchDate, PlayerName) has the composite primary key PlayerID + MatchID. Identify every partial dependency and give the 2NF tables.
A table is in 2NF and has a single-attribute primary key. Explain why it may still not be in 3NF.
Answer key and marking notes
- (a) No repeating groups of attributes; all attributes atomic; a primary key identifies each tuple.
- (b) In 1NF and no partial dependencies: every non-key attribute depends on the whole primary key.
- (c) In 2NF and no non-key dependencies: no non-key attribute depends on another non-key attribute.
- CustomerEmail depends on CustomerID, not on BookingID.
- CustomerID is a non-key attribute, so this is a non-key (transitive) dependency.
- REPAIR(RepairID, RepairDate, CarReg).
- CAR(CarReg, CarModel, OwnerID).
- OWNER(OwnerID, OwnerName).
- PART(PartID, PartName, PartPrice).
- REPAIRPART(RepairID, PartID, Quantity), with foreign keys identified: CarReg in REPAIR, OwnerID in CAR, RepairID and PartID in REPAIRPART.
- Mr Ade's name is stored in every row for students S1 and S3 (redundancy).
- If his name changes, every one of those rows must be updated (update problem).
- If one row is missed, the data becomes inconsistent; similarly a new course cannot be added until a student enrols (insertion problem).
- PlayerName depends on PlayerID only; MatchDate depends on MatchID only.
- Goals depends on the whole key.
- 2NF: PLAYER(PlayerID, PlayerName), MATCH(MatchID, MatchDate), PERFORMANCE(PlayerID, MatchID, Goals).
- A single-attribute key rules out partial dependencies only.
- A non-key attribute may still depend on another non-key attribute, which breaks 3NF.
6 multiple-choice questions on this lesson
From the topic question bank. Assign them in the app for a marked score in your gradebook, or run the topic as a class game.
Which condition must a table in first normal form also meet to be in second normal form?
- AEvery non-key attribute depends on the whole of the primary key, with no partial dependencies
- BNo non-key attribute depends on another non-key attribute
- CEvery attribute holds a single value, with no repeating groups
- DEvery table has at least one foreign key linking it to another table
A club stores its members like this:
| MemberID | Name | Sports |
|---|---|---|
| C1 | Ravi | Tennis, Hockey |
| C2 | Mia | Netball |
| C3 | Tom | Hockey, Rugby, Tennis |
- AThe Sports field holds a list of values instead of a single atomic value
- BName depends on MemberID, which is the primary key
- CHockey and Tennis are stored more than once in different records
- DThe table has no composite primary key
COURSEWORK(StudentID, TaskID, Mark, TaskTitle) has the composite primary key StudentID + TaskID. Each task has one title. Which statement is correct?
- AIt is not in 2NF, because TaskTitle depends only on TaskID
- BIt is not in 2NF, because Mark depends only on StudentID
- CIt is not in 3NF, because Mark depends on TaskTitle
- DIt is in 3NF, because every attribute depends on a key field
BOOKING(BookingID, GuestID, GuestName, RoomNo, ArrivalDate) has the primary key BookingID. Why is it not in third normal form?
- AGuestName depends on GuestID, which is not the primary key
- BArrivalDate depends on only part of the primary key
- CRoomNo is a repeating group within each booking
- DBookingID is a single attribute, so it cannot be a primary key
A school records trips in one unnormalised table: TRIP(TripID, Destination, TeacherID, TeacherName, StudentID, StudentName, AmountPaid). Each trip is led by one teacher, a trip takes many students, and a student can go on many trips. AmountPaid is what one student paid for one trip. How many tables are in the 3NF design?
- A4
- B3
- C5
- D2
This table is in 1NF with composite key StudentID + CourseCode:
| StudentID | TutorID | TutorName | CourseCode | Grade |
|---|---|---|---|---|
| S1 | T1 | Mr Ade | CS | A |
| S1 | T1 | Mr Ade | MA | B |
| S2 | T2 | Ms Roy | CS | C |
| S3 | T1 | Mr Ade | MA | A |
- AThe fact that tutor T2 is Ms Roy is lost from the database
- BThe course CS is lost, because no student takes it any more
- CMr Ade's name becomes inconsistent across the remaining records
- DThe remaining records break referential integrity with TUTOR
Quiz key
2NF removes partial dependencies: a non-key attribute must depend on the whole composite key, not part of it. Removing non-key dependencies is the 3NF step. Atomic values with no repeating groups is the 1NF condition, and normal forms say nothing about every table needing a foreign key.
1NF requires every attribute to hold one atomic value with no repeating group; Sports holds a list for C1 and C3. Name depending on the primary key is exactly what should happen. A sport name appearing in several records is not itself a 1NF breach, and 1NF does not require a composite key.
TaskTitle is a fact about the task alone, so it depends on only part of the composite key: a partial dependency, which breaks 2NF. Mark depends on the student and the task together, so it is fully dependent. Mark does not depend on the title, and depending on part of a key is not enough for 3NF.
A guest's name is determined by GuestID, a non-key attribute: a non-key (transitive) dependency, which breaks 3NF; the fix is GUEST(GuestID, GuestName) with GuestID left in BOOKING as a foreign key. With a single-attribute key there can be no partial dependency. One room per booking is not a repeating group, and a single attribute is a perfectly good primary key.
The 3NF design is TRIP(TripID, Destination, TeacherID), TEACHER(TeacherID, TeacherName), STUDENT(StudentID, StudentName) and a link table such as BOOKING(TripID, StudentID, AmountPaid): 4 tables. Three tables would leave TeacherName depending on the non-key TeacherID or lose the link between trips and students. Five would split something that has no dependency to remove, and two cannot store the many-to-many relationship.
Ms Roy appears only in S2's record, so deleting it loses the tutor's details as well: a deletion problem caused by not normalising. CS is still taken by S1, so it is not lost. Mr Ade's rows are untouched, so nothing becomes inconsistent, and there is no TUTOR table in this design for referential integrity to apply to.
What to project
The student lesson embeds these tabs. Open them full screen on the projector; every slider has a drag handle on the stage.
Slides for this lesson
A projectable deck for every pack is on the way. Until then, present the student lesson full screen: its sections, figure and lab tabs follow the same order as the plan above.