CPSC 343 Database Theory and Practice Fall 2017

# Homework 1: ER Modeling

## due Fri Sep 15 in class

For each of the following, develop an ER or EER diagram for the situation described. Represent the situation as accurately as possible, including participation, cardinality, and key constraints as appropriate. Also identify any attributes which are required (i.e. should be NOT NULL). Note any constraints present that you did not capture in the diagram along with a reason why they were omitted. If necessary specifics are lacking in the description of the situation, make reasonable assumptions and state those assumptions.

Draw your diagrams by hand (neatly) or use a program such as dia (choose the "ER" symbol set; for EER elements use the attribute symbol for the small circle).

1. Design a database for recording information about the activities (specifically votes) taken in the US House of Representatives, including:

• Each Congress has a unique number (such as 115) and range of dates when it meets (such as January 3, 2017 to January 3, 2019).

• Each US state has a name and region (Northeast, Midwest, Southeast, Southwest, West).

• Each Representative is described by his or her name, represents a particular district in their state, and belongs to a political party (Republican, Democrat, Independent, Other). Note that a Representative's district and/or party affiliation may be different for different Congresses, but assume that it won't change during a single Congress.

• A bill has a unique bill number, a title, at least one sponsor, the Congress it was introduced in, and the date on which it was introduced.

• For each bill that has been voted on, the date of the vote, the number of "ayes" and "nos", and whether the bill passed or failed is recorded. Assume there can only be one vote on a given bill. In addition, how each Representative voted on the bill (yes, no, abstain, absent) is also recorded.

2. Design a database to manage conference papers. Researchers interested in presenting at a conference submit their research papers for consideration. Reviews by reviewers are recorded for use in the paper selection process. The database system caters primarily to reviewers who record answers to evaluation questions for each paper they review and make recommendations regarding whether to accept or reject the paper. The data requirements are summarized as follows:

• Authors of papers are uniquely identified by email id. First and last names are also recorded.

• Each paper is assigned a unique identifier by the system and is described by a title, abstract, and the name of the electronic file containing the paper.

• A paper may have multiple authors, but one of the authors is designated as the contact author.

• Reviewers of papers are uniquely identified by email address. Each reviewer's first name, last name, phone number, affiliation, and topics of interest are also recorded.

• Each paper is assigned between two and four reviewers. A reviewer rates each paper assigned to him or her on a scale of 1 to 10 in four categories: technical merit, readability, originality, and relevance to the conference. Finally, each reviewer provides an overall recommendation regarding each paper.

• Each review contains two types of written comments: one to be seen by the review committee only and the other as feedback to the author(s).

3. Design a database to keep track of information for an art museum:

• The museum has a collection of art objects, each of which has a unique ID number, an artist (if known), the year it was created (if known), a title, and a description. In addition, there is a country or culture of origin (Italian, Egyptian, American, Indian, etc) and time period (Renaissance, Modern, Ancient, etc).

• For artists, there is a name, date of birth (if known), date of death (if not living), country, period (Renaissance, Modern, Ancient, etc), main style, and description. The name is assumed to be unique.

• An art object is either a painting, a sculpture, a statue, or "other" (for objects that don't fall into one of the other three categories).

• A painting has a type (oil, watercolor, etc), material (paper, canvas, wood, etc), and style (modern, abstract, etc).
• A sculpture or statue has a material (wood, stone, etc), height, weight, and style.
• All other art objects have a type (print, photo, etc) and style.
• Art objects either belong to the permanent collection or are borrowed.

• For objects in the permanent collection, the date acquired, status (on display, on loan, or stored), and cost are recorded.
• For objects on loan, the museum that the object is loaned to and the loan period (start and end dates) are recorded. The museum is identified by a name; contact information (a name, an address, and a phone number) and a description are also recorded.
• For borrowed objects, the owner, date borrowed, and date returned (if applicable) are recorded. The owner of a borrowed object can be an individual or another museum. In both cases, there is a name (unique) and contact information (a name, an address, and a phone number). For a museum, there is also a description.
• Different exhibitions occur, each having a name, start date, and end date. Any object can be part of an exhibition.

4. An automobile repair shop needs to keep information about its operations. Customers initially bring their cars to the shop for an estimate of repairs. A mechanic looks at the car and estimates the cost and time required for the entire job. A job might include several repairs (e.g. replacing the left fender, painting the passenger door) and may include both body work and mechanical repairs. If the customer accepts the estimate, a job number is assigned and the customer's name and contact information; the car's license plate number, make, model, and year; and a list of the repairs needed are recorded. The customer then makes an appointment to bring in the car on a specified date. When the car is brought in for repairs, a technician and/or a mechanic is assigned to work on the car. (Body work can be done by a technician or a mechanic, but mechanical repairs require a licensed mechanic.) The shop keeps track of the charges for parts and labor as they accumulate. The time actually spent for each repair is recorded and used to calculate the cost of labor, using a fixed hourly rate. When the work is complete, the shop produces an itemized bill for the customer showing the part(s) required, the cost of each part, the total labor hours and cost, and the name of the technician/mechanic who completed that repair for each of the repairs in the job.

 last updated: --Tue Sep 5 00:52:43 EDT 2017-- page owned by: bridgeman@hws.edu