Relational Algebra and Relational Calculus - PowerPoint PPT Presentation

About This Presentation
Title:

Relational Algebra and Relational Calculus

Description:

Example: List each employee's first and last name and salary: ... partially compatible, meaning that only some of their attributes, say X, are type compatible. ... – PowerPoint PPT presentation

Number of Views:131
Avg rating:3.0/5.0
Slides: 57
Provided by: hpc8
Category:

less

Transcript and Presenter's Notes

Title: Relational Algebra and Relational Calculus


1
Chapter 6
  • Relational Algebra and Relational
    Calculus

2
Introduction
  • Relational algebra and relational calculus are
    formal query languages of the relational model.
  • Relational algebra is a procedural language.
  • Relational calculus is a non-procedural language.
  • Both are equivalent to one another.
  • Both have formal strong foundation on logic.
  • Query languages ! programming languages
  • The result of an operation is a new relation.
  • Using one or more input relations.
  • Operation does not change original relations.

3
Relational Algebra
  • Other characteristics of Relational algebra
  • Set language All tuples are manipulated without
    looping.
  • Output from one operation can become input to
    another operation.
  • It allows operations to be nested, just as in
    arithmetic. This property is called closure.
  • The sequence of relational algebra operations
    forms a relational algebra expression
  • The result is also a relation.

4
Relational Algebra Operations
  • Unary relational operations Selection,
    Projection, and Rename.
  • Set theory operations Union, Intersection,
    Difference, and Cartesian Product.
  • Binary relational operations Join, and Division.
  • Additional relational operations Outer Joins,
    Outer Union, and Semijoin.

5
Relational Algebra Operations
6
Relational Algebra Operations
7
COMPANY database schema
8
COMPANY database state
9
Selection (or Restriction)
  • ?predicate (R)
  • It selects a subset of the tuples (rows) of R
    that satisfy the specified condition (predicate).
  • No duplicates in the result (Why?)
  • The selection condition acts as a filter.
  • Tuples that make the condition false are filtered
    out.
  • Schema of result is identical to the schema of
    the input relation.
  • Degree(Result) Degree(R)
  • Cardinality(Result) Cardinality(R)

10
Example - Selection (or Restriction)
  • List the employees whose department is 4
  • ?dno 4 (Employee)
  • List all employees whose salary is greater than
    30,000
  • ?salary gt 10000 (Employee)

11
Selection (contd.)
  • SELECTION Operation Properties
  • The Selection operation ? ltselection
    conditiongt(R) produces a relation S that has the
    same schema (same attributes) as R
  • Selection ? is commutative
  • ? ltcondition1gt(? lt condition2gt (R)) ?
    ltcondition2gt (? lt condition1gt (R))
  • Because of commutativity property, a cascade
    (sequence) of Selection operations may be applied
    in any order
  • ?ltcond1gt(?ltcond2gt (?ltcond3gt (R)) ?ltcond2gt
    (?ltcond3gt (?ltcond1gt ( R)))
  • A cascade of Selection operations may be replaced
    by a single selection with a conjunction of all
    the conditions
  • ?ltcond1gt(?lt cond2gt (?ltcond3gt(R)) ? ltcond1gt AND
    lt cond2gt AND lt cond3gt(R)))
  • The number of tuples in the result of a Selection
    is less than (or equal to) the number of tuples
    in the input relation R.

12
Projection
  • ?col1, . . . , coln(R)
  • It defines a relation that contains a vertical
    subset of R, extracting the values of specified
    attributes.
  • It creates a vertical partitioning of R.
  • It eliminates duplicates. Why?
  • Some DBMSs do not eliminate duplicates, unless
    the user asks for it.
  • Example List each employees first and last name
    and salary
  • ?lname, fname, salary(Employee)

13
Projection (contd.)
  • Projection Operation Properties
  • The number of tuples in the result of projection
    ?ltlistgt(R) is always less or equal to the number
    of tuples in R.
  • If the list of attributes includes a key of R,
    then the number of tuples in the result of
    Projection is equal to the number of tuples in R.
  • Projection is not commutative.
  • ? ltlist1gt (? ltlist2gt (R) ) ? ltlist1gt (R) as
    long as ltlist2gt contains the attributes in
    ltlist1gt.
  • Degree(Result) Degree(R)
  • Cardinality(Result) Cardinality(R)

14
Applying Selection and Projection
15
Single relational expression
  • To retrieve the first name, last name, and salary
    of all employees who work in department number 5
  • We can write a single relational expression
  • ?lname, fname, salary(?dno 5(Employee))
  • Or a sequence of relational operations, giving a
    name to each intermediate relations
  • Dept5Emps ?dno 5(Employee)
  • Result ?lname, fname, salary(Dept5Emps)

16
The Rename operation
  • Denoted by ? (rho), it is used to
  • assign names to intermediate relations, or
  • rename a relation and its attributes.
  • General form
  • ?S (B1, B2, , Bn )(R) changes both
  • the relation name to S, and
  • the column (attribute) names to B1, B1, ..Bn
  • ?S(R) changes
  • the relation name only to S
  • ?(B1, B2, , Bn )(R) changes
  • the column (attribute) names only to B1, B1, ..Bn

17
The Rename operation (contd.)
  • For convenience, we also use a shorthand for
    renaming attributes in an intermediate relation
  • If we write
  • RESULT ? ? FNAME, LNAME, SALARY (DEP5_EMPS)
  • RESULT will have the same attribute names as
    DEP5_EMPS (same attributes as EMPLOYEE)
  • If we write
  • RESULT (F, M, L, S, B, A, SX, SAL, SU, DNO) ?
  • ? RESULT (F,M,L.S.B,A,SX,SAL,SU, DNO)(DEP5_EMPS)
  • The 10 attributes of DEP5_EMPS are renamed to F,
    M, L, S, B, A, SX, SAL, SU, DNO, respectively.

18
Binary Union operation
  • R ? S
  • Union of two relations R and S defines a relation
    that contains all the tuples of R, or S, or both
    R and S, duplicate tuples being eliminated.
  • R and S must be union compatible.
  • Same number of attributes
  • Corresponding attributes have the same or
    compatible domain (type compatible).
  • Schema of the result is 1st relation schema.

19
Example - Union
  • To retrieve the social security numbers of all
    employees who either work in department 5
    (RESULT1 below) or directly supervise an employee
    who works in department 5 (RESULT2 below)
  • We can use the UNION operation as follows
  • DEP5_EMPS ? ?dno5 (EMPLOYEE)
  • RESULT1 ? ? SSN(DEP5_EMPS)
  • RESULT2(SSN) ? ?SUPERSSN(DEP5_EMPS)
  • RESULT ? RESULT1 ? RESULT2
  • The union operation produces the tuples that are
    in either RESULT1 or RESULT2 or both

20
Example of Union (contd.)
21
Set Difference (Minus or Except)
  • R S
  • Defines a relation consisting of the tuples that
    are in relation R, but not in S.
  • R and S must be union compatible.
  • Schema of the result is 1st relation schema.
  • Same definition applies to all the set operations.

22
Intersection
  • R ? S
  • Defines a relation consisting of the set of all
    tuples that are in both R and S.
  • R and S must be union compatible.
  • Expressed using basic operations
  • R ? S R (R S)

23
Examples Set Theory operations
24
Properties of Union, intersect, and difference
  • Notice that both union and intersection are
    commutative operations that is
  • R ? S S ? R, and R ? S S ? R
  • Both union and intersection can be treated as
    n-ary operations applicable to any number of
    relations as both are associative operations
  • R ? (S ? T) (R ? S) ? T
  • (R ? S) ? T R ? (S ? T)
  • The minus operation is not commutative that is
    in general
  • R S ? S R

25
Cartesian (or cross) product
  • R X S
  • Defines a relation that is the concatenation of
    every tuple of relation R with every tuple of
    relation S.
  • Each row of R is paired with each row of S.
  • Relations do not have to be type compatible.
  • Result schema has one field for each field of R
    and S.

26
Cartesian Product (contd.)
  • Generally, CROSS PRODUCT is not a meaningful
    operation
  • Can become meaningful when followed by other
    operations
  • Example (not meaningful)
  • FEMALE_EMPS ? ? sexF(EMPLOYEE)
  • EMPNAMES ? ? fname, lname, SSN (FEMALE_EMPS)
  • EMP_DEPENDENTS ? EMPNAMES x DEPENDENT
  • EMP_DEPENDENTS will contain every combination of
    EMPNAMES and DEPENDENT
  • whether or not they are actually related

27
Example Cartesian Product
28
Join Operation
  • Join is a derivative of Cartesian product.
  • Equivalent to performing a Selection, using join
    predicate as selection formula, over Cartesian
    product of the two operand relations.
  • Very important and of the most difficult
    operation
  • Hard to implement efficiently in an RDBMS.
  • It allows us combine related tuples from various
    relations.
  • General form (called theta join) R ltjoin
    conditiongt S
  • Where R and S can be any relations.
  • Join condition can be any general Boolean
    expression.

29
Join Operation (contd.)
  • Example Suppose that we want to retrieve the
    name of the manager of each department.
  • To get the managers name, we need to combine
    each DEPARTMENT tuple with the EMPLOYEE tuple
    whose SSN value matches the MGRSSN value in the
    department tuple.
  • We do this by using the join operation.
  • DEPT_MGR ? DEPARTMENT MGRSSNSSN EMPLOYEE
  • MGRSSNSSN is the join condition.
  • Combines each department record with the employee
    who manages the department
  • The join condition can also be specified as
    DEPARTMENT.MGRSSN EMPLOYEE.SSN

30
Example of the Join Operation
DEPT_MGR ? DEPARTMENT MGRSSNSSN
EMPLOYEE
31
Some properties of Join
  • Consider the following JOIN operation
  • R(A1, A2, . . ., An) S(B1, B2,
    . . ., Bm)
  • R.AiS.Bj
  • Result is a relation Q with degree n m
    attributes
  • Q(A1, A2, . . ., An, B1, B2, . . ., Bm), in that
    order.
  • The resulting relation state has one tuple for
    each combination of tuplesr from R and s from S,
    but only if they satisfy the join condition
    rAisBj
  • Hence, if R has nR tuples, and S has nS tuples,
    then the join result will generally have less
    than nR nS tuples.
  • Only related tuples (based on the join condition)
    will appear in the result

32
Other Join Operations
  • In addition to the Theta Join
  • Equijoin (a particular type of Theta Join)
  • Natural join
  • Outer join
  • Semijoin

33
Equijoin
  • A theta join where join condition contains only
    equality ().
  • The result of an equijoin always have one or more
    pair of attributes (with identical names or not)
    that have identical values in every tuple.
  • The join for the creation of DEPT_MGR was an
    equijoin.
  • Same degree and cardinality of a Theta join
  • Degree(Result) Degree(R) Degree(S)
  • Card(Result) Card(R) x Card(S)

34
Natural Join
  • R S
  • An Equijoin of the two relations R and S over all
    common attributes x. One occurrence of each
    common attribute is eliminated from the result.
  • If attributes have different names, a renaming
    operations is applied first.

35
Example - Natural Join
  • To apply a natural join on the DNUMBER attributes
    of DEPARTMENT and DEPT_LOCATIONS, it is
    sufficient to write
  • DEPT_LOCS ? DEPARTMENT DEPT_LOCATIONS
  • Only attribute with the same name is DNUMBER
  • An implicit join condition is created based on
    this attribute
  • DEPARTMENT.DNUMBERDEPT_LOCATIONS.DNUMBER
  • Another example Q ? R(A,B,C,D) S(C,D,E)
  • The implicit join condition includes each pair of
    attributes with the same name, ANDed together
  • R.CS.C AND R.D.S.D
  • Result keeps only one attribute of each such
    pair
  • Q(A,B,C,D,E)

36
Another example of Natural Join
37
Complete set of Relational Operations
  • The set of operations including Selection ?,
    Projection ? , Union ?, Difference -, and
    Cartesian Product X is called a complete set
    because any other relational algebra expression
    can be expressed by a combination of these five
    basic operations.
  • For example
  • R ? S (R ? S ) ((R - S) ? (S - R))
  • R ltjoin conditiongtS ? ltjoin conditiongt (R
    X S)

38
Division
  • R(x,y) ? S(y)
  • Not supported as a primitive operator
  • Let R have 2 fields, x and y S has only field y
  • R(x,y) ? S(y) is the set of all x values in R
    such that the y
  • values associated with an x value in R
    contains all y
  • values in S.
  • In general, x and y can be any lists of fields.
  • Expressed using basic operations
  • T1 ? ?x(R)
  • T2 ? ?x((S X T1) R)
  • T ? T1 T2

39
Example - Division
40
Query Tree Notation
  • Query Tree
  • An internal data structure to represent a query.
  • Standard technique for estimating the work
    involved in executing the query, the generation
    of intermediate results, and the optimization of
    execution.
  • Nodes stand for operations like selection,
    projection, join, renaming, division, .
  • Leaf nodes represent base relations.
  • A tree gives a good visual feel of the complexity
    of the query and the operations involved.
  • Algebraic Query Optimization consists of
    rewriting the query or modifying the query tree
    into an equivalent tree.
  • (see Chapter 15)

41
Example of Query Tree
42
Outer joins
  • Use Outer joins if you want to display rows in
    the result that do not have matching values in
    the join column.
  • Outer joins Left ( ), Right ( ), and Full
    ( )
  • Left Outer Join R S
  • (Left) outer join is a join in which tuples from
    R that do not have matching values in common
    columns of S are also included in result
    relation.
  • Attribute of S in the unmatched tuples are filled
    with null values.

43
Outer joins (contd.)
  • Right outer join is similar to left outer join.
  • It keeps unmatching tuples of the right relation
    in the result of R S.
  • R attributes in the unmatched tuples are filled
    with null values.
  • The full outer joins keeps all unmatching tuples
    of the left and right relations of the operation.
  • It is a combination of a left- and a right outer
    join.

44
Example - Left Outer join
  • Temp ?(Employee) SSNMGR_SSN ( Department)
    Result ? ?fname,minit,lname,dname(Department)

45
Outer Union Operations
  • OUTER UNION Operations
  • It was developed to take the union of tuples from
    two relations if the relations are not type
    compatible.
  • This operation will take the union of tuples in
    two relations R(X, Y) and S(X, Z) that are
    partially compatible, meaning that only some of
    their attributes, say X, are type compatible.
  • The attributes that are type compatible are
    represented only once in the result, and those
    attributes that are not type compatible from
    either relation are also kept in the result
    relation T(X, Y, Z).

46
Outer Union (contd.)
  • Example Outer union of relations Student(name,
    SSN, department, advisor) Instructor(name, SSN,
    department, rank)
  • Tuples are matched by the common attributes
    name, SSN, and department.
  • If a student is also an instructor, both advisor
    and rank will have values otherwise, one of
    these attributes will be null.
  • Resulting schema
  • Stu_Or_Inst(name, SSN, department, advisor, rank)

47
Semijoin
  • R FS
  • Defines a relation that contains the tuples of R
    that participate in the join of R with S.
  • Result schema is the schema of the first
    relation.
  • Can rewrite Semijoin using Projection and Join
  • R F S ?R.A(R F S)
  • Example List employees data for employees who
    work at the Research department
  • Employee dnodnumber (sdnameResearch(
    Department))

48
Examples of queries in RA Procedural form
  • Q1 Retrieve the name and address of all
    employees who work for the Research department.
  • RESEARCH_DEPT ? ? DNAMEResearch (DEPARTMENT)
  • RESEARCH_EMPS ? (RESEARCH_DEPT DNUMBER
    DNOEMPLOYEEEMPLOYEE)
  • RESULT ? ? FNAME, LNAME, ADDRESS (RESEARCH_EMPS)
  • Q6 Retrieve the names of employees who have no
    dependents.
  • ALL_EMPS ? ? SSN(EMPLOYEE)
  • EMPS_WITH_DEPS(SSN) ? ? ESSN(DEPENDENT)
  • EMPS_WITHOUT_DEPS ? (ALL_EMPS - EMPS_WITH_DEPS)
  • RESULT ? ? LNAME, FNAME (EMPS_WITHOUT_DEPS
    EMPLOYEE)

49
Examples of queries in RA Single expression
  • As a single expression, these queries become
  • Q1 Retrieve the name and address of all
    employees who work for the Research department.
  • ? Fname, Lname, Address (s Dname Research
  • (DEPARTMENT DnumberDno(EMPLOYEE))
  • Q6 Retrieve the names of employees who have no
    dependents.
  • ? Lname, Fname((? Ssn (EMPLOYEE) - ? Ssn (?
    Essn (DEPENDENT))) EMPLOYEE)

50
Relational Calculus
  • Two versions tuple relational calculus (TRC) and
    domain relational calculus (DRC).
  • Calculus uses variables, constants, operators
    (comparison and logical), and quantifiers.
  • TRC variables range over tuples (rows).
  • DRC variables range over domain elements
    (columns).
  • Expressions in relational calculus are called
    formulas (or predicates).
  • Usually an answer to a formula is a set of tuples
    that make the formula evaluate to true.

51
Tuple Relational Calculus
  • Query has the form t p(t)
  • t is a tuple variable and p(t) is a formula.
  • It finds the set of all tuples t such that p(t)
    is true.
  • Tuple variable is a variable that ranges over a
    named relation ie., variable whose only
    permitted values are tuples of the relation.
  • Example Specify range of a tuple variable t of
    the Employee relation as Employee(t)
  • t Employee(t) ? Get all Employee tuples

52
Example - Tuple Relational Calculus
  • Example To find the first and last names of all
    employees whose salary is above 50,000, we can
    write the following tuple calculus expression
  • t.fname, t.lname Employee(t) AND
    t.salarygt50000
  • The first and last name (Projection ?fname,
    lname) of each EMPLOYEE tuple t that satisfies
    the condition t.salarygt50000 (Selection ? SALARY
    gt50000) will be retrieved.
  • Queries are evaluated on instances of Employee.

53
Quantifiers in Relational calculus
  • Can use two quantifiers to tell how many
    instances the formula applies to
  • Existential quantifier (there exists)
  • Universal quantifier " (for all)
  • Tuple variables qualified by " or are called
    bound variables, otherwise called free variables.
  • Only free variables should appear to the left of
    the bar .
  • Otherwise, the answer is either True or False.
  • If F(t) is a formula, then so are (t)(F(t)) and
    ("t)(F(t)), where t is a tuple variable.

54
Quantifiers (contd.)
  • The formula (? t)(F) is true if the formula F
    evaluates to true for some (at least one) tuple
    assigned to free occurrences of t in F otherwise
    (? t)(F) is false.
  • Example Retrieve the name and address of all
    employees who work for the Research department
  • t.fname,t.lname,t.address Employee(t) Ù
    (d) (Department(d) Ù (d.name Research) Ù
    (d.dnumber t.dno))

55
Quantifiers (contd.)
  • The formula (? t)(F) is true if the formula F
    evaluates to true for every tuple (in the
    universe) assigned to free occurrences of t in F
    otherwise (? t)(F) is false.
  • (? t) is used in in statements about every
    instance
  • Example For all Project tuples, the location is
    not Houston.
  • (? t)(p.plocation ? Houston)
  • Can also use (p) (p.plocation Houston)
    which means There are no locations in Houston.
  • ("t) (P(t)) ? (t) (P(t))

56
Quantifiers (contd.)
  • Find the names of employees who work on all the
    projects controlled by department number 5. The
    query can be
  • e.lname, e.fname Employee(e) Ù ( (?
    x)((Project(x)) V (x.dnum5) V
  • ( (? w)(Works_On(w) Ù w.ESSNe.SSN Ù
    x.pnumberw.pno))))
  • Exclude from the universal quantification all
    tuples that we are not interested in by making
    the condition true for all such tuples.
  • The first tuples to exclude (by making them
    evaluate automatically to true) are those that
    are not in the relation R of interest.
  • In query above, using the expression
    (Project(x)) inside the universally quantified
    formula evaluates to true all tuples x that are
    not in the Project relation.
  • Then we exclude the tuples we are not interested
    in from R itself. The expression not(x.dnum5)
    evaluates to true all tuples x that are in the
    project relation but are not controlled by
    department 5.
  • Finally, we specify a condition that must hold on
    all the remaining tuples in R.
  • ( (? w)(Works_On(w) Ù w.ESSNe.SSN Ù
    x.Pnumberw.pno)
Write a Comment
User Comments (0)
About PowerShow.com