Case Study 2: Dirt Road Driving

exercises
tutorial
case-study
databases
relational-model
integrity-constraints

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/projectLeader values both change to valid new values (1919 is a real, existing employee), and nothing here conflicts with a key/entity/referential/semantic rule.
  • Op 2 Yes — Key constraint. id = 2014 already exists in Employee (id is its primary key), so this insert isn’t unique.
  • Op 3 Yes — Referential integrity. TimeLog.projectName references Project.name, but no Project named "Overall Marketing" exists — the foreign key value doesn’t resolve.
  • Op 4 Yes — Entity integrity. Employee.id is the primary key, and it’s NULL here — no part of a primary key may ever be NULL.
  • Op 5 Yes — Referential integrity. TimeLog.employeeID (foreign key to Employee.id) has a row referencing employeeID = 2014 — deleting that Employee row would leave it dangling. (Strictly: under the default ON DELETE RESTRICT — see below.)
  • Op 6 Yes — Semantic (user-defined) constraint. projectLeader = 2014 is an Administration staff 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 CASCADE

Figure 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.

  1. Domain constraint: inserting ("A string is not a number", "Joe", "Blog", "Administration") into Employee — id is meant to be numeric, not a string. (b) Referential integrity: deleting (2020, "Russell", "Turner", "Manager") from Employee while a Project row still has projectLeader = 2020 — the deletion would leave that foreign key dangling.