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. A relational database stores each fact once, in linked tables, and the database itself checks that the links stay valid.
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.
Fig. 1: MemberID is the primary key of MEMBER and a foreign key in LOAN
Section 01
The trouble with files.
Before databases, each department wrote its own program with its own data files. The library file and the exams file both held every student's name, form group and address. That approach has well-known limitations, and exam questions ask you to describe them precisely.
File-based limitation
What it means
How a relational database addresses it
Data redundancy
The same data is stored in several files, wasting storage.
Each fact is stored once, in one table, and linked by keys.
Data inconsistency
A change is made in one file but not the others, so the copies disagree.
With one copy, an update is made once and every user sees it.
Data dependency
Programs are written for one file structure; changing the structure means rewriting the programs.
The DBMS separates the data from the programs (program-data independence).
Poor integrity
Nothing checks that related data in different files still matches.
Referential integrity and validation rules are enforced by the DBMS.
Weak security and sharing
Access is all-or-nothing for a whole file; sharing files between users is difficult.
Access rights can be set per user or group; many users can share the data at once.
Hard to query
A new question needs a new program to be written.
Ad hoc queries can be written in SQL.
Worked example Redundancy leads to inconsistency
A student moves house. The office updates her address in the admissions file, but the library file still holds the old one. Name and explain the two problems shown.
The address is stored in two files: this is data redundancy.
Only one copy was updated, so the two files now give different addresses: this is data inconsistency.
In a relational database the address is stored once, in the STUDENT table, so there is only one copy to update.
Answer: Data redundancy causes data inconsistency
Examiner trap
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.
Section 02
Tables, tuples and attributes.
In the lab, the STUDENT, CLUB and MEMBERSHIP tables are shown with their primary keys underlined. Look at how each table holds data about one kind of thing, and how the keys repeat between tables.
Term
Meaning
Library example
Entity
A person, object, event or idea about which data is stored
a member, a book, a loan
Table (relation)
Holds the data about one entity type, in rows and columns
MEMBER
Record (tuple)
One row: the data about one instance of the entity
M02, Ben, Okafor, 12B
Field (attribute)
One column: one item of data about every instance
Surname
Primary key
An attribute (or combination) that uniquely identifies each tuple
MemberID
Candidate key
Any attribute (or minimal combination) that could be the primary key, because it is unique for every tuple
MemberID, or a school email address
Secondary key
A candidate key not chosen as the primary key; it is often indexed so that searches on it are fast
Email
Foreign key
An attribute in one table that is the primary key of another table, creating the link
MemberID in LOAN
Worked example Choosing keys
A table STUDENT(StudentID, Email, Surname, DateOfBirth) holds one record per student. Every student has their own school email address. Identify the candidate keys and suggest the primary key.
Surname and DateOfBirth can repeat (two students can share them), so they cannot identify a tuple.
StudentID is unique for every student; so is Email. Both are candidate keys.
StudentID is short and never changes, so it is the better primary key. Email is then a secondary key.
Answer: Candidate keys StudentID and Email; primary key StudentID
Your turn
In the table LOAN(LoanID, BookID, MemberID, LoanDate, Returned), identify the primary key and the two foreign keys.
Primary key LoanID; foreign keys BookID (links to BOOK) and MemberID (links to MEMBER)
Stage 1 · Learn
Check what the sim just showed you
Four quick checks. Each correct answer earns XP and records this skill on your profile.
Quick check+10 XP
What is meant by data redundancy?
Quick check+10 XP
Which term means the same as a tuple?
Quick check+10 XP
In the lab, why does DELETE FROM Club WHERE ClubID = 'C2' fail?
Quick check+10 XP
What is a candidate key?
Section 03
Keys that link.
Pick the lab example DELETE FROM Club WHERE ClubID = 'C2' and run it. The DBMS refuses, because memberships still point at club C2. Then try inserting a membership for a student who does not exist.
Relationship: a foreign key creates a link between two tables. One member can have many loans, so MemberID appears once in MEMBER but many times in LOAN.
Referential integrity: every foreign key value must match an existing primary key value in the linked table. The DBMS rejects a LOAN record with MemberID M09 if there is no member M09, and it will not delete member M01 while loans still refer to M01.
Indexing: an index is a separate, sorted list of key values with pointers to the records. The DBMS searches the small index instead of every record, so searching and sorting on an indexed field (the primary key or a secondary key) are much faster.
Worked example Will the DBMS allow it?
Using the MEMBER and LOAN tables in Fig. 1 (members M01 to M04), decide whether each change keeps referential integrity: (a) add loan L07 for book B102 and member M03; (b) add loan L08 for member M06; (c) delete member M02.
(a) M03 exists in MEMBER, so the new foreign key value matches a primary key: allowed.
(b) There is no M06 in MEMBER, so the loan would point at nothing: rejected.
(c) Loan L02 still has MemberID M02, so deleting M02 would leave an orphan record: rejected (unless the loan is deleted first).
Answer: (a) allowed; (b) and (c) rejected
Examiner trap
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.
Stage 2 · Exam
Exam-style questions
Unlocks once the four checks above are done. Worth more XP, written to AS Paper 1 and Paper 2 standard.
Finish the four checks above to unlock the exam questions
Exam style+20 XP
Which feature of a relational database removes the problem of data dependency?
Exam style+20 XP
LOAN(LoanID, BookID, MemberID, LoanDate). Which attribute is a foreign key?
Exam style+20 XP
Which of these would break referential integrity?
Exam style+20 XP
Why is the primary key field indexed?
Skill unlocked
Relational databases and keys, mastered.
This skill is now recorded on your profile. Keep the chain going.