Question

Exercise 1: Consider the table below, find the functional dependencies and make the design BCNIF DormCost $3,500.00 DormName The Willows The Pines The Palms StudentNum 100 200 300 400 500 600 700 StudentName Smith Johnson Abernathy Smith Wilcox Khan Simon $3,800.00 | $4,000.00 | $3,800.00 $3,500.00 $4,000.00 $3,500.00 | The Pines The Willows The Palms The Willows

Answer 1

Lets have short variable names for all the attributes, so that understanding solution is easy:

A = StudentNum

B = Student name

C = DormName

D = DormCost

Functional Dependcies present are:

A->B

A->C

A->D

as A is obviously primary key

C->D as cost is uniquely determined by dorm

To normalize using BCNF:

Step 1: Find merged minimal cover of functional dependencies:

A-> BC

C->D

Checking for BCNF, we find this is not in BCNF as C is not a superkey, so we break it into 2 relations

(A,B,C) with functional dependcies

A->B

A->C

and (C,D) with functional dependcies

C->D

We determined that both the relations are in BCNF.

Hence, the BCNFnormalizations leads to 2 relations

(StudentNum,StudentName,DormName) and (DormName, DormCost)