You need to use SQL Server at MyEducator with Credentials set as your Student Account through the Advanced Query editor to do this assignment. Be sure to format and name columns as appropriate.

Save your work as instructed below and turn in:

1. A printout of each query (4-21), both your input and the results, where needed. Staple the above printouts in order and handwrite #4-21 on each (if you click print on the browser and change the layout to portrait, it should give you what you need to printout).

2. Submit one Notepad file (containing all queries 4-21 only through D2L (make sure it runs).

3. Run the script file to create all the tables and insert all the records for Alexamara Marina Group (attached to this assignment in D2L. There is another drop table script file attached if you need it.) Do not include this information in the Notepad file you submit.

4. Modify Marina_Slip to have foreign keys. Marina_Num should reference Marina and Owner_Num should reference Owner. For both, on update cascade and on delete no action.

5. Modify Service_Request to have foreign keys. Category_Num should reference Service_Category and Slip_ID should reference Marina_Slip. On update cascade and on delete no action.

6. List the slip ID, boat name, owner name, category description, and number of hours estimated on each service request for Electrical systems.

7. How many Sprite boats are stored at each marina? List the Marina number and number of Sprite boats.

8. For every service request category, list the category description, the number of requests for that category, the average estimated hours, and the average spent hours (only include spent hours in the average when the number of spent hours is not 0) be sure to format appropriately.

9. List the names for all owners, in alphabetical order, who have a boat in a slip that is larger than the average slip.

10. List the marina name, length, and calculate the total rental fees Alexamara receives each year based on the length of the slip for each marina.

Do numbers 11-21 below. After each change is made, execute an appropriate query to show that the change was made correctly. In other words, for number 11 you should have the create and alter table commands, and for number 12, have the insert command as well as the select command to show the records.

11. Create a LargeSlip table with the structure shown below. Be sure to specify what happens if a corresponding record with a PK is updated or deleted.

Column Name	Type	Length	Dec. Places	Nulls?	Description
MarinaNum	Char	4		No	Marina No PK, FK-Marina
SlipNum	Char	4		No	Slip No in the marina - PK
RentalFee	Dec	8	2		Annual slip rental fee
BoatName	Char	50			Name of boat currently in slip
OwnerNum	Char	4			Number of boat owner renting slip, FK - Owner

12. Insert into the LargeSlip table the record information for all boat/owners whose length is 40 feet.

13. Decrease the rental fee in LargeSlip by one percent for any slip whose fee is greater than $3000.

14. Insert the information for the following boat into LargeSlip:

1, A4, $3900, Bilmore, FE82

15. Delete all slips in the LargeSlip table for which the owner number is TR72.

16. The boat in Marina 1, slip A1 is changing its name. Change the boat name in LargeSlip to null.

17. Add a new column called Charter that is one character to LargeSlip and set all the values to N.

Drop the LargeSlip table.

Create a view named LargeSlipView (same info as 11 & 12 above)

List all the information from LargeSlipView for all rental fees greater than $3500.

Delete the LargeSlipView

Should you/can you update tables through the LargeSlipView? Why or why not? (You may answer this in the comments when you upload in D2L, rather