DBMS - (7) Relational Algebra - set operator, renaming

DBMS - (7) Relational Algebra - set operator, renaming

Relational algebra query(expression) on set of relations produces relation as a result.

Example
College(cName, state, enrollment)
Student(sID, sName, GPA, size of HS)
Apply(sID, cName, major, decision)

<그림 1: Relation Example>

Union operator
(1) List of college and student name
Example,
Stanford
Susan
Cornell
...
- π(sName)(Student) U π(cName)(College)
- but, All of Set operator(include union) requires that the schema of relation(operand) must be same.
- Rename operator로 향후 수정

Difference operator
(1) IDs of students who didn't apply anywhere
- π(sID)(Student) - π(sID)(Apply)
(2) IDs and Names of students who didn't apply anywhere
- π(sID, sName) ((π(sID)(Student) - π(sID)(Apply)) ⋈ Student)

Intersection operator
(1) Names that are both a college name and a student name(학교랑 학생이 이름이 똑같은 경우)
- π(sName)(Student) ⋂ π(cName)(College)
- 스키마가 달라서 Renaming operator가 필요

intersection doesn't add expressive power
- E1 ⋂ E2 == E1 - ( E1 - E2 )
- E1 ⋂ E2 == E1 ⋈ E2(natural join eliminates the same attribute and has the condition)


Rename operator
- Rename operator is necessary to express relational algebra.
- ρ[Relation Name](attributes)
(1) ρ[R](A1,A2,...An)(E) is general form(highly recommended)
(2) ρ[R](E) is abbreviation
(3) ρ(A1,A2,..., An) is abbreviation

Rename operator의 용도
- To unify schemas for set operator(union, difference, intersection)
(1) List of college and student names.
 - ρ[S](name)(π(sName)(Student)) U ρ[C](name)(π(cName)(College))

- For disambiguation in 'self-join'
(2) pairs of college in same state
Example.
Stanford, Berkeley
...
- σ(state=state)(College X College)
'state'가 어느 College의 state를 가르키는 것인지 모호하므로(ambiguous) 불가능하다.
따라서, Rename operator를 사용해 모호성을 없애는 것이 목표
- σ(s1 = s2) ρ[C1](c1, s1, e1)(College) X ρ[C2](c2, s2, e2)(College) ... ⓐ
- ρ[C1](c1, s, e1)(College) ⋈ ρ[C2](c2, s, e2)(College) ... ⓑ
- attribute의 이름을 일치시키고 natural join을 사용하면 ⓐ와 ⓑ가 동일해진다.
그러나, 위 relation algebra의 결과는
Stanford, Stanford를 포함한다.
- σ(n1 ≠ n2)ρ[C1](c1, s, e1)(College) ⋈ ρ[C2](c2, s, e2)(College)
그러나, 위 relation algebra의 결과는
Stanford, Berkeley
Berkeley, Stanford를 포함한다.
- σ(n1 < n2)ρ[C1](c1, s, e1)(College) ⋈ ρ[C2](c2, s, e2)(College)

Reference
https://www.youtube.com/watch?v=GkBf2dZAES0&list=PL6hGtHedy2Z4EkgY76QOcueU8lAC4o6c3&index=10