Programming assignment solution

CS2203 Database WA 7

Develop a SQL query that will select all of the books that are currently checked out and are past
due to be returned, which have a value greater than $10.00. Your query must return the name,
address, postal code, and phone number of the borrower, the name and phone number of the
librarian responsible for the transaction, the date the book was due to be returned, and the name,
cost, and ISBN number of the book. You query should sort the list by the return date in
descending order.

You must submit both your SQL query and the output of executing your query against your
database.



Create Database Schema



create table book ( title varchar(60) not null, author varchar(40), cost decimal(10,2), isbn_number
integer not null );



alter table book add unique (isbn_number);



alter table book add primary key (isbn_number);



create table bookcopy ( Isbn_number integer, publicationdate date, sequence integer);



alter table bookcopy add primary key (isbn_number, sequence);



alter table bookcopy add CONSTRAINT bookcopy_uniq UNIQUE (ISBN_number, sequence);



alter table bookcopy add CONSTRAINT book_fkey FOREIGN KEY (isbn_number) REFERENCES book
(isbn_number) ON DELETE CASCADE;




create table borrower ( librarycard integer, name char(40), address char(40), postalcode char(20),
phonenumber char(20), membershipdate date);

, alter table borrower add primary key (librarycard);



create table librarian ( librarianid integer, name char(40), phonenumber char(20), supervisor integer);



alter table librarian add primary key (librarianid);




create table booklended ( librarycard integer not null, checkoutdate date not null, returndate date,
ISBN_number integer not null,

sequence integer not null, librarianid integer not null

);



alter table booklended add PRIMARY KEY (ISBN_number, sequence, librarycard, checkoutdate);



alter table booklended add CONSTRAINT booklended_uniq UNIQUE (ISBN_number, sequence,
librarycard, checkoutdate);



alter table booklended add CONSTRAINT borrower_fkey FOREIGN KEY (librarycard) REFERENCES
borrower (librarycard) ON DELETE CASCADE;



alter table booklended add CONSTRAINT librarian_fkey FOREIGN KEY (librarianid) REFERENCES librarian
(librarianid) ON DELETE CASCADE;



alter table booklended add CONSTRAINT bookcopy_fkey FOREIGN KEY (isbn_number, sequence)
REFERENCES bookcopy (isbn_number, sequence);




Populate Book Table