******DATABASE 1 IN OPENOFFICE 2 Using the Book, BookCopy, Borrower, Librarian a

Question

******DATABASE 1 IN OPENOFFICE

2 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) 5

Explanation / Answer

SOLUTION:

According to the given data to create tables the below data follows;

Here the below codes that represents the data for the TABLE BOOK,TABLE BOOK COPY,TABLE BORROWER,TABLE LIBRARIAN,TABLE BOOK LENDED,

àfor the TABLE BOOK;

CREATE TABLE BOOK
(
ISBN_NUMBER INT NOT NULL PRIMARY KEY,
TITLE VARCHAR(30),
AUTHOR VARCHAR(30),
COST FLOAT
);

àfor the TABLE BOOK COPY;

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

àfor the TABLE BORROWER:

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

àfor the TABLE LIBRARIAN:

CREATE TABLE LIBRARIAN
(
LIBRARIAN_ID INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
NAME VARCHAR(200),
PHONE_NUMBER INT,
SUPERVISOR INT
);

àFor the TABLE BOOKLENDED;

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

The values need to be inserted in the table commands;

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

by using the below procedure by taking the selected statement we need to enter the values;

SELECT * FROM BOOK;

SELECT * FROM BOOKCOPY;

SELECT * FROM BOOKLENDED;

SELECT * FROM LIBRARIAN;

SELECT * FROM BORROWER;

Hence by using the above data we can have our requirement

Related Questions

Navigate

• Browse (All)
• Browse #
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.