Examination Management System DB¶

Role Sole Database Architect & Developer

Supported Engines PostgreSQL 17, MySQL 8.4, SQLite 3

Automation Stack Python, Pytest, Docker Compose, UV

Accreditation Harvard CS50 SQL with Distinction

Multi-Engine Consistency: 100% trigger and relational logic parity validated across PostgreSQL, MySQL, and SQLite using automated pytest test benches.

Architecture & Parity Pipeline¶

graph TD
    A["Relational Requirements &amp; ER Modeling"] --> B["Multi-Dialect DDL Schemas"]
    B --> C1["PostgreSQL (PL/pgSQL Functions)"]
    B --> C2["MySQL (Delimiter Triggers)"]
    B --> C3["SQLite (Embedded Triggers &amp; CHECKs)"]
    C1 --> D["Containerized Docker Environments"]
    C2 --> D
    C3 --> D
    D --> E["Automated Python Automation ('db.py')"]
    E --> F["Pytest Verification Test Harnesses"]
    F --> G["Materialized Analytical Reporting Views"]

Executive Overview¶

The Examination Management System (EMS DB) project is a modular, production-ready relational database architecture designed to administer educational examinations. It models students, proctors, tests, dynamic question banks, timed test sessions, audit events, and computed academic scores.

The architecture was engineered with strict multi-RDBMS parity: the system maintains three synchronized dialect implementations (PostgreSQL, MySQL, and SQLite) with automated Python test harnesses validating identical business logic execution across all three engines.

Technical Challenges & Architectural Solutions¶

1. Multi-Engine Relational & Trigger Parity¶

Challenge: Differences in dialect features (PL/pgSQL trigger functions vs MySQL delimiters vs SQLite embedded triggers) risked behavioral discrepancies.
Solution: Designed modular directory hierarchies (/psql, /mysql, /sqlite) with corresponding migration scripts, automating query testing via engine-specific Python drivers (psycopg2, mysql-connector-python, sqlite3).

2. Temporal Logic & Session Auto-Termination¶

Challenge: Dynamically computing test session termination timestamps without race conditions.
Solution: Implemented engine-native triggers (set_end_for_test_session) calculating interval arithmetic directly at write time based on test duration configurations.

3. Reporting Query Optimization¶

Challenge: Heavy joins across student records, question options, and audit history caused query latency.
Solution: Created targeted composite indexes and encapsulated analytical reporting logic into optimized SQL views (tests_history, summary_reports).

Verified Accreditation¶

Harvard CS50 SQL: Introduction to Databases with SQL • Harvard University (CS50)

Entity Relationship Architecture¶

Navigating the Nuances: A Developer's Guide to SQL Dialects: Deep dive into DDL differences, autoincrement sequence strategies, and trigger syntax across SQLite, MySQL, and PostgreSQL.
Beyond the Schema: Querying, CLI Interaction, & Docker Nuances: Practical patterns for script piping, container networking, and auto-increment resets.