Relational Algebra(1) 2Relational Algebra(1).pptx

Relational Algebra(1)

1. Relational Algebra

2. Database state for company

3. Relational Algebra Overview These operations enable a user to specify basic retrievel requests (Queries) The result of these operation is a new relation

4. Select operation Select the employee tuples whose department number is 4: (employee) (The number of columns) = (The number of columns) employee (The number of tubles (rows) staisfies the condition) Select the employee tuples whose salary is greater than 30000 (employee) (The number of columns) = (The number of columns) employee (The number of tubles (rows) staisfies the condition)  

5. Select operation (More than one condition) (employee) (employee) Employee Table   Eno Ename Dno Salary 4 35000 4 25000 3 30000 2 20000 -      

6. Select operation (Complex) Employee working Dno=4 and got salary >30000 or Dno=4 and got salary <20000 (employee)  

7. Project Operation To list each employee’s first and last name and salary from employee table (Employee) To list each employee’s snn , salary and address from employee table (Employee)   snn salary address snn salary address -- -- -- <=  

8. Select and project operations For employees working in Dno=5, list F n ame, ssn, salary Select project (employee). (all columns in emplyee table , the rows scatisfy the condition) ( ) (all rwos in table , the list columns(Fname,ssn,salary))  

9. Union operation Including a ll tubles that are either in R or in S or in both R and S Duplicate tubles are eliminated Two conditions 1- R and S have the same number of attributes 2- Each pair of coresponding attributes in R and S have the same data type  

10. Union operation Example1

11. Union operation Example2

12. Intersection operation R A ll tuples that are in both R and S  

13. Difference operation R I ncludes all tuples that are in R but not in S  

14. Join operation Campine related tuples from various relations

15. Join operation example1

16. Join operation example2 Suppose that we want to retrieve the name of the manager of each department Department.MGRSSN = Employee.SSN

17. EXAMPLE2

18. Join operation example3 Suppose that we want to retrieve the name of the project and name of the controlling department

19. Join operation example 4 Suppose that we want to retrieve the name of the employee and names of the projects that worked on

20. Natural join R equires the two join attributes have the same name in both relations.

21. Outer join