Q1. 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) to support the following requirements. A customer is not necessarily represented by a single sales rep, but they 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.

 

Q2. Open a new Microsoft® Word document and answer the following question.

• Save the file on your computer with your last name in the file name. (Example: Case_Exercise_1 _Jones.doc)
• Click the Choose File button to find and select your saved document.

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. Draw an E-R diagram for the new design.

Q3. Open a new Microsoft® Word document and answer the following question.

• Save the file on your computer with your last name in the file name. (Example: Case_Exercise_1 _Jones.doc)
• Click the Choose File button to find and select your saved document.

Indicate the changes (using the shorthand representation) that you would need to make to the original KimTay Pet Supplies database design in the event that the original Requirement 3 is changed as follows. For an item, store the item's ID, description, category, and price. In addition, for each location in which the item is located, store the value of the location, the description of the location, and the number of units of the item stored in the location. Draw an E-R diagram for the new design.

 

Q4. Open a new Microsoft® Word document and answer the following question.

• Save the file on your computer with your last name in the file name. (Example: Case_Exercise_1 _Jones.doc)
• Click the Choose File button to find and select your saved document.

Using your knowledge of KimTay Pet Supplies, determine the functional dependencies that exist in the following table. After determining the functional dependencies, convert this table to an equivalent collection of tables that are in third normal form.

Q5 Indicate the changes you need to make to the KimTay Pet Supplies database to support the following additional requirement. Each location has a manager who is identified by a manager ID, a manager first name, and a manager last name.

 I only need the output No Code!!! thanks so much. * The professor has not taught us any code for SQL.*

  

Transcribed Image Text:

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