Part 1: Data Modeling

INSTRUCTION

Create a Crow's Foot ER diagram for each business rule. The diagrams should clearly describe and identify each entity and show relationships. Show all cardinality, primary keys, and foreign keys. Use verbs to name and clarify relationships. Show only the attributes contained explicitly in the case/ business rules.

You should also make and document any reasonable assumptions where you feel the case is vague, ambiguous, or missing data necessary to meet the requirements.

Simplify M: N relationships, if any.

You may develop the diagram on paper.

Steps to do:

The requirements collection and analysis phase of the database design process has provided the following data requirements for a company called Convenient Rentals, which rents out vehicles (cars and vans).

a) Convenient Rentals has various outlets (garages/offices) throughout Niagara. Each outlet has a number, address, phone number, fax number, and a manager who supervises the garage operation and offices at each site. A manager is also an employee; a manager may manage the same outlet more than once at different times. See question b for data maintained on each employee. (6 Marks)

b) The company maintains the following data on each staff: staff number, name (first and last name), home address, home phone number, date of birth (DOB), sex, Social Insurance Number (SIN), date joined the company, job title, and salary. Each staff member is associated with a single outlet but may be moved to an alternative outlet as required, and that location information is maintained for each staff member. (6 Marks)

c) Each site is allocated a stock of vehicles for hire; however, individual vehicles may be moved between outlets as required. Only the current location for each vehicle is stored. The registration number uniquely identifies each vehicle for hire and is used when hiring a vehicle for a client. Data stored on the vehicles for hire include: the vehicle registration number, model, make, engine size, capacity, current mileage, daily hire rate, and the current location (outlet) of each vehicle. (3 Marks)

d) Clients may hire vehicles for various periods (minimum one day to maximum one year). Each individual hire agreement between a client and the Company is uniquely identified using a hire number. The data stored on a hire agreement includes the hire number, the client's number, name, address, and phone number, date the client started the hire period, date the client wishes to terminate the hire period, the vehicle registration number, model and make, the mileage before and after the hire period. The Company has personal clients. The data stored on personal clients includes the client number, name (first and last name), home address, phone number, date of birth, and driving licence number. The client number uniquely identifies each client and the information stored relates to all clients who have hired in the past and those currently hiring a vehicle. (6 Marks)