IS344 DATABASE MANAGEMENT SYSTEMS

PURPOSE

The purpose of this assignment to help you apply your knowledge in entity relationship modeling

and advanced data modeling.

TASKS

Before starting, please go over the topics of the entity relationship model, developing an

ER diagram, and the extended entity relationship model.

In this assignment you will have 2 questions to complete.

Create a MS Word document (assignment document) to keep your answers.

For question 1 and 2, draw an entity relationship diagram. Please use MS Visio The

database model diagram template to draw it and copy it into your assignment document.

1. Given the following business scenario, create a Crow's Foot ERD using a specialization hierarchy if appropriate. (MS Visio - The database model diagram template) Granite Sales Company keeps information on employees and the departments that they work in. The department name, internal mailbox number, and office phone extension are kept for each department. A department may have many assigned employees, and each employee is assigned to only one department. All employees are assigned an employee number. This is kept along with the employee's name and address. Employees can be either salaried employees, hourly employees, or contract employees. For hourly employees, hourly wage and target weekly work hours are stored (e.g., the company may target 40 hours/week for some, 32 hours/week for others, and 20 hours/week for others). The yearly salary amount is recorded in the system for all salaried employees. For contract employees, their contract's beginning date and end dates are stored along with the billing rate for their hours. 2. (Use MS Visio - The database model diagram template): A car insurance company has a set of customers who own one or more cars. The car insurance company keeps a record of claims. A customer may have many claims. Therefore, a claim includes many customers (in case of a car accident, many customers may involve). For customers, we store driver's license number, the state in which driver's license was given, name, sex, address, phone number, distance to work, number of years he or she has been driving. Cars have VIN, tag, make, year, model, special designation (i.e., Ix, is, etc.), status (owned/leased). We also store whether the car has an alarm system, airbags, anti-lock brakes. We have a type (traffic violation or accident) and severity (number of points) for claims. When a claim occurs, the company also keeps the date of the claim, whether driver's license status (suspended/revoked), court location and the court date, and the final decision. They are kept as a driving record