# Relational Operations

All procedural relational query languages provide a set of operations that can be applied to either a single relation or a pair of relations. These operations have the nice and desired property that their result is always a single relation. This property allows one to combine several of these operations in a modular way. Specifically, since the result of a relational query is itself a relation, relational operations can be applied to the results of queries as well as to the given set of relations.

The most frequent operation is the selection of specific tuples from a single relation (say *instructor*) that satisfies some particular predicate (say *salary* > $85,000). The result is a new relation that is a subset of the original relation(*instructor*). For example, if we select tuples from the *instructor* relation of Figure 2.1, satisfying the predicate “*salary* is greater than $85000”, we get the result shown in Figure 2.10.

Another frequent operation is to select certain attributes (*columns*) from a relation.The result is a new relation having only those selected attributes. For example, suppose we want a list of instructor *ID* and salaries without listing the *name* and *dept_name* values from the instructor relation of Figure 2.1, then the result shown in Figure 2.11, has the two attributes *ID* and *salary*. Each tuple in the result is derived from a tuple of the *instructor* relation but with only selected attributes shown.

**join**operation allows the combining of two relations by merging pairs of

*instructor*and

*department*tables with the new tuples

*department*relation for the instructor’s department.

**natural**

**join**, a tuple

from the

*instructor*relation matches a tuple in the

*department*relation if the values of their

*dept_name*attributes are the same. All such matching pairs of tuples are present in the join result. In general, the natural join operation on two relations

matches tuples whose values are the same on all attribute names that are common

to both relations.

**Cartesian**

**product**operation combines tuples from two relations, but unlike

the join operation, its result contains

*all*pairs of tuples from the two relations,

regardless of whether their attribute values match.

The

**union**operation performs a set union of two “similarly structured” tables

(say a table of all graduate students and a table of all undergraduate students).

For example, one can obtain the set of all students in a department. Other set

operations, such as

**intersection**and

**set**

**difference**can be performed as well.

example, if we want to find the

*ID*and

*salary*for those instructors who have salary

greater than $85,000, we would perform the first two operations in our example

above. First we select those tuples from the

*instructor*relation where the

*salary*

value is greater than $85,000 and then, from that result, select the two attributes

*ID*and

*salary*, resulting in the relation shown in Figure 2.13 consisting of the

*ID*and

*salary*. In this example, we could have performed the operations in either

order, but that is not the case for all situations, as we shall see.

select the

*dept_name*attribute from the

*instructor*relation, there are several cases

of duplication, including “Comp. Sci.”, which shows up three times. Certain relational languages adhere strictly to the mathematical definition of a set and remove

duplicates. Others, in consideration of the relatively large amount of processing

required to remove duplicates from large result relations, retain duplicates. In

these latter cases, the relations are not truly relations in the pure mathematical

sense of the term.

updated by inserting new tuples, deleting existing tuples, or modifying tuples by

changing the values of certain attributes. Entire relations can be deleted and new

ones created.

**Frequently Asked Questions**

## Recommended Posts:

- Characteristics of the Database Approach
- View of Data
- Subclasses, Superclasses, and Inheritance
- what is database management system
- Database-System Applications
- Specialization and Generalization
- Constraints and Characteristics of Specialization and Generalization Hierarchies
- Modeling of UNION Types Using Categories
- A Sample UNIVERSITY EER Schema, Design Choices, and Formal Definitions
- Example of Other Notation: Representing Specialization and Generalization in UML Class Diagrams
- Data Abstraction, Knowledge Representation, and Ontology Concepts
- Using High-Level Conceptual Data Models for Database Design
- Using High-Level Conceptual Data Models for Database Design
- Using High-Level Conceptual Data Models for Database Design
- Using High-Level Conceptual Data Models for Database Design

**3/5**