Question 2

Consider a data warehouse storing data about sales, where the total items sold are stored, organised by customer order, product, store. Each customer order includes the customer id, name of the customer, address, city, country and the date of the order; Each product includes a product id, product name, description of the product and its price; Each store includes the store id, address, city, country. i. Devise and draw the relational schema (specifying the relations, the attributes, the primary keys, and the foreign keys) of the above data warehouse using the star schema. [10 marks] ii. Devise and draw the relational schema (specifying the relations, the attributes, the primary keys, and the foreign keys) of the above data warehouse using the snowflake schema. [10 marks] iii. Explain what can be done to the snowflake schema devised in (ii) above to improve its performance. [5 marks]