Week4 - a. Create an index named BoatIndex on the BoatName...

Info iconThis preview shows page 1. Sign up to view the full content.

View Full Document Right Arrow Icon
Alexamara Marina Group Case 1. Create a view named LargeSlip using the data in the MarinaNum, SlipNum, RentalFee, BoatName, and OwnerNum columns in the MarinaSlip table for those slips with lengths of 40 feet. Display the data in the view. CREATEVIEW LargeSlip AS SELECT MarinaNum, SlipNum, RentalFee, BoatName, OwnerNum FROM MarinaSlip WHERE length= 40 ; 2. Create a view named InitialService using the slip ID, category number, category description, and estimated hours for every service request for which the spent hours are zero. Display the data in the view. CREATEVIEW InitialService AS SELECT SlipID, CategoryNum, Description, EstHours FROM ServiceRequest WHERE SpentHours=0 ; 3. Create a view named TypesOfBoats using the boat type and a count of all boats of each type. Display the data in the view. CREATEVIEW TypesOfBoats AS SELECT Count (*) FROM BoatType ; 4. Create the following indexes. If it is necessary to name the index in your DBMS, use the indicated name.
Background image of page 1
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: a. Create an index named BoatIndex on the BoatName field in the MarinaSlip table. CREATE INDEX BoatIndex ON MarinaSlip(BoatName) ; b. Create an index named BoatIndex2 on the OwnerNum field in the MarinaSlip table. CREATE INDEX BoatIndex2 ON MarinaSlip(OwnerNum) ; c. Create an index named BoatIndex3 on the Length and BoatName fields in the MarinaSlip table and list the lengths in descending order. CREATE INDEX BoatIndex3 ON MarinaSlip(Length, BoatName DESC) ; 6. Assume the MarinaSlip table has been created but there are no integrity constraints. Create the necessary integrity constraints so the rental fee must be less than $5,000 and the slip length must be 25, 30, or 40. PRIMARY KEY (RentalFee, Length) SELECT RentalFee, Length FROM MarinaSlip WHERE RentalFee<5000, Length =25, 30, 40 ;...
View Full Document

Ask a homework question - tutors are online