Description
In Module Four, submit a systems analysis for your project, including a comprehensive entity-relationship diagram (ERD). Although you may use any tool to complete the ERD, it is strongly recommended that you use Microsoft Visio and then save the diagram into an image format (GIF or JPG) and then copy and paste the image into your systems analysis.
All sections of this milestone should be compiled into a single Word document. Furthermore, all of Milestone One should be included in Milestone Two.
Remember, this activity is based upon the scenario from your final project:
Scenario
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. 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, 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?
This milestone will be graded using the Milestone Two Rubric found on the following page. The milestone should include the following:
ï‚· A comprehensive entity-relationship diagram (ERD)
o This must include an appropriate set of attributes for each entity.
ï‚· A relational model
o Your 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
o Based upon the ERD you create, devise dependency diagrams and normalize the data into 3NF.
o This step will require you to compile a collective list of all the functional dependencies in order to create the model.
o 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
o Based upon the ERD and the dependency diagrams, create the relational schema and normalize the data into 3NF.
ï‚· A list of proposed queries
o Based on the six questions that most concern the bookstore owners, develop a single query that will answer each question.
Rubric
Guidelines for Submission: Written components of the project must follow these formatting guidelines when applicable: double spacing, 12-point Times New
Roman font, one-inch margins, and discipline-appropriate citations.
Instructor Feedback: This activity uses an integrated rubric in Blackboard. Students can view instructor feedback in the Grade Center. For more information,
review these instructions.
Critical Elements Exemplary (100%) Proficient (85%) Needs Improvement (55%) Not Evident (0%) Value
Entity-Relationship
Diagram (ERD)
ERD is logical, comprehensive,
and well-designed, includes all
of the design considerations and
requirements discussed in the
Milestone One submission, and
includes an appropriate set of
attributes for each entity
ERD is logical and complete and
includes most of the design
considerations and
requirements discussed in the
Milestone One submission. It
includes an appropriate set of
attributes for most or all of the
entities
ERD is logical but lacking some
of the important design
considerations and
requirements discussed in the
Milestone One submission. It
includes an appropriate set of
attributes for some of the
entities
ERD lacks logic or is incomplete.
Several of the design
considerations and
requirements discussed in the
Milestone One submission are
missing. Several of the entities
are missing the appropriate
attributes
20
Relational Model Relational model is logical and
reflects a strong understanding
of the overall function of your
database. Relational model
complies with each of the
requirements laid out in the
ERD
Relational model is logical and
reflects understanding of the
overall function of your
database. Relational model
complies with most of the
requirements laid out in the
ERD
Relational model is logical and
cohesive. Relational model
complies with some of the
requirements laid out in the
ERD
Relational model lacks cohesion,
logic, or both. Relational model
fails to comply with most or all
of the requirements laid out in
the ERD
20
Functional
Dependencies Model
Accurately normalized into 3NF Fails to normalize to 3NF but
sufficiently normalizes to 2NF
Fails to normalize to 3NF or 2NF.
Normalized to 1NF or below
Fails to normalize 20
FunctionalRelational
Schema
Accurately normalized into 3NF Fails to normalize to 3NF but
sufficiently normalizes to 2NF
Fails to normalize to 3NF or 2NF.
Normalized to 1NF or below
Fails to normalize 20
Queries Accurately writes all queries to
answer owners’ questions
Accurately writes most queries
to answer owners’ questions
Queries written do not answer
owners’ questions
Queries not provided 20
Earned Total 100%
