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).

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 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.

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.

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}).