Group Project Homework [78 pts] Note: This assignment can become very large, due to the screen shots. Use trimming and compression to keep it at a manageable size. Suppose that you have designed a database for Marcia’s Dry Cleaning that has the following tables: CUSTOMER (CustomerlD, FirstName, LastName, Phone, Email) ORDER (InvoiceNumber, CustomerlD, Dateln, DateOut, Subtotal, Tax, TotalAmount) ORDER_ITEM (InvoiceNumber, ItemNumber, Service, Quantity, UnitPrice, ExtendedPrice) SERVICE (Service, Description, UnitPrice) Add the prefix “HW7G_” to each table name.
Do parts A through M as given below. Those that involve SQL should be written in SQL*Plus and run, unless indicated otherwise. Include screen shots of the results of your runs. Note: This database has unnecessary duplicated data, which the owner wants to keep. Here is a crow’s foot diagram of the relationships that helps answer some parts: A. Specify NULL/NOT NULL constraints for each table column. Also specify alternate keys, if any. Use the following table form. (Note: This is a big table, since every column of every table appears as a row. ) Table Column NULUNOT NULL Alternate Key?
Customer CustomerlD NOT NULL No Phone Yes Email NULL FirstName LastName Order InvoiceNumber Date Subtotal Tax Total Order Item ItemNumber Quantity Service UnitPrice ExtendedPrice Description B. State relationships as implied by foreign keys and specify the maximum and minimum cardinality of each relationship. Justify your choices. Use the following table format: (Note: Your Justification should come after the table. ) Relationship Cardinality Parent Child Type Max Min Ddependent Strong 1 . A customer must exist to have one order 2. Customer doesn’t need to have an order 3.
Order Item needs a order but order does not need to exist in Order Item 4. Order can have many Order_ltem 5. Service does not need an order item but an order item needs a service 6. Service can have many order_item C. Explain how you will enforce the minimum cardinalities in your answer to part B. Use referential integrity actions for required parents, if any. Use Figure 6-28(b) as a boilerplate for required children, if any. Note that you do not have to write any code for this part, Just explain what must be done. HW7G ORDER. custornerlD must exist HW7G CUSTOMER. ustomerlD. HW7G ORDER ITEM. nvotcetqumber must exist HW7G ORDER. lnvotceNumber. HW7G ORDER ITEM. service must exist HW7G SERVICE. servtce. Each of the foreign key columns must be NOT NULL. D. Write CREATE TABLE statements for each of the tables, using your answers to parts A to C as necessary. Use FOREIGN KEY constraints to create appropriate referential integrity constraints. Set UPDATE and DELETE behavior in accordance with your referential integrity action design, remembering that Oracle does not allow IJ:C, so an Application or Trigger will be needed in that case i. e. we won’t have the referential ntegrity action in the CREATE TABLE statement. Write a constraint that SERVICE. UnitPrice be between 1. 50 and 10. 00. Also write a Sequence named HW7G_CustlD that starts at 95 and increments by 5 for later use in creating CustomerlD values (starting with 95 means that the first actual value assigned by the NextVal method will be 100). Note that tables must be created in a specific order. (We expect to see five screen shots here one for each table creation and one for the sequence creation. ) be equal to HW7G_SERVlCE. unitPnce, where = HW7G_SERVlCE. ervtce. Again, no code at this time, just n explanation. We would use a interrrelation constraint to enforce the data constraint. An interrrelation constraint can be used to set it so UnitPrice will equal each other. F. Insert all of the following data into the tables, using the CustlD Sequence where appropriate: The data used here is essentially the same data used in Chapter Two. Note that a Sales Tax Rate is needed for calculating the ORDER. Total. This data set (as given) uses 7. 90% (. 079). Note that dates are not in Oracle style, so you’ll have to change them when you enter them.
TABLE OF CUSTOMER DATA: Nikki Kaccaton 723-543-1233 NKaccaton@somewhere. com Brenda Catnazaro 723-543-2344 BCatnazaro@somewhere. com as specified Bruce LeCat 723-543-3455 BLeCat@somewhere. com by the Betsy Miller 725-654-321 1 BMiller@somewhere. com sequence hiller ’25-654-4322 Willer@somewhere. com Cathy vliller ’23-514-9877 (Miller@somewhere. com 3etsy 723-514-8766 BMiller@elsewhere. com TABLE OF SERVICES: UnitPrlce 10 Mens Shirt 1. 50 11 Dress Shirt 2. 50 15 Womens Shirt 16 Blouse 3. 50 17 Dress Blouse 4. 50 20 Mens Slacks 5. 00 25 Womens Slacks 6. 00 30 Skirt 31 Mens Suit 9. 0 45 Womens Suit 8. 50 0 Tuxedo 10. 00 Formal Gown TABLE OF ORDER DATA: [Note: A sales Tax Rate needed for calculating the ORDER. Total. This data uses 7. 90% (. 079). ] Dateln DateOut 2009001 10/4/2012 10/6/2012 158. 50 12. 52 171 . 02 2009002 105 25. 00 1. 98 26. 98 2009003 10/8/2012 49. 00 3. 87 2009004 115 17. 50 1 . 38 18. 88 2009005 125 10/7/2012 10/11/2012 12. 00 0. 95 12. 95 2009006 110 10/13/2012 152. 50 12. 05 164. 55 2009007 7. 00 0. 55 7. 55 2009008 130 10/12/2012 10/14/2012 140. 50 11. 10 151. 60 2009009 120 27. 00 2. 13 29. 13 TABLE OF ORDER ITEMS: 12. 50 20. 00 50. 00 60. 00 24. 00
