Applied Class 9: Functional Dependencies

exercises
tutorial
databases
functional-dependencies
keys
normalisation

Practice for 2026-04-27-database-design-guidelines-and-functional-dependencies.

Section A — Anomalies and functional dependencies

Question A.1

Based on the following data, provide an example and explanation of an insertion, deletion and modification anomaly.

A  B  C  D  E
2  2  1  5  6
2  3  1  5  4
3  4  5  3  2
3  5  5  1  3

Functional dependencies: {A} → {C}, {B} → {D, E}.

  • Insertion anomaly: insert anything into B, D and E (with B, D, E values that don’t already exist in the data) without also inserting a value into A — the row is incomplete but there’s no A to attach it to.
  • Deletion anomaly: deleting the tuple with B = 4 loses the information for the FD B → {D, E}, specifically 4 → {3, 2} — no other row records that mapping.
  • Modification anomaly: updating {2, 2, 1, 5, 6} to {2, 4, 1, 5, 6} creates an inconsistency in B → {D, E} — it now implies both 4 → {3, 2} (from the existing row with B=4) and 4 → {5, 6} (from the just-modified row).

Question A.2

Based on A → B, B → C, {C, D} → E, fill in the blanks (?) below so that no FD is violated.

A  B  C  D  E
1  2  1  6  2
1  ?  1  4  ?
2  4  2  7  4
3  ?  ?  4  ?
A  B  C  D  E
1  2  1  6  2
1  2  1  4  3
2  4  2  7  4
3  2  1  4  3

Row 2’s B is forced: A = 1 also appears in row 1 (B = 2), and A → B requires matching A values to share the same B. Row 2’s E is then constrained by {C, D} → E: row 2 has {C=1, D=4}, a combination that will recur in row 4 — both rows sharing that {C, D} pair must agree on E (here, 3).

Row 4’s B and C are not strictly forced by the given FDs (A = 3 doesn’t recur elsewhere, so A → B places no constraint on it) — but choosing B = 2, C = 1 is a valid, self-consistent choice: it matches row 1/2’s B → C mapping (B=2 → C=1), and then {C=1, D=4} → E correctly forces row 4’s E to match row 2’s (E = 3), since both rows now share {C=1, D=4}.

Question A.3

Based on the following data, identify which options are potential functional dependencies.

A  B  C  D  E
1  X  1  M  1
2  Y  1  M  1
3  Y  4  N  3
4  W  2  L  5
5  W  2  M  1
6  T  5  O  2
  • A → B — B → A — A → C — B → C — C → D — C → E — D → E
  • {A, B} → C — {B, C} → E — {B, C, D} → E

Potential FDs: A → B, A → C (both trivially hold — every row has a distinct A, so there’s never a repeated-A pair to violate anything). D → E (every repeated D value agrees on E: D=M in rows 1, 2, 5 always has E=1). {A, B} → C (trivially holds, since A alone is already unique per row). {B, C, D} → E (every (B,C,D) triple in the table is distinct, so it trivially holds too).

Not potential (contradicted by the data):

  • B → A: B=Y appears in rows 2 and 3 with different A (2 vs 3).
  • B → C: B=Y appears in rows 2 and 3 with different C (1 vs 4).
  • C → D: C=2 appears in rows 4 and 5 with different D (L vs M).
  • C → E: C=2 appears in rows 4 and 5 with different E (5 vs 1).
  • {B, C} → E: {B=W, C=2} appears in rows 4 and 5 with different E (5 vs 1).

Section B — Closures

Question B.1

Given R [A, B, C, D, E, F, G] with {A} → {D}, {B, C} → {A}, {C} → {F}, {F} → {E} — find the following closures.

  • {C}⁺ = {C, F, E} (via C→F, then F→E).
  • {B, C, A}⁺ = {A, B, C, D, E, F} (via A→D, C→F, F→E) — this is a superkey.
  • {B, C}⁺ = {A, B, C, D, E, F} (via {B,C}→A, then A→D, C→F, F→E) — this is a composite (candidate) key: {B, C, A} is a superkey but not minimal (since {B, C} alone already suffices), and {B, C} is the minimal set achieving the same closure.

Question B.2

Given R [A, B, C, D, E, F] with {A} → {B, C}, {C, D} → {E}, {A, C} → {E}, {B} → {D}, {E} → {A, B} — find the following closures.

  • {A, F}⁺ = {A, B, C, D, E, F} (via A→{B,C}, B→D, {C,D}→E or {A,C}→E, E→{A,B}).
  • {C, D, F}⁺ = {A, B, C, D, E, F} (via {C,D}→E, E→{A,B}).
  • Both {A, F} and {C, D, F} are minimal — removing any single attribute from either set makes the reduced set no longer a superkey.

Section C — Candidate keys

Question C.1

R [A, B, C, D, E, F] with {A, E} → {D}, {B, C} → {A}, {B} → {F}, {F} → {E}. Find all candidate keys.

Candidate key(s): {B, C}.

{B, C}⁺: B→F adds F; F→E adds E; {B,C}→A adds A; {A,E}→D adds D — covers everything, and no proper subset of {B,C} does (neither B nor C alone determines the other).

Question C.2

R [A, B, C, D, E, F] with {A} → {B, C}, {C, D} → {E}, {A, C} → {E}, {B} → {D}, {E} → {A, B}. Find all candidate keys.

Candidate keys: {B, C, F}, {C, D, F}, {A, F}, {E, F}.

F never appears on any FD’s right-hand side, so it must be part of every key. Among {A, B, C, D, E}, the FDs form a tightly-coupled cluster (A, B/D, C/E combinations all inter-derive each other) — A, E, {B,C}, and {C,D} are each independently sufficient to derive the rest of that cluster, giving four minimal keys once F is added to each.

Question C.3

R [A, B, C, D] with {A, B} → {C, D}, {C} → {A, B, D}, {D} → {C}. Find all candidate keys.

Candidate keys: {C}, {A, B}, {D}.

{C}⁺ = {A,B,C,D} directly. {D}⁺: D→C, then C→{A,B,D} — covers everything. {A,B}⁺ = {A,B,C,D} directly. All three are minimal.

Question C.4

R [A, B, C, D, E, F, G, H, I, J] with {A, B} → {C}, {A} → {D, E}, {B} → {F}, {F} → {G, H}, {D} → {I, J}. Find all candidate keys.

Candidate key: {A, B}.

{A, B}⁺: A→{D,E} adds D,E; B→F adds F; {A,B}→C adds C; F→{G,H} adds G,H; D→{I,J} adds I,J — every attribute, and neither A nor B alone reaches the other’s attributes.

Section D — Highest normal form

Question D.1

R [A, B, C, D, E, F] with {A, E} → {D}, {B, C} → {A}, {B} → {F}, {F} → {E}. Identify the highest normal form and justify.

Only candidate key (CK) is {B, C} (from Question C.1). Highest normal form is 1NF, because of the partial dependency {B} → {F} — B is a proper subset of the candidate key {B, C}, and F is a non-prime attribute, violating 2NF.

Question D.2

R [A, B, C, D, E] with {A} → {B, C, D, E}, {B} → {A, C, D, E}. Identify the highest normal form and justify.

CKs are {A} and {B}. Highest normal form is BCNF, because the LHS of every FD (A and B) is itself a superkey.

Question D.3

R [A, B, C, D, E, F, G] with {A} → {B, C, D}, {D} → {A}, {C} → {F, G}. Identify the highest normal form and justify.

CKs are {A, E} and {D, E}. Highest normal form is 1NF. Decomposing {A} → {B, C, D} into {A}→{B}, {A}→{C}, {A}→{D}: {A} → {B} and {A} → {C} are both partial dependencies, since A is a proper subset of the candidate key {A, E} and B/C are non-prime attributes. ({D} → {A} is not a partial dependency, since A is a prime attribute — appearing in candidate key {A, E}.)

Question D.4

R [A, B, C, D, E] with {A, B} → {C, E}, {D} → {A}, {A} → {D}. Identify the highest normal form and justify.

CKs are {A, B} and {D, B}. Highest normal form is 3NF — there are no partial or transitive dependencies. It’s not BCNF, though: the LHS of {A} → {D} (just A) is not a superkey, violating BCNF — but this is not a partial dependency, since D is a prime attribute (appears in candidate key {D, B}). The same reasoning applies symmetrically to {D} → {A}.