Keys and Integrity Constraints in DBMS: One Cross-Concept Worked Example

Follow one college-registration database from functional dependencies to declared keys, then decide which of eight inserts and deletes preserve integrity.

KnowledgeGate Team

Exam prep & CS education

Updated 17 Nov 20266 min read
Keys and Integrity Constraints in DBMS: One Cross-Concept Worked Example

Primary, candidate and foreign keys are easy to memorise separately. One question becomes harder when it combines them across a changing database state. Eight ordered inserts and deletes on a college-registration schema force key, domain, entity and referential checks to interact. Every accepted operation changes the baseline for the next; every rejection preserves it. Related revision sits in CS Fundamentals.

1. Put every key and integrity rule in its proper role

A superkey is any attribute set that uniquely identifies a tuple. A candidate key is a minimal superkey, so no proper subset still identifies it. The primary key is the chosen candidate key; an alternate key is an unchosen candidate key. A composite key contains multiple attributes. A foreign key refers to a declared candidate key in the same or another relation.

Keep three layers separate. Domain integrity controls permitted types and values. Entity integrity forbids NULL in every primary-key component. Referential integrity requires every non-NULL foreign-key value or tuple to match an existing referenced-key value. Foreign-key values may repeat unless another constraint makes them unique.

Our relations are Department(DeptCode, DeptName), Student(StudentID, Email, StudentName, DeptCode), Course(CourseCode, Title, DeptCode) and Enrolment(StudentID, CourseCode, Term, Grade). Enrolment has a three-column natural key, not a surrogate key.

2. Derive candidate keys before choosing a primary key

For Student(StudentID, Email, StudentName, DeptCode), use these functional dependencies:

  • StudentID -> Email, StudentName, DeptCode

  • Email -> StudentID

Now compute closures from the attributes themselves.

  • StudentID+ starts as {StudentID}. The first dependency adds Email, StudentName and DeptCode, giving {StudentID, Email, StudentName, DeptCode}.

  • Email+ starts as {Email}. Email -> StudentID adds StudentID; then the first dependency adds StudentName and DeptCode. Its closure is also the full relation.

  • StudentName+ = {StudentName} and DeptCode+ = {DeptCode} because neither attribute determines anything else.

Therefore {StudentID} and {Email} are the two candidate keys. Choose StudentID as primary and enforce Email as UNIQUE NOT NULL. Each is minimal because its only proper subset is empty. Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples owns the broader definitions and full closure proof; the sequence below uses these two keys only to drive an eight-operation state trace. {StudentID, Email} remains a non-minimal superkey.

3. Declare the four-relation schema and fix one starting state

The declarations can be recorded without depending on one database vendor:

Relation and attributes

Constraint declaration

Department.DeptCode

Primary key

Course.CourseCode

Primary key

Student.StudentID

Primary key

Student.Email

UNIQUE NOT NULL alternate key

Student.DeptCode

Nullable foreign key to Department.DeptCode

Course.DeptCode

NOT NULL foreign key to Department.DeptCode

Enrolment(StudentID, CourseCode, Term)

Composite primary key

Enrolment.StudentID, Enrolment.CourseCode

Foreign keys to Student.StudentID, Course.CourseCode

Enrolment.Grade

One of {A, B+, B, C, F}

Fix this exact initial state:

  • Department = {(D10, CSE), (D20, ECE)}

  • Student = {(101, ananya@college.in, Ananya, D10), (102, bharat@college.in, Bharat, D20)}

  • Course = {(DB301, Database Systems, D10), (OS201, Operating Systems, D10)}

  • Enrolment = {(101, DB301, 2026S, A), (102, DB301, 2026S, B+)}

The parent key D10 occurs once in Department but may repeat in child tables. StudentID 101 may likewise occur in many enrolments. Only the full triple (StudentID, CourseCode, Term) must be unique. Parent keys therefore migrate to the many side as foreign keys.

Four starting tables with primary and alternate keys marked, plus foreign-key arrows connecting department, student, course and enrolment rows.

Apply every accepted operation before the next test. A rejection makes no change, so the next step sees the legal state.

Step

Proposed operation

Rule checked

Accepted?

State consequence

1

Insert Student (103, charu@college.in, Charu, D10)

Both candidate-key values are new; parent D10 exists

Yes

Add Student 103

2

Insert Student (104, ananya@college.in, Dev, D20)

Alternate key Email must be unique

No

No change; the email duplicates Student 101

3

Insert Student (NULL, esha@college.in, Esha, D10)

Entity integrity on the primary key

No

No change; StudentID cannot be NULL

4

Insert Student (104, dev@college.in, Dev, NULL)

New candidate-key values; nullable foreign key

Yes

Add Student 104 with no department reference

5

Insert Enrolment (103, DB301, 2026S, A)

New composite key; both parent rows exist

Yes

Add the enrolment

6

Insert Enrolment (103, DB301, 2026S, B+)

Composite primary-key uniqueness

No

No change; changing Grade does not change the key

7

Insert Enrolment (999, DB301, 2026S, A)

Referential integrity

No

No change; Student 999 does not exist

8

Delete Department D10 with restrictive action

All surviving child references

No

No change; Students 101 and 103 and Courses DB301 and OS201 still reference D10

The accepted additions are exactly Students (103, charu@college.in, Charu, D10) and (104, dev@college.in, Dev, NULL), plus Enrolment (103, DB301, 2026S, A). Parent rows and all initial rows remain. Step 5 succeeds because step 1 added Student 103; step 6 then fails because step 5 claimed composite key (103, DB301, 2026S).

Eight-step legality flow: steps 1, 4 and 5 accepted, steps 2, 3, 6, 7 and 8 rejected, ending with the final legal database state.

5. Change one declaration and see which answers change

Foreign Key and Referential Integrity in DBMS: Worked Examples and Exam Traps isolates a six-operation parent-child trace. Step 8 instead changes one declaration inside a four-relation schema that also enforces alternate and composite keys. Consider each alternative separately.

With ON DELETE SET NULL on both direct child references, deleting D10 works only if Student.DeptCode and Course.DeptCode permit NULL. Course does not, so that alternative requires changing its column declaration. With cascades, deleting D10 removes or propagates through dependent rows according to every downstream action. The final Enrolment state cannot be calculated until those actions are declared.

Step 3 fails because primary-key StudentID cannot be NULL; step 4 succeeds because nullable DeptCode = NULL asserts no reference. A non-NULL D90 would fail without a matching Department row.

Composite references match as tuples. If Offering(CourseCode, Term) contains only (DB301, 2026S), that child pair matches but (DB301, 2026F) does not. Both components must match one parent tuple.

6. Cross-concept traps and the exact repair for each

  • Trap: Choose {StudentID, Email} as a candidate key. Wrong conclusion: Superkey status is enough. Repair: Test every proper subset. Either single attribute already determines the row, so the pair is not minimal.

  • Trap: Treat alternate as optional. Wrong conclusion: Duplicate emails are acceptable. Repair: An alternate key is still a candidate key and must remain unique.

  • Trap: Call every repeated value a key violation. Wrong conclusion: Students 101 and 103 cannot both use D10. Repair: Foreign-key repetition is allowed on the many side.

  • Trap: Check only the changed tuple. Wrong conclusion: Deleting D10 is legal because the parent key is unique. Repair: Inspect every surviving child reference after a parent change.

  • Trap: Confuse normalization with integrity enforcement. Wrong conclusion: A BCNF design automatically prevents orphaned rows. Repair: Normalization from 1NF to BCNF addresses dependency-based design, while declared foreign keys enforce references across relations.

7. How a cross-concept exam question can test the schema

A cross-concept question may ask for candidate keys from FDs, key roles, the first integrity violation, or the final state after rejected operations.

Check these statements:

  • I. {StudentID, Email} is a candidate key.

  • II. Two enrolment rows may share StudentID when their full composite keys differ.

  • III. Two students may share DeptCode D10.

  • IV. A foreign key may never reference an alternate key.

Only II and III are correct. I ignores minimality because each attribute alone is a candidate key. IV is false because a foreign key may reference any declared candidate key, not just the primary key.

Practise mixed key-and-integrity questions until both the closure step and the state check feel routine. Placement-focused learners can revise with CS Fundamentals for Placements by Sanchit Sir.

8. Keys and integrity constraints: the short version and next step

Derive minimal identifiers, declare keys, write the current parent-key sets, then test integrity after each accepted operation. Here the candidate keys are {StudentID} and {Email}. Steps 1, 4 and 5 pass; steps 2, 3, 6, 7 and 8 fail.

Redo step 8 after giving every relevant foreign key ON DELETE CASCADE, including both Enrolment references, and write the complete surviving state. Use Zero to Hero Complete CS Course to study DBMS with other core CS subjects. Finish by drawing the key and reference graph.