Normalization Theory

No. product_name depends only on product_id and order_date only on order_id, both parts of the composite key, so both are partial dependencies. Split into ORDER(order_id, order_date), PRODUCT(product_id, product_name), and ORDER_LINE(order_id, product_id, quantity).

No. dept_id determines dept_name and dept_location, so both depend on the key only through a non-key attribute. Split out DEPARTMENT(dept_id, dept_name, dept_location) and leave EMPLOYEE(emp_id, emp_name, dept_id) with a foreign key.

Repeating a fact means you cannot record it without an unrelated row (insertion anomaly), must rewrite every copy to change it (update anomaly, with drift if partial), and lose it when the last carrier row is deleted (deletion anomaly). Move the fact to its own table with a foreign key.

BCNF requires that for every non-trivial functional dependency X to Y in a table, X is a superkey. The determinant is X, the left-hand side. Nothing but a superkey may determine anything.

Denormalizing means deliberately storing redundant data — a copied column, a precomputed count, a summary table — so reads avoid joins or aggregation. You gain read speed and simpler queries; you give up a single source of truth, so every write path must keep the copies in step or they drift.

X -> Y means any two rows that agree on every column of X must also agree on Y: X determines Y. It is a rule about all legal data, so sample rows can disprove it but never prove it.

A superkey is any column set that determines all other columns. A candidate key is a minimal superkey - remove any column and it stops being one. The primary key is the candidate key you designate. Prime attributes are those inside some candidate key.

You lose foreign keys, useful indexing and typing: substring matching finds false hits, per-tag counts need string surgery, adding one tag rewrites the whole string so concurrent updates lose each other, and typos accumulate. Fix: a child table with one row per (entity, tag).

2NF means the table is in 1NF and no non-key attribute depends on only part of a composite candidate key. That part-of-the-key dependency is a partial dependency; remove it by moving the attribute into a table keyed by that part.

3NF means the table is in 2NF and no non-key attribute is determined by another non-key attribute. That indirect chain, key determines A and A determines B, is a transitive dependency; move A and B into their own table keyed by A.

A split is lossless when the natural join of the two pieces returns exactly the original rows. The condition: the shared columns must functionally determine all of at least one piece, meaning they form a superkey there. Otherwise the join invents spurious rows that were never in the original.

Take (student, course, teacher) where each course has one teacher and a student takes a course from one teacher. Candidate keys are (student, course) and (student, teacher). The dependency course to teacher has a non-superkey determinant, but teacher is part of a candidate key, so 3NF permits it and BCNF does not.

Three options: a database trigger on the child table, an application write inside the same transaction, or an asynchronous job. Triggers catch every write path; application code misses backfills and scripts; async jobs are cheap but stale. Always update with a relative change, and add a reconciliation query to repair drift.

Start with X, then repeatedly add the right side of any dependency whose left side is already contained in the set, until nothing changes. The result X+ is everything X determines. If X+ covers all columns, X is a superkey; X+ also tests whether X -> Y is implied.

A partial dependency means a non-key attribute depends on a proper subset of a candidate key. A one-attribute key has no non-empty proper subset, so no partial dependency can exist. Only composite candidate keys can violate 2NF.

Textbooks and instructors are unrelated facts about a course, so the table is forced to store their cross product. Row counts multiply, adding one textbook costs one insert per instructor, and partial writes imply pairings that are not real. Split into two tables keyed by course_id.

It encodes a one-to-many relationship horizontally: the limit of three is arbitrary, most cells are NULL, a fourth phone needs DDL and code changes, and any search must check all three columns. Fix: a child table with one row per phone plus a foreign key.

Duplicates are indistinguishable, so you cannot update or delete just one of them, an ORM or replication cannot identify a row, and joins multiply counts silently. Repair: find the duplicate groups, decide which to keep, delete the rest, then add a primary key and a unique constraint on the business identity.

A multivalued dependency X to Y means fixing X fixes a whole set of Y values, independently of the table's other columns. 4NF says every nontrivial multivalued dependency must have a superkey on its left side, otherwise split the table so each independent set lives on its own.

Strictly yes: a cell holding a collection the schema must decompose is not atomic. It is defensible when the value is opaque to the database — always read and written whole with its row, never filtered per element, never joined, and never subject to a foreign key or per-element constraint.

A surrogate key hides the composite natural key, it does not remove the duplicated facts or their update anomalies. If the natural key is still declared unique it remains a candidate key and 2NF is still violated; prove it by counting distinct dependent values per determinant.

For every non-trivial dependency X to A, 3NF requires X to be a superkey or A to be a prime attribute (part of some candidate key). The second clause is the relaxation that makes 3NF strictly weaker than BCNF and always reachable by a lossless, dependency-preserving decomposition.

Formally yes: zip_code is a non-key attribute determining city and state, a transitive dependency, so 3NF fails. Whether to split is a judgement call, because the assumed dependency often does not hold in real postal data and the reference table needs an owner and a refresh process.

Entity-per-table modelling with one junction table per relationship produces 4NF schemas by accident, so there is nothing left to fix. Higher forms also need semantic input no tool can derive and no constraint can enforce. Check them when you see wide attribute tables, spreadsheet imports, or multiplying row counts.

A decomposition is dependency-preserving when every original functional dependency can still be checked inside a single resulting table. If one spans two tables, no single-table constraint can enforce it, so every write needs a join-based check via trigger or application logic, with race conditions under concurrency.

First decide whether the column is a point-in-time snapshot of where the order shipped, which is legitimate, or a stale copy of the current address, which is a defect. Quantify the drift, pick an authoritative source, extract addresses to their own table with a foreign key, and close the write paths that let copies diverge.

BCNF decomposition is always lossless-join but not always dependency-preserving: some original dependency ends up spanning two tables, so no single table's keys enforce it any more. You either accept 3NF instead, or decompose and enforce the stray dependency with a trigger, a redundant column plus a unique index, or reconciliation.

Measure first: confirm the query is hot and that the join or aggregate dominates its plan. Exhaust cheaper fixes — indexes, especially covering ones, query rewrites, caching. Denormalize when the cost grows with data you cannot bound, the read rate greatly exceeds the write rate, and you can name a sync mechanism and a drift check.

Armstrong's axioms are reflexivity, augmentation and transitivity; they are sound and complete, so everything implied can be derived from them. A minimal cover is an equivalent dependency set with single-attribute right sides, no extraneous left-side attributes and no redundant dependencies.