MySQL Lab (2)
To convert an ER
diagram to its Relational Schema
To create the catalog of the database for implementation
You will submit one file at the end: 1) The
catalog for your database.
You are to create relational database base on an ER diagram that is
This ER diagram is for an airline service with the following key
requirements and constraints:
( 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.
As a team work together to create the Relational Database equivalent to the given
ER digram for the Airline database. You need to convert this ER diagram
to a relational schema. It is very important to do this very
carefully and accurately on your own first and then compare your
solutions with the other members of your team.
Hints: See the hint, if you have really tried your best. Hint1
Please make sure you have discussed and developed a final relational
database for your team. Once you discussed this as a team, come up with
one relational database for your team and write it clean on a piece paper. Then compare your solution to the one given
Even now that you have looked, are you sure this answer is correct?
Is there anything missing?
What to Submit?
The Catalog for the database using the given Template.
Lab Activity (2)
Create the catolog of the this database. You need to define every attributes
and determine their data types, whether they are required and/or not null.
You can download a template for catalog: Catalog Template
What to Submit?
1. Ctalog of your database on ASUlearn.
2. One script that creates all the tables in the Airline Database.