DBMS - Relational Algebra

Procedural language

1. Relational Algebra A procedural language which is used to access database to read tables to read data in different ways. denoted using operators. each relational query describes a step by step procedure for computing the desired answer, based on the order in which operators are applied in the query.

2. Relational Algebra Operations: Selection projection Cartesian product Set Operations Join

3. Relational Algebra Selection: to fetch rows or tuple from the table. syntax: σ predicate (relation) predicate – logic using which the data from the relation is selected.

4. Relational Algebra Ex: Student - Table σ age>15 (Student) Reg. No Sname Age 1 AAA 15 2 BBB 18 3 CCC 16 Sname BBB CCC

5. Relational Algebra Projection: to project only a certain set of attributes of a relation. remove duplicate data from the columns. Syntax: Π A1,A2,… (r)

6. Relational Algebra Projection: Ex: Student - Table Π age (Student) Reg. No Sname Age 1 AAA 15 2 BBB 18 3 CCC 16

7. Relational Algebra Cartesian product: to combine data from two different relations into one and fetch data from the combined relation Syntax: A x B

8. Relational Algebra Cartesian product: Example Syntax: A1 x B1 Table A1 A B 1 1 2 2 Table B1 C D E 1 2 7 2 6 8 5 7 9 A1 x B1 A B C D E 1 1 1 2 7 1 1 2 6 8 1 1 5 7 9 2 2 1 2 7 2 2 2 6 8 2 2 5 7 9

9. Set Operations Union Intersection Set difference Relational Algebra

10. Relational Algebra Union: to fetch data from two relations or temporary relation. the relations specified should have same number of attributes and same attribute domain. duplicated tuples are automatically eliminated from the result. Syntax: A ᴜ B

11. Relational Algebra Union: Example Syntax: A1 ᴜ B1 Table A1 A B 1 1 1 2 2 1 Table B1 A B 1 2 2 3 A1 ᴜ B1 A B 1 1 1 2 2 1 2 3

12. Relational Algebra – Additional Operators Intersection: to fetch data from both tables which is common in both the tables. Syntax: A ∩ B

13. Relational Algebra Intersection: Example Syntax: A1 ∩ B1 Table A1 Name id aaa 1 bbb 2 ccc 1 Table B1 Name course aaa C ccc C++ A1 ∩ B1 Name aaa ccc

14. Relational Algebra Set-Difference: the result of set difference is tuples, which are present in one relation but are not in the second relation. Syntax: A - B

15. Relational Algebra Set-Difference: Example Syntax: A1 - B1 Table A1 A B 1 1 1 2 2 1 Table B1 A B 1 2 2 3 A1 - B1 A B 1 1 2 1

16. Relational Algebra Rename Operation: to rename the output relation for any query relation which returns the result. Syntax: ρ ( RelationNew , RelationOld )

17. Relational Algebra Rename: Example ρ (A1,B1) Table A1 A B 1 1 2 2 Table B1 A B 1 1 2 2

18. Relational Algebra – Additional Operators Join: to fetch data from both tables which is common in both the tables. Operator: *

19. Relational Algebra Natural Join: Example Syntax: A1* B1 Table A1 A B 1 1 1 2 2 1 Table B1 A C 1 2 2 3 A1 * B1 A B C 1 1 2 1 2 2 2 1 3