DAD220 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
Industrial Engineering
Date
Feb 20, 2024
Type
docx
Pages
8
Uploaded by GeneralIceRook13
1
Module 4 Assignment 4-2 Lab
DAD 220 – INTRODUCTION TO STRUCTURAL DATABASE
Professor Chaunda Wilson
Ken
24th September 2023
Assignment 4-2 Lab: Cardinality and Targeted Data using Codio
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.
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.
2
Module 4 Assignment 4-2 Lab
1.
Retrieve employee tuples and identify the number of employees
in San Francisco and New York.
Answer:
> use classicmodels;
> select firstName, lastName, jobTitle, offices.city from employees
-> inner join offices on employees.officeCode = offices.officeCode
-> where state = 'CA';
> select firstName, lastName, jobTitle, offices.city from employees
-> inner join offices on employees.officeCode = offices.officeCode
-> where state = 'NY';
2.
Retrieve order details
for orderNumber 10330, 10338, and 10194 and identify what type of cardinality this represents in the entity relationship model.
A. Answer:
> show tables;
> select * FROM orders WHERE orderNumber IN (10330, 10338, 10194);
3
Module 4 Assignment 4-2 Lab
B. Answer:
Details of the three orders (10330, 10338, and 10194)
> SELECT orders.orderNumber, orderDate, productName, quantityOrdered, priceEach, quantityOrdered*priceEach AS TOTAL_PRICE
-> FROM orders INNER JOIN orderdetails ON orders.orderNumber = orderdetails.orderNumber
-> INNER JOIN products ON orderdetails.productCode = products.productCode
-> where orders.orderNumber IN (10330, 10338, 10194);
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
Module 4 Assignment 4-2 Lab
C. Answer:
Relationship between the tables that include products and orderdetails as seen below has a cardinality of one to many. This is because one product could have many order details.
3.
Delete records from the payments table where the customer number equals 103.
a.
Answer:
> describe payments;
> select * FROM payments where customerNumber = 103;
b. Answer:
> DELETE FROM payments where customerNumber = 103;
> select * FROM payments where customerNumber = 103;
One-many
5
Module 4 Assignment 4-2 Lab
4.
Retrieve customer records for sales representative Barry Jones and
identify
if the relationships
are one-to-one or one-to-many
.
Answers: > SELECT CONCAT(employees.firstName, " ", employees.lastName) AS SALES_REP,
customers.customerName AS CUSTOMER
-> FROM employees INNER JOIN customers ON employees.employeeNumber = customers.salesRepEmployeeNumber
-> WHERE employees.employeeNumber = 1504;
From the
table to retrieve customer records regarding the employee Barry Jones, the relationship between SALES_REP and CUSTOMER shows a
one-to-many cardinality.
As the Customer Barry Jones as one employee is shown to have many customers. The CONCAT command is used here to combine the first and last name of the employee.
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.
Answers:
> SELECT customers.customerName AS CUSTOMER, CONCAT(employees.firstName, " ", employees.lastName)AS SALES_REP
-> FROM employees INNER JOIN customers ON employees.employeeNumber = customers.salesRepEmployeeNumber
-> WHERE UPPER(customers.state) = 'MA';
From the table to retrieve records of customers who reside in Massachusetts, their SALES_REP are Julie Firrelli
and Steve Paterson. The cardinality in the relationship as seen in the table would be
many-to-many
, as it shows multiple employees having multiple customers.
6
Module 4 Assignment 4-2 Lab
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.
Answers:
> show databases;
> use classicmodels;
> show tables;
iii - iv
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
7
Module 4 Assignment 4-2 Lab
> INSERT INTO customers VALUES (2911, 'Kamsi Godwin', 'Ejiogu', 'Chukwuma', '862-270-7748', '369 Park Ave', 'APT F7', 'Orange', 'NJ', '07050', 'USA', NULL, 350000.00);
> select * FROM customers where customerNumber = 2911;
NOTE
For a better, readable, and vertical view of the lengthy table, I decided to use the ‘
\G
’ command instead of the ‘
;
’
> select * FROM customers where customerNumber = 2911 \G
8
Module 4 Assignment 4-2 Lab
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.
Answer:
Cardinality in mysql is used to define Limits in databases known as either maxima or minima. Maxima and minima describe attributes within entities and enable the connection of tables.
Different numbers of records returned from the different offices due to the difference in locations. Example is CA offices returning 6 rows as they have 6 employee, and NY office varied by returning 2 rows because they have two employees.
b.
Compare and contrast the different queries
you ran and how cardinality applies to them.
Answer:
In two queries I ran, the first was a one-to-many cardinality in regard to Barry Jones
, because there was just one employee (Barry Jones) with multiple customers. While the second was a many-to-many regarding Julie Firrelli
and Steve Paterson, as table reveals multiple employees with multiple customers.
c.
Describe two of the crucial benefits
of cardinality
in this type of database.
Answers:
1. Query performance optimization. This is because columns of high cardinality have many unique values which help to filter data efficiently.
2. Join performance, as high cardinality columns are more likely to provide unique keys for joining tables, thus reducing likelihood of overly large result set. Which means information can be accessed quickly and efficiently.