You are viewing a free preview of this lesson.
Subscribe to unlock all 10 lessons in this course and every other course on LearningBro.
Relational Database Concepts
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:
- Unique — no two records can share the same primary key value.
- Not null — every record must have a value for the primary key.
- 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.