IT, Web
Assignment 3
1- Draw an entity-relationship diagram that describes the following business environment.
Grand Travel Airlines has to keep track of its flight and airplane history. A flight is uniquely identified by the combination of a flight number and a date. In addition, every flight has an actual departure time and an actual arrival time. Every passenger who has flown on Grand Travel has a unique passenger number plus their name, address, and telephone number. For a particular passenger who has taken a particular flight, the company wants to keep track of the fare that she paid for it and the date that she made the reservation for it. Clearly, a passenger may have taken many flights (he must have taken at least one to be in the database) and every flight has had many passengers on it.
A pilot is identified by a unique pilot (or employee) number, a name, date of birth, and date of hire. A flight on a particular date has exactly one pilot. Each pilot has typically flown many flights but a pilot may be new to the company, is in training, and has not flown any flights, yet.
Each airplane has a unique serial number, a model, manufacturer name, passenger capacity, and year built. A flight on a particular date used one airplane. Each airplane has flown on many flights and dates, but a new airplane may not have been used at all, yet.
Grand Travel also wants to maintain data about its airplanes’ maintenance history. A maintenance procedure has a unique procedure number, a procedure name, and the frequency with which it is to be performed on every airplane. A maintenance location has a unique location name, plus an address, telephone number, and manager. Grand Travel wants to keep track of which airplane had which maintenance procedure performed at which location. For each such event it wants to know the date of the event and the duration.
2- The following is an example of a grade report for two students at the University. Convert the user view to a 3NF relation. Show each step along the way.
USNJ Grade Report
Spring Semester 2000
Name: I. M. Smarte
Student: 053-6929-24 Major: MIS
Status: Senior
Course Number
Course Title
Professor Professor’s
Department
Grade
MIS 403 Systems Analysis Diggs, T. MIS A
MIS 411 Conceptual Foundations Barre, G. MIS A
MIS 420 Human Factors in IS Barre, G. MIS B
CIS 412 Database Design Menzel, I. CIS A
DESC 353 Management Models Murney, J. MIS A
USNJ Grade Report
Spring Semester 2000
Name: E.Z. Grayed
Student: 472-6124-59 Major: MIS
Status: Senior
Course Number
Course Title
Professor Professor’s
Department
Grade
MIS 403 Systems Analysis Diggs, T. MIS B
MIS 411 Conceptual Foundations Barre, G. MIS A