In terms of 8 original relational operations such as SELECT (), PROJECT(), UNION

ID: 3667431 • Letter: I

Question

In terms of 8 original relational operations such as SELECT (), PROJECT(), UNION (), INTERSECT(), DIFFERENCE (-), CARTESIAN PRODUCT (), JOIN (), and DIVISION (),

a. What is the minimal set of relational operations to support the set of 8 original relational operations? (5 points)

b. Which of the 8 original relational operations is (are) commutative? (5 points)

c. Which of the 8 original relational operations is (are) associative? (5 points)

d. Please use the set of 8 original relational operations to express the right outer join operation. (i.e., use any of 8 original relational operators to construct right outer join expression in terms of relational algebra) (15 points)

Explanation / Answer

{{Main|Outer join}}
Whereas the result of a join (or inner join) consists of tuples formed by combining matching tuples in the two operands, an outer join contains those tuples and additionally some tuples formed by extending an unmatched tuple in one of the operands by "fill" values for each of the attributes of the other operand. Note that outer joins are not considered part of the classical relational algebra discussed so far.<ref name="O'NeilO'Neil2001">{{cite book|author1=Patrick O'Neil|author2=Elizabeth O'Neil|title=Database: Principles, Programming, and Performance, Second Edition|url=http://books.google.com/books?id=UXh4qTpmO8QC&pg=PA120|year=2001|publisher=Morgan Kaufmann|isbn=978-1-55860-438-4|page=120}}</ref>

The operators defined in this section assume the existence of a ''null'' value, '''', which we do not define, to be used for the fill values; in practice this corresponds to the [[Null (SQL)|NULL]] in SQL. In order to make subsequent selection operations on the resulting table meaningful, a semantic meaning needs to be assigned to nulls; in Codd's approach the propositional logic used by the selection is [[Null (SQL)#Comparisons with NULL and the three-valued logic .283VL.29|extended to a three-valued logic]], although we elide those details in this article.

Three outer join operators are defined: left outer join, right outer join, and full outer join. (The word "outer" is sometimes omitted.)

==== {{visible anchor|Left outer join}} () ====

The left outer join is written as ''R'' ''S'' where ''R'' and ''S'' are [[relation (database)|relation]]s.<ref>In [[Unicode]], the Left outer join symbol is {{unicode|}} (U+27D5).</ref> The result of the left outer join is the set of all combinations of tuples in ''R'' and ''S'' that are equal on their common attribute names, in addition (loosely speaking) to tuples in ''R'' that have no matching tuples in ''S''.

For an example consider the tables ''Employee'' and ''Dept'' and their left outer join:

{{col-begin|width=auto; margin:0.5em auto}}
{{col-break}}
{| class="wikitable"
|+ ''Employee''
|-
! Name !! EmpId !! DeptName
|-
| Harry || 3415 || Finance
|-
| Sally || 2241 || Sales
|-
| George || 3401 || Finance
|-
| Harriet || 2202 || Sales
|-
| Tim || 1123 || Executive
|}
{{col-break|gap=2em}}
{| class="wikitable"
|+ ''Dept''
|-
! DeptName !! Manager
|-
| Sales || Harriet
|-
| Production || Charles
|}
{{col-break|gap=2em}}
{| class="wikitable"
|+ ''Employee'' ''Dept''
|-
! Name !! EmpId !! DeptName !! Manager
|-
| Harry || 3415 || Finance ||
|-
| Sally || 2241 || Sales || Harriet
|-
| George || 3401 || Finance ||
|-
| Harriet || 2202 || Sales || Harriet
|-
| Tim || 1123 || Executive ||
|}
{{col-end}}

In the resulting relation, tuples in ''S'' which have no common values in common attribute names with tuples in ''R'' take a ''null'' value, ''''.

Since there are no tuples in ''Dept'' with a ''DeptName'' of ''Finance'' or ''Executive'', ''''s occur in the resulting relation where tuples in ''Employee'' have a ''DeptName'' of ''Finance'' or ''Executive''.

Let ''r''1, ''r''2, ..., ''r''''n'' be the attributes of the relation ''R'' and let {('''', ..., '''')} be the singleton
relation on the attributes that are ''unique'' to the relation ''S'' (those that are not attributes of ''R''). Then the left outer join can be described in terms of the natural join (and hence using basic operators) as follows:

:<math>(R owtie S) cup ((R - pi_{r_1, r_2, dots, r_n}(R owtie S)) imes {(omega, dots omega)})</math>

==== {{visible anchor|Right outer join}} () ====
The right outer join behaves almost identically to the left outer join, but the roles of the tables are switched.

The right outer join of [[relation (database)|relation]]s ''R'' and ''S'' is written as ''R'' ''S''.<ref>In [[Unicode]], the Right outer join symbol is {{unicode|}} (U+27D6).</ref> The result of the right outer join is the set of all combinations of tuples in ''R'' and ''S'' that are equal on their common attribute names, in addition to tuples in ''S'' that have no matching tuples in ''R''.

For example consider the tables ''Employee'' and ''Dept'' and their
right outer join:

{{col-begin|width=auto; margin:0.5em auto}}
{{col-break}}
{| class="wikitable"
|+ ''Employee''
|-
! Name !! EmpId !! DeptName
|-
| Harry || 3415 || Finance
|-
| Sally || 2241 || Sales
|-
| George || 3401 || Finance
|-
| Harriet || 2202 || Sales
|-
| Tim || 1123 || Executive
|}
{{col-break|gap=2em}}
{| class="wikitable"
|+ ''Dept''
|-
! DeptName !! Manager
|-
| Sales || Harriet
|-
| Production || Charles
|}
{{col-break|gap=2em}}
{| class="wikitable"
|+ ''Employee'' ''Dept''
|-
! Name !! EmpId !! DeptName !! Manager
|-
| Sally || 2241 || Sales || Harriet
|-
| Harriet || 2202 || Sales || Harriet
|-
| || || Production || Charles
|}
{{col-end}}

In the resulting relation, tuples in ''R'' which have no common values in common attribute names with tuples in ''S'' take a ''null'' value, ''''.

Since there are no tuples in ''Employee'' with a ''DeptName'' of ''Production'', ''''s occur in the Name attribute of the resulting relation where tuples in ''DeptName'' had tuples of ''Production''.

Let ''s''1, ''s''2, ..., ''s''''n'' be the attributes of the relation ''S'' and let {('''', ..., '''')} be the singleton
relation on the attributes that are ''unique'' to the relation ''R'' (those that are not attributes of ''S''). Then, as with the left outer join, the right outer join can be simulated using the natural join as follows:

:<math>(R owtie S) cup ({(omega, dots, omega)} imes (S - pi_{s_1, s_2, dots, s_n}(R owtie S)))</math>

==== {{visible anchor|Full outer join}} () ====

The '''outer join''' or '''full outer join''' in effect combines the results of the left and right outer joins.

The full outer join is written as ''R'' ''S'' where ''R'' and ''S'' are [[relation (database)|relation]]s.<ref>In [[Unicode]], the Full Outer join symbol is {{unicode|}} (U+27D7).</ref> The result of the full outer join is the set of all combinations of tuples in ''R'' and ''S'' that are equal on their common attribute names, in addition to tuples in ''S'' that have no matching tuples in ''R'' and tuples in ''R'' that have no matching tuples in ''S'' in their common attribute names.

For an example consider the tables ''Employee'' and ''Dept'' and their
full outer join:

In the resulting relation, tuples in ''R'' which have no common values in common attribute names with tuples in ''S'' take a ''null'' value, ''''. Tuples in ''S'' which have no common values in common attribute names with tuples in ''R'' also take a ''null'' value, ''''.

The full outer join can be simulated using the left and right outer joins (and hence the natural join and set union) as follows:

:''R'' ''S'' = (''R'' ''S'') <math>cup</math> (''R'' ''S'') hence this are the relations carried out by the 8 operations

Navigate

In tennis, when you serve, if the “first serve”lands in, the point is played unt

In terms of Lewis Acid-Base reaction between Fe^+3 and OH^-: FeCl_3 + 6H_2O doub

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.

In terms of 8 original relational operations such as SELECT (), PROJECT(), UNION

Question

Explanation / Answer

Related Questions

Navigate