PyQdeck LogoPyQdeckSubjectsSyllabusBlog
Back to the 2021 paper

Module 1: Data Warehousing and Business Analysis

202110m

A university plans to build a data warehouse that would help them in analyzing the performance of the students in various courses in different academic sessions. They want to analyze if there is any relation between the average grade of a course and the number of students attending it. They would also like to know if there were some courses offered but did not have any students registered for them. Relative performance among boys and girls and average grades of students from various States and cities of the country for each course must be analyzed and also overall CGPA.

Design a star schema for such a data warehouse clearly identifying the fact table(s) and dimension table(s), their primary key(s) and foreign key(s). Your schema should at least be able to satisfy the above-mentioned analysis requirements. You may consider other suitable attributes for the dimension table(s).

Search GoogleAsk AI
Worked SolutionAI Assisted

Star Schema Design — University Performance Data Warehouse

Requirements Analysis

We need to analyze: grade-vs-enrollment relation, courses with zero registrations, gender-wise performance, state/city-wise average grade per course, and overall CGPA. This means our grain (finest fact-table row) should be one student's performance in one course, in one academic session.

Fact Table

FACT_PERFORMANCE

Column Type Key
student_key FK → DIM_STUDENT PK (composite)
course_key FK → DIM_COURSE PK (composite)
session_key FK → DIM_SESSION PK (composite)
grade_point Measure
sgpa Measure
is_registered Measure (flag, 1/0)

Using a is_registered flag (or simply having a row exist) lets us detect courses offered but with zero enrolled students via a LEFT JOIN from DIM_COURSE × DIM_SESSION against the fact table.

Dimension Tables

DIM_STUDENT
| student_key (PK) | student_id | name | gender | state | city | cgpa |

DIM_COURSE
| course_key (PK) | course_id | course_name | department | credits |

DIM_SESSION
| session_key (PK) | academic_year | semester_type (Odd/Even) |

Schema Diagram

                  DIM_STUDENT
                 (student_key PK)
                  gender, state, city, cgpa
                        │
                        │FK
                        ▼
DIM_COURSE ──FK──▶ FACT_PERFORMANCE ◀──FK── DIM_SESSION
(course_key PK)     (student_key,        (session_key PK)
course_name,         course_key,          academic_year,
department           session_key,         semester_type
                      grade_point,
                      sgpa,
                      is_registered)

How it satisfies the requirements

  • Grade vs enrollment relation → aggregate AVG(grade_point) and COUNT(student_key) grouped by course_key.
  • Courses with no students → DIM_COURSE LEFT JOIN FACT_PERFORMANCE, filter WHERE fact IS NULL.
  • Gender-wise performance → join DIM_STUDENT.gender, group by course & gender.
  • State/city-wise average grade → join DIM_STUDENT.state/city, group by course.
  • Overall CGPA → stored directly in DIM_STUDENT.cgpa (or derived as AVG(sgpa) across sessions per student).

This is a classic star schema: one fact table with numeric measures (grade_point, sgpa) surrounded by denormalized dimension tables, which keeps analytical queries fast (few joins, simple filters).

Similar questions

Data MiningDiscuss the concept of a star schema and snowflake schema in data warehouse design. Illustrate with an example.20247mData MiningWrite an SQL query that runs on the schema designed in Q4a and returns the average SGPA of boys from the State of Karnataka for each Autumn (odd) semester during the years 2005–2009.20214mDatabase Management SystemConsider: * Student(SID, Name, Dept, Age) * Course(CID, CName, Faculty) * Enroll(SID, CID, Marks) 1. Write relational algebra expressions to find: * a) Students enrolled in DBMS * b) Students scoring >80 marks * c) Students not enrolled in any course 2. Write SQL queries: * a) Find highest marks * b) Find average marks department-wise * c) Display students enrolled in more than two courses 3. Translate the following query into tuple relational calculus: * "Find names of students from CSE department." 4. Convert the following relational algebra into SQL: * \pi_{Name}(\sigma_{Marks > 85}(Student \bowtie Enroll))202510mData MiningDiscuss Star schema with suitable example.20234m
PreviousWhat is over-fitting and under-fitting of a model? When do they happen?NextWrite an SQL query that runs on the schema designed in Q4a and returns the average SGPA of boys from the State of Karnataka for each Autumn (odd) semester during the years 2005–2009.
Free AppAndroid App

Coursify Android App

Generate AI courses, take quick quizzes between classes, and master complex engineering concepts on the go.

Install Coursify