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

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.