[빅데이터 및 지식관리시스템] SQL에서 모든 것에 대한 만족을 표현하고 싶을때..?

Set vs Bag semantics

Relational Algebra에서는 기본적으로 set sementic을 기본으로 한다.

하지만 real-world에서 SQL로 구현할 때, bag sementic을 기본으로 한다.

 

Bag은 set과 비슷하지만, 같은 요소가 여러번 나타날 수 있다.

• {1,2,3,1}: 1이라는 중복을 허용하는 집합이다.
• {1,2,3}: 같은 bag이지만 중복이 없는 set이다.

또한 element의 순서가 중요한 list와 다르게 bag은 순서가 중요하지 않다.

 

SQL이 bag semantics를 기본으로 하는 이유는 일부 연산에서 더 효율적이기 때문이다.

또한 중복된 것을 "distinct" 키워드를 이용하여 제거할 수 있기 때문이다.

 

Division

Division은 기본 연산자는 아니지만, 관계대수에서 "모든 것에 대해 만족" 조건을 표현할 때 유용하다.

어떤 \(x\)에 대해 \(B\)에 속한 \(y\)와 \((x, y)\)조합으로 \(A\)에 존재하면, 그 \(x\)는 결과에 포함된다.

위 정의를 relational algebra형태(procedural, operational)로 표현한 것이다.

A에 있는 모든 \(x\)(projection)와  B를 합친다.(Cartesian Product)

이후 A를 빼고 다시 \(x\)에 대해 projection을 하면 조건을 만족하지않는 \(x\)만 남는다.

 

\(A\)의 모든 \(x\)에 대해 조건을 만족하지 않는 \(x\)을 빼면 division연산 결과가 나온다.

 

Examples of Algebra Quries

Q1. Find names of sailors who've reserved boat #103

Solution 2는 solution 1과 별 차이는 없다. 

단지, rename operation이 추가됐다.

Q2. Find names of sailors who've reserved a red boat?

이 결과는 red boat를 적어도 1개인 사람 모두 출력한다.

그러면 딱 1개인 사람만 출력하려면 어떻게 해야할까?

Count=1으로 지정하는 operation을 이용해야한다.

γ_{sid, sname; count(bid) → count}

Q5. Find sailors who've reserved a red or a green boat

이건 red와 green에 selection을 걸었는데

만약 Union연산을 이용하면 어떻게 표현할 수 있을까?

Tempboats := σ_{color='red'}(Boats) ∪ σ_{color='green'}(Boats)

단, 튜플 차원에서 걸래내는 위의 relation algebra가 더 효율적이다.

Q6. Find sailors who've reserved a red and a green boat

이 경우는 or와 다르게 튜플 차원에서 걸래낼 수 없다.

그 이유는 color가 red이면서 green일 수는 없기 때문이다.

즉, intersection 연산을 이용해야한다.

Q9. Find the names of sailors who've reserved all boats

Conceptual Evaluation

쿼리를 해석하고 결과를 정의할때, 개념적으로 다음 4단계의 절차를 따른다고 가정한다.

실제 시스템에서는 더 효율적이다.

• Cross-Product: 모든 튜플 조합을 생성한다.
• Selection(Where): 조건에 맞지 않는 튜플을 제거한다.
• Projection: 필요한 column만 선택한다.
• Distinct: 명시된 경우에 중복을 제거한다.

Range Variable(or Tuple Variable)

Range Variable은 table에 붙이는 alias다.

같은 relation이 From에서 2번 이상 등장할 때 명확성/ 간결성/ self-join을 대비해서 써주면 좋다.

 

중복 처리 관련 문제

Q. Find sailors who've reserved at least one boat

• Select에 S.sid가 아니라 S.sname이었다면?

Alice, Bob, Alice, Alice로 출력돼 이름만 보면 누군지 구분할 수 없다.(S.name은 primary key가 아님)

• DISTINCT를 S.sname와 같이 사용했다면?

이 경우 Alice, Bob으로 출력되는데 sid=1, sid=3으로 다른사람인데 생략돼 정보손실이 발생한다.

 

Self join

자기 자신과 join 하는 것을 말한다.

주로 같은 부서에 있는 직원끼리 비교, 상사-직원 관계 표현 등에 사용한다.

select * from emp e1, emp e2 where e1.mgr = e2.empno;

이 예시는 직장-상사의 관계를 표현하는 쿼리이다.

emp 테이블을 alias로 e1과 e2을 각각 사용했다.

 

그럼 직장 상사의 상사는 어떻게 구할까?

동일하게 자기 자신의 table 3개가 필요하다.

SELECT e1.ename, e2.ename, e3.ename from emp e1, emp e2, emp e3 where e1.mgr = e2.empno and e2.mgr=e3.empno;