Search

SQL: Converting SQL Queries into Relational Algebra

Languages for describing queries on a relational database –

### Structured Query Language

Structured Query Language (SQL) is a query language that allows performing some operations on data/query such as create, edit, modify/change, delete, etc.,

### Relational Algebra

Relational Algebra is a theoretical language used for converting SQL queries into relational algebra form by using operations & operators defined under relational algebra.

In other words, relational algebra is a theoretical language with operators applied to one or two relations to produce another.

Points to remember:

• A procedural language
• Not implemented in native forms in DBMS
• Basis for other HL DMLs

### Types of Relational Algebra Operation

Relational Operations are divided into three groups:

• SELECT
• PROJECT
• RENAME

#### Relational Algebra Operations from Set Theory

• UNION
• INTERSECTION
• DIFFERENCE
• CARTESIAN PRODUCT

• JOIN
• DIVISION

### Explanations of:

#### SELECT

The SELECT command is applied to the single table and takes queries from rows that meet conditions copying them into a new table.

Syntax:

```				```
SELECT Column name
FROM Table name
WHERE Condition
```
```

Symbolic form:

```				```
σ Age=20 (Student)
```
```

σ : σ is the symbolic representation of command SELECT.

Explanation:

Table name: Student
Column name: Age
Condition: 20

Select the column named Age from table Student where condition = 20.

#### PROJECT

The PROJECT command is applied to the single table and takes a query from columns, extracts the value from the table in vertical form, eliminates duplicate values, and places them into a new table.

Syntax:

```				```
PROJECT tablename OVER (column name,....., column name)
```
```

Symbolic form:

```				```
π Name (Student)

```
```

#### Combining SELECT and PROJECT

```				```
SELECT Student Where name = 'John'
PROJECT Name OVER(lastname, firstname)

π last name, firstname (  name = 'John'(Student))

```
```

#### JOIN

The JOIN operation is a combination of the SELECT and PRODUCT and returns possible projection operations.

The JOIN of two relations, say A and B, operates as follows:

• First form the product of A times B.
• Then make a selection to eliminate some tuples (criteria for the selection are specified as part of the join)
• Then (optionally) remove some attributes by means of projection.

#### NATURAL JOIN

NATURAL JOIN is an equijoin in which the repeated column is eliminated.

• This is the most common form of the join operation and is usually what is meant by JOIN

Syntax:

```				```
tableName1 JOIN tableName2 [GIVING newTableName]
```
```

### Operators

Operators are the same as mathematical operators and are always used in difficult relation algebra conditions.

• <, <=,
• >, >=,
• =, ,
• AND,
• OR,
• NOT

### Set Operations on Relations

• CARTESIAN PRODUCT
• UNION
• INTERSECTION
• DIFFERENCE

### Relation Operations

• Cartesian Product
• The cartesian product of two relations is the concatenation of every tuple of one relation with every tuple of second relations.
• The Cartesian product of relation A (having m tuples) and relation B (having n tuples) has m times n tuples.
• The Cartesian product is denoted A X B or A TIMES B.

Though converting a high-level language (SQL) to low-level relational algebra is challenging, we can define the concept logically and with a few examples. To overcome ambiguities, the relation symbols in a SQL statement are assigned a specific name through the alias (SQL aliases are used to give a table or a column in a table a temporary name. Aliases are often used to make column names more readable. An alias only exists for the duration of the query.) mechanism of SQL.

SQL statements, where a relation symbol occurs multiple times,  for example,

SELECT * FROM R,

R is rewritten into a SQL statement of the form

SELECT * FROM R, R R1

Here every occurrence is given a distinct (alias) name. Let us study two occurrences.

1. Select-from-where statements without sub-queries

Consider a general SELECT-FROM-WHERE statement of the form

```				```
SELECT   Select-list
FROM R1, . . . , R2 T1, . . .
WHERE (Where-condition)

```
```

Since, here query does not use sub queries in where-condition then it can be translated into the relational algebra as follows:

```				```
π Select-list σWhere-condition(R1  X ……..X   ρT1(R2) _ _ _ _ )
```
```

Note:  Here an alias R2 T1 in the FROM-clause corresponds to a renaming ρT1(R2).

If there is no WHERE clause then there is no need to include the selection σ  in the expression.

For omitting the projection (π) we obtain the translation of the following special case:

```				```
SELECT *
FROM R1, . . . , R2 T1, . . .
WHERE Where-condition

```
```

E.g.: SQL SELECT-FROM-WHERE statement is

```				```
SELECT Cinema_Name
FROM Actors_In, Cinema_Actor
WHERE Actor_Name = name AND date of birth = 1990
```
```

Translating relational algebra like

```				```
πCinema_NameσActor_Name=name (Actors_In X Cinema_Actor):
^ date of birth =1990

```
```

Example with Sub queries: The SQL query is

```				```
SELECT LastName, FirstName
FROM EMPLOYEE
WHERE Salary > (SELECT MAX (Salary)
FROM EMPLOYEE
WHERE IdNo = 2);

```
```

It can be split into the following sub-queries like

```				```
SELECT LastName, FirstName
FROM EMPLOYEE
WHERE Salary > 10000

```
```

Respective R.A expression:

```				```
πLastName, FirstName(σ Salary>10000(EMPLOYEE))
```
```
```				```
SELECT MAX (Salary)
FROM EMPLOYEE
WHERE IdNo = 2

```
```

Respective R.A expression:

```				```
MAX Salary (σIdNo = 2 (EMPLOYEE))
```
```

Translating Joins

```				```
(SELECT * FROM R R1) JOIN (SELECT * FROM R R1) ON R1.A = R2.B
ρR1(R) R1.A= R2.B R2(R)

```
```

Group and Having

```				```
SELECT Select-list
FROM From-list
WHERE Where-condition
GROUP BY Group-list
HAVING Having-condition

```
```
```				```
πA1;:::;An; Select-listσ Having-condition ϒ A1;:::;An; Group-list;Agg-list(E):
```
```

E.g.: SELECT name, SUM(length)

```				```
FROM Cinema Exce, Cinema
WHERE cert = SeniorProducer
GROUP BY name
HAVING MIN(year) <1960

```
```

Relational Algebra

```				```
π name;SUM(length) σMIN(year)<1960ϒname;MIN(year);SUM(length)
σ cert=SeniorProducer(CinemaExecx Cinema)
```
```

Share on