Airline-db

capstone project

Project Overview

The Airline Database Capstone Project involves designing and implementing a database management system for a fictional airline company. The project aims to demonstrate proficiency in database design, development, and management.

Project Objectives

1. Design a conceptual database schema for the airline company.
2. Implement the database schema using a relational database management system (RDBMS) such as MySQL or PostgreSQL.
3. Populate the database with sample data.
4. Develop a user interface to interact with the database.
5. Perform queries and analytics on the database to extract insights.

Entity-Relationship Diagram (ERD)

The ERD should include the following entities and relationships:

1. Airlines
   • Airline ID (primary key)
   • Airline name
   • Airline code
2. Flights
   • Flight ID (primary key)
   • Airline ID (foreign key)
   • Departure airport
   • Arrival airport
   • Departure time
   • Arrival time
3. Passengers
   • Passenger ID (primary key)
   • Passenger name
   • Passenger email
   • Passenger phone number
4. Bookings
   • Booking ID (primary key)
   • Passenger ID (foreign key)
   • Flight ID (foreign key)
   • Booking date
   • Booking status
5. Payments
   • Payment ID (primary key)
   • Booking ID (foreign key)
   • Payment method
   • Payment date
   • Payment amount

Database Schema

The database schema should include the following tables:

1. airlines
2. flights
3. passengers
4. bookings
5. payments

User Interface

The user interface should allow users to:

1. Create, read, update, and delete (CRUD) airlines, flights, passengers, bookings, and payments.
2. Search for flights by departure and arrival airports, dates, and times.
3. Book flights and make payments.
4. View booking history and payment receipts.

Queries and Analytics

The following queries and analytics should be performed:

1. Retrieve the total number of flights operated by each airline.
2. Retrieve the total number of passengers booked on each flight.
3. Retrieve the total revenue generated by each airline.
4. Analyze the booking trends by month, quarter, and year.
5. Analyze the payment methods used by passengers.

Tools and Technologies

The following tools and technologies can be used:

1. MySQL or PostgreSQL for database management.
2. Python or Java for backend development.
3. HTML, CSS, and JavaScript for frontend development.
4. Flask or Django for web framework.
5. pandas and NumPy for data analysis.

Timeline

The project timeline should include the following milestones:

1. Week 1-2: Database design and schema creation.
2. Week 3-4: Database implementation and population.
3. Week 5-6: User interface development.
4. Week 7-8: Query and analytics development.
5. Week 9-10: Testing and debugging.
6. Week 11: Final presentation and submission.