Unit 12.4A · Term 4 (Revision)
Databases Revision
This revision lesson covers relational databases — their structure, terminology, keys, normalization, and entity-relationship modelling.
Learning Objectives
- 11.4.1.1 Describe relational databases and their purpose
- 11.4.1.2 Use the terms attribute, object, index, record, table and tuple
- 11.4.1.3 Explain the difference between primary, composite and foreign key
- 11.4.1.4 Define data types when creating a database
- 11.4.1.5 Define the connections between tables (1–3NF)
- 11.4.1.6 Define the connections between tables
- 11.4.1.7 Create an entity-relationship (ER) model
Key Concepts Review
Database Terminology
| Term | Definition | Example |
|---|---|---|
| Table (Relation) | A structured set of data organized in rows and columns | Students table |
| Record (Tuple) | A single row in a table — one complete entry | One student's data |
| Field (Attribute) | A single column — one category of data | Name, Age, Grade |
| Primary Key | Unique identifier for each record in a table | StudentID |
| Foreign Key | A field that links to the primary key of another table | ClassID in Students → Classes table |
| Composite Key | Two or more fields combined to form a unique key | (StudentID, CourseID) |
| Index | A data structure that speeds up searching in a table | Index on LastName field |
Normalization (1NF → 2NF → 3NF)
| Normal Form | Rule |
|---|---|
| 1NF | No repeating groups — each cell contains a single value; each row is unique |
| 2NF | In 1NF + no partial dependencies (all non-key fields depend on the entire primary key) |
| 3NF | In 2NF + no transitive dependencies (non-key fields depend only on the primary key, not on other non-key fields) |
Relationships & ER Diagrams
| Relationship | Description | Example |
|---|---|---|
| One-to-One | One record in table A links to one record in table B | Person → Passport |
| One-to-Many | One record in table A links to many records in table B | Teacher → Students |
| Many-to-Many | Many records in A link to many in B (requires junction table) | Students ↔ Courses |
Revision Tasks
Remember
Define: primary key, foreign key, composite key, attribute, tuple.
Understand
Explain the difference between 1NF, 2NF, and 3NF with an example.
Apply
Draw an ER diagram for a library system with tables: Books, Members, Loans.
Analyze
Given an unnormalized table of student grades, normalize it to 3NF.
Self-Check Quiz
Q1: What is a foreign key?
A field in one table that
references the primary key of another table, creating a link between the two tables.
Q2: What does 2NF require beyond 1NF?
No partial dependencies — every
non-key field must depend on the entire primary key, not just part of it.
Q3: What type of relationship requires a junction table?
Many-to-Many — a junction table
(with composite key) is created to break it into two One-to-Many relationships.