# username9843/airport-mis architecture

> Architecture diagram of the GitHub repository username9843/airport-mis, generated by GitDiagram from its file tree, README and a sample of source files (last updated 2026-08-10). It is an AI-made overview: check details against the source.

- Interactive diagram: https://gitdiagram.com/username9843/airport-mis
- Repository: https://github.com/username9843/airport-mis

## Overview

#### Purpose and Execution

PostgreSQL database project for Ercan Airport (LCEN) management information. The repository is SQL-first, with no application runtime: create a PostgreSQL database and execute the scripts in sequence:

1. `01_ddl.sql` — schema and database behavior
2. `02_dml.sql` — seed/sample operational data
3. `03_queries.sql` — management reporting queries

`README.md` defines this execution order; `REPORT.md` is the supporting design/report artifact.

#### Schema and Integrity Layer

`01_ddl.sql` is the system’s primary architectural boundary. It creates the relational model for airport operations, including tables, keys, constraints, and a trigger. This layer owns canonical data structure and enforces business/data integrity inside PostgreSQL rather than in an external service.

Paths:
- `01_ddl.sql`
- `REPORT.md`

#### Operational Data Domain

The schema represents airport-management entities and their relationships, with the report providing the ERD/design context. Core operational records are stored in PostgreSQL and linked through relational constraints established by the DDL.

The database is the sole material runtime and persistence boundary; there are no indicated APIs, UI services, queues, or external integrations.

Paths:
- `01_ddl.sql`
- `REPORT.md`

#### Seed and Demonstration Data

`02_dml.sql` populates the schema with sample data after tables and constraints exist. It is intended for reproducible demonstrations and for supplying the reporting layer with representative operational records.

The DML script depends on successful execution of `01_ddl.sql`; its inserts must conform to the DDL’s relationships and trigger behavior.

Paths:
- `02_dml.sql`
- `01_ddl.sql`

#### Reporting and Management Queries

`03_queries.sql` is the read/reporting stage. It contains 15 management queries over the populated operational schema, producing decision-support views directly from PostgreSQL.

This script depends on both prior stages: schema objects from `01_ddl.sql` and available records from `02_dml.sql`.

Paths:
- `03_queries.sql`
- `02_dml.sql`
- `01_ddl.sql`

#### Primary Control and Data Flow

Control flow is linear and manual or SQL-client-driven:

`01_ddl.sql` → `02_dml.sql` → `03_queries.sql`

Data flow follows the same direction: DDL defines valid structures and database-side enforcement; DML writes sample airport data; queries join, filter, aggregate, and present that data for management use. The trigger in `01_ddl.sql` is an important write-time control boundary because it can apply behavior automatically during data changes.

#### Infrastructure Boundaries

Required infrastructure is a PostgreSQL server and a database created by the operator. SQL execution tooling is external to the repository (for example, `psql` or a PostgreSQL GUI), and no deployment automation or application host is represented.

Paths:
- `README.md`
- `01_ddl.sql`
- `03_queries.sql`

Main entry points: [`01_ddl.sql`](https://github.com/username9843/airport-mis/blob/main/01_ddl.sql) (DDL stage), [`03_queries.sql`](https://github.com/username9843/airport-mis/blob/main/03_queries.sql) (Management query stage), [`02_dml.sql`](https://github.com/username9843/airport-mis/blob/main/02_dml.sql) (Seed data stage), [`README.md`](https://github.com/username9843/airport-mis/blob/main/README.md) (Execution guide)

## Diagram (Mermaid)

```mermaid
flowchart TD

subgraph group_repo["SQL project"]
  node_readme["Execution guide<br/>documentation<br/>[README.md]"]
  node_report["Design report and ERD<br/>documentation<br/>[REPORT.md]"]
  node_ddl["DDL stage<br/>schema script<br/>[01_ddl.sql]"]
  node_dml["Seed data stage<br/>data script<br/>[02_dml.sql]"]
  node_queries["Management query stage<br/>reporting script<br/>[03_queries.sql]"]
end

subgraph group_postgres["PostgreSQL runtime"]
  node_model[("Airport operational schema<br/>relational model<br/>[01_ddl.sql]")]
  node_integrity{{"Keys and constraints<br/>integrity controls<br/>[01_ddl.sql]"}}
  node_trigger{{"Write-time trigger<br/>database trigger<br/>[01_ddl.sql]"}}
  node_records[("Operational records<br/>sample data<br/>[02_dml.sql]")]
  node_reports["Management results<br/>query results<br/>[03_queries.sql]"]
end

node_operator(("Operator / SQL client<br/>external execution"))

node_readme -.->|"directs execution"| node_operator
node_operator -->|"runs first"| node_ddl
node_ddl -->|"creates"| node_model
node_ddl -->|"defines"| node_integrity
node_ddl -->|"installs"| node_trigger
node_report -.->|"documents"| node_model
node_ddl -->|"must precede"| node_dml
node_operator -->|"runs second"| node_dml
node_dml -->|"inserts"| node_records
node_integrity -->|"validates"| node_records
node_trigger -->|"acts on writes"| node_records
node_dml -->|"supplies data before"| node_queries
node_operator -->|"runs third"| node_queries
node_queries -->|"reads"| node_model
node_queries -->|"queries"| node_records
node_queries -->|"produces"| node_reports

click node_readme "https://github.com/username9843/airport-mis/blob/main/README.md"
click node_report "https://github.com/username9843/airport-mis/blob/main/REPORT.md"
click node_ddl "https://github.com/username9843/airport-mis/blob/main/01_ddl.sql"
click node_model "https://github.com/username9843/airport-mis/blob/main/01_ddl.sql"
click node_integrity "https://github.com/username9843/airport-mis/blob/main/01_ddl.sql"
click node_trigger "https://github.com/username9843/airport-mis/blob/main/01_ddl.sql"
click node_dml "https://github.com/username9843/airport-mis/blob/main/02_dml.sql"
click node_records "https://github.com/username9843/airport-mis/blob/main/02_dml.sql"
click node_queries "https://github.com/username9843/airport-mis/blob/main/03_queries.sql"
click node_reports "https://github.com/username9843/airport-mis/blob/main/03_queries.sql"

classDef toneNeutral fill:#f8fafc,stroke:#334155,stroke-width:1.5px,color:#0f172a
classDef toneBlue fill:#dbeafe,stroke:#2563eb,stroke-width:1.5px,color:#172554
classDef toneAmber fill:#fef3c7,stroke:#d97706,stroke-width:1.5px,color:#78350f
classDef toneMint fill:#dcfce7,stroke:#16a34a,stroke-width:1.5px,color:#14532d
classDef toneRose fill:#ffe4e6,stroke:#e11d48,stroke-width:1.5px,color:#881337
classDef toneIndigo fill:#e0e7ff,stroke:#4f46e5,stroke-width:1.5px,color:#312e81
classDef toneTeal fill:#ccfbf1,stroke:#0f766e,stroke-width:1.5px,color:#134e4a
class node_readme,node_report,node_ddl,node_dml,node_queries toneBlue
class node_model,node_integrity,node_trigger,node_records,node_reports toneAmber
class node_operator toneNeutral
```

## Components

### SQL project

- [Execution guide](https://github.com/username9843/airport-mis/blob/main/README.md): documentation · Defines the required script order. · `README.md`
- [Design report and ERD](https://github.com/username9843/airport-mis/blob/main/REPORT.md): documentation · Provides relational design context. · `REPORT.md`
- [DDL stage](https://github.com/username9843/airport-mis/blob/main/01_ddl.sql): schema script · Creates the airport operational model and database behavior. · `01_ddl.sql`
- [Seed data stage](https://github.com/username9843/airport-mis/blob/main/02_dml.sql): data script · Loads reproducible sample operational records. · `02_dml.sql`
- [Management query stage](https://github.com/username9843/airport-mis/blob/main/03_queries.sql): reporting script · Contains 15 decision-support queries. · `03_queries.sql`

### PostgreSQL runtime

The sole persistence and execution boundary.

- [Airport operational schema](https://github.com/username9843/airport-mis/blob/main/01_ddl.sql): relational model · Canonical airport-management entities and relationships. · `01_ddl.sql`
- [Keys and constraints](https://github.com/username9843/airport-mis/blob/main/01_ddl.sql): integrity controls · Enforces relational and business-data validity. · `01_ddl.sql`
- [Write-time trigger](https://github.com/username9843/airport-mis/blob/main/01_ddl.sql): database trigger · Applies automatic behavior during data changes. · `01_ddl.sql`
- [Operational records](https://github.com/username9843/airport-mis/blob/main/02_dml.sql): sample data · Representative airport data stored in the schema. · `02_dml.sql`
- [Management results](https://github.com/username9843/airport-mis/blob/main/03_queries.sql): query results · Joined, filtered, and aggregated operational information. · `03_queries.sql`

### Other components

- **Operator / SQL client**: external execution · Manually creates the database and runs the scripts.

## Connections

- Execution guide → Operator / SQL client: directs execution
- Operator / SQL client → DDL stage: runs first
- DDL stage → Airport operational schema: creates
- DDL stage → Keys and constraints: defines
- DDL stage → Write-time trigger: installs
- Design report and ERD → Airport operational schema: documents
- DDL stage → Seed data stage: must precede
- Operator / SQL client → Seed data stage: runs second
- Seed data stage → Operational records: inserts
- Keys and constraints → Operational records: validates
- Write-time trigger → Operational records: acts on writes
- Seed data stage → Management query stage: supplies data before
- Operator / SQL client → Management query stage: runs third
- Management query stage → Airport operational schema: reads
- Management query stage → Operational records: queries
- Management query stage → Management results: produces