Be able to create relationships between tables

Resources | Subject Notes | Information Communication Technology ICT | Lesson Plan

ICT 0417 - Databases

Objective: Create Relationships Between Tables

This section focuses on understanding and implementing relationships between tables in a database. Relationships are crucial for organizing data efficiently and avoiding redundancy. We will explore different types of relationships and how to define them using primary and foreign keys.

Understanding Primary and Foreign Keys

A primary key is a unique identifier for each record in a table. It ensures that every record can be distinguished from all others. A foreign key is a field in one table that refers to the primary key of another table. This is how we link tables together.

Types of Relationships

There are three main types of relationships between tables:

  • One-to-One (1:1): One record in table A relates to exactly one record in table B.
  • One-to-Many (1:N): One record in table A relates to many records in table B.
  • Many-to-Many (M:N): Many records in table A relate to many records in table B. This type of relationship usually requires a linking table.

One-to-Many Relationship Example

Consider a Customers table and an Orders table. A customer can place multiple orders, but each order belongs to only one customer. This is a one-to-many relationship.

Table Columns Primary Key Foreign Key
Customers CustomerID, Name, Address, Phone CustomerID
Orders OrderID, CustomerID, OrderDate, TotalAmount OrderID CustomerID (references Customers.CustomerID)

In this example, the CustomerID in the Orders table is a foreign key that references the CustomerID (primary key) in the Customers table.

Many-to-Many Relationship Example

Consider a Students table and a Courses table. A student can enroll in multiple courses, and a course can have multiple students. This is a many-to-many relationship. To represent this, we need a StudentCourses linking table.

The StudentCourses table would have two foreign keys, one referencing Students and one referencing Courses.

Table Columns Primary Key Foreign Key
Students StudentID, Name, Major StudentID
Courses CourseID, CourseName, Credits CourseID
StudentCourses StudentID, CourseID, Grade StudentID, CourseID (composite key)

The StudentCourses table acts as a bridge between the Students and Courses tables.

Implementing Relationships in a Database System

Database systems like MySQL, PostgreSQL, and SQLite allow you to define relationships between tables using foreign key constraints. These constraints ensure data integrity by preventing actions that would violate the relationships.

For example, a foreign key constraint on the Orders table would prevent you from adding an order with a CustomerID that does not exist in the Customers table.

Diagrammatic Representation

Suggested diagram: Illustrating a one-to-many relationship between Customers and Orders, and a many-to-many relationship between Students and Courses using a linking table.

Benefits of Using Relationships

  • Data Integrity: Relationships help ensure that data is consistent and accurate.
  • Reduced Redundancy: Data is stored only once, avoiding duplication and saving storage space.
  • Improved Data Retrieval: Relationships make it easier to retrieve related data from multiple tables.
  • Data Consistency: Changes to data in one table are automatically reflected in related tables.

Further Considerations

When designing database relationships, consider the following:

  • Choose appropriate primary keys: Primary keys should be unique and stable.
  • Define relationships clearly: Document the relationships between tables to ensure that they are understood by all users.
  • Use foreign key constraints: Enforce relationships using foreign key constraints to maintain data integrity.
  • Consider performance: Complex relationships can impact database performance. Optimize relationships where necessary.