In this assignment, we will continue to work with the book database from the previous assignment.

Here are the relations again:

BookAuthor(book, year, author, earnings)

BookReference(book, year, referencedBook, referencedYear, times)

BookReview(book, year, reviewer, score)

BookPublish(book, year, publisher, price, num)

Recall that in this database, each book may have one or more authors and each author may make a different amount of money from that book. One book may make reference to other books. One book may be reviewed by different reviewers and get different scores. An author could also be a reviewer and a publisher. Books with the same name but published in different years are considered different books.

Recall the previous assignment (#9):

Write schemas (CREATE TABLE ...) for these relations in SQL, with the following constraints. Use attribute-based checks, tuple-based checks, foreign key declarations, or SQL assertions, as appropriate.

a) All books that appear anywhere in this database must appear in the relation BookPublish.

b) A book cannot reference a book published in a future year. Neither can it reference itself.

c) Revenues from a book (price times number of copies) must be at least twice the authors' total earnings for that book.

d) No reviewer may review more than 20 books published in the same year.

e) If a book is referenced by more than 20 other books, then its average review score must be at least 8, unless there is no review for this book.

Part 1: Write the following triggers in either SQL standard notation or in the Oracle trigger language (tell which notation you chose!) a) Whenever a book gets a new review of score 10, each of the authors earns an additional $100. b) The database should reject insertions into BookAuthor which makes an author earn more than twice as much as a co-author of the same book. Note: In Standard SQL, it is also possible to clean up (i.e., reverse) the modification after it is made, rather than rejecting it.

Part 2: Consider the referential integrity constraint in previous assignment (#9) Question (a) above. Suppose we want to enforce the cascade policy for deletions on relation BookPublish. Write one or more triggers in Standard SQL that implement this enforcement policy.