Relational Model

📘 Learn Relational Model from zero

The relational model (Codd, 1970) is a precise, set-based way to store facts in tables. Think of a well-organized spreadsheet workbook: each sheet is a relation (table), each column is an attribute with a fixed type (its domain), and each row is a tuple, one fact about one thing.

Three rules make it more than a spreadsheet. First, every row must be uniquely identifiable by a primary key (entity integrity: no part of it may be NULL, and the combination is never duplicated). Second, to link sheets, a column in one table holds a key value from another, a foreign key (referential integrity: it must reference a row that actually exists, or be NULL). Third, a relation is a set of tuples, so the order of rows and columns carries no meaning and there are no duplicate rows; only the values matter.

Worked example. Model students and the courses they take. Two strong entities become two tables:

• Student(student_id PK, name) with rows (1, 'Ada'), (2, 'Linus').
• Course(course_id PK, title) with row ('CS101', 'Databases').

A student can take many courses and a course has many students, an M:N relationship. You cannot store this with a foreign key on either entity table, so you create a junction table: Enrollment(student_id FK, course_id FK, grade) whose primary key is the composite (student_id, course_id). Row (1, 'CS101', 'A') says Ada took Databases. Both foreign keys must reference existing rows, so you can never enroll a phantom student in a phantom course.

Key insight: the relational model represents everything, entities and the relationships between them, as relations governed by keys, and constraints declared in the schema (not application code) guarantee the data stays valid.

✨ Added by the guide to build intuition — not from the source course.

🎯 Guided practice

1. Easy, identify the keys. Given Employee(emp_id, email, ssn, dept_id, name) where emp_id, email, and ssn are each individually unique.

   Step 1: List the candidate keys, the minimal attribute sets that uniquely identify a row. Here each of emp_id, email, and ssn alone uniquely identifies an employee, so there are three candidate keys. (Note: the set {emp_id, email} is a superkey but not a candidate key, because it is not minimal.)

   Step 2: Pick one as the primary key. Prefer the stable, simple, non-changing one: emp_id. (Emails change; SSNs are sensitive and not universal.) The others remain alternate keys, enforced as UNIQUE (and ideally NOT NULL).

   Step 3: Spot the foreign key. dept_id identifies a department, not an employee, so it is a foreign key referencing Department(dept_id); referential integrity requires every dept_id value to match an existing department (or be NULL if the column is nullable). Pattern: uniqueness within the table = candidate/primary key; a reference to another table's key = foreign key.

2. Medium, map an ER diagram to relations. ER: entity Author (with a multivalued attribute phone), entity Book, and an M:N relationship Writes between them carrying an attribute royalty_pct.

   Step 1, strong entities → tables. Each becomes a table keyed by its identifier: Author(author_id PK, name) and Book(isbn PK, title).

   Step 2, multivalued attribute → its own table. A single column cannot hold many phone numbers atomically (storing a list violates first normal form). Create AuthorPhone(author_id FK, phone) with primary key (author_id, phone), where author_id references Author.

   Step 3, M:N relationship → junction table. Writes cannot live as a foreign key on either entity, so create Writes(author_id FK, isbn FK, royalty_pct) with composite primary key (author_id, isbn). The relationship's own attribute, royalty_pct, naturally lives on this junction table. (A 1:N relationship, by contrast, needs no new table: you put a foreign key on the "many" side.)

   Step 4, verify constraints. Every foreign key, in Writes and in AuthorPhone, must reference an existing parent row (referential integrity), and no attribute of any primary key may be NULL (entity integrity). Pattern to remember: strong entity → table; multivalued attribute → separate table; 1:N → foreign key on the "many" side; M:N → junction table whose primary key is the pair of foreign keys, and that junction table is where relationship attributes go.

✨ Added by the guide — work these before the full problem set.