Select Operator

1
Select Operator

• The select operation is used to select a subset
  of tuples from a relation that satisfy a
  selection condition.
• Condition can include boolean operators AND, OR,
  NOT applied on relational operators lt, gt lt,gt,
  !,

2
Selection
3
Selection

• EXAMPLE Select all tuples from Student that
  have gpa gt 3.2
• s gpagt3.2 (STUDENT)
• EXAMPLE Select all tuples from Student that
  have gpa gt 3.0 or SID 123
• s gpagt3.0 OR SID 123 (STUDENT)

4
Selection Criteria in Selection Operator

• ltattribute namegt ltrelational operatorgt ltconstant
  valuegt
• age gt 25
• Constant value should be from the domain of the
  attribute on the left side
• ltattribute namegt ltrelational operatorgt ltattribute
  namegt
• S1 AND S2 S1 OR S2 Not S
• S1, S2, S are simple selection criteria

5
Projection Operator

• The project operator is used to select a subset
  of columns from the existing relation.
• The resulting relation has only specified
  attributes.
• It has all the rows from the original relation or
  a subset of them if there is repetition.

6
Projection

• P Name, gpa (STUDENT)
• Name gpa
• Bill Pierce 4.0
• Sue Jones 3.3
• Sue Smith 3.2

7
Join Operator

• Combines related tuples from two relations into
  single tuples.
• STUDENT JOIN PROFESOR
• AdvisorName

8
Join

• In join we take some rows from the first relation
  concatenated by some rows from the second
  relation such that they satisfy the condition
  criteria.
• In R1 x R2 we have all tuples mn where m
  belongs to R1 and N belongs to R2.
• In R1 JOIN R2 we have those tuples mn that
  satisfy the condition.

9
Join types

• Q Theta Join
• Tuples whose join attributes are null do not
  appear in the result
• EQUIJOIN
• Join with condition involving only relational
  operator
• In equijoin one attribute is redundant since its
  value is equal to another attribute
• NATURAL JOIN
• It is equijoin without the redundant attribute
• Natural join requires the same name for the two
  attributes in different tables

10
General Natural Join

• A more general but nonstandard defintion for
  natural join
• R S
• (ltlist1gt), (ltlist2gt)
• ltlist1gt specifies list of n attributes from R
• ltlist2gt specifies list of n attributes from S
• The lists are used to form equality comparison
  between pairs of corresponding attributes. These
  conditions are then AND-ed together. Only the
  list1 attributes are kept in the result.

11
Join

• Result of Join may be an empty relation if no
  tuples satisfy the condition
• The number of tuples in Join ranges from
• 0 to NumTuplesR NumTuplesS

12
Complete Set of Operatons

• Set of relational algebra operations
• s, P, U, -, x is a complete set of operations
  because ny of the other operations can be
  expressed as a sequence of operations from this
  set.
• R JOIN S s cond (R x S)
• cond
• R INTERSECTION S R U S ((R-S)U(S-R))

13
Division Operator

• Requirement Set of attributes in R A,B
  contains set of attributes in S A
• R( A, B) S(A)

14
Division R S

• Result are all x values of attribute B such that
  tuples bellow belong to R(A,B)

15
Aggregate Functions

• Relational Algebra with basic operations does not
  allow requests with aggregate functions on
  collections of values from the database such as
• Get average salary for all employees
• Get average salary for employees where DNO2

16
Numeric Functions

• Functions applied to a collection of numeric
  attribute values include
• Sum
• Average
• Maximum
• Minimum
• Count (count tuples)

17
Grouping

• Request may require grouping of tuples in a
  relation by the value of some of their
  attributes, for instance Major for STUDENTS, and
  then applying aggregate function independently to
  each group (with the same Major)
• Average or Max gpa for students in each Major

18
STUDENT Example

• STUDENT

19
Grouping Requests

• ltgrouping attributesgt F ltfunction listgt(R)
• Major F Average Gpa(STUDENT)

20
Example

• Major F MIN Gpa, MAX Gpa (STUDENT)

21
No attribute

• If no grouping attribute is specified, the
  functions are applied to the attribute values of
  all the tuples in the relation.
• The resulting relation has single tuple only.
• F Count Gpa, Average Gpa (STUDENT)

22
Recursive Closure Operators

• Another operation that cannot be specified in the
  basic relational algebra is recursive closure.
• It si applied to recursive relationship between
  tuples of the same type.
• EMPLOYEE entity
• SUPERVISION relationship

23
Example

• Find all supervisees of an employee e at all
  levels.
• This includes
• All employees E1 directly supervised by e.
• All employees E2 directly supervised by some
  employee from E1.
• All employees E3 directly supervised by some
  employee from E2.
• and so on

24
Example
25
Supervisees

• P SubID, SuperId (EMPLOYEE) ? SUPERVISION
• P SubID ( s SuperID e (SUPERVISION)) ? RESULT1
• P SubID ( RESULT1 Join SUPERVISION ) ? RESULT2
  SubIdSuperId
• RESULT ? RESULT1 U RESULT

26
Outer Join

• Outer Join
• Left Outer Join
• Right Outer Join
• Full outer Join

27
Left Outer join

• L Left Outer join R
• All tuples from left relation L joined with
  matching tuples from the right relation R.
• For tuples from L with no matching tuples in R
  second tuple is filled with null values.

28
Employee Left Outer Join Car
29
Right Outer join

• L Right Outer join R
• All tuples from right relation R joined with
  matching tuples from the left relation L.
• For tuples from Rwith no matching tuples in L
  tuple is filled with null values

30

Employee Right Outer Join Car
31
Full Outer Join

• Keeps all tuples in both left and right relation.
  When no matching tuples are found padding tuples
  with null values as needed.

32
Employee Full Outer Join Car
33
Outer UNION

• The outer UNION is designed for two relations
  that are not union compatible, but are partially
  compatible (only some of their attributes are
  union compatible)
• The list of union compatible attributes includes
  key(s) of both relations.

34
Example

• STUDENT(Nmae, SSN, Dept, Advisor)
• FACULTY(Name, SSN, Dept, Rank)
• STUDENT OuterUnion FACULTY ? RESULT(Name, SSN,
  DEPT, Advisor, Rank)

35
Example

• Each student row will have value for Advisor and
  null for Rank.
• Each faculty row will have value for Rank and
  Null for Advisor.
• All tuples from both relations are included in
  result.
• A tuple that exists in both relations will have
  values for both Advisor and Rank.