Please answer questions
PROBLEM-SOLVING CASES 1. The manager for the Clearwater Traders wants to collect the following data for each order placed by a customer: customer's name and address, item(s) ordered, each item. quantity of each item, item's size or color-if applicable-and the retail price of a. Create an entity-relationship model representing the data that the manager wants to store in the database, based on the following assumptions: o Each customer can place multiple orders. 0 Each order can only belong to a single customer. o Different items can be ordered on the same order. Each item on an order can have a different size and/or color, and some items may not have a size or color. b. Based on the data elements the Clearwater Traders' manager wants you to include in the database, assign an appropriate field name to each of the data elements. Make certain the name of each field is descriptive so it will be easily recognizable in the final version of the database. c. Take the named data elements from the previous step and convert the data to first normal form.40 Chapter 1 Client/Server Databases and the Oracle 10g Relational Database d. Convert the first normal form of the data elements to second normal form. Use the shorthand method to specify which elements belong to each entity and underline the primary key for each element. e. Convert each of the previously identified entities to third normal form. Make certain that the necessary foreign keys have been added to the final tables to support the relationships shown in your initial ER model. 2. Identify the tables and fields that must be referenced in the Clearwater Traders database to answer the following questions: a. How many 3-Season Tents are left in stock? b. Which customer(s) lives in Georgia? c. How many customers placed an order in May 2006? d. Who was the last customer to order a Men's Expedition Parka? e. Which items of women's clothing were sold between May 29 and June 1? 3. Using the Clearwater Traders data set presented in Figure 1-24, determine the answer for each of the five questions asked in Problem 2. 4. Based on the Clearwater Traders data set presented in Figure 1-24, draft an ER model representing the relationships in the database. 5. Using the ER model created in Problem 4, create a list of each table using the shorthand method shown in Figure 1-19. Underline the primary key of each table once and underline any foreign keys the table may contain twice.CUSTOMER C_ID C_LAST C_FIRST C_MI C_DOB C_ADDRESS C_ CITY C_ STATE C_ ZIP Number String String String Date/Time String String String String Graham Neal 12/10/1967 9815 Circle Dr. Tallahassee FL 32308 Sanchez Myra 8/14/1958 172 Alto Park Seattle WA 42180 Smith Lisa M 4/12/1960 850 East Main Santa Ana CA 51875 Phelp Paul 1/18/1981 994 Kirkman Rd. Northpoint NY 11795 Lewis Sheila 8/30/1978 195 College Bivd. Newton GA 37812 James Thomas E 6/01/1973 348 Rice Lane Radcliff WY 87195 CUSTOMER (continued) C_DPHONE C_EPHONE C_ USERID C PASSWORD String String String String 9045551897 9045558599 grahamn barbiecar 4185551791 4185556643 sanchezmt qwert5 3075557841 3075559852 smithlm joshua5 4825554788 4825558219 phelpp hold98er 3525554972 3525551811 lewissa 125pass 7615553485 7615353319 jamest nok$tell ORDER_SOURCE OS ID OS DESC Number String Winter 2005 2 Spring 2006 Summer 2006 Outdoor 2006 Children's 2006 6 Web SiteORDERS O_ID O_DATE O_METHPMT C_ID OS_ID Number Date/Time String Number Number 5/29/2006 CC 2 W N 5/29/2006 CC 5/31/2006 CHECK 5/31/2006 CC 5 6/01/2006 CC on 6/01/2006 CC W O W N O CATEGORY CAT_ID CAT_DESC Number String 1 Women's Clothing 2 Children's Clothing Men's Clothing Outdoor Gear ITEM ITEM_ID ITEM_DESC CAT ID ITEM IMAGE Number String Number String Men's Expedition Parka 3 parkajpg A W N 3-Season Tent 4 tents.jpg Women's Hiking Shorts shorts.jpg Women's Fleece Pullover fleece.jpg Children's Beachcomber Sandals 2 sandals.jpg Boy's Surf Shorts NN surfshorts.jpg Girl's Soccer Tee girlstee.jpg Figure 1-24 Clearwater Traders databaseORDER_LINE O_JD INV_ID OL QUANTITY Number Number Number - 14 19 W - - - N - - - N - SHIPMENT SHIP_ID SHIP DATE EXPECTED Number Date/Time 1 IN 09/15/2006 11/15/2006 4 06/25/2006 06/25/2006 08/15/2006 INVENTORY INV_ID ITEM ID COLOR INV_SIZE INV_PRICE Number Number INV_QOH A A S A A W W W W W W N N String String Sky Blue Number Number Light Grey 259.99 259.99 16 Khaki S 29.95 12 Khaki M 29.95 150 Khaki L 29.95 147 0 Navy S Navy 29.95 M 139 29.95 Navy L 137 29.95 Eggplant 115 S 59.95 Eggplant M 135 59.95 Eggplant L 168 59.95 Royal S 187 Royal M 59.95 0 59.95 124INVENTORY INV_ID ITEM ID COLOR INV_SIZE INV_PRICE INV_QOH Number Number String String Number Number - - - C UM UM UM UT U U U A A A A A A W W W W W W N N Sky Blue 259.99 Light Grey 259.99 16 12 Khaki Khaki S 29.95 M 29.95 150 Khaki L 29.95 147 0 Navy 29.95 139 Navy M 29.95 137 Navy L 29.95 115 Eggplant S 59.95 Eggplant M 135 59.95 168 Eggplant L 59.95 187 Royal S 0 Royal M 59.95 59.95 124 Royal L 59.95 112 Turquoise 10 15.99 121 Turquoise 11 15.99 111 Turquoise 12 15.99 113 Turquoise 1 15.99 121 Bright Pink 10 15.99 148 Bright Pink 11 15.99 137 Bright Pink 12 15.99 134 Bright Pink 1 15.99 123 Spruce S 199.95 114 Spruce M 199.95 17 Spruce L 209.95 0 26 Spruce XL 209.95 12 Blue S 15.95 50 28 Blue M 15.95 100 29 Blue L 15.95 100 30 White S 19.99 100 31 White M 19.99 100 32 White L 19.99 100 Figure 1-24 Clearwater Traders database (continued)SHIPMENT_LINE SHIP_ID INV_ID SL_QUANTITY SL_DATE_RECEIVED Number Number Number Date/Time VQUINN - 25 09/10/2006 25 09/10/2006 W N 25 UI UI UI A A W W 200 200 200 12 100 08/15/2006 13 100 08/25/2006 23 50 08/15/2006 24 100 08/15/2006 25 100 08/15/2006 COLOR COLOR String Sky Blue Light Grey Khaki Navy Royal Eggplant Blue24 100 08/15/2006 25 100 08/15/2006 COLOR COLOR String Sky Blue Light Grey Khaki Navy Royal Eggplant Blue Red Spruce Turquoise Bright Pink White Figure 1-24 Clearwater Traders database (con
