5.2Finding the highest normal form
The idea
To find the highest normal form, work in a fixed order: keys first, then test each FD against BCNF, then 3NF, then 2NF.
Think of it like this: A security check with three gates; stop at the first gate that fails.
Why it exists
This question appears nearly every year in the form "R is in which normal form".
How it works
- List all candidate keys and the prime attributes.
- If every FD has a super key on the left: BCNF.
- Else if every violating FD has only prime attributes on the right: 3NF.
- Else check for partial dependencies (non-prime depending on a part of a key): if none, 2NF; otherwise 1NF.
Worked example: Student(S, C, I, T) with SC → I, I → C, SI → T.
Keys: SC and SI. All attributes are prime, so 3NF. I → C violates BCNF because I is not a super key.
Now play with it
- →Run the checker on the Student example and follow the table.
- →Create an FD with a partial dependency on a key.
| FD | Left side a super key? | Right side prime? | Partial dependency? | Verdict |
|---|---|---|---|---|
| CS → I | yes | yes | no | fine for BCNF |
| I → C | no | yes | no | breaks BCNF, ok for 3NF |
| IS → T | yes | no | no | fine for BCNF |
BCNF: every non-trivial FD has a super key on the left. 3NF: or the right side is prime. 2NF: no non-prime attribute depends on part of a key.
| Step | Do this |
|---|---|
| 1 | List candidate keys by closure, mark prime attributes |
| 2 | For every FD ask: is the left side a super key? If all are, BCNF |
| 3 | Otherwise ask: is every right-side attribute prime? If so, 3NF |
| 4 | Otherwise a non-prime attribute depends on a non-key: if the left side is part of a key it is only 1NF, if not it is 2NF |
Check yourself
R(A, B, C), F = {A → B, B → C}. The key is A. Highest normal form?
Exam notes (the dense version)
Procedure: (1) find all candidate keys and the prime attributes. (2) For each FD test the left side: super key everywhere → BCNF. (3) If some left side is not a super key but every right-side attribute is prime → still 3NF. (4) If a non-prime attribute depends on a non-key: not 3NF; if that left side is only a part of a key it is also not 2NF, otherwise it is exactly 2NF.
Previous year questions: Normal Forms & Decomposition (11)
PYQs are grouped by topic, so this is the full set for Normal Forms & Decomposition; try the ones that match this subtopic. Open all 11 on the topic page.