4-2 Lab_Cardinality and Targeted Data
docx
keyboard_arrow_up
School
Southern New Hampshire University *
*We aren’t endorsed by this school
Course
220
Subject
Information Systems
Date
Apr 3, 2024
Type
docx
Pages
8
Uploaded by mrsjdsmith15
Jessica DelValle-Smith
Southern New Hampshire University
DAD-220 Intro to Struct Database Environment
4-2 Lab: Cardinality and Targeted Data
November 15, 2023
MODULE FOUR LAB
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.
-
Since there may be either no occurrences of the order detail or only one instance, the cardinality type for this function would be 0-1. Despite the fact that three ordernumber are requested, each ordernumber will only correspond to one instance.
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
.
3)
The relationships that
were used in the ERD document would be the order
4)
number and product code.
The relationships that were used in the ERD document would be the order number and product code.
-
The order number and product code are the relationships that were used in the ERD document.
d)
Now, identify what type of cardinality this represents in the entity relationship model.
-
Given that there is only one order containing the specified order data, the type of cardinality used in the ERD document would be an optional attribute or multiple attributes. The product or products from that order would be obtained from the order's
information.
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.
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
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.
- Due to the fact that Rep Barry Jones only has one employee yet oversees numerous clients, the relationship is one-to-many.
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.
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
c.
Identify whether these entities demonstrate one-to-one or many-to-many relationships
.
o
According to the documents, two of the employees have numerous Massachusetts-based clients. Despite the fact that the query returned several employees and consumers, the relationship would be one to many because Julie the employee and Steve the client are both single individuals with numerous customers in the specified area.
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:
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.
Cardinality is used in this database by having both one to one and one to many relationships, where one customer may only have one order and another sales person may have many clients and many orders.
b.
Compare and contrast
the different
queries
you ran and how cardinality applies to them.
We can see how we can use them differently by looking at the various queries that
were run. For instance, in the second stage, we searched for just orders that had nothing to do with a sales representative. This was the one-to-one method because
we didn't need to learn anything about a sales representative. By looking for an employee number and how that related to the sales and customers that said person
had, we employed the one-to-many approach in the selection.
c.
Describe two
of the crucial
benefits of cardinality
in this type of database.
The two advantages that stand out to me the most in this form of database are the ones that allow you to rapidly locate the records of a particular sales representative. This allows us to immediately determine how many clients the representative is serving. The other is one-to-one marketing, and how quick and simple it is to locate a client based on a single order. Even though they may have just placed one order, we can still keep track of the things they may have bought. However, we do not need to know who their representative is.