Bug Tracking DBMS

A PostgreSQL database project for tracking employees, projects, issues, solutions, roles, comments, and attachments, with 26 reporting queries. This is a backend schema and SQL reporting project; it does not include an application UI.

Getting started

Requires PostgreSQL and its psql command-line client. Tested with PostgreSQL 16. Run these commands from the repository directory, using your PostgreSQL connection settings as needed:

createdb bug_tracking
psql -X -v ON_ERROR_STOP=1 -d bug_tracking -f schema.sql
psql -X -v ON_ERROR_STOP=1 -d bug_tracking -f queries.sql

schema.sql creates tables and domains in a new, empty database. It is not a migration for an existing installation. Reports initially return no data or zero counts until you insert records. The fixtures below are synthetic test data.

Files

• schema.sql: executable schema and constraints; the current source of truth.
• queries.sql: the 26 numbered reports.
• tests/fixtures.sql: synthetic data covering reporting edge cases.
• tests/test_reports.py: PostgreSQL regression checks for the actual queries.
• requirements_list.pdf: original assignment requirements.
• ddl_fd_normalization.pdf: original schema and normalization write-up.
• er_diagram.drawio.png: original entity-relationship diagram.
• relational_diagram.drawio.png: original relational diagram.

Reporting semantics

• Project issue counts and average staffing include projects with zero issues or employees. Employee rankings and issue comment counts likewise include zero counts. With no projects at all, average staffing is NULL (undefined).
• Resolved-issue reports count distinct issues whose current status is Resolved. Multiple solution notes or history rows do not multiply the count. Every employee linked through IssueSolution receives credit; the schema does not distinguish a final resolver from other contributors.
• Time-window reports require a resolution timestamp within the inclusive interval from the stated cutoff through the current timestamp. Future timestamps and currently reopened issues are excluded. Resolution history is attached to the issue, not to an individual employee's solution note.
• Report 12 lists employees with a global Write permission who are assigned to at least one project. The schema cannot express different permissions for each project. Roles can represent testers, but there is no separate tester-team model.
• Report 21 measures elapsed days, including fractions, from the earliest recorded history timestamp. Issues with no history cannot be aged and are excluded.
• Report 23 ranks all employees by project count, rather than returning only the employee(s) tied for first place. Report 24 averages priority scores only for projects that have issues; no-issue projects have no priority average.
• Attachments retained after issue deletion have a NULL issue ID. They are not treated as a group of attachments belonging to an issue in report 26.

Schema corrections and limitations

Compared with the original PDF:

• New history records leave solution_date as NULL until explicitly resolved; resolution dates cannot precede their corresponding creation dates.
• Issue status, priority, and history creation timestamps cannot be NULL.
• Deleting an employee removes their EmployeeProjects assignments using ON DELETE CASCADE. The original SET NULL conflicts with the composite primary key, which cannot contain NULL.

The original PDFs and diagrams are retained as historical assignment artifacts; use schema.sql and the notes above for current behavior. The PDF's 5NF conclusion is not established by its discussion of functional dependencies through BCNF. This repository does not claim a separate proof of 4NF or 5NF.

The schema does not automatically create history, synchronize resolution dates with issue status, or record which employee performed a status transition. A caller must update related records consistently in a transaction. Employee deletion also removes their solution records, following the original design; this is not an immutable audit log.

Verification

Requires Python 3 and psql; no Python packages are needed. Use a test database where your user can create a schema:

python3 tests/test_reports.py bug_tracking
# A PostgreSQL connection string also works:
python3 tests/test_reports.py 'host=localhost dbname=bug_tracking user=postgres'

The runner creates a uniquely named schema inside one transaction, loads the schema and fixtures, executes all 26 reports, and checks expected results. It rolls back its changes on success; a failed connection also rolls back the transaction. It does not modify the application's tables.

Fixtures exercise zero counts, duplicate solution/history records, reopened issues, resolution date cutoffs, future dates, orphan attachments, and deletion constraints.