Instruction: Design, develop, and implement the specified queries for the following problems. Experiment: Consider the following schema
Question:
Instruction: Design, develop, and implement the specified queries for the following problems.
Experiment:
Consider the following schema for a Library Database:
BOOK(Book_id, Title, Publisher_Name, Pub_Year)
BOOK_AUTHORS(Book_id, Author_Name)
PUBLISHER(Name, Address, Phone)
BOOK_COPIES(Book_idu, Programme_id, No-of_Copies)
BOOK_LENDING(Book_id, Programme_id, Card_No, Date_Out, Due_Date)
LIBRARY_PROGRAMME(Programme_id, Programme_Name, Address)
Write SQL queries to-
1.Retrieve details of all books in the library – id, title, name of publisher, authors, number of copies in each Programme, etc.
2.Get the particulars of borrowers who have borrowed more than 3 books, but from Jan 2020 to Jun 2021.
2.Delete a book in the BOOK table. Update the contents of other tables to reflect this data manipulation operation.
4.Partition the BOOK table based on year of publication. Demonstrate its working with a simple query.
5.Create a view of all books and the number of copies that are currently available in the Library.
Fundamentals of Database Systems
ISBN: 978-0136086208
6th edition
Authors: Ramez Elmasri, Shamkant Navathe