Second, Third and Boyce–Codd Form

1Second normal form: no partial dependency

Imagine we stuffed item_name onto a table whose key is {sale_id, item_id} — a line-item sheet. item_id → item_name still holds, but item_id is only part of the key. That is a partial dependency: a non-key column determined by a piece of the key. Second normal form forbids it. 2NF = 1NF and no non-prime attribute depends on only part of a candidate key.

The kiosk already avoids this by keeping item_name on Item, keyed by item_id alone. The stuffed sheet would repeat "Tea" on every tea sale. Updating Tea's name would mean finding every copy. The 2NF fix is the split we already did: Item(item_id, item_name, price_rs) and Sale(sale_id, student_id, item_id, qty).

Figure. item_name hanging off a composite sale key is a partial dependency. It belongs on Item.

Spot a partial dependency

1. Composite keyThe sheet is keyed by {sale_id, item_id}.
2. Leftover FDitem_id → item_name uses only item_id.
3. SplitGive item_name its own table keyed by item_id.

2Third normal form: no transitive dependency

Suppose we add warden to Student: student_id → hostel and hostel → warden. Then student_id → warden by transitivity. warden is not a fact about the student; it is a fact about the hostel. That leftover hop is a transitive dependency. Third normal form forbids a non-prime attribute that depends on the key only through some other non-key column.

The 3NF test on an FD X \to Y: either X is a superkey, or Y is prime (part of some candidate key). hostel → warden fails both — hostel is not a key of Student, and warden is not prime. The fix is a Hostel table: hostel → warden lives there, and Student keeps only hostel as a foreign key.

Figure. Key to hostel to warden. The second hop is the 3NF leftover — warden belongs with hostel.

Split the transitive hop

1. See the chainstudent_id → hostel → warden.
2. Name the leftoverwarden should move with hostel.
3. New tableHostel(hostel, warden); Student keeps hostel as a foreign key.

3NF split for the warden

CREATE TABLE hostel (
  hostel TEXT PRIMARY KEY,
  warden TEXT
);
CREATE TABLE student (
  student_id INT PRIMARY KEY,
  name TEXT,
  hostel TEXT REFERENCES hostel(hostel)
);

3BCNF: every determinant is a key

Boyce–Codd normal form is 3NF with the prime-attribute exception removed. For every non-trivial FD X \to Y, X must be a superkey. 3NF still allows that leftover when the right side is prime. BCNF does not.

A kiosk-shaped example: StallSale(student_id, item_id, stall) with {student_id, item_id} → stall (who bought what at which stall) and stall → item_id (each stall sells only one item). stall is a determinant but not a superkey, so the table is not BCNF. item_id is prime, so 3NF may still hold. The BCNF split is Stall(stall, item_id) plus a sale table that names student and stall.

Figure. 3NF lets a non-key determinant survive when Y is prime. BCNF does not. stall → item_id is that leftover.

3NF versus BCNF on StallSale

1. Key{student_id, item_id} → stall. That FD is fine for both.
2. Leftoverstall → item_id. stall is not a superkey.
3. 3NF exceptionitem_id is prime, so 3NF can hold; BCNF still fails.

4Integrity constraints

Keys and normal forms are not the only rules. Entity integrity: a primary key cell is never NULL — sale_id 100 must exist. Referential integrity: a foreign key is either NULL (if the design allows) or matches a live key — Sale.student_id = 1 must be Asha's row, not a dangling 99. Domain integrity: qty is a positive integer, not "two" and not −1.

These are the database saying no. Inserting sale 104 for student_id 99 should fail if 99 is not in Student. That failure is the point: the shop cannot record a purchase by nobody.

Figure. A dashed pointer to a student who is not there. Referential integrity rejects the insert.

A sale that should bounce

-- student 99 does not exist
INSERT INTO sale VALUES (104, 99, 11, 1);
-- referential integrity: reject