Chapter 3 Relational Algebra

ATTEMPT THIS ONLY WHEN YOU FINISH THE PRACTICAL EXCERCISES READ THE NOTES BELOW AND ANSWER THE QUESTIONS PLEASE BRING THIS NOTES TO NEXT CLASS Chapter 3 : RELATIONAL ALGEBRA Relational algebra operators operate on Relations (or tables) to produce new relations. In this method data retrieval functions are specified by a procedural language. It is based on algebraic concepts that consist of a collection of operators that are defined upon relations and produce as result other relations. We shall see some basic operators like: Selection, Projection, Union, Intersection, Difference, Cartesian product and Join.

1. Selection: When applied on a relation, this operator results in a horizontal (selected row-wise) selection of a portion of that relation. Consider the following relation: EMPLOYEE EMPLOYEE NAME SEX AGE SALARY Mustafa Male 25 2000 Salima Female 40 3000 Ahad Male 36 4500 Muneera Female 28 3800 The selection operation of Age < 30 or Salary > 4000 gives the following relation: EMPLOYEE NAME AGE SALARY

EMPLOYEE NAME

AGE

SALARY

EMPLOYEE NAME Mustafa

AGE 25

SALARY 2000

28

Ahad Muneera

36 28

4500 3800

Write the selection of all females above 35. EMPLOYEE NAME SEX AGE

SALARY

2. Projection Projection gives a vertical (selected columns only) selection of a portion of a relation as the result. EMPLOYEE NAME DEPARTMENT MANAGER Maryam Sales Ali Latifa Sales Ali Mustafa Marketing Saif Mohammed Accounts Mansoor The projection attributes name and head from the above EMPLOYEE relation would be as follows: EMPLOYEE NAME MANAGER Maryam Ali Latifa Ali Mustafa Saif Mohammed Mansoor Write the projection of Department and Manager:

3. Union This operator when applied on two relations NATIONALS and MANAGERS results in a combination of a new relation. Remember to remove duplicates if any. Consider the following relations: NATIONALS and MANAGERS NATIONALS ID NAME 145 Mohammed 238 Ali

AGE 25 40

29

650

Anisa

36 MANAGERS ID NAME 99 Ali 232388 Ali 650650 Anisa

AGE 52 39 26

Then NATIONALS Union MANAGERS gives the following relation. NATIONALS ∪ MANAGERS ID NAME AGE 145 Mohammed 25 238 Ali 40 650 Anisa 36 99 Ali 52

4. Intersection This operator when applied on two relations GRADUATES and MANAGERS results in a perfectly matching combination of a new relation. Consider the following relation: GRADUATES and MANAGERS Consider the following relation, Arabic Speakers ID NAME 145 Mohammed 238 Ali 650 Anisa

AGE 25 40 36

English Speakers ID 99 238 650

NAME Ali Ali Anisa

AGE 52 39 26

The GRADUATES intersection MANAGERS gives the following relations: Arabic speakers ∩ English Speakers ID NAME AGE 145 Mohammed 25 238 Ali 40 650 Anisa 36 What is the meaning of the new relation after Intersection?

5. Difference This operator when applied on two relations ARABS and MANAGERS results in a subtraction of rows to result in a new relation. Consider the following relations: ARABS, MANAGERS

30

ARABS ID 145 238 650

NAME Mohammed Ali Anisa

AGE 25 40 36

MANAGERS ID NAME 99 Ali 238 Ali 650 Anisa

AGE 52 39 26

Then ARABS – MANAGERS gives the following relation. ARABS - MANAGERS ID NAME 145 Mohammed

AGE 25

It is like a Subtraction – all records in the first relation subtract all records in the second relation. What is MANAGERS – ARABS relation?

6. Cartesian Product It is also called the Product operation. It takes two relations and returns a relation where every row of the first relation is concatenated with every Tuple in the second relation. Consider the following two relations: EMPLOYEE NAME DEPT GRADE Maryam Sales 4 Latifa Sales 4 Mustafa Marketing 5 Mohammed Accounts 3 DEPARTMENT DEPT_NAME Sales Marketing Accounts

MANAGER Ali Saif Mansoor

BUDGET 1,200,000 50,000 50,000,000

Now the first relation has 3 columns and the second relation has 3 columns. The resulting relation has 3+3=6 columns. Each row of relation EMPLOYEE appears with each relation of DEPARTMENT and so there are 4x3=12 records shown below: EMPLOYEE product DEPARTMENT NAME DEPT GRADE DEPT_NAME MANAGER BUDGET Maryam

Sales

4

Sales

Ali

1,200,000

31

Maryam Maryam Latifa Latifa Latifa Mustafa Mustafa Mustafa Mohammad Mohammad Mohammad

Sales Sales Sales Sales Sales Marketing Marketing Marketing Accounts Accounts Accounts

4 4 4 4 4 5 5 5 3 3 3

Marketing Accounts Sales Marketing Accounts Sales Marketing Accounts Sales Marketing Accounts

Saif Mansoor Ali Saif Mansoor Ali Saif Mansoor Ali Saif Mansoor

50,000 50,000,000 1,200,000 50,000 50,000,000 1,200,000 50,000 50,000,000 1,200,000 50,000 50,000,000

Write the DEPARTMENT product EMPLOYEE DEPARTMENT product EMPLOYEE DEPT_NAME MANAGER BUDGET NAME

DEPT

GRADE

Summary of Relational Operations

Summary : Relational algebra is a set of operations on relational databases that allow retrieval of data. We know that in mathematics, a set can have any number of subsets. A set is the subject of another if all its members are also members of other set. For example, consider the following two sets: We can state here that S is a subset of S because all the elements of S are also the elements of S.

32

As in the case of set theory in mathematics, we can treat a table as a set and derive some rows from that table as its subset. In effect, we will have another table with the same header, but with a different name. For example, from a student table, we can derive another table that contains records for only those students who have passed. Thus we restrict the number of rows that we want. The idea in a restrict operation is to select only the desired rows and eliminate the unwanted rows. The operation of eliminating columns in a table is called a project operation. Projection operation on a table leads to the formation of another table by copying specified columns of the original table. During the process, if any duplicate rows are found because of this elimination, they are ignored too. Product we know of Cartesian product in mathematics. Cartesian product of two sets yields a third set. This set contains pairs of elements. Here, the first element in each pair belongs to the first set, and the second element in each pair belongs to the second set. Union In mathematical set theory, the union of two sets is a set that contains elements present in either or both of the sets. In the process, any resulting duplicates are eliminated. By using union, multiple queries can be put together and their output merged. Intersection In mathematics, the intersection of two sets is the set of elements that are common to both the sets. The result of intersection is the rows common to the rows produced by the individual queries. In mathematics, the difference between two sets is a set that contains members of the first set that are not in the second set. Join joins two or more tables by using operators such as equal to, not equal to, greater than etc. Joining two (or more) tables has some prerequisites, if it has to be of some practical value: The tables should be joined based on a common column. The common column should be compatible in terms of domain.

33

TUTORIAL 4 1. What does relational algebra work with? 2. Explain the following operators: a. Selection b. Projection c. Union d. Difference 4. What does a Join operator do? 5. Perform a join operation of the following relations where PROJ_NAME = PROJ_NAME? PROJECT PROJ_NAME Sultan Centre Markat Al Bahja Villa

LEADER Ali Saif Mansoor

LOCATION Azaiba Seeb Qurum

PROJECT STAFF_NAME Ahmed Bader Khalfan

PROJ_NAME Sultan Centre Villa Villa

TIME 2 months 1 month 3 months

PROJECT join STAFF PROJ_NAME LEADER

LOCATION

STAFF_NAME PROJ_NAME

TIME

34