DAD 220 Southern New Hampshir

Overview

You have a job interview coming up with a prospective employer. They have given you the task of writing a summary that explains how you would write a report for one of their clients. They also want you to give a brief explanation justifying the approach you took.

Prompt

The potential employer would like you to conduct an analysis of data and then summarize your findings using clear language for a nontechnical audience. They have provided you with a data set called FleetMaintenanceRecords to base your report on for the interview. They have asked you to identify relevant themes that can be used to help them manage their fleet of trucks. In preparation for your interview, create an example summary of the analysis you conducted that you would use to communicate the findings to stakeholders. Place the deliverables for your instructor and the potential employer in the Analysis and Summary Template Word Document.

  1. Analyze the data you’ve been provided to identify themes. Guiding information to gather:
    1. Review part-replacement frequencies and types and create a hypothesis that can be used by the fleet management team to better deal with vehicle maintenance. You’ll need to create a table called Parts Maintenance and load the data into it. Put this table in the database named after yourself. Load this data set from the ‘/home/codio/workspace’ path and run queries to find the results.
      1. Which parts are being replaced most?
      2. Is there a region of the country that experiences more part failures and replacements than others?
        1. Can you identify a region (Northeast, Southeast, Midwest, Northwest, Southwest) that has more reasons for replacement? Use the Region Definitions sheet PDF to identify states in each region.
        2. How might the fleet maintenance team use the information to update its maintenance schedule? Your response should be backed by the data you have found.
      3. Which parts are being replaced most due to corrosion or rust?
      4. Which parts are being replaced because of mechanical failure, like a flat tire or rock through the windshield?
  2. Write a brief summary of your analysis that takes the information from step one and presents it in a way that nontechnical stakeholders will be able to digest.
  3. Outline the approach that you took to conduct the analysis.
    1. What queries did you use to identify trends or themes in the data?
    2. What are the benefits of using these queries to retrieve the information in a way that allows you to provide valuable information to your stakeholders?
  4. Lastly, identify how the functions in the analysis tool (Codio) allowed you to organize the data and retrieve records quickly so that they demonstrated what you wanted.

Place your responses in the template linked in the Prompt section. After you download the template, rename your copy by adding your last name to its file name. This will help organize your work once it has been submitted. 

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized

DAD 220 Southern New Hampshir

Overview

It’s now time to populate the QuantigrationRMA database from the Module Three major activity with records and run some basic queries against those tables. You will need to import data from the CSV data files into the tables, perform queries against those tables, and create an output listing to the screen of the query results. Your goal is to track shipments for defective or incorrect parts.

Description of QuantigrationRMA Entity Relationship Diagram

An entity relationship diagram (ERD) is a visual representation of a database’s objects and relationships between those objects. It is a blueprint for creating a database, its tables (entities), their attributes (fields), the data types, and the relationships between entities (cardinality constraints) and keys in each table. In our example, we have three tables named customers, orders, and rma. These tables are the boxes that have several fields such as FirstName, LastName, OrderID, and so on. Each table also contains a primary key denoted by “PK” on the entity relationship diagram. The connection between tables is represented with an industry standard notation called crows feet. These lines represent different entity relation types such as one-to-one (1:1) or one-to-many (1:N), as visually depicted in the ERD.

Prompt

In the Module Three major activity, you created a customer return merchandise authorization (RMA) database using SQL commands. To complete your objectives in this activity, follow these directions:

  • Before you begin, do the following:
    • Make sure to download the Module Four Major Activity Database Documentation Template Word Document for this assignment. You will need to place your answers and screenshots in this document and then submit it for grading.
    • Check the Database Documentation Template Example for clarity on expectations of what yours should look like.
    • Make sure to review the example RMA entity relationship diagram (ERD) that you should be using as a guide before you begin. A text version is also available: Quantigration RMA ERD Text Version.
    • Review the resources on how to capture screenshots, if necessary.
  • Please note before you begin that the three data files being used for this project are preloaded into Codio:
    • rma.csv
    • customers.csv
    • orders.csv
  1. Import the data from each file into tables.
    1. Use the QuantigrationRMA database, the three tables you created, and the three CSV files preloaded into Codio.
    2. Use the import utility of your database program to load the data from each file into the table of the same name. You will perform this step three times, once for each table.
  2. Write basic queries against imported tables to organize and analyze targeted data. For each query, include a screenshot of the query and its output. You should also include a brief, 1- to 3-sentence description of the output.
    1. Write an SQL query that returns the count of orders for customers located only in the city of Framingham, Massachusetts.
      1. This query will use a table join between the customers and orders tables. The query will also use a where clause.
      2. How many records were returned?
    2. Write an SQL query to select all of the customers located in the state of Massachusetts.
      1. Use a WHERE clause to limit the number of records in the customers table to only those who are located in Massachusetts.
      2. Record an answer to the following question: How many records were returned?
    3. Write an SQL query to insert four new records into the orders and customers tables using the following data:Customers TableCustomerIDFirstNameLastNameStreetAddressCityStateZipCodeTelephone100004LukeSkywalker17 Maiden LaneNew YorkNY10222212-555-1234100005WinstonSmith128 Sycamore StreetGreensboroNC27401919-555-6623100006MaryAnneJenkins2 Coconut WayJupiterFL33458321-555-8907100007JanetWilliams58 Redondo Beach BlvdTorrenceCA90501310-555-5678Orders TableOrderIDCustomerIDSKUDescription1204305100004ADV-24-10CAdvanced Switch 10GigE Copper 24 port1204306100005ADV-48-10FAdvanced Switch 10 GigE Copper/Fiber 44 port copper 4 port fiber1204307100006ENT-24-10FEnterprise Switch 10GigE SFP+ 24 Port1204308100007ENT-48-10FEnterprise Switch 10GigE SFP+ 48 port
    4. In the customers table, perform a query to count all records where the city is Woonsocket, Rhode Island.
      1. How many records are in the customers table where the field “city” equals “Woonsocket”?
    5. In the rma database, update a customer’s records.
      1. Write an SQL statement to select the current fields of status and step for the record in the rma table with an orderid value of “5175.”
        1. What are the current status and step?
      2. Write an SQL statement to update the status and step for the orderid, 5175 to status = “Complete” and step = “Credit Customer Account.”
        1. What are the updated status and step values for this record? Provide a screenshot of your work.
    6. Delete rma records.
      1. Write an SQL statement to delete all records with a reason of “Rejected.”
        1. How many records were deleted? Provide a screenshot of your work.
  3. Create an output file of the required query results. Write an SQL statement to list the contents of the orders table and send the output to a file that has a .csv extension.

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized

DAD 220 Southern New Hampshir

Overview

Because information changes rapidly, monitoring and reviewing it is critical. It’s important for data analysts and scientists to be able to add or delete records due to the changing nature of information. Another benefit to working with data is identifying relationships that exist between different entities. For example, manufacturers might want to identify why their equipment came back, where it came from, or how long customers took to return it, along with a host of other reasons.

Prompt

In this lab, you will be using an existing database that is preloaded into Codio from MYSQLTutorial.org. You will need to compare records from different locations, identify cardinality, delete records, and reflect on the value of these skills. You will also need to articulate how cardinality can be applied and what its benefits are. All your screenshots and written responses need to be placed in the Cardinality and Targeted Data Template Word Document that goes with this assignment.

Reference

MySQLTutorial. (n.d.). MySQL sample database classicmodels [Data file]. Available from https://www.mysqltutorial.org/mysql-sample-databas… 

Before you begin, load the “classicmodels” data set:

  • Start a terminal session and run this command: mysql < mysqlsampledatabase.sql
  • Type mysql in the command line and begin working with SQL the way you have been in previous labs.
  • Write commands to use the classicmodels database and show its tables to verify that you’re in the right place.
  1. Retrieve employee tuples and identify the number of employees in San Francisco and New York.
    1. Command for San Francisco: select firstName, lastName, jobTitle, offices.city from employees inner join offices on employees.officeCode = offices.officeCode where state = ‘CA’;
    2. Write and run a command to return records from New York on your own.
    3. Validate the completion of this step with a screenshot of these two tables.
  2. Retrieve order details for order Number 10330, 10338, and 10194 by running SELECT queries with WHERE clauses against the orders table.
    1. Validate the completion of this step with a screenshot.
    2. Then, reference the Module Four Lab ERD to assist in identifying relationships. A version with alternative text is available: Module Four Lab ERD With Alternative Text.
    3. Now, identify what type of cardinality this represents in the entity relationship model.
  3. Delete records from the payments table where the customer number equals 103.
    1. Run a describe statement to identify fields in the payments table first.
    2. Select the records from the payments table for customer number 103 before deleting them.
      1. Validate that the above instructions have worked with a screenshot.
    3. Delete the records from the payments table for customer number 103.
    4. Run a SELECT statement against the table to show that customer number 103 is no longer there.
      1. Validate the completion of this step with a screenshot.
  4. Retrieve customer records for employee Rep Barry Jones and identify relationships. Remember: SELECT, FROM, Inner Join, and WHERE.
    1. Use Barry’s employeeNumber, 1504, and perform a join between the customer salesRepEmployeeNumber to retrieve these records.
      1. Identify if these entities demonstrate one-to-one or one-to-many relationships.
  5. Retrieve records for customers who reside in Massachusetts, then identify their sales rep and the relationship of entities. Remember: SELECT, FROM, Inner Join, and WHERE.
    1. Use employee.firstName and employee.lastName in your command.
    2. Identify if these entities demonstrate one-to-one or many-to-many relationships.
  6. Add one customer record with your last name using an INSERT statement. You may use the name of a celebrity or fictional character if you don’t use your own name. Think of this as your signature.
    1. Complete these actions to get to the right place to enter this information: (1)Show databases, (2)use classicmodels, (3)show tables, (4)describe customers;
      1. You should now be seeing all of the fields that you’ll need to fill in to complete this step.
      2. Reference your Module Two lab or resources on how to populate these fields if you need to.
      3. Fields you’ll need to populate: customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit
        1. You will need to look up a valid salesRepEmployeeNumber in the employee table to populate salesRepEmployeeNumber.
      4. Run a select statement on the customers table, capture it in a screenshot, and put it in your template.
  7. Reflection: Use the lab environment or the screenshots you’ve worked with for this step. To retrieve information you were previously working with, run the mysql < mysqlsampledatabase.sql script.
    1. Define how cardinality is applied to the databases you’ve been working with and why different numbers of records returned from the different offices.
    2. Compare and contrast the different queries you ran and how cardinality applies to them.
    3. Describe two of the crucial benefits of cardinality in this type of database.

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized

DAD 220 Southern New Hampshir

Overview

Quantigration, a rapidly expanding networking equipment manufacturer, wants to document all of its equipment returns. It has identified this as a problematic area. You have been hired to create a database using SQL commands for processing all returns.

Prompt

You will use the return merchandise authorization (RMA) entity relationship diagram (ERD) to build a database. Each of these tables represents a database entity. The ERD is the blueprint for building your relational database.

  1. Go to your online integrated development environment (Codio), log in, start a new terminal session, and then create a database schema called QuantigrationRMA and list it out on the screen. Then, connect to the QuantigrationRMA schema.
  2. Using the ERD as a reference, create the following tables with the appropriate attributes and keys:
    • A table to store customer information with a primary key of customer ID
    • A table to store order information with a primary key of Order ID
    • A table to store RMA information with a primary key of RMA ID
  3. Manually add 10 records into the Customers table. The data can be made up for now, as you will populate all three tables later from the provided .csv files
  4. Due to a new company branding initiative, you’ve been asked to establish a database view called “Collaborators” that is based on the “Customers” table. Create a view from the existing Customers table by using the SQL command provided below to say “Collaborators.” The view should show all instances of “Customer” renamed as “Collaborator.” Execute the following statements and provide one or more supporting screenshots verifying establishment of the database view: 
    • The following command is partially complete. Fill in the missing information in the brackets to complete it and run the commands correctly.
    • CREATE VIEW Collaborator AS
      SELECT CustomerID AS CollaboratorID, [Enter in the correct column names from that customer table that you want to change in the collaborator table]
      FROM Customers;
    • DESCRIBE Collaborators;
    • SELECT * FROM Collaborators LIMIT 5;

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized

DAD 220 Southern New Hampshir

Competency

In this project, you will demonstrate your mastery of the following competency:

  • Analyze the results of queries constructed to address data requirements

Scenario

The product manager of Quantigration has asked your data analytics team for a report summarizing your analysis of the return merchandise authorizations (RMAs) that have been received. These are the same data sets that you have already been working with. Your report should focus on summarizing the analysis and presenting your findings to the product manager.

Directions

RMA Report
In your report on the findings of your analysis, respond to the manager’s requests to summarize the data you have been working with to identify key information that will be of value to the company when developing future business strategies to streamline operations. Remember, not everyone who reviews this report will have a technical background.

  1. Begin by writing SQL commands to capture usable data (which you have preloaded into Codio) for your analysis. Specifically, the product manager wants you to investigate and analyze the following:
    • Analyze the frequency of returns by state, and describe your findings in your report.
    • Analyze the percentage of returns by product type and describe your findings in your report.
  2. In your report, summarize your analysis of the data you’ve captured. Include screenshots of the results of each query. When summarizing results, you may want to consider the following questions:
    • How does the data provide the product manager with usable information?
    • What are the potential flaws in the data that has been presented?
    • Are there any limitations on your conclusions, or any angles you haven’t considered?
  3. Clearly communicate your findings to stakeholders.
    • Make sure that all parts of your report are written in a way that very clearly explains the necessary information.

What to Submit

To complete this project, you must submit the following:

Data Analysis RMA Report

Submit a report for the product manager summarizing the results of your analysis. Your report will address the analysis that you have conducted. Provide screenshots of your SQL queries and their resultant output following the same instructions for capturing screenshots that you were given in previous assignments. 

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized

DAD 220 Southern New Hampshir

Overview

To complete this assignment, you will work in the Codio virtual lab environment to create tables similar to the ones that you saw in the previous activity.

Prompt

To begin, go to your Codio virtual lab environment and start a new terminal session. Once there, perform the steps below to complete this activity. The commands you see, or are asked to write, must all be entered manually. To validate that the commands you have written work, capture screenshots PDF of the results and place them in a document for submission. Each screenshot should be around one quarter of a page.

  1. Connect to the database you created and named in Module One (for example, Jetson). Type after the prompt mysql>
    1. use (table you named);
      1. Example: mysql> use Jetson;
  2. Create the Employee table using the SQL statement shown here. Press Return after each line.CREATE TABLE   Employee (Employee_ID   SMALLINT,First_Name    VARCHAR(40),Last_Name   VARCHAR(60),Department_ID   SMALLINT,Classification   VARCHAR(10),STATUS   VARCHAR(10),Salary   DECIMAL(7,2));
  • Create the Branches table. Fill in the missing characters or punctuation in the incomplete statement shown below to complete this action.
    1. CREATE   Branches (Department_ID   SMALLINT,Department_Name   )
  • After creating the tables, use the correct commands to describe them. You will only be given commands to describe one of the tables and must complete the same action for the second one on your own. Validate your work with a screenshot.
    1. describe Employee;
    2. Write the correct command to describe the Branches table
  • Insert the following records into the Employee table. Each line going from left to right is a record. Each line going from top to bottom is a column. Validate your work with a screenshot.
    1. INSERT INTO Employee VALUES (100, ‘John’, ‘Smith’, 1, ‘Exempt’, ‘Full-Time’, 90000), (101,’Mary’,’Jones’,2,’Non-Exempt’,’Part-Time’,35000), (102,’Mary’,’Williams’,3,’Exempt’,’Full-Time’,80000);
    2. Type the command select* from Employee; and take a screenshot of it to validate this step.
  • Insert the following records into the Employee table for Gwen Johnson and Michael Jones by writing the correct SQL commands on your own.
    1. Gwen Johnson: Employee ID = 103, DEPARTMENT_ID = 4, Classification = NULL, Status = Full-Time, SALARY = 40000
    2. Michael Jones: Employee ID = 104, DEPARTMENT_ID = 4, Classification = Non-Exempt, Status = Full-Time, SALARY = 90000
    3. Insert your name into the table to verify and prove your work. (Your First and Last Name, or a nickname): Employee ID = 105, DEPARTMENT_ID = 1, Classification = Non-Exempt, Statues = Full-time, SALARY = (Choose a value between 50000 and 99000)
    4. Type the command select* from Employee; and take a screenshot of it to validate this step.
    5. Insert records for a musician, athlete, or other famous character of your choice. Make sure to enter information for all of the fields listed in this table. The Department_ID must be a number between one and four.
    6. Write the correct command to prove that you’ve successfully completed this step, and validate it with a screenshot.
  • Select the fields of last name, first name, employee id, and department id from the table. Validate your work with a screenshot.
    1. Select First_Name, Last_Name, Employee_ID, Department_ID from Employee;

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized

DAD 220 Southern New Hampshir

Competencies

In this project, you will demonstrate your mastery of the following competencies:

  • Create a structured database through the use of relational concepts
  • Implement basic processing functionalities to create new structured database environments

Step One Scenario

You are working as a database administrator for Quantigration, a networking equipment manufacturer. The company has grown quickly and is still evolving its internal business processes to handle the rapid growth. Customer return merchandise authorizations (RMAs), used to track shipments for defective or incorrect parts, are a specific area that has been identified as problematic.

Your supervisor at Quantigration has asked you, as part of their development team, to create a database for a new software application that will be used for processing RMAs. Because there are several locations where merchandise can be taken in, the application will also need to record where items are received. This system will also capture where the equipment is going next—repair, scrap, or refurbishing and resale. The warehouse shipping and receiving employees at your company will be the people using this application and are considered your “end users” for the application. Part of their job is to process customer return shipments. This involves the following:

  • Assessing the quality of the items being returned for defects
  • Identifying missing parts
  • Identifying signs of obvious wear and tear (such as dents or scratches to the equipment)

Step One Directions

Create a Database

The data architect on your DevOps team has come to you with a logical database diagram referred to as an entity relationship diagram (ERD). This is a documentation of the tables, their relationships to each other, their attributes, and any primary or foreign keys on those attributes. These elements are required to create the new database. To create your three tables named customers, orders, and rma in the QuantigrationUpdates  database based on the example you have been given, complete the following steps, and place your work in the provided Database Documentation Template:

  • Prior to beginning, review the example entity relationship diagram (ERD) in the Quantigration RMA Diagram document. A text version of this diagram is available: Quantigration RMA Diagram Text Version.
  1. Navigate to your online integrated development environment (IDE). Here, you will need to write the proper SQL commands in command line to create tables that demonstrate relationships based on the entity relationship diagram.
  2. Create a database schema called QuantigrationUpdates and list it out to the screen.
  3. Using the ERD as a reference, create the following tables with the appropriate attributes and keys:
    • A table to store customer information with a primary key of Customer ID
    • A table to store order information with a primary key of Order ID
    • A table to store RMA information with a primary key of RMA ID
  4. Due to a new company branding initiative, you’ve been asked to change the name of the “Customer” to “Collaborator.” Update your existing table using SQL based on this change in requirements. Copy and paste the SQL you write to do the following:
    • Rename all instances of “Customer” to “Collaborator.”

STOP: Make sure to save your work before moving on to Step Two of this process. You will continue to work in Codio and use the documentation you started in Step One.

Step Two Scenario

Now it’s time to populate the database with records and run some basic queries against those tables. You will be using the QuantigrationUpdates database and three tables that were created in Step One to import data from the CSV data files into the tables, perform queries against those tables, and create an output listing to the screen of the query results. Your goal is to track shipments for defective or incorrect parts.

The shipping and receiving team that you are working with has been keeping records of their RMAs in spreadsheets, while your team has been creating its database in MySQL. The information that they have is detailed and won’t need cleaning. The data in the tables can be exported to CSV files. The data should also align with the columns and data types in your tables.

Step Two Directions

Load and Query the Data

In Step One, you created a customer return merchandise authorization (RMA) database using SQL to create tables and placed your work in the database documentation template. To complete your objectives in this step, continue to place your work in the database documentation template and follow these directions:

  • Please note before you begin that the three data files being used for this project are preloaded into Codio:
    • rma.csv
    • customers.csv
    • orders.csv
  1. Import the data from each file into tables.
    • Use the QuantigrationUpdates database, the three tables you created, and the three CSV files preloaded into Codio.
    • Use the import utility of your database program to load the data from each file into the table of the same name. You will perform this step three times, once for each table.
  2. Write basic queries against imported tables to organize and analyze targeted data. For each query, include a screenshot of the query and its output. You should also include a brief, 1- to 3-sentence description of the output.
    • Write an SQL query that returns the count of orders for customers located only in the city of Framingham, Massachusetts.
      1. This query will use a table join between the customers and orders tables. The query will also use a where clause.
      2. How many records were returned?
    • Write an SQL query to select all of the customers located in the state of Massachusetts.
      1. Use a WHERE clause to limit the number of records in the customers table to only those who are located in Massachusetts.
      2. Record an answer to the following question: How many records were returned?
    • Write an SQL query to insert four new records into the orders and customers tables using the following data:Customers TableCustomerIDFirstNameLastNameStreetAddressCityStateZipCodeTelephone100004LukeSkywalker15 Maiden LaneNew YorkNY10222212-555-1234100005WinstonSmith123 Sycamore StreetGreensboroNC27401919-555-6623100006MaryAnneJenkins1 Coconut WayJupiterFL33458321-555-8907100007JanetWilliams55 Redondo Beach BlvdTorrenceCA90501310-555-5678Orders TableOrderIDCustomerIDSKUDescription1204305100004ADV-24-10CAdvanced Switch 10GigE Copper 24 port1204306100005ADV-48-10FAdvanced Switch 10 GigE Copper/Fiber 44 port copper 4 port fiber1204307100006ENT-24-10FEnterprise Switch 10GigE SFP+ 24 Port1204308100007ENT-48-10FEnterprise Switch 10GigE SFP+ 48 port
    • In the customers table, perform a query to count all records where the city is Woonsocket, Rhode Island.
      1. How many records are in the customers table where the field “city” equals “Woonsocket”?
    • In the rma database, update a customer’s records.
      1. Write an SQL statement to select the current fields of status and step for the record in the rma table with an orderid value of “5175.”
        1. What are the current status and step?
      2. Write an SQL statement to update the status and step for the orderid, 5175 to status = “Complete” and step = “Credit Customer Account.”
        1. What are the updated status and step values for this record? Provide a screenshot of your work.
    • Delete rma records.
      1. Write an SQL statement to delete all records with a reason of “Rejected.”
        1. How many records were deleted? Provide a screenshot of your work.
  3. Create an output file of the required query results. Write an SQL statement to list the contents of the orders table and send the output to a file that has a .csv extension.

Place this order or similar order and get an amazing discount. USE Discount code “GET20” for 20% discount

Posted in Uncategorized