Assignment
A pharmaceutical retail company possesses various stores and manages its current sales, procurement and materials availability at the stores through Excel sheets. The company executives are now finding this process of data maintenance tedious and would like to develop a complete data warehouse architecture to store historical data and to help making better business decisions. For this reason, you are asked to design a star schema to help manage and record the company order operations, with information related to the ordered medicines, the suppliers, the various stores information and when orders were made. Your star schema should be designed so that the following information can be obtained:
1. What is the name and complete address (Street, City, Zip code) of the supplier associated with a specific medicine order?
2. What is the complete address (Street, City, Zip code) of the store associated with an order made on a specific day, month, or year?
3. Not all medicine can be taken by both kids and adult. Actually, each medicine is designed for people within a specific age range, recorded using a starting age and an ending age. We are interested in knowing the name of all medicine available for anyone within a specific age range.
4. How many orders of a specific medicine were made by a specific store on specific day, month or year? Each store is represented by its own store code. You can also consider the following information:
5. The order cost should be recorded as facts.
6. The data mart will contain 4 years worth of data
7. The data mart will be used to manage 3 different stores.
8. Each store carries no more than 300 different medicines at the same time
9. The medicines can be supplied by 5 different suppliers Based on this information, answer the following questions:
I. Design a star schema for this problem.
II. Estimate the number of rows in the fact table.
III. Based on your star schema, write the SQL code allowing you to answer the following questions:
i. What is the name and complete address (Street, City, Zip code) of the supplier associated with a medicine named "Tylenol" and suited for anyone above 18 years old?
ii. For each city, indicate the total amount of order cost of all medicines ordered by a store in this city in November 2020. Your query should display the list of cities where stores are located and the associated total order cost.
Format your assignment according to the following formatting requirements:
i) The answer should be typed, using Times New Roman font (size 12), double spaced, with one-inch margins on all sides.
ii) The response also includes a cover page containing the title of the assignment, the student's name, the course title, and the date. The cover page is not included in the required page length.
iii) Also include a reference page. The Citations and references must follow APA format. The reference page is not included in the required page length.