Fact and dimensions
Enrollment is the fact table (who took which class, with what grade). Professor, Class, Major, Student are dimensions linked by foreign keys.
← Back to all projects
SQL · Python · PostgreSQL · ETL
University Database: Design & Cleaning
I designed a five-table university schema with data-integrity rules, then built a pandas ETL that cleaned messy source files so every row respected those rules before loading.
5Tables
4CHECK constraints
21 → 16Students after cleaning
27 → 10Enrollments after cleaning
Schema design
Rules in the database, not just in the code
Validation with CHECK
Faculty emails must end in .edu.sm, room numbers 1–15, students must be at least 18, and grades must be 0–100.
Why: bad data is rejected at the door
Keys
Primary keys on every table and foreign keys from classes to professors, students to majors, and enrollments to students and classes.
ETL
Cleaning the source data before loading
| Table | Before | After | What was removed |
|---|---|---|---|
| Professor | 10 | 6 | Invalid email domains |
| Class | 10 | 6 | Classes taught by removed professors |
| Major | 5 | 5 | Typo fixed (Mathtimatics → Mathematics) |
| Student | 21 | 16 | Test records and impossible birth dates |
| Enrollment | 27 | 10 | Orphans, dropped courses, standardised grades |
🔗 Order matters: parents are cleaned first (professors → classes, students), then children (enrollments) are filtered against the surviving keys. That is how referential integrity is kept.
Code
Schema and ETL
sql · schema
-- fact table : enrollment
-- dimension tables: professor, class , student , major
create table schema_42.Professor(
professor_id int primary key not null,
prof_first_name varchar(30),
prof_last_name varchar(30),
faculty_email text check (faculty_email like '%.edu.sm')
);
create table schema_42.Class(
class_id int primary key not null,
class_title varchar(50),
professor_id int,
Foreign Key (professor_id) REFERENCES schema_42.Professor(professor_id),
room_number int check (room_number between 1 and 15)
);
create table schema_42.Major(
major_id int primary key not null,
major_name varchar(50),
department_head varchar (30)
);
create table schema_42.Student(
student_id serial primary key not null,
first_name varchar(30),
last_name varchar(30),
birth_date DATE not null check (birth_date <= current_date - interval '18 years'),
major_id int,
Foreign Key (major_id) REFERENCES schema_42.Major(major_id)
);
create table schema_42.Enrollment(
enrollment_id int primary key not null,
student_id int not null,
Foreign Key (student_id) REFERENCES schema_42.Student(student_id),
class_id int not null,
Foreign Key (class_id) REFERENCES schema_42.Class(class_id),
grade int check (grade between 0 and 100)
);
python · cleaning
# professors: keep only valid university emails (.edu.sm)
professor["faculty_email"] = professor["faculty_email"].str.lower()
professor = professor[professor["faculty_email"].str.contains(".edu.sm")].reset_index(drop=True)
# classes must belong to a valid professor
classes = classes[classes["professor_id"].isin(professor["professor_id"])].reset_index(drop=True)
# fix a typo in the major name
major["major_name"] = major["major_name"].replace("Mathtimatics", "Mathematics")
# students: remove test records and impossible ages (18 to 40 allowed)
student = student[~student["first_name"].str.contains("Test", case=False)]
student["birth_date"] = pd.to_datetime(student["birth_date"])
today = pd.Timestamp("today")
student = student[(student["birth_date"] <= today - pd.DateOffset(years=18)) &
(student["birth_date"] >= today - pd.DateOffset(years=40))]
# enrollments: keep only rows whose student and class survived
enrollment = enrollment[enrollment["student_id"].isin(student["student_id"])]
enrollment = enrollment[enrollment["class_id"].isin(classes["class_id"])].reset_index(drop=True)
# standardise the grade column
enrollment["grade"] = enrollment["grade"].str.lower()
enrollment = enrollment[enrollment["grade"] != "drop"]
enrollment["grade"] = enrollment["grade"].str.upper()