Homework 2, Problem 1, United Nations
Draw an entity-relationship diagram for the following case.
The United Nations wants to develop a global database, tracking countries, cities, and multinational companies. For each country, the UN wants to store its name, capital, population, and size. The UN assigns each city in the world a unique, global, numeric identifier. It also wants to record the name of the city, the name of the city’s mayor (or chief executive), its population, and its size in square miles. Obviously, a city is associated with only one country. Each city also has city-owned buildings. Each building a city owns has a type (fire station, police station, etc.) an address (its unique identifier), and the year it was built.
Each multinational company has a globally unique identifier, a name, a president, and the year it was founded. Obviously, a country has many companies operating in it and, by definition, a multinational company can operate in more than one country. Each company has many employees. Each employee has an employee number unique to that company, plus a name and year of hire. Some employees of a company manage other employees of that company.
The United Nations is also interested in the agricultural output of each country, based on each crop. A crop has a unique name (corn, wheat, etc.), the season in which it is harvested, and its watering requirements on a scale of 1-10. The UN also wants to track the annual yield of each crop in each country.
Homework 2, Problem 2, Happy Airlines
Draw an entity-relationship diagram for the following case.
Happy Airlines wants to develop a system to keep track of the history of its flights and other related entities in its business model. It is important to understand the meaning of a “flight”: it is a trip made by a particular airplane on a particular date. A flight is identified by the combination of flight number and date, since the same flight number is used repeatedly on different dates. A particular flight on a particular date had a duration that Happy Airlines wants to record.
Airports are identified by a unique airport code. Other information stored about airports includes the airport name and size in square miles. Flights and airports are related in two ways: the origin of a flight and the destination of a flight. Happy Airlines also wants to maintain data about the city that an airport is located in. Cities are identified by the combination of state name and city name, and, in addition, the name of the mayor is to be stored. Airports have hangars that are identified by hangar numbers that are only unique within an airport. The size of each hangar must also be recorded.
Pilots are identified by a unique pilot number. The system is also to maintain each pilot’s name and cell phone number. Some pilots supervise other pilots. Each flight is assumed to have one pilot. Pilots are required to take periodic training classes. A class is identified by a unique class name and also has a description and length in days. Pilots take many training classes over time and, obviously, training classes are taken by many pilots. Happy Airlines wants to keep track of the date that a particular pilot took a particular class and the grade the pilot earned.
The airline industry includes airplanes of many models. Each airplane model is uniquely identified by the combination of its manufacturer (e.g. Boeing, Airbus, etc.), and its model number (e.g. 767, 320, etc.). The system is also to record the base price of each such model. Of course, there are many airplanes for each of these airplane models. In fact, Happy Airlines owns or leases many airplanes. Each airplane has a unique serial number, plus the year it was built and its passenger capacity. Each flight utilizes one airplane, but a newly acquired airplane may not have been used on any flights, yet.
A pilot may be trained and qualified to fly one or more airplane models and, of course, there are many pilots who are qualified to fly each airplane model.
Finally, Happy Airlines wants to keep a historical record of the passengers on its flights. Passengers are identified by a unique passenger number (like a required frequent flyer number), plus their name and date of birth must also be stored. A passenger may have taken many flights on Happy Airlines and, obviously, each flight has many passengers. For a particular passenger on a particular flight, the system must store the fare the passenger paid and the assigned seat number.
You can use any software tool (visio.com, lucidchart.com, diagrams.net, etc. ) for the diagrams or you can hand draw them NEATLY and then scan or photograph them.
Your Chapter 2 diagrams should be in the style illustrated by Fig. 2-22 on Page 94, which, of course, is what the chapter was all about.
In general, draw the diagrams carefully and completely. At this point you must list all of the fields involved, indicate the name of the entity type and the unique identifier. You also must indicate both minimum and maximums in the links expressing the different types of relationships (e.g. one-to-many, etc.). Again, all of this is as shown in Fig. 2-22. Put another way, make sure you understand Fig. 2-22 before you attempt the assignments.
