1.3.2 Databases
1.3.2(a) Relational Databases
Modern applications need to store and retrieve large amounts of structured data efficiently. Relational databases organise this data into tables with defined relationships, making it easy to query and maintain.
Definition
A relational database organises data into tables (also called relations), where each table represents an entity. Tables are linked through relationships using keys to maintain data integrity.
Key Database Terminology
Understanding these terms is essential for working with relational databases.
Entity
Definition
An entity is a real-world object or concept that we want to store information about. Each entity becomes a table in the database.
Example
Examples of Entities:
• Student - stores information about students
• Course - stores information about courses
• Order - stores information about customer orders
• Product - stores information about items for sale
• Doctor - stores information about medical staff
• Student - stores information about students
• Course - stores information about courses
• Order - stores information about customer orders
• Product - stores information about items for sale
• Doctor - stores information about medical staff
Attribute
Definition
An attribute is a property or characteristic of an entity. Attributes become columns in a database table.
Example
Student Entity Attributes:
• StudentID
• FirstName
• LastName
• DateOfBirth
• Email
• CourseID
• StudentID
• FirstName
• LastName
• DateOfBirth
• CourseID
Primary Key
Definition
A primary key is an attribute (or combination of attributes) that uniquely identifies each record in a table. No two records can have the same primary key value, and it cannot be null.
Key Point
Primary Key Characteristics:
• Must be unique for every record
• Cannot be NULL (empty)
• Should not change over time
• Often an auto-generated ID number
• Each table should have exactly one primary key
• Must be unique for every record
• Cannot be NULL (empty)
• Should not change over time
• Often an auto-generated ID number
• Each table should have exactly one primary key
Example
Good Primary Keys:
• StudentID: S001, S002, S003...
• OrderNumber: ORD-2024-0001
• ISBN: 978-3-16-148410-0
Bad Primary Keys:
• Name (not unique - many people share names)
• Email (might change)
• Phone number (might change)
• StudentID: S001, S002, S003...
• OrderNumber: ORD-2024-0001
• ISBN: 978-3-16-148410-0
Bad Primary Keys:
• Name (not unique - many people share names)
• Email (might change)
• Phone number (might change)
Foreign Key
Definition
A foreign key is an attribute in one table that references the primary key of another table, creating a link between them.
Worked Example
Worked Example: Primary and Foreign Keys
Doctor Table:
Patient Table:
How it works:
• DoctorID is the PRIMARY KEY in Doctor table (unique identifier)
• DoctorID appears in Patient table as a FOREIGN KEY
• This links each patient to their doctor
• D001 (Dr Smith) has two patients (P001, P002)
• The foreign key MUST match an existing primary key
Doctor Table:
| DoctorID | Name | Speciality |
|---|---|---|
| D001 | Dr Smith | Cardiology |
| D002 | Dr Jones | Neurology |
| PatientID | Name | DoctorID (FK) |
|---|---|---|
| P001 | John Brown | D001 |
| P002 | Jane White | D001 |
| P003 | Bob Green | D002 |
• DoctorID is the PRIMARY KEY in Doctor table (unique identifier)
• DoctorID appears in Patient table as a FOREIGN KEY
• This links each patient to their doctor
• D001 (Dr Smith) has two patients (P001, P002)
• The foreign key MUST match an existing primary key
Secondary Key
Definition
A secondary key is an attribute that is indexed to allow faster searching. Unlike primary keys, secondary keys don't need to be unique.
Example
Secondary Key Example:
In a Patient table, "Surname" might be a secondary key - it's not unique, but you often search by surname, so indexing it makes queries faster.
In a Patient table, "Surname" might be a secondary key - it's not unique, but you often search by surname, so indexing it makes queries faster.
1.3.2(b) Entity Relationship Diagrams
Entity Relationship Diagrams (ERDs) visually represent the structure of a database, showing entities and the relationships between them.
One-to-One (1:1)
Definition
In a one-to-one relationship, each record in Table A relates to exactly one record in Table B, and vice versa.
Example
Example: Person and Passport
• Each person has exactly one passport
• Each passport belongs to exactly one person
• Each person has exactly one passport
• Each passport belongs to exactly one person
One-to-Many (1:M)
Definition
In a one-to-many relationship, one record in Table A can relate to many records in Table B, but each record in Table B relates to only one record in Table A.
Example
Example: Doctor and Patients
• One doctor can have MANY patients
• Each patient has only ONE doctor (in this system)
Example: Customer and Orders
• One customer can place MANY orders
• Each order belongs to ONE customer
• One doctor can have MANY patients
• Each patient has only ONE doctor (in this system)
Example: Customer and Orders
• One customer can place MANY orders
• Each order belongs to ONE customer
Many-to-Many (M:M)
Definition
In a many-to-many relationship, records in Table A can relate to many records in Table B, and records in Table B can relate to many records in Table A.
Key Point
Resolving Many-to-Many:
Many-to-many relationships require a JUNCTION TABLE (also called a linking table or bridge table) to implement in a relational database.
Many-to-many relationships require a JUNCTION TABLE (also called a linking table or bridge table) to implement in a relational database.
Worked Example
Worked Example: Students and Courses (M:M)
Problem:
• One student takes MANY courses
• One course has MANY students
• This is many-to-many - can't directly link!
Solution - Junction Table:
Student Table:
Course Table:
Enrolment Table (Junction):
Result:
• Alice (S001) takes Maths AND Physics
• Bob (S002) takes only Maths
• Maths course has Alice AND Bob
• The junction table resolves the M:M relationship
Problem:
• One student takes MANY courses
• One course has MANY students
• This is many-to-many - can't directly link!
Solution - Junction Table:
Student Table:
| StudentID | Name |
|---|---|
| S001 | Alice |
| S002 | Bob |
| CourseID | Title |
|---|---|
| C001 | Maths |
| C002 | Physics |
| StudentID | CourseID | Grade |
|---|---|---|
| S001 | C001 | A |
| S001 | C002 | B |
| S002 | C001 | B |
• Alice (S001) takes Maths AND Physics
• Bob (S002) takes only Maths
• Maths course has Alice AND Bob
• The junction table resolves the M:M relationship
1.3.2(c) Normalisation
When designing a database, poor structure leads to data redundancy (duplicate data), update anomalies, and wasted storage. Normalisation is the process of organising tables to minimise these problems.
Definition
Normalisation is the process of organising a database to reduce redundancy and improve data integrity by following a series of rules called normal forms.
First Normal Form (1NF)
Definition
A table is in First Normal Form (1NF) if:
• Each cell contains only atomic (single, indivisible) values
• There are no repeating groups or arrays
• Each record is unique (has a primary key)
• Each cell contains only atomic (single, indivisible) values
• There are no repeating groups or arrays
• Each record is unique (has a primary key)
Worked Example
Worked Example: Achieving 1NF
NOT in 1NF (repeating groups):
Problem: Courses column contains multiple values
IN 1NF (atomic values):
Now each cell contains ONE value only
NOT in 1NF (repeating groups):
| StudentID | Name | Courses |
|---|---|---|
| S001 | Alice | Maths, Physics, Chemistry |
| S002 | Bob | Maths, Biology |
IN 1NF (atomic values):
| StudentID | Name | Course |
|---|---|---|
| S001 | Alice | Maths |
| S001 | Alice | Physics |
| S001 | Alice | Chemistry |
| S002 | Bob | Maths |
| S002 | Bob | Biology |
Second Normal Form (2NF)
Definition
A table is in Second Normal Form (2NF) if:
• It is already in 1NF
• All non-key attributes are fully dependent on the ENTIRE primary key (no partial dependencies)
• It is already in 1NF
• All non-key attributes are fully dependent on the ENTIRE primary key (no partial dependencies)
Worked Example
Worked Example: Achieving 2NF
IN 1NF but NOT 2NF:
Primary key: (StudentID, CourseID) - composite key
Problem: StudentName depends only on StudentID, not CourseID. CourseName depends only on CourseID, not StudentID. These are PARTIAL dependencies.
IN 2NF (split into separate tables):
Student Table:
Course Table:
Enrolment Table:
Now all non-key attributes depend on the full primary key
IN 1NF but NOT 2NF:
| StudentID | CourseID | StudentName | CourseName | Grade |
|---|---|---|---|---|
| S001 | C001 | Alice | Maths | A |
| S001 | C002 | Alice | Physics | B |
| S002 | C001 | Bob | Maths | B |
Problem: StudentName depends only on StudentID, not CourseID. CourseName depends only on CourseID, not StudentID. These are PARTIAL dependencies.
IN 2NF (split into separate tables):
Student Table:
| StudentID | StudentName |
|---|---|
| S001 | Alice |
| S002 | Bob |
| CourseID | CourseName |
|---|---|
| C001 | Maths |
| C002 | Physics |
| StudentID | CourseID | Grade |
|---|---|---|
| S001 | C001 | A |
| S001 | C002 | B |
| S002 | C001 | B |
Third Normal Form (3NF)
Definition
A table is in Third Normal Form (3NF) if:
• It is already in 2NF
• No non-key attribute depends on another non-key attribute (no transitive dependencies)
• It is already in 2NF
• No non-key attribute depends on another non-key attribute (no transitive dependencies)
Worked Example
Worked Example: Achieving 3NF
IN 2NF but NOT 3NF:
Problem: TutorName and TutorOffice depend on TutorID, not StudentID. StudentID → TutorID → TutorName (transitive dependency).
IN 3NF (remove transitive dependency):
Student Table:
Tutor Table:
Now no non-key attribute depends on another non-key attribute
IN 2NF but NOT 3NF:
| StudentID | Name | TutorID | TutorName | TutorOffice |
|---|---|---|---|---|
| S001 | Alice | T01 | Mr Brown | Room 12 |
| S002 | Bob | T01 | Mr Brown | Room 12 |
| S003 | Charlie | T02 | Ms Green | Room 15 |
IN 3NF (remove transitive dependency):
Student Table:
| StudentID | Name | TutorID |
|---|---|---|
| S001 | Alice | T01 |
| S002 | Bob | T01 |
| S003 | Charlie | T02 |
| TutorID | TutorName | TutorOffice |
|---|---|---|
| T01 | Mr Brown | Room 12 |
| T02 | Ms Green | Room 15 |
Exam Tip
Benefits of Normalisation:
• Reduces data redundancy (no duplicate data)
• Prevents update anomalies (change once, applies everywhere)
• Saves storage space
• Improves data integrity
• Makes database easier to maintain
• Reduces data redundancy (no duplicate data)
• Prevents update anomalies (change once, applies everywhere)
• Saves storage space
• Improves data integrity
• Makes database easier to maintain
1.3.2(d) SQL (Structured Query Language)
SQL is the standard language for interacting with relational databases. It allows you to create, query, update, and delete data.
Definition
SQL (Structured Query Language) is a declarative programming language used to manage and manipulate relational databases.
SELECT Queries
Worked Example
Basic SELECT:
This query:
• Selects three columns
• From the Student table
• Only where Year equals 12
• Sorted alphabetically by LastName
sql
SELECT FirstName, LastName, Email
FROM Student
WHERE Year = 12
ORDER BY LastName ASC;
This query:
• Selects three columns
• From the Student table
• Only where Year equals 12
• Sorted alphabetically by LastName
Worked Example
Using Wildcards:
Wildcards:
• % matches any number of characters
• _ matches exactly one character
• LIKE 'A%' finds anything starting with A
• LIKE '_ones' finds 5-letter words ending in 'ones'
sql
-- Select ALL columns from Student
SELECT * FROM Student;
-- Select products where name starts with 'A'
SELECT * FROM Product WHERE Name LIKE 'A%';
Wildcards:
• % matches any number of characters
• _ matches exactly one character
• LIKE 'A%' finds anything starting with A
• LIKE '_ones' finds 5-letter words ending in 'ones'
JOIN Queries
Definition
A JOIN combines rows from two or more tables based on a related column (usually a foreign key relationship).
Worked Example
Worked Example: JOIN Query
Tables:
• Student: StudentID, Name, TutorID
• Tutor: TutorID, TutorName, Department
Result: Names of students and their tutors, but only for the Computer Science department
Tables:
• Student: StudentID, Name, TutorID
• Tutor: TutorID, TutorName, Department
sql
SELECT Student.Name, Tutor.TutorName, Tutor.Department
FROM Student
JOIN Tutor ON Student.TutorID = Tutor.TutorID
WHERE Tutor.Department = 'Computer Science';
Result: Names of students and their tutors, but only for the Computer Science department
Data Modification Commands
Worked Example
INSERT - Add new records:
UPDATE - Modify existing records:
DELETE - Remove records:
CREATE TABLE - Make a new table:
ALTER TABLE - Change table structure:
sql
INSERT INTO Student (Name, Year)
VALUES ('Alice', 12);
UPDATE - Modify existing records:
sql
UPDATE Student
SET Year = 13
WHERE StudentID = 'S001';
DELETE - Remove records:
sql
DELETE FROM Student
WHERE Year = 11;
CREATE TABLE - Make a new table:
sql
CREATE TABLE Student (
StudentID INT PRIMARY KEY,
Name VARCHAR(50),
Year INT,
Email VARCHAR(100)
);
ALTER TABLE - Change table structure:
sql
ALTER TABLE Student
ADD Email VARCHAR(100);
Key Point
SQL Command Summary:
• SELECT - retrieve data from tables
• INSERT INTO - add new records
• UPDATE - modify existing records
• DELETE FROM - remove records
• CREATE TABLE - create a new table
• ALTER TABLE - modify table structure
• DROP TABLE - delete a table entirely
• JOIN - combine data from multiple tables
• WHERE - filter results by condition
• ORDER BY - sort results (ASC/DESC)
• LIKE - pattern matching with wildcards
• SELECT - retrieve data from tables
• INSERT INTO - add new records
• UPDATE - modify existing records
• DELETE FROM - remove records
• CREATE TABLE - create a new table
• ALTER TABLE - modify table structure
• DROP TABLE - delete a table entirely
• JOIN - combine data from multiple tables
• WHERE - filter results by condition
• ORDER BY - sort results (ASC/DESC)
• LIKE - pattern matching with wildcards
1.3.2(e) Referential Integrity
Definition
Referential integrity ensures that relationships between tables remain valid. A foreign key must either match an existing primary key value or be null.
Key Point
Referential Integrity Rules:
• Cannot insert a record with a foreign key that doesn't exist in the parent table
• Cannot delete a parent record if child records reference it
• Cannot update a primary key if foreign keys reference it
Enforcement:
Modern DBMSs enforce these rules automatically when foreign key constraints are defined.
• Cannot insert a record with a foreign key that doesn't exist in the parent table
• Cannot delete a parent record if child records reference it
• Cannot update a primary key if foreign keys reference it
Enforcement:
Modern DBMSs enforce these rules automatically when foreign key constraints are defined.
Worked Example
Referential Integrity Example
Tables:
• Department: DeptID (PK), DeptName
• Employee: EmpID (PK), Name, DeptID (FK)
Valid Operation:
Insert Employee with DeptID = 'D001' only if 'D001' exists in Department table
Invalid Operation:
Insert Employee with DeptID = 'D999' if 'D999' doesn't exist - would violate referential integrity
Cascading Actions:
• ON DELETE CASCADE: Delete child records when parent deleted
• ON UPDATE CASCADE: Update foreign keys when primary key changes
Tables:
• Department: DeptID (PK), DeptName
• Employee: EmpID (PK), Name, DeptID (FK)
Valid Operation:
Insert Employee with DeptID = 'D001' only if 'D001' exists in Department table
Invalid Operation:
Insert Employee with DeptID = 'D999' if 'D999' doesn't exist - would violate referential integrity
Cascading Actions:
• ON DELETE CASCADE: Delete child records when parent deleted
• ON UPDATE CASCADE: Update foreign keys when primary key changes
1.3.2(f) Query by Example (QBE)
Definition
Query by Example (QBE) is a visual method of querying databases where users fill in example values in a table template to specify what data they want.
Key Point
QBE Characteristics:
• Visual interface - no need to learn SQL syntax
• Fill in values in a grid/table format
• Criteria entered in same row = AND
• Criteria entered in different rows = OR
• Check boxes select which fields to display
• Visual interface - no need to learn SQL syntax
• Fill in values in a grid/table format
• Criteria entered in same row = AND
• Criteria entered in different rows = OR
• Check boxes select which fields to display
Worked Example
QBE Example
Query: Find all Year 12 students studying Computer Science
How to read this:
• Row criteria: Year = 12 AND Course = 'CompSci'
• Show ticked for StudentID and Name only
• Result shows StudentID and Name of matching records
For OR conditions, use multiple rows:
This finds Year 12 students OR students studying Maths
Query: Find all Year 12 students studying Computer Science
| Field | StudentID | Name | Year | Course |
|---|---|---|---|---|
| Show | ☑ | ☑ | ☐ | ☐ |
| Criteria | 12 | CompSci |
• Row criteria: Year = 12 AND Course = 'CompSci'
• Show ticked for StudentID and Name only
• Result shows StudentID and Name of matching records
For OR conditions, use multiple rows:
| Field | Year | Course |
|---|---|---|
| Criteria Row 1 | 12 | |
| Criteria Row 2 | Maths |
Exam Tip
Exam Comparison: SQL vs QBE
| Feature | SQL | QBE |
|---|---|---|
| User interface | Text commands | Visual grid |
| Learning curve | Steeper | Easier for beginners |
| Flexibility | Very flexible | Limited but intuitive |
| Precision | Exact syntax required | Visual selection |
| Use case | Programmers, power users | End users, simple queries |