Database Processing: Fundamentals, Design, and Implementation 13th Edition by David Kroenke, David Auer

Exercise 31

Write SQL statements to accomplish the following tasks, and save your work as *.sql scripts. Run your *.sql scripts in MySQL using the MySQL Workbench.
A. In the SQL Workbench folder structure in your MyDocuments folder, create a folder named DBP-e12-VRG-CH10B-PQ-Database in the Schemas folder. Use this folder to save and store *.sql scripts containing the SQL statements that you are asked to create in the remaining questions in this section.
B. Create a MySQL database (schema) named VRG-CH10B-PQ.
C. In the VRG-CH10C-PQ database, create tables in Figure 10C-19, but do not create the NationalityValues constraint.
D. Populate your database with the sample data from Figure 10C-32.
E. Write a stored procedure to add a new artist into the ARTIST table. Research data for two actual artists, and add that data to the ARTIST table using your stored procedure in the MySQL Workbench.
F. Write a stored procedure that adds a new artist to the ARTIST table and a work by that artist to the WORK table. Research data for two actual artists, and add that data to the ARTIST and WORK tables using your stored procedure in the MySQL Workbench.
G. Write a stored procedure to update customer phone data. Assume that your stored procedure receives LastName, FirstName, priorAreaCode, newAreaCode, priorPhoneNumber, and newPhoneNumber. Your procedure should first ensure that there is only one customer with the values of (LastName, FirstName, priorAreaCode, priorPhoneNumber). If not, produce an error message and quit. Otherwise, update the customer data with the new phone number data.
H. Create a table named ALLOWED_NATIONALITY with one column, called Nation. Place the values of all nationalities currently in the View Ridge database into the table. If possible, write a trigger that will check to determine whether a new or updated value of Nationality resides in this table, and, if not, write an error message and roll back the insert or change. Use the MySQL Workbench to demonstrate that your trigger works. If it is not possible to write a MySQL trigger, explain why and write an equivalent stored procedure.
I. Create the view named WorkAndTransView as described in this chapter, which has all of the data from the WORK and TRANS tables except for the surrogate keys. Write a stored procedure in lieu of an INSTEAD OF INSERT trigger on this view that will create a new row in both WORK and TRANS. Use the MySQL Workbench to demonstrate that your stored procedure works.
J. Create a user named VRG-CH10C-User, with a password of VRG-CH10C-User+password. Assign VRG-CH10C-User all schema privileges except GRANT to the VRG-CH10C-PQ database.