Comprehensive Database Design for Airline Management System

Design a relational database for an airline management system including entities like Flight, Crew Member, Airplane, Airport, Customer, Ticket, and Booking. Establishing relationships and attributes to efficiently manage flight operations, crew members, customer bookings, and airport information.

• Database Design
• Airline Management
• Relational Database
• Flight Operations
• Entity Relationships

call_2 Follow

Uploaded on Oct 11, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Database Systems PROJECT PART 1 ANSWERS

2. Flight, Crew Member, Airplane Every flight is identified by a flight number and has a date and three or more crew members. Flight crew members have an employee id, first name, and last name. Each crew member has a position on each of their flights. For each flight, we also need to track the airplane used. Each airplane has a serial number and a number of seats.

3. Airport Each airport is identified by a three- letter code and also has a name and city. Each flight departs from one airport and arrives at one airport. You also need to track the gate number at which each flight departs and arrives.

4. Member Customers make bookings to purchase tickets for flights. A customer is uniqely identified by their first name, last name, and DOB. A ticket belongs to one customer and is for one flight. Each ticket has a price. A booking consists of one or more tickets and is identified by the booking id. Each ticket is part of exactly one booking. A booking is paid for by one customer (who may or may not have a ticket). Customers who are closely related to employees get special perks. Thus, you also need to track which customers are related to which flight crew members.

5. Whole Model CrewMember(empID, firstName, lastName) Customer(firstName, lastName, dob) Airplane(serialNo, numSeats) Flight(flightNo, date) Ticket(price) Airport(code, name, city) Booking(bookingID)

6. Parent-Child Relationships CrewMember(empID, firstName, lastName) Customer(firstName, lastName, dob) Airplane(serialNo, numSeats) Flight(flightNo, date, airplaneNo, departAirport, departGate, arriveAirport, arriveGate) Ticket(price, firstName, lastName, dob, flightNo, bookingID) Airport(code, name, city) Booking(bookingID, firstName, lastName, dob)

7. Weak Entity Primary Key CrewMember(empID, firstName, lastName) Customer(firstName, lastName, dob) Airplane(serialNo, numSeats) Flight(flightNo, date, airplaneNo, departAirport, departGate, arriveAirport, arriveGate) Ticket(price, firstName, lastName, dob, flightNo, bookingID) Airport(code, name, city) Booking(bookingID, firstName, lastName, dob)

8. Additional Tables CrewMember(empID, firstName, lastName) Customer(firstName, lastName, dob) Airplane(serialNo, numSeats) Flight(flightNo, date, airplaneNo, departAirport, departGate, arriveAirport, arriveGate) Ticket(price, firstName, lastName, dob, flightNo, bookingID) Airport(code, name, city) Booking(bookingID, firstName, lastName, dob) Staffing(empID, flightNo, position) Relationship(empID, firstName, lastName, dob)

9. Project Part 2 Use the ER-Model and schema on the previous slide to guide you. Pay attention to data types, foreign keys, and nullability. Hand in the DDL. Part 2 is due Tuesday, November 8.