LOGISTICS AND LATE POLICY REMINDER

• Turn-in: Please turn in your written work before the beginning of the class due date. Please put your name and preferred email address on every page that you turn in - you'd be surprised how many times assignments mysteriously come apart. Please remember that written work will not be accepted. All submissions are to be made  electronically via the MOODLE.
• Legibility: Please use a word processor or type your homework assignments. E/R diagrams should be done using a suitable ER diagramming tool or a graphics editor..
• Deadline: The on-time deadline for all students is in class on the due date.
• Late policy: All written work is to be turned in during class on the day that it is due. All assignments turned in late will be penalized at the rate of 10% per day for each unexcused late day.

THE ASSIGNMENT

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

   Specify an entity-relationship diagram for this database. Don't forget to underline key attributes and include arrowheads indicating the multiplicity of relationships. Note that there is no single right answer to this question, although some answers may be better than others.

2. The following E/R diagram is an attempt to design a database in which a store keeps a permanent record of customers (identified by social-security numbers) and the items they buy (identified by a unique item ID assigned by the store).
   

   However, there is a problem with this design, related to our ability to recover the history of, say, orders by a particular customer for a particular item.

   (a)

   Explain what the problem is and propose a solution.

   (b)

   Draw a revised E/R diagram that implements your solution.

3. Consider an E/R diagram such as the one below, with a ternary relationship and arrows entering two of the related entity sets, A and B.

    / \
    |---| / \ |---|
    | A |<------------- ------------->| B |
    |---| \ / |---|
    \ /
    |
    |
    |---|
    | C |
    |---|
   

   There is a correct interpretation, which is that, given A and C entities, there is at most one related B entity, and, given B and C entities, there is at most one related A entity. There is also an incorrect interpretation "given a C entity, there is at most one related A entity and at most one related B entity."

   (a)

   Give an example of a relationship set that meets the correct interpretation of the diagram but does not meet the incorrect interpretation. You may use abstract entities, like a1, a2, b1, and so on, or you may give the entity sets a real interpretation (e.g., movies), and use actual values in the triples of your relationship set.

   (b)

   Does every relationship set that satisfies the incorrect interpretation also satisfy the correct interpretation? Justify your answer.

   (c)

   On the other hand, if the incorrect interpretation is what we want to say, there is another E/R diagram that says it. Draw this diagram.

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

   Draw an E/R diagram that represents, as best you can, the information described above. Do not forget to indicate keys, possible weak entity sets, and arrows on relationships where appropriate.