✈️ FlyHaraty Database Management System

This project presents a fully structured relational database system for FlyHaraty, a fictional international airline company.
It was designed and implemented as part of a DBMS course project, demonstrating conceptual modeling, logical schema design, normalization, and SQL implementation for a real-world-style airline management scenario.

---

📖 Overview

FlyHaraty operates international flights, offering clients a complete travel experience including flight booking, in-flight services, hotel reservations, ground transport, and insurance coverage.

This database was developed to manage all aspects of the company’s ecosystem — from passengers and staff to partner stores and airports — using relational integrity constraints, foreign key dependencies, and advanced SQL queries.

---

🧱 Database Schema

Entities and Relationships

The database schema was carefully normalized (3NF) and includes the following key relations:

Table	Description
Client	Stores personal information of passengers including flight details and terminal assignments.
Ticket	Contains booking information, class, meal, insurance, and luggage weight for each reservation.
HotelRoom	Manages hotel reservations associated with clients, including check-in/out and pricing.
Flight	Records all flight details such as plane number, origin, destination, and departure/arrival times.
Transport	Represents ground transport services including pickup/dropoff and driver assignment.
Staff	Stores information about employees, their departments, salaries, insurance, and supervisors.
Insurance	Lists available insurance policies with start and renewal dates.
Department	Defines the company’s functional departments, their countries, budgets, and managers.
Airport	Contains details about airports (capacity, terminals, gates, and web pages).
Store	Manages partner stores within airports and their profit-sharing agreements.
Includes	Junction table linking tickets with transport services.
Operates	Links staff (pilots and crew) to specific flights.
Nationality	Allows multiple nationalities per client.

Each entity and relation was modeled following referential integrity constraints and logical consistency for realistic airline operations.

---

⚙️ Features

• 🧩 Relational Schema Implementation
  Complete CREATE TABLE statements with primary/foreign keys and referential integrity.

• 📦 Data Population
  Massive insertion blocks (INSERT ALL ... INTO ...) pre-filled with realistic test data covering clients, staff, flights, and hotels.

• 🧮 Normalization & Referential Integrity
  Schema normalized to 3NF; foreign key constraints ensure consistency between related tables.

• 🔍 Complex Query Examples
  Includes advanced SQL queries for analysis and report generation — e.g. partnership evaluation, flight crew allocation, client tracking, etc.

---

🗄️ Database Creation Order

Due to foreign key dependencies, tables should be created in this order:

1. Airport
2. Insurance
3. Department
4. Staff
5. Flight
6. Client
7. Ticket
8. HotelRoom
9. Transport
10. Store
11. Includes
12. Operates
13. Nationality

(Alternatively, constraints can be added after all tables are created using ALTER TABLE ... ADD CONSTRAINT.)

---

💾 Example Queries

Here are a few examples included in the project:

1. Partnership Evaluation

SELECT StoreName, AName, AirportName, ACountry
FROM Store, Airport
WHERE AName = AirportName
  AND ProfitSplit <= 40;

2. Passenger Tracking

SELECT FName, MName, LName, Client.CustomerID,
       PassportNum, PhoneNumber, HotelName, RoomNum
FROM Client, HotelRoom, Ticket
WHERE Client.PNum = 'FH123'
  AND CustomerID = PsngrID
  AND PassengerID = CustomerID
  AND Meal = 'Caesar Salad';

3. Pilot Assignment

SELECT s.Name, o.Role, f.PlaneNum, f.DepartFrom, f.Destination
FROM Operates o
JOIN Staff s ON s.EmployeeID = o.OperatorID
JOIN Flight f ON f.PlaneNum = o.PlaneNo
WHERE o.Role = 'Pilot';

📊 ER Diagram

---

🚀 How to Run

1. Clone the repository

git clone https://github.com/gaelleloutfi/Database-SQL.git
cd Database-SQL

2. Open your SQL environment (e.g., Oracle SQL Developer, DBeaver, or pgAdmin).
3. Run the schema and data

@src/fly_haraty.sql

4. Execute the example queries at the end of the same file, or in your SQL editor.

📘 Learning Outcomes

This project demonstrates:

• Logical database design and normalization (ER → relational schema).

• Use of primary/foreign keys and referential constraints.

• Realistic data modeling for an airline ecosystem.

• Multi-table joins and analytical SQL queries.