In the following exercises, you will use the data in the TAL Distributors database shown in Figure

Question:

In the following exercises, you will use the data in the TAL Distributors database shown in Figure 2-1 in Chapter 2. (If you use a computer to complete these exercises, use a copy of the original TAL Distributors database so your data will not reflect the changes you made in Chapter 3.) If you have access to a DBMS, use the DBMS to perform the tasks and explain the steps you used in the process. If not, explain how you would use SQL to obtain the desired results. Check with your instructor if you are uncertain about which approach to take.
1. Create the TopLevelCust view described in Review Question 2. Display the data in the view.
2. Create the ItemOrder view described in Review Question 3. Display the data in the view.
3. Create a view named OrdTot. It consists of the order number and order total for each order currently on file. (The order total is the sum of the number ordered multiplied by the quoted price on each order line for each order.) Display the data in the view.
4. Create the following indexes. If it is necessary to name the index in your DBMS, use the indicated name.
a. Create an index named ItemIndex1 on the ItemNum field in the OrderLine table.
b. Create an index named ItemIndex2 on the Storehouse field in the Item table.
c. Create an index named ItemIndex3 on the Storehouse and Category fields in the Item table.
d. Create an index named ItemIndex4 on the Storehouse and OnHand fields in the Item table and list units on hand in descending order.
5. Drop the ItemIndex3 index from the Item table.
6. Assume the Item table has been created, but there are no integrity constraints. Create the necessary integrity constraint to ensure that the only allowable values for the Category field are PZL, GME, and TOY. Ensure that the ItemNum field is the primary key and that the ItemNum field in the OrderLine table is a foreign key that must match the primary key of the Item table.
7. Add a field named Allocation to the Item table. The allocation is a number representing the number of units of each item that have been allocated to each customer. Set all Allocation values to zero. Calculate the number of units of item number KD34 currently on order. Change the Allocation value for item number KD34 to this number. Display all the data in the Item table.
8. Increase the length of the Storehouse field in the Item table to two characters. Change the Storehouse number for Storehouse 1 to 1a. Display all the data in the Item table.
9. Delete the Allocation field from the Item table. Display all the data in the Item table.
10. What command would you use to delete the Item table from the TAL Distributors database? (Do not delete the Item table.)
11. If you are using Access 2013, create the following data macros.
a. Create a data macro associated with the After Insert event for the Customer table to add the customer€™s balance multiplied by the rep€™s commission rate to the commission for the corresponding rep when adding a customer. Test the data macro by adding a customer and ensuring that the corresponding rep€™s commission is updated correctly.
b. Create a data macro associated with the After Update event for the Customer table to add the difference between the new balance and the old balance multiplied by the rep€™s commission rate to the commission for the corresponding rep when updating a customer. Test the data macro by changing a customer€™s balance and ensuring that the corresponding rep€™s commission is updated correctly.

c. Create a data macro associated with the After Delete event for the Customer table to subtract the balance multiplied by the rep€™s commission rate from the commission for the corresponding rep when deleting a customer. Test the data macro by deleting a customer and ensuring that the corresponding rep€™s commission is updated correctly.
12. Using Access 2013, an employee at TAL Distributors tried to delete item number MT03 from the Item table and received the following error message: €œThe record cannot be deleted or changed because table €˜OrderLine€™ includes related records.€ Why did the employee receive this error message? What change is needed in the database to allow the deletion of records from the Item table?
13. TAL Distributors has decided to include wood carvings in its product line and has assigned these items to the category CRV. What change is needed in the database to add items in category CRV to the Item table? Would you add any integrity constraints to the Storehouse and Price fields in the Item table? Why or why not?

Figure 2-1