Coffee Merchant Enterprises (CM) sells coffee from a variety of different countr
ID: 3873169 • Letter: C
Question
Coffee Merchant Enterprises (CM) sells coffee from a variety of different countries to consumers in the US. Their sales are all on-line. CM wants to make data more available to their marketing analysts so that they can develop more effective marketing programs. They want to accomplish this goal without slowing down the performance of their existing OLTP system. They have decided to create a Data Warehouse to provide the information that the marketing anyalysts need. Your task is to design the database for the Data Warehouse. The marketing analysts have asked for the ability to see sales data by product (inventory), consumer, employee who placed the order, and by various date variables including date, week, quarter, year, day of the week, and season. They do not need to see every sales but would need to see product sales summarized for each product on a daily basis.
Explanation / Answer
Product
Product_id
Name
Description
Exipry_date
Tables for Data warehouse:
Consumer
Consumer_id
Name
Ph_no
Employee
Emp_id
Name
Ph_no
Address
Order
Order_id
description
Total_price
Emp_id
Consumer_id
Day_placed
Week_palaced
Quarter_placed
Year_palced
Season_placed
Order_Mapping
Order_id
Product_id
The Order and product table can be joined and queried easily order by day, quarter, year or season
For eg:
Select Order.Oder_id, order_mapping.Product_id, Week_palaced from order, order_mapping group by Week_palaced;
I have solved your question. Please do not forget to give a positive like to the answer. Thank you.
Product_id
Name
Description
Exipry_date