EnglandComputer ScienceSyllabus dot point

How does a relational database organise data into tables, and what does normalising to third normal form achieve?

The relational database model: entities, attributes, primary and foreign keys, entity relationships, the difference between a flat file and a relational database, and normalisation to first, second and third normal form.

An OCR H446 answer on relational databases and normalisation: entities, attributes, primary and foreign keys, entity relationships, the difference between flat-file and relational databases, and how to normalise data to first, second and third normal form.

Generated by Claude Opus 4.814 min answerUpdated 2026-06-02

Reviewed by: AI editorial process; not yet individually human-reviewed

Have a quick question? Jump to the Q&A page

Quick answer

A relational database stores data in linked tables to avoid the redundancy of a single flat-file table. An entity is a thing data is held about (a table); an attribute is a property of it (a column); a record is one row. A primary key uniquely identifies each record. A foreign key is an attribute that is the primary key of another table, used to link the two and form relationships (one-to-one, one-to-many, many-to-many). A flat file holds everything in one table, causing redundancy and update, insertion and deletion anomalies. Normalisation organises data to remove these. First normal form (1NF) removes repeating groups so every field is atomic. Second normal form (2NF) is 1NF with no partial dependency on part of a composite key. Third normal form (3NF) is 2NF with no non-key attribute depending on another non-key attribute (no transitive dependency).

Jump to a section

What this dot point is asking
The answer
Examples in context
Try this

What this dot point is asking

OCR wants the building blocks of a relational database (entities, attributes, primary and foreign keys, relationships), the contrast with a flat file, and the ability to normalise data to first, second and third normal form. Expect a "problems of a flat file" discussion and a "normalise this data" task.

The answer

Entities, attributes and keys

Relationships and flat files

Key fact

Tables are linked by relationships: one-to-one (each record in A matches exactly one in B), one-to-many (one record in A relates to many in B, the commonest, for example one customer to many orders), and many-to-many (records in A relate to many in B and vice versa, for example students and clubs), which is resolved by a link (junction) table holding two foreign keys. A flat-file database stores everything in a single table, which forces data to be repeated across rows; this causes redundancy and the update anomaly (a change must be made in many places, risking inconsistency), the insertion anomaly (some data cannot be added without unrelated data) and the deletion anomaly (deleting a row loses unrelated data). A relational design splits the data into linked tables to remove these.

Normalisation

Normalise an orders flat file to 3NF

A flat table holds: OrderID, OrderDate, CustomerID, CustomerName, CustomerCity, ProductID, ProductName, Quantity, with one row per product on an order. Normalise it to 3NF.

step 1: Reach 1NF (atomic, no repeating groups)

Each row already holds single values, but an order with several products repeats the order and customer data on every product line. Give each row a key: the combination (OrderID, ProductID) uniquely identifies a line, so that is the composite primary key.

step 2: Reach 2NF (remove partial dependencies)

OrderDate and the customer details depend only on OrderID (part of the key), and ProductName depends only on ProductID. Split these out: an Order table (OrderID, OrderDate, CustomerID) and a Product table (ProductID, ProductName). The line table keeps (OrderID, ProductID, Quantity).

step 3: Reach 3NF (remove transitive dependencies)

In the Order table, CustomerName and CustomerCity depend on CustomerID, a non-key attribute, not directly on OrderID. Move them to a Customer table (CustomerID, CustomerName, CustomerCity), leaving CustomerID as a foreign key in Order.

step 4: State the final design

Customer(CustomerID, CustomerName, CustomerCity); Order(OrderID, OrderDate, CustomerID); Product(ProductID, ProductName); OrderLine(OrderID, ProductID, Quantity). Each fact is stored once and tables link by keys, removing the redundancy and anomalies.

Examples in context

A school management system separates Student, Class and Teacher tables linked by keys, so a teacher's name is stored once. An online shop normalises customers, orders, order lines and products, so changing a product's name updates one row. Normalisation underpins referential integrity and efficient SQL joins. OCR pairs this with SQL (querying the linked tables) and with transaction processing (keeping the normalised data consistent under concurrent access).

Try this

Q1. State two problems caused by storing data in a single flat-file table. [2 marks]

Cue. Any two of: data redundancy, update anomaly, insertion anomaly, deletion anomaly.

Q2. Explain how a many-to-many relationship is implemented in a relational database. [2 marks]

Cue. With a link (junction) table containing the primary keys of both entities as foreign keys, often forming a composite primary key.

Q3. State the condition a table must meet to be in third normal form, beyond being in 2NF. [1 mark]

Cue. No non-key attribute depends on another non-key attribute (no transitive dependency).

Exam-style practice questions

Practice questions written in the style of OCR exam questions on this dot point, with worked answer explainers. The year tag is the paper they imitate, not the source.

OCR 20208 marksA flat-file database stores, in one table, each student's details repeated alongside every club they belong to. Explain the problems this causes and how normalisation to third normal form would resolve them.

Show worked answer →

Levels-of-response question; reward problems plus the normalised design.

Problems with the flat file: data redundancy (a student's details are repeated for each club), which wastes space and risks update anomalies (changing an address means editing many rows, and missing one creates inconsistency), insertion anomalies (cannot record a club with no members easily) and deletion anomalies (removing a student's last club loses the student).

Normalisation to 3NF: split into separate tables, for example Student (StudentID primary key, name, address), Club (ClubID primary key, club name) and a Membership table (StudentID and ClubID as foreign keys, together the composite key) to resolve the many-to-many relationship. Each non-key attribute now depends only on its table's whole key and on nothing but the key, removing the repetition and the anomalies.

Top marks need at least two named anomalies and a correct three-table design with the link table resolving the many-to-many.

OCR 20224 marksDefine the terms primary key and foreign key, and explain how they are used to link two tables in a relational database.

Show worked answer →

Primary key (1): an attribute (or set of attributes) that uniquely identifies each record in a table, with no duplicates and no nulls.

Foreign key (1): an attribute in one table that is the primary key of another table, used to create a relationship between them.

Linking (2): the foreign key in the "many" table holds the value of the primary key in the "one" table, so each record can be matched to its related record (for example Order.CustomerID is a foreign key referencing Customer.CustomerID, linking each order to its customer). Markers reward "uniquely identifies", "primary key of another table", and a correct description of the link.

Related dot points

Sources & how we know this

OCR AS and A Level Computer Science (H046, H446) specification — OCR (2015)