[solved] Assignment 3 – Normalization

Assignment 3 – Normalization
Assignment 3 is based on the content of Chapter 4 of the text: Logical Database Design and the Relational Model.
Part A (50 points): HomeServices, Inc. is a provider of home appliance repair services. The company needs to automate their record keeping. The following business rules apply:
Each service is identified by a unique service invoice number. Each service is performed by a single employee on a single appliance for a single customer. An employee can perform more than one service per day. If an employee performs more than one service for the same customer on the same date, these services are recorded separately, each recorded in a separate invoice.
Appliance model number uniquely identifies the appliance and determines its type, brand and weight.
The service cost depends solely on the problem type.
An amateur database programmer has come up with the following schema for the database:
SERVICE (ServiceInvoiceNumber, ServiceDate, CustomerAddress, CustomerName,
ServiceCost, ProblemType, ServiceEmployeeName, ServiceEmployeePhone,
ApplianceBrand, ApplianceType, ApplianceModelNumber, ApplianceWeight)
Answer the following questions based on the business rules and the schema described above.
1. (10 points) List all functional dependencies present in this relation.
2. (5 points) What is an appropriate primary key for the SERVICE table?
3. (3 points) Describe a situation that would result in an insertion anomaly in the SERVICE relation.
4. (3 points) Describe a situation that would result in a deletion anomaly in the SERVICE relation.
5. (3 points) Describe a situation that would result in a modification anomaly in the SERVICE relation.
6. (3 points) List a transitive dependency in the SERVICE relation.
7. (4 points) Is SERVICE relation in second normal form? Justify your answer.
8. (4 points) Is SERVICE relation in third normal form? Justify your answer.
9. (15 points) Decompose the SERVICE relation to achieve the third normal form. Identify primary and foreign keys and referential integrity constraints.
 Part B on next page –
Assignment #3 (Individual) CS605 – Data Management & SQL, Summer 2020
Page 2 of 2
Part B (50 points). Consider a table with the columns: A, B, C, D, E, F, G, H, I, J and the following set of functional dependencies
A  I
H  J
(A, B)  C
(B, D)  E, F
(A, D)  G, H
What is the primary key for this table? (10 points)
Decompose the table into 2NF, then 3NF relations. (40 points)
Submit


Buy plagiarism free, original and professional custom paper online now at a cheaper price. Submit your order proudly with us



Essay Hope