[Solved]Consider Following Scenario Involving Big Z Inc Automotive Products Wholesaler Big Z Inc W Q37268439

Consider the following scenario involving Big Z Inc., anautomotive products wholesaler.

Big Z Inc. wants to create the analytical database (datawarehouse) to analyze its order quantities.

The two available data sources, Source 1 and Source 2, aredescribed below.

Source 1 The Big Z Inc. Human Resources Department Table, shownin Figure 8.47.

Source 2 The Big Z Inc. Orders Database, shown in Figure8.48.

The data warehouse has to enable an analysis of order quantitiesby:

• date, including:

• full date

• day of week

• day of month

• month

• quarter

• year

• time

• product, including:

• product ID

• product name

• product type

• product supplier name

• customer, including:

• customer ID

• customer name

• customer type

• customer zip

• depot, including:

• depot ID

• depot size

• depot zip

• order clerk, including:

• order clerk id

• order clerk name

• order clerk title

• order clerk education level

• order clerk year of hire

Based on the sources and requirements defined in thisexercise, design a data warehouse model using star schema approachfor the Big Z Inc. Work on your star schema usingerdplus.com

8.47

8.48

HUMAN RESOURCES DEPARTMENT TABLE (EMPLOYEE DATA) EducationLevelYearOfHire EmployeelD Name Title OC1 OC2 Antonio Order Clerk High School Wesley Orer Clerk College Lilly Order Clerk College 2001 2005 2005 lierTD ProductName ProductType ProductID PRODU Supplies SUPPLIER DEPOT SupplierName DepotSize Includes ShipsFrom OCID OCName Customer1D CustomerNamec ORDERCLERK ORDER Submits CUSTOMER OrderID OrderTime CustomerŽip CustomerType OrderDate PRODUCT ProductID ProductName ProductType SupplierID (rK) SUPPLIER SupplierID SupplierName DEPOT ORDEREDVIA ProductID FK OrderID (FK) Quantity DepotID DepotSize DepotZip ORDER OrderID OrderDate OrderTime CustomerID (FK) DepotID K) OCID (FK) ORDERCLERK OCID OCName CUSTOMER CustomerID CustomerName CustomerType CustomerZip DEPOT DepotID DepotSize DepotZipProductlD ProductName ProductType SupplierlD SupplierlD SupplierName PRODUCT SUPPLIER BigGripperTire TractionWiz Tire SureStart ST Smal 60611 60660 60611 ST BE Batturies Etc Large P2 Battery BE ORDEREDVIA ORDERCLERK ORDER OCID İ OCName l OC1 Antonio OC2 Wesley OC3 Lilly OrderID | CustomerID | DepotID | OCID | OrderDate OrderTime OC1 1-Ja-2013 9:00:00 AM OC2 2-Jan-2013 9:00:00 AM OC3 2-Jan-20139:30:00 AMM OC1 3-Jan-2013 8:00:00 ANM OC2 3-Jan-2013 9:15:00 AM 0C3 3-Ja-2013 9:30:00 AM ProductID OrderlD Quantity 02 03 04 01 D2 02 D2 O5 06 O8 CUSTOMER CustomerID CustomerName CustomerType CustomerZip Auto Dac Bo’s Car Repair Repair Shop 60140 J Auto Parts Repair Shop 60137 C2 Retailer 60605 Show transcribed image text HUMAN RESOURCES DEPARTMENT TABLE (EMPLOYEE DATA) EducationLevelYearOfHire EmployeelD Name Title OC1 OC2 Antonio Order Clerk High School Wesley Orer Clerk College Lilly Order Clerk College 2001 2005 2005
lierTD ProductName ProductType ProductID PRODU Supplies SUPPLIER DEPOT SupplierName DepotSize Includes ShipsFrom OCID OCName Customer1D CustomerNamec ORDERCLERK ORDER Submits CUSTOMER OrderID OrderTime CustomerŽip CustomerType OrderDate PRODUCT ProductID ProductName ProductType SupplierID (rK) SUPPLIER SupplierID SupplierName DEPOT ORDEREDVIA ProductID FK OrderID (FK) Quantity DepotID DepotSize DepotZip ORDER OrderID OrderDate OrderTime CustomerID (FK) DepotID K) OCID (FK) ORDERCLERK OCID OCName CUSTOMER CustomerID CustomerName CustomerType CustomerZip DEPOT DepotID DepotSize DepotZipProductlD ProductName ProductType SupplierlD SupplierlD SupplierName PRODUCT SUPPLIER BigGripperTire TractionWiz Tire SureStart ST Smal 60611 60660 60611 ST BE Batturies Etc Large P2 Battery BE ORDEREDVIA ORDERCLERK ORDER OCID İ OCName l OC1 Antonio OC2 Wesley OC3 Lilly OrderID | CustomerID | DepotID | OCID | OrderDate OrderTime OC1 1-Ja-2013 9:00:00 AM OC2 2-Jan-2013 9:00:00 AM OC3 2-Jan-20139:30:00 AMM OC1 3-Jan-2013 8:00:00 ANM OC2 3-Jan-2013 9:15:00 AM 0C3 3-Ja-2013 9:30:00 AM ProductID OrderlD Quantity 02 03 04 01 D2 02 D2 O5 06 O8 CUSTOMER CustomerID CustomerName CustomerType CustomerZip Auto Dac Bo’s Car Repair Repair Shop 60140 J Auto Parts Repair Shop 60137 C2 Retailer 60605

Expert Answer

---

Answer to Consider the following scenario involving Big Z Inc., an automotive products wholesaler. Big Z Inc. wants to create the … . . .