Databases, SQL and Normalisation
A relational database organises data into linked tables (entities) instead of one flat file, which avoids the data redundancy and update, insertion and deletion anomalies that a single unnormalised table causes. Each table has a primary key that uniquely identifies each row, and a foreign key in one table can reference the primary key of another to model a relationship (one-to-many or, via a linking/junction table, many-to-many). Normalisation is the step-by-step process of designing this table structure correctly: first normal form (1NF) removes repeating groups, second normal form (2NF) removes partial dependencies (a non-key attribute depending on only part of a composite key), and third normal form (3NF) removes transitive dependencies (a non-key attribute depending on another non-key attribute rather than on the key itself).
Before you start
Make sure you're comfortable with these topics first:
Method
- Before normalising anything, identify every entity (real-world thing being stored, e.g. an owner, a pet) that the data describes, and list the attributes that belong to each one.
- Spot an unnormalised table (UNF) by looking for a repeating group: a single row that would need to store more than one value for the same attribute (e.g. more than one appointment for the same owner).
- Reach 1NF by removing the repeating group so every cell holds a single (atomic) value, giving the resulting flat table a primary key, which may need to be a composite key made of more than one column.
- Reach 2NF by checking every non-key attribute against a composite primary key: if an attribute depends on only part of that key (a partial dependency), move it into its own table keyed on just that part.
- Reach 3NF by checking every remaining non-key attribute: if it depends on another non-key attribute rather than directly on the primary key (a transitive dependency), move it into its own table.
- After splitting tables apart, add a foreign key in the table that used to hold the removed attribute(s), so the split-out data can still be linked back together.
- Draw or describe an entity relationship (ER) diagram from the final tables, showing each relationship's degree (one-to-one, one-to-many, many-to-many) and using a linking table wherever two entities have a many-to-many relationship.
Worked example
A vet clinic stores its records in a single unnormalised table, CLINIC_RECORD(OwnerID, OwnerName, OwnerPhone, PetName, Species, ApptDate, VetName, Treatment, Cost), where a single owner's row contains a repeating group of appointment details, one for every appointment across all of their pets. Every appointment for a given pet happens on a different date, and the Cost of an appointment is always the fixed price for its Treatment (e.g. every 'Vaccination' costs 45). Take this table from unnormalised form (UNF) to third normal form (3NF).
- Remove the repeating group of appointment details to reach first normal form (1NF): give each appointment its own row in a single flat table, CLINIC_1NF(OwnerID, OwnerName, OwnerPhone, PetName, Species, ApptDate, VetName, Treatment, Cost), with composite primary key (OwnerID, PetName, ApptDate).
- Identify partial dependencies against this composite key: OwnerID alone determines OwnerName and OwnerPhone, and (OwnerID, PetName) alone determines Species, so both depend on only part of the key rather than the whole (OwnerID, PetName, ApptDate) key.
- Remove these partial dependencies to reach second normal form (2NF), splitting the table into OWNER(OwnerID, OwnerName, OwnerPhone), PET(OwnerID, PetName, Species) and APPOINTMENT(OwnerID, PetName, ApptDate, VetName, Treatment, Cost).
- Identify a transitive dependency inside APPOINTMENT: Cost does not depend on the appointment itself, it depends on Treatment (a non-key attribute), since every appointment with the same Treatment always has the same Cost.
- Remove this transitive dependency to reach third normal form (3NF), moving Cost into its own table, TREATMENT(TreatmentName, Cost), and replacing Treatment and Cost in APPOINTMENT with a foreign key, TreatmentName.
- Final answer: the 3NF schema is OWNER(OwnerID, OwnerName, OwnerPhone), PET(OwnerID, PetName, Species), TREATMENT(TreatmentName, Cost) and APPOINTMENT(OwnerID, PetName, ApptDate, VetName, TreatmentName), where PET's OwnerID and APPOINTMENT's (OwnerID, PetName) and TreatmentName are all foreign keys linking back to the tables above.
Practice questions
Type your answer and press Check to be marked straight away, or reveal the answer and mark yourself.
Q1State one problem, other than wasted storage space, that data redundancy can cause in an unnormalised database table.Show answer
Answer: An update anomaly: if the same fact (e.g. an owner's phone number) is stored in more than one row, updating it in only some of those rows leaves the data inconsistent.
Q2In the table PET(OwnerID, PetName, Species), state why (OwnerID, PetName) is used as a composite primary key rather than PetName alone.Show answer
Answer: Because PetName alone is not guaranteed to be unique across every owner (two different owners could both have a pet called 'Biscuit'), whereas the combination of OwnerID and PetName is unique for each pet.
Q3Define the term 'foreign key'.Show answer
Answer: An attribute (or set of attributes) in one table that refers to the primary key of another table, used to create a link (relationship) between the two tables.
Q4A vet clinic's APPOINTMENT table includes both Treatment and Cost, where every appointment with the same Treatment always has the same Cost. State the type of dependency this represents, and name the normal form reached by removing it.Show answer
Answer: A transitive dependency (Cost depends on Treatment, a non-key attribute, rather than directly on the appointment's own key); removing it reaches third normal form (3NF).
Q5State the relationship (one-to-one, one-to-many or many-to-many) between OWNER and PET in a vet clinic database, where one owner can register several pets but each pet belongs to only one owner.Show answer
Answer: One-to-many (one owner can have many pets, but each pet has only one owner).
Q6A clinic wants to record that a single appointment can involve more than one vet, and a vet can be involved in many appointments. State the relationship type this describes, and state what a database designer would normally add to model it.Show answer
Answer: Many-to-many; the designer would normally add a linking (junction) table, e.g. APPOINTMENT_VET(ApptID, VetID), containing a foreign key to each of the two tables.
Q7Explain what 'atomic' means when describing the values allowed in a table that meets first normal form (1NF).Show answer
Answer: Each cell in the table must hold a single, indivisible value; it must not hold a list, a set of multiple values, or a repeating group of values.
Q8State one advantage a database management system (DBMS) has over a flat-file system for a growing veterinary clinic with several branches.Show answer
Answer: A DBMS lets data be organised into properly linked, non-redundant tables (via normalisation) and provides built-in tools for querying, validating and controlling access to the data, rather than each branch managing its own separate, potentially inconsistent flat file.
Exam-style questions
Written in the style of a A Level Computer Science exam paper, with a full mark scheme.
Define the term 'partial dependency', and state which normal form is reached by removing all partial dependencies from a table.
Show mark scheme
Tick each line you got. Your score builds from the marks on the scheme.
Nothing ticked yet - 2 available
A library stores its data in a single unnormalised table, LOAN_RECORD(MemberID, MemberName, MemberEmail, ISBN, BookTitle, Genre, LoanDate, ReturnDate), where a single member's row contains a repeating group of loan details, one for every book they have ever borrowed. State why this table is not in first normal form (1NF), then show the table once it has been converted to 1NF, stating a suitable primary key for the new table (you may assume a member never borrows the same ISBN on the same LoanDate).
Show mark scheme
Tick each line you got. Your score builds from the marks on the scheme.
Nothing ticked yet - 4 available
A veterinary clinic currently stores all of its data in one large, unnormalised spreadsheet, with a repeating group of appointment details in every owner's row. The practice manager is considering paying a developer to redesign this as a normalised relational database, but is concerned about the cost and the extra complexity of using several linked tables instead of one. Evaluate whether the clinic should normalise its data to third normal form (3NF).
Show mark scheme
Tick each line you got. Your score builds from the marks on the scheme.
Nothing ticked yet - 9 available
See real A Level Computer Science past-paper questions, with official mark schemes →
Free printable worksheet
Want more practice on paper? Download the databases, sql and normalisation worksheet pack - 13 pages of exam-style questions with a full mark scheme. One email opens every download in this browser for 14 days - no account, no card. Print it for personal and classroom use.
Next topics
Not quite what you needed?
Tell us what is missing on databases, sql and normalisation, or which topic to write up next. Every request is read, and we reply to every one.
Build a full practice pack.
This topic is one of hundreds in the library - pick the ones a student needs and generate a printable PDF in minutes.