Relational databases
A relational database stores data in tables (relations). Each row is a record about one entity; each column is a field representing one attribute. Tables are linked by keys to reduce duplication and keep data consistent.
📖Definition— Key terminology
- Table (relation) — a 2D grid storing one type of entity (e.g. Students, Books).
- Record (row, tuple) — one entity instance.
- Field (column, attribute) — one property of the entity.
- Primary key — a field (or combination) that uniquely identifies each record.
- Foreign key — a field in one table that references the primary key in another, creating a link.
- Composite key — a primary key made of two or more fields together.
✦Worked example— Example: a school database
Students
StudentID (PK) | Name | DOB | TutorGroup
1001 | Aisha | 2009-03-12 | 10A
1002 | Ben | 2009-07-04 | 10B
1003 | Chloe | 2009-11-22 | 10A
Subjects
SubjectID (PK) | Name
S01 | Maths
S02 | Computer Science
S03 | English
Enrolments
StudentID (FK) | SubjectID (FK) | Grade
1001 | S01 | 7
1001 | S02 | 8
1002 | S01 | 6
In Enrolments:
- The primary key is the composite
(StudentID, SubjectID). StudentIDis a foreign key referring toStudents.StudentID.SubjectIDis a foreign key referring toSubjects.SubjectID.
This way, each student's name is stored only once (in Students), not repeated in every enrolment.
Why "relational"?
Tables are linked through keys, forming relationships. The three common cardinalities:
- One-to-one (1:1) — each row in A pairs with at most one row in B (and vice versa). Rare; often combined into one table.
- One-to-many (1:N) — one row in A pairs with many in B (and each B has one A). Most common: one teacher → many lessons.
- Many-to-many (N:M) — many in A pair with many in B. Example: students ↔ subjects. Implemented with a linking table (Enrolments above).
Entity-relationship (ER) diagrams
A graphical view of the database structure:
- Boxes for entities (tables).
- Lines for relationships, with crow's-foot notation showing cardinality (single line = "one"; crow's foot = "many").
Example:
[ Students ] ──< [ Enrolments ] >── [ Subjects ]
1:N 1:N
Many students enrol on many subjects; the linking table resolves the N:M relationship into two 1:N relationships.
Properties of a good primary key
- Unique — no two records share the same value.
- Not null — every record has a value.
- Stable — value rarely (ideally never) changes.
- Compact — short integers preferred to long strings.
Auto-incrementing integer IDs (e.g. 1, 2, 3) are common because they meet all these properties without involving real-world data that might change.
Referential integrity
A foreign key must point to an existing row in the referenced table — you can't enrol a student who doesn't exist. The DBMS enforces this. Common rules on delete/update:
- Cascade: deleting a student deletes their enrolments.
- Restrict: prevent deletion if dependent rows exist.
- Set null: foreign key becomes NULL.
Why split into tables?
If everything was in one giant table, you'd have:
- Redundancy — student name repeated in every enrolment.
- Inconsistency risk — typing the name differently in different rows.
- Update anomalies — change a student's name in one row, miss another.
Splitting into related tables (normalisation) eliminates these issues.
✦Worked example— Worked example — design a database
Design tables for a small library: books, members and loans.
Books(BookID PK, Title, Author, ISBN)
Members(MemberID PK, Name, JoinDate)
Loans(LoanID PK, BookID FK, MemberID FK, LoanDate, ReturnDate)
Primary keys identify each record uniquely. Foreign keys in Loans link to Books and Members. ER diagram: Members 1:N Loans N:1 Books.
⚠Common mistakes— Pitfalls
- Repeating data instead of using foreign keys. Defeats the point of a relational database.
- Using non-unique fields as primary keys. Two students might share a name.
- Forgetting the linking table for N:M. You can't have a "many-to-many" line directly without a join table.
- Confusing primary and foreign keys. PK identifies; FK references another table's PK.
- Ignoring referential integrity. Allowing orphan foreign keys breaks the model.
➜Try this— Quick check
In a database with Customers, Orders and Products:
CustomerIDin Customers is a primary key.CustomerIDin Orders is a foreign key.- An OrderItems linking table resolves the many-to-many between Orders and Products.
AI-generated · claude-opus-4-7 · v3-deep-computer-science