Premiere Products Exercise Chapter 4 Solution Correct Answers

Preview (2 of 6 Pages)

100%

Purchase to unlock

Loading page image...

Premiere Products Exercise Chapter4Solution Correct AnswersUsing the week4 database file located in the course materials section of the onlineclassroom to complete the Alexamara Maria Group Case on page 146. Complete 1, 2, 3, 4,and 6, make sure that you provide "SQL statements"1. Create a view named LargeSlip using the data in the MarinaNum, SlipNum, RentalFee, BoatName, andOwnerNum columns in the MarinaSlip table for those slips with lengths of 40 feet. Display the data in theview.2. Create a view named InitialService using the slip ID, category number, category description, and estimatedhoursfor every service request for which the spent hours are zero. Display the data in the view.3. Create a view named TypesOfBoats using the boat type and a count of all boatsof each type. Display thedatain 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 BoatIndex on the BoatName field in the MarinaSlip table.b. Create an index named BoatIndex2 on the OwnerNum field in the MarinaSlip table.c. Create an index named BoatIndex3 on the Length and BoatName fields in the MarinaSlip table and listthelengths in descending order.5. Drop the BoatIndex3 index.6. Assume the MarinaSlip table has been created but there are no integrity constraints.Create the necessaryintegrityconstraints so the rental fee must be less than $5,000 and the slip length must be 25, 30, or 40.7. Ensure that the following are foreign keys (that is, specify referential integrity) in the Alexamara database.a. MarinaNum is a foreign key in the MarinaSlip table.b. OwnerNum is a foreign key in the MarinaSlip table.c. CategoryNum is a foreign key in the ServiceRequest table.d. SlipID is a foreign key in the SerivceRequest table.8. Add to the MarinaSlip table a new character field named FeePaid that is one character in length. On allrecords,change the value for the FeePaid field to Y.9. Change the FeePaid field in the MarinaSlip table to N for the slip whose slip ID is 4.10. Change the length of the BoatName field in the MarinaSlip table to 60.11. Write a stored procedure that will change the rental fee of a slip with a given slip ID. How would you usethis storedprocedure to change the rental fee of slip ID 3 to 3,700.00?12. Assume the Owner table contains a column called TotalRental that represents thetotal rental fee for allslipsrented by that owner. Write the code for the following triggers following the style shown in the text.a. When inserting a row in the MarinaSlip table, add the rental fee to the total rental for the appropriateowner.b. When updating a row in the MarinaSlip table, add the difference between the new rental fee and the oldrental fee to the total rental for the appropriate owner.c. When deleting a row in the MarinaSlip table, subtract the rental fee from the total rental for theappropriateowner.

Loading page image...

Preview Mode

This document has 6 pages. Sign in to access the full document!

Report

Study Now!

Document Details

Related Documents

DevOps and Its Importance Study Notes

Veeam Certified Engineer VMCE-V12

Modules 1 - 3 Basic Network Connectivity and Commu

Notes CCNA

CCNA 200-301 Portable Command Guide 5th Edition 20

CCNA 200-301 Portable Command Guide 5th Edition 22

CCNA 200-301 Portable Command Guide 5th Edition 36

Foundations of Novell Networking Section 26

Modulo 7 DHCPv4

Build and troubleshooting Integration Challenge

Company

Explore

Study Tools