Assignment $3$: Data Warehouse $(60$ points max$)$

A retail company wants to create a data warehouse. Suppose the data warehouse consists of five dimensions: Time, Product, Supplier, Transportation, and Customer, and two measures: Count and Revenue.

Time dimension has attributes Date, Month, Quarter, and Year.

Product dimension has attributes Product ID$,$ Product Name, Category, and Price.

Supplier dimension has attributes Supplier ID$,$ Name, Lead Time, Location.

Transportation dimension has attributes Transportation Type, Vehicle Number, Delivery time, and Fuel Consumption.

Customer dimension has attributes like Customer ID$,$ Age, Gender, Customer Segment, City, and Country.

Draw a star schema diagram for the above data warehouse specification. $(30$ pts $)$

Starting with the base cuboid $[$time$,$ product, supplier, transportation, customer$],$ write the specific OLAP operations one should perform to list the total revenue generated from VIP customers $($from customer segment$)$ purchasing electronics products $($from product category$)$ delivered by truck $($from Transportation type$)$ in $2022$ and $2023.(15$ pts$)$

For the above data warehouse, draw a snowflake schema by expanding any $2$ of the dimensions $($except Time$).(15$ pts $)$

Important Note: You can complete your assignment in Word or hand$-$drawn on a paper and scan it as pdf$.$ Submit your answers in a single file $($Word or pdf$)$ and not as individual files