DOC PREVIEW
GSU CIS 8040 - Lab 2 Assignment

This preview shows page 1 out of 2 pages.

Save
View full document
View full document
Premium Document
Do you want full access? Go Premium and unlock all 2 pages.
Access to all documents
Download any document
Ad free experience
Premium Document
Do you want full access? Go Premium and unlock all 2 pages.
Access to all documents
Download any document
Ad free experience

Unformatted text preview:

Lab 2Given the following SQL DDL statements for declaring the LIBRARY relational database schema. 1) Create and populate the tables in Oracle. 2) Write the SQL queries for the LIBRARY database listed below the DDL statements (Letters a-g). Upload these queries in a .zip file under the menu choice for "Lab 2 - Upload Here"; but first run them against your database...make sure you include the necessary attribute values when you insert "dummy" data into the tables. CREATE TABLE BOOK ( BookId CHAR(20) NOT NULL,Title VARCHAR(30) NOT NULL,PublisherName VARCHAR(20),PRIMARY KEY (BookId),FOREIGN KEY (PublisherName) REFERENCES PUBLISHER (PublisherName) ); CREATE TABLE BOOK_AUTHORS ( BookId CHAR(20) NOT NULL,AuthorName VARCHAR(30) NOT NULL,PRIMARY KEY (BookId, AuthorName),FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE CASCADE ); //PublisherName was added, as it was referenced by another table but did not exist as an attribute.CREATE TABLE PUBLISHER ( PublisherName VARCHAR(20) NOT NULL,Address VARCHAR(40) NOT NULL,Phone CHAR(12),PRIMARY KEY (PublisherName) ); CREATE TABLE BOOK_COPIES ( BookId CHAR(20) NOT NULL,BranchId INTEGER NOT NULL,No_Of_Copies INTEGER NOT NULL,PRIMARY KEY (BookId, BranchId),FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE CASCADE,FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId)ON DELETE CASCADE); CREATE TABLE BORROWER ( CardNo INTEGER NOT NULL,Name VARCHAR(30) NOT NULL,Address VARCHAR(40) NOT NULL,Phone CHAR(12),PRIMARY KEY (CardNo) ); CREATE TABLE BOOK_LOANS ( CardNo INTEGER NOT NULL,BookId CHAR(20) NOT NULL,BranchId INTEGER NOT NULL,DateOut DATE NOT NULL,DueDate DATE NOT NULL,PRIMARY KEY (CardNo, BookId, BranchId),FOREIGN KEY (CardNo) REFERENCES BORROWER (CardNo)ON DELETE CASCADE,FOREIGN KEY (BranchId) REFERENCES BRANCH (BranchId)ON DELETE CASCADE,FOREIGN KEY (BookId) REFERENCES BOOK (BookId)ON DELETE SET NULL ); CREATE TABLE BRANCH ( BranchId INTEGER NOT NULL,BranchName VARCHAR(20) NOT NULL,Address VARCHAR(40) NOT NULL,PRIMARY KEY (BranchId) ); (a) How many copies of the book titled The Lost Tribe are owned by the library branchwhose name is "Sharpstown"? (b) How many copies of the book titled The Lost Tribe are owned by each librarybranch? (c) Retrieve the names of all borrowers who do not have any books checked out. (d) For each book that is loaned out from the "Sharpstown" branch and whose DueDateis today, retrieve the book title, the borrower's name, and the borrower's address. (e) For each library branch, retrieve the branch name and the total number of books loaned out from that branch. (f) Retrieve the names, addresses, and number of books checked out for all borrowerswho have more than five books checked out. (g) For each book authored (or co-authored) by "Stephen King", retrieve the title andthe number of copies owned by the library branch whose name is


View Full Document

GSU CIS 8040 - Lab 2 Assignment

Download Lab 2 Assignment
Our administrator received your request to download this document. We will send you the file to your email shortly.
Loading Unlocking...
Login

Join to view Lab 2 Assignment and access 3M+ class-specific study document.

or
We will never post anything without your permission.
Don't have an account?
Sign Up

Join to view Lab 2 Assignment 2 2 and access 3M+ class-specific study document.

or

By creating an account you agree to our Privacy Policy and Terms Of Use

Already a member?