University registration database
- Role
- Solo, course project
- Period
- November 2024
- Outcome
- Schema normalised to 3NF and BCNF with integrity constraints preventing duplicate and invalid registrations
- Stack
- Oracle 11g
- SQL
- Unix shell
Problem
Model university course registration properly. Students, courses, sections, instructors, and enrollments, with all the rules that go with them: a student cannot register twice for the same section, a section cannot be assigned an instructor who does not exist, and the answers to enrollment questions have to stay correct as the data changes.
Registration data is where denormalised schemas go wrong quickly. Store the instructor’s name on the enrollment row and it will disagree with itself within a term.
Constraints
- Oracle 11g SQL.
- The interface had to be a Unix shell, so no application layer to hide logic in. Anything the system enforces, the database enforces.
What I built
A schema normalised to 3NF and then BCNF, which removed the update anomalies that a flatter design would have carried and kept the query paths predictable.
Integrity enforced in the database: primary and foreign key constraints plus validation logic, so duplicate registrations and invalid course assignments are rejected by the schema rather than by whoever happens to be calling it.
Unix shell scripts with text menus over the complex queries: enrollment, course availability, and instructor assignment. The menus exist so the queries are usable, but the queries are the actual artifact.
Decisions and tradeoffs
BCNF rather than stopping at 3NF. Going to BCNF removed the remaining anomalies, at the cost of more joins on read. For a registration system, where correctness under concurrent writes matters more than read speed on a small dataset, that is the right side of the trade. On a read heavy system at scale it would not be, and the answer would be a denormalised read model.