PFA screenshot Business scenario: Dallas WareHouse Inc. has an OLTP to record its daily operating transactions. When a customer make order(s) with one employee, the orders info such as order number, order date, ship date, and customer id and employee id are recorded. All customers information and employees information (i.e. customers first name, customers last name, employees first name etc.) are also stored. When each order is created, the products that customers ordered are checked in their current inventory in order to provide accurate orders ship date estimation. If the products quantity on hand is less than the quantity ordered, then additional products are requested to vendors. For each request, product id, vendor id, wholesale price and days to deliver are recorded. All vendors information and products information (i.e., vendors name, products description, and products retail price etc.) are also stored. The figure above shows the logical schema of Dallas WareHouses OLTP. Part 1: The CEO of Dallas WareHouse Inc. wants a data warehouse. He would like to determine how much each customer order by week, month, and year, and how many orders are created by time periods. He is also interested in how many orders are made on weekends, Black Friday, and Independence Day. Provide 4- step dimensional model in the SQL comment section (2 points) and create data mart solution in SQL for the dimension model that you propose (2 points). Part 2: The CEO of Dallas WareHouse Inc. wants another data warehouse to track the use of his facilities. He is concerned with how effective his employees run their inventory operations to maximize profits. The profits come from billing customers as much as possible for each product, while lowering the cost of product purchase from vendors. To measure the effectiveness of product pricing to customers, gross profit is calculated. The product acquisition is strongly tied with the volume of products requested from vendors. For each 100 of same products shipped to Dallas WareHouse, a 50-cent reduction in wholesale price is promoted, with the maximum of $2 reduction for each product. Provide 4-step dimensional model in the SQL comment section (2 points) and create data mart solution in SQL for the dimension model that you propose (4 points).

CUSTOMERS ORDERS OrdorNumber PK OrderDate ShipDate CustomerlD FK EmployeelD FR EMPLOYEES EmployeelD PK ORDER DETAILS EmpCity OrderNumber CPK ProduciNumber CPK QuotedPrice EmpzipCode EmpAreaCode CategorylD F CATEGORIES PRODUCT VENDORS Category D PK CategoryDescription ProductNumber CPK VENDOAS VondorlD Vendcity VendPhoneNumber VendFaxNumber VendWebPage CUSTOMERS ORDERS OrdorNumber PK OrderDate ShipDate CustomerlD FK EmployeelD FR EMPLOYEES EmployeelD PK ORDER DETAILS EmpCity OrderNumber CPK ProduciNumber CPK QuotedPrice EmpzipCode EmpAreaCode CategorylD F CATEGORIES PRODUCT VENDORS Category D PK CategoryDescription ProductNumber CPK VENDOAS VondorlD Vendcity VendPhoneNumber VendFaxNumber VendWebPage