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

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, DeptCodeEmail -> StudentID
Now compute closures from the attributes themselves.
StudentID+starts as{StudentID}. The first dependency addsEmail,StudentNameandDeptCode, giving{StudentID, Email, StudentName, DeptCode}.Email+starts as{Email}.Email -> StudentIDaddsStudentID; then the first dependency addsStudentNameandDeptCode. Its closure is also the full relation.StudentName+ = {StudentName}andDeptCode+ = {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 |
|---|---|
| Primary key |
| Primary key |
| Primary key |
|
|
| Nullable foreign key to |
|
|
| Composite primary key |
| Foreign keys to |
| One of |
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.

4. Work eight interdependent operations in order and recompute the legal state
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 | Both candidate-key values are new; parent | Yes | Add Student 103 |
2 | Insert Student | Alternate key | No | No change; the email duplicates Student 101 |
3 | Insert Student | Entity integrity on the primary key | No | No change; |
4 | Insert Student | New candidate-key values; nullable foreign key | Yes | Add Student 104 with no department reference |
5 | Insert Enrolment | New composite key; both parent rows exist | Yes | Add the enrolment |
6 | Insert Enrolment | Composite primary-key uniqueness | No | No change; changing |
7 | Insert Enrolment | Referential integrity | No | No change; Student 999 does not exist |
8 | Delete Department | All surviving child references | No | No change; Students 101 and 103 and Courses DB301 and OS201 still reference |
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).

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
D10is 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
StudentIDwhen 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.
Keep learning
Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples
Learn how superkeys, candidate keys, primary keys, foreign keys and integrity rules work together through one university database and a complete closure proof.
INSERT, UPDATE and DELETE in SQL: Runnable DML Tutorial with Safe Worked Examples
Follow one table from its first insert through targeted updates and a rolled-back deletion. The runnable lab makes affected rows, defaults and NULL behaviour visible.
SQL DML: INSERT, UPDATE, and DELETE with Transaction-Safe Examples
Follow one employees table through deliberate inserts, a calculated update, a targeted delete, and a savepoint rollback in PostgreSQL.
Constraints and Keys in SQL: Runnable Examples, Failed Inserts, and Fixes
Build three related SQL tables, test every major constraint with successful and rejected inserts, and trace a foreign-key delete action row by row.





