About this Assignment
In this course, you have learned about relational database models and normalization of data. You have covered the concepts of relation between tables and first and third normal forms.
For this assignment, you will design a relational database and normalize it to third normal form and then create several simple queries to retrieve data from that database. You will submit a final report that details and explains what decisions you made when you designed the database and contains your queries and outputs.
Prompt
In this project, you are taking on the role of a database administrator. Your boss tasked you to create a database to track company-owned devices such as tablets, phones, and laptops across their employees and offices.
For this assignment, you will use the Assignment Data below to build the database. There are three main components to this assignment.
- Normalize the tables from the sample data and create the database in a database creation tool
- Design queries to pull information from the database
- Write a final report that describes your process and contains your queries and output
Part 1 – Normalize and Create the Database
Evaluate the available data, examining the relationships between them and then identify any areas where you can generate third normal form for the data. You may need to add new data like primary keys or tables to the sample data during this process and you must identify the data type for each field.
Each table you create must hold the correct amount of records and all fields should be populated. You may choose to use Microsoft Access or MySQL to create the tables and test your design.
Part 2 – Query the Data
Using Microsoft Word, write an SQL query for each of the following:
- A specific device for a specific user
- A device from a specific location
- All users who have tablets
- All users with multiple devices
- Any Support Agent users with multiple devices
The SQL queries must be syntactically correct; we recommend you use a database management tool to sample each query.
Part 3 – Write a Final Report
Compile your initial table setup, the third normal form data, the sample data, the SQL queries, and output in a Microsoft Word document.
The final document should contain 500 to 750 words that describe your process for creating the original design, the steps you took to normalize the data, and the rationale for your choices, using supporting research and explanation.
Assignment Data
Set 1
| Name | ID | Role |
| Ashley Tucker | 52 | Support Agent |
| Jaime Black | 756 | Support Agent |
| Erica Peterson | 672 | Support Agent |
| Bessie Bell | 392 | Support Agent |
| Glen Mckenzie | 51 | Support Agent |
| Simon Patrick | 299 | Support Agent |
| Courtney Torres | 720 | Support Agent |
| Neil Ray | 57 | Support Agent |
| Rachael Neal | 473 | Support Agent |
| Warren Pearson | 190 | Support Agent |
| Tiffany Ward | 784 | Support Agent |
| Sam Castro | 983 | Senior Support Agent |
| Lamar Willis | 328 | Senior Support Agent |
| Maxine Wilkerson | 246 | Senior Support Agent |
| Dewey Watkins | 446 | Senior Support Agent |
| Blanca Lynch | 326 | Support Agent |
| Elizabeth Terry | 498 | Support Agent |
| Ismael Schneider | 58 | Support Agent |
| Florence Malone | 150 | Support Agent Manager |
| Lynn Morgan | 233 | Support Agent Manager |
| James Burke | 91 | Support Agent Manager |
| Shelia Myers | 29 | Senior Support Agent |
| Elvira Rowe | 602 | Senior Support Agent |
| Kellie Reynolds | 70 | Support Agent |
| Jody Blair | 566 | Support Agent |
Set 2
| Type | Version | Checked Out To | ID | Location |
| Laptop | macOS 10.14 | Ashley Tucker | 98 | Office 1 |
| Laptop | macOS 10.14 | Jaime Black | 83 | Office 1 |
| Laptop | macOS 10.14 | Erica Peterson | 96 | Office 1 |
| Laptop | macOS 10.14 | Bessie Bell | 97 | Office 1 |
| Laptop | macOS 10.14 | Glen Mckenzie | 73 | Office 1 |
| Laptop | macOS 10.14 | Simon Patrick | 65 | Office 1 |
| Laptop | macOS 10.14 | Courtney Torres | 13 | Office 1 |
| Laptop | macOS 10.14 | Neil Ray | 24 | Office 1 |
| Laptop | macOS 10.14 | Rachael Neal | 28 | Office 1 |
| Laptop | macOS 10.14 | Warren Pearson | 37 | Office 3 |
| Laptop | macOS 10.14 | Tiffany Ward | 36 | Office 3 |
| Laptop | macOS 10.14 | Sam Castro | 60 | Office 2 |
| Laptop | macOS 10.14 | Lamar Willis | 22 | Office 2 |
| Laptop | macOS 10.14 | Maxine Wilkerson | 70 | Office 2 |
| Laptop | macOS 10.14 | Dewey Watkins | 55 | Office 2 |
| Laptop | macOS 10.14 | Blanca Lynch | 40 | Office 2 |
| Laptop | macOS 10.14 | Elizabeth Terry | 30 | Office 2 |
| Laptop | macOS 10.14 | Ismael Schneider | 16 | Office 2 |
| Laptop | macOS 10.14 | Florence Malone | 8 | Office 3 |
| Laptop | macOS 10.14 | Lynn Morgan | 52 | Office 3 |
| Laptop | macOS 10.14 | James Burke | 94 | Office 3 |
| Laptop | macOS 10.15 | Shelia Myers | 4 | Office 3 |
| Laptop | macOS 10.15 | Elvira Rowe | 63 | Office 2 |
| Laptop | macOS 10.15 | Kellie Reynolds | 70 | Office 1 |
| Laptop | macOS 10.15 | Jody Blair | 57 | Office 1 |
| Tablet | IOS 10 | Florence Malone | 53 | Office 1 |
| Tablet | IOS 10 | Lynn Morgan | 3 | Office 1 |
| Tablet | IOS 10 | Maxine Wilkerson | 48 | Office 2 |
| Tablet | IOS 11 | Dewey Watkins | 73 | Office 2 |
| Tablet | IOS 11 | Jody Blair | 81 | Office 3 |
| Tablet | IOS 11 | Jody Blair | 6 | Office 3 |
| Smartphone | Android 10 | Florence Malone | 71 | N/A |
| Smartphone | Android 10 | N/A | 67 | Office 3 |
| Smartphone | IOS 11 | Simon Patrick | 74 | N/A |
| Smartphone | IOS 11 | Courtney Torres | 57 | N/A |
Set 3
| Office | Capacity | Network ID |
| Office 1 | 15 | 1561 |
| Office 2 | 15 | 1660 |
| Office 3 | 10 | 2158 |
| Office 4 | 10 | 2483 |
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 at least 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 |
|---|---|---|---|---|---|
| Database Design (x2) | Database design is incomplete; key components missing from the structure. | Provides a database design with identified tables; however, fields are missing or there are redundancies; field types do not match the type of data | Basic database structure with identified tables, fields, data types. Missing primary/foreign keys. | All components clearly laid out and described. Tables, fields, data types, and primary/foreign keys are clearly identified in the layout | 10 |
| Normalize and Create the Database (x3) | Database is not normalized, or all fields are contained in only a couple of tables. | Provides partial normalization, but there are still transitive dependencies, or extra fields; structure does not meet relational database model design constructs. | Database is mostly in third normal form, but there are still transitive dependencies that exist, or there are missing primary/foreign key links. | Database is in third normal form, with no transitive dependencies, fields are unique and not repeated. Primary and foreign keys exist and database meets relational model design standards. | 15 |
| Query (x3) | Queries are missing or do not contain information needed to extract the data. | Partial query or queries provided, but do not extract required information. | Queries are mostly complete, but are missing key words, or do not extract the data required. | Queries are complete and have the correct syntax, and are setup to pull the required information from the database. | 15 |
| Final Report (x2) | Final report is incomplete or missing; does not include the original layout, the normal form, the data or the queries. | Final report missing one or more elements from the requirement (original layout, normal form, data, queries) | Final report complete, but missing one element from the requirements. | All required elements included in the final report, including original layout, normalized data, sample data, and SQL queries). | 10 |
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.
How to Submit Your Assignment
When you are ready to submit your assignment, please fill out the following submission form by attaching your assignment as a Microsoft Word file. After turning in your assignment, you may go ahead and take the proctored final exam. You do not need to wait for your written response to be graded. You should receive your assignment grade within one week.
If you are not satisfied with the score you receive on your assignment, you may revise or rewrite it, and resubmit them for grading using the same submission form above. Keep in mind that the grade you receive on your assignment is only a portion of your overall grade for the course, and you are free to retake the proctored final exam as well if you choose. Please see the course syllabus for a more detailed breakdown of the grading policy.
