Relational database concepts - Information Technology - Pearson A-Level

* The content provided by thinka is generated by AI and may not always be accurate or up-to-date. Please use it as a supplementary resource and verify with official materials.

Unit 4: Relational Database Concepts Study Notes

Hello future IT expert! Welcome to the exciting world of databases. This chapter is absolutely fundamental to understanding how modern businesses manage their vast amounts of information—from your school register to massive services like Netflix and Amazon. Don't worry if this seems technical; we will break down every concept using simple analogies, making sure you feel confident navigating these key ideas.

The goal here is to understand not just what a database is, but how different pieces of information are related to each other—that’s the 'relational' part!

Section 1: The Core Building Blocks of a Relational Database

Think of a database like a super-organised digital filing cabinet. We use a specific structure to store data efficiently.

1.1 Tables, Records, and Fields

A Relational Database Management System (RDBMS) stores data in two-dimensional structures called Tables (sometimes called relations).

Table (Relation): This is the main structure that holds a specific type of information, like 'Customers' or 'Products'. (Analogy: A single spreadsheet dedicated to one topic.)
Record (Tuple/Row): A single complete entry in the table. It holds all the data for one item or person. (Analogy: One row in the spreadsheet, containing all the details for one specific customer.)
Field (Attribute/Column): A single category of information within the table. (Analogy: The column header, such as 'First Name', 'Address', or 'Order Date'.)

Quick Tip for Struggling Students:

If you see a table, remember:
Rows = Records
Columns = Categories (Fields)

Section 2: The Power of Keys

Keys are crucial! They are special fields (or groups of fields) that uniquely identify records and establish links between tables.

2.1 Primary Key (PK)

The Primary Key (PK) is the unique identifier for each record in a table.

It must be unique—no two records can have the same PK value.
It cannot be NULL (it must always have a value).
Example: In a 'Students' table, the Student ID number is usually the PK.

Memory Aid: A Primary Key is like your Passport Key—it uniquely identifies only you!

2.2 Candidate Key

A Candidate Key is any field (or combination of fields) that could potentially serve as the Primary Key because it meets the uniqueness criteria. Once you choose one candidate key to be the PK, the others become Alternate Keys.

Example: In an 'Employees' table, both the 'Employee ID' and the 'National Insurance Number' might be unique. Both are Candidate Keys. If you choose 'Employee ID' as the PK, the NI number is the Alternate Key.

2.3 Foreign Key (FK)

A Foreign Key (FK) is a field in one table that refers to the Primary Key in another table. The Foreign Key is the fundamental building block of relationships.

It acts as a link or a bridge between the two tables.
The FK field does not have to be unique in its own table (it will often repeat).
It allows us to look up details without duplicating data.

Example: If we have a 'Customers' table (with Customer ID as PK) and an 'Orders' table, the 'Orders' table will contain the Customer ID. In the 'Orders' table, this Customer ID field is the Foreign Key.

Key Takeaway: PKs ensure uniqueness within a table. FKs create links between tables.

Section 3: Connecting the Data – Relationships

Relationships define how records in one table relate to records in another. There are three main types you must know:

3.1 One-to-One (1:1)

One record in Table A relates to exactly one record in Table B, and vice-versa.

Use Case: This is rare, often used to separate extremely sensitive or large amounts of data into an extension table.
Example: One employee has exactly one set of security access details.

3.2 One-to-Many (1:M)

One record in Table A can relate to many records in Table B, but each record in Table B relates to only one record in Table A. This is the most common relationship type.

The link is always achieved by placing the PK from the 'One' side into the 'Many' side as an FK.
Example: One Department (Table A) has many Employees (Table B). The Department ID (PK) is placed in the Employees table as an FK.

3.3 Many-to-Many (M:M)

A record in Table A can relate to many records in Table B, AND a record in Table B can relate to many records in Table A.

Example: Many Students can enrol in many Courses.
Crucial Point: M:M relationships cannot be implemented directly in an RDBMS.
Solution: You must resolve the M:M relationship by creating a third table, known as a Junction Table (or Link/Intermediate table).

Step-by-Step for M:M Resolution:

Create a new Junction Table (e.g., 'Enrolment').
Take the Primary Key from the first table (e.g., StudentID). This becomes an FK in the Junction Table.
Take the Primary Key from the second table (e.g., CourseID). This becomes an FK in the Junction Table.
The Primary Key of the Junction Table is usually the combination of both these Foreign Keys (a Composite Key).

Now you have two 1:M relationships leading into the Junction Table, effectively solving the M:M problem!

Key Takeaway: 1:M is the core of relational design. M:M always requires a junction table.

Section 4: Ensuring Data Quality – Referential Integrity

Referential Integrity is a set of rules that ensures that the relationships between tables are consistent and valid. Simply put, it ensures that your Foreign Keys always refer to an existing Primary Key.

4.1 The Rule

If Table B contains a Foreign Key referencing Table A, then every value of the Foreign Key in Table B must either match a value of the Primary Key in Table A, or the Foreign Key value must be NULL (empty).

4.2 Why is this important?

If referential integrity is violated, you end up with orphan records—data that links nowhere. (Imagine having an Order record linked to a Customer ID that doesn't exist. You wouldn't know who to send the invoice to!)

4.3 Actions on Deletion/Update

RDBMS systems offer rules to maintain integrity when records are changed:

Restrict: Prevents the deletion or update of a PK if there are corresponding FK records. (Safest option).
Cascade: If the parent PK is deleted/updated, the corresponding FK records in the child table are also deleted/updated. (Use with caution!).
Set NULL: If the parent PK is deleted/updated, the corresponding FK field in the child record is set to NULL.

Did you know? Enforcing referential integrity is the database's way of automatically checking your data consistency, saving huge amounts of time and preventing errors!

Section 5: Efficiency and Organisation – Normalisation

Before normalisation, we must understand the problems it solves:

Data Redundancy: Storing the same data multiple times (e.g., storing the full department name next to every employee in that department).
Update Anomalies: If redundant data needs updating, you must update it everywhere. If you miss one, the data becomes inconsistent.
Insertion Anomalies: You might not be able to insert data about a new department until an employee is hired.
Deletion Anomalies: Deleting the last employee in a department accidentally deletes all information about that department.

Normalisation is the process of structuring a relational database in stages (forms) to reduce data redundancy and improve data integrity.

5.1 First Normal Form (1NF)

A table is in 1NF if:

It has a Primary Key.
There are no repeating groups of data (e.g., having three columns: Item1, Item2, Item3).
All attributes are atomic (indivisible). (Example: The 'Address' field should be broken down into 'Street', 'City', 'Postcode', etc., not stored as one large block.)

Rule: One cell should contain only one value.

5.2 Second Normal Form (2NF)

A table is in 2NF if:

It is already in 1NF.
All non-key attributes are fully dependent on the entire Primary Key.

This only matters when you have a Composite Key (a PK made up of two or more fields).

Common Mistake to Avoid: If you have a composite key (StudentID, CourseID), and you store the Student Name. The Student Name only depends on StudentID, not the combination of StudentID + CourseID. This is a Partial Dependency. To fix it, you move Student Name to a separate 'Students' table.

5.3 Third Normal Form (3NF)

A table is in 3NF if:

It is already in 2NF.
There are no transitive dependencies.

A Transitive Dependency exists when a non-key attribute depends on another non-key attribute.

Example:

PK	Non-Key	Non-Key
EmployeeID	DepartmentID	DepartmentName

Here, DepartmentName depends on DepartmentID, which is NOT the PK. This is a transitive dependency. We fix this by moving the dependency (DepartmentID, DepartmentName) into its own 'Departments' table.

Don't panic! 3NF simply means: Every non-key field depends only on the Primary Key, the whole Primary Key, and nothing but the Primary Key.

Key Takeaway: Normalisation is about breaking down one big, inefficient table into several smaller, highly efficient and linked tables.

Quick Review Box: Relational Database Essentials

RDBMS: Manages linked tables (relations).
PK: Unique identifier (cannot be NULL).
FK: Links tables by referencing another table's PK.
1:M: The most common type of link.
M:M: Must be resolved using a Junction Table.
Referential Integrity: Ensures FK values match existing PK values.
3NF: The goal of good design (no redundancy, no transitive dependencies).

You have mastered the core concepts of relational databases! Go back and review the roles of the keys and the normalisation steps—they are the key to exam success in this unit.