✈️ Ethiopian Airlines Domestic Operations Database

PostgreSQL V2 — Full Airline Management System

---

📌 Overview

A production-grade PostgreSQL relational database modelling Ethiopian domestic airline operations. Fully redesigned from MySQL V1 (10 tables) into a 31-table PostgreSQL V2 system covering flight operations, passenger management, ticketing, Ethiopian payment providers, baggage tracking, crew operations, aircraft maintenance, and administration.

---

✨ V1 vs V2 Comparison

Feature	V1 MySQL	V2 PostgreSQL
Tables	10	31
Airlines	4 mixed countries	6 Ethiopian only
Airports	7	25 Ethiopian
Payments	None	10 Ethiopian providers
Ticketing	None	Ticket + Boarding Pass
Baggage	None	Full tracking history
Maintenance	None	Aircraft maintenance logs
Audit logging	None	JSONB audit trail
Views	None	5 reporting views
Functions	None	2 PL/pgSQL functions
Triggers	None	3 automated triggers

---

🏗️ 10-Phase Architecture

Phase	Tables
1 — Core Foundation	Airline, Airport, Aircraft, FlightRoute, FlightSchedule, Flights
2 — Passenger System	Student, Passenger, Role, UserAccount
3 — Booking Engine	Bookings, Seat, SeatAssignment
4 — Ticketing	Ticket, BoardingPass
5 — Payments	Discount, PaymentMethod, Payment, Invoice, Refund
6 — Baggage	Baggage, BaggageTracking
7 — Crew Operations	CrewMembers, CrewAssignment, CrewTraining
8 — Maintenance	AircraftMaintenance, MaintenanceSchedule
9 — Flight Monitoring	Terminal, Gate, FlightStatus
10 — Administration	Notification, AuditLog

---

🛫 Ethiopian Coverage

6 Ethiopian Airlines: Ethiopian Airlines (ET) · Abyssinian Flight Services (AN) · Trans Nation Airways (TT) · National Airways Ethiopia (9Y) · AberdAir (AR) · East African Aviation

25 Ethiopian Airports: ADD · DIR · BJR · AWA · MQX · GDQ · JIM · AMH · LLI · AXU · JIJ · ASO · GMB · SHC · GOB · DSE · DBM · SZE · BCO · DEM · FNH · DBT · ETE · BEI · and new Addis Ababa mega-airport (planned)

10 Payment Methods: Telebirr · CBE Birr · Awash Birr · Amole · M-Pesa Ethiopia · Dashen Super App · Visa · Mastercard · Bank Transfer · Cash

---

📂 Repository Structure

Database-Project/
│
├── sql/
│   ├── airlines_v2_full.sql   ← Full schema + sample data
│   └── queries.sql            ← Advanced analytical queries
│
├── docs/
│   ├── ERD-v2.png             ← Entity Relationship Diagram
│   └── Database-Diagram-v2.png
│
├── screenshots/
│   └── queries.png
│
└── README.md

---

🚀 Getting Started

Requirements: PostgreSQL 14+

# Clone the repo
git clone https://github.com/gemachistesfaye/Database-Project.git
cd Database-Project
git checkout v2

# Create database
psql -U postgres -c "CREATE DATABASE airlines_db;"

# Run full schema
psql -U postgres -d airlines_db -f sql/airlines_v2_full.sql

# Verify — should show 31 tables
psql -U postgres -d airlines_db -c "\dt"

---

📊 PostgreSQL Features

5 Views

SELECT * FROM vw_active_bookings;
SELECT * FROM vw_revenue_by_route;
SELECT * FROM vw_student_discounts;
SELECT * FROM vw_aircraft_utilization;
SELECT * FROM vw_crew_schedule;

2 Functions

-- Fare with student discount applied
SELECT calculate_fare(4500.00, '1100/17');

-- Available seats on a flight
SELECT * FROM check_seat_availability(1, 'Economy');

3 Triggers

• Auto-create ticket when booking confirmed
• Auto-write audit log on every booking change
• Auto-send notification on new booking

---

📜 Version History

Version	Database	Tables	Description
v1.0.0	MySQL	10	Academic group project — Haramaya University
v2.0.0	PostgreSQL	31	Full redesign — portfolio project

---

👤 Author

Gemachis Tesfaye Haramaya University · College of Computing and Informatics Department of Information Science

github.com/gemachistesfaye