Projects / Aircraft maintenance
Modelling aircraft maintenance tests from a regulator's brief
Western Sydney Airport needed to track which technician ran which CASA test on which aircraft, and report on it. I turned the requirements brief into a Chen ERD and wrote the reporting queries on top.
From brief to model
The brief described the work in plain language: airlines own aircraft, technicians run tests, CASA defines the specifications those tests follow. The nouns became entities and the verbs became relationships. The decision that mattered most was treating a test as its own entity, a Testing Event, rather than a relationship between a technician and an aircraft. That gave it a place to store when it happened and whether it passed, and it made each report a simple query.
I documented every assumption the brief left open, along with the model's limits, so a reviewer could see which parts came from the requirements and which came from me.
The reports
- Test history by aircraft: every test an aircraft has had, newest first
- Technician workload: tests per technician over a period
- Pass rate by specification: which CASA specs fail most often
-- pass rate by CASA test specification (simplified) SELECT s.spec_id, s.description, COUNT(*) AS tests, ROUND(100.0 * AVG(CASE WHEN e.result = 'pass' THEN 1 ELSE 0 END), 1) AS pass_rate_pct FROM testing_event e JOIN casa_test_spec s ON s.spec_id = e.spec_id GROUP BY s.spec_id, s.description ORDER BY pass_rate_pct ASC;
What I'd add next
- An index on
testing_event(aircraft_id, tested_at), since the history report filters and sorts on exactly those columns - An audit trail on results. In a regulated setting, a test result being edited after the fact should leave a record
- Constraints that stop a technician being assigned a test they aren't certified for