You own a services company and would like to start keeping better track your sales representatives and customers.
You want to keep track of the customers and the sales representatives. Only one sales representative can be assigned to each customer.
You would like to track the customer ID, the customer name, the customer address, the customer's contact name, the customer's contact number, the customer's credit limit, the customer's outstanding balance, and the sales representative assigned to the customer. Youwould like to track each sales representatives ID number, their name, their address, their contact number and their date of hire.
You only want to track one contact number and one address for the customer, and one contact number and one address for the sales representative.
The unnormalized table looks as follows: Customers (CustID, CustName, CustAddr, CustContactName, CustContactNumber, CustCreditLimit,CustBalance, SalesRepID, SalesRepName, SalesRepAddr, SalesRepContactNumber, SalesRepHireDate)
Part A
Using the normalization process and rules submit:
1 The 1NF table(s) - and an explanation of why each table(s), is in 1NF - using normal form definitions.
2 The 2NF table(s) - and an explanation of why each table(s), is in 2NF - using normal form definitions.
3 The 3NF table(s) - and an explanation of why each table(s), is in 3NF - using normal form definitions.
Use the table notation on page 34 to describe each table - DO NOT submit spreadsheet formatted tables or content.
4 The functional dependencies - use functional notation on page 162 to describe the functional dependencies. Only one set of functional dependencies should be submitted.
AND provide a narration explaining the choice of the primary key for each table.
You must use and refer to the Normalization rules used in our textbook. Failure to use these rules in the explanation will result in a minimal grade reduction of 15%. Other normalization approaches may be used to augment, but not replace the rules, in our text.
Do not expand the solution beyond the above boundaries and do not add any fields without an explanation of why you added that field.
Part B (worth 5% of the exercise grade)
You now realize that some customers have enough business to enable you to assign more than one sales
representative to a customer. You must expand the design to be able to accommodate more than one sales rep for each customer.
The unnormalized table now appears as follows:
Customers (CustID, CustName, CustAddr, CustContactName, CustContactNumber, CustCreditLimit,
CustBalance, (SalesRepID, SalesRepName, SalesRepAddr, SalesRepContactNumber, SalesRepHireDate))
Submit the 3NF table(s) needed for this new design AND explain the reasoning behind any changes to
this set of 3NF tables versus those found in Part A.
Contact the instructor with any questions.
Submit the assignment in a word document under the week's Assignment. Name your document Last
Name_Design (i.e. Smith_Design).