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.

The schema comes from this ER diagram, and every answer below can be read straight off it:

Figure 1: The electoral-roll ER diagram the Question 2 schema was mapped from. CANDIDATE is a subclass of MEMBER (the arc on the connecting line, opening towards the superclass), which is why Candidate.enrolmentId is simultaneously its primary key and a foreign key to Member. REGION’s key Code is a composite attribute splitting into Prefix and Suffix, which is why the mapped primary key is the pair. Both EnrolmentID and PassportNum are drawn as key attributes, giving MEMBER two candidate keys.

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

Three things to notice, because each is a different rule doing the work: Member.passportNum is a candidate key that lost the nomination, so it is not a primary key (relational-keys); Region.{prefix, suffix} is the only composite primary key here, and it forces every reference to it to be a composite foreign key; and Candidate.enrolmentId is the one attribute that is a primary key and a foreign key at once, because CANDIDATE is a subclass of MEMBER and subclass mapping gives the subclass relation its superclass’s key.

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], DeptLocations [dNumber, dLocation], Project [pName, pNumber, pLocation, dNum], WorksOn [essn, pNo, hours], Dependent [essn, depName, sex, bDate, relationship]. Two extra business rules, requested by the Board of Directors on top of the DBMS-enforced ones: 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)?

Every answer in this section is a question about the arrows below, so read the foreign-key map before the operations:

Figure 2: The relational diagram for the company database: eight foreign keys over six relations. Arrowheads land on the referenced attribute: an arrow points child → parent. Three separate relations reference Employee.ssn and Employee references itself through Super_ssn, which is why deleting one employee row is the messiest operation in Section B. Attribute names in the diagram are the textbook spellings (Ssn, Dno, Essn, Pno); the prose above uses the camelCase equivalents for the same columns. Each component of a composite key is underlined in its own box, where the written schema convention runs one continuous underline.

Employee

fName mInit lName ssn bDate address sex salary superSsn dNo
John B Smith 123456789 1965-01-09 731 Fondren, Houston, TX M 30000 333445555 5
Franklin T Wong 333445555 1955-12-08 638 Voss, Houston, TX M 40000 888665555 5
Alicia J Zelaya 999887777 1968-01-19 3321 Castle, Spring, TX F 25000 987654321 4
Jennifer S Wallace 987654321 1941-06-20 291 Berry, Bellaire, TX F 43000 888665555 4
Ramesh K Narayan 666884444 1962-09-15 975 Fire Oak, Humble, TX M 38000 333445555 5
Joyce A English 453453453 1972-07-31 5631 Rice, Houston, TX F 25000 333445555 5
Ahmad V Jabbar 987987987 1969-03-29 980 Dallas, Houston, TX M 25000 987654321 4
James E Borg 888665555 1937-11-10 450 Stone, Houston, TX M 55000 NULL 1

Department and DeptLocations

dName dNumber mgrSsn mgrStartDate
Research 5 333445555 1988-05-22
Administration 4 987654321 1995-01-01
Headquarters 1 888665555 1981-06-19
dNumber dLocation
1 Houston
4 Stafford
5 Bellaire
5 Sugarland
5 Houston

Project and WorksOn

pName pNumber pLocation dNum
ProductX 1 Bellaire 5
ProductY 2 Sugarland 5
ProductZ 3 Houston 5
Computerization 10 Stafford 4
Reorganization 20 Houston 1
Newbenefits 30 Stafford 4
essn pNo hours
123456789 1 32.5
123456789 2 7.5
666884444 3 40.0
453453453 1 20.0
453453453 2 20.0
333445555 2 10.0
333445555 3 10.0
333445555 10 10.0
333445555 20 10.0
999887777 30 30.0
999887777 10 10.0
987987987 10 35.0
987987987 30 5.0
987654321 30 20.0
987654321 20 15.0
888665555 20 NULL

Dependent

essn depName sex bDate relationship
333445555 Alice F 1986-04-05 Daughter
333445555 Theodore M 1983-10-25 Son
333445555 Joy F 1958-05-03 Spouse
987654321 Abner M 1942-02-28 Spouse
123456789 Michael M 1988-01-04 Son
123456789 Alice F 1988-12-30 Daughter
123456789 Elizabeth F 1967-05-05 Spouse

The three facts that decide most of Section B: the only department numbers in use are 1, 4 and 5; department 5 already has three projects (ProductX, ProductY, ProductZ), which is the semantic limit; and 888665555 (James Borg) earns 55000, so any subordinate of his earning more breaks the salary rule.

B1 — Insert ('Robert', 'F', 'Scott', 943775543, '1942-06-21', '2365 Newcastle Rd, Bellaire, TX', 'M', 50000, 888665555, 1) into Employee (943775543 is not yet an Employee ssn; the supervisor 888665555 is James Borg, who earns 55000 and runs department 1).

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