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. (b) Find the titles of books that have fewer than 10 copies. (c) Find the names of borrowers who have been members since 2005 and have read all the books. (d) Find the average number of pages in the books for each branch and order the result by increasing average page count. (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.
Added by Rodrigo M.
Close
Step 1
Is it due to a lack of resources, capability, or other constraints? Show more…
Show all steps
Your feedback will help us improve your experience
Akash M and 98 other AP CS educators are ready to help you.
Ask a new question
Labs
Want to see this concept in action?
Explore this concept interactively to see how it behaves as you change inputs.
Key Concepts
Recommended Videos
BOOK: Book_id Title Publisher_name BOOK_AUTHORS: Book_id Author_name PUBLISHER: Name Address Phone BOOK_COPIES: Book_id Branch_id No_of_copies BOOK_LOANS: Book_id Branch_id Card_no Date_out Due_date LIBRARY_BRANCH: Branch_id Branch_name Address BORROWER: Card_no Name Address Phone
Akash M.
6.1 Consider the following universal relation that holds information about the inventory of books in a bookstore: Books (title, isbn, author, publisherName, publisherAdd, totalCopiesOrdered, copiesInStock, publicationDate, category, sellingPrice, cost) Assume: - The isbn uniquely identifies a book. (It does not identify each copy of the book, however.) - A book may have more than one author. - An author may have more than one book. - Each publisher name is unique. Each publisher has one unique address - the address of the firm's national headquarters. - Titles are not unique. - totalCopiesOrdered is the number of copies of a particular book that the bookstore has ever ordered, while copiesInStock is the number still unsold in the bookstore. - Each book has only one publication date. A revision of a book is given a new ISBN. - The category may be biography, science fiction, poetry, and so on. The title alone is not sufficient to determine the category. - The sellingPrice, which is the amount the bookstore charges for a book, is always 20 percent above the cost, which is the amount the bookstore pays the publisher or distributor for the book.
Instructions: Answer the following questions in the format specified by your instructor. 1. Figure 11–33 contains sample data for a Book table. Use this figure to answer the following: a. Is the table in first normal form (1NF)? Why or why not? b. Is the table in second normal form (2NF)? Why or why not? c. Is the table in third normal form (3NF)? Why or why not? d. Identify candidate keys for the table. Book ISBN Book Code Title Publisher Code Publisher Name 0-7895-1344-7 0180 The Stranger BP Bavant Publishing 0-8967-1544-8 079X Second Sight SB Science Books 1-1234-2334-5 1351 Travel Mysteries MP Mysterious Press 1-4188-3635-4 3350 Dawn BP Bavant Publishing Figure 11–33 2. Figure 11–34 contains sample data for books and stores. In discussing the data with users, you find that book codes — but not titles — uniquely identify books and that location names uniquely identify store location. Multiple book stores can stock the same book. a. Convert the data in Figure 11–34 into a relation in first normal form (1NF) using the shorthand representation used in this chapter. b. Identify all functional dependencies using the notation demonstrated in this chapter. 3. Using only the data in Figure 11–33, how could you identify the entities and attributes that would be the starting point for a database design? Book Book Code Title Location Name Address Number of Copies 0180 The Stranger Phil Downtown Georgetown 2 Phil Brentwood Brentwood 1 079X Second Sight Phil Downtown Georgetown 4 Phil Brentwood Brentwood 3 Phil Eastshore Georgetown 5 Figure 11–34
Recommended Textbooks
Computer Science and Information Technology
Introduction to Programming Using Python
Computer Science - An Overview
Transcript
18,000,000+
Students on Numerade
Trusted by students at 8,000+ universities
Watch the video solution with this free unlock.
EMAIL
PASSWORD