2. The diagram below shows a relational schemaand its functional dependencies. L

Question

2. The diagram below shows a relational schemaand its functional dependencies. Label each dependency as a full, partial or transitive dependency. Convert the relation to a set of relations in second normal form. Then show the relations in 3rd normal form to illustrate the progression from a single relation to 2 normal form and then 3rd normal form. For the set of relations in 3 normal form, show the referential integrity constraints. Proiect Employee# 1 Project Name | Employee Job Class Hourly Charge Hours 3. Create the relational schema for the table below and draw and label the type of the functional dependencies in the relation. Convert the relation to a set of relations in second normal form. Then show the relations in 3rd normal form to illustrate the progression from a single relation to 2nd normal form and then 3d normal form. For the set of relations in 3rd normal form, show the referential integrity constraints. There is 1 full dependency, 1 transitive dependency, and one partial dependency. The full dependency is Invoice, Product# SaleRa Quantity Sold Quantity Sold Product Price 49.95 3.45 39.99 49.95 87.75 invoice# Product# 1 Sale date | Product Type | Vendor | Vendor Name Code 211347AAE3422 1/15/2012Rotary sander 211 211347OD30093 1/15/20124 in. drill bit 21:1 211347RU99574 1/15/2012 Band saw 211348AAE3422 1/15/2012Rotary sander 211 211349 GH77834 1/16/2012 Power drill Inc. 309 Inc 157 Inc.

Explanation / Answer

table1(ProjectNumber, Employee#, ProjectName, Employeename, JobClass, HourlyCharge, Hours)

FDs:

ProjectNumber, Employee# -> Hours (Full)

ProjectNumber -> ProjectName (Full)

Employee# -> EmployeeName, JobClass, HourlyCharge (Full)

JobClass -> HourlyCharge (Full)

Employee# -> JobClass -> HourlyCharge (Transitive)

2NF Form:

Table1(ProjectNumber, Employee#, Hours)

Table2(Employee#, EmployeeName, JobClass, HourlyCharges)

Table3(ProjectNumber, ProjectName)

3NF :

Table1(ProjectNumber, Employee#, Hours)

Employee(Employee#, EmployeeName, JobClass)

Project(ProjectNumber, ProjectName)

Table4(JobClass, HourlyCharges)  

One Employee can take one project at a time. A project can be handled by many employee at the same time.

Constraint Employee# references Table1 Employee#

Constraint ProjectNumber references Table1 ProjectNumber.

3.)

Table1(Invoice#, Product#, SaleDate, ProductType, VendorCode, VendorName, QuantitySold, ProductPrice)

FDs:

Invoice# , Product# -> Saledate, QuantitySold. (Full)

Product# , ProductType -> ProductPrice, VendorCode, VendorName. (Partial)

Product# -> VendorCode -> VendorName (Transitive)

2NF:

Table1(Invoice#, Product#, SalesDate, QuantitySold)

Product(Product#, ProductType, ProductPrice, VendorCode, VendorName)

3NF :

Table1(Invoice#, Product#, SalesDate, QuantitySold)

Product(Product#(FK), ProductType, ProductPrice,VendorCode)

Vendor(VendorCode, VendorName)

Constraint Product# refers Table1 Product#

Constraint VendorCode refers Product VendorCode

Related Questions

Navigate

• Browse (All)
• Browse 2
• Subjects

---

---

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