INFO210: Spring 2001

Assignment 1

Place your completed assignment in my Rush Building mailbox BEFORE class on Wednesday, April 18. No late submissions will be accepted without prior extension. Hand-drawn diagrams are OK if done very carefully. Follow all diagramming conventions as outlined in the textbook. Make sure your name and email are on the top of every page.

Part A (40 points): ER Diagram of Wonder Airlines

After a preliminary discussion with the Chairman of the up-and-coming Wonder Airlines you have the following preliminary notes for the database system you have been hired to design. Use them to prepare an ER diagram of the airline’s operations.

Personnel are important to the airline. The different classes of employee include pilot, flight attendant and security agent. Each person hired has a unique SSN, a start date, and a name. The system should also track their current salary.

In the case of pilots, it must track their total number of flight hours and the number of years flying experience they have had. Flight attendants are qualified to work in certain models of plane. In addition, pilots are certified only to fly certain models of plane. Certification for each class of plane needs to be renewed on a regular basis, so the system must be able to generate a reminder when certification is due to expire.

The airline has a number of different flight numbers. Each flight number goes between two airports. It runs at the same time every day. A flight number always uses the same model of plane, but the specific plane might be different each time it runs. Each plane has its own unique registration number. The pilot and flight attendants assigned to a flight number will be different each time it runs (let’s call this a flight instance). Each flight instance has two pilots and a varying number of flight attendants. Flight statistics are very important to Wonder Airlines, so the system must also log the actual time each flight instance departs, as well as the scheduled time for the flight number.

The airline also cares about passengers. It must be able to generate lists of passengers on each flight, together with the amount they paid. The system must store their current names and addresses on file.

Extra Credit: Some passengers are members of the frequent flier program – for these the system must store their frequent flier account information. Frequent fliers accumulate points every time they fly – the system must be able to generate their statements and to track their current status level.

Part B (10 points): Questions and Assumptions

Although this is much more coherent and complete that the summary you would likely receive from a real first meeting, I have deliberately left a few items ambiguous and failed to explicitly mention a couple of important attributes. Make intelligent assumptions here. Name entity and relationship types consistently and carefully. When you submit your diagram, attach a listing of the assumptions you have made, and list at least three (3) questions that you would have for the chairman on your next meeting.