Using the Book, BookCopy, Borrower, Librarian and BookLended tables created for
ID: 3919484 • Letter: U
Question
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 (3rded.)
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.
***Please do not copy someone else's solution or copy the solution from a website over the internet. Thanks***
ISBN_number
Title
Author
Cost
1441438
Alice in Wonderland
Lewis Carroll
$7.95
6006374
A First Course in Database Systems (3rded.)
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
Creating table for Book:
CREATE TABLE Book
(
ISBN_number int primary key,
Title varchar(255),
Author varchar(255),
Cost decimal(4,3)
);
Inserting the records in the table:
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 (3rded.)','Jeffrey Ullman,'99.49');
I have inserted two tuples.
Creating table for Bookcopy:
CREATE TABLE Bookcopy
(
ISBN_number int,
Sequence int,
PublicationDate date
);
Inserting the records in the table:
INSERT INTO Bookcopy(ISBN_number, Sequence, PublicationDate)
VALUES ('1441438', '1', '5/1/1997');
Creating table for Borrower:
CREATE TABLE Borrower(Library_Card_No int AUTO_INCREMENT, Name varchar(255), Address varchar(255), PostalCode int, Phone_Number int, MembershipDate date);
Inserting the records in the table:
INSERT INTO Borrower(Library_Card_No, Name, Address, PostalCode, Phone_Number, MembershipDate)
VALUES ('Samil Shah','123', 'Home st','62989','5551212','02/01/2008');
Creating table for BoookLended:
CREATE TABLE BookLended(Borrower_Librarycard int, Check_Out_Date date, Return_Date date, ISBN_number int, Sequence int, LibrarianId int
LibrarianId FOREIGN KEY (Librarian_id) REFERENCES Librarian(Librarian_id)
);
Inserting the records in the table:
INSERT INTO BookLended(Borrower_Librarycard, Check_Out_Date, Return_Date, ISBN_number, Sequence, LibrarianId)
VALUES ('2','12/01/2010','10/20/2013','1441438','1',1);
Creating table for Librarian:
CREATE TABLE Librarian(Librarian_id int not null, Name varchar(255), Phone int, Supervisor int, PRIMARY KEY (Librarian_id));
Inserting the records in the table:
INSERT INTO Librarian(Librarian_id, Name, Phone, Supervisor)
VALUES ('1','Gertrude Smith','5551212','');
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.