8.7. 

The referential integrity constraints that need to hold are:

PREREQUISITE's Course_number has to refer to a valid Course_number in COURSE.
SECTION's Course_number has to refer to a valid Course_number in COURSE.
GRADE_REPORT's Student_number has to refer to a valid Student_number in STUDENT.
GRADE_REPORT's Section_identifier has to refer to a valid Section_identifier in SECTION.

The DDL syntax would be:

In the PREREQUISITE table:

CONSTRAINT PREREQFK
	FOREIGN KEY(Course_number) REFERENCES COURSE(Course_number)
			ON DELETE SET NULL		ON UPDATE CASCADE,
			
In the SECTION table:

CONSTRAINT COURSEFK
	FOREIGN KEY(Course_number) REFERENCES COURSE(Course_number)
			ON DELETE SET NULL		ON UPDATE CASCADE,
			

In the GRADE_REPORT table:

CONSTRAINT STUDENTNOFK
	FOREIGN KEY(Student_number) REFERENCES STUDENT(Student_number)
			ON DELETE SET NULL		ON UPDATE CASCADE,
			
CONSTRAINT SECTIONIDENTFK
	FOREIGN KEY(Section_identifier) REFERENCES SECTION(Section_identifier)
			ON DELETE SET NULL		ON UPDATE CASCADE,
			
			
8.9.

Choose actions for referential constraints on UPDATE and DELETE for LIBRARY.

BOOK_AUTHOR's Book_id references BOOK's Book_id:
On delete, I would cascade the deletion, as there is no longer a valid book for this tuple to reference, and it is a part of the combination key, so the tuple is no longer valid.
On update, I would cascade the update, as the book still exists, it just needs to know what the new ID is.

BOOK's Publisher_name references PUBLISHER's Name:
On delete, I would set to a default value.  The name of the publisher is no longer there, but the rest of the book's information is valid.
On update, I would cascade the update, to keep the value of the publisher's name consistent.

BOOK_COPIES's Book_id references BOOK's Book_id:
Deletion and update would both cascade, for the same reason as BOOK_AUTHOR's Book_id.

BOOK_COPIES's Branch_id references LIBRARY BRANCH's Branch_id:
Deletion and update would both cascade, again for the same reason as the Book_id references; the branch no longer exists/has simply changed IDs, and it is a part of BOOK_COPIES' primary key.

BOOK_LOANS' Book_id, Branch_id, and Card_no should all cascade for updating and deleting, again because they are all part of the main key.

8.10.



CREATE TABLE BOOK
(	Book_id			STRING			NOT NULL,
	Title			STRING,
	Publisher_name	STRING,
	PRIMARY KEY(Book_id),
	FOREIGN KEY(Publisher_name) REFERENCES PUBLISHER(Name)
			ON DELETE CASCADE		ON UPDATE CASCADE
	);
	
CREATE TABLE BOOK_AUTHORS
(	Book_id			INT				NOT NULL,
	Author_name		STRING			NOT NULL,
	PRIMARY KEY(Book_id, Author_name)
	FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id),
			ON DELETE SET DEFAULT	ON UPDATE CASCADE
	);
	
CREATE TABLE PUBLISHER
(	Name			STRING			NOT NULL,
	Address			STRING
	Phone			STRING
	PRIMARY KEY(Name),
	);
	
CREATE TABLE BOOK_COPIES
(	Book_id			INT				NOT NULL,
	Branch_id		INT				NOT NULL,
	No_of_copies	INT,
	PRIMARY KEY(Book_id, Branch_id),
	FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id)
			ON DELETE CASCADE		ON UPDATE CASCADE,
	FOREIGN KEY(Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id)
			ON DELETE CASCADE		ON UPDATE CASCADE
	);
	
CREATE TABLE BOOK_LOANS
(	Book_id			INT				NOT NULL,
	Branch_id		INT				NOT NULL,
	Card_no			INT				NOT NULL,
	Date_out		DATE			
	Due_date		DATE
	PRIMARY KEY(Book_id, Branch_id, Card_no),
	FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id)
			ON DELETE CASCADE		ON UPDATE CASCADE,
	FOREIGN KEY(Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id)
			ON DELETE CASCADE		ON UPDATE CASCADE,
	FOREIGN KEY(Card_no) REFERENCES BORROWER(Card_no)
			ON DELETE CASCADE		ON UPDATE CASCADE
	);
	
CREATE TABLE LIBRARY_BRANCH
(	Branch_id		INT				NOT NULL,
	Branch_name		STRING,
	Address			STRING,
	PRIMARY KEY(Branch_id)
	);
	
CREATE TABLE BORROWER
(	Card_no			INT				NOT NULL,
	Name			STRING,
	Address			STRING,
	Phone			STRING,
	PRIMARY KEY(Card_no)
	);
	
8.11.

a) How many copies of the book titled The Lost Tribe are owned by the library branch whose name is 'Sharpstown'?

SELECT 	No_of_copies
FROM	BOOK_COPIES AS BC, BOOK AS B, LIBRARY_BRANCH AS LB
WHERE	BC.Book_id = B.Book_id, B.Name = 'The Lost Tribe', BC.Branch_id = LB.Branch_id

b) How many copies of the book titled The Lost Tribe are owned by each library branch?

SELECT	No_of_copies, Branch_id
FROM	BOOK_COPIES AS BC, BOOK AS B, LIBRARY_BRANCH AS LB
WHERE	BC.Book_id = B.Book_id, B.Name = 'The Lost Tribe', BC.Branch_id = LB.Branch_id

c) Retrieve the names of all borrowers who do not have any books checked out.

SELECT	Name
FROM	BORROWER as B, BOOK_LOANS as BL
WHERE	BORROWER.Card_no = BOOK_LOANS.Card_no AND Due_date=NULL

d) For each book that is loaned out from the Sharpstown branch and whose Due_date is today, retrieve the book title, the borrower's name, and the borrower's address.

SELECT  BORROWER.name, BORROWER.Address, BOOK.Title
FROM	BORROWER, BOOK, LIBRARY_BRANCH, BOOK_LOANS
WHERE	BORROWER.Card_no = BOOK_LOANS.Card_no, BOOK_LOANS.Branch_id = 'Sharpstown', BOOK_LOANS.Due_date = 'Today'

e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch.

SELECT	ALL LIBRARY_BRANCH.Name, No_of_copies
FROM	LIBRARY_BRANCH, BOOK_COPIES
WHERE	Branch_id = BOOK_COPIES.Branch_id

f) Retrieve the names, addresses, and number of books checked out for all borrowers who have more than five books checked out.

SELECT	Name, Address, COUNT(Due_date)
FROM	LIBRARY_BRANCH, BORROWER, BOOK_LOANS
WHERE	BORROWER.Card_no = BOOK_LOANS.Card_no, COUNT(Due_date) >= 5