[Database] 04 - 1 관계 대수와 SQL

관계 데이터 모델에서 지원되는 두 가지 정형적인 언어

- 관계 해석(relational calculus)

• 원하는 데이터만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 선언적인 언어

- 관계 대수(relational algebra)

• 어떻게 질의를 수행할 것인가를 명시하는 절차적 인어
• 관계 대수는 상용 관계 DBMS들에서 널리 사용되는 SQL의 이론적인 기초
• 관계 대수는 SQL을 구현하고 최적화하기 위해 DBMS의 내부 언어로서도 사용됨

SQL

- 상용 관계 DBMS들의 사실상의 표준 질의어인 SQL을 이해하고 사용할 수 있는 능력은 매우 중요함

- 사용자는 SQL을 사용하여 관계 데이터베이스에 릴레이션을 정의하고, 관계 데이터베이스에서 정보를 검색하고, 관계 데이터베이스를 갱신하며, 여러 가지 무결성 제약조건들을 명시할 수 있음

 

관계 대수

• 기존의 릴레이션들로부터 새로운 릴레이션을 생성함
• 릴레이션이나 관계 대수식(이것의 결과도 릴레이션임)에 연산자들을 적용하여 보다 복잡한 관계 대수식을 점차적으로 만들 수 있음
• 기본적인 연산자들의 집합으로 이루어짐
• 산술 연산자와 유사하게 단일 릴레이션이나 두 개의 릴레이션을 입력으로 받아 하나의 결과 릴레이션을 생성함
• 결과 릴레이션은 또 다른 관계 연산자의 입력으로 사용될 수 있음

관계 연산자

관계 연산자들의 종류와 표기법

*필수 연산자는 잘 알아둘 것

*조인 관련 연산자가 여러 개인 것에 주목할 것

관계 연산자들의 기능

 

실렉션 연산자 (형식: σ<실렉션 조건>(릴레이션))

• 한 릴레이션에서 실렉션 조건(selection condition)을 만족하는 투플들의 부분 집합을 생성함(행을 뽑음)
• 단항 연산자
• 결과 릴레이션의 차수는 입력 릴레이션의 차수와 같음
• 결과 릴레이션의 카디날리티는 항상 원래 릴레이션의 카디날리티보다 작거나 같음
• 실렉션 조건을 프레디키트(predicate)라고도 함
• 실렉션 조건은 일반적으로 릴레이션의 임의의 애트리뷰트와 상수, = , <>(같지 않다), <=, <, >=, > 등의 비교 연산자, AND, OR, NOT 등의 부울 연산자를 포함할 수 있음

실렉션 연산자

 

프로젝션 연산자 (형식: π<애트리뷰트 리스트>(릴레이션))

• 한 릴레이션의 애트리뷰트들의 부분 집합을 구함
• 결과로 생성되는 릴레이션은 <애트리뷰트 리스트>에 명시된 애트리뷰트들만 가짐
• 실렉션의 결과 릴레이션에는 중복 투플이 존재할 수 없지만, 프로젝션 연산의 결과 릴레이션에는 중복된 투플들이 존재할 수 있음(키 애트리뷰트를 포함하지 않다면 중복이 발생하여 카디널리티도 줄어들 수 있다.)

집합 연산자

• 릴레이션이 투플들의 집합이기 때문에 기존의 집합 연산이 릴레이션에 적용됨
• 세 가지 집합 연산자: 합집합, 교집합, 차집합 연산자
• 집합 연산자의 입력으로 사용되는 두 개의 릴레이션은 합집합 호환(union compatible)이어야 함
• 이항 연산자

합집합 호환(합집합이 형식적으로 가능한지 확인 -> 차수와 도메인이 같아야함)

• 두 릴레이션 R1(A1, A2, ..., An)과 R2(B1, B2, ..., Bm)이 합집합 호환일 필요 충분 조건은 n=m이고, 모든 1<=i<=n에 대해 domain(Ai)=domain(Bi)

 

합집합 연산자 (형식: 릴레이션1 ∪ 릴레이션2)

• 두 릴레이션 R과 S의 합집합 R ∪ S는 R 또는 S에 있거나 R과 S 모두에 속한 투플들로 이루어진 릴레이션
• 결과 릴레이션에서 중복된 투플들은 제외됨
• 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음

 

정리

관계 해서기 선언적 언어

관계 대수: 잘차적 언어

 

짝수의 집합: {x|x는 짝수}

 

부서번호가 3인 사원들의 집합

{t | EMPLOYEE(t) and t.dno = 3}

 

{t.ename, t.dno | EMPLOYEE(t) and t.salary >1000}

 

필수 관계 연산자 5가지

실렉션, 프로젝션, 합집합, 차집합, 카티션곱(주의: 교집합은 필수연산자 아님)

 

필수 연산자 vs. 유도된 연산자

*교집합은 차집합과 합집합을 활용하면 만들 수 있다.

 

rename 연산사 : 애트리뷰트 이름을 변경하는 연산자(합집합 연산시 이름이 다를 경우 사용해줌)

 

 

교집합 연산자 (형식: 릴레이션1 ∩ 릴레이션2)

• 두 릴레이션 R과 S의 교집합 R ∩ S는 R과 S 모두에 속한 투플들로 이루어진 릴레이션
• 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음
• 여기서도 역시 합집합 호환이 이루어져야 연산이 가능함

 

차집합 연산자 (형식: 릴레이션1 - 릴레이션2)

• 두 릴레이션 R과 S의 차집합 R - S는 R에는 속하지만 S에는 속하지 않은 투플들로 이루어진 릴레이션
• 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음
• 이 역시 합집합 호환을 만족해야 가능함

 

카티션 곱 연산자 (형식: R × S)

• 카디날리티가 n인 릴레이션 R(A1, A2, ..., An)과 카디날리티가 m인 릴레이션 S(B1, B2, ..., Bm)의 카티션 곱 R × S는 차수가 n+m이고, 카디날리티가 n*m이고, 애트리뷰트가 (A1, A2, ..., An, B1, B2, ..., Bm)이며, R과 S의 투플들의 모든 가능한 조합으로 이루어진 릴레이션
• 카티션 곱의 결과 릴레이션의 크기가 매우 클 수 있으며, 사용자가 실제로 원하는 것은 카티션 곱의 결과 릴레이션의 일부인 경우가 대부분이므로 카티션 곱 자체는 유용한 연산자가 아님

*사원이 부서에 속할 수 있는 모든 경우를 표현함

 

관계 대수의 완전성

• 실렉션, 프로젝션, 합집합, 차집합, 카티션 곱은 관계 대수의 필수적인 연산자
• 다른 관계 연산자들은 필수적인 관계 연산자를 두 개 이상 조합하여 표현할 수 있음
• 임의의 질의어가 적어도 필수적인 관계 대수 연산자들만큼의 표현력을 갖고 있으면 관계적으로 완전(relationally complete)하다고 말함 -> 관계 대수의 완전성

조인 연산자

• 두 개의 릴레이션으로부터 연관된 투플들을 결합하는 연산자
• 관계 데이터베이스에서 두 개 이상의 릴레이션들의 관계를 다루는데 매우 중요한 연산자
• 세타 조인(theta join), 동등 조인(equijoin), 자연 조인(natural join), 외부 조인(outer join), 세미 조인(semijoin) 등

 

자연 조인 (형식: R * S)

• 두 릴레이션의 공통된 애트리뷰트에 대해 동등 조인을 수행하고, 동등 조인의 결과 릴레이션에 있는 두 개의 조인 애트리뷰트 중 하나를 제외한 조인
• 여러 가지 조인 연산자들 중에서 가장 자주 사용됨
• 실제로 관계 데이터베이스에서 대부분의 질의는 실렉션, 프로젝션, 자연 조인으로 표현 가능
• 하나만이 아닌 여러애트리뷰트도 연산자가 될 수 있음

*확장된 자연조인 방법 ','(콤마)로 구분

 

디비전 연산자 (형식: R ÷ S, 각행의 집합중 공통된 것이 결과로 나타남)

• 차수가 n+m인 릴레이션 R(A1, A2, ..., An, B1, B2, ..., Bm)과 차수가 m인 릴레이션 S(B1, B2, ..., Bm)의 디비전 R ÷ S는 차수가 n이고, S에 속하는 모든 투플 u에 대하여 투플 tu(투플 t와 투플 u을 결합한 것)가 R에 존재하는 투플 t들의 집합
• 릴레이션 S의 모든(ALL) 투플 값과 쌍을 이루는 릴레이션 R의 A1, A2, ..., An 값
• “모든 …에 대해 ~하는“ 형태의 질의에 사용될 수 있음
• SQL로 표현할 때 동치를 활용: ~하지 않는 …가 없다
• 왼쪽 릴레이션의 불필한 애트리뷰트를 제거해준 뒤 처리해준다.

관계 대수 질의의 예

 

관계 대수의 한계

• 관계 대수는 산술 연산을 할 수 없음
• 집단 함수(aggregate function, 여러 개의 튜플에 대해 합계등을 구하는 것)를 지원하지 않음
• 정렬을 나타낼 수 없음
• 데이터베이스를 수정할 수 없음(읽기 전용임)
• 프로젝션 연산의 결과에 중복된 투플을 나타내는 것이 필요할 때가 있는데 이를 명시하지 못함

 

추가된 관계 대수 연산자

• 집단(aggregation) 함수: AVG, SUM, MIN, MAX, COUNT
• 디폴트 값으로 중복제거는 하지 않는다.

• 그룹화: 각 그룹에 대해 집단 함수를 적용(반드시 집단 함수와 함께 사용해야함)

 

외부 조인

• 상대 릴레이션에서 대응되는 투플을 갖지 못하는 투플이나 조인 애트리뷰트에 널값이 들어 있는 투플들을 다루기 위해서 조인 연산을 확장한 조인
• 두 릴레이션에서 대응되는 투플들을 결합하면서, 대응되는 투플을 갖지 않는 투플과 조인 애트리뷰트에 널값을 갖는 투플도 결과에 포함시킴
• 왼쪽 외부 조인(left outer join), 오른쪽 외부 조인(right outer join), 완전 외부 조인(full outer join)

 

왼쪽 외부 조인 (형식: R ⟕ S)

• 릴레이션 R과 S의 왼쪽 외부 조인 연산은 R의 모든 투플들을 결과에 포함시키고, 만일 릴레이션 S에 관련된 투플이 없으면 결과 릴레이션에서 릴레이션 S의 애트리뷰트들은 널값으로 채움

오른쪽 외부 조인 (형식: R ⟖ S)

• 릴레이션 R와 S의 오른쪽 외부 조인 연산은 S의 모든 투플들을 결과에 포함시키고, 만일 릴레이션 R에 관련된 투플이 없으면 결과 릴레이션에서 릴레이션 R의 애트리뷰트들은 널값으로 채움
• 소속 인원이 없더라도 부서를 전부 표현하고 싶을 때 사용가능

완전 외부 조인 (형식: R ⟗ S)

• 릴레이션 R와 S의 완전 외부 조인 연산은 R과 S의 모든 투플들을 결과에 포함시키고, 만일 상대 릴레이션에 관련된 투플이 없으면 결과 릴레이션에서 상대 릴레이션의 애트리뷰트들은 널값으로 채움 R ⟗ S = (R ⟕ S) ∪ (R ⟖ S)

세미 조인(semi-join)

- 왼쪽 혹은 오른쪽 릴레이션만을 나타내는 것

- anti semi-join도 존재 포함하지 않는 것만을 나타냄

*Q1 :  A  C

        a1 c3

 Q2 : A B

        a b

        e d

 

가정 : R X S = T

T % R = S

T % S = R

but, 산술연산과 완전히 동일하진 않음

R % S = V

V X S = R(?)

 

비디오 대여점에서 사용할 수 있는 아래와 같은 관계 데이터베이스 스키마를 보고 각 질의를 관계 대수식으로 표현하라CUSTOMER(CUSTOMER_ID, NAME, ADDRESS, PHONE)

VIDEO(VIDOE_ID, TITLE, GENRE)

RESERVED(CUSTOMER_ID, VIDEO_ID, DATE)

a. 제목이 ＇반지의 제왕＇인 비디오 테이프의 장르를 검색하라

b. 예약된 비디오 테이프의 ID를 검색하라

c. 예약되지 않은 비디오 테이프의 ID를 검색하라

d. 예약된 모든 비디오 테이프의 제목을 검색하라

e. 비디오 테이프를 예약한 고객의 이름을 검색하라

f. 장르가 ‘액션＇인 비디오 테이프를 예약한 고객의 주소를 검색하라

g. 장르가 ‘다큐멘터리’인 비디오 테이프를 모두 예약한 고객의 이름을 검색하라

 

ex)

학생(학번, 이름)

과목(코드, 교과목명, 개설학과)

수강내역(학번, 과목코드) -> 수강내역2(학번, 과목코드, 년도) 이렇게 되면 불가능(학번이 같아도 년도가 다르다면 디비젼 결과로 나오지 않기 때문 -> 해결을 위해 수강내역2 전체를 사용하지말고 일부만 추출해서 디비젼 해야함)

 

질의: 컴퓨터공학과에서 개설한 모든 교과목을 수강한 학생은?

S = select<개설학과 = '컴공'(과목)

답: 수강내역 % S

학생의 이름을 알고싶으면 학생이름과 자연조인시켜줌

 

관계대수식을 만들 때 고려사항
- 사용할 릴레이션? 질의 조건 또는 결과에 사용되는 애트리뷰트들을 포함하고 있는 릴레이션
- 무슨 관계 대수 연산자를 사용할 것인가?
 실렉션 조건이 있나? 무슨 애트리뷰트들이 출력되는가?
- 두 개 이상의 릴레이션이 포함되는 경우에 조인 필요
 조인 연산 종류 (세타 조인, 동등/자연 조인, 왼쪽/오른쪽/완전 외부 조인, 세미조인)
 조인 조건이 무엇인가? 조인 애트리뷰트는?
- 결과 릴레이션의 스키마는?

*실렉션 연산을 먼저 처리해 주는 것이 효율적임