Hospital Information System

We would like to design a database to maintain information about hospital staff, including doctors and nurses, and patients at the hospital. The information we need includes:

• Staff, including their names, addresses and social-security numbers.
• Patients, including their names, addresses, and the name of their insurance company.
• Patients are each assigned to a ward (room).
• Those staff who are nurses are assigned to zero or more wards. Each ward has at least one nurse assigned.
• Those staff who are doctors are assigned to zero or more patients. Patients may or may not have a doctor assigned, and they may have more than one doctor. Patients in the same ward may have different doctors but will always have the same nurse(s).

Note that there is no single right answer to this question, although some answers may be better than others.

Manufacturing Company

Draw an ER diagram that models the information in the following scenario: A manufacturing company has several assembly plants in different cities.  Each plant produces one product which requires certain parts in its assembly.  The parts are from appropriate suppliers, located in different cities.  Obtained in bulk amounts, certain parts may be used in more than one product.

Public Housing

Imagine you work for the public housing agency of a city, and you have been charged with keeping track of who is living in the agency's developments over time. To help you in this task, you have decided to use a relational database for your record keeping. Your task is to design a database that allows you to capture the facts described below:

• The city has three public housing developments. You want to record their names, locations, the year they opened, and their height in stories.


• For each unit in the development, you want to keep track of the number of bedrooms, the number of bathrooms, whether the unit has a kitchen or living room, and the square footage.


• The database should keep track of the households living in the units. For each member of a household, you want to record their name, date of birth, sex, and indicate whether or not they are they are the head of the household (more than one person can share that distinction).


• You also want to keep track of when a household moved into and out of a particular unit. You want to be able to follow households as they move from one unit to another or from one development to another. Think about how you will find the unit that the household is currently occupying (i.e., what query would you write to find the current unit of each household).

You will invent data for the three developments; two units in each development; and three families, one with 2 members, one with 3 members, and one with 4 members. Include records for each household making one move to another unit.

You may be interested to know that this assignment was inspired by records kept by the Boston Housing Authority about their developments. Hence, this type of problem has definite real-world importance.

The Music Database

The music database stores details of a personal music library, and could be used to manage your MP3, CD, or vinyl collection. Because this database is for a personal collection, it’s relatively simple and stores only the relationships between artists, albums, and tracks. It ignores the requirements of many music genres, making it most useful for storing popular music and less useful for storing jazz or classical music. (

Requirements for our database:

• The collection consists of albums.

• An album is made by exactly one artist.

• An artist makes one or more albums.

• An album contains one or more tracks

• Artists, albums, and tracks each have a name.

• Each track is on exactly one album.

• Each track has a time length, measured in seconds.

• When a track is played, the date and time the playback began (to the nearest second) should be recorded; this is used for reporting when a track was last played, as well as the number of times music by an artist, from an album, or a track has been played.

There’s no requirement to capture composers, group members or sidemen, recording date or location, the source media, or any other details of artists, albums, or tracks.

The Flight Database

The flight database stores details about an airline’s fleet, flights, and seat bookings. Again, it’s a hugely simplified version of what a real airline would use, but the principles are the same.

Consider the following requirements list:

• The airline has one or more airplanes.

• An airplane has a model number, a unique registration number, and the capacity to take one or more passengers.

• An airplane flight has a unique flight number, a departure airport, a destination airport, a departure date and time, and an arrival date and time.

• Each flight is carried out by a single airplane.

• A passenger has given names, a surname, and a unique email address.

• A passenger can book a seat on a flight.

Consulates in Embassies

In this problem, we shall design a database involving cities, the countries they are in, and "consulates." The relevant information:

• Each city is in a unique country. Data about cities include the name of the city and its population. Names of cities are unique within countries, but two cities in different countries may have the same name (and possibly even the same population).
• Information about a country includes its name and its head of state. You may assume no two countries have the same name.
• Information about a consulate includes its name, which is unique (e.g., "consulate of the United States in Toronto") and its street address (which is unique within a city, but might not be unique among all consulates in different cities).
• Cities and the country they are in are related by a relationship In.
• Consulates and the city in which each is located are related by a relationship Location.
• Countries and consulates are related by a representation Represents. Each country represented by a consulate has a consul, who is represented only by a name. A subtle point is that occasionally, a consulate will represent more that one country. For example, country A may not have diplomatic relations with country B, so A will ask country C to represent A's interests in country B at their own consultates. In that case, the consul for country A is actually a citizen of country C, and that person will likely not be the same as the consul for country C at the same consulate.
• While you might assume that a country would have at most one consulate in a given city, that is not always the case. For example, the US retains two consulates in Jerusalem.

Car Dealership

The dealership sells both new and used cars, and it operates a service facility. Base your design on the following business rules:

• A salesperson may sell many cars, but each car is sold by only one salesperson.
• A customer may buy many cars, but each car is bought by only one customer.
• A salesperson writes a single invoice for each car he or she sells.
• A customer gets an invoice for each car he or she buys.
• A customer may come in just to have his or her car serviced; that is, a customer need not buy a car to be classified as a customer.
• When a customer takes one or more cars in for repair or service, one service ticket is written for each car.
• The car dealership maintains a service history for each of the cars serviced. The service  records are referenced by the car’s serial number.
• A car brought in for service can be worked on by many mechanics, and each mechanic may work on many cars.
• A car that is serviced may or may not need parts (e.g., adjusting a carburetor or cleaning a fuel injector nozzle does not require providing new parts).

Congress

Design an ER diagram for keeping track of information about votes taken in the U.S. House of Representatives during the current two-year congressional session.

• The database needs to keep track of each U.S. STATE’s Name (e.g., Texas, New York, California) and includes the Region of the state (whose domain is {Northeast, Midwest, Southeast, Southwest, West}).
• Each CONGRESSPERSON in the House of Representatives is described by their Name, and includes the District represented, the StartDate when they were first elected, and the political Party they belong to (whose domain is {Republican Democrat, Independent, Other}).
• The database keeps track of each BILL (i.e., proposed law), and includes the BillName, the DateOfVote on the bill, whether the bill PassedOrFailed (whose domain is {YES,NO}), and the Sponsor (the congressperson(s) who sponsored--i.e., proposed--the bill).
• The database keeps track how each congressperson voted on each bill (domain of vote attribute is {Yes, No, Abstain, Absent}).

Draw an ER diagram for the above application. State clearly any assumptions you make.

Examples taken from a variety of sources, e.g., California Polytechnic State University, the open source textbook Database Design,