Home work

 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. 

Don't use plagiarized sources. Get Your Custom Essay on
Home work
Just from $13/Page
Order Essay
Calculate your order
Pages (275 words)
Standard price: $0.00
Client Reviews
4.9
Sitejabber
4.6
Trustpilot
4.8
Our Guarantees
100% Confidentiality
Information about customers is confidential and never disclosed to third parties.
Original Writing
We complete all papers from scratch. You can get a plagiarism report.
Timely Delivery
No missed deadlines – 97% of assignments are completed in time.
Money Back
If you're confident that a writer didn't follow your order details, ask for a refund.

Calculate the price of your order

You will get a personal manager and a discount.
We'll send you the first draft for approval by at
Total price:
$0.00
Power up Your Academic Success with the
Team of Professionals. We’ve Got Your Back.
Power up Your Study Success with Experts We’ve Got Your Back.
Live Chat+1(978) 822-0999EmailWhatsApp

Order your essay today and save 20% with the discount code ORIGINAL