This preview has intentionally blurred sections. Sign up to view the full version.
View Full Document
Unformatted text preview: TOPIC 8: Multiple Tables and the Join Perhaps the most powerful aspect of SQL and the relational database model is the ability to obtain information from multiple tables simultaneously. It is also a difficult concept to master without some understanding of the mathematical background of relational algebra and set theory. After reading this topic, you should be able to: • Have a basic understanding of set theory as it relates to relational database theory. • Create basic table join queries using the Structured Query Language (SQL). • Understand some of the mathematical basis behind the joining of relations. • Describe the use of Union, Intersection, and Difference operations. Lesson 1: Set Theory You may not realize it, but the work we have been doing in database design and basic SQL queries means you have been working with the mathematical concepts of Set Theory all along. We will begin to take a more direct review of the concepts and see how it is can be employed in SQL operations. After reading this lesson, you should be able to: • Define some of the basic concepts of set theory • See how it can relate to multiple table queries • See how the Union, Difference, and Intersection SQL operations can be implemented Basic Set Theory Concepts Actually, you have been doing this throughout your work, as set theory is part of the original framework for the SQL and relational database. As an example: • Each table is a set of information on one subject • When you use the SELECT statement, you obtain a result set • Each row in the result set is a member of the Basic Definitions We need to start with some general definitions and operations. A set is simply a collection of things . When a number x is in a set S we write x ∈ S meaning x is a member of the set S There are several operations that we can perform on mathematical sets that translate directly into SQL functions. The three set operations are: Intersection, Difference, and Union . Intersection In set theory, the intersection between two set are those items or elements that are identical in both sets. In mathematical symbols, the ∩ represents the intersection . We can take the intersection of two sets S and T as follows and translates as the intersection of S and T equals x where s is a member of the set S and x is a member of the set T: S ∩ T = {x : x ∈ S and x ∈ T} Examples: (1) If Set S is (−7, 12) and Set T is (3, 12) then S ∩ T = (7, 12) ∩ (3, 12) = (12) (2) We have two stew recipe collections with each row a recipe and each column an ingredient: Potatoes Water Lamb Peas Rice Chicken Stock Chicken Carrots Pasta Water Tofu Snap Peas Potatoes Beef Stock Beef Cabbage Pasta Water Pork Onions Potatoes Water Lamb Onions Rice Chicken Stock Turkey Carrots Pasta Vegetable Stock Tofu Snap Peas Potatoes Beef Stock Beef Cabbage Beans Water Pork Onions The intersection is the members whose attributes all match both sets....
View
Full Document
 Summer '08
 MCCONN,CHARLOTTERYOO,JUNGWOO
 Relational model, Peas Cabbage Onions, Tofu Beef Pork, Water Chicken Stock, Beef Stock Water

Click to edit the document details