Library Relational Database Schema Used In Week 1

With The Library Relational Database Schema Used In Week 1s Homework

With the LIBRARY relational database schema used in week 1's homework, implement the following requests: Find the number of copies of a given book that exists at a given branch. Find the name of the book with the largest number of copies on any branch (not the total across all branches). List all the books that are out rented at the moment. List the books borrowed by a given person in the last month. List the books borrowed at the moment to customers living in a given city. Set the due date to the day after tomorrow, for a certain book borrowed from a specific branch by a particular customer. Notes: - In the above requests, a given book will be specified by its title, a given branch is specified by its branch name and a customer (borrower) is specified by his/her name. - You will need to find how to get the current date in Oracle and add or subtract days to/from it.

Paper For Above instruction

The assignment involves creating SQL statements based on the LIBRARY relational database schema to handle various queries related to library operations. These queries include retrieving specific information about books, branches, and borrowers, as well as updating due dates for borrowed books. This essay details each request and provides suitable SQL code snippets tailored for an Oracle database environment, considering Oracle's syntax for date operations and other unique features.

1. Finding the Number of Copies of a Specific Book at a Given Branch

The first task requires determining the number of copies of a specific book available at a particular branch. Assume the 'Book' table contains 'title' as the book's name, and the 'Branch' table has the 'branch_name.' The relevant relationship for copies could be in a 'Copies' or similar table, which includes 'book_id', 'branch_id', and 'number_of_copies.'

The SQL statement involves joining the 'Book' and 'Copies' tables with the 'Branch' table to filter by 'title' and 'branch_name.' Here is a typical query:

SELECT c.number_of_copies

FROM Book b


JOIN Copies c ON b.book_id = c.book_id


JOIN Branch br ON c.branch_id = br.branch_id


WHERE b.title = :given_book_title


AND br.branch_name = :given_branch_name;

Replace :given_book_title and :given_branch_name with the specific book title and branch name during execution.

2. Identifying the Book with the Largest Number of Copies on Any Branch

This request requires finding the book that has the highest number of copies at any single branch (not total across multiple branches). The SQL query should aggregate the 'Copies' data and find the maximum number for each book, then determine which book has the largest number.

SELECT b.title, c.number_of_copies

FROM Book b


JOIN Copies c ON b.book_id = c.book_id


WHERE c.number_of_copies = (


SELECT MAX(c2.number_of_copies)


FROM Copies c2


WHERE c2.book_id = b.book_id


);

This query returns all books that share the maximum count if there are ties.

3. Listing All Books Currently Rented Out

This involves querying the 'Borrow' or 'Loan' table to find books that are currently borrowed and not yet returned. Assuming 'Loan' has 'book_id', 'date_out', and 'date_in' (with NULL indicating the book is still out), the SQL looks like:

SELECT b.title, l.date_out, c.name AS borrower_name

FROM Loan l


JOIN Book b ON l.book_id = b.book_id


JOIN Customer c ON l.customer_id = c.customer_id


WHERE l.date_in IS NULL;

This returns all books out currently, along with their borrowers and checkout date.

4. Books Borrowed by a Given Person in the Last Month

The query filters loans where the borrower is the specified person and the loan start date falls within the last month. Using Oracle's CURRENT_DATE and INTERVAL syntax:

SELECT b.title, l.date_out, c.name AS borrower_name

FROM Loan l


JOIN Book b ON l.book_id = b.book_id


JOIN Customer c ON l.customer_id = c.customer_id


WHERE c.name = :borrower_name


AND l.date_out >= ADD_MONTHS(CURRENT_DATE, -1)


AND l.date_out

5. Books Borrowed by Customers Living in a Specific City

This query joins customer data with loan and book details, filtering customers by city:

SELECT b.title, l.date_out, c.name AS borrower_name, c.city

FROM Loan l


JOIN Book b ON l.book_id = b.book_id


JOIN Customer c ON l.customer_id = c.customer_id


WHERE c.city = :city_name;

6. Setting the Due Date to the Day After Tomorrow for a Borrowed Book

Assuming there is a 'Loan' or 'Borrow' table with a 'due_date' field, and 'date_out' is the date when the book was borrowed, the SQL to update the due date to the day after tomorrow (i.e., current date + 2 days) is:

UPDATE Loan l

SET l.due_date = (CURRENT_DATE + 2)


WHERE l.book_id = (SELECT book_id FROM Book WHERE title = :book_title)


AND l.branch_id = (SELECT branch_id FROM Branch WHERE branch_name = :branch_name)


AND l.customer_id = (SELECT customer_id FROM Customer WHERE name = :customer_name);

In Oracle, adding days to a date is straightforward leveraging simple addition. Ensure parameters are replaced with actual values during execution.

Conclusion

In conclusion, constructing precise SQL statements for these library-related queries involves understanding the schema relationships and Oracle's syntax for date manipulation. Proper joins, filters, and aggregate functions are essential to fetch the correct data. These queries serve as a foundation for effective database querying and management within a library context, demonstrating essential SQL proficiency for administrative tasks.

References

  • Elmasri, R., & Navathe, S. B. (2015). Fundamentals of Database Systems (7th ed.). Pearson.

Related Assignments