Mission 1 · Spec 4.10.1 – 4.10.3
Relational databases and normalisation
Conceptual data models and entity-relationship diagrams, relational databases and keys, and normalisation to third normal form.
- Starter 5 min
- Learn 20 min
- Lab 15 min
- Quiz 10 min
- Exam 15 min
The update anomaly
A table stores each student's name alongside every course they take. A student changes their surname. What could go wrong?
Reveal
The name is stored in many rows; if one isn't updated the data becomes inconsistent. Normalisation removes this redundancy so each fact is stored once.
Key ideas
Data models
A conceptual model identifies entities (things we store data about), their attributes and relationships: one-to-one, one-to-many, many-to-many. Entity-relationship diagrams show them; entity descriptions are written like Student(StudentID, Name, Form).
Keys
Primary key: uniquely identifies a record. Composite key: a primary key made of more than one attribute. Foreign key: an attribute linking to another table's primary key. Many-to-many relationships need a link table.
Normalisation
1NF: atomic attributes, no repeating groups. 2NF: 1NF and no partial key dependencies. 3NF: 2NF and no non-key dependencies. "The key, the whole key and nothing but the key."
Why normalise?
Reduces redundancy, prevents update, insertion and deletion anomalies, keeps data consistent, and makes queries and maintenance more efficient.
Which normal form rule is broken?
Relationship types
Exam-style questions
1. Explain what is meant by a composite key and give an example.
[2 marks]Mark scheme
- A primary key made up of two or more attributes that together uniquely identify a record (1)
- e.g. (StudentID, CourseID) in an Enrolment table (1)
2. Explain why a relational database should be normalised.
[3 marks]Mark scheme
- Removes data redundancy / each fact stored once, saving space (1)
- Avoids update / insertion / deletion anomalies, keeping data consistent (1)
- Makes the database easier to maintain and change (1)
TUTOR NOTES
- Exam habit: in entity descriptions, underline the primary key and mark foreign keys with an asterisk or a note.
Mission 2 · Spec 4.10.4
Structured Query Language
Retrieving, updating, inserting and deleting data from multiple tables, and defining tables with CREATE TABLE.
- Starter 5 min
- Learn 10 min
- Lab 30 min
- Quiz 10 min
- Exam 15 min
Say it in SQL
How would you ask a database for the first names of students in 10B, sorted alphabetically?
Reveal
SELECT FirstName FROM Students WHERE Form = '10B' ORDER BY FirstName
SQL you need
Querying
SELECT … FROM … WHERE … ORDER BY … ASC/DESC
… FROM A, B WHERE A.key = B.key
… FROM A INNER JOIN B ON A.key = B.key
Changing data
INSERT INTO T (f1, f2) VALUES (v1, v2)
UPDATE T SET f = v WHERE …
DELETE FROM T WHERE …
Defining tables
CREATE TABLE Rooms (
RoomID INTEGER PRIMARY KEY,
Building VARCHAR(20),
Capacity INTEGER
)
Foreign keys: FOREIGN KEY (f) REFERENCES T(key).
SQL: querying multiple tables
SQL: changing data
SQL: defining tables
Exam-style questions
1. Write an SQL query to list the surname of each student and the name of their club, for clubs run by 'Mr Patel', in surname order.
[5 marks]Mark scheme
SELECT Students.Surname, Clubs.ClubName(1)FROM Students, Clubsor an INNER JOIN (1)- Link condition
Students.ClubID = Clubs.ClubID(1) Clubs.Teacher = 'Mr Patel'(1)ORDER BY Students.Surname(1)
2. Write a CREATE TABLE statement for a table Loans with LoanID (primary key), StudentID (foreign key referencing Students) and DueDate.
[4 marks]Mark scheme
CREATE TABLE Loans ((1)- LoanID with a suitable type and
PRIMARY KEY(1) - StudentID and DueDate with suitable types (1)
FOREIGN KEY (StudentID) REFERENCES Students(StudentID)(1)
TUTOR NOTES
- Note: the playground uses SQLite, which accepts INTEGER, TEXT and VARCHAR types.
Mission 3 · Spec 4.10.5
Client-server databases and concurrency
How a database server handles many simultaneous users: record locks, serialisation, timestamp ordering and commitment ordering.
- Starter 5 min
- Learn 15 min
- Lab 10 min
- Quiz 10 min
- Exam 15 min
The lost update
Two staff read a stock level of 10 at the same time. One sells 3 and saves 7; the other sells 2 and saves 8. What's wrong?
Reveal
The stock should be 5, but the second save overwrote the first: a lost update. Concurrent access must be controlled.
Key ideas
Client-server database
A database server holds the data and handles requests from many clients, giving consistent, centrally managed data. Concurrent access can cause lost updates.
Record locks
A record being updated is locked so no other transaction can change it until the first finishes. Can lead to deadlock.
Serialisation
Transactions are executed one after another (or as if they were), so they can't interfere.
Timestamp ordering
Each transaction gets a timestamp; each record stores the timestamps of its last read and write. A transaction that would conflict with a later one is rolled back and restarted.
Commitment ordering
Transactions are ordered by their dependencies on each other and the time they were initiated, so they are committed in a safe order, preventing deadlock.
Match the technique
Exam-style question
1. Explain how timestamp ordering can prevent the lost update problem.
[4 marks]Mark scheme
- Each transaction is given a timestamp when it starts (1)
- Each record stores the timestamps of the last transactions to read and write it (1)
- Before a transaction saves a change, the timestamps are compared (1)
- If a later transaction has already accessed the record, the earlier transaction is aborted and restarted, so no update is lost (1)
TUTOR NOTES
- Link: connect to ACID properties and transaction processing.