Lecture 3: Database Relationships Implementation

1. The Concept of Relationships

In a relational database, data is split into multiple tables to avoid Redundancy (duplicate data) and ensure Consistency. These tables are then linked using Relationships.

• Foreign Key (FK): A field in one table that refers to the Primary Key in another table. The relationship is built by connecting the PK to the FK.

2. Types of Relationships

1. One-to-One (1:1):
   • Definition: One record in Table A relates to exactly one record in Table B.
   • Example: An Employee table and a Payroll table (assuming one payroll record per employee). Or Person and Passport.
   • Usage: Often used to split a table with too many fields for security or organization.
2. One-to-Many (1:M):
   • Definition: One record in Table A relates to multiple records in Table B.
   • Example: Customer and Orders. One customer makes many orders, but an order belongs to only one customer.
   • Implementation: The PK of the "One" side is added as a FK in the "Many" side.
3. Many-to-Many (M:N):
   • Definition: Multiple records in Table A relate to multiple records in Table B.
   • Example: Student and Courses.
   • Implementation: Cannot be done directly. Requires a third Junction Table (e.g., Enrollments) containing FKs from both sides.

3. Implementing One-to-One in Access

• Scenario: Linking Employee (PK: Employee_ID) with Cars (if each employee gets one car).
• Steps:
  1. Go to Database Tools tab -> Click Relationships.
  2. Show Table: Select both tables (Employee, Cars) and click Add.
  3. Create Link: Drag the Employee_ID (PK) from the Employee table and drop it onto the Employee_ID (FK) in the Cars table.
  4. Edit Relationships Dialog:
     • Verify the relationship type says "One-to-One".
     • Check "Enforce Referential Integrity": This ensures you cannot add a car for an employee who doesn't exist.
  5. Click Create. A line with 1 on both ends will appear connecting the tables.