Answer to Question #252713 in Databases | SQL | Oracle | MS Access for Tege

Question

2. Consider the ER diagram in Figure, which shows a simplified schema for an airline reservations system. Extract from the ER diagram the requirements and constraints that produced this schema. List and identify entities, attributes, relationships and keys. 

Answer 1

SOLUTION ON THE ABOVE QUESTION USING THE ABOVE ER DIAGRAM

REQUIREMENTS EXTRACTED FROM OUR ER DIAGRAM

i)The airline has one or more airplanes.

ii)An airplane has a model number, a unique registration number, and the capacity to take one or more passengers.

iii)An airplane flight has a unique flight number, a departure airport, a destination airport, a departure date and time, and an arrival date and time.

iv)Each flight is carried out by a single airplane.

v)A passenger has given names, a surname, and a unique email address.

vi)A passenger can book a seat on a flight.

CONSTRAINTS EXTRACTED FROM THE ER DIAGRAM

i)Each plane must have a unique registration number

ii)The capacity of the plane should take one or more passengers.

iii)Each flight must have a unique flight number

ENTITIES IN THE ER DIAGRAM

i).Airplane

ii)Flight

iii)Passenger

Attributes In The ER Diagram

i)Attributes for Airplane entity are model Number, Registration Number, capacity.

ii)Attributes for Flight entity are Flight Number, From CITY, To CITY, Departure date,

 Departure time, Arrival date, Arrival time.

iii)Attributes for Passenger entity are Given Names, surname, Email address.

Relationships in The ER diagram

i)Flies - A relationship between a airplane and flight, i.e. An airplane flies from which city

 to which city.

ii)Books - This is a relationship between a passenger and a flight i.e. a passenger books a flight.

Keys in The ER diagram

i)Registration Number - this is the primary key for airplane entity which uniquely identifies a plane.

ii)Flight Number - this is the primary key for flight entity which uniquely identifies a flight.

iii)Email address - this is the primary key for passenger entity which uniquely identifies a passenger.