Test #2:  Data Modeling and Table Normalization

Take home test
(40 points and 10% of Final Grade)

Due date: June 3^rd, 2021 at 1255 (12:55 PM) via Sakai

Release date: June 2^nd, 2021 at 1300 (1 PM) on Sakai

Part 1: Data Modeling – 30 marks

INSTRUCTION

1. Create a Crow’s Foot ER diagram for each of the following business rules. The diagrams should clearly describe and identify each entity and show relationships. 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/ business rules.
2. 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.
3. Simplify M: N relationships, if any.
4. You may develop the diagram on paper and submit the scanned version or you may use any relevant software. If you use a software, make sure that the file you submit is openable in the windows environment. If you can, please submit a PDF file.

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).

 

1. The Convenient Rentals has various outlets (garage/offices) throughout the Niagara Region. 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. A manager is an employee as well. (3 Marks)

 

 

 

 

 

 

 

 

 

 

 

 

 

1. 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. (3 Marks)

 

 

 

 

 

 

 

 

 

 

 

1. 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. (2 Marks)

 

 

 

 

 

 

 

 

 

 

 

1. 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. (3 Marks)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

1. 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. (6 Marks)

 

 

 

 

 

 

 

 

1. 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. (6 Marks)

 

 

 

 

 

 

 

1. 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. (3 Marks)

 

 

 

 

 

 

1. Represent the complete set of requirements in one ER diagram. (4 Marks)

 

 

 

 

 

 

 

 

 

 

 

Part 2: Normalization – 10 marks

• Explain the purpose of data normalization and describe the main steps in the normalization process. (3 Marks)

 

 

 

 

 

 

 

 

 

• The table shown below displays the details of the roles played by actors/actresses in films.

 

filmNo	fTitle	dirNo	director	actorNo	aName	role	timeOnScreen
F1100	Happy Days	D101	Jim Alan	A1020	Sheila Toner	Jean Simson	15.45
		D101	Jim Alan	A1222	Peter Watt	Tom Kinder	25.38
		D101	Jim Alan	A1020	Sheila Toner	Silvia Simpson	22.56
F1109	Snake Bite	D076	Sue Ramsay	A1567	Steven McDonald	Tim Rosey	19.56
		D076	Sue Ramsay	A1222	Peter Watt	Archie Bold	10.44

 

1. Describe why the table shown above is not in first normal form (1NF). (1 Mark)

 

 

 

 

 

 

 

 

1. Identify the functional dependencies represented by the table shown above. State any assumptions you make about the data shown in this table (if necessary). (2 Marks)

 

 

 

 

 

1. Using the functional dependencies identified in part (b), describe and illustrate the process of normalization by converting the table shown above to the third normal form. Identify the primary and foreign keys properly. (4 Marks)