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 given: The ER diagram

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.

Lab Activity (1)
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 here. here.
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?
Submit the catalog of your database on ASUlearn.