Question $1-$A library is setting up a database with the following relational schema:

$$Book$($isbn$,$title$,$ author$)$

$$Copy$($isbn$,$serial$\_$number, weight, bookcase$)$

A book must have an isbn and a title. Several copies of each book can be present in the library, each with a unique serial number, a weight in grams. Each bookcase has a number. The following constraints apply to every change to the database:$$

$1.$Every book has a unique value for isbn.

$2.$For every copy C there exists a book that has C$\text{'}$s isbn. A copy cannot be added to the database if a book with the copy's isbn does not exists in the table Book.

$3.$If the isbn of a book in the database changes, then the isbn of copies

of that book should also change $($so those copies continue to belong to the same book$).$

$4.$The removal of a book must result in all its copies being deleted.

$$

Give the SQL create statements for Book and Copy such that the above requirements are fulfilled. Use sensible data types or each attribute. Use key constraints and associated actions. Do not use triggers.

$$

NB foreign keys need to be enabled explicitly in SQLite $3$using the command$"$PRAGMA foreign$\_$keys $=$ON;$".$Without this option enabled, SQLite will ignore foreign keys, which will make it difficult to properly test your queries.$$

$$Hide hint for Question $1$

Remember that foreign key constraints are stated as follows:

$$FOREIGN KEY $($x$1,$x$2,...)$REFERENCES T$($y$1,$y$2,...)$ON e$1$a$1$ON e$2$a$2...$

where x$1,$x$2$and y$1,$y$2$are attribute names; T is a table name; e$1,$e$2$are events $($delete$,$update$)$and a$1,$a$2$are actions $($set null, set default, no action, cascade$).$Actions might or might not be deferrable.

Question $2-$

$$

Give a sequence of two insert statements that violate constraint $1$above $($Every book has a unique value for isbn$).$

Question $3-$

$$

Let's assume that the book table contains the following row $($and no other rows$)$:

$(\text{'}9781292025827\text{'},\text{'}$A first course in Database Systems', 'Jennifer Widom'$)$

Give an SQL statement that violates constraint $2.$

Question $4-$

$$

Let's assume that we have two copies of Jennifer Widom's book, i$.$e$.,$the table Copy has the following two rows:

$[(\text{'}9781292025827\text{'},1,200,81),$

$(\text{'}9781292025827\text{'},2,200,81)]$

Give an SQL update statement that shows that condition $3$is handled correctly.

Question $5-$

$$

Listen

For your database, give an SQL statement that shows that constraint $4$is handled correctly.

Question $6-$

$$

Add a constraint to Copy $($or recreate Copy with the added constraint$)$that makes sure that books never weigh less than $10$grams and never more than $1000$grams$.$

$$Hint for Question $6$

Create the table with an additional "CHECK" constraint. In PostgreSQL you can use 'ALTER TABLE' to add the constraint

Question $7-$

Write an assertion that makes sure that somewhere in our library there exists a book copy written by 'Jennifer Widom'

NB PostgreSQL and SQLite do not support assertions yet. PostgreSQL does check the syntax of your query.

hint for Question $7$

Tip: Test the SQL query inside your assertion separately on your database.

$$Question $8-$

Create an assertion that states that the total amount of book copies in our library should not exceed $1$billion grams.

$$

$$Question $9-$

Create an assertion that makes sure that no bookcase in our library has more than $1,000,000$grams of books.