Case Study 2: Dirt Road Driving
Group case study applying integrity constraints from 2026-03-09-the-relational-model-and-integrity-constraints to Dirt Road Driving’s internal payroll system. See relational-mapping-notation for reference.
Section A — The schema
Dirt Road Driving’s payroll backend, built by an external developer:
Employee [id, firstName, lastName, role]
Project [name, description, funding, projectLeader]
Project.projectLeader references Employee.id
TimeLog [employeeID, projectName, date, hoursWorked, approved]
TimeLog.employeeID references Employee.id
TimeLog.projectName references Project.name
Company policy (a semantic/business-rule constraint, not enforceable by the schema’s structure alone): administration staff cannot be project leaders.
Question 1 — Identify a key and a foreign key
Any candidate key works, e.g. Employee.id, Project.name, or TimeLog.{employeeID, projectName, date} (composite). Any foreign key works, e.g. Project.projectLeader, TimeLog.employeeID, or TimeLog.projectName.
Section B — Integrity constraint violations
Sample data: Employee has (1919, Diluen, Smith, Developer), (2014, Daniel, Johnson, Administration), (2019, Annie, Fang, Developer), (2020, Russell, Turner, Manager). Project has ("Website Setup", ..., 12000, 2019), ("2020 Marketing", ..., 40000, 2020). TimeLog has 5 rows, including (1919, "Website Setup", 2/1/2020, 5, true) and (2020, "2020 Marketing", 2/1/2020, 5, true), among others — no row with employeeID = 1919 and projectName = "2020 Marketing". For each operation, does it violate an integrity constraint?
Operation 1 — Update ("Website Setup", ..., 12000, 2019) to ("Website Setup", ..., 20000, 1919) in Project (changes funding and projectLeader; 1919 is a Developer).
Operation 2 — Insert (2014, "Rebecca", "Zhang", "Administration") into Employee.
Operation 3 — Update (2020, "2020 Marketing", 2/1/2020, 5, true) to (1919, "Overall Marketing", 2/1/2020, 5, true) in TimeLog.
Operation 4 — Insert (NULL, "Test", "Test", "Test") into Employee.
Operation 5 — Delete (2014, "Daniel", "Johnson", "Administration") from Employee.
Operation 6 — Insert ("Talent Recruitment Initiative", ..., 10000, 2014) into Project (2014 is Administration).
- Op 1 No — a well-formed update;
funding/projectLeadervalues both change to valid new values (1919is a real, existing employee), and nothing here conflicts with a key/entity/referential/semantic rule. - Op 2 Yes — Key constraint.
id = 2014already exists inEmployee(idis its primary key), so this insert isn’t unique. - Op 3 Yes — Referential integrity.
TimeLog.projectNamereferencesProject.name, but noProjectnamed"Overall Marketing"exists — the foreign key value doesn’t resolve. - Op 4 Yes — Entity integrity.
Employee.idis the primary key, and it’sNULLhere — no part of a primary key may ever beNULL. - Op 5 Yes — Referential integrity.
TimeLog.employeeID(foreign key toEmployee.id) has a row referencingemployeeID = 2014— deleting thatEmployeerow would leave it dangling. (Strictly: under the defaultON DELETE RESTRICT— see below.) - Op 6 Yes — Semantic (user-defined) constraint.
projectLeader = 2014is anAdministrationstaff member, violating the business rule that administration staff cannot be project leaders — this isn’t something the schema’s structure alone captures (no foreign key/domain/key rule is broken), it’s an organisation-specific policy.
What a deletion actually “violates”
Ops 3 and 5 are both answered “referential integrity”, but only one of them is a fact about the data. Op 3 sets TimeLog.projectName to "Overall Marketing", and no Project row carries that name — nothing the schema can be told will make that value resolve. Op 5 deletes an Employee row that a TimeLog row still points at, and there the foreign key declaration chooses the outcome: what happens to the child rows is a referential triggered action, written alongside the foreign key in the CREATE TABLE (or added later by ALTER TABLE), in the same shape the lecture uses:
CONSTRAINT employee_fk FOREIGN KEY (employeeID) REFERENCES Employee(id)
ON DELETE RESTRICT ON UPDATE CASCADEFigure 1: The four referential triggered actions, applied to this case study’s own foreign key. Only the first refuses the delete, so “Op 5 violates referential integrity” is really “Op 5 violates it under the default ON DELETE RESTRICT” — under the other three the delete succeeds and the child row is redefined instead. The crimson arrow is the course’s foreign-key convention: arrowhead on the referenced attribute, pointing from child to parent. The brief states only that some TimeLog row references employee 2014 without spelling that row out; the row drawn stands in for it.
So a “violation” of referential integrity on a delete is a statement about the schema as much as about the data — the same delete is legal, illegal or silently destructive depending on one clause. The identical four choices exist as ON UPDATE … and govern a change to a referenced value: renaming Employee.id 2014 raises exactly the same question as deleting the row. See foreign-keys and sql-ddl.
Question 2 — Original examples
Give an original example (not copied from above) of an operation that would cause (a) a domain constraint violation, (b) a referential integrity constraint violation.
- Domain constraint: inserting
("A string is not a number", "Joe", "Blog", "Administration")intoEmployee—idis meant to be numeric, not a string. (b) Referential integrity: deleting(2020, "Russell", "Turner", "Manager")fromEmployeewhile aProjectrow still hasprojectLeader = 2020— the deletion would leave that foreign key dangling.