SQL DATABASE DESIGN
Store A is a local bookstore. Store A has been able to withstandthe challenges of larger bookstore chains, as well as the internetgiant Amazon.com, by offering new and used books and other productsin 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 includesMicrosoft Excel. The owners of these two stores would like a moreefficient way to store inventory data and report on the variousbusiness essentials. They are also preparing to offer e-commerce ontheir website. A consulting firm has recommended capturinginformation in a database and linking access to the database foreach store. The stores purchased Microsoft SQL Server to do this.The consulting firm has hired you to complete the database for thestores. In addition to creating the database, you will provide areport for your supervisor at the consulting firm; this reportshould describe the rationale for and proposed development of thedatabase. Imagine that you have interviewed the store owners andthese are the questions with which they are most concerned:
1. How many books are sold each month by the publisher? This isimportant because quantity discounts are available from thepublisher.
2. Which authors are the biggest sellers of books in our stores?This is important because the publisher offers discounts forcertain authors each month.
3. What books are associated with each publisher?
4. What are the most popular products besides books that aresold in each store? In addition to books, the stores sellmagazines, café-specific products likecoffee and pastries, andvarious gift products.
5. From what region(s) (by ZIP code) do customers visit ourstores? This is important because it will assist with futuremarketing efforts.
6. What customer data must be stored for the e-commerce portionof the website?
This will be graded using the below questions. The projectshould include the following:
– A comprehensive entity-relationship diagram (ERD)which must include an appropriate set of attributes for eachentity.
– A relational model. The relational modelshould be logical, should reflect strong understanding of theoverall function of your database, and must comply with each of therequirements laid out in the ERD.
– A functional dependencies diagram normalized into3NF. Based upon the ERD you create, devise dependencydiagrams and normalize the data into 3NF. This step will requireyou to compile a collective list of all the functional dependenciesin order to create the model. The data in this section must reflectthe list of functional dependencies and must be accuratelynormalized into 3NF.
– A functional relational schema normalized into3NF. Based upon the ERD and the dependency diagrams,create the relational schema and normalize the data into3NF.
– A list of proposed queries based on the six questionsthat most concern the bookstore owners, develop a single query thatwill answer each question.