2013 Feb 27: corrected availability of the dia program.

Entity-Relationship Modeling

Due date: See Oncourse Modules and Assignments.

40 points (up to 50 points with extra credit)

Learning Objectives

1. Ability to create and use E-R diagrams for database design.
2. Ability to reduce the E-R design to a relational schema.

Instructions

1 Entity-Relationship Model

Design an entity-relationship model for an automobile insurance database. Each customer of the insurance company may own one or more cars, and each car may be involved in zero or more accidents which are recorded in the database.

• Use appropriate software to make an E-R diagram for the database.
  • Hint: the dia program is good for this; it is available in the Linux Lab (TR 102) and on merlin.iue.edu. See below for details. Other programs, including xfig and Microsoft Visio, can be used.
• Be sure to show primary keys by underlining.
• Be sure to show mapping cardinalities (one to many, etc.) and participation constraints (partial or total participation).

Sample Data

The database should allow at least for data such as the following:

James Rowe was born January 21, 1951. He lives at 123 Green Street, Richmond, Indiana. He owns two cars: (a) Buick Century, 1998, VIN 123456789, value $10,000. (b) Ford Taurus, 2002, VIN 654321987, value $15,000.

Vehicle (a) has had three accidents:

1. On December 12, 1999, at the intersection of Chester Boulevard and A Street, Richmond, Indiana, cost $500.
2. On April 19, 2000, at Emerson and 30th Streets, Indianapolis, Indiana, cost $875.
3. On June 25, 2005, at 4th and Ludlow Streets, Dayton, Ohio, cost $1350.

Vehicle (b) has had no accidents.

If you have knowledge of the insurance business, or if you just want to spice it up, you can plan your database to accommodate other kinds of relevant data. If you justify these enhancements, you may earn some extra credit.

2 Relational Model

Reduce the E-R model to tables, as described in the Coursepack, Section 6.8. Show the schema of each table in the form of

tablename(attr1, attr2, attr3, attr4, …)

underlining the primary key attributes.

Using Dia

• Select the ER tools.
• Double click on an object to change its name or other properties.
• In ER tools, use the participation tool (looks like a double line ||) to connect relationship sets to their participating entity sets.
• Use lines to connect entity sets and their attributes.
• Snap connections to the center of objects (yellow flash) or to their external connection ports (red flash).
• Export the diagram to an image file (e.g., SVG, PDF, or PNG) that you can import into your word processor (or whatever text processing tool you will use for the reduction to tables).

What to Turn in

Combine your ER diagram and table schemas into a single document. If you needed to make any assumptions to derive your model, or if you wish to include additional data, explain these decisions in your report. (A plausible explanation is required for the extra credit points.)

Save the report as a PDF file and turn it in through Oncourse Assignments.

Grading Criteria

• (20 points) E-R diagram.
  • Appropriate relationship sets, entity sets, and attributes
  • Primary keys identified
  • Mapping cardinalities shown
  • Participation constraints shown
• (20 points) List of table schemata.
  • All tables shown, with all attributes, primary keys underlined.
• Base Total: 40 points
• (up to 10 points extra) Enhancements, with justification