Terminology

Codd's model imposes strict properties on relations: every attribute value is atomic (no lists or nested tables — this is 1NF), tuples are unordered (a relation is a set, so there is no "first row"), attribute names within a relation are unique, and — in the pure model — no duplicate tuples exist. Note the practical wrinkle: SQL tables are bags, not sets, and happily store duplicate rows unless a key constraint forbids them. This set-vs-bag gap is why SELECT DISTINCT exists.

Keys — the most tested GATE topic in DBMS

GATE: Super key vs Candidate key: Every candidate key is a super key, but not every super key is a candidate key. Super key = uniqueness only. Candidate key = uniqueness + minimality. If {A,B} uniquely identifies tuples but {A} alone also does, then {A,B} is a super key but NOT a candidate key.

Functional dependencies and attribute closure — how keys are actually found

A functional dependency (FD) X → Y means: whenever two tuples agree on the attributes in X, they must also agree on Y. "StudentID → Name" reads as "StudentID determines Name." FDs are the machinery behind every candidate-key question and all of normalization — and the tool for working with them is the attribute closure.

The closure of X, written X⁺, is the set of every attribute X can determine. Algorithm: start with X⁺ = X; repeatedly scan the FDs, and whenever some FD Y → Z has Y ⊆ X⁺, add Z to X⁺; stop when nothing new is added. X is a super key iff X⁺ contains every attribute of R. X is a candidate key iff it is also minimal — no proper subset has a full closure.

Compute (CD)+:
  Start:        (CD)+ = {C, D}
  CD → A fires: (CD)+ = {C, D, A}
  A → B fires:  (CD)+ = {C, D, A, B}   ← all of R, so CD is a super key

Minimality check:
  C+ = {C}      (nothing fires)  — not a super key
  D+ = {D}      (nothing fires)  — not a super key
  → CD is a CANDIDATE KEY

Trick for finding all candidate keys quickly:
  Attributes appearing on NO right-hand side (here: D) must be
  in EVERY candidate key. Grow from D: AD+ = ABCD, BD+ = ABCD,
  CD+ = ABCD → candidate keys are {AD, BD, CD}.

GATE shortcut: Any attribute that never appears on the right-hand side of any FD cannot be derived by anything — it must be part of every candidate key. Start every candidate-key hunt by collecting these attributes, then grow the set with closures. This one trick halves the time on most GATE key-finding questions.

NULLs and three-valued logic — the model's sharpest edge

SQL comparisons involving NULL do not return TRUE or FALSE — they return UNKNOWN, and WHERE keeps only rows evaluating to TRUE. This three-valued logic (3VL) produces the classic surprises: NULL = NULL is UNKNOWN (use IS NULL), WHERE Salary <> 50000 silently drops rows with NULL salary, and NOT IN against a list containing a NULL matches nothing at all. Aggregates ignore NULLs — except COUNT(*), which counts rows, not values.

Interview favorite: Why does SELECT * FROM Emp WHERE Dept = 'IT' OR Dept <> 'IT' not return all rows? Because rows where Dept IS NULL evaluate both conditions to UNKNOWN, and UNKNOWN OR UNKNOWN = UNKNOWN — the row is filtered out. The predicate is not a tautology under three-valued logic.

Integrity constraints

CREATE TABLE Department (
    DeptID   INT         PRIMARY KEY,
    DeptName VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Employee (
    EmpID    INT          PRIMARY KEY,           -- Entity integrity: no NULL PK
    Name     VARCHAR(100) NOT NULL,
    Email    VARCHAR(100) UNIQUE,
    DeptID   INT          REFERENCES Department(DeptID)  -- Referential integrity
                          ON DELETE SET NULL
                          ON UPDATE CASCADE,
    Salary   DECIMAL(10,2) CHECK (Salary > 0),  -- Domain constraint
    Age      INT           CHECK (Age BETWEEN 18 AND 65)
);

-- Referential integrity violation:
-- INSERT INTO Employee VALUES (1,'Ravi','r@x.com', 99, 50000, 30)
-- ERROR: DeptID 99 does not exist in Department

• Entity Integrity: Primary key cannot be NULL.
• Referential Integrity: FK value must match existing PK or be NULL.
• Domain Integrity: Values must fit the column domain (data type + CHECK).
• User-defined Integrity: Business rules via CHECK constraints.

Practice questions (GATE-style)

1. Relation R(A,B,C,D). FDs: A→B, B→C, CD→A. Find all candidate keys. (Answer: Test: CD→A,B,C,D — CD is a candidate key. AD→B,C and AD→D so AD→ABCD — AD is candidate key. BD→C, BD→CD→A — BD is candidate key. Answer: {CD, AD, BD}.)
2. What constraint is violated when you insert a row with NULL as the primary key? (Answer: Entity integrity.)
3. ON DELETE CASCADE: if Department with DeptID=5 is deleted, what happens to Employees with DeptID=5? (Answer: They are automatically deleted — CASCADE propagates to referencing rows.)
4. Is {StudentID, CourseID, InstructorID} a candidate key if {StudentID, CourseID} already identifies each row? (Answer: No — it is a super key but not candidate key because it is not minimal.)
5. Difference between primary key and unique key: (Answer: Primary key: cannot be NULL, only one per table. Unique key: can be NULL (in most DBMS), multiple unique keys allowed per table. Both enforce uniqueness.)