*Combining Operations to Form Queries*

If all we could do was to write single operations on one or two relations as queries, then relational algebra would not be as useful as it is. On the other hand, relational algebra, like all algebras, allows us to form expressions of arbitrary complexity by applying operators either to given relations or to relations that are the result of applying one or more relational operators to relations.

One can create expressions of relational algebra by applying operators to subexpressions, using parentheses when necessary to show grouping of operands. It is also possible to represent expressions as expression trees; the latter often are easier for us to read, although they are less convenient as a machine-readable notation.**Example (a) :** Let us reconsider the decomposed Movies relation of "Decomposing Relations" Example. Assume we want to know "What are the titles and years of movies made by Fox that are at least 100 minutes long?" One way to compute the answer to this query is:

1. Select those Movies tuples that have length ≥ 100.

2. Select those Movies tuples that have studioName = 'Fox'.

3. Compute the intersection of (1) and (2).

4. Project the relation from (3) onto attributes title and year.

In Figure (a) we see the above steps represented as an expression tree. The two selection nodes correspond to steps (1) and (2). The intersection node corresponds to step (3), and the projection node is step (4).

On the other hand, we could represent the same expression in a usual, linear notation, with parentheses. The formula

represents the same expression.

Incidentally, there is often more than one relational algebra expression that represents the same computation. For example, the above query could also be written by replacing the intersection by logical AND within a single selection operation. That is,

is an equivalent form of the query.

**Example (b) :**One use of the natural join operation is to recombine relations that were decomposed to put them into BCNF. Remember the decomposed relations from "Decomposing Relations" Example:

**Movies1 with schema {title, year, length, filmType, studioName}**

Movies2 with schema {title, year, starName}

Movies2 with schema {title, year, starName}

Let us write an expression to answer the query "Find the stars of movies that are at least 100 minutes long". This query relates the starName attribute of Movies2 with the length attribute of Movies1. We can connect these attributes by joining the two relations. The natural join successfully pairs only those tuples that agree on title and year: that is, pairs of tuples that refer to the same movie. Therefore, Movies1 ⋈ Movies2 is an expression of relational algebra that produces the relation we called Movies in "Decomposing Relations" Example. That relation is the non-BCNF relation whose schema is all six attributes and that includes many tuples for the same movie when that movie has many stars.

To the join of Movies1 and Movies2 we must apply a selection that enforces the condition that the length of the movie is at least 100 minutes. We then project onto the desired attribute: starName. The expression

implements the desired query in relational algebra.

### Tags

- relational algebra
- attributes
- natural join
- tuples
- 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
- Interpreting Multirelation 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
- 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