Task#1 [Marks: 5]: Draw an Entity Relationship Diagram (ER Model) for the given case study along with proper associations and cardinalities.

Task#1 [Marks: 5]:  Draw an Entity Relationship Diagram (ER Model) for the given case study along with proper associations and cardinalities.

Solution:

                        First we write the entities that are involved in this case study.

·         Passenger

·         Booking office

·         Fair

·         Route

·         Flight

·         Airline

·         Airplane

·         Airport

The Diagram is as under. . . .

Task#2 [Marks 5]:

Derive the tables from the ER model and normalize up to third normal form (3^rd NF)

Solution:

Detail Table

Flight ID	Airport code	Airport	Date	Departure	Arrival	Passenger id	Name	Contact
XYZ123	DUB	Dublin	01/11	09:15	09:45	C3458	SMITH	909090
XYZ123	DUB	Dublin	01/11	09:15	09:45	C8765	JONES	919191
XYZ123	DUB	Dublin	01/11	09:15	09:45	C1383	GREEN	909190

1NF

FLIGHT (FlightID, AirportCode, Airport, Date, Departure, Arrival)

FLIGHT-PASSENGER (FlightID,PassengerID, Name, Contact)

2NF

FLIGHT (FlightID, AirportCode, Airport, Date, Departure, Arrical)

FLIGHT_PASSENGER (FlightID, PassengerID)

Passenger (PassengerID, Name, Contact)

3NF

FLIGHT (FlightID, AirportCode, Date, Departure, Arrival)

AIRPORT (AirportCode, Airport)

FLIGHT-PASSENGER (FlightID, PassengerID)

PASSENGER (PassengerID, Name, Contact)

Task#3 [Marks 10]:

De-normalize the database (obtained after completing task#2) by using the “collapsing tables” technique.