About this Assignment
In this course, you learned about relational database models and data normalization, covering concepts like the relationship between tables and first and third normal forms.
For this assignment, you will clean up an existing database that has lost its data integrity. You will need to identify required adjustments to the fields, tables, and relationships within the database, as well as write SQL statements to clean up the data.
Prompt
In this project, you are taking on the role of a database administrator. You have been contracted by the owner of a hobby store to fix her database, which holds purchases, customers, orders, and products. Currently, the data is a disorganized and contains duplicate and inaccurate data in almost every table.
See the Assignment Data at the bottom of the prompt. There are three main parts to this project:
For this assignment, you will submit a 500-750-word final report that will document your process and explains the decisions you made, in addition to containing the updated tables and the SQL statements needed to update the tables.
Part 1 – Remove Redundancies & Fix Errors
Identify the tables that contain fields that are redundant or inaccurate. For example, should a table that stores book information also store the author’s home address? Or should that belong in a different table? Using Microsoft Word, identify the fields that are redundant and fix any inaccuracies, explaining why for each one.
Part 2 – Identify a Logical Table Structure
Identify a logical structure for the tables and any additional tables needed to normalize the data. In your final report, recreate the tables with the correct structure, including new tables and the relevant data within them. Explain the new structure and how it helps reduce redundancy and achieve normalization.
Part 3 – List New Fields and Field Types
List the new table structure and include the updated fields and field types. For example, if you changed a field to INT so that only integers are stored, mark this field as INT. Be sure that the data in the fields matches the type. Explain consistent field types are important and document each change in your final report.
Part 4 – Write SQL Statements to Fix the Data
Develop 3 SQL statements to fix the database structure and the data. These would include the following:
- DDL (Data Definition Language) statements to remove extraneous column(s) from a table
- DML statements that enter new data, or update data in existing tables (e.g., conform to new data types)
- DDL statements to create new tables
Compile the SQL and save it in your final report. We recommend you create your tables in Microsoft Access or MySQL to test them out.
Assignment Data
Product Table
| productID | productName | productPrice | orderQty | qoh | manufacturer |
|---|---|---|---|---|---|
| 1 | Lionel Freight Car | Ten | 10 | 19 | NULL |
| 2 | Tomcat F-14 | 39.99 | NULL | 2 | Revell |
| 3 | 1957 Corvette Convertible | 56.75” | 5 | 1 | Revell |
| 4 | The USS Constitution Ship | $99.99 | NULL | 2 | Red Start Diecast |
Orders Table
| orderID | qty | customerName | orderDate | status | customerID |
|---|---|---|---|---|---|
| 1 | NULL | Dave Thomas | 12/15/2019 | cancelled | 5 |
| 2 | 3 | Dave Thomas | 10/13/1919 | shipped | 5 |
| 3 | Five | Jane Eyre | NULL | in process | 7 |
| 4 | NULL | 1/15/2020 | shipped | 50 | |
| 5 | 12 | Sherlock Holmes | 1/31/2020 | shipped | 4 |
| 6 | 1 | Edgar Allen Poe | 2/15/2020 | shipped | 4 |
Customers Table
| customerID | customerName | customerAddress | customerPhone | orderIDs |
|---|---|---|---|---|
| 1 | Jane Eyre | 123 North South St | 715-555-1212 | 1, 2, 7, 3 |
| 2 | Sherlock Holmes | 221b Baker St | 800-555-1212 | 15 45 100 235 |
| 3 | Sherlock Holmes | 221b | 800-555-1414 | NULL |
| 4 | Edgar Allen Poe | Boston, MA | 800-999-1547 | 0 |
Formatting and Sources
Please write your paper in the APA format. You may refer to the course material for supporting evidence, but you must also use two sources and cite them using APA format. Please include a mix of both primary and secondary sources, with at least one source from a scholarly peer-reviewed journal. If you use any Study.com lessons as sources, please also cite them in APA (including the lesson title and instructor’s name).
- Primary sources are first-hand accounts such as interviews, advertisements, speeches, company documents, statements, and press releases published by the company in question.
- Secondary sources come from peer-reviewed scholarly journals, such as Journal of Digital Forensics, Security, and Law and International Journal of Cybersecurity and Forensics. You may use sources like JSTOR, Google Scholar, to find articles from these journals. Secondary sources may also come from reputable websites with .gov, .edu, or .org in the domain. (Wikipedia is not a reputable source, though the sources listed in Wikipedia articles may be acceptable.)
If you’re unsure about how to use APA format to cite your sources, please see the following lessons:
Grading Rubric
Your essay will be graded based on the following rubric:
| Category | Unacceptable (0) | Needs Improvement (1-2) | Good (3-4) | Excellent (5) | Total Possible Points |
|---|---|---|---|---|---|
| Data Redundancies (x2) | Report does not list redundancies, or lists fields that are not redundant and required for the table. | Only one or two redundancies are identified, or redundancies are complete, but field(s) that are not redundant are listed. Lacking analysis of why fields are redundant. | List of tables is complete with a listing of the fields and corresponding tables. Brief description of redundant fields. | Comprehensive list of redundant fields and corresponding tables; in-depth analysis is provided explaining why they are redundant. | 10 |
| Identify Table Structure/Fields (x3) | Correct table structure is missing or does not fix the issues identified in previous section; no analysis provided | New structure is partially complete, but still includes redundant data; lacking analysis of new structure and how normalization is achieved. | New structure is complete, but analysis is incomplete or lacking in its explanation of normalization. | New structure is fully complete and in-depth analysis of why and how the new structure helps to achieve normalization. | 15 |
| List of Fields and Field Types (x2) | New fields and field types are not identified, or they do not match the fields. | Fields and new types are identified, with a few miss-matches (e.g., numeric field set as varchar); analysis is missing. | Fields and new field types are identified and accurate; analysis is missing or incomplete. | In-depth analysis of how field types are important to data integrity; new fields and field types are consistent and appropriate. | 10 |
| SQL Statements (x3) | SQL statements missing or incomplete; statements do not match with new table structure. | SQL statements show an attempt to update the table, but do not fully address question (e.g., new data types don’t match data being entered) | SQL statements successfully update the tables and address the question (e.g., new data types match data being entered) | SQL statements to update the tables are syntactically correct and are appropriately written to match data being entered to data types; DDL statements are syntactically correct. | 15 |
Before You Submit
When you complete your assignment, we suggest taking some time to check for any errors or to add any finishing touches. We also suggest that you use online plagiarism checkers such as PlagScan or Duplichecker to make sure that your assignment is not too similar to any existing materials. Plagiarized submissions will NOT be graded.
