حل مشروع مستودعات البيانات (DW) مع شرح طريقة الحل.

حل مشروع مستودعات البيانات (DW) مع شرح طريقة الحل بالخطوات وبطريقة مبسطه وسهلة الفهم.

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 (include primary keys PK and forign keys FK for all

tables and relationship cardinality one-to-many or many-to-one).

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 (include primary keys PK and forign keys FK for all tables and relationship cardinality

one-to-many or many-to-one).

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 Question 1, are not normalized, you have to convert your

star schema into snowflake schema to normalize these dimension tables (include primary keys PK and forign keys FK for

all tables and relationship cardinality one-to-many or many-to-one).