University Records Database with Roles, Audit Trail and Reports

The problem departments have with spreadsheet records

Many university departments and colleges still keep a lot of records in spreadsheets passed between coordinators and lecturers: who is enrolled in which section, who has cleared prerequisites, which grades were changed after the result was announced. The same student appears twice with a different spelling, a section is enrolled beyond capacity, a grade is changed and nobody can say by whom. When an audit or an accreditation visit arrives, someone spends a week reconciling files.

A well-designed relational database solves most of this, but only if the design, constraints and permissions are done properly. It is also exactly what database courses teach on paper and rarely make students build end to end.

What already exists and the gap

• Ellucian Banner and Oracle PeopleSoft Campus Solutions are full student information systems used by large universities.
• OpenEduCat is an open source education management system built on Odoo.
• Fedena and similar school ERPs cover admissions, fees and grades for schools and colleges.

These systems are large, expensive to customise and usually owned by the central IT office. Departments and small colleges often work around them in spreadsheets. The gap this project fills is twofold: a lean, well-documented departmental records database that enforces the rules at the database level, and a reference design that shows how integrity, security and auditing should work, useful for teaching as much as for use.

What you build

• Schema: students, programmes, courses, prerequisites, sections, lecturers, enrolments and grades, normalised to third normal form, with documented decisions about where you denormalise and why.
• Constraints for every rule you can express: unique enrolments, valid grade values, section capacity, credit limits per term, foreign keys with deliberate ON DELETE behaviour.
• Transactions: enrolment with capacity and prerequisite checks, and grade corrections, as procedures that are safe under concurrency.
• Audit trail: a table filled by triggers recording old and new values, the database user and the time.
• Roles: registrar (full write), lecturer (own sections only, through views) and read-only reporting.
• Reports: 20 or more queries with the question each answers, from "students at risk of exceeding the credit limit" to "grade distribution per section compared with the course average".
• Operations: versioned migrations, seed data and a backup and restore drill.
• A thin front end for the registrar and lecturers, only enough to demonstrate the flows.

The technically interesting part

The interesting problem is putting the rules in the database instead of the app. Show your supervisor what happens when two registrar sessions try to enrol different students into the last seat at the same moment: without the right isolation level or row lock, both succeed and the section is over capacity. Demonstrate the bug, then the fix. Do the same for grade changes: the audit trigger must record every change even if someone connects directly with a SQL client, which an application-level log cannot guarantee.

How to evaluate it

• Constraint tests: a script of at least 30 invalid operations (duplicate enrolment, grade out of range, lecturer editing another section) that must all fail with a clear error.
• Concurrency test: 100 simulated simultaneous enrolments into a 40-seat section always end with exactly 40.
• Permissions: a test that logs in as each role and confirms what it can and cannot read or change.
• Recovery: time to restore from backup and verification that row counts and checksums match.
• Usability: a department coordinator tries the 5 most common tasks and reports what was confusing.

Suggested plan

1. Weeks 1 to 2: interview a department coordinator, list the rules, draw the ER diagram.
2. Weeks 3 to 5: schema, constraints, migrations, seed data.
3. Weeks 6 to 8: transactions, concurrency tests, audit trail.
4. Weeks 9 to 11: roles, views, reporting queries.
5. Weeks 12 to 15: thin front end, backup drill, evaluation and report.

Path to a product

The first user is your own department's coordinator. Small private colleges and training institutes that cannot afford a full student information system could use a hosted version at a modest per-institution fee, with setup and data migration as paid services. The schema and tests also make a good open source teaching resource that database lecturers can adopt.

Scope warning

Use synthetic data only, never real student records. Do not build fee collection, timetabling, admissions or a learning management system. Keep the front end minimal. Choose one database and use its features properly rather than aiming for lowest-common-denominator SQL.

Learn what you need on Sefism

• Backend roadmap : databases, transactions and security in the order you need them.
• Backend cheat sheet : SQL, indexing and isolation levels at a glance.
• PostgreSQL Documentation : constraints, triggers, roles and row-level security.
• CMU 15-445/645: Intro to Database Systems : concurrency control and recovery explained properly.
• roadmap.sh: SQL : a checklist of the SQL features your reports should use.

Get help with this idea

A database-only project is marked on the quality of its design and tests. Tauseef can help you choose which rules to enforce, how to demonstrate concurrency bugs, and how to present the evaluation. If you are new here, create a free account first.

• Discuss this idea with a mentor
• Send a query to Tauseef
• Book a 1:1 session