9/24/20111BCIS 4660Sample Solutions Homework #4pChapters 5 & 6 Pratt & AdamskiNAME_________________________BCIS 4660 Database Warehouse Concepts(20 Points)Homework Assignment #4 Score sheetAssignmentAppearance__ Name on Cover page__ Table of Contents__ Overall appearance+ ½ +½ + ½ SCORE/‐1Problem Number Functional Dependencies(1 point per question)+½ determinants+½ attributes/properties;3NF Tables(1 point per question)Or Relations+½ each;PP 5-1 /2Concepts of Database Management, 7th Edition, Pratt & AdamskiPP 5-2 /2PP 5-3 /2HB 5-2 /2HB 5-4 /2Chapter 5SUBTOTALMaximum /10DBDLs or Relation Lists (1 pt per question)ERDs;(1 pt each)PP 6-2 /2PP 6-4 /2HB 6-1 /2HB 6-2 /2HB 6-3 /2Chapter 6SUBTOTALMaximum /10MAX. TOTAL /20Exercise #4• Due: Sep 29 • Points: 20 points• Pratt & Adamski (End of Chapters 5 & 6)• Assignments must have Cover sheet/Table of contents and be stapled.Ch t 5 [ 180181 7thEd] A ti 1 2Concepts of Database Management, 7th Edition, Pratt & Adamski•Chapter 5 [pp 180‐181; 7thEd]: Answer questions 1, 2, 3 for Premiere Products and questions 2, 4 for Henry Books. • Chapter 6 pp 226‐7; 7thEd]: Answer questions 2, 4 for Premiere Products and questions 1, 2, 3 for Henry Books. – Turn in DBDL (Relations Lists) &– ERDs (Relationship Diagrams)Student RequirementsAs per the Grading Form above, students will need to produce the following type of OUTCOMES:1. Functional Dependency formulas or diagram2. Relation Lists3ERD/R l i hi Di3.ERD/Relationship DiagramsExamples of all 3 follow below.Generally st udents will use the Functional dependencies to construct GOOD relations. Then create an ERD for the database.Functional Dependency Diagram for Ordersw/ Sample Functional Dependency NotationFunctional Dependency Diagram:Good functional dependency on topConcepts of Database Management, 7th Edition, Pratt & AdamskiThis is the Functional Dependence Notation used in class (All final tablesMust be in at least 3rdNormal Form:Unnormalized 2NF Table ORDERSPrimary Key: (OrderNum, PartNum)F(OrderNum)  OrderDateF(PartNum)  DescriptionBad functional dependency on bottomSAMPLE Relation List: REP table; CUSTOMER tableNote: REP & CUSTOMER Relations:REP [RepNum, LastName, FirstName, Street, City, State, Zip, Commission, Rate]Concepts of Database Management, 7th Edition, Pratt & AdamskiCUSTOMER[CustNum, CustLastName, CustFirstName, …, RepNum]Primary Keys: Bold and UnderlinedForeign Keys: Italicized and Bold9/24/20112Another Exampleof Functional Dependenciesfor HOLT IndustriesConcepts of Database Management, 7th Edition, Pratt & AdamskiTentative List of Entities/RelationsHOLT INDUSTRIES EXAMPLE Concepts of Database Management, 7th Edition, Pratt & AdamskiExpand into relationsSee next page.SAMPLE List of Functional Dependenciesw/ RELATIONSCUSTOMER [Cust omerNumber, SoldToName, SoldToAddr1, SoldToAddr2, SoldToCity, SoldToState, SoldToZip, RepNum, RepLastName, RepFirstName]PART [ItemNum, ItemDesc, ItemPrice]INVOICE [InvoiceNo, InvDate, OrderNum, ShipDate, Frieght, IT l]Concepts of Database Management, 7th Edition, Pratt & AdamskiInvTotal]Order [OrderNo, OrderDate, PONum, CustNoShipToNum,ShipToName, ShipToAddr1, SoldToAddr2, ShipToCity, SoldToState, ShipToZip ]OrderLine [OrderNum, ItemNum, QuantityOrdered,QuantityShipped, QuantityBO, ItemPrice]Complete ERD Diagram;Students should use ACCESS Relationship Diagram ToolNOT necessary to show PropertyOvals on Relationship Diagram.Also, an Intersection table will beUsed instead of the Relationship Box shown for OrderLine.Concepts of Database Management, 7th Edition, Pratt &