Table of Contents
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:
Unary Relational Operations
- SELECT
- PROJECT
- RENAME
Relational Algebra Operations from Set Theory
- UNION
- INTERSECTION
- DIFFERENCE
- CARTESIAN PRODUCT
Binary Relational Operations
- 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)
