Relation (Table)
A two-dimensional structure of rows and columns representing an entity.
Tuple (Row)
A single record in the table — one instance of the entity.
Attribute (Column)
A property of the entity — all values in a column share the same domain.
Primary Key
Uniquely identifies each tuple. Cannot be null. No two rows have the same PK value.
Foreign Key
An attribute in one table that references the primary key in another — creates a link between tables.
Referential Integrity
Ensures every FK value matches an existing PK value — no orphan records.
Composite Key
A primary key made of two or more attributes — needed when no single attribute is unique.
DBMS
Software that manages the database — provides query processing, security, concurrency control, backup.
Unnormalised Form (UNF): Repeating groups and multiple values in a single field. Data redundancy present.
| OrderID | CustomerName | CustomerCity | Products (repeating) | ProductPrice |
|---|---|---|---|---|
| 1 | Alice Smith | London | Laptop, Mouse | £800, £20 |
| 2 | Bob Jones | London | Monitor | £300 |
| 3 | Alice Smith | London | Keyboard, Mouse | £50, £20 |
Problems: Alice's city repeated; Mouse price repeated; no atomic values in Products column.
SELECT Students.name, Courses.title FROM Students INNER JOIN Enrolments ON Students.id = Enrolments.student_id INNER JOIN Courses ON Enrolments.course_id = Courses.id WHERE Students.year = 12 ORDER BY Students.name ASC
SELECT year, COUNT(*) AS total_students, AVG(score) AS avg_score FROM Students GROUP BY year HAVING AVG(score) > 65 ORDER BY avg_score DESC
-- Insert
INSERT INTO Students (name, year, score) VALUES ('Priya Patel', 12, 87)
-- Update
UPDATE Students SET score = 92 WHERE name = 'Priya Patel'
-- Delete
DELETE FROM Students WHERE year = 13 AND score < 40
Atomicity
All operations in a transaction succeed, or none do. No partial commits.
Consistency
A transaction brings the database from one valid state to another. Constraints are enforced.
Isolation
Concurrent transactions do not interfere with each other — each runs as if alone.
Durability
Once committed, changes persist even if the system crashes — written to permanent storage.
Two transactions read the same record simultaneously. Both modify it based on the original value. The second write overwrites the first — one update is lost.
Solution: Record locking — lock the record when one transaction reads it, preventing others from accessing it until the first transaction commits (or rolls back).
Build a query against the sample Students table (id, name, year, score). The query updates as you configure it.
| id | name | year | score |
|---|---|---|---|
| 1 | Alice | 12 | 87 |
| 2 | Bob | 11 | 55 |
| 3 | Chloe | 12 | 92 |
| 4 | Dan | 11 | 73 |
| 5 | Eve | 13 | 61 |
SELECT * FROM Students
1. A table called Orders has the following attributes: OrderID (PK), CustomerID (FK), CustomerName, CustomerCity, ProductID (FK), ProductName, ProductPrice, Quantity. Explain why this table is not in third normal form and describe the steps to achieve 3NF. [6 marks]
Mark scheme:
2. Write a SQL query to display the year group and average score for each year group, only showing years where the average score exceeds 70, ordered by average score descending. [4 marks]
Mark scheme (1 mark per correct clause):
SELECT year, AVG(score) AS avg_score FROM Students GROUP BY year HAVING AVG(score) > 70 ORDER BY avg_score DESC
HAVING is required (not WHERE) as it filters on aggregated values.
3. Describe the lost update problem in a concurrent database environment and explain one technique to prevent it. [4 marks]
Mark scheme:
4. Explain what is meant by referential integrity in a relational database and state one consequence of violating it. [3 marks]
Mark scheme: