Relational Database Concepts

A relational database organises data into tables (also called relations). Each table stores data about a single entity — for example, a Customers table, an Orders table, or a Products table. Understanding the structure and terminology of relational databases is essential for A-Level Computer Science.

---

Key Terminology

Term	Definition
Table (Relation)	A structured collection of data about one entity, organised into rows and columns
Record (Row / Tuple)	A single entry in a table representing one instance of the entity
Field (Column / Attribute)	A single piece of data stored for each record — e.g. FirstName, DateOfBirth
Primary Key	A field (or combination of fields) that uniquely identifies each record in a table
Foreign Key	A field in one table that references the primary key of another table, creating a link between them
Composite Key	A primary key made up of two or more fields combined
Candidate Key	Any field (or combination of fields) that could serve as the primary key
Secondary Key	A field that is indexed for faster searching but is not the primary key

---

Tables, Records, and Fields

Consider a simple Students table:

StudentID	FirstName	Surname	DateOfBirth	Form
1001	Alice	Chen	2007-09-15	10A
1002	Bob	Patel	2007-03-22	10B
1003	Carol	Smith	2008-01-10	10A

• The table is called Students.
• Each row is a record — for example, the row for Alice Chen is one record.
• Each column is a field — FirstName, Surname, DateOfBirth, and Form are all fields.
• StudentID is the primary key because it uniquely identifies each student.

Data Types

Every field has a data type that determines what values it can hold:

Data Type	Example Use	Example Value
INTEGER	StudentID	1001
VARCHAR(n)	FirstName (up to n characters)	'Alice'
DATE	DateOfBirth	'2007-09-15'
BOOLEAN	IsActive	TRUE
FLOAT / REAL	AverageScore	72.5

Choosing the correct data type is important for data integrity and storage efficiency.

---

Primary Keys

A primary key must be:

1. Unique — no two records can share the same primary key value.
2. Not null — every record must have a value for the primary key.
3. Immutable — ideally, the value should not change over time.

Natural vs. Surrogate Keys

• A natural key uses real-world data (e.g. NationalInsuranceNumber).
• A surrogate key is an artificially generated value (e.g. an auto-incrementing StudentID).

Surrogate keys are often preferred because natural data can change or contain duplicates in unexpected ways.

Composite Keys

Sometimes no single field uniquely identifies a record. For example, in a StudentModules table recording which students take which modules:

StudentID	ModuleID	EnrolmentDate
1001	CS101	2024-09-01
1001	CS102	2024-09-01
1002	CS101	2024-09-01

Here, neither StudentID nor ModuleID alone is unique. The composite primary key is (StudentID, ModuleID).

---

Foreign Keys and Relationships

A foreign key is a field in one table that references the primary key of another table. This creates a relationship between the two tables.

Consider two tables:

Departments

DeptID	DeptName
D1	Computer Science
D2	Mathematics

Teachers

TeacherID	Name	DeptID
T1	Dr Williams	D1
T2	Ms Johnson	D2
T3	Mr Lee	D1

In the Teachers table, DeptID is a foreign key referencing Departments.DeptID. This means:

• Each teacher belongs to exactly one department.
• The database enforces referential integrity — you cannot assign a teacher to a department that does not exist.

Types of Relationships

Relationship	Description	Example
One-to-One (1:1)	Each record in Table A relates to exactly one record in Table B	Person ↔ Passport
One-to-Many (1:M)	One record in Table A relates to many records in Table B	Department → Teachers
Many-to-Many (M:M)	Many records in A relate to many records in B	Students ↔ Modules

A many-to-many relationship cannot be directly represented in a relational database. It must be broken down using a junction table (also called a linking table or bridge table), which contains foreign keys referencing both tables.

---

Referential Integrity

Referential integrity ensures that relationships between tables remain consistent. The database will prevent:

• Inserting a record with a foreign key value that does not exist in the referenced table.
• Deleting a record from a parent table if related records exist in a child table (unless cascade rules are set).

---

Worked Example

Scenario: A school wants to store data about students, courses, and enrolments.

Entities identified: Student, Course, Enrolment.

Tables:

• Student(StudentID, FirstName, Surname, Email)
• Course(CourseID, CourseName, TeacherID)
• Enrolment(StudentID, CourseID, EnrolmentDate, Grade)

Enrolment is the junction table resolving the many-to-many relationship between Student and Course. Its composite primary key is (StudentID, CourseID).

---

Exam Tips

• Always state what makes a good primary key: unique, not null, stable.
• When identifying foreign keys, say which table they reference and which field they link to.
• Remember that many-to-many relationships require a junction table — this is a very common exam question.
• Use correct terminology: say "relation" or "table," "tuple" or "record," "attribute" or "field."
• If asked to draw a table, show at least three sample records to demonstrate how the key structure works.