관계 대수

목차 I. 관계 대수 II. SQL 개요 III. 데이터 정의어와 무결성 제약조건 IV. SELECT문 V. INSERT, DELETE, UPDATE문 VI. 트리거(trigger)와 주장(assertion) VII. 내포된 SQL

관계 데이터 모델에서 지원되는 두 가지 정형적인 언어 4.관계 대수와 SQL 관계 데이터 모델에서 지원되는 두 가지 정형적인 언어 관계 해석(relational calculus) 원하는 데이터만 명시하고 질의를 어떻게 수행할 것인가는 명시하지 않는 ( ) 관계 대수(relational algebra) 어떻게 질의를 수행할 것인가를 명시하는 ( ) 관계 대수는 상용 관계 DBMS들에서 널리 사용되는 SQL의 이론적인 기초 관계 대수는 SQL을 구현하고 최적화하기 위해 DBMS의 내부 언어로서도 사용됨 SQL 상용 관계 DBMS들의 사실상의 표준 질의어인 SQL을 이해하고 사용할 수 있는 능력은 매우 중요함 사용자는 SQL을 사용하여 관계 데이터베이스에 릴레이션을 정의하고, 관계 데이터베이스에서 정보를 검색하고, 관계 데이터베이스를 갱신하며, 여러 가지 무결성 제약조건들을 명시할 수 있음

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

4.1 관계 대수 관계 연산자 1 관계 연산자 2 [그림4.1] 관계 연산자

4.1 관계 대수  ᛞ  [표4.1] 관계 연산자들의 종류와 표기법 분류 연산자 표기법 필수적인 연산자 б ( ) б 프로젝션(projection) π 합집합(union) ∪ 차집합(difference)  x 편의를 위해 유도된 연산자 교집합(intersection) ∩ 세타조인(theta join) ᛞ 동등 조인(equijoin) 자연 조인(natural join) * 디비젼(division) 

4.1 관계 대수 x  = [그림4.2] 관계 연산자들의 기능 intersection selection union projection union intersection difference Cartesian product division R S  = x [그림4.2] 관계 연산자들의 기능

4.1 관계 대수 셀렉션 연산자 한 릴레이션에서 셀렉션 조건(selection condition)을 만족하는 튜플들의 부분 집합을 생성함 주어진 조건을 만족하는 튜플들만 걸러내는 연산으로 생각할 수 있음 셀렉션 연산자는 하나의 입력 릴레이션에 적용되므로 단항 연산자 셀렉션의 결과 릴레이션의 차수는 입력 릴레이션의 차수와 같음 결과 릴레이션의 카디날리티는 항상 원래 릴레이션의 카디날리티보다 작거나 같음 셀렉션 조건을 프레디키트(predicate)라고도 함 셀렉션 조건은 일반적으로 릴레이션의 임의의 애트리뷰트와 상수, = , <>, <=, <, >=, > 등의 ( ), AND, OR, NOT 등의 ( ) 포함할 수 있음 형식 : б<셀렉션조건>(릴레이션)

4.1 관계 대수 셀렉션(selection) 연산자 бDNO=3(EMPLOYEE) 예: 셀렉션 EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭 대리 1077 2500000 2 3426 박영권 과장 4377 3000000 1 3011 이수민 부장 4000000 3 1003 조민희 3427 최종철 사원 1500000 1365 김상원 이성래 사장 ^ 5000000 бDNO=3(EMPLOYEE) RESULT EMPNO EMPNAME TITLE MANAGER SALARY DNO 3011 이수민 부장 4377 4000000 3 3427 최종철 사원 1500000

4.1 관계 대수 프로젝션 연산자 한 릴레이션의 ( ) 들의 부분 집합을 구함 한 릴레이션의 ( ) 들의 부분 집합을 구함 프로젝션의 결과로 생성되는 릴레이션은 <애트리뷰트 리스트>에 명시된 애트리뷰트들만 가짐 셀렉션의 결과 릴레이션에는 중복 튜플이 존재할 수 없지만, 프로젝션 연산의 결과 릴레이션에는 중복된 튜플들이 존재할 수 있음 형식 : π<애트리뷰트 리스트>(릴레이션)

4.1 관계 대수 πTITLE(EMPLOYEE) 예: 프로젝션 질의 : 모든 사원들의 직급을 검색하라. 프로젝션 연산자의 입력 릴레이션에는 중복된 튜플이 없지만 이 릴레이션에서 일부 애트리뷰트를 프로젝션한 결과 릴레이션에는 중복 튜플이 존재하므로, 중복을 제거해야 한다. EMPLOYEE RESULT RESULT EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭 대리 1077 2500000 2 3426 박영권 과장 4377 3000000 1 3011 이수민 부장 4000000 3 1003 조민희 3427 최종철 사원 1500000 1365 김상원 이성래 사장 ^ 5000000 TITLE 대리 과장 부장 사원 사장 TITLE 대리 과장 부장 사원 사장 중복이 제거된 릴레이션 중복이 존재하는 릴레이션 πTITLE(EMPLOYEE)

4.1 관계 대수 집합 연산자 릴레이션이 튜플들의 집합이기 때문에 기존의 집합 연산이 릴레이션에 적용됨 세 가지 집합 연산자: ( ) 연산자 집합 연산자의 입력으로 사용되는 두 개의 릴레이션은 합집합 호환(union compatible)이어야 함 집합 연산자들은 두 개의 릴레이션을 입력으로 받아들이므로 이항 연산자

4.1 관계 대수 합집합 호환 두 릴레이션 R1(A1, A2, ..., An)과 R2(B1, B2, ..., Bm)이 합집합 호환일 필요 충분 조건은 n=m이고, 모든 1<=i<=n에 대해 domain(Ai)=domain(Bi) 예: 합집합 호환 다음의 합집합 예에서 EMPLOYEE 릴레이션 스키마와 EDPARTMENT 릴레이션 스키마는 애트리뷰트 수가 다르므로 합집합 호환이 되지 않는다. EMPLOYEE(EMPNO, EMPNAME, TITLE, MANAGER, SALARY, DNO) DEPARTMENT(DEPTNO, DEPTNAME, FLOOR) 그러나 EMPLOYEE 릴레이션에서 DNO를 프로젝션한 결과 릴레이션(πDNO(EMPLOYEE))과 DEPARTMENT 릴레이션에서 DEPTNO를 프로젝션한 결과 릴레이션 (πDEPTNO(DEPARTMENT))은 애트리뷰트 수가 같으며 DNO와 DEPTNO의 도메인이 같으므로 합집합 호환이다.

4.1 관계 대수 합집합 연산자 두 릴레이션 R과 S의 합집합 R ∪ S는 R 또는 S에 있거나 R과 S 모두에 속한 튜플들로 이루어진 릴레이션 결과 릴레이션에서 ( )된 튜플들은 제외됨 결과 릴레이션의 차수는 R 또는 S의 차수와 같으며, 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음

πDNO(бEMPNAME =‘김창섭’(EMPLOYEE)) 4.1 관계 대수 예: 합집합 질의 : 김창섭이 속한 부서이거나 개발 부서의 부서번호를 검색하라. EMPLOYEE EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭 대리 1077 2500000 2 3426 박영권 과장 4377 3000000 1 3011 이수민 부장 4000000 3 1003 조민희 3427 최종철 사원 1500000 1365 김상원 이성래 사장 ^ 5000000 πDNO(бEMPNAME =‘김창섭’(EMPLOYEE)) RESULT 1 DNO 2

πDEPTNO(бDEPTNAME =‘개발’(DEPARTMENT)) 4.1 관계 대수 DEPARTMENT DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 RESULT 2 DEPTNO 3 πDEPTNO(бDEPTNAME =‘개발’(DEPARTMENT)) RESULT 3 ← RESULT 1 U RESULT 2 RESULT 3 DEPTNO 2 3

4.1 관계 대수 교집합 연산자 두 릴레이션 R과 S의 교집합 R ∩ S는 R과 S 모두에 속한 튜플들로 이루어진 릴레이션

πDNO(бEMPNAME =‘김창섭’ OR EMPNAME = ‘최종철’(EMPLOYEE)) 4.1 관계 대수 예: 교집합 질의 : 김창섭 또는 최종철이 속한 부서이면서 기획 부서의 부서번호를 검색하라. EMPLOYEE EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭 대리 1077 2500000 2 3426 박영권 과장 4377 3000000 1 3011 이수민 부장 4000000 3 1003 조민희 3427 최종철 사원 1500000 1365 김상원 이성래 사장 ^ 5000000 πDNO(бEMPNAME =‘김창섭’ OR EMPNAME = ‘최종철’(EMPLOYEE)) RESULT 1 DNO 2 3

πDEPTNO(бDEPTNAME =‘기획’(DEPARTMENT)) 4.1 관계 대수 DEPARTMENT DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 RESULT 2 DEPTNO 2 πDEPTNO(бDEPTNAME =‘기획’(DEPARTMENT)) RESULT 3 ← RESULT 1 ∩ RESULT 2 RESULT 3 DEPTNO 2

4.1 관계 대수 차집합 연산자 두 릴레이션 R과 S의 차집합 R - S는 R에는 속하지만 S에는 속하지 않은 튜플들로 이루어진 릴레이션 결과 릴레이션의 차수는 R 또는 S의 ( ), 결과 릴레이션의 애트리뷰트 이름들은 R의 애트리뷰트들의 이름과 같거나 S의 애트리뷰트들의 이름과 같음

4.1 관계 대수 πDEPTNO(DEPARTMENT) 예: 차집합 질의 : 소속된 직원이 한 명도 없는 부서의 부서번호를 검색하라. DEPARTMENT DEPTNO DEPTNAME FLOOR 1 영업 8 2 기획 10 3 개발 9 4 총무 RESULT 1 DEPTNO 1 2 3 4 πDEPTNO(DEPARTMENT)

RESULT 3 ← RESULT 1 - RESULT 2 4.1 관계 대수 EMPLOYEE EMPNO EMPNAME TITLE MANAGER SALARY DNO 2106 김창섭 대리 1077 2500000 2 3426 박영권 과장 4377 3000000 1 3011 이수민 부장 4000000 3 1003 조민희 3427 최종철 사원 1500000 1365 김상원 이성래 사장 ^ 5000000 πDNO(EMPLOYEE) RESULT 2 DNO 2 1 3 RESULT 3 ← RESULT 1 - RESULT 2 RESULT 3 DEPTNO 4

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

4.1 관계 대수 예: 카티션 곱 연산자 질의 : EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션의 카티션 곱을 구하라 EMPNO 2106 3426 3011 EMPNO DEPTNO 2106 2 3426 1 3011 3 1003 3427 1365 4377 RESULT EMPLOYEE x DEPARTMENT DEPARTMENT DEPTNO 1 2

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

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

4.1 관계 대수 세타 조인과 동등 조인 두 릴레이션 R(A1, A2, ..., An)과 S(B1, B2, ..., Bm)의 세타 조인의 결과는 차수가 n+m이고, 애트리뷰트가 (A1, A2, ..., An, B1, B2, ..., Bm)이며, 조인 조건을 만족하는 튜플들로 이루어진 릴레이션 세타는 {=, <>, <=, <, >=, >} 중의 하나 동등 조인은 세타 조인 중에서 비교 연산자가 ( ) 인 조인

EMPLOYEE DNO=DEPTNO DEPARTMENT 4.1 관계 대수 예: 동등조인 질의 : EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션을 동등 조인 구하라 EMPLOYEE EMPNO EMPNAME DNO 2106 김창섭 2 3426 박영권 1 3011 이수민 3 1003 조민희 3427 최종철 DEPARTMENT DEPTNO DEPTNAME 1 영업 2 기획 3 개발 4 총무 EMPLOYEE DNO=DEPTNO DEPARTMENT RESULT EMPNO EMPNAME DNO 2106 김창섭 2 3426 박영권 1 3011 이수민 3 1003 조민희 3427 최종철 DEPTNO DEPTNAME 2 기획 1 영업 3 개발

4.1 관계 대수 자연 조인 동등 조인의 결과 릴레이션에서 조인 애트리뷰트를 한 개 제외한 조인 여러 가지 조인 연산자들 중에서 가장 자주 사용됨 실제로 관계 데이터베이스에서 대부분의 질의는 셀렉션, 프로젝션, 자연 조인으로 표현 가능

EMPLOYEE * DNO,DEPTNO DEPARTMENT 4.1 관계 대수 예: 자연조인 질의 : EMPLOYEE 릴레이션과 DEPARTMENT 릴레이션을 자연 조인 구하라 EMPLOYEE EMPNO EMPNAME DNO 2106 김창섭 2 3426 박영권 1 3011 이수민 3 1003 조민희 3427 최종철 DEPARTMENT DEPTNO DEPTNAME 1 영업 2 기획 3 개발 4 총무 EMPLOYEE * DNO,DEPTNO DEPARTMENT RESULT EMPNO EMPNAME DNO 2106 김창섭 2 3426 박영권 1 3011 이수민 3 1003 조민희 3427 최종철 DEPTNAME 기획 영업 개발

4.1 관계 대수 디비전 연산자 차수가 n+m인 릴레이션 R(A1, A2, ..., An, B1, B2, ..., Bm)과 차수가 m인 릴레이션 S(B1, B2, ..., Bm)의 디비전 R ÷ S는 차수가 n이고, S에 속하는 모든 튜플 u에 대하여 튜플 tu(튜플 t와 튜플 u을 결합한 것)가 R에 존재하는 튜플 t들의 집합

4.1 관계 대수    예: 디비젼 RESULT1 RESULT2 RESULT3 C A# a1 a4 AB A# B# a1

πEMPNAME, SALARY(бDNO=2 OR DNO=3(EMPLOYEE)) 4.1 관계 대수 관계 대수 질의의 예 예: 셀렉션, 프로젝션 질의 : 2번 부서나 3번 부서에 근무하는 모든 사원들의 이름과 급여를 검색하라. EMPLOYEE(EMPNO, EMPNAME, TITLE, MANAGER, SALARY, DNO) DEPARTMENT(DEPTNO, DEPTNAME, FLOOR) πEMPNAME, SALARY(бDNO=2 OR DNO=3(EMPLOYEE)) 예: 셀렉션, 프로젝션, 조인 질의 : 개발 부서에서 근무하는 모든 사원들의 이름을 검색하라. πEMPNAME(EMPLOYEE DNO=DEPTNO (бDEPTNAME=‘개발’(DEPARTMENT)))

4.1 관계 대수 관계 대수의 한계 관계 대수는 ( ) 연산을 할 수 없음 정렬을 나타낼 수 없음 관계 대수는 ( ) 연산을 할 수 없음 ( ) 함수(aggregate function)를 지원하지 않음 정렬을 나타낼 수 없음 데이터베이스를 수정할 수 없음 프로젝션 연산의 결과에 ( ) 튜플을 나타내는 것이 필요할 때가 있는데 이를 명시하지 못함

AVGSALARY(EMPLOYEE)  2,928,571 4.1 관계 대수 추가된 관계 대수 연산자 집단 함수 예: 집단합수 질의 : 모든 사원들의 급여 평균은 얼마인가? EMPLOYEE EMPNO … SALARY DNO 2106 2500000 2 3426 3000000 1 3011 4000000 3 1003 3427 1500000 1365 4377 5000000 AVGSALARY(EMPLOYEE)  2,928,571

DNOgAVG(SALARY)(EMPLOYEE) 4.1 관계 대수 추가된 관계 대수 연산자(계속) 그룹화 예: 그룹화 질의 : 각 부서별 사원들의 급여 평균은 얼마인가? EMPLOYEE EMPNO … SALARY DNO 2106 2500000 2 3426 3000000 1 3011 4000000 3 1003 3427 1500000 1365 4377 5000000 RESULT DNO AVG(SALARY) 1 2250000 2 3500000 3 2750000 DNOgAVG(SALARY)(EMPLOYEE)

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

4.1 관계 대수 왼쪽 외부 조인 릴레이션 R과 S의 왼쪽 외부 조인 연산은 R의 모든 튜플들을 결과에 포함시키고, 만일 릴레이션 S에 관련된 튜플이 없으면 결과 릴레이션에서 릴레이션 S의 애트리뷰트들은 널값으로 채움 예: 자연조인과 왼쪽 외부 조인 R과 S의 자연 조인 R A B C a1 b1 c1 a2 b2 c2 S C D E c1 d1 e1 c3 d2 e2 RESULT A B C D E a1 b1 c1 d1 e1 R * S R과 S의 왼쪽 외부 조인 R A B C a1 b1 c1 a2 b2 c2 S C D E c1 d1 e1 c3 d2 e2 RESULT A B C D E a1 b1 c1 d1 e1 a2 b2 c2 ^ R S

4.1 관계 대수 오른쪽 외부 조인 릴레이션 R와 S의 오른쪽 외부 조인 연산은 S의 모든 튜플들을 결과에 포함시키고, 만일 릴레이션 R에 관련된 튜플이 없으면 결과 릴레이션에서 릴레이션 R의 애트리뷰트들은 널값으로 채움 예: R과 S의 오른쪽 외부 조인 R A B C a1 b1 c1 a2 b2 c2 S C D E c1 d1 e1 c3 d2 e2 RESULT A B C D E a1 b1 c1 d1 e1 ^ c3 d2 e2 R S

4.1 관계 대수 완전 외부 조인 릴레이션 R와 S의 완전 외부 조인 연산은 R과 S의 모든 튜플들을 결과에 포함시키고, 만일 상대 릴레이션에 관련된 튜플이 없으면 결과 릴레이션에서 상대 릴레이션의 애트리뷰트들은 널값으로 채움 예: 완전 외부 조인 R A B C a1 b1 c1 a2 b2 c2 S C D E c1 d1 e1 c3 d2 e2 RESULT A B C D E a1 b1 c1 d1 e1 a2 b2 c2 ^ c3 d2 e2 R S