Assignment $02$

You need to change table design and write the new table design in shorthand representations. No need to write SQL commands or table data.

Questions:

KimTay Pet Supplies

$1.$ Indicate the changes $($using the shorthand representation$)$ that you would need to make to the original KimTay Pet Supplies database design $($see Figure $2-1$ of textbook$)$ to support the following requirements. A customer is not necessarily represented by a single sales rep, but can be represented by several sales reps. When a customer places an order, the sales rep who gets the commission on the invoice must be in the collection of sales reps who represent the customer.

$($Hint: The CUSTOMER table no longer need a REP$\_$ID because one customer maybe represented by multiple sales reps. To record the representation relationship between a customer and sales rep, you need to create a new table including only the CUST$\_$ID and REP$\_$ID$.)$

$2.$ Indicate the changes $($using the shorthand representation$)$ that you would need to make to the original KimTay Pet Supplies database design to support the following requirements. There is no relationship between customers and sales reps. When a customer places an order, any sales rep can process the order and create the invoice. On the invoice, you need to identify both the customer placing the order and the sales rep responsible for the invoice.

$($Hint: REP$\_$ID is no longer needed in CUSTOMER table but in the INVOICES table.$)$

$3.$ Using your knowledge of KimTay Pet Supplies:

a$.$ write out the functional dependencies that exist in the following ITEM table.

b$.$ convert this table to an equivalent collection of tables that are in third normal form $(3$NF$).$

ITEM $($ITEM$\_$ID$,$ DESCRIPTION, ON$\_$HAND, CATEGORY, LOCATION, PRICE, $($INVOICE$\_$NUM, INVOICE$\_$DATE, CUST$\_$ID$,$ FIRST$\_$NAME, LAST$\_$NAME, QUANTITY, QUOTED$\_$PRICE$))$

$($Hint: a$)$ Write out the functional dependencies. b$)$ This question is very similar with the example of converting the STUDENT table from unnormalized to $3$NF in textbook Module $2$ Q&A $($page $42)$ and in our chapter $02$ slides. Read and understand the STUDENT table example to answer this question. No need to draw ER diagram.$)$