Relational Algebra DBMS / RDBMS

Relational algebra had firstly predicted by EF codd. in 1970, EF codd introduced an algebra which work as base for relational database and query language in its research paper " Relational algebra model of data". in relational algebra mainly five primitive operations are used.

1. selection (σ)

2. projection (π)

3. Cartesian product (x)

4. set union (u)

5. set difference (-)

except it, other operations like-

1. set intersection (A)

2. rename

3. join etc are used.

RDBMS is a computer application software which stores data with well arranged and continuity in form of relations. RDBMS is relational model based DBMS, which obey all the fundamental principle of relational algebra. operations of relational algebra are supported by SQL queries and access RDBMS. in present time, all useable DBMS software like- Oracle, Sybase, MY SQL, SQL server, Ingress, FoxPro, MS-Access  etc. are the examples of RDBMS.

To understand operations of relational algebra we study following tables-

teacher_details  

Emp code	Name	City	Sub	Fees
1001	Shyam Naik	Barwani	Physics	5000
1002	Ajay Yadav	Barwani	Maths	6000
1003	Ritesh Joshi	Manawar	CS	3500
1004	R.K. Gupta	Barwani	Physics	3000
1005	Himanshu Soni	Khukshi	Maths	4000

student_details

En. no.	SName	Surname	DOB
5001	Pallavi	Yadav	1998
5002	Aakash	Nirgude	1998
5003	Anjana	Meena	1997
5004	Devendra	Mandloi	1996
5005	Pankaj	Tomar	1996
5006	Vaishnavi	Tomar	1997

registration

Emp code	En. no.
1001	5002
1001	5004
1002	5002
1003	5001
1004	5005
1004	5004
1004	5006

1. Selection operation (σ) :-

notation : σ

purpose : pick some touple from the input relation according given predicate.

input : A relation R.

output : has same columns as relation R but those rows which satisfies the predicate P.

example :- Q. give details of teachers whose course fees less than 4000.

syntax :-    σ_(predicate) Relation

                 σ_(fees<4000)Teacher_Details.

result :-

Empcode	Name	City	Sub.	Fees
1003	Ritesh Joshi	Manawar	CS	3500
1004	R.K. Gupta	Barwani	Physics	3000

2. Projection operation (π):-

Notation : π

purpose : pick some column from input relation according to given predicate.

input : A relation R.

output : has some touples as relation R but those columns which satisfies the predicate.

example :- Q. display EN.no. and SName from students_details table.

syntax :- π_(predicate) Relation

                π_{(En.no., SName)} student_details.

result :-

En. no.	S Name
5001	Pallavi
5002	Aakash
5003	Anjana
5004	Devendra
5005	Pankaj
5006	Vaishnavi

3. Cartesian product operation (x) :-

notation : X

purpose : produce a single relation of  paired touples of two or more relations.

input : relation R & S.

output : relation that has all the combination of attributes of input relation R & S.

example  :- Q. how many student learns from one teacher.

syntax :- π_(name) Teacher_details X π_(SName)student_details

Name	SName
Ajay	Pallavi
Ajay	Aakash
Ajay	Anjana
Ajay	Devendra
Ajay	Pankaj
Ajay	Vaishnavi
..
..
..
Ketan	Pallavi
Ketan	Aakash
Ketan	Anjana
Ketan	Devendra
Ketan	Pankaj
Ketan	Vaishnavi

4. set union :-

Notation : U

purpose : produce a single relation of all touples from two or more identical relations.

input : relation R & S.

output : relation that has same schema as per R & S.

note :- set union is binary relation which applied on two or more union compatible relations. it means both relations are same degree, same attribute type and same domain set union will produce a relation in output. if there is duplicate touples in input. relations then they will display once.

example :- Q. provide Empcode of all teachers who presently teaches or not.

               π_(Empcode)teacher_details U π_(Empcode) registration.

result :-

Emp code

1001

1002

1003

1004

1005

5. set intersection (n) :-

Notation : n

purpose : produce a single relation of common touples from two or more identical relations.

input : relation R & S.

output : relation that has same schema as per input relations R and S.

input relations must be union compatible.

example :- Q. provide Empcode of teachers who presently teaches students.

    π_(Empcode) teacher_details n π_(Empcode) registration

result :-

Empcode

1001

1002

1003

1004

6. set difference (-) :-

notation : -

purpose : produce a single relation of distinct (not common) touples from two or more relations.

input : relation R & S.

output : relation that has same schema as per input relation R & S.

example :- provide Empcode of teachers who presently not teaches any student.

       π_(Empcode) teacher_details - π_(Empcode) registration

result :-

Empcode

1005

7. rename (ρ) :-

Notation : ρ

purpose : rename the input relation.

input : A relation R .

output : produce a single relation that has same schema as per relation R but with different name.

example:- change the name of teacher_details to faculty.

  ρ_{(new table name)} <old table name>

  ρ_(faculty) Teacher_Details