4 Questions about data warehouseq

 1. State different between data warehouse and data mart in detail?     

 

The primary distinction between Data warehouse and Data mart is that, Data Warehouse is the type of database which is data-oriented in nature. While, Data Mart is the type of database which is the project-oriented in nature. In addition, Data warehouse is enormous in scope, large in size and has long life whereas Data mart is restricted in scope, smaller in size and has short life than warehouse

Warehouse is flexible but difficult to build. While Data mart is not flexible but easy to build. In addition, Fact constellation schema is used in Data warehouse. While in Data mart, Star schema and snowflake schema are used.

Data warehouse is a centralized system and contains data in detail form. While Data mart is a decentralised system and contains data in summarized form. Data warehouse is top-down model and there is lightly denormalization.          While Data mart is a bottom-up model and there is highly denormalization.

                                                                             

2. What are situations where snowflake schema is better than star schema when the opposite is true?                                                                                      

 

Both are type of multidimensional model. If we don't need to normalization or joins and we need to fast, easy and simple queries, so it is better to use star schema.

Whereas if we need to use less space, normalization and there is low data redundancy, so it is better to use  snowflake schema.      

If we don’t care about time and we need to show large number of tables (include subdimentions tables), so it is better to use  snowflake schema.      

We can say Snowflake schemas are good for Data warehouses, whereas star schemas are better for Data marts with simple relationships.

 

3. How many cuboids in a 10-dimensional cube with 5 levels?         [1 Marks]

This is the formula for n-dimensional cube and L-level:

T=

In this question, the cube has 10 dimensions and each dimension has five levels, so

L= 5, n=10

The total number of cuboids that can be generated is 8 × 1 0⁶= 8000000.

 

4. A data warehouse consists of the four dimensions instructor, student, course, and semester, and the one measure avg_grade, where the avg_grade measure stores the actual course grade of the student.                                                            [2 Marks]

Draw the lattice of cuboids (from apex to base cuboid) for the above data warehouse.

Lattice of cuboids

b) Draw the star schema and snowflake schema diagram for the above data warehouse.

Star schema diagram          

Snowflake schema diagram