Relational algebra in SQL

Here is the relational algebra in SQL

Relational algebra in SQL is a procedural query language(PQL). It consists of a set of unary or binary operations that take input as one or two database relations and produce a new table as their output. The unary and binary operations are select, project, union, set difference, Cartesian product, and rename.

 

Fundamental operations in relational algebra:

Select:

The select operation is a unary operation that selects certain tuples that satisfy a given condition. We denote select operation by the lower case Greek letter sigma (σ).

For example, consider the table instructor

ID	name	dept_ name	salary
76766	Crick	Biology	72000
45565	Katz	Comp. Sci.	75000
10101	Srinivasan	Comp. Sci.	65000
83821	Brandt	Comp. Sci.	92000
98345	Kim	Elec. Eng.	80000
12121	Wu	Finance	90000
76543	Singh	Finance	80000
32343	El-Said	History	60000
58583	Califieri	History	62000

 

 

 

 

 

 

To select those tuples of the instructor relation where the instructor is in the “Finance” department, we write:

σdept_name = “Finance”(instructor )

Output:

12121	Wu	Finance	90000
76543	Singh	Finance	80000

We can find all instructors with salaries greater than $90,000 by writing:

σsalary>90000 (instructor)

Output:

83821

Brandt

Comp. Sci.

92000

Project:

It is a unary operation that projects its row, with certain attributes left out. Since a relation is a set, any duplicate rows are discarded. We denote the projection operation by the uppercase Greek letter pi (π).

For example,

πID, name, salary (instructor )

Union:

This operation is used to perform a union of the two relational databases. It is denoted by ∪.

Set difference:

The set-difference operation is denoted by the symbol −, allows us to find the difference between two relations. The expression table1− table2 produces a relation containing those tuples in table1 but not in table2.

Cartesian product:

The Cartesian-product operation is denoted by a cross symbol(×), which allows us to combine two relations. We write the Cartesian product of relations table1 and table2 as table1 × table2.

for example:

Table1:

ID	NAME	GENDER	AGE
1	Anu	F	19
2	Ram	M	20

Table2:

ID	COURSE
1	DS
2	DBMS

Output: Table1 X Table2

ID	NAME	GENDER	AGE	COURSE
1	Anu	F	19	DS
1	Anu	F	19	DS
2	Ram	M	20	DBMS
2	Ram	M	20	DBMS

Rename:

The rename operator is a unary operation and it is denoted by the lowercase Greek letter rho (ρ).

 

Also, read Creating an ER diagram for a class and stuck on what entities I can create.