Database Design Concepts

Why databases are split into multiple related tables, and the rules that decide where each field belongs.

Primary key: A field that uniquely identifies each record. Never null, never duplicated.
Foreign key: A field that references another table's primary key — this is what creates the relationship.
Referential integrity: A foreign key must point at a record that actually exists.
Data redundancy: The same fact stored in more than one place — the problem normalisation solves.

Relationship	Meaning	Example
One-to-one	One record links to exactly one other	Learner ↔ LearnerPhoto
One-to-many	One record links to many others	One Subject ↔ many Marks
Many-to-many	Needs a junction table to resolve	Learners ↔ Subjects, via Enrolments

Redundancy causes three classic anomalies, and naming them is worth marks: an insert anomaly (you can't add a subject until a learner takes it), an update anomaly (change a teacher's name in one row and the others disagree), and a delete anomaly (removing the last learner loses the subject entirely).

First normal form (1NF): no repeating groups; every field holds a single atomic value.
Second normal form (2NF): 1NF, plus every non-key field depends on the WHOLE primary key.
Third normal form (3NF): 2NF, plus no non-key field depends on another non-key field.

💡 Exam Tip

A field you can calculate from others (like Age from DateOfBirth, or Total from Price × Quantity) should generally NOT be stored — that's redundancy, and it's a favourite exam question.