ICT 0417 - Databases

Objective: Create Relationships Between Tables

This section focuses on understanding and implementing relationships between different 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 uniquely identified. A table can have only one primary key.

A foreign key is a field in one table that refers to the primary key of another table. It establishes a link between the two tables. Foreign keys are used to represent relationships between data.

Types of Relationships

There are three main types of relationships between tables:

• One-to-One (1:1): One record in table A is related to exactly one record in table B.
• One-to-Many (1:N): One record in table A is related to multiple records in table B.
• Many-to-Many (M:N): Multiple records in table A are related to multiple records in table B.

Implementing Relationships

Relationships are typically implemented using foreign keys. The foreign key in the "many" side of the relationship points to the primary key in the "one" side.

Example: Library Database

Let's consider a library database with two tables: Books and Borrowers.

Books Table

Book ID	Title	Author
1	The Hitchhiker's Guide to the Galaxy	Douglas Adams
2	Pride and Prejudice	Jane Austen
3	1984	George Orwell

Book ID is the primary key in the Books table.

Borrowers Table

Borrower ID	Name	Address
101	Alice Smith	123 Main St
102	Bob Johnson	456 Oak Ave

Borrower ID is the primary key in the Borrowers table.

If a borrower borrows a book, we need to link these two tables. We can achieve this by adding a foreign key to the Borrowing_Records table.

Borrowing_Records Table

Borrowing Record ID	Book ID	Borrower ID	Borrow Date	Return Date
1	1	101	2023-10-26	2023-11-09
2	2	102	2023-10-27	2023-11-12
3	1	102	2023-10-28	2023-11-13

In the Borrowing_Records table:

• Borrowing Record ID is the primary key.
• Book ID is a foreign key referencing the Books table.
• Borrower ID is a foreign key referencing the Borrowers table.

Relationship Diagram

Benefits of Using Relationships

Using relationships between tables offers several benefits:

• Data Integrity: Ensures data consistency and accuracy.
• Reduced Redundancy: Avoids repeating the same data in multiple tables.
• Improved Data Organization: Makes the database easier to understand and manage.
• Efficient Data Retrieval: Allows for complex queries that combine data from multiple tables.

Important Considerations

When designing relationships, consider the following:

• Choose appropriate primary keys: Primary keys should be unique and stable.
• Define relationships accurately: Ensure that relationships accurately reflect the real-world data.
• Use appropriate data types: Use data types that are suitable for the data being stored.