LAB 4
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
220
Subject
Information Systems
Date
Dec 6, 2023
Type
docx
Pages
4
Uploaded by ProfessorHeat17493
10Overview
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
MySQLTutorial. (n.d.).
MySQL sample database classicmodels
[Data file].
MySQLTutorial. https://www.mysqltutorial.org/mysql-sample-
database.aspx
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:
o
How do you properly size a VARCHAR?
o
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.
1.
Retrieve employee tuples and identify the number of
employees
in San Francisco and New York
.
A.
Command for San Francisco: select firstName, lastName,
jobTitle, offices.city from employees inner join offices on
employees.officeCode = offices.officeCode where state =
'CA'.
B.
Write and run a command to return records from New
York on your own.
C.
Validate the completion of this step with a screenshot of
these two tables.
2.
Retrieve order details
for orderNumber 10330, 10338, and
10194 and
identify
what
type of cardinality
this represents
in the entity relationship model.
A.
Retrieve the order details by running SELECT queries with
WHERE clauses against the orders table.
B.
Validate the completion of this step with a screenshot.
C.
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
.
D.
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.
A.
Run a DESCRIBE statement to identify fields in the
payments table first.
B.
Select the records from the payments table for customer
number 103 before deleting them.
i.
Validate that the above instructions have worked
with a screenshot.
C.
Delete the records from the payments table for
customer
number 103.
D.
Run a SELECT statement against the table to show that
customer number 103 is no longer there.
i.
Validate the completion of this step with a
screenshot.
4.
Retrieve customer records
for sales representative Barry
Jones and
identify
if the
relationships
are one-to-one or one-
to-many
.
A.
Remember: SELECT, FROM, Inner Join, and WHERE.
B.
Use Barry’s employeeNumber, 1504, and perform a join
between the customer salesRepEmployeeNumber to
retrieve these records.
i.
Validate the completion of this step with a
screenshot.
ii.
Identify whether these entities demonstrate one-to-
one or one-to-many
relationships.
5.
Retrieve records
for customers who reside in Massachusetts
and
identify their sales rep and the relationship of
entities.
A.
Remember: SELECT, FROM, Inner Join, and WHERE.
B.
Use employee.firstName and employee.lastName in your
command.
C.
Identify whether these entities demonstrate one-to-one or
many-to-many relationships
.
6.
Add one customer record
with your last name using an
INSERT statement.
A.
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.
B.
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;
i.
You should now be seeing all of the fields that you’ll
need to fill in to complete this step.
ii.
Reference your Module Two lab or resources on how
to populate these fields if you need to.
iii.
Fields you’ll need to populate: customerNumber,
customerName, contactLastName,
contactFirstName, phone, addressLine1,
addressLine2, city, state, postalCode, country,
salesRepEmployeeNumber, and creditLimit.
iv.
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. Address the following in your
reflection:
Your preview ends here
Eager to read complete document? Join bartleby learn and gain access to the full version
- Access to all documents
- Unlimited textbook solutions
- 24/7 expert homework help
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.
B.
Compare and contrast
the different
queries
you ran
and how cardinality applies to them.
C.
Describe two
of the crucial
benefits of cardinality
in
this type of database.
What to Submit
Submit your responses using the
Cardinality and Targeted Data
Template
. Each screenshot and its explanation should be sized to
approximately one quarter of the page, with a description written
below the screenshot. After you download it, rename your copy of
this document by adding your last name to its file name. This will
help organize your work once it has been submitted.