16 Nov A pharmaceutical retail company possesses various stores and manages its current sales, procurement and materials availabi
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:
• What is the name and complete address (Street, City, Zip code) of the supplier associated with a specific medicine order?
• What is the complete address (Street, City, Zip code) of the store associated with an order made on a specific day, month, or year?
• 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.
• 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:
• The order cost should be recorded as facts.
• The data mart will contain 4 years worth of data
• The data mart will be used to manage 3 different stores.
• Each store carries no more than 300 different medicines at the same time
• The medicines can be supplied by 5 different suppliers Based on this information, answer the following questions:
a. Design a star schema for this problem. (10 pts)
b. Estimate the number of rows in the fact table. (2 pts)
c. Based on your star schema, write the SQL code allowing you to answer the following questions: (8 pts, 4 pts each)
• 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?
• 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.
Our website has a team of professional writers who can help you write any of your homework. They will write your papers from scratch. We also have a team of editors just to make sure all papers are of HIGH QUALITY & PLAGIARISM FREE. To make an Order you only need to click Ask A Question and we will direct you to our Order Page at WriteEdu. Then fill Our Order Form with all your assignment instructions. Select your deadline and pay for your paper. You will get it few hours before your set deadline.
Fill in all the assignment paper details that are required in the order form with the standard information being the page count, deadline, academic level and type of paper. It is advisable to have this information at hand so that you can quickly fill in the necessary information needed in the form for the essay writer to be immediately assigned to your writing project. Make payment for the custom essay order to enable us to assign a suitable writer to your order. Payments are made through Paypal on a secured billing page. Finally, sit back and relax.
Do you need help with this question?
Get assignment help from WriteEdu.com Paper Writing Website and forget about your problems.
WriteEdu provides custom & cheap essay writing 100% original, plagiarism free essays, assignments & dissertations.
With an exceptional team of professional academic experts in a wide range of subjects, we can guarantee you an unrivaled quality of custom-written papers.
Chat with us today! We are always waiting to answer all your questions.