IM Relational Algebra

• Relational Algebra - Formal language for the relational model. And defines the basic set of operations for the relational model for basic retrieval requests
• Operations specifically developed for relational databases

  SELECT , PROJECT – unary

  JOIN - binary
• I. Unary Relational Operations

  (applied to a single relation)

  SELECT – used to select a subset of the tuples from a relation that satisfies a selection condition

  PROJECT – selects certain columns from the table and discards the other columns; used if we are interested only in certain attributes of a relation
• select - selects rows/tuples from the table

  project - selects the columns from the table
• SELECT format :

  σ<selection condition> (R)

  Selection condition is a boolean expression specified on the

  attributes of relation R
• SELECT operation is commutative

  σcond1 (σcond2 (R))= σcond2 (σcond1> (R))

  same as

  σ(cond1 AND cond2)(R)
• sigma for select SS
• PROJECT – selects certain columns and discards other

  columns

  PROJECT format :

  π<attribute list> (R)
• Pi sign for project PP
• List each employee’s last name,

  first name, and salary

  π<Lname, Fname, Salary> (EMPLOYEE)
• Project removes duplicate tuples
• Write the operations as a single relational algebra expression by nesting the operation

  Ex. Retrieve the first name, last name, and salary of all employees who work in department number 5.

  π Lname, Fname, Salary (σDno=5 (EMPLOYEE))
• Ex. Retrieve the first name, last name, and salary of all employees who work in department number

  We can apply one operation at a time and create intermediate result relations in which case we need to give names to the relations that hold the intermediate results

  Ex. Dep5Emp ← σDno=5 (EMPLOYEE)

  FinalList ← π Lname, Fname, Salary(Dep5Emp)
• Display the Certification records for employees who passed

  σremarks = ‘P’ (Certification)
• Display the empid, exam, datetaken of employees who passed

  Passed <- σremarks = ‘P’ (Certification)

  πempid,exam,datetaken (Passed)
• Display the empid, exam, datetaken of employees who passed

  πempid,exam,datetaken ( σremarks = ‘P’ (Certification))
• List the empname, salary of employees earning more than 100 who are from FIN

  πempname,salary( σsalary>100 AND dept=‘FIN’ (Employee))
• Relational Algebra Operations from Mathematical Set

  Theory – standard mathematical operations on sets and

  applied to 2 sets of tuples

  We can define 3 operations

  ❑ UNION

  ❑ INTERSECTION

  ❑ MINUS (SET DIFFERENCE)
• UNION – the result of this operation is a relation that includes

  all tuples that are either in R or in S or in both R and S.

  Duplicate tuples are eliminated

  R U S

  lahat ng nasa relation R and relation S
• INTERSECTION – the result of this operation is a relation that

  includes all tuples that are in both R and S

  R ∩ S

  yung magkamukha lang sa relation R and S
• MINUS (SET DIFFERENCE) – the result of this operation is a

  relation that includes all tuples that are in R but not in S

  R - S

  lahat ng nasa R tas ibawas mo lahat ng nasa S
• Union compatibility - Relations should have same degree (same number of attributes) and each corresponding pair of attributes have same domain (data type, structure)

  The 2 relations, on which UNION, INTERSECTION, MINUS are applied must be union compatible.
• CARTESIAN PRODUCT

  (Cross Product or Cross Join)–

  R X S

  Combines every tuple from one relation with every tuple from another relation; not union compatible
• CARTESIAN PRODUCT

  (Cross Product or Cross Join)

  R(A1, A2, ...An) X S(B1, B2,...Bm)

  The resulting relation will have a degree (column) of n+m attributes (yung n is column nung isa, yung m is yung column nung isa)

  
  

  The resulting relation will have one tuple for each combination of tuples, one from R and one from S.

  Hence number of tuples from R (nR) and number of tuples from S (nS) will have nR * nS

  tuples
• Binary Relational Operations is applied to two sets of tuples

  JOIN– used to combine related tuples from 2 relations into a single tuple

  R ⋈<join condition> S
• Binary Relational Operations is applied to two sets of tuples

  The result of JOIN is a relation with n + m attributes

  The result of JOIN has one tuple for each combination of tuples --- one from R and one from S --- whenever the combination satisfies the join condition.

  This is the main difference between CARTESIAN PRODUCT and JOIN.
• How many tuples will be output by a JOIN?

  depends on how many are matched from the condition
• How many tuples will be output by a CROSS JOIN?

  rows of the other table * rows of the other table
• sa join inaaad lahat ng attributes, walang automatic removal of duplicate kyaa pag tinanong ilang attributes, add mo lang ilang columsn sa isa tas columns din nung isa