Considering the relational database schema of a library management system shown below. If a book is...

 

Transcribed Image Text:

Considering the relational database schema of a library management system shown below. If a book is borrowed by a borrower, a corresponding record will be inserted in the BOOK LOANS table. Please write the following queries using relational algebra expressions (NO SCORE WILL BE GIVEN FOR SQL). BOOK Book ID Title Publisher ID Category AUTHORS Book ID Author name Gender Nationality PUBLISHER Publisher ID Publisher_name Country City LIBRARY BRANCH Branch ID Branch_name City BOOK STORAGE Book ID Branch ID Quantity BORROWERS Card NO BOOK LOANS Book ID Branch ID Card NO Date out Due_date Status Phone Name Gender City 1. Find the names of the borrowers who have book borrowing records. (4 points) 2. Retrieve the titles of the books written by the female authors. (4 points) 3. List the name of each author and total number of books written by the author. (8 points) 4. List the name of the borrowers who are currently borrowing more than three books. (Borrowing books have status of 'out' in BOOK_LOANS table.) (8 points) 5. Find the book ID of the favorite book (borrowed most) in the last year. (8 points) 6. Find the CardNo of the borrowers who have read all the books written by 'J. K. Rowling' (8 points) Considering the relational database schema of a library management system shown below. If a book is borrowed by a borrower, a corresponding record will be inserted in the BOOK LOANS table. Please write the following queries using relational algebra expressions (NO SCORE WILL BE GIVEN FOR SQL). BOOK Book ID Title Publisher ID Category AUTHORS Book ID Author name Gender Nationality PUBLISHER Publisher ID Publisher_name Country City LIBRARY BRANCH Branch ID Branch_name City BOOK STORAGE Book ID Branch ID Quantity BORROWERS Card NO BOOK LOANS Book ID Branch ID Card NO Date out Due_date Status Phone Name Gender City 1. Find the names of the borrowers who have book borrowing records. (4 points) 2. Retrieve the titles of the books written by the female authors. (4 points) 3. List the name of each author and total number of books written by the author. (8 points) 4. List the name of the borrowers who are currently borrowing more than three books. (Borrowing books have status of 'out' in BOOK_LOANS table.) (8 points) 5. Find the book ID of the favorite book (borrowed most) in the last year. (8 points) 6. Find the CardNo of the borrowers who have read all the books written by 'J. K. Rowling' (8 points)