Here is the question:

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.

Assignment Instructions:

the assignment must include the SQL DML statements required to implement at least the following relations:

Book

Borrower

BookLended

Librarian

the assignment must make appropriate use of Null (and Not Null) parameters to ensure data validity. The minimum standard will be measured against the instructors example and will be based upon rules associated with primary and foreign keys and integrity constraint rules.

the assignment must make appropriate use of constraint clauses to ensure the referential integrity of the relations in the schema. The minimum standard will be measured against the instructors example and will be based upon rules associated with primary and foreign keys and integrity constraint rules.

the assignment must make appropriate use of keys including the automatic generation of key values where appropriate.

I created a database using c9.io and it is as follow:

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);

insert into book (isbn_number, title, author, cost) values (1441438, 'Alice in Wonderland','Lewis Carroll',7.95); insert into book (isbn_number, title, author, cost) values (6006374, 'A First Course in Database Systems (3rd ed.) ', 'Jeffrey Ullman',99.49); insert into book (isbn_number, title, author, cost) values (3523323, 'Database System Concepts ', 'Abraham Silberschatz',119.67); insert into book (isbn_number, title, author, cost) values (1429477, 'Grimms Fairy Tales', 'Jacob Grimm',26.99); insert into book (isbn_number, title, author, cost) values (1486025, 'A Tale of Two Cities' ,'Charles Dickens',7.95); insert into book (isbn_number, title, author, cost) values (1853602, 'War and Peace','Leo Tolstoy',7.99); insert into book (isbn_number, title, author, cost) values (1904129, 'The Scarlet letter','Nathaniel Hawthorne',7.95); insert into book (isbn_number, title, author, cost) values (1593832, 'Pride and Prejudice', 'Jane Austen',7.95); insert into book (isbn_number, title, author, cost) values (1538243, 'Pride and Prejudice','Jane Austen',7.95);

insert into bookcopy (isbn_number, sequence, publicationdate) values (1441438 ,1, '1997-05-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (6006374 ,1, '2007-10-06'); insert into bookcopy (isbn_number, sequence, publicationdate) values (6006374 ,2, '2007-10-06'); insert into bookcopy (isbn_number, sequence, publicationdate) values (3523323 ,1, '2010-01-27'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,1, '2004-02-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,2, '2004-02-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,3, '2004-02-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1429477 ,4, '2004-02-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1486025 ,1, '2010-12-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1853602 ,1, '2007-09-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1853602 ,2, '2010-09-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1904129 ,1, '2009-10-01'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1593832 ,1, '2004-09-20'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1538243 ,1, '2004-09-20'); insert into bookcopy (isbn_number, sequence, publicationdate) values (1538243 ,2, '2004-09-20');

insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Samil Shah','123 Home st',62989, '555-1212', '2008-02-01'); insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Tim Jones', '3435 Main st.',54232,'555-2934','2011-07-13'); insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Sue Smith', '2176 Baker st.',43542,'555-6723','2005-05-10'); insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Jeff Bridges', '176 Right st. ',28460,'555-1745','2010-06-20'); insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Steve Smith', '435 Main St. ',28454,'555-6565','2005-05-18'); insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Arun Goel', '34 Home St. ',56234,'555-4889','2008-03-15'); insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Jane Doe', '65 Water St. ',42358,'555-4581','2011-09-07'); insert into borrower (librarycard, name, address, postalcode, phonenumber, membershipdate) values (null, 'Jim Jones', '23 Hill Drive',85423,'555-7891','2010-11-23');

insert into librarian (librarianid, name, phonenumber, supervisor) values (1,'Gertrude Smith','555- 1212',null); insert into librarian (librarianid, name, phonenumber, supervisor) values (2,'Mable Markham','555- 1212',1); insert into librarian (librarianid, name, phonenumber, supervisor) values (3,'Penelope Pretty','555- 1212',1); insert into librarian (librarianid, name, phonenumber, supervisor) values (4,'Olga Brown','555-2300',1);

insert into booklended (librarycard, checkoutdate, returndate, isbn_number, sequence, librarianid) values (2,'2010-12-01','2013-10-20',1441438,1,1); insert into booklended (librarycard, checkoutdate, returndate, isbn_number, sequence, librarianid) values (5,'2010-12-01','2010-10-01',6006374,2,2); insert into booklended (librarycard, checkoutdate, returndate, isbn_number, sequence, librarianid) values (2,'2010-12-01','2012-01-20',3523323,1,2); insert into booklended (librarycard, checkoutdate, returndate, isbn_number, sequence, librarianid) values (7,'2010-12-01','2015-01-27',1429477,1,3); insert into booklended (librarycard, checkoutdate, returndate, isbn_number, sequence, librarianid) values (6,'2010-12-01','2015-07-01',1853602,2,4); insert into booklended (librarycard, checkoutdate, returndate, isbn_number, sequence, librarianid) values (3,'2010-12-01','2015-08-23',1904129,1,2);

+-------------+-----------------+----------+ | Isbn_number | publicationdate | sequence | +-------------+-----------------+----------+ | 1429477 | 2004-02-01 | 1 | | 1429477 | 2004-02-01 | 2 | | 1429477 | 2004-02-01 | 3 | | 1429477 | 2004-02-01 | 4 | | 1441438 | 1997-05-01 | 1 | | 1486025 | 2010-12-01 | 1 | | 1538243 | 2004-09-20 | 1 | | 1538243 | 2004-09-20 | 2 | | 1593832 | 2004-09-20 | 1 | | 1853602 | 2007-09-01 | 1 | | 1853602 | 2010-09-01 | 2 | | 1904129 | 2009-10-01 | 1 | | 3523323 | 2010-01-27 | 1 | | 6006374 | 2007-10-06 | 1 | | 6006374 | 2007-10-06 | 2 | +-------------+-----------------+----------+ 15 rows in set (0.00 sec)

mysql> select*from librarian; +-------------+-----------------+-------------+------------+ | librarianid | name | phonenumber | supervisor | +-------------+-----------------+-------------+------------+ | 1 | Gertrude Smith | 555- 1212 | NULL | | 2 | Mable Markham | 555- 1212 | 1 | | 3 | Penelope Pretty | 555- 1212 | 1 | | 4 | Olga Brown | 555-2300 | 1 | +-------------+-----------------+-------------+------------+ 4 rows in set (0.00 sec)

mysql> select*from borrower; +-------------+--------------+----------------+------------+-------------+----------------+ | librarycard | name | address | postalcode | phonenumber | membershipdate | +-------------+--------------+----------------+------------+-------------+----------------+ | 1 | Samil Shah | 123 Home st | 62989 | 555-1212 | 2008-02-01 | | 2 | Tim Jones | 3435 Main st. | 54232 | 555-2934 | 2011-07-13 | | 3 | Sue Smith | 2176 Baker st. | 43542 | 555-6723 | 2005-05-10 | | 4 | Jeff Bridges | 176 Right st. | 28460 | 555-1745 | 2010-06-20 | | 5 | Steve Smith | 435 Main St. | 28454 | 555-6565 | 2005-05-18 | | 6 | Arun Goel | 34 Home St. | 56234 | 555-4889 | 2008-03-15 | | 7 | Jane Doe | 65 Water St. | 42358 | 555-4581 | 2011-09-07 | | 8 | Jim Jones | 23 Hill Drive | 85423 | 555-7891 | 2010-11-23 | +-------------+--------------+----------------+------------+-------------+----------------+ 8 rows in set (0.00 sec)

mysql> select*from booklended; +-------------+--------------+------------+-------------+----------+-------------+ | librarycard | checkoutdate | returndate | ISBN_number | sequence | librarianid | +-------------+--------------+------------+-------------+----------+-------------+ | 7 | 2010-12-01 | 2015-01-27 | 1429477 | 1 | 3 | | 2 | 2010-12-01 | 2013-10-20 | 1441438 | 1 | 1 | | 6 | 2010-12-01 | 2015-07-01 | 1853602 | 2 | 4 | | 3 | 2010-12-01 | 2015-08-23 | 1904129 | 1 | 2 | | 2 | 2010-12-01 | 2012-01-20 | 3523323 | 1 | 2 | | 5 | 2010-12-01 | 2010-10-01 | 6006374 | 2 | 2 | +-------------+--------------+------------+-------------+----------+-------------+