Exercises — Keys — primary, candidate, super, foreign, natural vs surrogate

What we do: apply two questions in order — Q1: is it unique? (super key) Q2: can we remove a column and stay unique? (if no, it's also candidate/minimal).

• (a) $\{emp\_id\}$ — unique by rule → super key. Single attribute, nothing to remove → also candidate key. ✅
• (b) $\{name\}$ — two "John Smith"s allowed → not unique → neither. ✅
• (c) $\{emp\_id,name\}$ — contains emp_id which is already unique, so the pair is unique → super key. But drop name and $\{emp\_id\}$ is still unique → not minimal → not a candidate key. So: super key only. ✅
• (d) $\{email\}$ — unique by rule, single attribute → super key AND candidate key. ✅

False. A foreign key column repeats freely — one student enrols in many courses, so roll_no appears many times. Only the referenced key (Student.roll_no) is unique. This is confusion (c) from the parent note.

No. A primary key must be unique AND NOT NULL. phone is unique but may be NULL, and a NULL means "unknown" — it cannot identify a row. So phone fails the NOT-NULL half of the rule. It could be enforced with a UNIQUE constraint (which allows NULLs), but never as PRIMARY KEY.

CREATE TABLE Employee (
  emp_id  INT PRIMARY KEY,          -- chosen candidate key
  email   VARCHAR(100) UNIQUE,      -- alternate key
  phone   VARCHAR(20)  UNIQUE,      -- unique, NULL allowed
  dept_id INT,
  name    VARCHAR(50),
  FOREIGN KEY (dept_id) REFERENCES Department(dept_id)
);

Why: PRIMARY KEY auto-enforces unique + not null (the official handle). UNIQUE on email keeps it an honest alternate key. The FOREIGN KEY clause is the glue enforcing Referential Integrity — you cannot insert a dept_id that has no matching department row.

What determines a row? The rule "no room double-booked on a date" means the pair $\{room,date\}$ is unique. So $\{room,date\}$ is a super key.

• Remove date: $\{room\}$ — one room appears on many dates → not unique → not a super key.
• Remove room: $\{date\}$ — one date has many rooms → not unique → not a super key.

Neither single column works, so $\{room,date\}$ is minimal → it is a candidate key. Since nothing else is unique, it is the only candidate key. {room} alone fails because a room recurs across dates — it does not functionally determine the whole row.

$\{email,phone\}$ is unique (it contains email, already unique) → super key. But remove phone: $\{email\}$ is still unique → the subset works → the pair is not minimal → not a candidate key. Only super key.

Three tables; the many-to-many between student and course needs a junction table.

CREATE TABLE Student (
  roll_no INT PRIMARY KEY,
  name    VARCHAR(50)
);
CREATE TABLE Course (
  course_id  INT PRIMARY KEY,
  title      VARCHAR(50),
  teacher    VARCHAR(50)          -- one teacher per course
);
CREATE TABLE Takes (
  roll_no   INT,
  course_id INT,
  grade     CHAR(2),
  PRIMARY KEY (roll_no, course_id),          -- composite key
  FOREIGN KEY (roll_no)   REFERENCES Student(roll_no),
  FOREIGN KEY (course_id) REFERENCES Course(course_id)
);

Why: Takes has a composite primary key $\{roll\_no,course\_id\}$ — a student can take a course only once, and this pair is exactly what makes a row unique. Both parts are also foreign keys pointing back to the parent tables — this is normal and correct. grade is a plain attribute depending on the whole key (satisfying 2NF, see Normalization).

Failure: an employee changes their email. Because email was the primary key, every foreign key row referencing them (payroll, enrolments, logs) must be updated in lock-step, or referential integrity breaks. Emails are also long strings → fat indexes.

Fix: use a surrogate emp_id (auto-increment) as primary key — stable and meaningless, so it never changes — and keep email VARCHAR(100) UNIQUE as an alternate key to still block duplicate people. This is confusion (d) resolved: surrogate for the handle, natural key kept under a UNIQUE constraint.

What makes a set a super key here? A set is a super key iff it contains $A$ or contains $B$ (either alone determines the whole row; any superset of a super key is a super key).

Total attribute-subsets of $\{A,B,C,D\}$: $2^4=16$. Count the ones that are not super keys — those contain neither $A$ nor $B$, i.e. subsets of $\{C,D\}$: there are $2^2=4$ of them ($\varnothing ,\{C\},\{D\},\{C,D\}$).

$\text{Super keys}=16-4=\boxed{12}.$

Fix $C\in S$. The remaining attributes $A,B,D$ are each free (in or out): $2^3=8$ subsets that contain $C$. From these subtract the ones with neither $A$ nor $B$: those are subsets of $\{C,D\}$ that contain $C$, namely $\{C\}$ and $\{C,D\}$ → $2$ of them.

$\text{Super keys containing }C=8-2=\boxed{6}.$

The employee row survives; its dept_id becomes NULL. Integrity still holds: a foreign key column is allowed to be NULL, and NULL means "references nothing" — which is a legal state, not a dangling pointer. It would only break if dept_id still held 7 after department 7 vanished. (Contrast: ON DELETE RESTRICT would have blocked the department deletion; CASCADE would have deleted the employee row too.)

• Super key = any name-tag description that's unique — extra words allowed.
• Candidate key = the shortest such descriptions (nothing removable).
• Primary key = the one candidate key we officially stamp on the tag — must never be blank.
• Foreign key = borrowing another table's tags; the borrowed number can repeat and can be blank.
• Natural vs surrogate = a true fact (email) vs a stable meaningless ticket (id).