The star schema has one fact table (Big_University fact) with keys to four dimension tables (Student, Course, Semester, Instructor) and the measures count and avg_grade.
i. Star schema
Student_dim
(student_id, name, area_id, major,
status, university)
|
Course_dim ---- FACT TABLE ---- Semester_dim
(course_id, student_id (semester_id,
name, dept) course_id semester, year)
semester_id
instructor_id
count
avg_grade
|
Instructor_dim
(instructor_id, name, dept, rank)
In a star schema each dimension is a single denormalised table joined directly to the fact table (in a snowflake schema the dimensions would be split further, e.g. area into a separate table).
ii. Base cuboid: [Student, Course, Semester, Instructor], the 4-D cuboid at the lowest level of every dimension, storing count and avg_grade for every combination.
OLAP operations on it
- Roll-up: on Course from course_id to department, giving count and avg_grade per department.
- Drill-down: on Semester from year to semester, giving finer detail.
- Slice: fix one dimension, e.g. Semester = "Fall 2023", giving a 3-D sub-cube.
- Dice: select on two or more dimensions, e.g. department = "CS" and instructor rank = "Professor".
- Pivot: rotate the axes, e.g. show Course on rows and Instructor on columns.
Example query: to find each CS student's average grade, roll up Course to department, slice department = "CS", and roll up Semester and Instructor to "all".