Lab (1) - DBMS
To learn to convert an ER
diagram to its Relational Schema
To design an Access database
system based on the Relational Schema
To create some simple queries
Create a directory for your group and save your files in that directory.
You are to create relational database based 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.
You need to use the handout for Chapter (7) to convert this ER diagram
to a relational schema. It is very important to do this very
carefully and accurately.
Hints: See the hint, if you have really tried your best. Hint1
Once you are done with your relational database, let me see it before you start the
If you need a tutorial for MS Access, please visit the Resource Page
for the course.
Open MS Access. Create a new database, give it any name as you
wish (perhaps groupnameAccess). Now you have a blank
database. You need to figure out the rest 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.
If you wish to see the solution click here, ar you sure?, then click here.
Before you leave the lab, save the ACCESS file somewhere permanent or e-mail it to yourself for later use