1. Create a view named PLUME. It consists of the book code, title, type, and price for every book published by the publisher whose code is PL. a. Write and execute the CREATE VIEW command to create the PLUME view. b. Write and execute the command to retrieve the book code, title, and price for every book with a price of less than $13. c. Write and execute the query that the DBMS actually executes. d. Does updating the database through this view create any problems? If so, what are they? If not, why not? 2. Create a view named NONPAPERBACK. It consists of the book code, title, publisher name, and price for every book that is not available in paperback. a. Write and execute the CREATE VIEW command to create the NONPAPERBACK view. b. Write and execute the command to retrieve the book title, publisher name, and price for every book in the NONPAPERBACK view with a price of less than $20. c. Write and execute the query that the DBMS actually executes. d. Does updating the database through this view create any problems? If so, what are they? If not, why not? 3. Create a view named BOOK_INVENTORY. It consists of the branch number and the total number of books on hand for each branch. Use UNITS as the name for the count of books on hand. Group and order the rows by branch number. a. Write and execute the CREATE VIEW command to create the BOOK_INVENTORY view. b. Write and execute the command to retrieve the branch number and units for each branch having more than 25 books on hand. c. Write and execute the query that the DBMS actually executes. d. Does updating the database through this view create any problems? If so, what are they? If not, why not? 4. Write, but do not execute, the commands to grant the following privileges: a. User Rodriquez must be able to retrieve data from the BOOK table. b. Users Gomez and Liston must be able to add new books and publishers to the database. c. Users Andrews and Zimmer must be able to change the price of any book. d. All users must be able to retrieve the book title, book code, and book price for every book. e. User Golden must be able to add and delete publishers. f. User Andrews must be able to create an index for the BOOK table. g. Users Andrews and Golden must be able to change the structure of the AUTHOR table. h. User Golden must have all privileges on the BRANCH, BOOK, and INVENTORY tables. 5. Write, but do not execute, the command to revoke all privileges from user Andrews. 6. Create the following indexes: a. Create an index named BOOK_INDEX1 on the TITLE column in the BOOK table. b. Create an index named BOOK_INDEX2 on the TYPE column in the BOOK table. c. Create an index named BOOK_INDEX3 on the CITY and PUBLISHER_NAME columns in the PUBLISHER table. 7. Delete the index named BOOK_INDEX3. 8. Write the commands to obtain the following information from the system catalog. Do not execute these commands unless your instructor asks you to do so. a. List every column in the PUBLISHER table and its associated data type. b. List every table that contains a column named PUBLISHER_CODE. c. List the table name, column name, and data type for the columns named BOOK_CODE, TITLE, and PRICE. Order the results by table name within column name. (That is, column name is the major sort key and table name is the minor sort key.) 9. Add the PUBLISHER_CODE column as a foreign key in the BOOK table. 10. Ensure that the PAPERBACK column in the BOOK table can accept only values of Y or N.



Source link

Leave a Reply

Your email address will not be published. Required fields are marked *