Normalization Problem

The following relation schema is used to store information about the customer of a car service center. Customer (Customer_ID, CName, CAddr, Phone, Customer_Visit_Date, Service_Advisor_ID, SAName, SAPhone, No of Cars_Serviced, Service_Date) A customer is identified with a unique Customer_ID, he can have only one phone number, and he may visit the service center many times. There are more service advisors and each service has many cars on given service date. Any service advisors can be reached at only one phone number. Create new relation by illustrating the process of normalizing the data shown in figure above to first (INF), second (2NF), third (3NF). At each stage show the primary key and any foreign keys of each relation and state assumptions that you make about any of the relationships between the columns of data. (16 marks) The following relation schema is used to store information about the customer of a car service center. Customer (Customer_ID, CName, CAddr, Phone, Customer_Visit_Date, Service_Advisor_ID, SAName, SAPhone, No of Cars_Serviced, Service_Date) A customer is identified with a unique Customer_ID, he can have only one phone number, and he may visit the service center many times. There are more service advisors and each service has many cars on given service date. Any service advisors can be reached at only one phone number. Create new relation by illustrating the process of normalizing the data shown in figure above to first (INF), second (2NF), third (3NF). At each stage show the primary key and any foreign keys of each relation and state assumptions that you make about any of the relationships between the columns of data. (16 marks)