1. Consider the following scenario involving Big Z Inc., an automotive products wholesaler.

   Big Z Inc. wants to create an analytical database (data warehouse) to analyze its order quantities. The two available data sources, Source 1 and Source 2 are described below.

   • Source 1 The Big Z Inc. Orders Database, shown in Figure 8.43.
   • Source 2 The Big Z Inc. Human Resources Department Table, shown in Figure 8.44.

   The data warehouse has to enable an analysis of order quantities by:

   • • date, including:
     • • full date
     • • day of week
     • • day of the month
     • • month
     • • quarter
     • • year
   • • time
   • • product, including:
     • • Product ID
     • • product name
     • • product type
     • • product supplier name

Figure 8.43

Figure 8.44 Source 2: The Big Z Inc. Human Resources Department Table

1. • customer, including:
   • • customer ID
   • • customer name
   • • customer type
   • • customer zip
2. • depot, including:
   • • Depot ID
   • • depot size
   • • depot zip
3. • order clerk, including:
   • • order clerk ID
   • • order clerk name
   • • order clerk title
   • • order clerk education level
   • • order clerk year of hire

1. Based on the sources and requirements listed above, create an ER model and a mapped relational model for the normalized data warehouse for Big Z Inc.
2. Using data from Figures 8.43 and 8.44 illustrate how the tables in the relational model created in E8.3c would be populated.
    
    

DepotZip DepotSize OCName 01 DZ 03 ORDER 8888888 DEPOT 02 03 DEPOT DepotID DepotSize Depotzip Small 60611 Large 60660 Large 60611 04 ORDERCLERK H 06 Depott ORDERCLERK CUSTOMER OCIO OCIO OCName OCI Antonio C1 OC2 Wesley C2 OC3 Lilly C3 000000 DEPOT CL DepotID DepotSize DepotZip C2 ORDERCLERK OCID OCName 282322 Ships Fros DI DZ D2 D3 D3 Processes CustomerID CustomerName Auto Doc Bo's Car Repair 11 Auto Parts ProductID P1 P2 P3 OrderID Customer10 DepotID OCID OrderDate DI ProductName PRODUCT ProductID Quantity OrderID PRODUCT Includes Product Type 2 ORDER Order Date ProductID ProductName PRODUCT Product Type Supplier1D (FK) ORDEREDVIA ProductID (FK) OrderID (FK) Quantity ORDER OrderID Order Date Order Time CustomerID (K) DepotID (FK) OCID (FK) OrderTime Order Time 0C1 1-Jan-2020 9:00:00 AM OCZ 2-Jan-2020 9:00:00 AM OC3 2-Jan-2020 9:30:00 AM act 3-Jan-2020 9:00:00 AM OC2 3-Jan-2020 9:15:00 AM OC3 3-Jan-2020 9:30:00 AM Submits ST ST BE 60137 60140 60605 Supplies Product Name ProductType SupplierID BigGripper Traction Viz Tire Tire SureStart Battery CustomerType CustomerZip Repair Shop Repair Shop Retailer SUPPLIER SupplierID SupplierName CUSTOMER Customer1b CustomerName CustomerType CustomerZip PL P2 PL P2 P3 P3 P2 Jikf CustomerZip PL CustomerID CustomerName 000 ORDEREDVIA ProductID Supplier10 ST BE SUPPLIER SupplierName CUSTOMER SUPPLIER Supplier10 SupplierName Super Tires Batteries Etc (CustomerType OrderID Quantity 01 01 02 03 04 05 06 06 4 8 12 4 7 no 5 B 4