OCR H446 · A Level Computer Science · ~14 min read
Notes
—
Video
—
Slides
—
Worksheet
—
Quiz
Relational Databases
A relational database organises data into tables (also called relations). Each table represents a single type of entity (thing). Tables are linked to each other through shared attributes, enabling complex queries across multiple related tables.
Key terminology:
Table / Relation: a collection of related data about one type of entity (e.g. a Student table, a Course table).
Row / Record / Tuple: a single data item within a table (one student, one course).
Column / Field / Attribute: a named piece of information stored about each entity (e.g. StudentID, Name, DOB).
Primary key: a field (or combination of fields) that uniquely identifies each row in a table. No two rows can have the same primary key value. It must never be null.
Foreign key: a field in one table that refers to the primary key in another table. This is how tables are linked/related. The foreign key enforces referential integrity.
Composite key: a primary key made up of two or more fields, used when no single field is unique on its own.
Primary and Foreign Keys
Example — two related tables:
Student table
StudentID (PK)
FirstName
LastName
DOB
S001
Aisha
Khan
2006-03-14
S002
Ben
Smith
2005-11-20
Enrolment table
EnrolmentID (PK)
StudentID (FK)
CourseCode (FK)
E01
S001
CS101
E02
S001
MA201
E03
S002
CS101
The StudentID in the Enrolment table is a foreign key referencing the StudentID primary key in the Student table. This enforces referential integrity — you cannot enrol a student who does not exist in the Student table.
Entity-Relationship (ER) Diagrams
An ER diagram is a graphical representation of the entities in a database and the relationships between them. It is used during the design phase to plan database structure before writing any SQL or creating actual tables.
Components of an ER Diagram
Entity: represented as a rectangle — a type of object stored in the database (e.g. Student, Course, Teacher).
Attribute: represented as an oval connected to an entity — a piece of data about that entity (e.g. StudentID, Name).
Relationship: represented as a diamond connecting two entities — describes how entities are related (e.g. "enrolls on", "teaches").
Cardinality (Relationship Types)
Cardinality describes how many instances of one entity relate to instances of another:
Type
Symbol (crow's foot)
Example
One-to-one (1:1)
|—|
A person has one passport; a passport belongs to one person.
One-to-many (1:M)
|—<
One teacher teaches many students; each student has one form tutor.
Many-to-many (M:N)
>—<
Students enrol on many courses; each course has many students.
Resolving Many-to-Many Relationships
A many-to-many relationship cannot be directly implemented in a relational database. It must be resolved by introducing a link table (also called a junction table, associative table, or bridge table). The link table has foreign keys referencing both original tables, and its primary key is a composite key made up of these two foreign keys.
Example: Student ↔ Course (M:N) resolves to:
Student table (StudentID PK, Name, …)
Course table (CourseCode PK, CourseName, …)
Enrolment table (StudentID FK + CourseCode FK = composite PK, Grade, DateEnrolled)
Referential Integrity
Referential integrity is a constraint that ensures foreign key values always refer to existing primary key values. A DBMS enforces referential integrity to prevent:
Adding a row with a foreign key value that does not exist in the referenced table (orphan record).
Deleting a primary key row that is still referenced by a foreign key in another table.
When referential integrity is enforced, the DBMS can either cascade (automatically delete/update child records when the parent is deleted/updated) or restrict (prevent the deletion/update).
Designing a Relational Database
Identify the entities (things the database needs to store).
Identify the attributes for each entity.
Identify the relationships between entities and their cardinality.
Choose or create a primary key for each table (ideally a meaningless surrogate key like an ID number).
Resolve any many-to-many relationships using link tables.
Add foreign keys to implement the relationships.
Apply normalisation (see 1.3.2c) to remove redundancy.
Exam tip: When asked to draw an ER diagram, use rectangles for entities, diamonds for relationships, and show cardinality using crow's foot notation or 1, M, N labels at each end of the relationship line. Always state whether each relationship is 1:1, 1:M, or M:N.
Exam tip: Remember: M:N relationships CANNOT be implemented directly in a relational database — you MUST create a link table with a composite primary key made from the two foreign keys.
⚠ Common Mistakes
Confusing primary key and foreign key — a primary key UNIQUELY IDENTIFIES rows in its OWN table. A foreign key references the primary key of ANOTHER table.
Drawing M:N relationships directly between tables in implementation — you must always introduce a link table. The link table's composite PK ensures each unique pair (student, course) can only appear once.
Forgetting that primary keys cannot be NULL or duplicate — primary keys must be unique and not null. This is the entity integrity constraint.
✓ Notes completed!
▶
Video coming soon
Click to advance · Arrow keys also work
Click slide or press arrow keys to navigate
✍
Worksheet — 1.3.2a Relational Databases & ER Modelling
8 questions · 20 marks · instantly marked
Q1Explain the purpose of a primary key in a relational database table. State TWO rules that a primary key must satisfy.[3 marks]
✓ Mark scheme
A primary key uniquely identifies each row/record within a table — no two rows can have the same primary key value [1]; Rule 1: it must be unique — no two rows can have the same value [1]; Rule 2: it must not be null — a primary key cannot be empty/missing [1].
Q2What is a foreign key? Explain how a foreign key is used to link two tables.[3 marks]
✓ Mark scheme
A foreign key is a field (attribute) in one table that holds the value of the primary key from another table [1]; it creates a link/relationship between the two tables — a row in one table can reference (or 'point to') a specific row in another table [1]; e.g. a StudentID field in an Enrolment table (foreign key) references the StudentID primary key in the Student table — so each enrolment row is associated with a specific student [1].
Q3Describe the three types of relationship cardinality in an ER diagram. Give a real-world example for each.[3 marks]
✓ Mark scheme
One-to-one (1:1): one instance of entity A relates to exactly one instance of entity B, and vice versa — e.g. a person has one passport; a passport belongs to one person [1]; one-to-many (1:M): one instance of entity A relates to many instances of entity B, but each B relates to only one A — e.g. one teacher teaches many classes; each class has one teacher [1]; many-to-many (M:N): one instance of A can relate to many instances of B, and one instance of B can relate to many instances of A — e.g. a student can enrol on many courses; each course can have many students [1].
Q4Explain why a many-to-many relationship cannot be directly implemented in a relational database, and describe how it is resolved.[4 marks]
✓ Mark scheme
A M:N relationship cannot be directly stored in two tables because there is no single column that could hold references to multiple rows of another table without violating the atomic (single value per field) rule of relational databases [1]; it is resolved by creating a link table (junction/associative table) between the two entities [1]; the link table contains foreign keys referencing the primary key of each of the original two tables [1]; the composite primary key of the link table is made up of these two foreign keys — this ensures each unique combination of the two entities can only appear once [1].
Q5A library database stores information about Members and Books. Members can borrow many Books, and a Book can be borrowed by many Members. Design the table structure to implement this, stating the primary key and any foreign keys for each table.[4 marks]
✓ Mark scheme
Member table: MemberID (PK), Name, EmailAddress, etc. [1]; Book table: BookID (PK), Title, Author, ISBN, etc. [1]; Loan table (link table): LoanID or composite PK of (MemberID FK + BookID FK), DueDate, ReturnDate — the table has MemberID as FK referencing Member.MemberID and BookID as FK referencing Book.BookID [1]; the M:N relationship is resolved by the Loan table which holds one row for each member-book borrowing instance, ensuring the relationship is tracked without data redundancy [1].
Q6Explain what referential integrity is and why it is important in a relational database.[2 marks]
✓ Mark scheme
Referential integrity is a constraint that ensures every foreign key value in a table must match an existing primary key value in the referenced table (no orphan records) [1]; it is important because it prevents inconsistent data — for example, it prevents an enrolment record pointing to a student who does not exist, or a student being deleted when they still have enrolment records [1].
Q7What is a composite key? Give an example where a composite key would be used.[2 marks]
✓ Mark scheme
A composite key is a primary key made up of two or more fields/attributes — used when no single field is unique enough on its own to identify each row [1]; example: in a Student-Course link table (Enrolment), neither StudentID alone nor CourseCode alone is unique — a student may enrol on many courses — but the combination (StudentID + CourseCode) is unique for each enrolment [1].
Q8Distinguish between an entity and an attribute in an ER diagram. Give one example of each in the context of a hospital database.[2 marks]
✓ Mark scheme
An entity is a category/type of object/person/thing stored in the database — it becomes a table — e.g. Patient, Doctor, Appointment [1]; an attribute is a specific piece of information stored about an entity — it becomes a column/field in the table — e.g. Patient.PatientID, Doctor.Specialisation, Appointment.Date [1].
Topic Quiz
1 of 15
You scored
out of 15
🎯
Mini Test — 1.3.2a Relational Databases
10 questions · 10 marks · 10 minutes
5 MCQ + 5 short answer
⏱10:00
10 marks
Section A — Multiple Choice
Q1What is the purpose of a primary key?
Q2In an ER diagram, what does a diamond shape represent?
Q3A student can study many subjects, and each subject is studied by many students. What type of relationship is this?
Q4How is a many-to-many relationship resolved in a relational database?
Q5What does referential integrity ensure?
Section B — Short Answer
Q6State what a foreign key is and what it references.
Mark schemeA foreign key is a field in one table that holds the value of the primary key of another table. It creates a link between the two tables and enforces referential integrity. [1 mark]
Q7State ONE rule that a primary key must always satisfy.
Mark schemeA primary key must be unique (no two rows can have the same value) OR it must never be null (cannot be empty). [1 mark]
Q8What is a composite key? State when one would be used.
Mark schemeA composite key is a primary key made from two or more fields. It is used when no single field is unique enough on its own — for example, in a link table where the combination of two foreign keys forms the unique identifier. [1 mark]
Q9In an ER diagram, what does an oval represent?
Mark schemeAn oval represents an attribute — a specific piece of data/information stored about an entity (e.g. StudentID, Name, DOB). [1 mark]
Q10State the difference between a one-to-one and a one-to-many relationship. Give one example of each.
Mark scheme1:1 — each instance of entity A relates to at most one instance of entity B, e.g. a person has one passport. 1:M — one instance of entity A relates to many instances of entity B, but each B relates to only one A, e.g. one customer places many orders. [1 mark]