Establishing Table Relationships: One-to-Many

Master the art of connecting database tables - a critical CSEC IT skill for efficient data management!

1

What is a "One-to-Many" Relationship? (Objective 5)

The Definition: A relationship where a single record in the first table (Parent) can be related to many records in the second table (Child), but a record in the second table is related to only one record in the first.

The "Real World" Analogy

  • One Mother → Many Children: A mother can have multiple children, but each child has only one biological mother.
  • One Teacher → Many Classes: A teacher can teach multiple classes, but each class has only one primary teacher.
  • One Customer → Many Orders: A customer can place many orders, but each order belongs to only one customer.
Relationship Matchmaker

Click the "1" and "∞" symbols to correctly label the relationship between these tables!

Departments

Stores department information

Each department has many teachers

1
∞
1
∞

Teachers

Stores teacher information

Each teacher belongs to one department

2

Why We Need Relationships (Reducing Redundancy)

The Problem of Flat Files

When all data is stored in one table (a "flat file"), information gets repeated. This causes "Update Anomalies" - if you need to change data, you must change it in multiple places.

The Solution

Splitting data into related tables and linking them with relationships eliminates redundancy and ensures data consistency.

Benefits of Relationships

  • Saves Storage Space: Data is stored once, not repeated
  • Increases Accuracy: Changes made in one place automatically update everywhere
  • Easier Updates: Update customer information once instead of in every order
  • Prevents Inconsistencies: No conflicting data entries
The Redundancy Highlighter

Click "Normalize" to see how splitting a flat file into two tables eliminates redundancy!

OrderID CustomerName CustomerEmail OrderDate Product Quantity
1001 Maria Rodriguez maria@email.com 2023-10-15 Laptop 1
1002 Maria Rodriguez maria@email.com 2023-10-20 Mouse 2
1003 David Chen david@email.com 2023-10-18 Keyboard 1
1004 Maria Rodriguez maria@email.com 2023-10-25 Monitor 1
CustomerID CustomerName CustomerEmail
C001 Maria Rodriguez maria@email.com
C002 David Chen david@email.com
OrderID CustomerID OrderDate Product Quantity
1001 C001 2023-10-15 Laptop 1
1002 C001 2023-10-20 Mouse 2
1003 C002 2023-10-18 Keyboard 1
1004 C001 2023-10-25 Monitor 1
3

The Mechanics: Primary Key (PK) to Foreign Key (FK)

The "Handshake"

The link is always formed by taking the Primary Key from the "One" side and placing it as a Foreign Key in the "Many" side.

Visual Guide

In a school database: Table_Students (PK: StudentID) connects to Table_Grades (FK: StudentID). The StudentID in Grades "points back" to the specific student.

Drag the Link

Drag the Primary Key from the "One" table and drop it on the correct field in the "Many" table to create the relationship!

Customers

"One" side

CustomerID (PK)
CustomerName
Email

Orders

"Many" side

OrderID (PK)
CustomerID (FK)
OrderDate
TotalAmount
4

Referential Integrity: The Rules of the Link (Objective 7)

Ensuring Consistency

When "Enforced Referential Integrity" is enabled, the database enforces these rules:

  • No Orphan Records: Cannot add a record to the "Many" table with a foreign key that doesn't exist in the "One" table
  • No Broken Links: Cannot delete a record from the "One" table if it has related records in the "Many" table
  • Cascade Options: Some databases allow "Cascade Delete" (delete parent and children) or "Cascade Update" (update parent and children)
The Integrity Simulator

Try to delete a customer who has orders, or add an order with an invalid customer ID!

Database Management System Referential Integrity: ON

Customers Table

C001 - Maria Rodriguez (3 orders)
C002 - David Chen (1 order)
C003 - Aisha Johnson (0 orders)

Add New Order

Referential Integrity Error!

5

Identifying the "Infinity" (∞) Symbol

ER Diagrams

Entity-Relationship diagrams use standardized symbols to represent database structures:

  • Rectangle: Represents an entity (table)
  • Diamond: Represents a relationship (optional in some notations)
  • Lines with symbols: Show cardinality (1, ∞, M, N)

CSEC Syllabus Requirement

You must understand how to interpret relationship lines in database software like Microsoft Access, which uses the "infinity" symbol to show the "Many" side of relationships.

Diagram Detective

Study this ER diagram of a school database. How many One-to-Many relationships can you identify?

Departments
  • DeptID (PK)
  • DeptName
  • Location
Teachers
  • TeacherID (PK)
  • Name
  • DeptID (FK)
Students
  • StudentID (PK)
  • Name
  • Grade
Courses
  • CourseID (PK)
  • CourseName
  • TeacherID (FK)
Enrollments
  • EnrollmentID (PK)
  • StudentID (FK)
  • CourseID (FK)
  • Grade

Count the One-to-Many relationships:

0
6

CSEC SBA Masterclass: Setting Up the Relationship

Step-by-Step Tutorial

Follow these steps to create a One-to-Many relationship in Microsoft Access (similar steps apply to other database software):

Microsoft Access - Relationships Window
Table List
Departments
Teachers
Students
Courses
Relationship Setup
Step 1: Open Relationships

Database Tools → Relationships

Step 2: Add Tables

Right-click → Add Table → Select "Departments" and "Teachers"

Step 3: Create the Link

Drag DeptID from Departments onto DeptID in Teachers

Checklist for Success

7

Knowledge Check: The Relationship Challenge

Scenario:

"A School Database has a Departments table and a Teachers table. Each teacher belongs to one department, and each department can have many teachers."

Question 1: Which table is on the "One" side of this relationship?

Departments
Teachers
Both are "One" side
Neither - it's Many-to-Many

Question 2: Where should the Foreign Key be placed?

In the Departments table
In the Teachers table
In both tables
No Foreign Key needed

Question 3: What happens if you try to delete a Department that has Teachers assigned to it (with Referential Integrity enforced)?

It deletes successfully
Database blocks the deletion
It deletes the Department but not the Teachers
It deletes both Department and all its Teachers

Question 4: In an ER diagram, which symbol represents the "Many" side?

1
∞
M
Both ∞ and M are correct

Question 5: Which of these is NOT a benefit of using relationships?

Reduces data redundancy
Prevents update anomalies
Makes database design more complex
Saves storage space

Quiz Results

Your score: 0/5

Scroll to Top