Modern applications need to store and retrieve large amounts of structured data efficiently. Relational databases organise this data into tables with defined relationships, making it easy to query and maintain.
Definition
A relational database organises data into tables (also called relations), where each table represents an entity. Tables are linked through relationships using keys to maintain data integrity.
Key Database Terminology
Understanding these terms is essential for working with relational databases.
Entity
Definition
An entity is a real-world object or concept that we want to store information about. Each entity becomes a table in the database.
Example
Examples of Entities:
• Student - stores information about students
• Course - stores information about courses
• Order - stores information about customer orders
• Product - stores information about items for sale
• Doctor - stores information about medical staff
• Student - stores information about students
• Course - stores information about courses
• Order - stores information about customer orders
• Product - stores information about items for sale
• Doctor - stores information about medical staff
Attribute
Definition
An attribute is a property or characteristic of an entity. Attributes become columns in a database table.
Example
Student Entity Attributes:
• StudentID
• FirstName
• LastName
• DateOfBirth
• Email
• CourseID
• StudentID
• FirstName
• LastName
• DateOfBirth
• CourseID
Primary Key
Definition
A primary key is an attribute (or combination of attributes) that uniquely identifies each record in a table. No two records can have the same primary key value, and it cannot be null.
Key Point
Primary Key Characteristics:
• Must be unique for every record
• Cannot be NULL (empty)
• Should not change over time
• Often an auto-generated ID number
• Each table should have exactly one primary key
• Must be unique for every record
• Cannot be NULL (empty)
• Should not change over time
• Often an auto-generated ID number
• Each table should have exactly one primary key
Example
Good Primary Keys:
• StudentID: S001, S002, S003...
• OrderNumber: ORD-2024-0001
• ISBN: 978-3-16-148410-0
Bad Primary Keys:
• Name (not unique - many people share names)
• Email (might change)
• Phone number (might change)
• StudentID: S001, S002, S003...
• OrderNumber: ORD-2024-0001
• ISBN: 978-3-16-148410-0
Bad Primary Keys:
• Name (not unique - many people share names)
• Email (might change)
• Phone number (might change)
Foreign Key
Definition
A foreign key is an attribute in one table that references the primary key of another table, creating a link between them.
Worked Example
Worked Example: Primary and Foreign Keys
Doctor Table:
Patient Table:
How it works:
• DoctorID is the PRIMARY KEY in Doctor table (unique identifier)
• DoctorID appears in Patient table as a FOREIGN KEY
• This links each patient to their doctor
• D001 (Dr Smith) has two patients (P001, P002)
• The foreign key MUST match an existing primary key
Doctor Table:
| DoctorID | Name | Speciality |
|---|---|---|
| D001 | Dr Smith | Cardiology |
| D002 | Dr Jones | Neurology |
| PatientID | Name | DoctorID (FK) |
|---|---|---|
| P001 | John Brown | D001 |
| P002 | Jane White | D001 |
| P003 | Bob Green | D002 |
• DoctorID is the PRIMARY KEY in Doctor table (unique identifier)
• DoctorID appears in Patient table as a FOREIGN KEY
• This links each patient to their doctor
• D001 (Dr Smith) has two patients (P001, P002)
• The foreign key MUST match an existing primary key
Secondary Key
Definition
A secondary key is an attribute that is indexed to allow faster searching. Unlike primary keys, secondary keys don't need to be unique.
Example
Secondary Key Example:
In a Patient table, "Surname" might be a secondary key - it's not unique, but you often search by surname, so indexing it makes queries faster.
In a Patient table, "Surname" might be a secondary key - it's not unique, but you often search by surname, so indexing it makes queries faster.
Indexing
Without an index, the database has to check every record in a table in turn to find the ones that match a search. On a large table this is slow.
Definition
An index is a separate, ordered list of the values in a chosen field, each stored with a pointer to its record. The database searches the index and follows the pointer, so records can be found without searching the whole table.
Key Point
Indexing:
• The primary key is indexed automatically
• A secondary key is a field that has been given an index because it is often searched or sorted on
• The index is kept in order, so it can be searched quickly, for example with a binary search
• Drawback: the index takes up extra storage space
• Drawback: every index must be updated whenever a record is added, changed or deleted, which slows those operations down
• The primary key is indexed automatically
• A secondary key is a field that has been given an index because it is often searched or sorted on
• The index is kept in order, so it can be searched quickly, for example with a binary search
• Drawback: the index takes up extra storage space
• Drawback: every index must be updated whenever a record is added, changed or deleted, which slows those operations down
Flat File Databases
Definition
A flat file database holds all of its data in a single table, often a plain text file such as a CSV file with one record per line.
A flat file is quick to create and easy to understand, so it suits a small amount of simple data. The problems appear as the data grows, because details that belong to one entity have to be repeated in many records.
Worked Example
Worked Example: Bookings Stored in a Flat File
Problems:
• Asha's name and phone number are stored twice: this is redundant data and it wastes storage
• If Asha's phone number is changed in one record but not the other, the data becomes inconsistent
• A new class cannot be stored until somebody books it
• Deleting booking 3 also deletes everything that is known about Tom
Relational version: a Member table, a Class table and a Booking table, linked by primary and foreign keys, so that each member and each class is stored once.
| BookingID | MemberName | MemberPhone | ClassName | Instructor |
|---|---|---|---|---|
| 1 | Asha Patel | 07700 900123 | Yoga | Mr Lee |
| 2 | Asha Patel | 07700 900123 | Spin | Ms Khan |
| 3 | Tom Reed | 07700 900456 | Yoga | Mr Lee |
• Asha's name and phone number are stored twice: this is redundant data and it wastes storage
• If Asha's phone number is changed in one record but not the other, the data becomes inconsistent
• A new class cannot be stored until somebody books it
• Deleting booking 3 also deletes everything that is known about Tom
Relational version: a Member table, a Class table and a Booking table, linked by primary and foreign keys, so that each member and each class is stored once.
Key Point
| Feature | Flat file | Relational database |
|---|---|---|
| Structure | One table | Several tables linked by primary and foreign keys |
| Setting up | Easy, with no specialist knowledge needed | Harder: the tables must be designed and normalised, and a DBMS is needed |
| Redundancy | The same data is repeated in many records | Each item of data is stored once |
| Consistency | Copies of the same data can come to disagree | A change is made in one place and is correct everywhere |
| Searching | Slower as the file grows | Faster, and complex queries across tables are possible |
| Security | Anyone who can open the file sees all of it | Access can be controlled for each table and each user |
| Expanding | Difficult to manage as the data grows | New tables can be added and linked to the existing ones |
Exam Tip
Choosing between them: A flat file suits a small, simple set of data that is not expected to grow. Once the same details would be repeated across many records, recommend a relational database and give the reasons: less redundant data, better consistency, more complex queries and easier expansion.