Views Exercise

Objectives:

1. Find out how to create a VIEW(external schema) of the database.
2. Determine some of the limitations of VIEWs.

Also remember that the Interactive SQL environment will run whatever is in the buffer. If you leave a CREATE VIEW statement there ISQL willl re-run it and give an error. Use the blank option to clear the buffer.

1. Define a VIEW that will allow the User access to borrower numbers and names, along with the ISBN, title and reservation date of books currently reserved.
   1. Invoke the Queries, SQL option.
   2. The VIEW will be based on a SELECT statement so you should concentrate on getting this right before you actually issue the CREATE VIEW command.
      Remember: For 3 tables you will need 2 joins. Have a go at generating the SELECT statement. Let's face it, you need the practice, that's why you're doing this trainer :-)
      Here is one I prepared earlier.

      Once you have a good SELECT statement you can turn it into a CREATE VIEW statement simply by adding the text CREATE VIEW borrower_reservations AS in front of your SELECT statement.
      Like this.

      You can then do a SELECT * FROM borrower_reservations to check that it worked.
2. Create a VIEW to restrict Users' access to the BORROWER Table to the borrower number, name and status only.
   1. Here we are using a VIEW to only present certain data from one table rather than linking several tables together. i.e. we are performing a relational PROJECT operation.
   2. Again you should concentrate on getting the SELECT statement right before you actually issue the CREATE VIEW command.
      Here is one I prepared earlier ....
      ... and here is the CREATE VIEW command..
3. Certain restrictions are placed on views. You cannot always use all the SQL data manipulation operations
   1. Try using QBF attempt to Retrieve, Append and Update data through the VIEWs you have created.
   2. Try using SQL INSERT statements attempt to append data through each of your VIEWs.
   What restrictions are placed on operations on views? Why do you think this is the case?

Continue with trainer.