In class, we defined the data contained in data warehousing as Subject-oriented, Integrated, Timevariant and Non-updatable.

Pick any two out of these four characteristics to explain the difference between the data contained in operational system and informational system.

You are expected to use your own wording. Feel free to use example scenario(s) to illustrate your explanation, as long as those are not the same examples covered in the slides.

Question 2 (12 pts) 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 the company order supply operations.

Dimension Table Attributes

Medicine Medicine_Name, Format, ExpirationDate

Supplier SupplierName, Address, City, ZipCode

Store StoreCode, Address, City, ZipCode

Time Day, Month, Year

You can also consider the following information :

The quantity ordered and 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 5 different stores. Each store carries no more than 400 different medicines at the same time The medicines can be supplied by 6 different suppliers

Based on these information, answer the following questions : a. Design a star schema for this problem. (6 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 : (4 pts, 2 pts each) - What is the code, address, city and zipcode of all the stores who ordered the medicine named Tylenol in 2020 ? - For each Supplier, indicate the total order cost of medicine ordered in November 2018. Your query should display the supplier name and the associated total order cost.