CS35: Relational Databases
Database concepts including tables, records, fields, primary keys, foreign keys, and how relational databases eliminate data inconsistency and redundancy.
📋 What is a Database?
Definition: A database is an organised, structured collection of data that can be easily accessed, managed, and updated. A relational database stores data in multiple linked tables that relate to each other.
Databases are used everywhere: schools store student records, shops store product and customer data, banks store account information. A well-designed database makes it easy to find, add, update, and delete data efficiently and accurately.
- Databases store data in a structured, organised way
- They allow efficient searching, sorting, and filtering
- Multiple users can access and update data simultaneously
- Data is kept consistent and accurate
- Relational databases link tables together using keys
📊 Database Structure
Tables
Definition: A table is a structured set of data organised in rows and columns. Each table stores data about one type of entity (e.g. a table for Students, a table for Courses).
Fields (Columns)
Definition: A field is a single category of data within a table. Each field has a name and a data type (e.g. text, integer, date). A field stores one piece of information for each record.
- Each column in a table represents one field
- Fields have specific data types (text, number, date, Boolean)
- Examples: StudentID, FirstName, LastName, DateOfBirth
- Each field must have a unique name within its table
Records (Rows)
Definition: A record is a single, complete set of data in a table. Each row represents one item or entity, containing values for every field.
- Each row in a table represents one record
- A record contains data for all fields in the table
- Each record should be uniquely identifiable
- Example: one student's complete information (ID, name, DOB, etc.)
Example: Students Table
| StudentID | FirstName | LastName | Form | DateOfBirth |
|---|---|---|---|---|
| 1001 | Alice | Smith | 10A | 2008-03-15 |
| 1002 | Bob | Jones | 10B | 2008-07-22 |
| 1003 | Clara | Patel | 10A | 2008-11-09 |
Fields: StudentID, FirstName, LastName, Form, DateOfBirth
Records: 3 (one per student)
Each cell contains one piece of data (one field value for one record)
🔑 Primary Keys
Definition: A primary key is a field (or combination of fields) that uniquely identifies each record in a table. No two records can have the same primary key value. A primary key cannot be left empty (null).
- Must be unique - no two records share the same primary key value
- Cannot be null (empty) - every record must have a primary key value
- Usually a number (ID field) rather than a name (names may not be unique)
- Often auto-incremented (the database assigns the next number automatically)
- Every table should have a primary key
Why Not Use Names as Primary Keys?
In a school, two students could have the same name "John Smith." A name field would not be unique, so it cannot be a primary key. A StudentID like "1001" is unique - no two students share the same ID.
Composite Primary Key
Sometimes a single field isn't enough to uniquely identify a record. A composite primary key uses two or more fields together to create uniqueness. Example: In an "Enrolments" table, neither StudentID nor CourseID alone is unique (a student takes many courses, a course has many students). But the combination of StudentID + CourseID is unique for each enrolment.
🔗 Foreign Keys
Definition: A foreign key is a field in one table that references the primary key of another table. It creates a link (relationship) between the two tables, allowing them to be connected.
- A foreign key in one table points to a primary key in another table
- It creates a relationship between tables
- Foreign key values must match an existing primary key value in the related table
- Foreign keys CAN be repeated (unlike primary keys)
- Foreign keys CAN be null (if the relationship is optional)
Example: Linking Tables
Students Table:
| StudentID (PK) | FirstName | LastName | TutorGroupID (FK) |
|---|---|---|---|
| 1001 | Alice | Smith | TG01 |
| 1002 | Bob | Jones | TG02 |
TutorGroups Table:
| TutorGroupID (PK) | TutorName | Room |
|---|---|---|
| TG01 | Mr Williams | R12 |
| TG02 | Mrs Khan | R15 |
The TutorGroupID field in the Students table is a foreign key that references the primary key in the TutorGroups table. This links each student to their tutor group.
📈 Entity-Relationship Diagrams
Definition: An entity-relationship (E-R) diagram is a visual representation of the data and relationships in a database. It shows entities (tables), their attributes (fields), and how they relate to each other.
Components of an E-R Diagram
- Entity: A thing about which data is stored (represented by a rectangle). Each entity becomes a table.
- Attribute: A property of an entity (represented by an oval, or listed inside the rectangle). Each attribute becomes a field.
- Relationship: A link between entities (represented by a line connecting them).
Types of Relationships
| Relationship Type | Notation | Description | Example |
|---|---|---|---|
| One-to-One | 1 : 1 | One record in table A relates to exactly one record in table B | Person : Passport (one person has one passport) |
| One-to-Many | 1 : M | One record in table A relates to many records in table B | Customer : Orders (one customer can have many orders) |
| Many-to-Many | M : N | Many records in table A relate to many records in table B | Students : Courses (many students take many courses) |
Important: Many-to-many relationships cannot be directly implemented in a relational database. They must be resolved using a linking (junction) table that breaks the M:N relationship into two 1:M relationships.
Resolving a Many-to-Many Relationship
Students and Courses have a M:N relationship (many students take many courses; many courses have many students). We resolve this by creating an "Enrolments" linking table:
Students 1 : M Enrolments M : 1 Courses
The Enrolments table contains StudentID (FK) and CourseID (FK), forming a composite primary key.
✂️ Eliminating Data Redundancy and Inconsistency
Data Redundancy: Data redundancy is when the same data is stored in multiple places in a database. This wastes storage space and can lead to data inconsistency.
Data Inconsistency: Data inconsistency is when the same data has different values in different places. This happens when redundant data is updated in one place but not in others.
Problems with Redundant Data
Example: Flat File with Redundancy
| OrderID | CustomerName | CustomerAddress | Item | Price |
|---|---|---|---|---|
| 1 | Alice Smith | 12 Oak Lane | Widget A | 5.00 |
| 2 | Alice Smith | 12 Oak Lane | Widget B | 7.50 |
| 3 | Alice Smith | 45 Elm Road | Widget C | 3.00 |
Alice's address is stored three times (redundancy). In Order 3, her address is different from Orders 1 and 2 (inconsistency). Which is correct? We don't know.
How Relational Databases Solve This
By splitting data into separate related tables, each piece of data is stored only ONCE:
Solution: Normalised Tables
Customers Table: CustomerID, CustomerName, CustomerAddress (stored ONCE)
Orders Table: OrderID, CustomerID (FK), Item, Price
Alice's address is stored in only one place. If she moves, we update it once in the Customers table. No redundancy, no inconsistency.
Benefits of Relational Databases:
No redundancy: each piece of data stored only once
No inconsistency: one change updates everywhere via relationships
Smaller file sizes: less duplicated data
Easier maintenance: update one record, not many copies
Data integrity: foreign keys ensure valid relationships
No redundancy: each piece of data stored only once
No inconsistency: one change updates everywhere via relationships
Smaller file sizes: less duplicated data
Easier maintenance: update one record, not many copies
Data integrity: foreign keys ensure valid relationships
⚠️ Common Mistakes to Avoid
| Mistake | Why It's Wrong | How to Fix It |
|---|---|---|
| Using a name as a primary key | Names are not necessarily unique | Use a unique ID number as primary key |
| Confusing primary key and foreign key | They serve different purposes | PK = uniquely identifies records; FK = links to another table's PK |
| Saying foreign keys must be unique | Foreign keys CAN repeat in a table | Only primary keys must be unique; foreign keys can repeat |
| Trying to create M:N relationship directly | Relational databases don't support M:N directly | Use a linking/junction table to resolve M:N |
| Confusing fields and records | Fields = columns; Records = rows | Field = category of data; Record = one complete set of data |
❓ Practice Questions
Q1: Define the terms field, record, and table in the context of a database.
Q2: What is a primary key? Why is it important?
Q3: Explain the difference between a primary key and a foreign key.
Q4: What is data redundancy and how does a relational database eliminate it?
Q5: How would you resolve a many-to-many relationship in a relational database?
✅ Answers
- A field is a single category of data in a table (a column). A record is a complete set of data for one entity in a table (a row). A table is a structured collection of related data organised in rows and columns.
- A primary key is a field that uniquely identifies each record in a table. It must be unique (no two records can have the same value) and cannot be null. It is important because it allows each record to be uniquely identified and retrieved, and it enables relationships between tables via foreign keys.
- A primary key uniquely identifies each record in its own table. A foreign key is a field in one table that references the primary key of another table, creating a link between them. Primary keys must be unique; foreign keys can repeat. Every table has one primary key; foreign keys only exist when there is a relationship between tables.
- Data redundancy is when the same data is stored in multiple places, wasting space and causing inconsistency. A relational database eliminates redundancy by splitting data into separate related tables, so each piece of data is stored only once. When data needs to be linked, foreign keys are used instead of duplicating the data.
- A many-to-many relationship is resolved by creating a linking (junction) table between the two entities. The linking table contains foreign keys referencing the primary keys of both tables, converting the M:N relationship into two 1:M relationships. For example, Students M:N Courses is resolved by creating an Enrolments table with StudentID (FK) and CourseID (FK).
🎯 Exam Tips
- Know the definitions: table, field, record, primary key, foreign key
- Primary key = unique identifier; Foreign key = link to another table
- Always explain WHY primary keys must be unique (to identify each record)
- For redundancy questions, give a concrete example of duplicated data
- Remember: M:N relationships need a linking table to resolve
- Be able to draw simple E-R diagrams showing 1:1, 1:M, and M:N relationships
⚠️ Common Errors
✗ Thinking a flat file and a relational database are the same ✓ A flat file stores all data in a single table with redundant data. A relational database splits data into linked tables, reducing redundancy and improving data integrity.
✗ Confusing primary key and foreign key ✓ A primary key uniquely identifies each record in a table. A foreign key is a field in one table that references the primary key of another table, creating the relationship between them.
✗ Forgetting that a primary key must be unique and cannot be NULL ✓ Every record must have a unique primary key value — no duplicates and no empty (NULL) values. This ensures each record can be uniquely identified.
✗ Not understanding why normalisation reduces data redundancy ✓ Without normalisation, the same data (e.g. customer name) is repeated in multiple records. Normalisation splits this into separate tables linked by keys, so each piece of data is stored only once.
✍️ Model Answer
Full-Mark Response
A school stores student data in a flat file with fields: StudentID, Name, TutorGroup, TutorName, TutorRoom. Explain two problems with this flat file design, and describe how a relational database would solve them. [5 marks]
Problem 1: Data redundancy — the TutorName and TutorRoom are repeated for every student in the same tutor group. If 30 students share a tutor, the tutor's name and room appear 30 times. Problem 2: Update anomaly — if a tutor changes room, every record for their students must be updated. Missing one record causes data inconsistency. Relational database solution: Students table: StudentID (PK), Name, TutorGroup (FK) Tutors table: TutorGroup (PK), TutorName, TutorRoom The TutorGroup field in Students is a foreign key linking to the Tutors table. Now each tutor's name and room are stored only once. If a tutor changes room, only ONE record in the Tutors table needs updating, eliminating redundancy and update anomalies.
📊 AO Deep Dive
Assessment Objective Analysis
AO1 (Computational Thinking — 40%): Demonstrate knowledge and understanding of the principles and concepts of computer science, including relational databases: primary keys, foreign keys and normalisation for AQA 8525, OCR J277 & Edexcel 1CP2.
AO2 (Application — 40%): Apply knowledge and understanding of computer science, including relational databases: primary keys, foreign keys and normalisation to analyse problems in computational terms and to design, write and evaluate solutions.
AO3 (Evaluation — 20%): Evaluate the effectiveness, correctness and efficiency of computational solutions, including relational databases: primary keys, foreign keys and normalisation, and make reasoned judgements about trade-offs.
📝 Exam Technique
GCSE Computer Science Exam Tips:
Flat file: single table, redundancy, anomalies. Relational DB: linked tables, keys, less redundancy. Primary key: unique, NOT NULL, identifies records. Foreign key: links to another table's PK. Normalisation: reduces redundancy, prevents insert/update/delete anomalies. Entity-relationship diagrams show relationships (1:1, 1:M, M:N). Always name tables, fields, and key types in your answers.
Flat file: single table, redundancy, anomalies. Relational DB: linked tables, keys, less redundancy. Primary key: unique, NOT NULL, identifies records. Foreign key: links to another table's PK. Normalisation: reduces redundancy, prevents insert/update/delete anomalies. Entity-relationship diagrams show relationships (1:1, 1:M, M:N). Always name tables, fields, and key types in your answers.
📝 Exam Questions by Topic
🎬 Video Resources
Ready to ace your GCSE Computer Science exams?
Get the best revision books and guides to boost your grades.