(Solved): Problem 4. Snowflake Schema (18 points) Suppose that a data warehouse for Big ...

???????

Problem 4. Snowflake Schema (18 points) Suppose that a data warehouse for Big Ten universities consists of the four dimensions: student, course, semester, and instructor, and two measures: avg grade and count. Each dimension has a concept hierarchy, e.g., for student, the concept hierarchy is "student < major $$<$$ college $$<$$ university $$<$$ all"; for course, the concept hierarchy is "course $$<$$ department $$<$$ college $$<$$ university $$<$$ all ", etc. At the lowest conceptual level, i.e., for a given student, course, semester, instructor combination, the avg grade measure stores the actual course grade of the student. At higher levels of the concept hierarchy for one or more dimensions, avg grade stores the average grade for the given combination. (1) (8 points) Assume that student has attributes $$S_1,S_2,S_{3,key},S_4$$, where $$S_{3,key}$$ has attributes $$S_{3,key},S_{3,1},S_{3,2},S_{3,3}$$; course has attributes $$C_1,C_{2,key},C_3$$, where $$C_{2,key}$$ has attributes $$C_{2,key},C_{2,1};$$ semester has attributes $$T_1,T_2$$, and instructor has attributes $$I_1,I_2$$. Draw a snowflake schema diagram for the data warehouse, where the dimension tables will be based on the attributes of the dimensions. (2) (5 points) Starting with the base cuboid [student, course, semester, instructor], what specific OLAP operations (e.g., roll-up, drill down, etc.) should you perform in order to list the average grade of Computer Science courses (i.e., department = "Computer Science" for dimension course) for each student. (3) (5 points) If each of the four dimensions in the data warehouse has five levels (including all), e.g., "student < major < status $$<$$ university $$<$$ all" for student, how many cuboids will the data cube contain (including the base and apex cuboids)? Clearly justify your answer. and we have the option of not having a dimension.