QUESTION 4 Answer all parts Consider the following relational schema: Primary keys are highlighted and underlined authors(author_id, last_name, first_name) books(book_id, title, num_pages, author_id) branches(branch_id, branch_name,

Question

85 users unlocked this solution today!

QUESTION 4 Answer all parts Consider the following relational schema: Primary keys are highlighted and underlined authors(author_id, last_name, first_name) books(book_id, title, num_pages, author_id) branches(branch_id, branch_name, address, phone_no) copies(copy_id, branch_id, book_id, cost) loans(loan_id, copy_id, borrower_id, due_date) borrowers(borrower_id, name,member_since) Write SQL queries to answer the following: (a) Using SQL Statement create a relational schema for the above entities. Remember to consider foreign keys when creating tables. (12 marks) (b) Find the titles of books that have fewer than 10 copies. (3 marks) (c) Find the names of borrowers who have been members since 2005 and have read all the books. (4 marks) (d) Find the average number of pages in the books for each branch and order the result by increasing average page count. (4 marks) (e) For each book-branch combination, find the number of copies of the book available at the branch. Give the branch name, book title, and count in the results. (2 marks)