Developing a Data Model

This is a group assignment. Groups must be between 4 and five students. Choose a team member for uploading the files. For this assignment you will be responsible to creating a logical data model. Your submission will be marked based on the accuracy, feature, and functions of the resulting ER diagram. Use the following convention to name the files that you submit (GroupName_DataModel). You can have any name for your group. Submission should be done before the deadline using the DataModelAsst link on Sakai. You are required to submit one PDF file per group through the course website. Verify that your assignment file is submitted properly. Late submissions and wrong files will automatically receive a grade of zero.

For this group assignment, please form a group and email the section number, name and ID number of each group member to Jiqing (Jessie) Bian (at JBian@brocku.ca) with other group members copied by October 20, 2020. Those of you who have not been part of any group by this date will be assigned to a group randomly. A group must have between 4 and five students. Please read the course outline for additional information about group composition.

Using the following narrative, design and document a logical data model – an ERD. You may develop the diagram on paper and submit the scanned version. You may also use any data modeling software to facilitate the model development process. Erwin, for example, is a data modeling software, which you can download the trial version from here https://erwin.com/products/erwin-data-modeler/. If you use a modeling software, please import the model into a word file and submit a PDF file.

You should also make and document any reasonable assumptions where you feel the case is vague, ambiguous or missing any data necessary to meet the requirements. However, you cannot ignore or overrule the requirements that are explicitly stated. Show all cardinality, primary keys and foreign keys. Use verbs to clarify your relationships. Show only the attributes that are contained explicitly in the case.

ITIS 2P51 FALL 2020

The narrative: Easy Rentals

The requirements collection and analysis phase of the database design process has provided the following data requirements for a company called Easy Rentals, which rents out vehicles (cars and vans). The Company has various outlets (garage/offices) throughout St. Catharines. Each outlet has a number, address, phone number, fax number, and a manager who supervises the operation of the garage and offices at each site.

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 to a client.

Clients may hire vehicles for various periods of time (minimum 1 day to maximum 1 year). Each individual hire agreement between a client and the Company is uniquely identified using a hire number. Information 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.

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. After each hire a member of staff checks the vehicle and notes any fault(s). Fault report information on each vehicle is stored, which records the name of the member of staff responsible for the check, date checked, whether fault(s) where found (yes or no), the vehicle registration number, model, make and the current mileage.

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.

Information is stored on the staff based at various outlets including: 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, although only the current location for each member of staff is stored.

The Company retains data on dependants for those staff members who have dependents. The data stored on dependents includes dependent ID, name of the dependent, relationship to the staff, sex and date of birth.

ITIS 2P51

FALL 2020

Marking Scheme

Mark Entities 14

Primary keys 7 Relationships 7 Attributes 6 Use of verbs for relationships 2 Presentation of the overall model 4

T otal 40

Obtained