From the Operational Model to the Dimensional Model

The Sales Database: An operational model

An ER model for an operational database is shown below.

Table: customer; Columns: custNumber, custName, address, currBal, credLimit, repNum.

Table: parts; Columns: partNum, partDesc, category, unitsOnHand, warehouseNum, unitPrice.

Table: slsrep; Columns: repNum, repName, repAddress, totComm, commRate.

Table: orders; Columns: orderNum, orderDate, custNum..

Table: orderline; Columns: orderNum, partNum, numOrdered.

Requirements for the Data Warehouse

We wish to design a data warehouse using the data from this operational database.

The process to be modeled is Customer Sales.

The grain is daily sales.

Dimensions will be determined by you.

The only facts to be stored are amount, representing daily sales total amount, and quantity, representing daily sales total quantity.

We want to be able to answer questions such as:

How many of part number ax$12$ were sold on September $2,1994?$

How many of part number ax$12$ did Customer $124$ purchase last year?

How much did Customer $124$ spend last year?

What was the average amount spent per customer per day during the month of September, $1994?$

What was the average quantity sold of part ax$12$ per day during the month of September, $1994?$

What was the average daily sales during the third quarter of $1994?$

How many appliance items were sold during the third quarter of $1994?$

What was the amount of revenue generated by all customers in zip code $64468$ during the month of September $1994?$

We do not care about answering questions such as the ones below:

What was the average amount of each order placed during $1994?$ In fact, we don$$t care about any questions related to specific orders.

How much did salesrep $3$ make during $1994?$ In fact, we don$$t care about any questions about sales reps.

What was the average credit limit for all customers on September $2,1994?$ In fact, we don$$t care about any questions about credit limits or balances.

We are not interested in inventory questions $$ such as which warehouse parts were stored in$,$ or how many units on hand were available during a given time period.

We do not want to store unnecessary data in the data warehouse.In particular, note that specific data about orders and orderline information, as well as salesrep information, is not necessary.

Data must be aggregated as necessary from the operational database before entering it into the data warehouse.

Your Task

Design the star schema dimensional model for a data warehouse to satisfy the above requirements. You may use any drawing tool you wish, or even draw the diagram on paper and upload.

My question is$,$ is the model that is attached to this correct?