Using Straight-to-BCNF process normalize the above relation to BCNF. Make sure y

Question

Using Straight-to-BCNF process normalize the above relation to BCNF. Make sure you follow the four step process. Show your work.

List final set of relations that are normalized to BCNF.

Include schemas for each of the final set of relations.

Indicate all the primary, foreign, and surrogate keys in the final schemas.

USE Stright-to-BCNF.

Funcitonal dependencies

R(PCode,PTitle,PManager,PBudget,PDept,PDuraton,ENum,EName,ECity,EZip,EDeptNo,EDeptName,HRate)

PCode->(PTitle,PManger,PBudget,PDept,PDuration)

PTitle->(PCode,PManager,PBudget,PDept,PDuration)

ENum->(EName,ECity,EZip,EDeptNo,EDeptName)

EZip->ECity

EDeptNo->EDeptName

EDeptName->EDeptNo

HRate->(PDuration,ECity,EZip,EDeptNo,EDeptName)

(PCode,ENum)-> (PCode,PTitle,PManager,PBudget,PDept,PDuration,ENum,EName,ECity,EZip,EDeptNo,EDeptName,HRate)

Straight-to-BCNF Steps

1. Identify every functional dependencies.

2. Identify every candidate key

3. If there is a functional dependencies, that has a determinant that is not a candidate key,

a. More the columns of that functional dependencies into a new relation.

b. Make the determinant of that functional dependencies as the primary key of the new relation.

c. Leave a copy of the determinant as foreign key in the original relation.

d. Create a referential integrity constraints between the new relation and original relation.

Code Title Manager Budget Dept Duration Enum EName ECity EZip EDeptNo EDeptName Rate 1 pay Martin 100k Acc 12 E13 Smith Liberty 64068 D4 R&D 22 1 pay Martin 100k Acc 12 E30 Jones Liberty 64068 D3 IT 18.5 1 pay Martin 100k Acc 12 E31 Jouet Liberty 64069 D4 R&D 21 2 Budget Baker 780k FI 12 E11 Ali Liberty 64069 D4 R&D 21 2 Budget Baker 780k FI 12 E13 Smith Liberty 64068 D4 R&D 18 2 Budget Baker 780k FI 12 E9 Gilbert Buckner 64076 D5 Management 25.5 4 Sales1 Lewis 780k FI 12 E10 Ross Buckner 64076 D8 Sales 17 3 Hiring Lewis 100k HR 12 E9 Gilbert Buckner 64076 D5 Management 23.25 3 Hiring Lewis 100k HR 12 E31 Jouet Liberty 64069 D4 R&D 17.5 5 Sales2 Martin 200k HR 12 E27 Smith Buckner 64076 D9 Operation 16.5

Explanation / Answer

R(PCode,PTitle,PManager,PBudget,PDept,PDuraton,ENum,EName,ECity,EZip,EDeptNo,EDeptName,HRate)

PCode->(PTitle,PManger,PBudget,PDept,PDuration)

PTitle->(PCode,PManager,PBudget,PDept,PDuration)

ENum->(EName,ECity,EZip,EDeptNo,EDeptName)

EZip->ECity

EDeptNo->EDeptName

EDeptName->EDeptNo

HRate->(PDuration,ECity,EZip,EDeptNo,EDeptName)

(PCode,ENum)-> (PCode,PTitle,PManager,PBudget,PDept,PDuration,ENum,EName,ECity,EZip,EDeptNo,EDeptName,HRate)

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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