Exercise 1

The purpose of a disposal dishware company manager is to implement a data warehouse application to observe his activity of sales at the different stores located in different cities of his products. Each store is characterized by a type (according to its surface), an address and a city. The manager wants to know the number of products sold and the total amount for each sale by year, quarter, and month. Based on this description, design a data warehouse star schema for this company with full details regarding dimensions and fact tables.

Exercise 2

The purpose of a movie manager is to implement a data warehouse application to improve analytics and decision making. The decision maker wants to make analysis according to several parameters. He wants to be able, for example, to get:

1. The number of films seen by category of film during a given year, in a given country.

2. The total budget per film, each year, and each month.

3. The total budget by Actor in a film.

4. The number of films seen by category of film (action, horror, romantic...), each year .

5. The total budget by category of film.

6. The number of viewers by film and by category of film.

7. The number of films by Studio.

Design the Movie Data Warehouse in the form of star schema. Indicate facts, measures, dimensions, and attributes of dimensions.

Exercise 3

1) The manager of an international company would like to know the quantity sold for each product and for each store by day, week, month, and year. For each store, the manager would like to know its corresponding city and country.

You have to design a star schema to achieve the goal of this manager.

2) If you think that some of your proposed dimension tables in Q1, are not normalized, you have to convert your star schema into snowflake schema to normalize these dimension tables.