Create a view named BOOK_INVENTORY. It consists of the branch number, and the to

Question

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.

   1. Write and execute the CREATE VIEW command to create the BOOK_INVENTORY view.
   2. Write and execute the command to retrieve the branch number and units on hand for each branch having more than 25 books on hand.
   3. Write and execute the query that the DBMS actually executes against the base tables.
   4. Does updating the database through the view create any problems? If so, what are they? If not, why not?

4. Write,  but do not execute (Just include the this code in your final clean file), the commands to grant the following privileges.

   1. User Rodriquez must be able to retrieve data from the book table.
   2. Users Gomez and Liston must be able to add new   books and publishers to the database.
   3. Users Andrews and Zimmer must be able to change the price of any book.
   4. All users must be able to retrieve the book title, book code, and book price for every book.
   5. User Golden must be able to add and delete publishers.
   6. User Andrews must be able to create an index for the BOOK table.
   7. Users Andrews and Golden must be able to change the structure of the AUTHOR table.
   8. User Golden must have all privileges on the BRANCH, BOOK, and INVENTORY tables.

5. Write,  but do not execute (Just include the this code in your final clean file), the commands to revoke privileges from Andrews.

Explanation / Answer

3.

a. Write and execute the CREATE VIEW command to create the BOOK_INVENTORY view.

create or replace view BOOK_INVENTORY is

select BRANCH.BRANCH_NUM,count(INVENTORY.ON_HAND) UNITS

from BRANCH,INVENTORY

where BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

group by BRANCH.BRANCH_NUM

order by BRANCH_NUM;

b. Write and execute the command to retrieve the branch number and units on hand for each branch having more than 25 books on hand.

select * from BOOK_INVENTORY where UNITS > 25;

c. Write and execute the query that the DBMS actually executes against the base tables.

select BRANCH.BRANCH_NUM,count(INVENTORY.ON_HAND) UNITS

from BRANCH,INVENTORY

where BRANCH.BRANCH_NUM = INVENTORY.BRANCH_NUM

group by BRANCH.BRANCH_NUM

order by BRANCH_NUM

d. Does updating the database through the view create any problems? If so, what are they? If not, why not?

we can't update the data through this view, because this view is depend on more then one table. if it's depend on one table we can update.

4.

a. User Rodriquez must be able to retrieve data from the book table.

Ans) grant select on book to Rodriquez;

b. Users Gomez and Liston must be able to add new books and publishers to the database.

Ans) grant insert on book to Gomez,Liston;

grant insert on publishers to Gomez,Liston;

c. Users Andrews and Zimmer must be able to change the price of any book.

Ans) grant update (price) on book to Andrews,Zimmer;

d. All users must be able to retrieve the book title, book code, and book price for every book.

ANS) create or replace view book_view is

select title, book_code, price from book;

grant select on book_view to PUBLIC;

e) User Golden must be able to add and delete publishers.

Ans) grant insert, delete on PUBLISHER to Golden;

f) User Andrews must be able to create an index for the BOOK table.

Ans) grant create any index on book to Andrews;

g) Users Andrews and Golden must be able to change the structure of the AUTHOR table.

grant alter on AUTHOR to Andrews,Golden;

f)User Golden must have all privileges on the BRANCH, BOOK, and INVENTORY tables.

grant all on BRANCH to Golden;

grant all on BOOK to Golden;

grant all on INVENTORY to Golden;

5. Write, but do not execute (Just include the this code in your final clean file), the commands to revoke privileges from Andrews.

revoke all on book from Andrews;

revoke all on book_view from Andrews;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.