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.
• scripts/generate_sample_data.py: configurable, seeded SQL sample-data generator.
• tests/test_sample_data.py: generator repeatability, safety, and database checks.
• 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.

Generate sample data

The hand-written fixtures and report regression runner already automate edge-case testing. For a larger demo dataset, the generator removes the need to write each employee, project, issue, and related record manually, coordinate their foreign keys, or adjust record counts by hand. It uses Python 3's standard library, with no additional packages, and writes an SQL file without connecting to a database.

From the repository directory:

# Defaults: 20 employees, 5 projects, 100 issues, seed 42.
python3 scripts/generate_sample_data.py

# Load into a NEW demo database; choose an unused database name.
createdb bug_tracking_demo
psql -X -v ON_ERROR_STOP=1 -d bug_tracking_demo -f schema.sql
psql -X -v ON_ERROR_STOP=1 -d bug_tracking_demo -f sample_data.sql
psql -X -v ON_ERROR_STOP=1 -d bug_tracking_demo -f queries.sql

Customize the size, seed, dates, and output location:

python3 scripts/generate_sample_data.py \
  --employees 50 --projects 10 --issues 1000 --seed 123 \
  --as-of 2026-09-20 --max-comments 4 --max-attachments 2 \
  --output demo_1000.sql

Employees and projects must each be at least 1. Issues may be 0. Comments and attachments are randomly generated from 0 through their respective per-issue maximums (defaults: 3 and 2); set either maximum to 0 to omit those records. The output's parent directory must exist. An existing output file is never overwritten: choose a different filename for another run.

The same options, script version, and Python version produce byte-identical SQL. The default date anchor is fixed at 2026-01-01, so time passing does not change the output; set --as-of explicitly for recent demo dates. Reports use the database's current clock, so date-window report results can change even when the SQL is identical.

The generated data includes roles, permissions, project assignments, one history row per issue, solution notes for resolved issues, comments, and attachment paths. Every project has an assigned employee with Write permission. Solution authors are selected from those employees. Open and in-progress issues have no resolution date or solution note; resolved issues have both. Dates run from project start through noon on the date anchor, with valid creation/resolution order. Attachment paths are placeholders; no physical attachments are created. Names and email addresses are synthetic, using example.com.

The SQL requires schema.sql to have been loaded and all 12 tables to be empty. It locks the tables against concurrent writes, checks emptiness, then inserts all records in one transaction. It never deletes, updates, or replaces existing rows; loading it twice fails with an explanatory error. Serial sequences are restarted transactionally after the inserts so later default IDs do not collide. Run the load as the schema owner, with ON_ERROR_STOP=1 as shown above. Do not wrap this file in another transaction; it contains its own BEGIN and COMMIT.

This is a baseline demo generator, not a model of a full status-transition audit trail or a performance benchmark. It keeps the generated data in memory and is intended for student-project datasets. Keep the existing edge-case fixtures for duplicate histories, reopened issues, orphan attachments, and boundary-date tests.

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.

Run the generator checks separately:

# Three checks without a database; six PostgreSQL checks are explicitly skipped.
python3 tests/test_sample_data.py

# All nine checks, including real imports and all 26 reports:
python3 tests/test_sample_data.py --database bug_tracking

The PostgreSQL generator checks create unique test schemas, load and commit the actual generated SQL there, and drop those schemas on completion (including ordinary test failures). They leave application tables alone. Use a disposable test database; an interrupted process may leave a sample_test_* schema behind. Checks cover foreign keys and constraints during import, workflow consistency, date ordering, sequence values, minimal and zero-issue datasets, multi-batch inserts, rejection of nonempty tables, rollback after an insert failure, and unchanged records after a rejected second load. The original report regression checks remain separate.