IB Computer Science Topic A3.1.1: Features, Benefits, and Limitations of a Relational Database Notes

Features of a Relational Database

Tables: The Foundation of Relational Databases

Each table represents an entity, such as customers, products, or orders.

Primary Keys: Ensuring Uniqueness

It ensures that no two rows have the same value in the primary key column.

Foreign Keys: Creating Relationships

This creates a link between the two tables, enabling data to be related across entities.

Composite Keys: Combining Attributes for Uniqueness

It is used when a single column is not sufficient to uniquely identify a record.

Relationships: Connecting Data Across Tables

They are established through primary and foreign keys.

Types of Relationships

1. Relationships might be optional or mandatory (modality).
   1. Mandatory Relationship:
      1. A relationship where a record in one table must be linked to a record in another table. The foreign key cannot be NULL.
      2. Example: Every student must be enrolled in a school.
         Student(SchoolID) must have a valid SchoolID from the School table.
   2. Optional Relationship:
      1. A relationship where a record in one table may or may not be linked to a record in another table. The foreign key can be NULL.
      2. Example: A teacher might have an assigned mentor, but not all teachers do. Teacher(MentorID) can be NULL if no mentor is assigned.
2. One-to-One: Each record in one table is related to exactly one record in another table.
   1. Example: A person and their passport.
3. One-to-Many: A record in one table can be related to multiple records in another table.
   1. Example: A customer can have multiple orders.
4. Many-to-Many: Multiple records in one table can be related to multiple records in another table.
   1. Example: Students enrolled in multiple courses.

Benefits of Relational Databases

Data Consistency and Integrity