Create A Database

Books’R’Us is a local bookstore that started in 1978 in a small New England town. Books’R’Us has been able to withstand the challenges of larger bookstore chains, as well as the internet giant Amazon.com, by offering new and used books and other products in a comfortable location that includes a café with free WiFi. Books’R’Us recently purchased another local bookstore called Great Books, USA, from an owner who is retiring. Both businesses use a simple bookkeeping system that includes Microsoft Excel. The owners of Books’R’Us, Tom and Sarah Smith, would like a more efficient way to store inventory data and report on the various business essentials. They are also preparing to offer e-commerce on their website. A consulting firm has recommended capturing information in a database and linking access to the database for each store. Tom and Sarah purchased Microsoft SQL Server to do this. The consulting firm has hired you to complete the database for Books’R’Us.

Imagine that you have interviewed the store owners, Tom and Sarah Smith, and these are the questions with which they are most concerned: 1. How many books are sold each month by the publisher? This is important because quantity discounts are available from the publisher. 2. Which authors are the biggest sellers of books in our stores? This is important because the publisher offers discounts for certain authors each month. 3. What books are associated with each publisher? 4. What are the most popular products besides books that are sold in each store? In addition to books, the stores sell magazines, café-specific products like coffee and pastries, and various gift products. 5. From what region(s) (by ZIP code) do customers visit our stores? This is important because it will assist with future marketing efforts. 6. What customer data must be stored for the e-commerce portion of the website?

Prompt Your database should answer the following prompt: Create a database that meets the needs of the store owners. Specifically, the following critical elements must be addressed: I. Database: You will use your entity-relationship diagram, relational model, functional dependencies diagram, and functional relational schema to inform the creation of your database. These will be submitted as part of your report. A. Tables: Create at least four tables that will display the data that is important to the store owners. Identify the primary and foreign keys that are necessary to relate the tables. Include screenshots that show the tables you created. B. Import Data: Use SQL statements to import the data into the tables you just created. You will come up with each of your data sets yourself. You should have at least five records for each table. Include screenshots that show the populated tables annotated with the SQL statements that you used. C. Queries: After you have populated the tables, write queries to extract the data to answer the owner’s questions. Include screenshots that show each query and the corresponding query results.

The database should consist of these tables : Publishers, Authors, Customers, Books, Merchandise, Orders, Store Number.

Publisher table should consist of: publisher number, publisher name, ISBN(s)

Author table should consist of: Author number, last name, first name, genre, ISBN(s), orders

Customer table should consist of: Customer number, last name, first name, email, address, orders

Book table should consist of: ISBN(s), title, last name, first name, genre, orders

Merchandise table should consist of: UPC, orders

Orders table should consist of: UPC/ISBN(s), cost, customer number, author number, publisher number, store number

Store number table should consist of: store number