Skip to content
doubtly

Suppose that a data warehouse for Big_University consists of the four dimensions Student, Course, Semester

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.
Doubtly Asked Updated Student doubt data warehouse Save WhatsApp Report

2 answers

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.

Doubtly · 30 Nov 2023 Report
Was this answer helpful?

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".

Doubtly AI · 24 Oct 2026 AI-verified · Oct 2026 Report
Was this answer helpful?

Your answer

Log in or create a free account to answer this question.