DAD 220 Module Four Lab
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
DAD-220-X3
Subject
Mechanical_engineering
Date
Jun 4, 2024
Type
docx
Pages
7
Uploaded by MasterButterflyPerson397
DAD 220 Module Four Lab Template
Federico Castellanos
6/1/2024
Overview
Begin by doing the following steps in the IDE (Codio): 1.
Load the classicmodels data set.
2.
Start a new terminal session and run this command: mysqlsampledatabase.sql
3.
Write commands to use the classicmodels database and show its tables to verify that you are in the right place.
Then perform the steps below to complete the lab. Manually enter any commands you are asked to write. At the end of each step, replace the bracketed text in this template with your screenshot, response, or both, as indicated. Submit your completed template for grading and feedback. Screenshots should be sized to about 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 it.
Identify Cardinality and Table Relationships
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.
Now, identify what type of cardinality this represents in the entity relationship model. i.
Reference the Module Four Lab ERD diagram linked in the guidelines and rubric to help identify relationships.
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.
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.
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
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.
Identify whether these entities demonstrate one-to-one or many-to-many relationships.
(LOTS OF TYPOS, I’M SORRY)
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.
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. You may use the name of a celebrity or fictional character if you don’t use your own name.
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 see 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.
Populate the following fields:
customerNumber customerName contactLastName contactFirstName phone addressLine1 addressLine2 city state postalCode country salesRepEmployeeNumber creditLimit
iv.
Run a SELECT statement on the Customers table and capture it in a screenshot.
7.
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:
a.
Define how cardinality is applied
to the databases you’ve been working with. Explain why different numbers of records were returned from the different offices.
-
Usually, limitations called maxima and minima are used to define cardinality. Tables are connected using these phrases, which define properties within entities. Because of their various locations, separate offices offered different information. In the California office, for example, there were six rows, representing the six employees; in the New York office, there were two rows, representing the two employees.
b.
Compare and contrast the different queries
you ran and how cardinality applies to them.
As I ran different queries, I noticed two different cardinalities. A many-to-many link between Julie and Steve was represented by one cardinality, in which the table showed several employees connected to several clients. One employee (Barry Jones) was connected to numerous clients, indicating a one-to-many cardinality in this case.
c.
Describe two of the crucial benefits
of cardinality
in this type of database.
The first two advantages of cardinality that come to mind are:
1. It is quite advantageous to be able to merge two different tables since it makes information retrieval fast and easy and removes the requirement for data replication.
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
2. It is possible to modify a table's contents effectively by being aware of its properties.