+61480015851
BIT231: Databases Systems- Melbourne Polytechnic- Bookstore Database Scenario- Database Assignment Help
Download Solution
Order New Solution
Internal Code: 1HDJH
More information on each table:
1. Publisher: A list of companies that publish books. The Primary Key is publisher_code
2. Book: Each record in this table stores a book release, NOT a list of individual copies (e.g. it would list First Among Equals once, not one record for each of its 12 copies of First Among Equals). Its Primary Key is bookcode and its Foreign Key is publisher_code
3. Author: A list of authors. Its Primary Key is author_no and it is an AUTO_INCREMENT field
4. Branch: A branch is a book store. Its Primary Key is branch_no
5. Wrote: Given that one book can be written by many authors and an author can write many books, this table servers as the association between the book and author tables. The Sequence attribute indicates the order in which the authors of a particular book are listed on the cover. Its Primary Key is a composite key – on bookcode and author_no – and these 2 fields also serve as Foreign Keys
6. Invent (Inventory): A customer can find a book title at many store locations and a store will have many books on its shelves, and so this table makes it possible to show how many copies of a book each branch holds on its shelves. Its Primary Key is a composite key – bookcode and author_no – and these 2 fields also serve as Foreign Keys
Part A
1. CREATE a table for every entity represented on the ER Diagram
a. Choose data types carefully
b. Use NOT NULL appropriately
c. Use the DEFAULT clause on at least one appropriate column
d. Set the referential integrity constraints ON UPDATE and ON DELETE
2. Create INSERT statements to populate the publisher, book, author, & wrote tables with:
e. One publisher - Allison and Busby. Find their details on the Web.
f. Two Authors. One of which should be you (that is, use your own name and details for one of the Authors) and the other should be an Author named Mark Twain.
g. One book called "Winning Ways", written by you and published by Allison and Busby
3. Locate the "BooksGalore records.sql" file on Moodle and insert all the records in that file into the appropriate tables
4. Use an update query to change the price of any fiction book that has a current price of 3.95 to 5.50. Fiction books are marked with the category FIC.
5. Delete all publishers that have the publisher code BB.
Part B
6. Retrieve all the data in the Author table
7. Retrieve the price of the Book "Stranger"
8. List in alphabetical order the names of all books that have a price greater than $5.50
9. List the names of publishers not located in New York city (That is: New York, NY).
10. List all the different book types. Avoid duplicates
11. List the book code and title of every book that has the type FIC, MYS or ART.
Part C
12. List the names and addresses of branches that have between 10 and 15 employees
13. Find all publishers with a "." (that is, a dot) in their name
14. Find all books that have the word "the" in their title
15. List the authors who published "Higher Creativity"
16. List the book titles that were published in the state of "NY"
17. List the books written by each author. Show only the author last name and the book title
18. List the books written by Agatha Christie
19. Determine which book has the most copies on hand and which book has the least. Show the name of each book and how many copies there are.
20. List the book code and title of every book that has the type "FIC" or that has been published by a publisher with a code of BB".
21. Calculate the average price for each type of book and display the type and average.
22. For every book published by "Addison Wesley", list the book title and book price.
23. Members get a 10% discount off regular book prices. Determine the discounted price of every book and display the book title, original price, and discounted price. Tip: You can calculate 90% of the current price or subtract 10% from the original price.
Database Assignment Help:
Task: Bookstore Database Scenario The BooksGalore database gathers and organises information about its stores and the books it stocks. Here is the ER Diagram of the BooksGalore database:
More information on each table:
1. Publisher: A list of companies that publish books. The Primary Key is publisher_code
2. Book: Each record in this table stores a book release, NOT a list of individual copies (e.g. it would list First Among Equals once, not one record for each of its 12 copies of First Among Equals). Its Primary Key is bookcode and its Foreign Key is publisher_code
3. Author: A list of authors. Its Primary Key is author_no and it is an AUTO_INCREMENT field
4. Branch: A branch is a book store. Its Primary Key is branch_no
5. Wrote: Given that one book can be written by many authors and an author can write many books, this table servers as the association between the book and author tables. The Sequence attribute indicates the order in which the authors of a particular book are listed on the cover. Its Primary Key is a composite key – on bookcode and author_no – and these 2 fields also serve as Foreign Keys
6. Invent (Inventory): A customer can find a book title at many store locations and a store will have many books on its shelves, and so this table makes it possible to show how many copies of a book each branch holds on its shelves. Its Primary Key is a composite key – bookcode and author_no – and these 2 fields also serve as Foreign Keys
Part A
1. CREATE a table for every entity represented on the ER Diagram
a. Choose data types carefully
b. Use NOT NULL appropriately
c. Use the DEFAULT clause on at least one appropriate column
d. Set the referential integrity constraints ON UPDATE and ON DELETE
2. Create INSERT statements to populate the publisher, book, author, & wrote tables with:
e. One publisher - Allison and Busby. Find their details on the Web.
f. Two Authors. One of which should be you (that is, use your own name and details for one of the Authors) and the other should be an Author named Mark Twain.
g. One book called "Winning Ways", written by you and published by Allison and Busby
3. Locate the "BooksGalore records.sql" file on Moodle and insert all the records in that file into the appropriate tables
4. Use an update query to change the price of any fiction book that has a current price of 3.95 to 5.50. Fiction books are marked with the category FIC.
5. Delete all publishers that have the publisher code BB.
Part B
6. Retrieve all the data in the Author table
7. Retrieve the price of the Book "Stranger"
8. List in alphabetical order the names of all books that have a price greater than $5.50
9. List the names of publishers not located in New York city (That is: New York, NY).
10. List all the different book types. Avoid duplicates
11. List the book code and title of every book that has the type FIC, MYS or ART.
Part C
12. List the names and addresses of branches that have between 10 and 15 employees
13. Find all publishers with a "." (that is, a dot) in their name
14. Find all books that have the word "the" in their title
15. List the authors who published "Higher Creativity"
16. List the book titles that were published in the state of "NY"
17. List the books written by each author. Show only the author last name and the book title
18. List the books written by Agatha Christie
19. Determine which book has the most copies on hand and which book has the least. Show the name of each book and how many copies there are.
20. List the book code and title of every book that has the type "FIC" or that has been published by a publisher with a code of BB".
21. Calculate the average price for each type of book and display the type and average.
22. For every book published by "Addison Wesley", list the book title and book price.
23. Members get a 10% discount off regular book prices. Determine the discounted price of every book and display the book title, original price, and discounted price. Tip: You can calculate 90% of the current price or subtract 10% from the original price.