Unit 6 Assignment: Using the Book, BookCopy, Borrower, Librarian and BookLended
ID: 3860362 • Letter: U
Question
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 database’s 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
Grimm’s 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 instructor’s 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 instructor’s 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
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
Grimm’s 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
Explanation / Answer
create database mydatabase;
create table book(
iSBN_number int primary key,
title varchar2(20),
author varchar2(20),
cost varchar2(10)
)
insert into book values(1441438, 'Alice in Wonderland', 'Lewis Carroll' ,$7.95)
insert into book values(6006374,'A First Course in Database Systems (3rd ed.)','Jeffrey Ullman',$99.49)
insert into book values(3523323,'Database System Concepts','Abraham Silberschatz',$119.67)
create table bookCopy(
iSBN_number int primary key,
Sequence int ,
publicationDate Date
)
create table borrower
(
librarycard int autoincrement primery key,
name varchar2(20),
Address varchar2(20),
postalcode varchar2(20),
pnoneNumber varchar2(10),
Membershipdate date
)
create table librarian(
librarianId int auto primery key,
Name varchar2(20),
phone varchar2(10),
supervisor int
)
insert into librarian values(1,'Gertrude Smith','555-1212')
insert into librarian values(2,'Mable Markham','555-1212')
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.