Question

Suppose that a data warehouse for Big_University consists of the four dimensions Student, Course, Semester and Instructor, and two measures count and avg_grade, where count is the number of students and average grade is the course grade of the student.
Perform following tasks:
i. Design the star schema for the Big_University.
ii. Create a base cuboid for the Big_university database and apply different OLAP operations.

Answer 1

a) A Snowflake Shema is shown in figure below:

b)  base cuboid [student, course, semester, instructor]

The specific OLAP operations to be performed:

1. Roll-up on course from course_id to department.

2. Roll-up on a student from student_id to university.

3. Dice on course, student with department =”CS” and university = “biguniversity”

4. Drill-down on a student from the university to student_name.