Unit 6 Assignment:

Using the Book, BookCopy, Borrower, Librarian and BookLended tables created for your library database in Unit 5, populate them with data using the following information. If your tables have additional attributes that are not included in the following list, then add appropriate values to populate your tables. You must submit all of the SQL statements required to create the tables and any indexes you deem appropriate (NOTE: indexes are NOT required but you may include them where you feel they would be appropriate), implement appropriate constraints including referential integrity and uniqueness and populate the tables with data (insert statements). Your assignment MUST contain ALL of the SQL statements to create the tables, populate the tables and define all of the appropriate constraints. Further you must issue a select statement and include both the select statement and the output of the select statement (using a screen shot of your databases output) that shows the contents of each table after you have completed populating the tables with data (after you have executed all of the insert statements).

Book

ISBN_number	Title	Author	Cost
1441438	Alice in Wonderland	Lewis Carroll	$7.95
6006374	A First Course in Database Systems (3rd ed.)	Jeffrey Ullman	$99.49
3523323	Database System Concepts	Abraham Silberschatz	$119.67
1429477	Grimms Fairy Tales	Jacob Grimm	$26.99
1486025	A Tale of Two Cities	Charles Dickens	$7.95
1853602	War and Peace	Leo Tolstoy	$7.99
1904129	The Scarlet letter	Nathaniel Hawthorne	$7.95
1593832	Pride and Prejudice	Jane Austen	$7.95
1538243	Pride and Prejudice	Jane Austen	$7.95

Bookcopy

ISBN_number	Sequence	PublicationDate (Month/Day/Year)
1441438	1	5/1/1997
6006374	1	10/6/2007
6006374	2	10/6/2007
3523323	1	01/27/2010
1429477	1	02/01/2004
1429477	2	02/01/2004
1429477	3	02/01/2004
1429477	4	02/01/2004
1486025	1	12/01/2010
1853602	1	09/01/2007
1853602	2	09/01/2010
1904129	1	10/01/2009
1593832	1	09/20/2004
1538243	1	09/20/2004
1538243	2	09/20/2004

Borrower

Library Card # (auto increment)	Name	Address	Postal Code	Phone Number	MembershipDate
	Samil Shah	123 Home st	62989	555-1212	02/01/2008
	Tim Jones	3435 Main st.	54232	555-2934	7/13/2011
	Sue Smith	2176 Baker st.	43542	555-6723	5/10/2005
	Jeff Bridges	176 Right st.	28460	555-1745	6/20/2010
	Steve Smith	435 Main St.	28454	555-6565	5/18/2005
	Arun Goel	34 Home St.	56234	555-4889	3/15/2008
	Jane Doe	65 Water St.	42358	555-4581	9/07/2011
	Jim Jones	23 Hill Drive	85423	555-7891	11/23/2010

BookLended

Borrower (library card)	Check Out Date	Return Date	ISBN_number	Sequence	LibrarianId
2	12/01/2010	10/20/2013	1441438	1	1
5	12/01/2010	10/01/2010	6006374	2	2
2	12/01/2010	01/20/2012	3523323	1	2
8	12/01/2010	01/27/2015	1429477	1	3
6	12/01/2010	07/01/2015	1853602	2	4
3	12/01/2010	08/23/2015	1904129	1	2

Librarian

Librarian id	Name	Phone	Supervisor
1	Gertrude Smith	555-1212
2	Mable Markham	555-1212	1
3	Penelope Pretty	555-1212	1
4	Olga Brown	555-2300	1

Assignment Instructions:

The assignment must include the SQL DDL and DML statements required to implement at least the following relations (both to create and populate):

Book

Borrower

BookLended

Librarian

The assignment must make appropriate use of 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 include a select statement that selects the contents of each table and include the output of this select statement when executed against the populated database.

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.

scribd.com/document/101675370/CS1203-Database-1-Unit-6