Applied Class 10: Normalisation, BCNF Decomposition and 3NF Synthesis
Practice for 2026-05-04-normalisation-and-relational-database-schema-design. Note: “consider the FDs in the order provided” is a default guideline — only FDs that actually violate BCNF get split on; other correct decompositions can exist depending on split order.
Section A — BCNF decomposition (top-down)
Question A.1
R [A, B, C, D, E] with {A} → {C}, {A, B} → {D, E}. Decompose into BCNF.
{A,B}⁺ = {A,B,C,D,E} — {A, B} is the only candidate key.
{A} → {C} violates BCNF (A isn’t a superkey) → split into R1 [A, C] and R2 [A, B, D, E]. Within R2, {A,B} is a key (it already was for the whole relation), so {A,B} → {D,E} doesn’t violate BCNF there — no further splitting needed.
Final answer: R1 [A, C] ({A}→{C}), R2 [A, B, D, E] ({A,B}→{D,E}).
Question A.2
R [A, B, C, D, E] with {A} → {B}, {C} → {D, E}. Decompose into BCNF.
{A,C}⁺ = {A,B,C,D,E} — {A, C} is the only candidate key.
{A} → {B} violates BCNF (A alone isn’t a superkey) → split into R1 [A, B] and R2 [A, C, D, E]. In R2, {C} → {D, E} violates BCNF (C alone isn’t a superkey for R2) → split into R3 [C, D, E] and R4 [A, C].
Final answer: R1 [A, B], R3 [C, D, E], R4 [A, C] (no non-trivial FD).
Question A.3
R [A, B, C, D, E, F] with {A} → {B, C}, {C} → {D, E}, {E} → {F}. Decompose into BCNF.
{A}⁺ = {A,B,C,D,E,F} — A is the only candidate key. A → {B, C} does not violate BCNF here, since A is the key — so we skip it and look for an FD whose LHS genuinely isn’t a superkey.
{C} → {D, E} violates BCNF (C isn’t a superkey) → split into R1 [C, D, E] and R2 [A, B, C, F]. Within R2, the explicit FDs don’t obviously violate BCNF — but the implicit FD C → F (derivable transitively via C → E and E → F) does (C isn’t a superkey for R2 either) → split R2 into R3 [C, F] and R4 [A, B, C].
Final answer: R1 [C, D, E] ({C}→{D,E}), R3 [C, F] ({C}→{F}, implicit), R4 [A, B, C] ({A}→{B,C}).
Note: {E} → {F} is lost by this decomposition — E and F never end up together in any final relation, so that FD can no longer be checked directly against the decomposed schema.
Question A.4
R [A, B, C, D, E] with {A} → {B}, {C} → {D, E}, {A, D, E} → {C}, {B, C} → {A}. Decompose into BCNF.
{A, C}, {A, D, E} and {B, C} are all candidate keys.
{A} → {B} violates BCNF → split into R1 [A, B] and R2 [A, C, D, E]. Within R2, {C} → {D, E} violates BCNF → split into R3 [C, D, E] and R4 [A, C].
Final answer: R1 [A, B], R3 [C, D, E], R4 [A, C] — the exact same result as Question A.2, even though A.4 started with twice as many FDs. {A, D, E} → {C} and {B, C} → {A} are both lost in this decomposition (their attributes never all end up together in one final relation) — this is worth noticing: extra FDs don’t necessarily survive decomposition, and a differently-ordered decomposition might have preserved different (but not necessarily more) FDs.
Section B — 3NF synthesis (bottom-up)
Question B.1
R [A, B, C, D, E, F, G, H] with {A, B} → {C}, {C, D} → {E}, {D} → {F, G}. Synthesise into 3NF.
{A,B,D,H}⁺ = {A,B,C,D,E,F,G,H} — the only candidate key. Prime attributes: A, B, D, H.
This FD set is already a minimal cover (RHS already single-attribute per FD after splitting {D}→{F,G} into {D}→F and {D}→G; no LHS or whole-FD redundancy).
Synthesis: R1 [A, B, C] ({A,B}→C), R2 [C, D, E] ({C,D}→E), R3 [D, F, G] ({D}→{F,G}), and — since the candidate key {A,B,D,H} isn’t contained in any of these — R4 [A, B, D, H] (no non-trivial FD). All four are in 3NF.
Question B.2
R [A, B, C, D, E, F] with {A, B} → {C}, {C} → {D, E}. Synthesise into 3NF.
{A,B,F}⁺ = {A,B,C,D,E,F} — the only candidate key. Prime attributes: A, B, F. Already a minimal cover.
Synthesis: R1 [A, B, C] ({A,B}→C), R2 [C, D, E] ({C}→{D,E}), R3 [A, B, F] (no non-trivial FD, added since the key isn’t otherwise present). All three are in 3NF.
Question B.3
R [A, B, C, D, E] with {A} → {D, E}, {D} → {A}, {B} → {C}, {B, C} → {A, D}, {E, A} → {D}. Synthesise into 3NF.
{B}⁺ = {A,B,C,D,E} — {B} is the only candidate key. Prime attributes: B.
Minimal cover: RHS-splitting and removing redundant LHS attributes (e.g. {E,A}→{D} simplifies to {A}→{D}, since E is redundant once A is present — A alone already reaches D) and redundant whole FDs ({B}→{D} becomes derivable via {B}→{A}→{D} so it’s dropped; the duplicate simplified {A}→{D} collapses into one) leaves: {A} → {D, E}, {B} → {A, C}, {D} → {A}.
Synthesis: R1 [A, D, E] ({A}→{D,E}, {D}→{A}), R2 [A, B, C] ({B}→{A,C}) — the candidate key {B} is already present in R2, so no extra all-prime-attributes relation is needed. Both are in 3NF.
Question B.4
R [A, B, C, D, E, F] with {A} → {B, C, D, E, F}, {B, C} → {A}, {D, E} → {B}, {C} → {D}. Synthesise into 3NF.
{A}, {B, C} and {C, E} are all candidate keys. Prime attributes: A, B, C, E.
Minimal cover: RHS-splitting {A} → {B,C,D,E,F} and simplifying — {A}→{B} is redundant (derivable via {A}→{D}, {A}→{E}, {D,E}→{B}); {A}→{D} is redundant (derivable via {A}→{C}, {C}→{D}) — leaves: {A} → {C, E, F}, {B, C} → {A}, {D, E} → {B}, {C} → {D}.
Synthesis: R1 [A, C, E, F] ({A}→{C,E,F}), R2 [B, C, A] ({B,C}→{A}), R3 [D, E, B] ({D,E}→{B}), R4 [C, D] ({C}→{D}). The candidate key {A} is already present in R1, so no extra relation is needed. All four are in 3NF.