Okay to use Microsoft sql workbeach. Also note that for unit 5, I used the follo
ID: 3728954 • Letter: O
Question
Okay to use Microsoft sql workbeach.
Also note that for unit 5, I used the following tables of library database:
CREATE TABLE BOOK
(
ISBN_NUMBER INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
TITLE VARCHAR(30),
AUTHOR VARCHAR(30),
PUBLICATION_DATE DATE,
COST FLOAT
);
CREATE TABLE BORROWER
(
LIBRARY_CARD INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50),
ADDRESS VARCHAR(200),
POSTAL_CODE VARCHAR(10),
PHONE_NUMBER VARCHAR(10),
MEMBERSHIPDATE DATE
);
CREATE TABLE LIBRARIAN
(
LIBRARIAN_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(200),
PHONE_NUMBER INT,
SUPERVISOR INT
);
CREATE TABLE BOOKLENDED
(
LIBRARY_CARD INT ,
LIBRARIAN_ID INT ,
ISBN_NUMBER INT ,
CHECKOUTDATE DATE,
CONSTRAINT FK_LIB_CARD FOREIGN KEY (LIBRARY_CARD) REFERENCES BORROWER(LIBRARY_CARD),
CONSTRAINT FK_LIBRARIAN_ID FOREIGN KEY (LIBRARIAN_ID) REFERENCES LIBRARIAN(LIBRARIAN_ID),
CONSTRAINT FK_ISBN FOREIGN KEY (ISBN_NUMBER) REFERENCES BOOK(ISBN_NUMBER)
);
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.
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
In order to create tables give commands as follows:
CREATE TABLE BOOK
(
ISBN_NUMBER INT NOT NULL PRIMARY KEY,
TITLE VARCHAR(30),
AUTHOR VARCHAR(30),
COST FLOAT
);
CREATE TABLE BOOKCOPY
(
ISBN_NUMBER INT NOT NULL PRIMARY KEY,
SEQUNCE INT,
PUBLICATION_DATE DATE,
CONSTRAINT FK_ISBN FOREIGN KEY(ISBN_NUMBER) REFERENCES BOOK(ISBN_NUMBER),
);
CREATE TABLE BORROWER
(
LIBRARY_CARD INT NOT NULL PRIMARY KEY,
NAME VARCHAR(50),
ADDRESS VARCHAR(200),
POSTAL_CODE VARCHAR(10),
PHONE_NUMBER VARCHAR(10),
MEMBERSHIPDATE DATE
);
CREATE TABLE LIBRARIAN
(
LIBRARIAN_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(200),
PHONE_NUMBER INT,
SUPERVISOR INT
);
CREATE TABLE BOOKLENDED
(
LIBRARY_CARD INT ,
CHECKOUTDATE DATE,
RETURNDATE DATE,
ISBN_NUMBER INT ,
SEQUENCE INT,
LIBRARIAN_ID INT ,
CONSTRAINT FK_LIB_CARD FOREIGN KEY (LIBRARY_CARD) REFERENCES BORROWER(LIBRARY_CARD),
CONSTRAINT FK_LIBRARIAN_ID FOREIGN KEY (LIBRARIAN_ID) REFERENCES LIBRARIAN(LIBRARIAN_ID),
CONSTRAINT FK_ISBN FOREIGN KEY (ISBN_NUMBER) REFERENCES BOOK(ISBN_NUMBER)
);
To populate values we need to insert values in tables give commands as follows:
INSERT INTO BOOK(1441438,'Alice in Wonderland','Lewis Carroll','$7.95');
INSERT INTO BOOKCOPY VALUES(1441438,1,'5/1/1997');
INSERT INTO BORROWER(NAME,ADDRESS,POSTALCODE,PHONENUMBER,MEMBERSHIPDATE) VALUES('Samil Shah',123,'Home st',62989,555-1212,'02/01/2008');
INSERT INTO LIBRARIAN VALUES(1,'Gertrude Smith',555-1212);
INSERT INTO BOOKLENDED VALUES(2,'12/01/2010','10/20/2013',1441438,1,1);
enter all values present in the table similarily like this. AND then populate the values using the select statment as follows:
SELECT * FROM BOOK;
SELECT * FROM BOOKCOPY;
SELECT * FROM BOOKLENDED;
SELECT * FROM LIBRARIAN;
SELECT * FROM BORROWER;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.