Overview
It is important to continuously review information because it changes so quickly. Data analysts and scientists must be able to add or delete records because of the changing nature of information. People benefit from working with data by seeing relationships that exist between different entities. For example, manufacturers might want to identify why their equipment came back, where it came from, how long customers took to return it, or many other factors.
Directions
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 Cardinality and Targeted Data Template from the What to Submit section.
Reference
Before you begin, load the “classicmodels” data set:
Reading:
Import CSV File Into MySQL Table
This resource discusses the process of creating a table with the necessary data types, fields, and their lengths so that information can be imported for querying. As you read, consider the following:
How do you properly size a VARCHAR?
How do you identify all of the column types that need to be in the table?
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’ve been in previous labs.
Write commands to use the classicmodels database and show its tables to verify that you’re in the right place.
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 = ‘CA’.
Write and run a command to return records from New York on your own.
Now, identify what type of cardinality this represents in the entity relationship model.
Run a DESCRIBE statement to identify fields in the payments table first.
Validate the completion of this step with a screenshot.
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 employee.firstName and employee.lastName in your command.
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.
Fields you’ll need to populate: customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, and creditLimit.
Define how cardinality is applied to the databases you’ve been working with and why different numbers of records returned from the different offices.
DAD 220 Cardinality and Targeted Data Template
Replace the bracketed text in this template with your screenshots and responses. Then submit it to the
Module Four Lab for submission, grading, and feedback. Screenshots should be sized to approximately
one quarter of a page. Written responses should be in complete sentences. Rename this document by
adding your last name to the file name before you submit.
1. Retrieve employee tuples and identify the number of employees in San Francisco and New
York.
a. [Insert a screenshot of both tables (they should both fit in one screenshot) here.]
2. Retrieve order details for orderNumber 10330, 10338, and 10194 and identify what type of
cardinality this represents in the entity relationship model.
a. [Insert your response here.]
3. Delete records from the payments table where the customer number equals 103.
a. [Insert a screenshot of this data before you delete it here.]
b. [Insert a screenshot showing that you have successfully deleted these records here.]
4. Retrieve customer records for sales representative Barry Jones and identify if the relationships
are one-to-one or one-to-many.
a. [Insert your response here.]
5. Retrieve records for customers who reside in Massachusetts and identify their sales rep and
the relationship of entities. Identify if these entities demonstrate one-to-one or many-to-many
relationships.
a. [Insert your response here.]
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.
a. [Insert a screenshot of your unique customer record here.]
7. Reflection
a. Define how cardinality is applied to the databases you’ve been working with and why
different numbers of records returned from the different offices.
i.
[Insert your response here.]
b. Compare and contrast the different queries you ran and how cardinality applies to
them.
i.
[Insert your response here.]
c. Describe two of the crucial benefits of cardinality in this type of database.
i.
[Insert your response here.]
