1. Using the information in DB1, create a table book

with the following constraints (as seen in todays class):

Respect the attribute types provided

Bookid is the primary key and authorid is a foreign key.

The foreign key constraint must ensure the deletion (and

update) of the referencing rows in the book table when the

referenced row is deleted (and updated) in the author table.

The rating must be an integer between 1 and 10.

The amount must be larger than zero.

The booktype must be one of the following: Fiction-adult,

Non-Fiction-adult, Fiction-youth, Non-Fiction-youth

2. You were requested to implement the following:

(i) Create the table author with the information provided in DB1.

(ii) You want to ensure that the total number of books and

authors (counted together) in your database does not surpasses

10000. Create an assertion that limits the total number of Books

and Authors records to 10000.

3 Suppose that your colleague has implemented a

function check_book_rating(). Create a trigger that executes the

check_book_rating() function whenever a row of the table book is

about to be updated. (Assume that function check_book_rating()

is already created.)

4. You were asked to implement some changes to the

database. Provide the code that implements the following.

(i) Add a new attribute named author_sum_rating to

the previously created table author. This attribute must have the

value zero as default.

(ii) The new attribute author_sum_rating should

represent the sum of the rating of all books of each author.

Create a trigger that maintains the value of the new column

author_sum_rating, i.e., each time a book rating is updated to an

integer value that is not zero, this column value is recalculated

and updated in table author.

Book Bookid Bookname Authorid integer varchar(20) integer Numeric(7,2) integer varchar(25) Amount Rating Booktype Author Authorid integer varchar(20) Authorname