CSE 3241 Introduction to Database Systems
Work Assignment 2
Entity Relationship (ER) and Enhanced ER (EER) Model
There are 3 questions in this assignment. Both electronic or paper copies are
acceptable; however, paper copies must be turned in during class on 9/6
Question 1: (Exercise 3.19)
Consider the ER diagram in Figure 3.21, which shows a simplified schema for an airline
reservations system. Extract from the ER diagram the requirements and constraints that
produced this schema. Try to be as precise as possible in your requirements and constraints
specification.
This study source was downloaded by 100000850872992 from CourseHero.com on 02-12-2023 23:02:47 GMT -06:00
https://www.coursehero.com/file/33903401/WA2-Keydocx/
, Answer:
(1) The database represents each AIRPORT, keeping its unique AirportCode, the AIRPORT Name,
and the City and State in which the AIRPORT is located.
(2) Each airline FLIGHT has a unique number, the Airline for the FLIGHT, and the Weekdays on
which the FLIGHT is scheduled (for example, every day of the week except Sunday can be coded as
X7).
(3) A FLIGHT is composed of one or more FLIGHT LEGs (for example, flight number
CO1223 from New York to Los Angeles may have two FLIGHT LEGs: leg 1 from New York to
Houston and leg 2 from Houston to Los Angeles). Each FLIGHT LEG has a DEPARTURE AIRPORT
and Scheduled Departure Time, and an ARRIVAL AIRPORT and Scheduled Arrival Time.
(4) A LEG INSTANCE is an instance of a FLIGHT LEG on a specific Date (for example, CO1223 leg
1 on July 30, 1989). The actual Departure and Arrival AIRPORTs and
Times are recorded for each flight leg after the flight leg has been concluded. The
Number of available seats and the AIRPLANE used in the LEG INSTANCE are also kept.
(5) The customer RESERVATIONs on each LEG INSTANCE include the Customer Name, Phone,
and Seat Number(s) for each reservation.
(6) Information on AIRPLANEs and AIRPLANE TYPEs are also kept. For each AIRPLANE
TYPE (for example, DC-10), the TypeName, manufacturing Company, and
Maximum Number of Seats are kept. The AIRPORTs in which planes of this type
CAN LAND are kept in the database. For each AIRPLANE, the AirplaneId, Total number of seats,
and TYPE are kept.
Question 2: (Exercise 3.27)
Cardinality ratios often dictate the detailed design of a database. The cardinality ratio depends
on the real-world meaning of the entity types involved and is defined by the specific application.
This study source was downloaded by 100000850872992 from CourseHero.com on 02-12-2023 23:02:47 GMT -06:00
https://www.coursehero.com/file/33903401/WA2-Keydocx/
Work Assignment 2
Entity Relationship (ER) and Enhanced ER (EER) Model
There are 3 questions in this assignment. Both electronic or paper copies are
acceptable; however, paper copies must be turned in during class on 9/6
Question 1: (Exercise 3.19)
Consider the ER diagram in Figure 3.21, which shows a simplified schema for an airline
reservations system. Extract from the ER diagram the requirements and constraints that
produced this schema. Try to be as precise as possible in your requirements and constraints
specification.
This study source was downloaded by 100000850872992 from CourseHero.com on 02-12-2023 23:02:47 GMT -06:00
https://www.coursehero.com/file/33903401/WA2-Keydocx/
, Answer:
(1) The database represents each AIRPORT, keeping its unique AirportCode, the AIRPORT Name,
and the City and State in which the AIRPORT is located.
(2) Each airline FLIGHT has a unique number, the Airline for the FLIGHT, and the Weekdays on
which the FLIGHT is scheduled (for example, every day of the week except Sunday can be coded as
X7).
(3) A FLIGHT is composed of one or more FLIGHT LEGs (for example, flight number
CO1223 from New York to Los Angeles may have two FLIGHT LEGs: leg 1 from New York to
Houston and leg 2 from Houston to Los Angeles). Each FLIGHT LEG has a DEPARTURE AIRPORT
and Scheduled Departure Time, and an ARRIVAL AIRPORT and Scheduled Arrival Time.
(4) A LEG INSTANCE is an instance of a FLIGHT LEG on a specific Date (for example, CO1223 leg
1 on July 30, 1989). The actual Departure and Arrival AIRPORTs and
Times are recorded for each flight leg after the flight leg has been concluded. The
Number of available seats and the AIRPLANE used in the LEG INSTANCE are also kept.
(5) The customer RESERVATIONs on each LEG INSTANCE include the Customer Name, Phone,
and Seat Number(s) for each reservation.
(6) Information on AIRPLANEs and AIRPLANE TYPEs are also kept. For each AIRPLANE
TYPE (for example, DC-10), the TypeName, manufacturing Company, and
Maximum Number of Seats are kept. The AIRPORTs in which planes of this type
CAN LAND are kept in the database. For each AIRPLANE, the AirplaneId, Total number of seats,
and TYPE are kept.
Question 2: (Exercise 3.27)
Cardinality ratios often dictate the detailed design of a database. The cardinality ratio depends
on the real-world meaning of the entity types involved and is defined by the specific application.
This study source was downloaded by 100000850872992 from CourseHero.com on 02-12-2023 23:02:47 GMT -06:00
https://www.coursehero.com/file/33903401/WA2-Keydocx/
