Answers to Premiere Products Exercises

Chapter 5 Partial Solutions
Answers to Premiere Products Exercises

PP 5-1

1. PartNum ( Description, OnHand, Class, Warehouse, Price OrderNum ( OrderDate, CustomerNum CustomerNum ( CustomerName, RepNum RepNum ( FirstName, LastName PartNum, OrderNum ( NumOrdered, QuotedPrice

Part (PartNum, Description, OnHand, Class, Warehouse, Price) Orders (OrderNum, OrderDate, CustomerNum) Customer (CustomerNum, CustomerName, RepNum) Rep (RepNum, FirstName, LastName) PartOrders (PartNum, OrderNum, NumOrdered, QuotedPrice)

PP 5-2

2. InvoiceNum ( CustomerNum, LastName, FirstName, Street, City, State, Zip, Date PartNum ( Description, Price CustomerNum ( LastName, FirstName, Street, City, State, Zip InvoiceNum, PartNum ( NumShipped

Invoice (InvoiceNum, CustomerNum, Date) Part (PartNum, Description, Price)

Customer (CustomerNum, LastName, FirstName, Street, City, State, Zip) InvoicePart (InvoiceNum, PartNum, NumShipped)

PP 5-3

3. Part (PartNum, Description, Class, Price) Warehouse (WarehouseNum, WarehouseName) Supplier (SupplierNum, SupplierName) PartWarehouse (PartNum, WarehouseNum, OnHand) PartSupplier (PartNum, SupplierNum, LeadTime)

Answers to Henry Books Case

HB 5-2

Dependencies: CourseNum (( Textbook CourseNum (( InstructorNum InstructorNum ( InstructorName
Original Relation: Course (CourseNum, Textbook, InstructorNum, InstructorName)
For our course assignment, we will ignore the multidependencies and focus on bringing the design to 3NF. The design is already in 1NF, but not in 2NF, due to the partial dependency InstructorNum → InstructorName. Split the table into two: Course (CourseNum, Textbook, InstructorNum) Instructor (InstructorNum, InstructorName)
The design is now in 2NF and also in 3NF. For the course assignment, you may stop here. If you wish to bring the design to 4NF, split the relation with the multidependencies: