I need help with this DBMS assignment. It has to work with oracle. Use the datab
ID: 3888254 • Letter: I
Question
I need help with this DBMS assignment. It has to work with oracle.
Use the database schema, primary keys are bold.
Book(bookID, ISBN, title, author, publish-year, category)
Member(memberID, lastname, firstname, address, phone-number, limit)
CurrentLoan(memberID, bookID, loan-date, due-date)
History(memberID, bookID, loan-date, return-date)
Members can borrow books from the library. The number of books they can borrow is limited by the “limit” field of the Member relation (it may differ for different members). The category of a book includes fiction, non-fiction, children’s and reference. The CurrentLoan table represents the information about books that are currently checked out. When the book is returned to the library, the record will be removed from CurrentLoad relation, and will be inserted into History relation with the return-date. A library may have more than one copy of the same book, in which case each copy has its own bookID, but all copies share the same ISBN.
Write SQL statements for each of the following questions.
(1)(16 pts) Create all the relations listed above. Make sure to indicate the primary key and the foreign keys (if any) in your statements.
(2)(10 pts) Insert at least 5 members, 10 books, and enough tuples in the CurrentLoan and History relation. Add tuples as needed to be able to test the following queries for different test cases.
(3)(8 pts) Find the book ID, title, author, and publish-year of all the books with the words “XML” and “XQuery” in the title. These two keywords can appear in the title in any order and do not have to be next to each other. Sort the results by publish year in descending order.
(4)(8 pts) Find the book ID, title, and due date of all the books currently being checked out by John Smith.
(5)(8 pts) Find the member ID, last name, and first name of the members who have never borrowed any books in the past or currently.
Explanation / Answer
Solution:
1)
CREATE TABLE Member(
memberID INT NOT NULL,
lastname CHAR (20) NOT NULL,
firstname CHAR (25) ,
address varchar(50),
phone-number INT (20),
limit INT(10),
PRIMARY KEY (memebrID, lastname, firstname)
);
CREATE TABLE CurrentLoan(
memberID INT NOT NULL,
bookID INT NOT NULL,
loan-date DATE
due-date DATE
PRIMARY KEY (memebrID, bookID),
FOREIGN KEY (bookID) REFERENCES Book(bookID)
);
CREATE TABLE History(
memberID INT NOT NULL,
bookID INT NOT NULL,
loan-date DATE
return-date DATE
PRIMARY KEY (memebrID, bookID, loan-date),
FOREIGN KEY (bookID, memberID) REFERENCES Book(bookID), Member(memberID)
);
2)
we can use INSERT INTO command to insert the values in the respective table, the sytax goes like this,
INSERT INTO Book
VALUES (value1, value2, value3, ...);
3)
SELECT bookID, title, author, publish-year FROM Book WHERE title= "XML" AND title= "XQuery"
4)
SELECT bookID, title, due-date FROM Book INNER JOIN CurrentLoan ON Book.bokID= CurrentLoan.bookID WHERE lastname="smith" AND firstname= "John"
5)
SELECT memberID, lasname, firstname FROM Member WHERE loan-date= null
I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)