Database Modeling and Design - Database Systems - page 108

We shall examine each explicit FD in turn and determine all the attributes that it
determines (explicitly or implicitly), bearing in mind that any attribute or combination
of attributes that is a determinant, necessarily determines itself. Hence, we conclude the
following:

• HR →CHR → CTHR

• HT→ HTR → HTRC

• CS → CSG → CSGT

• HS → HSR→ CHSR → CHSRG → CHSRGT

• C → CT

From this exercise, observe that [H,S] is the only candidate key; it is therefore the
primary key (PK).

Step 2 — Obtain 2NF Relations

The next step is to obtain 2NF relations. We may rewrite the initial relation as follows:
StudPerfDraft {H, S, C, T, R, G} with PK [H,S]

Observe that StudPerfDraft is in 2NF.

Step 3 — Obtain 3NF Relations

Step 3 is to obtain 3NF relations. StudPerfDraft is not in 3NF due to FD5 (C →T). To
resolve this, decompose via Heath's Theorem to obtain:
R1 {H, S, C, R, G} with PK [H,S] and R2 {C ,T} with PK [C].

Step 4 — Obtain BCNF Relations

Our fourth step is to obtain a set of BCNF relations. Observe that R2 is in BCNF but R1 is
not, due to FD3 ([C,S] → G). To resolve this, decompose via Heath's theorem to obtain:
R3 {C, S,G} with PK [C,S] and R4 {H, S, C, R} with PK [H,S].
R3 is now in BCNF but R4 is not, due to FD1 ([H,R] → C). Decompose via Heath's
theorem to obtain:
R5 {H, R, C} with PK [H,R] and R6 {H, S, R} with PK [H,S].
We now have R2 , R3 , R5 , and R6 all in BCNF. Additionally, note that all the FDs
have been resolved, except for FD2 ([H,T] → R). This may be resolved by introducing the
relation R7 as follows:
R7 {H, T, R} with PK [H, T].