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:

  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:

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

  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.

Valid HTML 4.01!