DAD 220 Module Four 4-2 Lab Cardinality and Targeted Data PDF

Preview

Summary

Download DAD 220 Module Four 4-2 Lab Cardinality and Targeted Data PDF

Description

1

4-2 Cardinality and Targeted Data

DAD-220-T387 Intro to Struct Database Env 21EW3 Professor Andrew August

January 31, 2021

2 1. Retrieve employee tuples and identify the number of employees in San Francisco and New York. a. In this picture you can see the employees for both California and New York

3 2. Retrieve order details for orderNumber 10330, 10338, and 10194 and identify what type of cardinality this represents in the entity relationship model.

a. In the picture below you can see the information typed in to get the information requested.

b. The relationships that were used in the ERD document would be the order number and product code.

c.

The type of cardinality that were used in the ERD document would be an optional attribute or plural attributes as there is one order with said order details. From the order details would get that product/products from that order

4

3. Delete records from the payments table where the customer number equals 103. a. In the picture below you can see that I have described payments before deleting information

5 b. In the picture you can see I have selected the customer number 103 from the payment table

6 c. In the picture you can see that we have deleted the customer number 103 d. In the same picture below you can see that we have verified that 103 was deleted

7 4. Retrieve customer records for employee Rep Barry Jones and identify if the relationships are one-to-one or one-to-many. a. In the picture below you can see how we found Rep Barry Jones. With the entities this shows us a one to many relationship.

8 5. Retrieve records for customers who reside in Massachusetts, and identify if the relationships are one-to-one or one-to-many. a. In the picture below you can see how we found customers in MA and who their rep is. The relationship is actually not either of the two options as an employee in this case can be optional one, one or many. This would be a many to many example.

9 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. In the picture below you can see that I added myself to the customers table with customer number 10003. I used the SELECT * From customers WHERE customerNumber=10003 to prove that I was added to the table.

10 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. i.

Within this database cardinality is applied by having both one to one in the way that one customer might only have a single order in this database or one to many by one single sales rep having many customers with many orders.

b. Compare and contrast the different queries you ran and how cardinality applies to them. i.

With the different queries that were ran we can see how we can use them differently. For example, in the second step we were looking for just orders that did not have anything to do with a sales rep. This was the oneto-one process as we did not need to find out any information about a sales rep. In the option we used the one to many by searching for an employee number and how that related to the sales and customers that said employee has.

11 c. Describe two of the crucial benefits of cardinality in this type of database. i.

There are many benefits in this type of database but the two that stick out to myself the most are being able to find the records of a single sales rep very quickly using the one to many. By this we can see how many customers the rep is representing very quickly. The other is the one to one and how quick and easy this again makes it finding a customer based on a single order. We can still track what products they might have purchased but we do not need to see who their rep is as they have only made a single order....