Normalisation (20 marks) 

The following question is based upon a Car-Service relation which records the details of transactions occurring in an Automotive Mechanic and Repair business. You may assume the data are representative. 

 

CustName	CustDOB	ServiceDATE	CarRego	CarMade	CarModel	Service FEE	Staff NAME	Staff speciality
Lois Price	8/09/1998	19/5/2023	1y568	Toyota	Camry	199	Ali	Service tech
Justin Thyme	10/10/2000	8/4/2022	1y576	Honda	CRV	400	Chip	Auto body
Stan Dupp	10/10/2000	9/5/2023	1t567	Toyota	Camry	199	Ali	General
Jack Pott	29/03/1981	2/10/2022	1t158	Ford	Focus	Fifty	Holly	Service tech
Lois Price	8/09/1998	1/8/2022	1y458	Tesla	Model X	199	Ali	Auto body
Ina Minat	03/01/1991	15/9/2022	1i132	Holden	Astra	190	Chip	Auto body
Mike Rafone	30/01/2004	15/9/2022	1l884	BMW	Z4	One twenty	Ali	99999
Ina Minat	03/01/1991	10/8/2019	1i123	Black car	Astra	269	Ali	General

 

You have been asked to design a relational database for this system. You know that there are problems with the current design and that it will need to be modified in order to work effectively. 

 

1. What are the specific problems associated with the current design and why do they arise?
2. How would you change the current design and how does your new design address the problems you have identified with the current design.

In order to receive high marks for this question, you will need to demonstrate an understanding of the theories discussed in Topics 1, 2 and 3, how they apply to this problem, and justify the changes you are making to the system. Simply providing the amended design (even if it is correct) will only attract a small percentage of the marks for this question.