DAD 220 4-2 Lab - Cardinality and Targeted Data.

Module Four Lab: Cardinality and Targeted Data
i i i i i i




Before we can even attempt the assignment, we must load the sample database to work with the cla
i i i i i i i i i i i i i i i i i


ssicmodels database. The code to launch the sample database is provided to us. We establish a MySQ
i i i i i i i i i i i i i i i i


L session as usual and enable the database with a USE statement.
i i i i i i i i i i i




1. Retrieve employee tuples and identify the number ofiemployees in San Francisco and N
i i i i i i i i i i i i


ew York.
i




SELECT firstName, lastName, jobTitle, offices.city
i i i i i


FROM employees INNER JOIN offices ON employees.officeCode = offices.officeCode WHE
i i i i i i i i i


RE state = 'CA';
i i i




SELECT firstName, lastName, jobTitle, offices.city FROM employees INNER JOIN offices O
i i i i i i i i i i


N employees.officeCode = offices.officeCode WHERE state = 'NY';
i i i i i i i

, Utilizing SELECT statements, we can narrow down which employees work in California and New Y
i i i i i i i i i i i i i i


ork. The first statement is provided, and the second is fundamentally the same. However, we change o
i i i i i i i i i i i i i i i i


ur desired state in the WHERE clause to attain our second set ofiemployees.
i i i i i i i i i i i i




2. Retrieve order details for orderNumber 10330, 10338, and 10194 and identify what type o
i i i i i i i i i i i i i


ficardinality this represents in the entity relationship model.
i i i i i i i


a.




b. In this model, the relationship between the orders and products tables would be a many
i i i i i i i i i i i i i i


-to-
many relationship. Looking at the three tables, we can determine that orderdetails is a J
i i i i i i i i i i i i i i


OIN with two foreign keys linking to the other tables. Moreover, a customer could have s
i i i i i i i i i i i i i i i


everal orders, and each order could contain multiple products.
i i i i i i i i




3. Delete records from the payments table where the customer number equals 103.
i i i i i i i i i i i


a. First, we identify the fields ofithe payments table by running a simple DESCRIBE state
i i i i i i i i i i i i i


ment.