Question

Struggling a little bit with one of my homework questions. we are to create an ER digram from the following business rules using UML notation, we are encouraged to uses weak entities, sub classes and ternary relationships. Thank you in advance  

You have just been employed as a database designer in a well-established software development firm. Your first job is to design and implement a database system for an airline booking company. The following information has been gathered after analysing the current practices of the company.

• The system records information about flight bookings by customers.

• Customers are identified by an email address and have a phone number and a name.

• Customers make bookings for specific instances of a flight. The booking has a reference number for easy lookup and records the credit card number and the seat number on the flight. Each customer makes their own booking.

• Each flight has a unique flight code, which identifies the scheduled departure time, departure gate and departure airport of the flight, as well as the scheduled arrival time, arrival gate and arrival airport of the flight.

• Each airport has a unique airport code, and has a city.

• Flights repeat each day, e.g. a flight with the code QF430 is scheduled to depart Melbourne Airport at 9:30am each day from gate 3 and arrive at 10:55am at Sydney Airport at gate 2. Your design should not repeat common information for each daily flight.

• The status of a specific flight (e.g. boarding) will depend on the scheduled date of the specific flight instance.

• The pilot will also vary from flight to flight, but each specific flight has exactly 2 pilots. Pilots have an employee id and a name.

• Similarly, the aircraft used can vary from flight to flight, but each specific flight is on board a specific aircraft.

• Aircrafts have a tail number to identify them and have a specific model.

Answer 1

Please find below the steps to create Entity Relationship Diagram for the given system requirement in UML notation:

Step 1: List entities and weak entities:

1. Customer: A regular entity to store the details about the customer

2. Flight: This is the flight details where the static departure and arrival airports along with the specific schedule that is to be same for all days on same aircraft.

3. FlightInstance: It is a weak entity where Flight can have many FlightInstances which stores the daily status of the flight schedule with date and its status.

4. Airport: This will store the details about airport

5. Pilot: The details about the pilot

6. Aircraft: The aircraft used in the flights.

7. Bookings: It will store the booking of the customer tickets.

Step 2: List relationships among entities:

1. Flight has many FlightInstances but a FlightInstance belongs to one FlightCode.

2. Flight has one Airport as departing airport and one airport as arrival airport.

3. Flight has 2 Pilots and a Pilot can fly many Flights. It is M:N relationship which will be stored separately.

4. Flight uses same Aircarft daily but an aircraft can be used in many flights. It is M:1 relationship.

Step 3: Add attributes to the entities:

1. Customer(customer_id, name, email, phone_number)

2. Flight(flightCode, dep_gate , dep_ariportcode(fk), arrival_gate , arrival_ariportcode(fk), aircraft_tail_no(fk))

3. Airport(ariportcode,       city)

4. FlightInstance(flightcode, date, scheduled_dep_time, sche_arrival_time, status)

5. Pilot(employeeID, name)

6. Aircraft(aircraftTailNo, model)

Relational entities:

7. FlightPilot(flightcode, date, pilot_employee_id)

8. Booking(referenceno, customerid, flightcode, date, creditcard_no, seatno)

Step 4: Final ERD:

**PK is Primary key and FK is foreign key

Step 5: Entity Type Notations:

The entities where PK is FK from other table, are weak entities as below:

The entities having independent PK are normal entities.