Applied Class 3: Relational Integrity

exercises
tutorial
databases
relational-model
integrity-constraints

Practice for 2026-03-09-the-relational-model-and-integrity-constraints. See relational-mapping-notation for reference.

Section A — Recap and preview

Question 1 — Matching relational concepts

Match each concept to its description: (1) any minimal set of attributes that can uniquely identify tuples in a relation, (2) defines the structure of a relation, specified during database design, (3) a candidate key chosen to identify tuples in a relation, (4) data in a relation, modified via create/update/delete operations.

Candidate key → 1, Relational Schema → 2, Primary Key → 3, Relational Instance → 4.

Question 2 — Electoral roll schema

Given:

Region [prefix, suffix]

Member [enrolmentId, passportNum, regionPrefix, regionSuffix]
Member.{regionPrefix, regionSuffix} references Region.{prefix, suffix}

Candidate [enrolmentId, name, regionPrefix, regionSuffix]
Candidate.enrolmentId references Member.enrolmentId
Candidate.{regionPrefix, regionSuffix} references Region.{prefix, suffix}

List all examples of each component: candidate key, primary key, composite primary key, foreign key.

  • Candidate key: Member.enrolmentId, Member.passportNum, Candidate.enrolmentId, Region.{prefix, suffix}.
  • Primary key: Member.enrolmentId, Candidate.enrolmentId, Region.{prefix, suffix}.
  • Composite primary key: Region.{prefix, suffix}.
  • Foreign key: Member.{regionPrefix, regionSuffix}, Candidate.{regionPrefix, regionSuffix}, Candidate.enrolmentId.

Question 3 — Schema statements

Select all true statements: (1) a schema is the metadata, or data describing the data, (2) a schema is specified during database design, (3) a schema is created during data updates and changes frequently, (4) a schema is the data in the database at a particular time.

Enter the correct option numbers in ascending order, comma-separated:

1, 2. A schema is metadata specified at design time — it doesn’t change with every data update (that’s the instance), and it isn’t itself “the data” (statements 3 and 4 both describe the instance, not the schema).

Question 4 — Key statements

Select all true statements: (1) a key is a unique identifier of a tuple in the relation, (2) the key constraint implies no two tuples can share the same values for all attributes, (3) a key cannot have multiple attributes, (4) an entity may have more than one primary key, (5) a primary key is a candidate key chosen as the main key for a relation.

Enter the correct option numbers in ascending order, comma-separated:

1, 5. (2) is a weaker, different notion (that’s just basic set semantics — no two tuples are ever fully identical in a relation regardless of keys — not what makes something a key specifically). (3) is false — composite keys exist (e.g. Region.{prefix, suffix} above). (4) is false — an entity can have several candidate keys, but only one of them is chosen as the primary key.

Section B — Integrity constraints in practice

A company database: Employee [ssn, fName, mInit, lName, bDate, address, sex, salary, superSsn, dNo], Department [dName, dNumber, mgrSsn, mgrStartDate], Project [pName, pNumber, pLocation, dNum], WorksOn [essn, pNo, hours], Dependent [essn, depName, sex, bDate, relationship]. Two extra business rules: an employee’s salary cannot exceed their supervisor’s, and a department cannot have more than three active projects at a time. For each operation below, does it violate an integrity constraint (assume any earlier operations in this list have not been applied)?

B1 — Insert ('Robert', 'F', 'Scott', 943775543, '1942-06-21', '2365 Newcastle Rd, Bellaire, TX', 'M', 50000, 888665555, 1) into Employee (943775543 and 888665555 are both not currently Employee SSNs other than as noted; 888665555 belongs to the company president, whose salary far exceeds 50000).

B2 — Insert ('ProductA', 'A', 'Bellaire', 2) into Project (no Department with dNumber = 2 exists).

B3 — Insert ('Production', 4, 943775543, '1988-10-01') into Department (a Department with dNumber = 4 already exists; no Employee with ssn = 943775543 exists).

B4 — Insert (677678989, null, 40.0) into WorksOn (no Employee with ssn = 677678989 exists).

B5 — Insert ('Grace', 'G', 'Chan', 31203126, '1962-06-21', '2312 Anchor Rd, Bellaire, TX', 'F', 58000, 888665555, 1) into Employee (supervisor 888665555 earns less than 58000).

B6 — Delete the WorksOn tuples with essn = 333445555.

B7 — Delete the Employee tuple with ssn = 987654321.

B8 — Modify mgrSsn/mgrStartDate of the Department tuple with dNumber = 5 to 123456789/'1988-10-01' (123456789 is an existing Employee SSN).

B9 — Modify superSsn of the Employee tuple with ssn = 999887777 to 943775543 (no Employee with ssn = 943775543 exists).

B10 — Insert ('ProductSuperDuperSecret', 86, 'Washington', 5) into Project (department 5 already has three active projects).

  • B1 No — this is a well-formed, non-conflicting insert: the new ssn doesn’t already exist (no key violation), the supervisor ssn does exist (no referential violation), and the salary doesn’t exceed the supervisor’s (no semantic violation).
  • B2 Yes — Referential integrity (dNum = 2 doesn’t exist in Department) and domain constraint (pNumber should be an integer, not the string 'A').
  • B3 Yes — Key constraint (dNumber = 4 already exists) and referential integrity (mgrSsn = 943775543 doesn’t exist in Employee).
  • B4 Yes — Entity integrity (pNo, part of WorksOn’s primary key, is NULL) and referential integrity (essn = 677678989 doesn’t exist in Employee).
  • B5 Yes — Semantic constraint (this employee’s salary exceeds their supervisor’s).
  • B6 No — nothing else references WorksOn rows as a foreign key target, and no business rule requires an employee to have work assignments, so deleting them is unconstrained.
  • B7 Yes — Referential integrity (WorksOn, Dependent, Department, and Employee itself all have tuples referencing this ssn, which would be left dangling).
  • B8 No — 123456789 is a valid existing employee, dNumber = 5 already exists (this is an update, not a duplicate insert), and no stated business rule constrains who can be a department’s manager.
  • B9 Yes — Referential integrity (the new superSsn value doesn’t match any existing Employee.ssn).
  • B10 Yes — Semantic constraint (exceeds the “max 3 active projects per department” business rule).