Lab (1) - DBMS
Fall 2004

Objectives
      
To learn to convert ER to Relational Schema
       To design an Access database system  based on the Relational Schema
       To create some simple queries

Preparations
Create a directory for your group and save your files in that directory.

Lab Activity (1)
You are to create a simplified ER diagram for airline reservations system.  For now the idea is that you as a team come up with a schema, then convert that to an ER diagram.  Here are some of the key requirements and constraints you need to consider:

( 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.

You need to use the handout for Chapter (9) to convert this ER diagram to a relational schema.  It is very important to do this very carefully and accurately. 

Hints:  You only look at the hints if you are really stocked.
Hint1
Hint2  (The ER diagram)

I need you to show me your ER diagram before you leave the lab.

Lab Activity (2)
Once that is done, open MS Access.  Create a new database, give it a name as you wish.  Now you have a blank database.  You can use section 10.7 for more information on Access.  You need to figure the rest out by discussing it among yourselves.  Note that Access also allows you to view the relational schema as a diagram.  Try to find out how. After you create your database, you need to add some fake records.