IB Computer Science Topic A3.2.4: Constructing Tables for Relational Databases Notes

Constructing Tables: Where the Design Becomes Something a DBMS Can Hold

1. A relational database stores data in tables, and each table holds one entity from the design.
2. A table has rows, also called records, and columns, also called attributes or fields.
3. One row holds one instance of the entity, and one column holds the same attribute for every instance.
4. Tables are linked to each other only through keys, so getting the keys right is what makes the whole design work.

The Four Key Types the Syllabus Names

1. Primary Key
   1. A primary key is the attribute, or set of attributes, that uniquely identifies each row in a table.
   2. It cannot be duplicated and it cannot be left empty, so the DBMS enforces both uniqueness and NOT NULL on it.
   3. In a Student table, StudentID is the primary key.
2. Foreign Key
   1. A foreign key is a column in one table that holds the primary key value of a row in another table.
   2. It is the mechanism that implements a relationship, and it always sits on the many side of a one-to-many relationship.
   3. In an Enrolment table, StudentID is a foreign key referencing Student(StudentID).
3. Composite Key
   1. A composite key is a primary key made of two or more separate columns used together, because no single column is unique on its own.
   2. Each column keeps its own identity and can still be queried and joined separately.
   3. In a CourseEnrolment table the composite key is (StudentID, CourseID): a student appears many times and a course appears many times, but the pair appears once.
4. Concatenated Key
   1. A concatenated key is a single column whose value is built by joining the values of two or more attributes into one string.
   2. Student 1024 taking course CS01 produces the single key value 1024-CS01, stored in one column called EnrolmentCode.
   3. It reads well to a human and needs only one column in a join, but the parts have to be split back out before either can be searched on its own.

Composite Against Concatenated: The Distinction That Gets Confused

Foreign Keys and Referential Integrity

1. Referential integrity is the rule that a foreign key must either match an existing primary key value or be NULL.
2. Declaring the foreign key makes the DBMS enforce that rule, so an Enrolment for student 9999 is refused if no such student exists.
3. It also blocks the deletion of a Student row while Enrolment rows still point at it, which is what prevents orphaned records.

Junction Tables: Building a Many-to-Many Relationship

1. A many-to-many relationship cannot be stored in two tables, because neither table has one place to put the foreign key.
2. The fix is a third table, called a junction table, holding one foreign key to each side.
3. Those two foreign keys together usually form the composite primary key of the junction table, which also stops the same pairing being recorded twice.
4. A column such as EnrolmentDate or Quantity is stored in the junction table, because its value is determined by both foreign keys together and by neither of them alone.

Constraints: How a Well-Defined Table Protects Its Own Data

1. A constraint is a rule declared on a column so the DBMS rejects bad data no matter which program tries to write it.
2. Validation written only in application code can be bypassed, while a constraint in the table cannot.

Constructing the Tables for a Student Enrolment Database

1. One table per entity
   1. Take each entity from the design and give it its own table with its own attributes.
2. Pick a primary key for each table
   1. Use a single ID column where one exists, and a composite key where the row is only unique as a combination.
3. Place a foreign key for every relationship
   1. Put it on the many side, and add a junction table wherever a many-to-many relationship remains.
4. Declare the constraints
   1. Mark every column NOT NULL unless it is genuinely optional, and add CHECK conditions for values with a known range.

Design Faults That Cost Data Integrity

1. Missing constraints
   1. A table with no primary key allows duplicate rows, and a relationship with no declared foreign key allows orphaned records.
2. Unresolved many-to-many relationships
   1. Trying to store several course IDs in one column of the Student table breaks atomicity and makes searching by course almost impossible.
3. Repeating the same fact in several tables
   1. Storing a student's address in both Student and Enrolment means one copy can be updated and the other left stale, which is the problem normalization exists to remove.