Create the Library database using the scheme below. Submit the following in a Wo

Question

Create the Library database using the scheme below. Submit the following in a Word document. 1. SQL script of all CREATE statements for each table and screenshot of each corresponding table structure from SQL Server (or Oracle SQL) 2. SQL script of all INSERT statements for each table. Insert 1 library branch and at least 3 rows of data in all other tables. 3. SQL script and the screenshot of all SELECT statements for each table and each corresponding output. 4. SQL script of an UPDATE statement for at least 1 row in the Borrower table

Explanation / Answer

If you have any doubts, please give me comment...

CREATE TABLE PUBLISHER(

Name VARCHAR(50) NOT NULL PRIMARY KEY,

Address VARCHAR(100),

Phone VARCHAR(15)

);

CREATE TABLE BOOK(

Book_id INT NOT NULL PRIMARY KEY,

Title VARCHAR(50),

Publisher_name VARCHAR(50),

FOREIGN KEY(Publisher_name) REFERENCES PUBLISHER(Name)

);

CREATE TABLE BOOK_AUTHORS(

Book_id INT NOT NULL PRIMARY KEY,

Author_name VARCHAR(50),

FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id)

);

CREATE TABLE LIBRARY_BRANCH(

Branch_id INT NOT NULL PRIMARY KEY,

Branch_name VARCHAR(50),

Address VARCHAR(100)

);

CREATE TABLE BOOK_COPIES(

Book_id INT,

Branch_id INT,

No_of_copies INT,

FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id),

FOREIGN KEY(Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id)

);

CREATE TABLE BORROWER(

CardNo INT NOT NULL PRIMARY KEY,

Name VARCHAR(50),

Address VARCHAR(100),

Phone VARCHAR(15)

);

CREATE TABLE BOOK_LOANS(

Book_id INT,

Branch_id INT,

Card_no INT,

Date_out DATE,

Due_date DATE,

FOREIGN KEY(Book_id) REFERENCES BOOK(Book_id),

FOREIGN KEY(Branch_id) REFERENCES LIBRARY_BRANCH(Branch_id),

FOREIGN KEY(Card_no) REFERENCES BORROWER(Card_no)

);

SELECT * FROM BOOK;

SELECT * FROM BOOK_AUTHORS;

SELECT * FROM PUBLISHER;

SELECT * FROM BOOK_COPIES;

SELECT * FROM BOOK_LOANS;

SELECT * FROM LIBRARY_BRANCH;

SELECT * FROM BORROWER;

UPDATE BORROWER SET Phone = '876-864-6542' WHERE Card_no = 87365;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---

---

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