Skip to topic
    ← Back to course topics

    Database design and normalisation techniques — AQA A-Level Computer Science

    Database design and normalisation involves structuring relational databases systematically to minimise data redundancy and avoid insertion, update, and deletion anomalies. By progressing relations through First (1NF), Second (2NF), and Third Normal Form (3NF), data integrity is preserved using primary and foreign keys.

    Database design and normalisation techniques explained

    Third normal form (3NF) requires a relation to be in second normal form and to have no transitive dependencies: no non-key attribute may depend on another non-key attribute.

    Read the full explanation

    First check 2NF, meaning no non-key attribute depends on only part of a composite key. Then look for a non-key attribute that determines another non-key attribute. For example, in Student(StudentID, StudentName, TutorID, TutorRoom) the key is StudentID. TutorID is a non-key attribute, and TutorRoom depends on TutorID, so TutorRoom depends on the key only through TutorID: a transitive dependency. Split it into Student(StudentID, StudentName, TutorID) and Tutor(TutorID, TutorRoom), keeping TutorID in Student as a foreign key. Each relation now has every non-key attribute determined by its own whole primary key alone.

    Your focus

    1. Normalise relations to third normal form.

    Which command word is this?

    Set out the causes, reasons, or mechanisms clearly using precise database terminology (e.g. state why a partial dependency violates 2NF and identify which specific attribute depends on which sub-key).

    Database design and normalisation techniques exam tips

    Topic Overview

    Database design and normalisation is a mathematical approach to structuring relational tables to eliminate data redundancy and prevent data modification anomalies. In AQA A-Level Computer Science, students must understand Entity-Relationship (E-R) modelling and progress relations through First, Second, and Third Normal Form.

    Mastering normalisation ensures efficient data storage, safeguards referential integrity, and forms the bedrock for effective database administration. This knowledge links directly to relational algebra, SQL querying, and Big Data concepts across the specification.

    Key Concepts
    • →Entity-Relationship (E-R) Modelling: Representing one-to-one (1:1), one-to-many (1:M), and many-to-many (M:N) real-world relationships, including resolving M:N links with composite link entities.
    • →First Normal Form (1NF): A relation containing no repeating groups or composite attributes, with each field holding an atomic value and uniquely identified by a primary key.
    • →Second Normal Form (2NF): A relation in 1NF where all non-key attributes are fully functionally dependent on the entire primary key, eliminating partial key dependencies.
    • →Third Normal Form (3NF): A relation in 2NF containing no transitive dependencies, ensuring all non-key attributes are dependent on 'the key, the whole key, and nothing but the key'.
    • →Referential Integrity: A relational constraint enforcing that a foreign key value must match an existing primary key value in the related table or be null.
    Marking Points
    • Checks that the relation is already in second normal form before looking for transitive dependencies.
    • Identifies a non-key attribute that determines another non-key attribute, such as TutorID determining TutorRoom.
    • Splits the relation so the dependent attribute moves into a new relation keyed by the determining attribute.
    • Retains the determining attribute in the original relation as a foreign key.
    • Confirms that each resulting relation has every non-key attribute depending on its own whole primary key.
    Examiner Tips
    • 💡Write the functional dependency you are removing with an arrow, such as TutorID → TutorRoom.
    • 💡State the table names and primary keys of each resulting relation to earn credit for the final structure.
    • 💡Confirm 2NF first in your working; a 3NF answer that skips this step leaves the reasoning incomplete.
    • 💡Use an example where the determining attribute is genuinely non-key, not part of a composite key.
    • 💡Memorise the classic mnemonic: 'The key, the whole key, and nothing but the key, so help me Codd' — this corresponds sequentially to 1NF (the key), 2NF (the whole key), and 3NF (nothing but the key).
    • 💡Always specify primary keys (underlined) and foreign keys (indicated with an asterisk or clearly stated) when writing relational notation in exam questions to secure all design marks.
    • 💡When explaining anomalies, give concrete consequences: Explain exactly what data is lost if a record is deleted (deletion anomaly) or what inconsistency arises if a field is edited in only one row (update anomaly).
    Common Mistakes
    • Mislabelling a partial-key dependency as a 3NF problem; a non-key attribute depending on part of a composite key is a 2NF violation, so check 2NF first.
    • Treating a non-key attribute depending on the whole key as a violation; the 3NF violation is a non-key attribute depending on another non-key attribute.
    • Moves the dependent attribute but omits the determining attribute from the original relation; keep it as a foreign key to preserve the link.
    • Stops after one split without rechecking the new relations for further transitive dependencies; repeat the test on every relation produced.
    • Believing 2NF is relevant to tables with single-attribute primary keys: If a table is in 1NF and its primary key consists of only one attribute, it is automatically in 2NF because partial dependency cannot exist without a composite key.
    • Assuming normalisation always speeds up database queries: While normalisation eliminates redundancy and protects integrity, heavily normalised databases require multiple SQL JOIN operations, which can reduce read performance compared to denormalised structures.
    • Dropping attributes during decomposition: Students frequently forget to place the common attribute as a foreign key in the child table, breaking the relational link between entities.
    Revision Plan
    1. 1Day 1-2: Review database terminology, primary keys, compound/composite keys, foreign keys, and practise drawing E-R diagrams resolving many-to-many relationships.
    2. 2Day 3-4: Master the formal definitions and identification of 1NF, 2NF (partial dependencies), and 3NF (transitive dependencies).
    3. 3Day 5-6: Practise step-by-step normalisation tables from past papers, decomposing unnormalised data tables into 3NF relational schemas.
    4. 4Day 7: Complete timed past paper exam questions requiring explanations of update, insertion, and deletion anomalies alongside normalisation.
    Exam Question Types
    • 📋Table Decomposition: Given an unnormalised flat file or 1NF table, explain step-by-step why it violates 2NF/3NF and write down the resulting relations in 3NF notation.
    • 📋Anomaly Identification: Short-scenario questions asking to identify specific insertion, deletion, or modification anomalies caused by unnormalised data.
    • 📋Entity-Relationship Diagramming: Interpreting or drawing an E-R diagram for a real-world scenario, typically requiring the introduction of a link entity to resolve a many-to-many relationship.
    Command Word Expectations (AQA)
    Explain

    Set out the causes, reasons, or mechanisms clearly using precise database terminology (e.g. state why a partial dependency violates 2NF and identify which specific attribute depends on which sub-key).

    State / Identify

    Provide a brief, factual answer without explanation (e.g. identifying the primary key or stating the normal form of a given table).

    Design / Produce

    Create a formal relational schema or E-R diagram following standard conventions, ensuring all entities, attributes, primary keys, and foreign keys are explicitly marked.

    How Students Lose Marks (Examiner Pitfalls)
    Pitfall: Confusing 2NF and 3NF conditions by not identifying the nature of the dependency (partial vs transitive).
    ❌ Weak Answer (Loses Marks):2NF removes repeating groups, and 3NF removes data that depends on other data.
    Example improved answer:A relation is in 2NF if it is in 1NF and contains no partial dependencies, meaning every non-key attribute is fully functionally dependent on the entire primary key (relevant when the key is composite). A relation is in 3NF if it is in 2NF and contains no transitive dependencies, meaning no non-key attribute depends on another non-key attribute.
    Examiner Tip: Explicitly state that partial dependency only applies when there is a composite primary key. If a table in 1NF has a single-attribute primary key, it is automatically in 2NF.
    Pitfall: Failing to link newly decomposed tables with appropriate primary and foreign keys.
    ❌ Weak Answer (Loses Marks):Split the table into two separate tables: Student and Course, listing the attributes for each.
    Example improved answer:Decompose the relation into Student(StudentID, StudentName, CourseID*) and Course(CourseID, CourseTitle, CourseTutor), where StudentID is the primary key of Student, CourseID is the primary key of Course, and CourseID* acts as a foreign key in Student to maintain referential integrity.
    Examiner Tip: Use standard relational notation with underlines for primary keys and asterisks or overlines for foreign keys when defining decomposed relations.
    Step-by-Step Worked Solutions

    Question: A school stores extracurricular clubs in an unnormalised format: Enrolment(StudentID, StudentName, ClubID, ClubName, ClubRoom, FeePaid) StudentID and ClubID form the composite primary key. Explain why this relation is not in 2NF and decompose it into relations in 3NF.

    1. 1.Step 1: Check 1NF status. The relation has atomic data and a defined primary key (StudentID, ClubID), so it is in 1NF.
    2. 2.Step 2: Check for partial dependencies (2NF test). StudentName depends only on StudentID, not the whole primary key. ClubName and ClubRoom depend only on ClubID. FeePaid depends on both (StudentID, ClubID). Because non-key attributes depend on part of the composite primary key, partial dependencies exist; thus, it is not in 2NF.
    3. 3.Step 3: Eliminate partial dependencies to reach 2NF by creating separate relations: - Student(StudentID, StudentName) - Club(ClubID, ClubName, ClubRoom) - ClubEnrolment(StudentID*, ClubID*, FeePaid)
    4. 4.Step 4: Check for transitive dependencies (3NF test). In Student, StudentName depends only on StudentID. In ClubEnrolment, FeePaid depends only on the composite key. In Club, ClubName and ClubRoom depend directly on ClubID (assuming ClubRoom is an attribute of the club and does not determine ClubName). No non-key attribute determines another non-key attribute.
    5. 5.Step 5: Define the final schema with primary keys underlined and foreign keys marked.
    Final Answer: The relation is not in 2NF because StudentName, ClubName, and ClubRoom exhibit partial dependency on parts of the composite primary key. The 3NF relations are: Student(StudentID, StudentName), Club(ClubID, ClubName, ClubRoom), and Enrolment(StudentID*, ClubID*, FeePaid).

    Question: An online retailer uses the following relation: OrderDetails(OrderID, CustomerID, CustomerAddress, ItemID, ItemDescription, ItemPrice, QuantityOrdered) OrderID and ItemID form the composite primary key. Normalise this relation directly into 3NF, providing the relation names, attributes, primary keys, and foreign keys.

    1. 1.Step 1: Identify functional dependencies. - (OrderID, ItemID) -> QuantityOrdered (Full key dependency) - OrderID -> CustomerID, CustomerAddress (Partial dependency) - CustomerID -> CustomerAddress (Transitive dependency via OrderID) - ItemID -> ItemDescription, ItemPrice (Partial dependency)
    2. 2.Step 2: Remove partial dependencies to form 2NF relations: - OrderLine(OrderID*, ItemID*, QuantityOrdered) - Orders(OrderID, CustomerID, CustomerAddress) - Item(ItemID, ItemDescription, ItemPrice)
    3. 3.Step 3: Remove transitive dependency (CustomerID -> CustomerAddress) from Orders to achieve 3NF: - Orders(OrderID, OrderDate, CustomerID*) - Customer(CustomerID, CustomerAddress)
    4. 4.Step 4: Verify all relations are in 3NF and assign primary and foreign keys.
    Final Answer: 3NF Relations: Customer(CustomerID, CustomerAddress) [PK: CustomerID] Item(ItemID, ItemDescription, ItemPrice) [PK: ItemID] Orders(OrderID, CustomerID*) [PK: OrderID, FK: CustomerID] OrderLine(OrderID*, ItemID*, QuantityOrdered) [PK: (OrderID, ItemID), FK: OrderID, ItemID]
    Active Recall Memory Test
    What three conditions must be satisfied for a relation to be in First Normal Form (1NF)?
    Key Fact: 1. All field values must be atomic (no repeating groups or arrays). 2. Each record must have a unique identifier (primary key). 3. Each column must have a distinct name with a consistent data type.
    What is a partial dependency, and which normal form does it violate?
    Key Fact: A partial dependency occurs when a non-key attribute is dependent on only part of a composite primary key. It violates Second Normal Form (2NF).
    What is a transitive dependency, and which normal form does it violate?
    Key Fact: A transitive dependency occurs when a non-key attribute depends functionally on another non-key attribute rather than directly on the primary key. It violates Third Normal Form (3NF).
    How is a many-to-many (M:N) relationship between two database entities resolved in a relational database?
    Key Fact: By creating an intermediate link (junction) entity that contains the primary keys of both parent entities as foreign keys, forming two 1:M relationships.
    Frequently Asked Questions
    Why do we normalise databases if computers have huge storage capacities now?
    Normalisation is primarily designed to prevent data integrity issues rather than merely save disk space. In an unnormalised database, updating an address or price stored in multiple records can lead to inconsistencies if not updated everywhere (an update anomaly). Furthermore, deletion anomalies can accidentally erase vital information when another record is deleted. Normalisation guarantees that each data fact is recorded in exactly one place.
    Can a table with a single primary key ever violate 2NF?
    No. By definition, a partial dependency requires a composite (compound) primary key because a non-key attribute must depend on only part of the key. If the primary key consists of just a single column, no partial dependencies can exist. Therefore, any table in 1NF with a single-attribute primary key is automatically in 2NF.
    What is the difference between a composite key and a compound key?
    In AQA A-Level Computer Science, both terms describe a primary key made up of two or more attributes. Strictly speaking, a compound key is composed entirely of attributes that are foreign keys from other tables (such as in an intersection table). In contrast, a composite key can contain any combination of attributes that together uniquely identify a record, regardless of whether they are individual foreign keys.
    What is denormalisation and why is it ever used?
    Denormalisation is the deliberate process of adding redundancy back into a normalised schema by combining tables. While normalisation improves data integrity, retrieving data spread across many 3NF tables requires complex SQL JOIN operations that can degrade query performance. In data warehousing, reporting, and high-read environments, denormalisation is used to speed up data retrieval.
    How do I represent primary and foreign keys in exam relational notation?
    In standard relational notation, you write the TableName followed by attributes in parentheses. Underline the primary key attribute (e.g. TableName(PrimaryKey, Attribute1)). Indicate foreign keys either with an asterisk (e.g. Attribute2*), an overline, or by explicitly stating 'where ForeignKey is a foreign key referencing OtherTable(PrimaryKey)' below your schema definition.

    More AQA A-Level Computer Science topics

    See every AQA A-Level Computer Science topic

    Start free

    Cookies on MasteryMind

    Essential cookies keep you signed in.

    Analytics and advertising cookies stay off unless you accept.

    Read our Privacy Policy.