Module 4-2 Lab: Cardinality and Targeted Data
Overview
Because information changes rapidly, monitoring and reviewing it is critical. It’s important for data analysts and scientists to be able to add or delete records due to the changing nature of information. Another benefit to working with data is identifying relationships that exist between different entities. For example, manufacturers might want to identify why their equipment came back, where it came from, or how long customers took to return it, along with a host of other reasons.
Prompt
In this lab, you will be using an existing database that is preloaded into Codio from MYSQLTutorial.org. You will need to compare records from different locations, identify cardinality, delete records, and reflect on the value of these skills. You will also need to articulate how cardinality can be applied and what its benefits are. All your screenshots and written responses need to be placed in the Cardinality and Targeted Data Template that goes with this assignment.
Reference
MySQLTutorial. (n.d.). MySQL sample database classicmodels [Data file]. Available from https://www.mysqltutorial.org/mysql-sample-database.aspx
Before you begin, load the “classicmodels” data set:
Start a terminal session and run this command: mysql <>
Type mysql in the command line and begin working with SQL the way you have 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.
Validate the completion of this step with a screenshot of these two tables.
Retrieve order details for order Number 10330, 10338, and 10194 by running SELECT queries with WHERE clauses against the orders table.
Validate the completion of this step with a screenshot.
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.
Now, identify what type of cardinality this represents in the entity relationship model.
Delete records from the payments table where the customer number equals 103.
Run a describe statement to identify fields in the payments table first.
Select the records from the payments table for customer number 103 before deleting them.
Validate that the above instructions have worked with a screenshot.
Delete the records from the payments table for customer number 103.
Run a SELECT statement against the table to show that customer number 103 is no longer there.
Validate the completion of this step with a screenshot.
Retrieve customer records for employee Rep Barry Jones and identify relationships. Remember: SELECT, FROM, Inner Join, and WHERE.
Use Barry’s employeeNumber, 1504, and perform a join between the customer salesRepEmployeeNumber to retrieve these records.
Identify if these entities demonstrate one-to-one or one-to-many relationships.
Retrieve records for customers who reside in Massachusetts, then identify their sales rep and the relationship of entities. Remember: SELECT, FROM, Inner Join, and WHERE.
Use employee.firstName and employee.lastName in your command.
Identify if these entities demonstrate one-to-one or many-to-many relationships.
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. Think of this as your signature.
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;
You should now be seeing all of the fields that you’ll need to fill in to complete this step.
Reference your Module Two lab or resources on how to populate these fields if you need to.
Fields you’ll need to populate: customerNumber, customerName, contactLastName, contactFirstName, phone, addressLine1, addressLine2, city, state, postalCode, country, salesRepEmployeeNumber, creditLimit
Run a select statement on the customers table, capture it in a screenshot, and put it in your template.
Reflection: Use the lab environment or the screenshots you’ve worked with for this step. To retrieve information you were previously working with, run the mysql < mysqlsampledatabase.sql=””>
Define how cardinality is applied to the databases you’ve been working with and why different numbers of records returned from the different offices.
Compare and contrast the different queries you ran and how cardinality applies to them.
Describe two of the crucial benefits of cardinality in this type of database.
The Correct Answer and Explanation is :
Understanding Cardinality in Databases
Cardinality in databases refers to the uniqueness of data values contained in a particular column. It helps define the relationship between tables, which can be one-to-one (1:1), one-to-many (1:M), or many-to-many (M:N). Understanding and applying cardinality correctly is essential in relational databases as it ensures data integrity, efficiency, and proper data retrieval.
In this lab, we examined cardinality through various queries, including retrieving employee records based on office locations, retrieving order details, and identifying customer relationships with sales representatives. The different numbers of records returned from different offices, such as San Francisco and New York, highlight the concept of one-to-many relationships. For example, multiple employees can be assigned to a single office, but each employee can only be in one office at a time, creating a one-to-many (1:M) relationship.
We also observed cardinality when retrieving customer records assigned to a particular sales representative. A single sales representative, such as Barry Jones, may handle multiple customers, demonstrating another example of a one-to-many (1:M) relationship between the employees and customers tables.
Comparison of Queries and Cardinality
When retrieving employee records based on location, we performed a join between employees and offices, which returned multiple employees for a given city. The cardinality here is one-to-many (1:M) because each office location may have multiple employees.
When retrieving order details for specific order numbers, we worked with a one-to-one (1:1) relationship, as each order number corresponds to a single order record.
Similarly, deleting records from the payments table and verifying their removal showcased another aspect of cardinality. A customer may have multiple payment records, but each payment is uniquely tied to one customer, illustrating a one-to-many (1:M) relationship.
Crucial Benefits of Cardinality
- Data Integrity and Accuracy:
Cardinality ensures that relationships between tables are properly maintained. This prevents duplication and inconsistencies, ensuring that each entity has well-defined connections in the database. For example, a customer may place multiple orders, but each order belongs to only one customer, ensuring accurate tracking of transactions. - Efficient Query Performance:
Understanding cardinality allows for optimized database indexing, reducing query execution time. When performing joins or filtering data, knowing the cardinality ensures that queries are structured to retrieve only relevant records efficiently. For example, when retrieving records for customers from Massachusetts, understanding that each customer is linked to a single sales representative allows for better indexing and faster retrieval.
Overall, cardinality is a foundational concept in relational databases, ensuring structured, optimized, and scalable data management.