Databases question a. Using these assumptions and stating any others you need to

Question

Databases question

a. Using these assumptions and stating any others you need to make, list all the non-trivial functional dependencies for this relation

b. What are the candidate keys for this relation? Identify the primary keys

c. Is this relation in third normal form? if not find a 3NF lossless join decomposition of College that preserves dependencies.

d.Is the relation or the resulting set of the relations in BCNF? if not find a lossless join decomposition that in BCNF. identify any functional dependencies that are not preserved.

Explanation / Answer

a) non-trivial functional dependencies :

stuId->lastName,homeAdd,status,dormRoom,roommateName,mealPlan)

dormRoom->dormAdd,dormCharge

homeAdd->homePhone

mealPlan->mealplanCharge

b) candidate key = stuid . primary key = stuid

c)this is not in 3NF, because transitive dependency is there between (dependency between nonprime attributes) the following three dependencies

dormRoom->dormAdd,dormCharge

homeAdd->homePhone

mealPlan->mealplanCharge

so, we can decompose it into the following set of table without any lossless decomposition,

A: (stuId,lastName,homeAdd,status,dormRoom,roommateName,mealPlan)

B: (dormRoom,dormAdd,dormCharge)

C: (homeAdd,homePhone)

D: (mealPlan,mealplanCharge)

d) these 4 tables are in BCNF. because stuId is a superkey in table A. dormRoom is a superkey in table B. homeAdd is a superkey in table C.and mealPlan  is a superkey in table D.

Related Questions

Navigate

• Browse (All)
• Browse D
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.