Relational databases — AQA A-Level Computer Science

A relational database organises data into structured tables (relations) comprising records (tuples) and fields (attributes), linked together using primary and foreign keys. Mastering database design requires applying normalization up to third normal form (3NF) and writing precise SQL queries to guarantee data integrity and reduce redundancy.

Relational databases explained

A relational database stores data in one or more tables, where each table holds data about a single type of entity and each row represents one instance of that entity.

Read the full explanation

Columns, called attributes or fields, hold the individual pieces of data for each row. Tables are linked to one another through keys: a primary key uniquely identifies each row within its table, and a foreign key is an attribute in one table that refers to the primary key of another table. This linking allows related data to be combined when queried, without repeating the same data in many places. For example, a Customers table and an Orders table can be linked by a CustomerID, so an order records which customer placed it. Explaining the concept means describing tables, rows, columns, and the keys that relate tables together.

Your focus

Explain the concept of a relational database.

Which command word is this?

Set out the reasons or mechanisms behind a database concept clearly. For example, explaining why a table is not in 3NF requires naming the specific transitive dependency and the columns involved.

Relational databases exam tips

Topic Overview

Relational databases provide an organised, robust architecture for persisting and managing structured data across multiple connected entities. By enforcing relationships via primary and foreign keys, relational databases minimise data redundancy and maintain referential integrity.

For AQA A-Level Computer Science, students must master conceptual modelling using Entity-Relationship Diagrams (ERDs), mathematical normalization from unnormalised form (UNF) through to 3NF, and practical manipulation using both Data Definition Language (DDL) and Data Manipulation Language (DML) in SQL.

Key Concepts

→Relational Model Structure: Relations (tables), attributes (columns), and tuples (rows or records), where each attribute must hold atomic values.
→Keys and Referential Integrity: Primary keys uniquely identify tuples; foreign keys establish links between relations and must match a valid primary key in the referenced relation.
→Normalization (1NF, 2NF, 3NF): Systematic progressive elimination of repeating groups (1NF), partial key dependencies (2NF), and non-key transitive dependencies (3NF).
→Structured Query Language (SQL): Declarative language dividing into DDL (CREATE, ALTER, DROP) and DML (SELECT, INSERT, UPDATE, DELETE) with complex filtering and joins.

Marking Points

Explains that data is stored in tables, each representing one type of entity
Explains that rows represent individual instances and columns represent attributes
Explains that tables are related to each other using keys
Explains that linking tables avoids repeating the same data

Examiner Tips

💡Define a table, a row and a column before explaining how tables are linked.
💡Give a concrete example of two tables linked by a key to show the concept.
💡Explain why linking tables avoids repeated data rather than just stating that it does.
💡When drawing or interpreting Entity-Relationship Diagrams (ERDs), always resolve many-to-many (M:N) relationships into two one-to-many (1:M) relationships using an associative link entity.
💡Underline primary keys and prefix foreign keys with an asterisk or hash, explicitly declaring the notation used if the exam question does not specify a convention.
💡In SQL questions, check whether the prompt asks for specific table aliases or fully qualified column names (e.g. Customer.CustomerID) to avoid ambiguity during table joins.

Common Mistakes

Describes a relational database as just a collection of files; the key idea is that tables are related through keys.
Describes a table as a single record instead of a set of rows that each hold one instance of the entity.
Omits the role of keys in linking tables, giving a description of storage only.
Thinking 2NF issues can occur in tables with a single-attribute primary key. Partial dependencies only exist when there is a composite primary key.
Assuming that normalizing a database always improves query speed. While normalization eliminates update/insert/delete anomalies and reduces redundancy, it often requires more JOIN operations, which can decrease read performance.
Confusing the WHERE clause with the HAVING clause in SQL: WHERE filters individual records before aggregation, whereas HAVING filters grouped data after a GROUP BY operation.

Revision Plan

1Day 1-2: Master Entity-Relationship Diagrams (ERDs), entity cardinality (1:1, 1:M, M:N), and resolving M:N links with associative junction entities.
2Day 3-5: Practice Normalization step-by-step with raw dataset tables: memorize definitions for 1NF, 2NF, and 3NF, and practice identifying partial vs transitive dependencies.
3Day 6-8: Write SQL DDL (CREATE TABLE with PRIMARY KEY, FOREIGN KEY constraints) and DML queries (SELECT ... WHERE ... ORDER BY, aggregates, and INNER JOINs).
4Day 9-10: Review transaction processing concepts (ACID properties, record locking) and complete timed AQA past-paper questions.

Exam Question Types

📋Normalization Scenario: Given a flat table or unnormalised form (UNF), produce relations in 3NF and annotate keys.
📋Entity-Relationship Modelling: Draw or complete an ER diagram based on a business scenario, resolving any many-to-many relationships.
📋SQL Query Writing: Construct DDL statements to build tables with constraints, or DML statements using SELECT with WHERE, GROUP BY, HAVING, or INNER JOIN.
📋Theory of Transaction Processing & DBMS: Discuss referential integrity, record locking, or how ACID properties ensure database reliability during concurrent access.

Command Word Expectations (AQA)

Explain

Set out the reasons or mechanisms behind a database concept clearly. For example, explaining why a table is not in 3NF requires naming the specific transitive dependency and the columns involved.

Design / Produce

Provide formal relation schemas with table names, bracketed attributes, and explicit notation indicating primary and foreign keys (e.g., TableName(*PrimaryKey, #ForeignKey, Attribute)).

Write

Produce syntactically valid SQL code adhering to standard SQL conventions, including correct keywords, clauses in standard order, and appropriate join conditions.

How Students Lose Marks (Examiner Pitfalls)

Pitfall: Confusing the definitions of Second Normal Form (2NF) and Third Normal Form (3NF), specifically confusing partial dependency with transitive dependency.

❌ Weak Answer (Loses Marks):A table is in 3NF when it is in 2NF and there are no repeating groups or composite keys.

Example improved answer:A relation is in 3NF if it is already in 2NF and contains no transitive dependencies; that is, all non-key attributes are fully functionally dependent solely on the primary key, meaning 'all attributes depend on the key, the whole key, and nothing but the key'.

Examiner Tip: Always state that a relation must satisfy the preceding normal form first (e.g. 'in 2NF and...') and use the formal terms 'partial dependency' for 2NF and 'transitive dependency' for 3NF.

Pitfall: Failing to correctly construct SQL JOIN conditions, either omitting the ON clause or matching incorrect key attributes.

❌ Weak Answer (Loses Marks):SELECT StudentName, CourseName FROM Student, Course;

Example improved answer:SELECT Student.StudentName, Course.CourseName FROM Student INNER JOIN Enrolment ON Student.StudentID = Enrolment.StudentID INNER JOIN Course ON Enrolment.CourseID = Course.CourseID;

Examiner Tip: When querying tables connected via a many-to-many relationship, remember you must join through the intermediate link table using both relevant foreign key matches.

Step-by-Step Worked Solutions

Question: A school stores data about musical instrument lessons in an unnormalised format: Lesson(StudentNumber, StudentName, Instrument, TeacherID, TeacherName, TeacherRoom) Each student can learn multiple instruments. Each instrument has only one dedicated teacher, and each teacher has their own room. Explain why this relation is not in 3NF and normalise it into 3NF relations, clearly denoting primary keys with an asterisk (*) and foreign keys with a hash (#).

1.Step 1: Check 1NF: Assuming values are atomic and each record is distinct, the composite primary key must be (StudentNumber, Instrument) because a student can learn multiple instruments.
2.Step 2: Check 2NF: Identify partial dependencies. StudentName depends only on StudentNumber (part of the composite key). TeacherID, TeacherName, and TeacherRoom depend solely on Instrument. Therefore, the relation violates 2NF.
3.Step 3: Resolve to 2NF: Split into Student(*StudentNumber, StudentName), InstrumentTeacher(*Instrument, TeacherID, TeacherName, TeacherRoom), and LessonLink(*#StudentNumber, *#Instrument).
4.Step 4: Check 3NF: In InstrumentTeacher, TeacherName and TeacherRoom depend functionally on TeacherID, which is a non-key attribute (transitive dependency: Instrument -> TeacherID -> TeacherName, TeacherRoom).
5.Step 5: Resolve to 3NF: Split the teacher details into a separate Teacher relation: Teacher(*TeacherID, TeacherName, TeacherRoom) and link Instrument to Teacher: Instrument(*Instrument, #TeacherID).

Final Answer: 3NF Relations: Student(*StudentNumber, StudentName) Teacher(*TeacherID, TeacherName, TeacherRoom) Instrument(*Instrument, #TeacherID) StudentLesson(*#StudentNumber, *#Instrument)

Question: Write an SQL query to retrieve the names of all students ('StudentName') and the instrument they play ('InstrumentName') who have lessons in room 'M12'. Use the following schema: Student(StudentID, StudentName) Lesson(LessonID, StudentID, TeacherID) Teacher(TeacherID, TeacherName, RoomNumber, InstrumentName)

1.Step 1: Identify required output fields: Student.StudentName and Teacher.InstrumentName.
2.Step 2: Identify tables involved: Student, Lesson, and Teacher.
3.Step 3: Formulate join predicates: Student.StudentID = Lesson.StudentID and Lesson.TeacherID = Teacher.TeacherID.
4.Step 4: Add the WHERE condition: Teacher.RoomNumber = 'M12'.

Final Answer: SELECT Student.StudentName, Teacher.InstrumentName FROM Student INNER JOIN Lesson ON Student.StudentID = Lesson.StudentID INNER JOIN Teacher ON Lesson.TeacherID = Teacher.TeacherID WHERE Teacher.RoomNumber = 'M12';

Active Recall Memory Test

What three conditions must be satisfied for a database relation to be in First Normal Form (1NF)?

Key Fact: It contains no repeating attributes/groups of data, all field values are atomic, and each record is uniquely identifiable via a primary key.

What defines a transitive dependency in the context of relational database normalization?

Key Fact: A condition where a non-key attribute depends functionally on another non-key attribute rather than directly on the primary key.

What does each letter stand for in the ACID transaction acronym?

Key Fact: Atomicity, Consistency, Isolation, and Durability.

What is the primary function of a foreign key in a relational database?

Key Fact: To establish and enforce a link between two relations, ensuring referential integrity by pointing to an existing primary key in another relation.

Frequently Asked Questions

Why do we bother normalizing a database if it makes queries slower?

Normalization prevents data modification anomalies (insertion, deletion, and update anomalies) and removes unnecessary data duplication. While querying multiple normalized tables requires JOIN operations that can introduce slight processing overhead, the protection of data integrity and consistency far outweighs the marginal performance difference in most business systems.

Can a table with a single primary key ever violate Second Normal Form (2NF)?

No. A violation of 2NF requires a partial dependency, which occurs when an attribute depends on only part of a composite primary key. If a relation is in 1NF and its primary key consists of only a single attribute, it is automatically in 2NF.

What is the difference between WHERE and HAVING in SQL?

The WHERE clause filters individual records before any grouping or aggregation takes place. The HAVING clause filters the aggregated rows generated after a GROUP BY clause has been applied. For example, use WHERE to select patients over 65, but use HAVING to filter doctors whose COUNT(AppointmentID) exceeds 20.

What happens if referential integrity is violated?

A violation of referential integrity occurs when a foreign key references a primary key value that does not exist in the linked parent table (an orphan record). Database management systems enforce referential integrity constraints to reject such invalid insertions or deletions, preventing corrupt or disconnected data.

How do you represent a many-to-many relationship in a relational schema?

Relational databases cannot directly implement many-to-many relationships without data duplication. You must break the relationship down by creating an intermediate linking (associative) table that holds foreign keys pointing to the primary keys of both original entities, often combining both foreign keys to form a composite primary key.

Relational databases explained

Your focus

Which command word is this?

Relational databases exam tips

More AQA A-Level Computer Science topics

Cookies on MasteryMind