Answer Happy

Part B: SQL (40 Marks) LibraryDB is a database system that keeps track of information concerning the books and their circulation in an imaginary library. Disclaimer: The data that populates the database are artificially constructed and by no means correspond to actual real-world data. The schema for the LibraryDB database is given below. borrow(transactionID, personlD*, borrowdate, duedate, returndate) author(authorID, firstname, middlename, lastname) book_copy(bookID, bookdescID*) book(bookdescID, title, subtitle, edition, voltitle, volnumber,language, place, year, isbn, dewey, subjectID*) borrow_copy(transactionlD*, bookID*) person(personlD, firstname, middlename, lastname, address, city, postcode, phonenumber, emailaddress, studentno, idcardno) publisher(publisherID, publisherfullname) written_by(bookdesclD*, authorlD*, role) published_by(bookdescID*, publisherlD*, role) subject(subjectID, subjecttype) The primary keys are underlined. The foreign keys are denoted by asterisks (*). . Description of the schema person -- keeps track of the people who borrow books from the library. The attributes contain personal and contact information. author -- keeps track of personal information about authors. publisher -- keeps track of the publisher information. To make simple, most of the attributes have been truncated in the sample database. subject -- this relation keeps information about the subjects on which the library collection have books (such as Mathematics, Database, etc) book -- contains information about the books that are available in the library. Every book can have one or more physical copies in the collection. Each book can have one or more authors and it is published by one or more publishers. book_copy -- keeps track of the physical copies of the books in the library collection. borrow -- keeps track of the check-ins and check-outs of the books. Every transaction is done by one person, however may involve with one or more book copies. If there is no return date, it means the book has been checked out but not returned. written_by -- associates books with authors. A book may be associated with several authors and an author may be associated with several books. There is also an attribute 'role' that specifies the role of the author for the book (author/ editor/ translator/ etc). published_by -- associates publishers with books. There is an attribute 'role' here too.

. borrow_copy -- associates physical copies of books with a transaction. Members are allowed to borrow several books in a single transaction. A conceptual data model (shown as an entity-relationship diagram) which represents these data is given below. Author Role BookCopy 1..N 0..N authorID PK) firstname middlename lastname 1..1 bookID (PK} Book 1..N Subject O.N bookdescID {PK} title subtitle edition 0..N voltitle volnumber O..N 1..1 Borrow subjectID (PK} subjecttype language place ON year isbn dewey transactionID (PK) borrowdate duedate returndate Publisher O..N 1..N publisherID (PK) publisherfullname 1..1 Role Person personID {PK) firstname middlename lastname address city postcode phonenumber emailaddress studentno idcardno If you wish to do this part of the assignment from home, you can install SQLite Studio. The instructions for installing, configuring and using SQLite Studio is provided in the Week 3 Labsheet. Also included is the pre-built Library database in SQLite format (Library.db), available for downloading at: https://rmit.instructure.com/courses/90 ... s/23138028.

Write SQL queries for the following tasks. 1. Display all the information of books published after 1980. 2. Display the first name and last name of the persons who translated books on the subject "IMAGE PROCESSING". a. Write your query using a sub query. b. Write your query using JOINS. 3. Who wrote the book "COMPUTER SCIENCE "? Display the first name, middle names, and last name of the author. Each author's role in the writing of the book is described in "role” attribute in written by table. 4. Display the titles of books that are not currently being borrowed. Page 4 of 7

5. A borrower wants to borrow the book titled "COMPUTING METHODS", but all of its copies are already borrowed by others. Write two queries to display other recommended titles using the following methods. a. Using partial matching of the book title note that the borrower is interested in a "COMPUTING" book. b. By searching of other books written by the same author (i.e. the author of " DATA PROCESSING CONCEPTS" --- 6. Display the list of publishers who have published books on the subject "COMPUTING METHODS". Your query should display publisher's full name. 7. Display the name of authors who have never written or translated any books. a. Write your query using OUTER JOINS. b. Write the query again without using OUTER JOINS. 8. Display full names of publishers with whom the author Alfred Aho did not publish his book(s). Your query must use EXISTS/NOT EXISTS clause. 9. Display the first name and last name of authors who had written more than 3 books. Along with each name, display the number of books as well. 10. Display the name of the author who had written most books. If there are more than one authors with the highest number of written books, show them all. Your query should show the names. Provide detailed answers to the following questions.

11. According to this database schema, it is assumed that all books borrowed in one transaction are due at the same time and should be returned at the same time. However, this is an unreasonable assumption and quite contrary to the common practice across all forms of libraries. The books may be due on different days due to the renew activity, and they can be returned separately no matter if they have the same due date. Provide the required changes to the database schema to accommodate this requirement. Your answer should include a portion of the ER model where the changes are applied and the updated relational database schema. If required, you may add new relations and attributes and also new foreign keys. 12. Libraries allow customers to place holds onto book, but not borrow it immediately. It can be borrowed by the customer who places the hold within one week. A customer can place hold onto a book unavailable and is put into a holds queue. When the book becomes available, it can be borrowed by the customer who places the hold first (i.e. the customer at the front of the queue). If the customer has not borrowed a book after holding it for one week, this book will be released for others to borrow. This database schema cannot keep information on holding of books. Extend this schema to accommodate this requirement. Your answer should include a portion of the ER model where the changes are applied and the updated relational database schema. If required, you may add new relations and attributes and also new foreign keys. 13. The library allows customers to renew the books in one transaction separately, and each book can be renewed for at most twice. Can this database schema support this operation? If so, how are they handled? If not, what changes required to the database schema? Your answers should include a portion of the ER model where the changes are applied and the updated rela nal database schema. If required, you may add new relations and attributes and also new foreign keys.