Notes › Chapter 8
Chapter 8 · Paper 1
Databases
Relational database concepts, entity-relationship modelling, normalisation, DBMS features and SQL queries.
8.1 Database Concepts
8.2 DBMS
8.3 DDL & DML / SQL
8.1 Database Concepts
Limitations of the File-Based Approach
Before databases, data was stored in separate flat files. This caused major problems:
| Problem | Description | Example |
|---|---|---|
| Data redundancy | Same data stored in multiple files | Customer address stored in sales file AND delivery file |
| Data inconsistency | Redundant copies get out of sync | Address updated in one file but not the other |
| Data isolation | Hard to access data across multiple files | Cross-referencing customer orders with inventory requires manual work |
| Data dependency | Programs tied to specific file structure | Changing file format breaks all programs using it |
| No data integrity | No validation enforced at storage level | Can store age = -5 with no error |
| No concurrent access control | Multiple users accessing same file causes corruption | Two users edit same record simultaneously |
✅ Databases solve all these problems through centralised data management, relationships, integrity constraints, and access control.
Key Database Terminology
| Term | Definition | Analogy |
|---|---|---|
| Entity | A real-world object or concept about which data is stored | A category (e.g. Student, Course) |
| Attribute | A property or characteristic of an entity | A column heading (e.g. StudentName) |
| Table / Relation | A 2D structure of rows and columns storing entity data | A spreadsheet tab |
| Record / Tuple / Row | One complete set of related data for a single entity instance | One row in a spreadsheet |
| Field / Column | A single attribute's values for all records | One column in a spreadsheet |
| Primary Key (PK) | An attribute (or set) that uniquely identifies each record in a table | A unique ID number |
| Foreign Key (FK) | An attribute in one table that references the primary key of another — creates a relationship | A link between tables |
| Composite Key | Primary key made of two or more fields combined | OrderID + ProductID together = unique |
| Index | A data structure that speeds up searches on a field | Book index → find pages faster |
Sample Database: School System
Student
| StudentID 🔑 | FirstName | LastName | DateOfBirth | CourseID 🔗 |
|---|---|---|---|---|
| S001 | Arjun | Patel | 2007-03-12 | C01 |
| S002 | Fatima | Khan | 2006-11-08 | C02 |
| S003 | James | Smith | 2007-05-22 | C01 |
🔑 = Primary Key | 🔗 = Foreign Key (references Course table)
Course
| CourseID 🔑 | CourseName | TeacherID 🔗 |
|---|---|---|
| C01 | Computer Science | T03 |
| C02 | Mathematics | T07 |
Entity-Relationship (ER) Diagrams
ER diagrams show entities and the relationships between them, including cardinality (how many of each entity can relate to the other).
Student
Many ← enrolls in → One
ENROLLED IN
Course
Course
Many ← taught by → One
TAUGHT BY
Teacher
| Relationship Type | Meaning | Example | Implementation |
|---|---|---|---|
| One-to-One (1:1) | Each A links to at most one B, and vice versa | Person ↔ Passport | FK in either table |
| One-to-Many (1:M) | One A links to many B, but each B links to only one A | Teacher → many Courses | FK in "many" table |
| Many-to-Many (M:M) | One A links to many B, and one B links to many A | Students ↔ Courses | Resolved with junction table |
Many-to-Many relationships cannot be directly implemented in relational databases. They must be resolved using a junction/linking table with composite primary key (e.g. StudentCourse table with StudentID + CourseID as primary key).
Normalisation
Normalisation
Normalisation is the process of organising a relational database to reduce data redundancy and improve data integrity. It involves decomposing tables into smaller, well-structured tables.
1NF — First Normal Form
Rules:
- → Every cell contains a single, atomic (indivisible) value
- → No repeating groups of columns
- → All values in a column are the same data type
- → Each row is unique (has a primary key)
❌ Violates 1NF: Student(ID, Name, Subject1, Subject2, Subject3) — repeating groups
✅ In 1NF: StudentSubject(StudentID, SubjectName) — each row = one student/subject pair
2NF — Second Normal Form
Rules:
- → Must already be in 1NF
- → No partial dependencies — every non-key attribute must depend on the whole primary key, not just part of it
- → (Only applies when primary key is composite)
❌ Partial dependency: OrderDetail(OrderID, ProductID, ProductName) — ProductName depends only on ProductID, not the full key
✅ Fix: Move ProductName to a separate Product(ProductID, ProductName) table
3NF — Third Normal Form
Rules:
- → Must already be in 2NF
- → No transitive dependencies — non-key attributes must not depend on other non-key attributes
❌ Transitive dependency: Student(StudentID, CourseID, CourseName) — CourseName depends on CourseID (not on StudentID)
✅ Fix: Separate Course(CourseID, CourseName) table; Student only keeps CourseID as FK
✅ Mnemonic: "The key, the whole key, and nothing but the key." — 1NF: the key (every row unique). 2NF: the whole key (no partial dependencies). 3NF: nothing but the key (no transitive dependencies).
8.2 Database Management System (DBMS)
DBMS Definition
A DBMS is software that manages, stores, and provides access to a database. It acts as an interface between applications and the database. Examples: MySQL, Microsoft Access, Oracle, PostgreSQL, SQLite.
Features of a DBMS
| Feature | Description |
|---|---|
| Data storage management | Handles physical storage; manages file organisation and access methods |
| Query processing | Processes SQL queries; optimises query execution plans |
| Data integrity enforcement | Enforces constraints (PK, FK, NOT NULL, UNIQUE) at database level |
| Access control / Security | User authentication; grant/revoke permissions (GRANT, REVOKE SQL commands) |
| Concurrency control | Manages simultaneous access by multiple users; prevents data corruption via locking |
| Backup and recovery | Transaction logs allow rollback after failure; supports backups |
| Data sharing | Multiple users and applications can access data simultaneously |
ACID Properties (Transactions)
A transaction is a sequence of database operations treated as a single unit. ACID properties guarantee reliable transactions:
⚛️
Atomicity
All operations in a transaction succeed, or none do. No partial updates.
✅
Consistency
Transaction brings database from one valid state to another. No constraint violations.
🏝️
Isolation
Concurrent transactions don't interfere with each other during execution.
💾
Durability
Once committed, changes are permanent — even after system crash.
8.3 SQL — DDL and DML
DDL — Data Definition Language
- Defines the structure of the database
- Commands: CREATE, ALTER, DROP
- Creates tables, defines columns and data types
- Sets constraints (PK, FK, NOT NULL)
DML — Data Manipulation Language
- Manipulates the data within tables
- Commands: SELECT, INSERT, UPDATE, DELETE
- Queries and modifies records
- Most commonly used SQL in exams
CREATE TABLE (DDL)
DDL — Create Table
CREATE TABLE Student (
StudentID CHAR(4) NOT NULL PRIMARY KEY,
FirstName VARCHAR(50) NOT NULL,
LastName VARCHAR(50) NOT NULL,
DateOfBirth DATE,
CourseID CHAR(3),
FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);
SQL Data Types
| Data Type | Use For | Example |
|---|---|---|
| CHAR(n) | Fixed-length string of exactly n characters | CHAR(3) for country codes: 'GBR' |
| VARCHAR(n) | Variable-length string, up to n characters | VARCHAR(100) for names |
| INTEGER / INT | Whole numbers | Age, quantity, year |
| FLOAT / REAL | Decimal numbers | Price, temperature, GPS coordinates |
| BOOLEAN | True/False values | IsActive, HasPaid |
| DATE | Date values (YYYY-MM-DD) | DateOfBirth, OrderDate |
SELECT (DML — Querying)
SELECT syntax
SELECT <fields>
FROM <table(s)>
[WHERE <condition>]
[ORDER BY <field> ASC | DESC]
-- Examples:
SELECT * FROM Student;
SELECT FirstName, LastName
FROM Student
WHERE CourseID = 'C01'
ORDER BY LastName ASC;
WHERE Conditions
WHERE operators
WHERE Age = 16 -- equals
WHERE Age <> 16 -- not equal
WHERE Age > 15 -- greater than
WHERE Age >= 16 -- greater than or equal
WHERE Name LIKE 'S%' -- starts with S
WHERE Name LIKE '%son' -- ends with son
WHERE Age BETWEEN 16 AND 18 -- range (inclusive)
WHERE Age = 16 AND Grade = 'A' -- both conditions
WHERE Age = 16 OR Age = 17 -- either condition
WHERE CourseID IS NULL -- no course assigned
JOIN — Combining Tables
INNER JOIN
-- Get student names alongside their course names:
SELECT Student.FirstName, Student.LastName, Course.CourseName
FROM Student
INNER JOIN Course ON Student.CourseID = Course.CourseID;
Only INNER JOIN is required for AS Level. INNER JOIN returns rows where there is a match in BOTH tables. If a student has no course (NULL CourseID), they won't appear in the result.
INSERT, UPDATE, DELETE
DML — Modify Data
-- INSERT a new record:
INSERT INTO Student (StudentID, FirstName, LastName, CourseID)
VALUES ('S004', 'Sara', 'Ahmed', 'C02');
-- UPDATE an existing record:
UPDATE Student
SET CourseID = 'C01'
WHERE StudentID = 'S002';
-- DELETE a record:
DELETE FROM Student
WHERE StudentID = 'S003';
▶ Interactive SQL Simulator