Relational Algebra Operations from Set Theory

1. UNION Operation

• Notation: r1∪r2r1∪r2 size 12{r1` union `r2`} {}

Figure 2: Union Operation Notation

• r1∪r2={t∣t∈r1∨t∈r2}r1∪r2={t∣t∈r1∨t∈r2} size 12{r1` union `r2= lbrace t \lline t in r1 or t in r2 rbrace } {} where r1(R) and r2(R)
• Result size: {}∣r1∪r2∣≤∣r1∣+∣r2∣∣r1∪r2∣≤∣r1∣+∣r2∣ size 12{ \lline r1` union `r2 \lline ` <= ` \lline r1 \lline + \lline r2 \lline } {}
• Result schema: R
• Producing the result of UNION
  • Make a copy of relation r1
  • For each tuple t in relation r2, check whether it is in the result or not. If it is not already in the result then place it there.
• Example:

Figure 3: Union Operation Example

1. INTERSECTION Operation

• Notation: r1∩r2r1∩r2 size 12{r1` intersection `r2} {}

Figure 4: Intersection Operation Notation

• r1∩r2={t∣t∈r1∧t∈r2}r1∩r2={t∣t∈r1∧t∈r2} size 12{r1` intersection `r2= lbrace t \lline t in r1 and t in r2 rbrace } {} where r1(R) and r2(R)
• Result size: ∣r1∩r2∣≤min(∣r1∣,∣r2∣)∣r1∩r2∣≤min(∣r1∣,∣r2∣) size 12{ \lline r1` intersection `r2 \lline ` <= `"min" \( \lline r1 \lline , \lline r2 \lline \) rbrace } {}
• Result schema: R
• Producing the result of INTERSECTION
  • Initially, result set is empty
  • For each tuple t in relation r1, if t is in the relation r2 then place t in the result set.
• Example

Figure 5: Intersection Operation Example

1. SET DIFFERENCE Operation

• Notation: r1 \ r2

Figure 6: Difference Operation Notation

• r1={t∣t∈r1∧t∉r2}r1={t∣t∈r1∧t∉r2} size 12{r1`\`r2= lbrace t \lline t in r1` and `t notin r2 rbrace } {} where r1(R) and r2(R)
• Result schema: R
• Producing the result of the DIFFERENCE operation
  • Initially, result set is empty
  • For each tuple in r1, check whether it appear in r2 or not. If it does not then place it in the result set. Otherwise, ignores it
• Example

Figure 7: Difference Operation Example

1. CARTESIAN PRODUCT Operation

• Notation: r x s
• r×s={(t1,t2)∣t1∈r∧t2∈s}r×s={(t1,t2)∣t1∈r∧t2∈s} size 12{r` times `s`=` lbrace \( t1`,`t2 \) ` \lline `t1 in r and `t2 in `s rbrace } {} where r(R) and s(S)
• Each tuple in the result contains all attributes in r and s, possibly with some fields renamed to avoid ambiguity. The result set contains all possible tuple that can be construct from one tuple in r and one tuple in s.
• Result schema: If we have R(A1, A2, …, An) and S(B1, B2, …, Bm) then the list of attributes in Result is (A1, A2, …, An, B1, B2, …, Bm)
• Result size: ∣r×s∣=∣r∣∗∣s∣∣r×s∣=∣r∣∗∣s∣ size 12{ \lline r` times s \lline `=` \lline r \lline `*` \lline s \lline } {}
• Producing the result of PRODUCT operation:
  • For each tuple in r, form new tuples by pair it with each tuple in s
  • Place all of these new tuples in the result set
• Example

Figure 8: Catesian Product Operation Notation

Figure 9: Catesian Product Operation Example

• Note: As we can notice, the CARTESIAN PRODUCT operation by itself is not a useful querying mechanism since the result size is large. However, it is an extremely important operation of relational algebra since it is the basic mechanism for combining information across relations. We will discuss about this topic in more detail in Query Processing lecture.

Other Relational Algebra Operations

1. SELECT Operation

• Notation: σselection−condition>(r)σselection−condition>(r) size 12{σ rSub { size 8{ ital "selection"` - ` ital "condition"}} \( r \) } {}

Figure 10: Select Operation Notation

• σ F ( r ) = { t ∣ t ∈ r ∧ F ( t ) } σ F ( r ) = { t ∣ t ∈ r ∧ F ( t ) } size 12{σ rSub { size 8{F} } \( r \) `=` lbrace t \lline `t in `r and `F \( t \) rbrace `} {} where r(R) and F is a boolean expression on attributes in R

• <attribute name> <comparison op> <constant value> OR
• <attribute name 1> <comparison op> <attribute name 2>

• Result size ∣σF(r)∣≤∣r∣∣σF(r)∣≤∣r∣ size 12{ \lline σ rSub { size 8{F} } \( r \) \lline <= ` \lline r \lline `} {}
• Result schema: R
• Producing the result of the SELECT operation
  • Selection condition F is evaluate for each tuple in r, with the attribute variables in F set to their values in the tuples
  • Any tuple t that F(t) = true is placed in the result set
  • Other tuples are not include in the result.
• Example: Retrieve the Id, Name, Suburb of students who live in Bundoora

Figure 11: Select Operation Example - 1

• Example: Retrieve the Id, Name, Suburd of student who’s name is Mary or students who live in Bundoora

Figure 12: Select Operation Example - 2

1. PROJECT Operation

• Notation: πattribute−list(r)πattribute−list(r) size 12{π rSub { size 8{ ital "attribute"` - ital "list"} } \( r \) } {}

Figure 13: Project Operation Notation

• π X ( r ) = { t [ X ] ∣ t ∈ r } π X ( r ) = { t [ X ] ∣ t ∈ r } size 12{π rSub { size 8{X} } \( r \) `=` lbrace t` \[ X \] ` \lline t in `r rbrace ` } {} where r(R)
• Result size: ∣πX(r)∣≤∣r∣∣πX(r)∣≤∣r∣ size 12{ \lline π rSub { size 8{X} } \( r \) \lline <= ` \lline r \lline `} {}
• Result schema: R’(X)
• Producing the result of PROJECT operation
  • Take each tuple in the original relation, extract the values of the specified attributes
  • Form new tuple from these values and place the new tuple in the result if it is not already there. This steps includes the duplicate removal phase, this makes the result of PROJECT operation a relation

• Example: Retrieve the suburbs that are stored in database

Figure 14: Project Operation Example - 1

• Retrieve the name of the subjects and department which is responsible for the subject

Figure 15: Project Operation Example - 2

1. JOIN Operation

• Notation: r⊲⊳join−conditionsr⊲⊳join−conditions size 12{r` ⊳ ⊲ rSub { size 8{ ital "join"` - ` ital "condition"} } s} {}

• r⊲⊳Cs={(t1,t2)∣t1∈r∧t2∈s∧C(t1,t2)}r⊲⊳Cs={(t1,t2)∣t1∈r∧t2∈s∧C(t1,t2)} size 12{r` ⊳ ⊲ rSub { size 8{C} } s`=` lbrace \( t1,t2 \) ` \lline t1 in r and t2 in s and C \( t1,t2 \) rbrace `} {}where r(R) , s(S)
• Similar to PRODUCT, each tuple in the result of JOIN operation contains all attributes from two original relations. However, in this operation one tuple in R and one tuple in S can be combined together to form a tuple in the result if the combination satisfies the join condition.
• Join condition is of the form:

• Result size: ∣r⊲⊳Cs∣≤∣r∣∗∣s∣∣r⊲⊳Cs∣≤∣r∣∗∣s∣ size 12{ \lline r` ⊳ ⊲ rSub { size 8{C} } `s \lline ` <= ` \lline r \lline `*` \lline s \lline } {}
• Result schema: If we have R(A1, A2, …, An) and S(B1, B2, …, Bm) then the list of attributes in Result is (A1, A2, …, An, B1, B2, …, Bm)
• Producing the result of JOIN operation:
  • For each tuple in r, form new tuples by pair it with each tuple in s
  • If the new tuple satisfies the specified condition, then place it in the result set.
• Example:

Figure 16: Join Operation Example - 1

Figure 17: Join Operation Example - 2

Figure 18: Join Operation Example - 3

Figure 19: Natural Join Operation Example - 1

• Notation: r * s
• Natural Join can be defined using other operation r∗s=πR∪S(σcondition(r×s))r∗s=πR∪S(σcondition(r×s)) size 12{r*s=π rSub { size 8{r union s} } \( σ rSub { size 8{ ital "condition"} } \( r times s \) \) } {}

• For each tuple in relation r, compare common attributes with those in each tuple of s
• If two tuples match in their common attributes then combine tuples, remove duplicate attributes and add to the result.

• Example: From the example of Equi Join, assume that the attribute list in s now is ( E, F,B) instead of (E, F, G) then we can have the expression r * s .

Figure 20

• Retrieve the information of student who enrols in at least one course.

Figure 21: Natural Join Operation Example - 2

1. DIVISION Operation

• Producing the result of the Division operation
  • Consider each subset of tuple in r that match on t[U1 – U2]
  • For this subset of tuples, take the values t[U2] from each. If this covers all tuples in s then add t[U1 – U2] in the result.
• Example: Retrieve the name of subject that is taught in all courses

Figure 22: Division Operation Example

Data Manipulation with Relational Algebra Expression