2. Given the following universal table ProjNo ProiName EmpNo EmpName JobClass P1

Question

2. Given the following universal table ProjNo ProiName EmpNo EmpName JobClass P15 P15 P15 P22 P22 HourlyRate Hours 97.50 110.00 Evergreen E101 John G. Evergreen E201 Anne K. Evergreen E307 Travis B. Clerical Support 35.75 Lone Star E201 Anne K. Lone Star E333 Josh H. Elec.Engineer DB Designer 35 DB Designer Clerical Support 35.75 31 3 4 24 110.00 and the following functional dependencies (ProjNo, EmpNo) >(ProjName, EmpName, JobClass, HourlyRate, Hours) ProjNo -> ProjName EmpNo > (EmpName, JobClass, HourlyRate) JobClass HourlyRate decompose/normalize the above table to obtain tables in the Boyce-Codd normal form (BCNF). 15 pts.] Note: You do not have to show the step-by-step normalization process; write only normalized tables. . As the answer, write a relational model description that consists of table names, attribute names, primary key designations, and foreign key references. For instance, faculty (fac no, name, dept) faculty.fac no primary key offering (offer_no, fac_no, semester, year) offering.offer no primary key offering.fac-no references faculty.fac_no.

Explanation / Answer

Here , In the given table , [ ProjNo , EmpNo. ] both are candidate key as well as super key , means only these two attributes are enough to find all other attribute values because of these two attribute's uniqueness. So Now, Here , For BCNF , You have to make 3 separate tables with such attributes :

Table1 ( ProjNo , EmpNo , EmpName , JobClass , Hours )

Table 2 ( ProjNo , ProjName )

Table 3 ( JobClass , HourlyRate )

Foreign Keys :

(i) ProjNo in Table1 referencing ProjNo in Table2.

(ii) JobClass in Table1 referencing JobClass in Table3.

Note : All Bolded with Underlined Attributes are primary keys for each table.

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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