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.

Type
Data modelling, university project
Stack
SQL, PostgreSQL, Chen notation
Entities
5
Reports
3 stakeholder queries
Entities: Airline owns many Aircraft. Testing Event links one Aircraft, one Technician and one CASA Test Spec. Airlineairline_id, name Aircraftaircraft_id, airline_id, model Testing Eventaircraft_id, tech_id, spec_idtested_at, result Techniciantech_id, name CASA Test Specspec_id, description 1N 1N 1N 1N
Simplified from the Chen diagram. The testing event is the centre of the model: every report reads from it.

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