A. In the SQL Server Management Studio folder structure in your My Documents folder, creat...

A. In the SQL Server Management Studio folder structure in your My Documents folder, create a folder named DBP-e12-MI-CH10-Database in the Projects 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. Using SQL Server 2008 R2 and the Microsoft SQL Server Management Studio, create a database named MI-CH10.

---

C. Answer questions A through M for Morgan Importing at the end of Chapter 7, page 309–312 if you have not already done so. If you have already answered the Chapter 7 questions, reuse your answers and apply them to the MI-CH10 database. Use the SQL Server data types in your answer, and store your answers in *.sql scripts, as appropriate.

---

D. Using SQL Server 2008 R2 and the Microsoft SQL Server Management Studio, execute all of the SQL statements that you created in your answers to A through N for Morgan Importing at the end of Chapter 7.

---

E. Assume that the relationship between SHIPMENT and SHIPMENT_ITEM is M-M. Design triggers to enforce this relationship. Use Figure 1 and the discussion of that figure as an example, but assume that Morgan does allow SHIPMENTs and their related SHIPMENT_ITEM rows to be deleted. Use the deletion strategy shown in Figures 2 and 3 for this case.

Figure Actions to Enforce Minimum Cardinality for the WORK-to- TRANS Relationship

Figure 2 Trigger Code to Delete All But Last Child

Figure 3 Trigger Code to Delete Last Child and Parent When Necessary

---

F. Write and test the triggers you designed in part E.

---

G. Create a SQL Server login named MI-User, with a password of MI-User+password. Create a MI-CH10 database user named MI-CH10-Database-User, which is linked to the MI-User login. Assign MI-CH10-Database-User db_owner permissions to the MI-CH10 database.