In this lab, you$$ll be using an existing database that is preloaded into Codio from MySQLTutorial.org. You$$ll need to compare records from different locations, identify cardinality, delete records, and reflect on the value of these skills. You$$ll also need to explain how cardinality can be applied and what its benefits are. All your screenshots and written responses must be placed in the Module Four Lab Template linked in the What to Submit section.

Before you begin, complete the following tasks:

Review resources in Supporting Materials below and the Resources area of this module for information to help you as you work through this lab.

Download the Module Four Lab Template for this assignment in What to Submit below. You will place the screenshots of your work in this document and submit the document for grading.

Check the Template Screenshot Example in Supporting Materials below to see how assignment screenshots should be created.

Begin by doing the following steps in the IDE $($Codio$)$:

Load the classicmodels data set.

Start a terminal session and run this command: mysqlsampledatabase.sql

Write commands to use the classicmodels database and show its tables to verify that you are in the right place.

Then follow the steps shown below.

Specifically, you must address the following rubric criteria:

Retrieve employee tuples and identify the number of employees in San Francisco and New York.

Command for San Francisco: SELECT firstName, lastName, jobTitle, offices.city from employees inner join offices on employees.officeCode $=$ offices.officeCode where state $=\text{'}$CA$\text{'}.$

Write and run a command to return records from New York on your own.

Validate the completion of this step with a screenshot of these two tables.

Retrieve order details for order numbers $10330,10338,$ and $10194$ and identify what type of cardinality this represents in the entity relationship model.

Validate the completion of this step with a screenshot.

Reference the Module Four Lab ERD in the Supporting Materials section below to help you identify relationships.

Reference the Module Four Lab ERD in the Supporting Materials section below to help you identify relationships.

Retrieve the order details by running SELECT queries with WHERE clauses against the Orders table.

Now, identify what type of cardinality this represents in the entity relationship model $($ERD$).$

Delete records from the payments table where the customer number equals $103.$

Validate that the above instructions have worked with a screenshot.

Validate the completion of this step with a screenshot.

Run a DESCRIBE statement to identify fields in the Payments table first.

Select the records from the Payments table for customer number $103$ before deleting them.

Delete the records from the Payments table for customer number $103.$

Run a SELECT statement against the table to show that customer number $103$ is no longer there.

Retrieve customer records for sales representative Barry Jones and identify if the relationships are one$-$to$-$one or one$-$to$-$many.

Validate the completion of this step with a screenshot.

Identify whether these entities demonstrate one$-$to$-$one or one$-$to$-$many relationships.

Remember: SELECT, FROM, INNER JOIN, and WHERE.

Use Barry$$s employeeNumber, $1504,$ and perform a join between the customer salesRepEmployeeNumber to retrieve these records.

Retrieve records for customers who live in Massachusetts and identify their sales rep and the relationship of entities.

Remember: SELECT, FROM, INNER JOIN, and WHERE.

Use employee.firstName and employee.lastName in your command.

Identify whether these entities demonstrate one$-$to$-$one or many$-$to$-$many relationships.

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.

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;

You should now see all of the fields that you$$ll need to fill in to complete this step.

Reference your Module Two lab or resources on how to populate these fields if you need to$.$

Populate the following fields:

customerNumber

customerName

contactLastName

contactFirstName

phone

addressLine$1$

addressLine$2$

city

state

postalCode

country

salesRepEmployeeNumber

creditLimit

Run a SELECT statement on the Customers table, capture it in a screenshot, and put it in your template.

Reflection: Use the lab environment or the screenshots you$$ve worked with for this step. Address the prompts below in your reflection. Write a paragraph in response to each prompt:

Define how cardinality is applied to the databases you have been working with. Explain why different numbers of records were returned from the different offices.

Compare and contrast the different queries you ran and how cardinality applies to them.

Describe two of the crucial bene