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.

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

Hello there,

* First of all, we come to candidate key or surrogate key, on which basis we can solve all the nomalization, so by analyzing complete table, you can see (code, Enum) is our surrogate key,

* but it is not the only surrogate key, because as we know if surrogate key or part of the surrogate key can be derived by any attribute then that attribute can replace the key or part of the key.

* So, here code can be derived by title, so we can get one more surrogate key as (title, Enum).

* Primary key can be any of the surrogate key or candidate key, so you can write any of the candidate key written above.

* Foreign key will be explained downside, when i will decompose this single relation into more than one relation.

* now, we will write functional dependencies, on which basis we will find out present normal form.

code ---> title,manager,budget,dept

Enum ---> Ename,Ecity,Ezip,Edeptno,Edeptname

(Enum,code) ---> duration,rate

(Enum,rate) ---> duration

code ---> duration,rate

Enum ---> duration,rate

* Now, as you can see last 2 fd's i.e.{ code ---> duration,rate} and {Enum ---> duration,rate} ; you can see part of the key is deriving non key attribute, so this represent current highest normal form present is one NF, so we have to convert it to BCNF. this is going to be our 4 step process i.e. (1-nf to 2-nf to 3-nf to BCNF).

* To remove this 1 NF dependency, we can decompose this relation into 2 relation either seperate ( code ,duration,rate ) or seperate ( Enum, duration,rate ) as a new relation, which will have full dependency of code -- > duration,rate OR Enum -- > duration,rate.

* But this relation will still have highest normal form as 2NF because it still have nonkey to nonkey derivation as rate --> duration in both the cases from above step.

* To remove, this 2NF, we have to decompose again this relation into (rate,duration) , which will have full dependency of rate---> duration, now this relation is also in the BCNF.

* So here i am listing one of the relation which is in BCNF i. e. (rate,duration)

* Now, again come to the previous relation from which we removed rate and duration; this relation still suffering from 3NF, because it has part of the key to part of the key derivation. i.e code ---> Enum.

* To remove this 3NF, we will seperate Enum,Ename,Ecity,Ezip,Edeptno,Edeptname as a new relation, so both the relation will have full dependency. one with Enum as a primary key, one with code as a primary key.

* So, again here i am listing 2 BCNF relation i.e. { Enum,Ename,Ecity,Ezip,Edeptno,Edeptname} and {code,title,manager,budget,dept }.

* Now comes to the foreign key, which i said i will explain downside. As we can see title can derive code so if we decompose this relation {code,duration,rate} on the basis of title, then {title,duration,rate} will be the new relation and this relation's primary key title will be foreign key in (code, title,manager,budget,dept) relation.   

* So there are total 3 BCNF relations are there i.e. (rate,duration),   { Enum,Ename,Ecity,Ezip,Edeptno,Edeptname} and {code,title,manager,budget,dept }.

Hope, you got all your answers, and feel free to ask any queries and to give feedback.

Thank you.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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