The shape of the lesson
By the end of the lesson, learners can
- The trouble with files: Before databases, each department wrote its own program with its own data files.
- Tables, tuples and attributes: In the lab, the STUDENT, CLUB and MEMBERSHIP tables are shown with their primary keys underlined.
- Keys that link: Pick the lab example DELETE FROM Club WHERE ClubID = 'C2' and run it.
- File-based problems: redundancy, inconsistency, data dependency, poor integrity, security and sharing.
- Keys: primary (chosen, unique), candidate (could be primary), secondary (candidate not chosen, indexed), foreign (links to another table's primary key).
- Referential integrity: every foreign key value matches an existing primary key value.
- Use the scenario: name the actual tables and fields in your answer.
One picture every idea returns to
In a file-based approach each application keeps its own files, which leads to data redundancy (the same data stored many times), data inconsistency (copies that disagree) and data dependency (programs tied to the file structure). A relational database stores data about each entity in its own table (relation) of records (tuples) and fields (attributes). Each table has a primary key that uniquely identifies every record, and tables are linked by foreign keys. Referential integrity means every foreign key value must match an existing primary key value.
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, Store each fact once.. A school that keeps a separate file for the library, the exams office and the sports department soon has three different addresses for the same student. 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: The trouble with files | Before databases, each department wrote its own program with its own data files. Model the worked example Redundancy leads to inconsistency on the board, one line at a time. | Whole class, then pairs |
| 13 to 21 min | Teach and check: Tables, tuples and attributes | In the lab, the STUDENT, CLUB and MEMBERSHIP tables are shown with their primary keys underlined. Project tab 4 of the lab (Relational database: keys and links) and let a student drive it while the class predicts. Model the worked example Choosing keys on the board, one line at a time. Your turn: In the table LOAN(LoanID, BookID, MemberID, LoanDate, Returned), identify the primary key and the two foreign keys. | Pairs |
| 21 to 30 min | Teach and check: Keys that link | Pick the lab example DELETE FROM Club WHERE ClubID = 'C2' and run it. Project tab 4 of the lab (Referential integrity in action) and let a student drive it while the class predicts. Model the worked example Will the DBMS allow it? on the board, one line at a time. | Think-Pair-Share |
| 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, 19 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 Keys that link, 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.
Think-Pair-Share
Pose the question, give thirty seconds of silent thinking, then a minute to compare with a partner before a few pairs share with the class. The silent minute is the part that cannot be cut.
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 |
|---|---|
| Redundancy and inconsistency are not the same thing. | Redundancy is the duplication; inconsistency is the result when duplicated copies are not all updated. Name the right one for the marks. |
| Referential integrity is about foreign keys matching primary keys. | It is not the same as validation (checking a value is sensible) or the uniqueness of a primary key. |
Relational databases and keys: worksheet
6 questions, 19 marks. Written in the style of the papers; no past paper question is reproduced.
A sports centre stores member details in a separate file for each activity: swimming, tennis and gym. Explain two limitations of this file-based approach.
Define the terms (a) tuple, (b) attribute, (c) primary key, (d) foreign key.
A table holds one record per car: CAR(RegNumber, ChassisNumber, Make, Colour, OwnerID). Every car has a unique registration number and a unique chassis number. Identify the candidate keys, suggest a primary key and state what OwnerID is likely to be.
Explain what is meant by referential integrity and give an example of a change that the DBMS would reject for the MEMBER and LOAN tables in this lesson.
Explain how indexing a field such as Surname helps the users of a database.
A student says: "A relational database avoids redundancy, so no data value is ever stored twice." Explain why this is not quite correct.
Answer key and marking notes
- Data redundancy: a member's details are stored in several files, wasting storage.
- This can lead to data inconsistency: if a member's phone number changes in one file only, the files disagree.
- Data dependency: each program is written for its own file structure, so changing a file structure means changing the programs.
- Any two limitations, each identified and explained (2 marks each).
- (a) A row in a table: the data about one instance of an entity.
- (b) A column in a table: one property of the entity.
- (c) An attribute (or combination) that uniquely identifies each tuple in a table.
- (d) An attribute in one table that is the primary key in another table, used to link the tables.
- Candidate keys: RegNumber and ChassisNumber (both unique for every car).
- Primary key: RegNumber (or ChassisNumber, with a reason); the other is a secondary key.
- OwnerID is a foreign key linking to the primary key of an OWNER table.
- Every foreign key value must match an existing primary key value in the linked table.
- Example: adding a LOAN record with MemberID M09 when there is no member M09.
- Or: deleting member M01 from MEMBER while loans still refer to M01.
- An index holds the Surname values in order with a pointer to each record.
- Searches and sorts on Surname use the index instead of reading every record, so they are much faster.
- Foreign keys repeat values on purpose: MemberID M01 appears in MEMBER and also in LOAN.
- This is controlled redundancy, needed to link the tables.
- What is removed is the uncontrolled duplication of other data, such as a member's name and form group being stored with every loan.
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.
A college stores each student's mobile number in both the admissions file and the sports file. A student changes number and only the sports file is updated. Which problem of a file-based approach does this show?
- AData inconsistency: the two copies of the number now disagree
- BData dependency: the programs are tied to the structure of each file
- CData integrity: the new number fails a validation check
- DData security: the wrong users can read the mobile number
What is a secondary key?
- AA candidate key that was not chosen as the primary key, often indexed for fast searching
- BA field in one table that is the primary key of another table
- CA second field added to the primary key to make a composite key
- DA field that holds a backup copy of the primary key value
A table holds one record per pupil:
| PupilNo | Surname | Form | DateOfBirth | |
|---|---|---|---|---|
| 1041 | akhan@sch.org | Khan | 10B | 2011-03-14 |
| 1042 | jlee@sch.org | Lee | 10A | 2011-07-02 |
| 1043 | rkhan@sch.org | Khan | 10A | 2011-03-14 |
- APupilNo and Email
- BPupilNo only
- CSurname and DateOfBirth
- DPupilNo, Email and Form
The library database enforces referential integrity on these tables (MemberID in LOAN is a foreign key).
| MEMBER | |||
|---|---|---|---|
| MemberID | FirstName | Surname | FormGroup |
| M01 | Aisha | Khan | 12A |
| M02 | Ben | Okafor | 12B |
| M03 | Chloe | Wang | 12A |
| M04 | Dev | Patel | 13C |
| LOAN | ||||
|---|---|---|---|---|
| LoanID | BookID | MemberID | LoanDate | Returned |
| L01 | B101 | M01 | 2026-09-01 | TRUE |
| L02 | B103 | M02 | 2026-09-02 | FALSE |
| L03 | B102 | M01 | 2026-09-03 | FALSE |
| L04 | B105 | M03 | 2026-09-03 | TRUE |
| L05 | B101 | M04 | 2026-09-05 | FALSE |
| L06 | B104 | M01 | 2026-09-06 | FALSE |
- AInserting loan L07 of book B102 for member M05
- BInserting loan L07 of book B102 for member M03
- CInserting a new member M05, Eli Brown, in form 12B
- DUpdating loan L03 so that Returned is TRUE
A developer suggests indexing every field in a large LOAN table, since an index makes searching faster. Which is the best reason not to do this?
- AEvery index takes extra storage and must be updated whenever a record is inserted, changed or deleted
- BA table is only allowed to have one index, which must be on the primary key
- CIndexing a field stops it from holding duplicate values, so foreign keys could not repeat
- DAn index on a non-key field would break referential integrity between the tables
In a file-based system, the field that stores a customer's postcode is lengthened from 7 to 8 characters. Every program that reads the customer file has to be edited and recompiled. Which limitation does this show, and how does a relational database with a DBMS address it?
- AData dependency; programs access data through the DBMS, so the structure can change without rewriting them
- BData redundancy; the postcode is stored once, so only one table has to be changed
- CData inconsistency; referential integrity makes sure every copy of the postcode matches
- DPoor security; access rights stop programs from changing the structure of the file
Quiz key
Storing the number twice is redundancy; the result of updating only one copy is inconsistency, because the two files now give different numbers. Data dependency is about programs having to be rewritten when a file structure changes. Nothing here suggests the new number is invalid or that unauthorised people can read it.
Every candidate key is unique for each tuple; one is chosen as the primary key and the others are secondary (alternate) keys, often indexed so searches on them are fast. A field that links to another table's primary key is a foreign key. Part of a composite key is not called a secondary key, and keys are not backup copies.
PupilNo and Email are both unique for every pupil, so each could be the primary key. Pupils 1041 and 1043 share both the surname Khan and the date of birth 2011-03-14, so even that pair cannot identify a tuple, and many pupils share a form. Saying PupilNo only misses Email, which is also unique.
There is no member M05, so a loan with MemberID M05 would point at nothing and is rejected. M03 exists, so a loan for M03 is accepted. Adding member M05 creates a new primary key value and refers to nothing, so it is allowed. Changing Returned does not touch any key, so referential integrity is unaffected.
Each index is a separate sorted list with pointers, so it uses storage and must be maintained on every insert, update and delete, which slows data entry; only fields that are searched or sorted often are worth indexing. Tables may have several indexes, including on secondary keys. An index does not have to be unique, and it has no effect on whether foreign keys match primary keys.
Programs that are written for one exact file layout show data dependency; the DBMS gives program-data independence because programs ask the DBMS for fields by name and the structure is held in the data dictionary. Redundancy and inconsistency concern duplicated data values, not program changes. Referential integrity is about foreign keys, and access rights control users, not the effect of a structure change.
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.