Using step-by-step process normalize the above relation to BCNF. Make sure you g

Question

Using step-by-step process normalize the above relation to BCNF.

Make sure you go through all normalization steps from 1NF to BCNF. Show your work.

You must indicate all anomalies lead to the violation of each normalization conditions.

List final set of relations that are normalized to BCNF.

Include schemas for each of the final set of relations. Indicate all primary, foreign, and surrogate keys in the final schemas.

PCode PTitle PManager PBudget PDept PDuration ENum EName ECity EZip EDeptNo EDeptName HRate 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, on which basis we can solve all the nomalization, so by analyzing complete table, you can see (Pcode, Enum) is our candidate key,

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

* So, here Pcode can be derived by Ptitle, so we can get one more candidate key as (Ptitle, Enum).

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

Pcode ---> Ptitle,Pmanager,Pbudget,Pdept

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

(Enum,Pcode) ---> Pduration,Hrate

(Enum,Hrate) ---> Pduration

Pcode ---> Pduration,Hrate

Enum ---> Pduration,Hrate

* One thing i want to clear that these above FD's are not all the fd's presented, but to fulfill our requirement to cover all the normal forms.

* Now, as you can see last fd i.e. Pcode ---> Pduration,Hrate and Enum ---> Pduration,Hrate ; have part of the key to non key derivation, which says there is highest normal form available is 1 NF.

* to remove this we can decompose this relation into 2 relation either seperate ( Pcode ,Pduration,Hrate ) or ( Enum, Pduration,Hrate ) as a new relation, which will have full dependency of pcode -- > pduration,hrate.

* but it will still have highest normal form as 2NF because it still have nonkey to nonkey derivation as hrate --> pduration.

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

* Now, again come to the previous relation from which we removed hrate and pduration; this relation still suffering from 3NF, because it has part of the key to part of the key derivation. i.e Pcode ---> 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 Pcode as a primary key.

* So by all these decompositions, all the relation will have BCNF as a highest normal form.

* Now comes to the key, as we can see ptitle can derive pcode so if we decompose any relation on the basis of ptitle then, this relation's primary key ptitle will be foreign key in (Pcode, Ptitle,Pmanager,Pbudget,Pdept) relation.   

* Surroagate key is known as candidate key, so there are many candidate key or surrogate key as (pcode,enum), and (ptitle,enum),etc.

* And come to primary key, we can select any candidate key as primary key, on that basis we sort out index of the table.

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.