Database Reference
In-Depth Information
Figure 5-16.
Revised Initial 1NF Relation for the Mountaineering Problem
Step 2 — Obtain 2NF Relations
The second step is to obtain a set of 2NFrelations. Because of this FD2, the relation can be
non-loss decomposed via Heath's theorem to obtain:
Relation
M1
:
{CID#, CNAME, CADDR} PK [CID#]
Relation
M2
:
{BDA.TE, CID#, EDATE, MTN#, MNAME,
MHGHT, CTRYCD, CTRYNM, DIST} PK [CID#, BDATE]
Step 3 — Obtain 3NF Relations
Next, we seek to obtain 3NF relations. Based on FD3 and FD4, relation
M2
is not in 3NF.
Again applying Heath's theorem for non-loss decomposition, we obtain the following
relations:
Relation
M3
: {CTRYCD, CTRYNM} PK [CTRYCD]
Relation
M4
: {MTN#, MNAME, MHGHT, CTRYCD, DIST} PK
[MTN#]
Relation
M5
: {BDATE, CID#, MTN#, EDATE} PK
[CID#, BDATE]
Step 4 — Obtain BCNF (and higher order) Relations
Next, we seek to obtain relations of higher order normal forms. Observe that relations
M1
,
M3
,
M4
, and
M5
are in BCNF, 4NF and 5NF.
