Notes · Computer ScienceUK · A-Levels

Fundamentals of databases

Databases store the structured data that most software depends on, and this chapter develops how they are modelled, designed and queried. It covers entity relationship modelling and the relational model, normalisation to third normal form to remove redundancy, the SQL used to query and modify data, referential integrity, and the transaction processing and ACID properties that keep a shared database correct under concurrent access.

5 sections~17 min reading time3 competenciesLevel Standard 3 · Advanced 2

T·0999 / 14

Exam profile

AO1 · Know the relational model, keys, the normal forms, SQL and the ACID transaction propertiesAO2 · Draw an entity relationship model, normalise data to 3NF and write SQL to query and modify a databaseAO3 · Evaluate a database design and a concurrency-control strategy

Operators:designnormalisewriteexplaindescribeevaluate

basic level

AS-Level expects the relational model, primary and foreign keys, and simple SQL SELECT queries.

higher level

The full A-Level adds entity relationship modelling, normalisation to 3NF, multi-table SQL with JOINs and data modification, referential integrity, and transaction processing with ACID.

Depth

Text

Contents · 5 sections

Fundamentals of databases

Entity relationship modelling and the relational model#

StandardAQA 7517 4.10.1AQA 7517 4.10.2DfE GCE Computer Science - databases

An entity relationship model with a link entity

Fig. 1Customer places many Orders (one-to-many). The many-to-many between Order and Product is resolved by the OrderLine link entity, which holds the foreign keys of both plus the quantity - turning one many-to-many into two one-to-many relationships.

Conceptual data modelling captures the things a system stores and how they relate before any table is built. An entity is a thing of interest about which data is held (a Student, an Order, a Product); an attribute is a property of an entity (a student's name, date of birth); and a relationship links entities. Relationships have a degree: one-to-one (each of A relates to one B and vice versa), one-to-many (one customer places many orders, each order for one customer), and many-to-many (a student takes many courses and each course has many students). Identifying entities, attributes and the degree of each relationship from a described scenario is the first design skill.

In the relational model, data is held in relations (tables). Each row (tuple) is one record; each column is an attribute. A primary key is an attribute (or combination) that uniquely identifies each row - a StudentID; where no single attribute is unique, a composite key of several attributes is used. A foreign key is an attribute in one table that refers to the primary key of another, and it is the mechanism that links tables together, representing the relationships from the conceptual model.

A many-to-many relationship cannot be represented directly in relational tables and must be resolved by introducing a link (junction) entity that sits between the two, turning one many-to-many into two one-to-many relationships. In the model opposite, the many-to-many between Order and Product is resolved by an OrderLine link entity holding the foreign keys of both, plus any attributes of the pairing such as quantity. Recognising and resolving many-to-many relationships is a standard exam requirement.

The relational model's strengths are why it dominates: data is stored once and linked by keys (reducing redundancy), the structure is independent of how the data is physically stored, and a standard language (SQL) queries it. The design flows from the conceptual model - entities become tables, attributes become columns, one-to-many relationships become foreign keys, and many-to-many relationships become link tables - so a clear entity relationship model leads directly to a sound set of tables.

Worked example

Resolving a many-to-many relationship

A school records which students study which subjects: each student studies several subjects and each subject is studied by several students. Design the tables.

Identify the relationship
Student to Subject is many-to-many, which cannot be stored directly in relational tables.
Introduce a link entity
Create a StudentSubject link table holding StudentID and SubjectID as foreign keys (together a composite primary key), plus any pairing attributes such as the set or grade.
Result
Student(StudentID, Name, ...), Subject(SubjectID, Title, ...), and StudentSubject(StudentID, SubjectID, ...) - two one-to-many relationships replacing the many-to-many.

Result: Three tables: Student, Subject and the StudentSubject link table. Each row of the link table records one student studying one subject, so the many-to-many is fully represented.

Typical mistakes

Trying to represent a many-to-many relationship directly instead of resolving it with a link (junction) table.
Confusing a primary key (uniquely identifies rows in this table) with a foreign key (refers to another table's primary key).

Active revision

A library lends books to members; a member can borrow many books over time and each copy of a book can be borrowed by many members. Identify the entities and relationships, state the degree of each, and explain how you would resolve any many-to-many relationship.

Active recall

Recall the key points — then reveal.

Sources: GCE AS and A level subject content for computer science (Department for Education) · AQA A-level Computer Science 7517 specification (AQA)

Normalisation (1NF, 2NF, 3NF)#

AdvancedAQA 7517 4.10.3DfE GCE Computer Science - normalisation

The first three normal forms

Fig. 2Applied in order, each normal form removes a class of problem: 1NF removes repeating groups, 2NF removes partial dependencies on part of a composite key, and 3NF removes transitive dependencies between non-key attributes.

Normalisation is the process of structuring tables to eliminate redundancy and the update, insertion and deletion anomalies it causes. Redundant data - the same customer address repeated in every order - wastes space, and worse, allows the copies to disagree if one is updated and others are not. Normalisation splits data into well-designed tables linked by keys so that each fact is stored exactly once. The A-Level requires the first three normal forms, applied in order (shown opposite).

First normal form (1NF) requires that there are no repeating groups and every cell holds a single, atomic value, with a primary key identifying each row. An order that lists several products in one row violates 1NF; the fix is to give each product its own row. Second normal form (2NF) requires the table to be in 1NF and every non-key attribute to depend on the whole primary key, not just part of it - so it only bites when there is a composite key. A product's name depending only on ProductID (part of an (OrderID, ProductID) key) is a partial dependency and must be split out.

Third normal form (3NF) requires the table to be in 2NF and to have no transitive dependencies - no non-key attribute depending on another non-key attribute. If a table keyed on OrderID stores CustomerName and CustomerAddress, the address depends on the customer, not directly on the order, so it should move to a Customer table keyed on CustomerID, with OrderID holding CustomerID as a foreign key. A memorable summary of 3NF is that every non-key attribute depends on 'the key, the whole key, and nothing but the key'.

Normalising to 3NF produces tables with no redundancy, no anomalies, and clean links by foreign key - the goal of good relational design. There is a trade-off: heavily normalised data may need more joins to answer a query, which can be slower, so performance-critical systems sometimes denormalise deliberately. But at A-Level the expectation is to normalise a given set of data to 3NF, showing each step and the resulting tables and keys - a demanding, high-value skill.

Worked example

Normalising order data to 3NF

An order table repeats, for each order: OrderID, CustomerName, CustomerAddress, and a list of (ProductID, ProductName, Quantity). Normalise it to 3NF.

To 1NF
Remove the repeating group of products: one row per product line. Table (OrderID, CustomerName, CustomerAddress, ProductID, ProductName, Quantity) with composite key (OrderID, ProductID).
To 2NF
Remove partial dependencies on part of the key: CustomerName/Address depend on OrderID only, ProductName on ProductID only. Split into Orders(OrderID, CustomerName, CustomerAddress), Product(ProductID, ProductName) and OrderLine(OrderID, ProductID, Quantity).
To 3NF
Remove the transitive dependency: CustomerName/Address depend on the customer, not the order. Split into Customer(CustomerID, Name, Address) and Orders(OrderID, CustomerID).

Result: Four 3NF tables: Customer(CustomerID, Name, Address), Orders(OrderID, CustomerID), Product(ProductID, ProductName), OrderLine(OrderID, ProductID, Quantity). Each fact is stored once and tables are linked by foreign keys.

Typical mistakes

Skipping steps or applying them out of order - a table must reach 1NF and 2NF before 3NF; 2NF concerns partial dependencies (composite keys), 3NF concerns transitive dependencies.
Leaving redundant data or a repeating group in a table and still calling it normalised.

Active revision

An unnormalised booking table stores, per booking: BookingID, MemberName, MemberPhone, and for each session booked (SessionID, SessionTitle, Date). Normalise it to 3NF, showing the tables and their keys at each stage.

Active recall

Recall the key points — then reveal.

Sources: AQA A-level Computer Science 7517 specification (AQA)

SQL: querying data#

StandardAQA 7517 4.10.4DfE GCE Computer Science - SQL

A sample Student table

Fig. 3The query SELECT Name, Mark FROM Student WHERE Mark >= 40 ORDER BY Mark DESC returns Diaz (81), Adeyemi (72) and Chen (55) - Brookes (38) is excluded by the WHERE condition, and the results are sorted in descending order of mark.

SQL (Structured Query Language) is the standard language for working with relational databases. The core retrieval statement is SELECT ... FROM ... WHERE: SELECT lists the columns to return, FROM names the table(s), and WHERE gives a condition that filters which rows are returned. For example, from the Student table opposite, 'SELECT Name, Mark FROM Student WHERE Mark >= 40' returns the name and mark of every student who passed. Selecting all columns uses '*'.

Results can be ordered and refined. ORDER BY sorts the results by one or more columns, ascending by default or DESC for descending. Conditions in the WHERE clause combine relational operators (

=

<>

<

>

\leq

\geq

) with AND, OR and NOT, and can use LIKE with wildcards for pattern matching and BETWEEN for ranges. Aggregate functions - COUNT, SUM, AVG, MAX, MIN - compute a single value over the selected rows, optionally grouped with GROUP BY, so 'SELECT AVG(Mark) FROM Student' returns the mean mark.

The real power of a relational database is combining tables with a JOIN. An INNER JOIN matches rows from two tables where a foreign key equals a primary key, returning only the rows that have a match, so a query can list each student's name alongside the titles of the courses they take by joining Student to Course through the link table. The join condition (ON) states which columns must match; without it the query would produce every combination of rows, a common and costly error.

Writing correct SQL to answer a stated question is a core Paper 2 skill: pick the right columns, the right table(s), a correct WHERE condition, a JOIN where data spans tables, and ORDER BY or an aggregate where the question asks for sorting or a summary. Reading a query and predicting its result from sample data is equally examined, so trace queries carefully against the given tables.

Worked example

Writing a SELECT query and tracing its result

From the Student table (StudentID, Name, Mark) with rows Adeyemi 72, Brookes 38, Chen 55, Diaz 81, write SQL to list the names and marks of students who passed (mark 40 or more), highest first, and give the result.

Choose columns and table
SELECT Name, Mark FROM Student - the required columns from the Student table.
Filter and sort
Add WHERE Mark >= 40 to keep only passes, and ORDER BY Mark DESC to sort from highest to lowest. Full query: SELECT Name, Mark FROM Student WHERE Mark >= 40 ORDER BY Mark DESC;
Trace the result
Brookes (38) fails the WHERE test and is dropped; the rest sort as Diaz 81, Adeyemi 72, Chen 55.

Result: The query returns three rows: Diaz 81, Adeyemi 72, Chen 55. The WHERE clause filtered the rows and ORDER BY ... DESC sorted them.

Typical mistakes

Omitting the JOIN condition (ON), which returns every combination of rows rather than the matched ones.
Confusing WHERE (filters rows) with the column list after SELECT (chooses columns), or forgetting quotes around text values in a condition.

Active revision

Using the Student table (StudentID, Name, Mark) opposite, write SQL to list the Name and Mark of every student scoring at least 40, sorted from highest to lowest mark, and separately write SQL to return the average mark of all students.

Active recall

Recall the key points — then reveal.

Sources: AQA A-level Computer Science 7517 specification (AQA)

SQL: defining and modifying data; referential integrity#

StandardAQA 7517 4.10.4DfE GCE Computer Science - SQL modification

As well as querying, SQL defines and modifies data. CREATE TABLE defines a new table, naming each column, its data type (INTEGER, VARCHAR, DATE, etc.) and constraints such as the PRIMARY KEY and FOREIGN KEY. INSERT INTO ... VALUES adds a new row; UPDATE ... SET ... WHERE changes existing rows that match a condition; and DELETE FROM ... WHERE removes matching rows. The WHERE clause is critical on UPDATE and DELETE - omitting it changes or deletes every row in the table, a dangerous and frequently examined mistake.

Referential integrity is the rule that a foreign key must always refer to a primary key that actually exists (or be null). It keeps the links between tables valid: you cannot add an order for a customer who does not exist, and the database will prevent (or cascade) the deletion of a customer who still has orders, so there are never 'orphaned' references. The database management system enforces this automatically once the foreign keys are declared, which is a major reason to define keys properly.

These operations must be considered together with integrity constraints. Declaring a primary key enforces uniqueness and prevents duplicate or null identifiers; declaring a foreign key enforces referential integrity; and column constraints (NOT NULL, data types, ranges) enforce domain integrity so that only sensible values are stored. Well-chosen constraints let the DBMS reject bad data automatically rather than relying on the application to check.

Being able to write correct CREATE, INSERT, UPDATE and DELETE statements, and to explain how referential integrity and key constraints protect the data, rounds out the SQL requirement. A common exam scenario asks what would happen to related records if a row were deleted, testing whether you understand that referential integrity blocks or cascades such changes to keep the database consistent.

Worked example

Modifying data safely

Write SQL to (a) add a student S5 named Evans with mark 64, and (b) raise the mark of student S2 to 45. Explain the role of the WHERE clause in (b).

Insert the new row
INSERT INTO Student (StudentID, Name, Mark) VALUES ('S5', 'Evans', 64); - text values are quoted, the numeric mark is not.
Update one row
UPDATE Student SET Mark = 45 WHERE StudentID = 'S2'; - the WHERE clause restricts the change to the one intended row.
Why WHERE matters
Without WHERE, UPDATE Student SET Mark = 45 would set every student's mark to 45 - the WHERE clause is what limits the update to S2.

Result: The INSERT adds Evans; the UPDATE changes only S2's mark to 45. The WHERE clause is essential to avoid altering every row.

Typical mistakes

Omitting the WHERE clause on UPDATE or DELETE, which affects every row in the table rather than the intended ones.
Thinking a foreign key can point to a non-existent primary key - referential integrity forbids it, preventing orphaned records.

Active revision

Write SQL to insert a new student (S5, 'Evans', 64) into the Student table, then to increase every mark below 40 by 5, and finally explain what referential integrity would do if you tried to delete a Course that still has enrolments referring to it.

Active recall

Recall the key points — then reveal.

Sources: AQA A-level Computer Science 7517 specification (AQA)

Transaction processing and ACID#

AdvancedAQA 7517 4.10.5DfE GCE Computer Science - transactions

The ACID properties of a transaction

Fig. 4The four guarantees a database provides for transactions. Together they keep the database correct even when a transaction fails partway or when many transactions run at the same time.

A transaction is a single logical unit of work made up of one or more database operations that must all succeed or all fail together - transferring money between two accounts (debit one, credit the other) is the classic example. To keep a database correct, especially when many users access it at once, transactions are required to satisfy the four ACID properties (shown opposite): Atomicity, Consistency, Isolation and Durability.

Atomicity means a transaction is all-or-nothing: if any part fails, the whole transaction is rolled back and the database is left as if it never started, so money is never debited without being credited. Consistency means a transaction takes the database from one valid state to another, never violating a constraint or rule. Isolation means concurrent transactions do not interfere - each behaves as though it ran alone, so one transaction never sees another's half-finished changes. Durability means once a transaction is committed its changes are permanent and survive a subsequent crash or power failure.

The hard part is concurrent access. If two transactions read and update the same data at once, updates can be lost - both read a balance of 100, both add 50, and the final value is 150 instead of 200 because one overwrites the other. The main defence is record locking: a transaction locks the records it is using so no other transaction can change them until it finishes, then releases the lock. Locking preserves isolation but can cause a deadlock if two transactions each hold a lock the other needs, which the DBMS detects and breaks (usually by aborting one).

Other techniques manage concurrency without heavy locking. Serialisation ensures the outcome of running transactions concurrently is the same as if they had run one after another in some order. Timestamp ordering and commitment ordering use timestamps to decide which transaction proceeds when there is a conflict, so that the result stays serialisable. Explaining a concurrency problem (the lost update) and how record locking and the ACID properties prevent it is a demanding but standard A-Level question.

Worked example

Preventing a lost update

Two transactions both read an account balance of 200 and each subtract 50. Explain how the balance could wrongly end at 150, and how locking fixes it.

The lost update
Transaction A reads 200; before it writes, transaction B also reads 200. A writes $200 - 50 = 150$ ; B, still holding its stale 200, writes $200 - 50 = 150$ , overwriting A. One withdrawal is lost - the balance is 150, not 100.
Apply record locking
When A starts, it locks the balance record. B must wait until A commits and releases the lock.
Serialised outcome
B now reads the updated 150 and writes $150 - 50 = 100$ . The transactions effectively ran one after the other (isolation), giving the correct result.

Result: With record locking the final balance is 100, not 150. Locking enforces isolation so concurrent transactions cannot overwrite each other's updates - the lost update is prevented.

Typical mistakes

Confusing the ACID properties - especially isolation (transactions do not interfere) with consistency (the database stays valid).
Ignoring concurrency: describing a transaction as safe without addressing what happens when two run at once (lost updates, deadlock).

Active revision

Two cashpoints simultaneously read an account balance of 200 and each withdraw 50. Explain how a lost update could leave the balance at 150 instead of 100, and how record locking and the ACID properties prevent it.

Active recall

Recall the key points — then reveal.

Sources: AQA A-level Computer Science 7517 specification (AQA)

References & sources

Sources

Department for Education

GCE AS and A level subject content for computer science

AQA

AQA A-level Computer Science 7517 specification

Fundamentals of databases

5 sections~17 min reading time3 competenciesLevel Standard 3 · Advanced 2

Fundamentals of databases

Key points

Resolving a many-to-many relationship

Key points

Normalising order data to 3NF

Key points

Writing a SELECT query and tracing its result

Key points

Modifying data safely

Key points

Preventing a lost update

Sources

Fundamentals of databases

Key points

Resolving a many-to-many relationship

Key points

Normalising order data to 3NF

Key points

Writing a SELECT query and tracing its result

Key points

Modifying data safely

Key points

Preventing a lost update

Sources