*Interpreting Multirelation Queries*

On September 06, 2014, In The Database Language SQL by Admin

Views (1099)

There are many ways to define the meaning of the select-from-where expressions that we have just studied. All are equivalent, in the sense that they each give the same answer for each query applied to the same relation instances. We shall examine each in turn.

### Nested Loops

The semantics that we have implicitly used in examples so far is that of tuple variables. Remember that a tuple variable ranges over all tuples of the corresponding relation. A relation name that is not aliased is also a tuple variable ranging over the relation itself, as we mentioned in the box on "Tuple Variables and Relation Names". If there are many tuple variables, we may imagine nested loops, one for each tuple variable, in which the variables each range over the tuples of their respective relations. For each assignment of tuples to the tuple variables, we decide whether the WHERE clause is true. If so, we produce a tuple consisting of the values of the expressions following SELECT; note that each term is given a value by the current assignment of tuples to tuple variables. This query-answering algorithm is suggested by Figure 1.### Parallel Assignment

There is an equivalent definition in which we do not explicitly create nested loops ranging over the tuple variables. Rather, we consider in arbitrary order or in parallel, all possible assignments of tuples from the appropriate relations to the tuple variables. For each such assignment, we consider whether the WHERE clause becomes true. Each assignment that produces a true WHERE clause contributes a tuple to the answer; that tuple is constructed from the attributes of the SELECT clause, evaluated according to that assignment.### Conversion to Relational Algebra

A third approach is to relate the SQL query to relational algebra. We start with the tuple variables in the FROM clause and take the Cartesian product of their relations. If two tuple variables refer to the same relation, then this relation appears twice in the product, and we rename its attributes so all attributes have unique names. Likewise, attributes of the same name from different relations are renamed to avoid uncertainty.Having created the product, we apply a selection operator to it by converting the WHERE clause to a selection condition in the obvious way. That is, each attribute reference in the WHERE clause is replaced by the attribute of the product to which it corresponds. Eventually, we create from the SELECT clause a list of expressions for a final (extended) projection operation. As we did for the WHERE clause, we interpret each attribute reference in the SELECT clause as the corresponding attribute in the product of relations.

**Example 1 :**Let us convert the query of "Tuple Variables" Example 1 to relational algebra. First, there are two tuple variables in the FROM clause, both referring to relation MovieStar. In this way, our expression (without the necessary renaming) begins:

MovieStar x MovieStar

The resulting relation has eight attributes, the first four correspond to attributes name, address, gender, and birthdate from the first copy of relation MovieStar, and the second four correspond to the same attributes from the other copy of MovieStar. We could create names for these attributes with a dot and the aliasing tuple variable e.g., Star1.gender - but for succinctness, let us invent new symbols and call the attributes simply A

_{1}, A

_{2}, . . . , A

_{8}. Thus, A

_{1}corresponds to Star1.name, A

_{5}corresponds to Star2.name, and so on.

Under this naming strategy for attributes, the selection condition obtained from the WHERE clause is A

_{2}= A

_{6}and A

_{1}< A

_{5}. The projection list is A

_{1}, A

_{5}. Thus,

renders the entire query in relational algebra.

### Tags

- tuple variables
- nested loops
- relational algebra
- attributes
- Instead-Of Triggers
- Schema-Level Constraints and Triggers
- Tuple-Based CHECK Constraints
- Constraints on Attributes and Tuples
- Declaring Foreign-Key Constraints
- Keys Declared With UNIQUE
- Constraints and Triggers
- Interpreting Queries Involving Views
- Modifying Views
- View Definitions
- Introduction to Selection of Indexes
- Default Values / Indexes
- Simple Table Declarations
- Defining a Relation Schema in SQL
- Deletion / Updates
- Database Modifications
- Grouping / HAVING Clauses
- Full-Relation Operations
- Natural Joins / Outerjoins
- Subqueries in FROM Clauses
- Conditions Involving Tuples
- Subqueries
- Union, Intersection, and Difference of Queries
- Disambiguating Attributes
- Queries Involving More Than One Relation
- Null Values and Comparisons Involving NULL
- Selection in SQL
- Projection in SQL
- The Database Language SQL
- Additional Constraint Examples
- Referential Integrity Constraints
- Constraints on Relations
- Extending the Projection Operator
- Extended Operators of Relational Algebra
- Selection on Bags / Product of Bags / Joins of Bags
- Union, Intersection, and Difference of Bags
- Relational Operations on Bags
- A Linear Notation for Algebraic Expressions
- Dependent and Independent Operations
- Renaming
- Combining Operations to Form Queries
- Selection / Cartesian Product
- Set Operations on Relations
- An Algebra of Relational Operations
- Relational Algebra
- Attribute Lists
- Semistructured Data Representation
- Object-Oriented Versus Object-Relational
- Nested Relations
- What If There Is No Key
- Representing ODL Relationships
- Representing Other Type Constructors
- Representing Set-Valued Attributes
- Nonatomic Attributes in Classes
- Declaring Keys in ODL
- Subclasses in ODL / Multiple Inheritance in ODL
- Types in ODL
- Methods in ODL
- Multiplicity of Relationships
- Relationships in ODL / Inverse Relationships
- Attributes in ODL
- Introduction to ODL
- The Type System
- Decomposition into Fourth Normal Form
- Reasoning About Multivalued Dependencies
- Definition of Multivalued Dependencies
- Multivalued Dependencies
- Third Normal Form
- Boyce-Codd Normal Form
- Projecting Functional Dependencies
- Closing Sets of Functional Dependencies
- The Transitive Rule
- Why the Closure Algorithm Works
- Computing the Closure of Attributes
- Trivial Functional Dependencies
- The Splitting/Combining Rule
- Rules About Functional Dependencies
- Keys of Relations
- Using Null Values to Combine Relations - Comparison of Approaches
- An Object-Oriented Approach
- Converting Subclass Structures to Relations
- Handling Weak Entity Sets
- Combining Relations
- From E/R Relationships to Relations
- From Entity Sets to Relations
- Relation Instances
- Equivalent Representations of a Relation
- Tuples / Domains
- Attributes / Schemas
- The Relational Data Model
- Summary of The Entity-Relationship Data Model
- Weak Entity Set Notation
- Requirements for Weak Entity Sets
- Representing Keys in the E/R Model
- Keys in the E/R Model
- The Modeling of Constraints
- Picking the Right Kind of Element
- Design Principles
- Subclasses in the E/R Model
- Attributes on Relationships
- Elements of the E/R Model
- Database Programming
- The Query Processor
- Relational Database Systems