CIS331: Building E-R diagrams - Examples

Airline Database

Construct an E-R diagram for an airline database. The airline database contains information about passengers, flights, departure information, employees of the airline, and aircrafts. For passengers we record their name, address, phone number and related flight information. For employees of the airline company we record, their name, address, salary, an identification number, and flight information. Not all employees can fly aircrafts; just the pilots :-). For those employees we record their qualifications, i.e., what kind of planes they can fly. For planes we record the model number and maker of the plane. The airline has many aircrafts of a certain type (of plane). For flights the airline needs to keep the following information: the flight number, origin, destination, departure time and arrival time. Notice that the same flight (flight number, etc) may run several times a week.

Department Store Database

Construct an E-R diagram for a department store database. This database contains information about customers, items they order, departments in the store, employees, and suppliers of merchandise. For customers we record their name, address, balance and orders they have placed. The orders are for certain items and should include a description of the item, item # and quantity. Products are usually being supplied by more than one supplier at different prices. The store needs to keep contact information about the suppliers. The store is divided into departments. An employee cannot work in more than one departments of the store. Each department has its own manager. For employees the store needs to keep their name, contact information and salary.

E-R diagrams

SQL scripts