Scribd
Upload
248 views

Formal Relational Query

The document discusses formal query languages for relational databases, including relational algebra, tuple relational calculus, and domain relational calculus. Relational algebra is a procedural query language consisting of operations like select, project, join, and union. Tuple relational calculus is a declarative language that describes query results without specifying procedures. Domain relational calculus uses first-order predicate logic to specify queries over database domains.

Uploaded by

Saarthak Vadhera
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
248 views

Formal Relational Query

The document discusses formal query languages for relational databases, including relational algebra, tuple relational calculus, and domain relational calculus. Relational algebra is a procedural query language consisting of operations like select, project, join, and union. Tuple relational calculus is a declarative language that describes query results without specifying procedures. Domain relational calculus uses first-order predicate logic to specify queries over database domains.

Uploaded by

Saarthak Vadhera
Copyright
© Attribution Non-Commercial (BY-NC)
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 53

Formal Relational Query Languages(Relational Algebra and Relational Calculus)

Formal Relational Query Languages


We cover three formal languages. We start by presenting the relational algebra, which forms the basis of the widely used SQL query language. We then cover the tuple relational calculus and the domain relational calculus, which are declarative query languages based on mathematical logic.

The Relational Algebra


The relational algebra is a procedural query language. It consists of a set of operations that take one or two relations as input and produce a new relation as their result.

Operation of RA
The fundamental operations in the relational algebra are select, project, union, set difference, Cartesian product ,and rename. In addition to the fundamental operations, there are several other operationsnamely, set intersection, natural join,and assignment. We shall define these operations in terms of the fundamental operations.

Fundamental Operations
The select, project, and rename operations are called unary operations, because they operate on one relation. The other three operations operate on pairs of relations and are, therefore, called binary operations.

The Select Operation

Select Operation

Example

The Project Operation

Composition of Relational Operations

The Union Operation

The Set-Difference Operation

The Cartesian-Product Operation

The Cartesian-Product Operation

The Cartesian-Product Operation

Cartesian Product

The Rename Operation

Assignment Operation

Outer join Operations


The outer-join operation is an extension of the join operation to deal with missing information.

Outer join Operations

Full Outer join Example


The outer join or full outer join in effect combines the results of the left and right outer joins. For an example consider the tables Employee and Dept and their full outer join: Employee
Name Harry Sally George Harriet EmpID 3415 2241 3401 2202 DeptName Finance Sales Finance Sales Harry Sally 3415 2241 3401 2202 Finance Sales Finance Sales Production Harriet Harriet Charles

Employee =X= Dept


Name EmpID DeptName Mgr

Dept
DeptName Sales Production Mgr Harriet Charles

George Harriet

Extended Relational-Algebra Operations


Aggregation

Suppose that we want to find out the sum of salaries of all instructors; the relationalalgebra expression for this query is:

RA Example
Find the total number of instructors who teach a course in the Spring 2010 semester.

RA Example
Find the average salary in each department.

RA Example
Find the average salary of all instructors.

Aggregation Operation

Relational Calculus
The Tuple Relational Calculus The Domain Relational Calculus

The Tuple Relational Calculus


When we write a relationalalgebra(procedural query language) expression, we provide a sequence of procedures that generates the answer to our query. The tuple relational calculus, by contrast, is a. It describes the desired information without giving a specific procedure for obtaining that information.

The Tuple Relational Calculus

TRC Example

TRC Example
Find the instructor ID for each instructor with a salary greater than $80,000.

Find the names of all instructors whose department is in the Watson building.

The Domain Relational Calculus

Definition

DRC Example

You might also like