Normalization — 1NF, 2NF, 3NF, BCNF — anomalies each resolves: Question bank

Before we start, one shared vocabulary reminder so no symbol sneaks in undefined:

True or false — justify

Every item below is a statement people confidently believe. Decide true/false and give the reason.

Higher normal form is always the better design choice.

False. More normalization means more tables, hence more JOINs on every read, and BCNF can even break dependency preservation. 3NF is the usual sweet spot; sometimes you deliberately denormalize.

A table with a single-column primary key is automatically in 2NF.

True. A partial dependency needs a proper subset of the key to exist, and a one-column key has no proper subset (the empty set determines nothing useful). So there is nothing for 2NF to forbid.

If a table has no visually repeated data, it must be in BCNF.

False. Normal forms are defined on functional dependencies, not on eyeballed duplication. A small table with no repeated values can still have a determinant that isn't a superkey, violating BCNF.

Every relation in 3NF is also in BCNF.

False. 3NF has an escape hatch: a non-superkey determinant is tolerated if the attribute it determines is prime. Teacher → Subject survives 3NF (Subject is prime) but fails BCNF.

Every relation in BCNF is also in 3NF.

True. BCNF's rule "every determinant is a superkey" is strictly stronger — it satisfies 3NF's condition automatically, since "X is a superkey" is the first branch of the 3NF test.

1NF is only about strings that look like lists.

False. 1NF forbids any non-atomic value: nested tables, repeating column groups (phone1, phone2, phone3), and sets — not just comma-separated strings.

Normalization can never remove information from the database.

True (when done right). A proper normalization is a lossless-join decomposition — you can reconstruct the original table exactly by joining the pieces. A careless split, though, can be lossy; that's a separate correctness check.

X \to Y

and

Y \to X

, then

X

and

Y

are interchangeable as determinants.

True. They determine each other, so they have identical closures — anywhere one is a superkey, so is the other. This is why alternate candidate keys arise.

A trivial dependency like

{A, B} \to A

can violate BCNF.

False. BCNF only constrains non-trivial FDs (where the right side isn't already inside the left). A trivial FD is free and never counts against any normal form.

Reaching 3NF guarantees all your original functional dependencies are still enforceable.

True. A 3NF decomposition can always be made both lossless and dependency-preserving. BCNF is the form that sometimes cannot preserve every FD.

Spot the error

Each item states a claim with a hidden mistake. Reveal the correction.

"Teacher → Subject is fine because Subject is prime, so the table is fully normalized."

The error is equating 3NF with "fully normalized." It is in 3NF, but BCNF still fails because Teacher is not a superkey. "Prime" saves 3NF, not BCNF.

"The table Enroll(StudentID, CourseID, Grade, StudentName) is in 2NF because every column holds a single value."

Single values only satisfy 1NF. 2NF fails: StudentID → StudentName is a partial dependency on part of the key {StudentID, CourseID}.

"2NF violations happen because the table has too many columns."

Column count is irrelevant. A violation needs a composite candidate key and a non-prime attribute depending on a proper subset of it — a wide table with a single-column key is still fine.

"Emp(EmpID, DeptID, DeptCity) with EmpID → DeptID → DeptCity violates 2NF."

It violates 3NF, not 2NF. The key is the single column EmpID, so no partial dependency is possible; the problem is the transitive dependency through the non-prime middleman DeptID.

"To fix a transitive dependency, delete the middleman column."

You don't delete it — you split it into its own table. Dept(DeptID, DeptCity) stores the fact once; Emp(EmpID, DeptID) keeps the link. Deleting DeptID would lose the connection.

"An FD is just any correlation you notice in the sample data."

An FD is a rule that must always hold, coming from the real-world semantics, not from a lucky snapshot. Two rows agreeing today doesn't make an FD; the business rule does.

"Since {Student, Subject} is a candidate key, Subject is non-prime."

Subject is part of a candidate key, so it is prime. Prime means "belongs to some candidate key," and {Student, Subject} is exactly such a key.

Why questions

Answer with the mechanism, not a definition restatement.

Why does an update anomaly happen at all?

Because a single real-world fact was stored in many rows. Changing that fact means editing every copy; miss one and the table contradicts itself.

Why does 2NF only "bite" tables with composite keys?

A partial dependency is defined as dependence on a proper subset of a key. Only a multi-column key has proper subsets, so single-column keys leave nothing for the rule to catch.

Why is BCNF strictly stronger than 3NF rather than a different flavor?

BCNF's rule ("every determinant is a superkey") is 3NF's first branch with the "or the attribute is prime" escape hatch removed. Removing an allowance can only tighten a condition, so BCNF ⊂ 3NF.

Why can decomposing into BCNF sometimes lose a functional dependency?

Splitting a table can scatter the columns of one FD across two tables, so no single table can check it anymore; enforcing it would need a join. 3NF is guaranteed to avoid this, BCNF is not.

Why do insert anomalies signal a missing table rather than missing data?

If you can't record fact A without also inventing an unrelated fact B, it means A deserves its own table where it can live alone. TeacherSubject exists precisely so a teacher's subject can be stored before any student enrolls.

Why is "normalize to the highest form possible" bad engineering advice?

Each split trades write-safety for read-cost (more JOINs), and past 3NF you may sacrifice dependency preservation. Design is a trade-off, not a race to BCNF.

Why do candidate keys, not just the primary key, matter for BCNF?

BCNF checks every determinant against every superkey, and superkeys are built from all candidate keys. Ignoring an alternate key like {Student, Teacher} would make you misjudge which attributes are prime.

Edge cases

The corners the topic quietly assumes you'll handle.

A relation with only two columns R(A, B) and the single FD A → B — which normal forms hold?

All of them up to BCNF. A is the only candidate key and is a superkey, so A → B satisfies BCNF, and there are no composite keys or transitive chains to worry about.

A relation where every attribute is prime (all-key relation, e.g. R(A, B, C) with key {A,B,C} and no non-trivial FDs).

It is automatically in BCNF. The only non-trivial FDs would have a non-superkey determinant, but here every determinant that fixes the row is the whole key. No FD to violate anything.

A table currently holding zero rows — can it violate a normal form?

Yes. Normal forms are about the schema's declared dependencies, not the data present. An empty table with Teacher → Subject still violates BCNF by design.

A dependency

X \to A

where

A

is already part of

X

(e.g.

{A, B} \to A

This is trivial and ignored by all normal forms. No table is ever penalized for facts it already contains on the left side.

A relation that is in 3NF, is dependency-preserving, but is not in BCNF.

This is exactly the Teach(Student, Subject, Teacher) case. Its Teacher → Subject blocks BCNF, yet forcing BCNF would split the FDs so one can't be checked locally — so 3NF is the best we keep while preserving all FDs.

Two different candidate keys overlapping on a shared column — does that break normalization?

No. Overlapping candidate keys are fine; they just make more attributes prime. Teach has {Student,Subject} and {Student,Teacher} sharing Student, which is why both Subject and Teacher count as prime.

What if a determinant

X

is itself a full candidate key — does its FD ever violate BCNF?

Never. A candidate key is a superkey, so any FD X → A with X a key passes BCNF trivially. Violations always come from non-key determinants.

Recall One-line self-test before you leave

Cover the answers and say the reason aloud for each: Why is a single-column-key table always 2NF? Why does 3NF ⊄ BCNF? Why can BCNF lose an FD? Why is a single-column-key table always 2NF? ::: No proper subset of a one-column key exists, so no partial dependency can form. Why is 3NF not always BCNF? ::: 3NF permits a non-superkey determinant when the determined attribute is prime; BCNF forbids that exception. Why can BCNF decomposition lose an FD? ::: The columns of an FD can end up split across tables, so the dependency can no longer be checked without a join.