| Database systems | Assignment # 1 | Areeba kamil (34421) | BSCS 4A | 10/4/2015 |

4.7. Consider the LIBRARY relational database schema shown in Figure 4.6. Choose the appropriate action (reject,cascade,set to NULL,set to default) for each referential integrity constraint, both for the deletion of a referenced tuple and for the update of a primary key attribute value in a referenced tuple.Justify your choices.

CREATE DATABASE library;

CREATE TABLE book( book_id int, title varchar(50), publisher_name varchar(50), primary key(book_id),

CONSTRAINT "fk_book_publisher"
FOREIGN KEY(publisher_name) REFRENCES publisher(name)
ON DELETE SET NULL ON UPDATE CASCADE
);
ON DELETE SET NULL is chosen in case the name of a publisher needs to be removed, it can be deleted and set to NULL in all the tables containing publisher’s name
ON UPDATE CASCADE is chosen here so that all the tables containing that publisher’s name are updated in accordance to the new publisher’s name

CREATE TABLE book_authors( book_id int, author_name varchar(50),
CONSTRAINT "fk_book_authors_book"
FOREIGN KEY(book_id) REFRENCES book(book_id)
ON DELETE CASCADE ON UPDATE CASCADE
);
ON DELETE CASCADE is chosen because in case a book is to be removed from the LIBRARY database, all the rows corresponding to that deleted book_id will be deleted from all tables containing book_id. Since this could be dangerous, we could have also used ON DELETE RESTRICT
ON UPDATE CASCADE is chosen because in case a book ID has been entered incorrectly, it can be re-entered and the new value of the book_id will be updated in all the tables containing that book_id

CREATE TABLE publisher( name varchar(50), address varchar(50), phone bigint,

primary key(name)
);

CREATE TABLE book_copies( book_id int, branch_id int, no_of_copies int,

CONSTRAINT "fk_book_copies_book"