SQL DATABASE DESIGN
Store A is a local bookstore. Store A has been able to withstand the challenges of larger bookstore chains, as well as the internet giant Amazon, by offering new and used books and other products in a comfortable location that includes a café with free WiFi. Store A recently purchased another local bookstore called Store B.
Both businesses use a simple bookkeeping system that includes Microsoft Excel. The owners of these two stores 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. The stores purchased Microsoft SQL Server to do this. The consulting firm has hired you to complete the database for the stores.
In addition to creating the database, you will provide a report for your supervisor at the consulting firm; this report should describe the rationale for and proposed development of the database. Imagine that you have interviewed the store owners 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 likecoffee 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?
This will be graded using the below questions. The project should include the following:
- A comprehensive entity-relationship diagram (ERD) which must include an appropriate set of attributes for each entity.
- A relational model. The relational model should be logical, should reflect strong understanding of the overall function of your database, and must comply with each of the requirements laid out in the ERD.
- A functional dependencies diagram normalized into 3NF. Based upon the ERD you create, devise dependency diagrams and normalize the data into 3NF. This step will require you to compile a collective list of all the functional dependencies in order to create the model. The data in this section must reflect the list of functional dependencies and must be accurately normalized into 3NF.
- A functional relational schema normalized into 3NF. Based upon the ERD and the dependency diagrams, create the relational schema and normalize the data into 3NF.
- A list of proposed queries based on the six questions that most concern the bookstore owners, develop a single query that will answer each question.