BookAuthor (book, year, author, earnings)

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

BookReview(book, year, reviewer, score)

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

Assume that 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.

Write SQL schema definitions (CREATE TABLE ...) statements 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.