Analytics 103 – Assignment 1: Creating a Relational Database

Analytics 103 – Assignment 1: Creating a Relational Database

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.

  1. Normalize the tables from the sample data and create the database in a database creation tool
  2. Design queries to pull information from the database
  3. 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:

  1. A specific device for a specific user
  2. A device from a specific location
  3. All users who have tablets
  4. All users with multiple devices
  5. 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

"You need a similar assignment done from scratch? Our qualified writers will help you with a guaranteed AI-free & plagiarism-free A+ quality paper, Confidentiality, Timely delivery & Livechat/phone Support.


Discount Code: CIPD30


WHATSAPP CHAT: +1 (781) 253-4162


Click ORDER NOW..

order custom paper