Make-up Project

Introduction - For this assignment, you are to create a database for a small bookstore according to the requirements specified below. To complete this assignment, you will need to create an ERD, properly labeled with relationships and attributes. You will need to implement your design in a database so that you can populate the tables with some test data and then create SQL queries that answer some questions about the store and its operations. You may use any database you choose and if you want to use one of mine, please let me know and I will set it up for you. The questions are shown at the end of this assignment. Your database design should include 5 to 6 tables and about 10 records in each. (Intersection tables may require a few more.). You should turn in your ERD and the answers to the questions shown below for credit.

Scenario - The book store wants to keep track of its inventory of books in each of its locations. The book store has 4 locations: South County, Midtown, East side and Suburban Estates. Each book may be stocked in any or all of these locations. The data currently is held in a manual system and it includes the following items for each book:

1) Book ISBN

2) Book Title

3) Book classification (science fiction, horror, mystery, psychology, philosophy, computers)

4) Book price

5) Binding: Paperback or hardback

6) Publisher name & city

Each book may be in one or more location and so the owners need to know the quantity of each book in each store. In addition, the authors of the books (some books may have more than one author) also needs to be recorded.

There are essentially 4 major entities you need to include in the database: authors (both first and last name), books, location (store) and publishers. You will need to relate each of these entities as necessary in order to capture all of the data.

Questions - Construct SQL queries to answer the following questions:

1) Provide a complete list of books. Include the ISBN, book title, author(s) first and last name and book price.

2) Create a list of store locations with the book title and quantity of books on hand in each. Include the name of the store, the book title and the number of books in each store.

3) Create a list of publishers and the books that are carried in the stores. Include the publisher name and city, the book title and the book classification.

4) What is the total value of the book store's current inventory?

5) Divide the total inventory calculated in #4 into two groups-paperbacks and hardbacks.