A university plans to build a data warehouse that would help them in analyzing the performance of the students in various courses in different academi

Question

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

Accepted Answer

## 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_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).

Star Schema Design — University Performance Data Warehouse

Requirements Analysis

Fact Table

Dimension Tables

Schema Diagram

How it satisfies the requirements

Similar questions