Be able to create relationships between tables

Topic 18 – Databases

Objective: Be able to create relationships between tables

In a relational database, data is stored in separate tables that are linked together through relationships. Understanding how to create and manage these relationships is essential for organising data efficiently and avoiding duplication.

Key Concepts

• Primary Key (PK) – a unique identifier for each record in a table.
• Foreign Key (FK) – a field (or combination of fields) in one table that refers to the primary key of another table.
• Referential Integrity – a rule that ensures foreign key values always point to existing primary key values.

Types of Relationships

1. One‑to‑One (1:1) – each record in Table A matches exactly one record in Table B.
2. One‑to‑Many (1:M) – a single record in Table A can be related to many records in Table B, but each record in Table B relates to only one record in Table A.
3. Many‑to‑Many (M:N) – records in Table A can relate to multiple records in Table B and vice‑versa. Implemented using a junction (link) table.

Example Scenario

Consider a school database with the following entities:

Table	Key Fields	Sample Fields
Students	StudentID (PK)	Name, DateOfBirth, YearGroup
Courses	CourseID (PK)	CourseName, Credits
Enrollments	EnrollmentID (PK)	StudentID (FK), CourseID (FK), EnrolDate

In this example:

• Students ↔ Enrollments is a one‑to‑many relationship (one student can have many enrollments).
• Courses ↔ Enrollments is also a one‑to‑many relationship (one course can have many enrollments).
• Students ↔ Courses is a many‑to‑many relationship, realised through the Enrollments junction table.

Steps to Create a Relationship (Using a Typical DBMS)

1. Identify the primary key in each table.
2. Decide which table will contain the foreign key.
3. Open the “Relationships” window (or equivalent) in the DBMS.
4. Drag the primary key field from the parent table onto the foreign key field in the child table.
5. Choose the relationship type (1:1, 1:M, M:N) and enforce referential integrity if required.
6. Save the design; the DBMS will now enforce the rules you set.

Enforcing Referential Integrity

When referential integrity is active, the DBMS prevents:

• Inserting a foreign‑key value that does not exist in the related primary‑key table.
• Deleting a primary‑key record that is still referenced by a foreign key (unless cascade delete is chosen).

Common Errors to Watch For

• Using the wrong field as a primary key (e.g., a non‑unique field).
• Failing to set the foreign key data type to match the primary key.
• Creating circular relationships without a clear hierarchy.
• Neglecting to enforce referential integrity, leading to orphan records.

Suggested Diagram

Practice Activity

Using the sample tables above, answer the following:

1. Identify the primary key in each table.
2. Write the SQL statement to add a foreign key from Enrollments.StudentID to Students.StudentID.
3. Explain what would happen if you tried to delete a student who has enrolments when referential integrity is enforced.

Sample SQL Statements

Creating tables with primary keys:

CREATE TABLE Students (
    StudentID INT PRIMARY KEY,
    Name \cdot ARCHAR(50),
    DateOfBirth DATE,
    YearGroup \cdot ARCHAR(10)
);

CREATE TABLE Courses (
    CourseID INT PRIMARY KEY,
    CourseName \cdot ARCHAR(100),
    Credits INT
);
    

Creating the junction table with foreign keys and referential integrity:

CREATE TABLE Enrollments (
    EnrollmentID INT PRIMARY KEY,
    StudentID INT,
    CourseID INT,
    EnrolDate DATE,
    FOREIGN KEY (StudentID) REFERENCES Students(StudentID)
        ON DELETE CASCADE,
    FOREIGN KEY (CourseID) REFERENCES Courses(CourseID)
        ON DELETE RESTRICT
);
    

Summary

• Relationships link tables using primary and foreign keys.
• One‑to‑many is the most common; many‑to‑many requires a junction table.
• Enforcing referential integrity keeps data consistent.
• Follow systematic steps in the DBMS to define and test relationships.