Differentiate between database systems and file systems by comparing features and functions to determine benefits in data management

Databases are increasingly important in the information age, as they provide a means to organize, track, manage, and use information easily and efficiently. Thousands of companies depend on databases to record and update data. Databases have a wide variety of uses, from tracking climate measurements worldwide to storing library research information to organizing user data for businesses and organizations.

 

For this assessment, you will solve an information storage problem. The owner of two local bookstores wants to consolidate individual store inventories. You will organize the store inventory data into workable tables and apply normalization techniques to create a single common SQL-based database that can be shared between the stores. In addition, you will submit a report explaining the choices you made in the creation of your database. The report will also include an entityrelationship diagram and other models as well as a description of the database design features.

 

Some components of this final project will have been developed earlier in the course and submitted for instructor feedback. You will submit them a second time as part of this final project, incorporating the feedback that you received from your instructor. This project is divided into two milestones, which will be submitted at various points throughout the course to scaffold learning and ensure quality final submissions.

 

This assessment addresses the following course outcomes:

 

  • Differentiate between database systems and file systems by comparing features and functions to determine benefits in data management
  • Analyze business information storage problems for depicting suitable information model solutions using entity relation diagrams
  • Apply database normalization theory to relational data models for defining relationships and minimizing redundancy
  • Develop solutions to query and data update problems using a Structured Query Language interface to create, populate, maintain, and query databases

 

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?

 

Prompt

Your database and report should answer the following prompt: Create a database that meets the needs of the store owners. In addition to the database, you will submit a report that includes several models and diagrams that you completed earlier in the course to inform your database creation; you will resubmit these models and diagrams after incorporating your instructor’s feedback.

 

Specifically, the following critical elements must be addressed:

 

  1. 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.
  2. 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.
  3. 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.
  4. 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.

 

  1. Report: Although some parts of the report will be completed before or during the creation of the database, the report as a whole will be written after you have created the database.
  2. Overview: Provide a concise overview of the problem you are solving and the database that you created to address the problem.
  3. Justification: Analyze the business information storage problem you are solving. Why is the database the appropriate solution to the problem?
  4. Benefits: What benefits in data management does the database you created have over a file system like the one the stores were previously using? What features and functions does a database have that a file system does not?
  5. Features: Describe each of the major features of the database you created. Why are these features required?
  6. Design: How did you design the database to manage the stores’ data? Discuss the tables you created and their relationships, including in your discussion the table name, attributes (field names and data types), and keys (primary and foreign). Consider including a diagram to support your response to this question.
  7. Entity-Relationship Diagram(ERD): Develop an entity-relationship diagram that has an appropriate set of attributes for each entity.
  8. Relational Model: Develop a relational model that is logical and complies with the requirements in your ERD.
  9. Functional Dependencies Diagram: Based on your ERD, develop a functional dependencies diagram that accurately normalizes the data into third normal form (3NF).
  10. Functional-Relational Schema: Based on your ERD and dependencies diagram, develop a relational schema that accurately normalizes the data into 3NF.

 

 

Milestones

Milestone One: Project Proposal

You will submit a project proposal and its overview as a Word document. This milestone is graded with the Milestone One Rubric.

 

Milestone Two: Systems Analysis

You will submit a systems analysis for your project, including a comprehensive entity-relation 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. This milestone is graded with the Milestone Two Rubric.

 

Final Project SubmissionDatabase and Report

You will submit your database and report. Your database and report should answer the following prompt: Create a database that meets the needs of the store owners. In addition to the database, you will also submit a report that includes several models and diagrams that you completed earlier in the course to inform your database creation; you will now resubmit these models and diagrams after incorporating your instructor’s feedback. This submission is graded with the Final Project Rubric.

 

 

 

Final Project Rubric

Guidelines for Submission: Your report should adhere to the following formatting requirements: 5–7 pages, double-spaced, using 12-point Times New Roman font and one-inch margins.

 

 

Critical Elements Exemplary (100%) Proficient (85%) Needs Improvement (55%) Not Evident (0%) Value
Tables Meets “Proficient” criteria and demonstrates nuanced understanding of table relationships Creates at least four tables that will display the data that is important to the store owners and accurately identifies the primary and foreign keys necessary to relate the tables Creates at least four tables and identifies primary and foreign keys, but with gaps in accuracy, relevance, or detail Does not create at least four tables 7.92
Import Data Meets “Proficient” criteria and demonstrates nuanced understanding of SQL Accurately uses SQL statements to import data to tables, displaying at least five records per table Uses SQL statements to import data to tables, displaying at least five records per table, but with gaps in accuracy or detail Does not use SQL statements to import data to tables 7.92
Queries Meets “Proficient” criteria and demonstrates nuanced understanding of SQL Writes queries that accurately extract the data to answer owners’ questions Writes queries that extract data, but with gaps in accuracy, relevance, or detail Does not write queries that extract the data to answer owners’ questions 7.92
Overview Meets “Proficient” criteria, and overview is exceptionally clear and contextualized Provides concise overview of problem and database that was created to address problem Provides overview of problem to be solved and database that will be created to address problem, but overview is wordy or vague Does not provide overview of problem to be solved and database that will be created to address problem 7.92
Justification Meets “Proficient” criteria, and analysis is exceptionally clear and

contextualized

Thoroughly analyzes the business information storage problem and explains why the database is the appropriate solution to the problem Analyzes the business information storage problem and explains why the database is the appropriate solution to the problem, but is cursory or inaccurate Does not analyze the business information storage problem and does not explain why the database is the appropriate solution to the problem 7.92
Benefits Meets “Proficient” criteria and demonstrates nuanced understanding of differences between databases and file systems Comprehensively describes benefits, features, and functions of databases compared to file systems Describes benefits, features, and functions of databases compared to file systems, but with gaps in accuracy or detail Does not describe benefits, features, and functions of databases compared to file systems 7.92
Features Meets “Proficient” criteria and demonstrates nuanced understanding of database

features

Comprehensively describes major features of database and explains why they are required Describes major features of database and explains why they are required, but with gaps in accuracy or detail Does not describe major features of database and does not explain why they are required 7.92
Design Meets “Proficient” criteria and discussion is supported with diagram(s) Thoroughly describes database design, tables, and relationships, supporting discussion with table names, attributes, and keys Describes database design, tables, and relationships, but description is cursory, inaccurate, or not supported with table names, attributes, and keys Does not describe database design, tables, and relationships 7.92
Entity-Relationship Diagram Meets “Proficient” criteria and demonstrates nuanced understanding of entities, attributes, and relationships Develops entity-relationship diagram that has appropriate set of attributes for each entity Develops entity-relationship diagram, but not all attributes are appropriate for entities Does not develop entity- relationship diagram 7.91
Relational Model  Meets “Proficient” criteria and demonstrates strong

understanding of overall function of database

Develops logical relational model that complies with ERD requirements Develops relational model, but there are gaps in logic or compliance with ERD requirements Does not develop relational model 7.91
Functional

Dependencies Diagram

Meets “Proficient” criteria and demonstrates nuanced understanding of relationship between database normalization theory and relational data models Develops a functional dependencies diagram that accurately normalizes data into

3NF, based on ERD

Develops a functional dependencies diagram that normalizes data into 3NF, but it is not based on ERD or has gaps in accuracy Does not develop a functional dependencies diagram 7.91
Functional-Relational Schema Meets “Proficient” criteria and demonstrates nuanced understanding of relationship between database normalization theory and relational data models Creates relational schema that accurately normalizes data into 3NF, based on ERD and dependencies diagram Creates relational schema that normalizes data into 3NF but is not based on ERD or dependencies diagram or has gaps in accuracy Does not create relational schema 7.91
Articulation of

Response

 

Submission is free of errors related to citations, grammar, spelling, syntax, and organization and is presented in a professional and easy-to-read format Submission has no major errors related to citations, grammar, spelling, syntax, or organization Submission has major errors related to citations, grammar, spelling, syntax, or organization that negatively impact readability and articulation of main ide

Last Completed Projects

# topic title discipline academic level pages delivered
6
Writer's choice
Business
University
2
1 hour 32 min
7
Wise Approach to
Philosophy
College
2
2 hours 19 min
8
1980's and 1990
History
College
3
2 hours 20 min
9
pick the best topic
Finance
School
2
2 hours 27 min
10
finance for leisure
Finance
University
12
2 hours 36 min