member(memb_no, name, age)

book(isbn, title, authors, publisher)

borrowed(memb_no, isbn, date)

 

1.    Consider the bank database above. Answer the following questions:

a.    Print the average number of books borrowed per member.  Take into account that if a member does not borrow any books, then that member does not appear in the borrowed relation at all.  Insure  that all members  are included in the result.

b.    Create a view that shows all books borrowed by members that does not include personal identifying information (name, age).

c.    Add the following constraints to these tables:

                                              i.     Name- not null

                                            ii.     Age- not null, between 18 and 120

                                           iii.     Isbn- not null

                                           iv.     Authors

                                             v.     Publisher- can be one of three publishers (your choice)

                                           vi.     Date- not null

d.    If you have not already done so, insert the appropriate foreign key constraints for all tables

e.    Set a default value of "Not known" for Authors.

f.     Create a role "clerk".

g.    Grant select access to the "clerk" role to the view from b.