Databases Reference
In-Depth Information
b.
Create an index named BookIndex2 on the Type field in the Book table.
c.
Create an index named BookIndex3 on the Type and Price fields in the Book table and list the prices in
descending order.
5.
Drop the BookIndex3 index.
6.
Specify the integrity constraint that the price of any book must be less than $90.
146
7.
Ensure that the following are foreign keys (that is, specify referential integrity) within the Henry Books database.
a.
PublisherCode is a foreign key in the Book table.
b.
BranchNum is a foreign key in the Inventory table.
c.
AuthorNum is a foreign key in the Wrote table.
8.
Add to the Book table a new character field named Classic that is one character in length.
9.
Change the Classic field in the Book table to Y for the book titled
The Grapes of Wrath
.
10.
Change the length of the Title field in the Book table to 60.
11.
What command would delete the Books table from the Henry Books database? (Do not delete the Book table.)
12.
Write a stored procedure that will change the price of a book with a given book code. How would you use this
stored procedure to change the price of book 0189 to $8.49?
13.
Assume the Book table contains a column called TotalOnHand that represents the total units on hand in all
branches for that book. Following the style shown in the text, write the code for the following triggers:
a.
When inserting a row in the Inventory table, add the OnHand value to the TotalOnHand value for the appro-
priate book.
b.
When updating a row in the Inventory table, add the difference between the new OnHand value and the old
OnHand value to the TotalOnHand value for the appropriate book.
c.
When deleting a row in the Inventory table, subtract the OnHand value from the TotalOnHand value for the
appropriate book.
In the following exercises, you will use the data in the Alexamara Marina Group database shown in Figures 1-20
through 1-24. (If you use a computer to complete these exercises, use a copy of the original Alexamara database so
your data will not reflect the changes you made in Chapter 3.) If you have access to a DBMS, use the DBMS to per-
form the tasks and explain the steps you used in the process. If not, explain how you would use SQL to obtain the
desired results. Check with your instructor if you are not certain about which approach to take.
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.
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.
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.
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 list the
lengths in descending order.
5.
Drop the BoatIndex3 index.
6.
Assume the MarinaSlip table has been created but there are no integrity constraints. Create the necessary integ-
rity constraints so the rental fee must be less than $5,000 and the slip length must be 25, 30, or 40.
