Super Key, Candidate Key and Primary Key: A Worked DBMS Example

One book calls an attribute set a super key, another calls a smaller set a candidate key, and SQL asks you to choose a primary key. The relationship is simple: every candidate key is a super key, but only a minimal super key is a candidate key, and the primary key is one selected candidate key. The relation below proves all three without relying on disconnected definitions. This is a useful foundation for the wider CS Fundamentals syllabus.

Related reading: DBMS key MCQs and keys and integrity constraints.

1. Build the definitions around uniqueness and minimality

A super key is an attribute set that identifies every tuple under the stated functional dependencies. A candidate key is a super key from which no attribute can be removed without losing that property. Minimal means minimal by set inclusion, not the candidate with the fewest attributes among all candidates.

A primary key is the candidate key selected as the table's main row identity. Every unselected candidate key is an alternate key. The relationship is:

primary key ∈ candidate keys ⊆ super keys

Choosing it is a design decision, not another closure calculation. If {StudentID, CourseID, Semester} is a candidate key, then {StudentID, CourseID, Semester, Grade} is a super key but not a candidate key. Removing Grade still leaves a super key.

2. Fix one relation, five rows and three dependencies

Use ENROLMENT(StudentID, Email, CourseID, Semester, Grade), abbreviated as R(S,E,C,M,G). Its non-trivial dependencies are:

• S -> E

• E -> S

• SCM -> G

Trivial dependencies remain implicit. StudentID and Email identify the same student, while a grade belongs to one student, course and semester.

Repeated S101 shows that StudentID alone does not identify an enrolment. Repeated DB101 does the same for CourseID. These rows illustrate the dependencies but cannot prove a key for future rows. Keys follow from schema constraints and functional dependencies, not accidentally unique snapshot values.

The same five-attribute pattern supports a broader treatment of domain, entity and referential integrity in Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples. Here it answers a narrower question: which sets are super keys, which are minimal, why the complete count is six rather than eight, and what changes when one candidate is selected as primary.

3. Compute both candidate keys with closures

The right-hand-side shortcut gives the starting point. C and M never appear on the right side of a non-trivial dependency, so neither can be derived. Both must therefore occur in every candidate key. But CM+ = {C,M} cannot reach S, E or G, so add at least one of S and E. The full method is covered in Attribute Closure and Candidate Keys for GATE.

Now calculate the two plausible closures:

1. SCM+ starts as {S,C,M}. Apply S -> E to get {S,E,C,M}. Now SCM -> G applies, giving {S,E,C,M,G}.

2. ECM+ starts as {E,C,M}. Apply E -> S to get {S,E,C,M}. Then apply SCM -> G, again giving {S,E,C,M,G}.

Both sets are super keys. The deletion tests establish minimality:

• From SCM, delete S to get CM+ = {C,M}; delete C to get SM+ = {S,E,M}; delete M to get SC+ = {S,E,C}.

• From ECM, delete E to get CM+ = {C,M}; delete C to get EM+ = {E,S,M}; delete M to get EC+ = {E,S,C}.

No reduced set reaches all five attributes. Therefore the complete candidate-key set is exactly {SCM, ECM}.

4. Enumerate all six super keys and select one primary key

Every super key must contain C and M, contain at least one of S or E, and may contain G. That condition produces exactly six sets:

1. {S,C,M}

2. {E,C,M}

3. {S,E,C,M}

4. {S,C,M,G}

5. {E,C,M,G}

6. {S,E,C,M,G}

Cross-check the count directly. There are 3 non-empty choices from {S,E}, namely {S}, {E} and {S,E}. For each choice, G may be absent or present. Thus 3 x 2 = 6.

Only {S,C,M} and {E,C,M} are minimal, so only they are candidate keys. For this design, select {S,C,M} as the primary key and call {E,C,M} the alternate key. Selecting {E,C,M} instead would also be logically valid if email is stable and controlled.

5. Translate the key choice into SQL constraints

A compact table definition can record the chosen key and the alternate key:

CREATE TABLE ENROLMENT (
  StudentID VARCHAR(20) NOT NULL,
  Email VARCHAR(254) NOT NULL,
  CourseID VARCHAR(20) NOT NULL,
  Semester VARCHAR(20) NOT NULL,
  Grade VARCHAR(10),
  PRIMARY KEY (StudentID, CourseID, Semester),
  UNIQUE (Email, CourseID, Semester)
);

The UNIQUE constraint represents the alternate candidate key only when its attributes cannot be null under the intended data model. SQL products differ in how UNIQUE treats nulls, which is why the explicit NOT NULL conditions matter.

Keep logical and physical design separate. PRIMARY KEY records the selected logical identifier. It neither requires every query to filter by those columns in that order nor settles secondary indexes. A more normalised design may put StudentID and Email in a separate STUDENT table. That improvement, explained in Normalization in DBMS: 1NF to BCNF, does not change this exercise's key reasoning.

6. Repair the traps behind almost-correct answers

Trap 1: calling every unique-looking attribute a key. Return to the dependencies and closures. StudentID and CourseID visibly repeat here, but even a unique-looking sample column would not establish a schema-level key.

Trap 2: stopping after finding a super key. Apply the deletion test to every attribute. {S,C,M,G} reaches all attributes, but deleting G leaves the candidate key {S,C,M}. The four-attribute set is not minimal.

Trap 3: assuming one primary key means one candidate key. This relation has two candidate keys and one selected primary key. Also, a composite key is not automatically non-minimal. {S,C,M} is composite and minimal because every one-attribute deletion fails.

Trap 4: adding each candidate key's superset count. That double-counts {S,E,C,M} and {S,E,C,M,G}, which contain both candidates. Use the direct condition or a set union. The answer is six, not eight.

7. Use a repeatable order for exam-style questions

Common problems ask you to identify candidate keys from dependencies, classify a set as a super key or candidate key, or count super keys when candidates overlap. Another asks which candidate may become primary. Several choices can be logically permissible because selection depends on design requirements.

Use this order:

1. Write all relation attributes.

2. Mark attributes absent from every right-hand side as mandatory.

3. Add the smallest plausible determinants and compute closures.

4. Delete one attribute at a time to prove minimality.

5. Generate supersets only after candidate keys are final.

6. Account for overlap before counting.

For R(S,E,C,M,G), this means marking C and M as mandatory, adding either S or E, proving SCM and ECM minimal, and then generating six distinct super keys.

8. The short version and the next practice step

A super key uniquely identifies a tuple. A candidate key is a minimal super key. A table may have multiple candidate keys. The primary key is the candidate key selected by the designer. Here, SCM and ECM are candidates, SCM is selected as primary, and their distinct supersets give six super keys.

KnowledgeGate offers 120+ practice questions on keys in DBMS. After checking the closures by hand, ZERO TO HERO is an optional structured next step through DBMS and other core CS subjects.