데이터베이스 (Database) (Part 1) 문양세강원대학교 IT특성화대학컴퓨터과학전공
관계대수및관계해석강의요약 단항관계연산 : 실렉트와프로젝트 집합이론과관계대수연산 이항관계연산 : 조인과디비전연산 추가적인관계연산 관계대수질의의예 투플관계해석 도메인관계해석 Page 2
관계대수 (Relational Algebra) 데이터모델의구성요소 데이터베이스구조와제약조건의정의 데이터를다루기위한연산들의집합 관계대수란? 릴레이션들을다루는연산들 검색요구 ( 질의 ) 를기술하는데에사용함 릴레이션에대한연산의결과도릴레이션임 관계대수연산의종류 수학적집합연산 : 합집합, 교집합, 차집합, 카티션프로덕트 관계데이터베이스를위한특별연산 : 실렉트, 프로젝트, 조인 Page 3
실렉트 (Select) 연산 (1/2) 릴레이션 R 에서어떤선택조건을만족하는투플들을선택함 결과릴레이션은 R 과동일한애트리뷰트들을가짐 ( 릴레이션차수동일 ) 결과릴레이션은 r(r) 의투플중애트리뷰트값들이선택조건을만족하는 투플들로구성됨 연산형식관계대수란? σ < 선택조건 > (R) 선택조건은 R 의애트리뷰트들에대한부울식 (Boolean equation) 임 < 애트리뷰트이름 > < 비교연산자 > < 상수값 > ( 예 : Age 18) < 애트리뷰트이름 > < 비교연산자 > < 애트리뷰트이름 > ( 예 : Emp.ID = Manager.ID) 비교연산자 : =, <,, >,,, SUBSTRING_OF ( 문자열연산시이용 ) Page 4
실렉트 (Select) 연산 (2/2) 예제 : σ DNO=4 (EMPLOYEE) σ SALARY>30000 (EMPLOYEE) σ (DNO=4 AND SALARY>25000) OR DNO=5 (EMPLOYEE) Page 5
실렉트연산의실행예제 σ (DNO=4 AND SALARY>25000) OR (DNO=5 AND SALARY>30000) (EMPLOYEE) Page 6
실렉트연산의성질 논리연산자이용 AND, OR, NOT 등논리연산자이용가능 교환법칙성립 σ < 조건 1> (σ < 조건 2> (R)) = σ < 조건 2> (σ < 조건 1> (R)) 결합법칙성립 σ < 조건 1> (σ < 조건 2> (...(σ < 조건 n> (R))...)) = σ < 조건 1>AND< 조건 2>... AND< 조건 n> (R) Page 7
프로젝트 (Project) 연산 (1/2) PROJECT 연산 (Π 로표기 ) 릴레이션 R 에서애트리뷰트리스트에명시된애트리뷰트들만선택함 연산형식 : Π < 애트리뷰트리스트 > (R) 결과릴레이션은애트리뷰트리스트에명시된 R 의애트리뷰트들만가짐 결과릴레이션의투플수는원래릴레이션의투플수보다작거나같음 ( 작을수있는이유 : 릴레이션은중복투플을인정하지않음 중복투플의제거 ) 애트리뷰트리스트가원래릴레이션의수퍼키를포함하면결과릴레이션과원래 릴레이션의투플수는동일함 ( 수퍼키를포함하여중복되지않기때문 ) 예제 : Π FNAME, LNAME, SALARY (EMPLOYEE) Page 8
프로젝트 (Project) 연산 (2/2) PROJECT 연산은결과릴레이션이수학적집합이므로중복된투플들을 제거함 예제 : Π SEX, SALARY (EMPLOYEE) 봉급이 25000 원인여자사원들이여러명이더라도결과릴레이션에는단지하나의 <F, 25000> 투플만이포함되며, 나머지는제거됨 Page 9
프로젝트연산의실행예제 Π FNAME, LNAME, SALARY (EMPLOYEE) Π SEX, SALARY (EMPLOYEE) Page 10
연산순서와이름변경연산 (1/4) 다수의연산을결합하여관계대수식 ( 질의 ) 을형성할수있음 예제 : 부서 5 에서일하는사원들의이름과월급을검색 Π FNAME, LNAME, SALARY (σ DNO=5 (EMPLOYEE)) Page 11
연산순서와이름변경연산 (2/4) 중간단계의임시릴레이션에이름을부여할수도있음 DEPT4_EMPSEMPS σ DNO=5 (EMPLOYEE) RESULT Π FNAME, LNAME, SALARY (DEPT4_EMPS) DEPT4_EMPS RESULT FNAME LNAME Page 12
연산순서와이름변경연산 (3/4) 결과릴레이션의애트리뷰트이름은재명명할수도있음 TEMP σ DNO=4 (EMPLOYEE) R(FIRSTNAME, LASTNAME, SALARY) Π FNAME, LNAME, SALARY (TEMP) Page 13
연산순서와이름변경연산 (4/4) 이름변경연산 DEPT4_EMPSEMPS σ DNO=5 (EMPLOYEE) S(B1, B2,, Bn) (R): 릴레이션이름과애트리뷰트이름을모두변경 S: 새로운릴레이션의이름 B 1, B 2,, B n : 새로운애트리뷰트의이름 S (R): 릴레이션이름을변경 (B1, B2,, Bn) (R): 애트리뷰트이름을변경 Page 14
관계대수및관계해석강의요약 단항관계연산 : 실렉트와프로젝트 집합이론과관계대수연산 이항관계연산 : 조인과디비전연산 추가적인관계연산 관계대수질의의예 투플관계해석 도메인관계해석 Page 15
합집합, 교집합, 차집합연산 (1/4) 수학적집합이론에서의이진연산 데이터베이스구조와제약조건의정의방식 합집합 : R1 R2 교집합 : R1 R2 차집합 : R1 R2 연산,, 에서의호환성 피연산자릴레이션 R 1 (A 1,A 2,..., A n ) 과 R 2 (B 1, B 2,..., B n ) 는애트리뷰트들의갯수가동일하 고, 대응되는애트리뷰트들의도메인이호환성을가져야함 ; 즉, i= 1, 2,..., n 에대하여 dom(a i i) = dom(b i ) 이어야함 이조건을합집합호환성 (union compatibility) 이라부름 Page 16
합집합, 교집합, 차집합연산 (2/4) 연산,, 의결과릴레이션은피연산자릴레이션 R1 과동일한애트리뷰 트이름들을가짐 ( 관례적으로 ) 합집합과교집합은교환법칙과결합법칙이성립됨 ( 차집합은?) Page 17
합집합, 교집합, 차집합연산 (3/4) 5 번부서에서일하거나 5 번부서에서근무하는사원들을직접감독하는 모든사원의주민등록번호를검색 DEP5_EMPS σ DNO=5 (EMPLOYEE) RESULT1 Π SSN (DEP5_EMPS) EMPS) RESULT2(SSN) Π SUPERSSN (DEP5_EMPS) RESULT RESULT1 RESULT2 Page 18
합집합, 교집합, 차집합연산 (4/4) STUDENT INSTRUCTOR STUDENT INSTRUCTOR STUDENT - INSTRUCTOR INSTRUCTOR - STUDENT Page 19
카티션곱 ( 또는크로스프로덕트 ) 연산 (1/3) 카티션곱 (CARTESIAN PRODUCT) R(A 1, A 2,...,A m, B 1, B 2,..., B n ) R 1 (A 1, A 2,..., A m ) R 2 (B 1, B 2,..., B n ) R 의투플 t 는 R 1 의투플 t 1 과 R 2 의투플 t 2 로분리됨 즉, t[a 1,A 2,..., A m ] = t 1 그리고 t[b 1, B 2,..., B n ] = t 2 R 1 이 n 1 개의투플을, R 2 가 n 2 개의투플을갖는다면, R 은 n 1 n 2 개의투플을가지게됨 카티션곱은그자체로는큰의미가없는연산이지만 적절한 SELECT 연산과함께사용되면두릴레이션에서 서로관련이있는투플들을생성하는데사용될수있음 Page 20
카티션곱 ( 또는크로스프로덕트 ) 연산 (2/3) 모든여자사원들에대해그들의부양가족들의이름을검색 FENAME_EMPS EMPS σ SEX=F (EMPLOYEE) EMPNAMES Π FNAME, LNAME, SSN (FENAME_EMPS) EMP_DEPENDENTS EMPNAMES DEPENDENT ACTUAL_DEPENDENTS σ SSN=ESSN (EMP_DEPENDENTS) RESULT Π FNAME, LNAME, DEPENDENT_NAME (ACTUAL_DEPENDENTS) Page 21
카티션곱 ( 또는크로스프로덕트 ) 연산 (3/3) FENAME_EMPS σ SEX=F (EMPLOYEE) EMPNAMES Π FNAME, LNAME, SSN (FENAME_EMPS) EMP_DEPENDENTS EMPNAMES DEPENDENT ACTUAL_DEPENDENTS σ SSN=ESSN (EMP_DEPENDENTS) RESULT Π FNAME, LNAME, (AC UA E E E DEPENDENT_NAME(ACTUAL_DEPENDENTS) 3 7 = 21 Page 22
관계대수및관계해석강의요약 단항관계연산 : 실렉트와프로젝트 집합이론과관계대수연산 이항관계연산 : 조인과디비전연산 추가적인관계연산 관계대수질의의예 투플관계해석 도메인관계해석 Page 23
조인 (Join) 연산 (1/2) Join 연산 두릴레이션으로부터관련있는투플을결합하여하나의투플로생성함 관련성의여부를조건으로표시하며, 이를조인조건이라고함 R < 조인조건 > S 조인조건 < 조건 > AND < 조건 > AND AND < 조건 > 각조건의형태는 A i ΘB j 이며, A i 는 R 의애트리뷰트, B j 는 S 의애트리뷰트임 Θ = {=, <,, >,, } 조인조건에사용된속성 (A i 와 B j 를조인속성이라고부름 ) Theta Join 일반적인조인조건 (=, <,, >,, ) 을가진조인연산 Page 24
조인 (Join) 연산 (2/2) DEPT_MGR DEPARTMENT MGRSSN=SSN EMPLOYEE Page 25
동등조인 (Equi-Join) 조인조건에서동등비교 (equality comparison) 만을사용하는조 EQUIJOIN 사용예제 : 모든 DEPARTMENT 의이름과그관리자의이름을검색하라 : T DEPARTMENT MGRSSN=SSN EMPLOYEE RESULT Π DNAME,FNAME,LNAME (T) Page 26
자연조인 (Natural Join) (1/4) EQUIJOIN 의결과에는두조인속성의값이중복되어나타남 조인결과에서조인속성하나를제거하여중복된값이나타나지않도록한조인을 자연조인이라고함 표시법 : R R1 (R1의조인애트리뷰트들 ), (R2의조인애트리뷰트들 ) R2 예제 : 모든 EMPLOYEE 의이름과그의 DEPARTMENT 이름을검색하라 T EMPLOYEE (DNO), (DNUMBER) DEPARTMENT RESULT Π FNAME, LNAME, DNAME (T) 두조인속성이동일한이름을갖는다면간단히 R R1 R2 라고표시함 예제 : 모든 EMPLOYEE 의이름과그상급자의이름을검색하라 SUPERVISOR(SUPERSSN, SFN, SLN) Π SSN, FNAME, LNAME (EMPLOYEE) T EMPLOYEE SUPERVISOR RESULT Π FNAME, LNAME, SFN, SLN (T) Page 27
자연조인 (Natural Join) (2/4) DNUM DEPT DNUM PROJ_DEPT PROJECT DEPT DNUM Page 28
자연조인 (Natural Join) (3/4) DNUMBER DNUMBER DEPT_LOCS DEPT_LOCATIONS DEPARTMENT DNUMBER Page 29
자연조인 (Natural Join) (4/4) 주의사항 자연조인에서는조인애트리뷰트들이양쪽의릴레이션에서동일한이름을가져야하며, 그렇지않는경우조인속성의이름을먼저동일하게변경해야함 두릴레이션에서하나이상의조인애트리뷰트쌍이존재하는경우주의가요망됨 예제 : 모든 EMPLOYEE 의이름과그가일하는 DEPARTMENT 의이름을검색하 라 에대한자연조인은다음과같이작성함 DEPT(DNAME, DNUM, MGRSSN, MGRSTARTDATE) DEPARTMENT PROJ_DEPT PROJECT DEPT // DUNM 이조인속성임 ; // MGRSSN 은조인속성이아님 Page 30
셀프조인 (Self Join) 하나의릴레이션에대한조인 Self join 은한릴레이션의서로다른두사본을조인하는것으로간주함 이경우, 사본릴레이션에서는원본애트리뷰트이름을재명명 (renaming) 하는것 이유용함 예제 : 모든 EMPLOYEE 의이름과그의 SUPERVISOR 의이름을검색하라. SUPERVISOR(SSSN,SFN,SLN) Π SSN,FNAME,LNAME (EMPLOYEE) T EMPLOYEE SUPERSSN=SSSN SUPERVISOR SUPERSSN SSSN RESULT Π FNAME,LNAME,SFN,SLN (T) Page 31
조인선택율 (selectivity) 선택율 = 결과투플의수 / 전체가능한투플의수 조인선택율 = 조인결과로선택된투플의개수 / [(R 의투플수 ) (S 의투플수 )] 왼편의조인선택율은? Page 32
관계대수연산의완전집합 최소한의연산자집합 지금까지소개한모든연산자는선택 (SELECT), 프로젝트 (PROJECT), 합집합 (UNION), 차집합 (SET DIFFERNECE), 카티션프로덕트 (CARTESIAN PRODUCT) 연산들만의 조합으로표현할수있음 연산자집합 {σ, Π,,, } 를관계대수연산자의완전집합 (complete set) 이라부름 이연산자집합과동등한모든질의언어들은관계적으로완전하다 (relationally complete) 라고정의함 기타연산자의표현 R S = (R S) ((R S) (S R)) R < 조건 >S > = σ < 조건 > (R S) Page 33
디비전연산 (1/3) T(Y) = R(Z) S(X) 는다음과같이정의됨 (X Z 이고, Y = Z X 임 ) T1 = Π Y (R) T2 = Π Y ((S T1) - R) T = T1 T2 예제 ( 과정은다음페이지참조 ) R A B S A T B a1 b1 a1 b1 = a2 b1 a2 b4 a3 b1 a3 a4 b1 a1 b2 a3 b2 T1 = Π B (R) a2 a3 a4 a1 a2 a3 b3 b3 b3 b4 b4 b4 T2 = Π B (( S T1) R) T = T1 T2 Page 34
디비전연산 (2/3) T 1 B S T 1 A B S T 1 R A B = b 1 b 2 b 3 a 1 a 1 a 1 b 1 b 2 b 3 a 1 a 2 b 3 b 2 T R S T 1 = Π B (R) T 2 = Π B ((S T 1 ) R) T = T 1 T 2 a 1, a 2, a 3 와의모든조합이있는놈 : b 1, b 4 3 b 4 a 1 3 b 4 a 2 a 2 b 1 b 2 a 2 b 3 a 2 b 4 a 3 b 1 a 3 b 2 a 3 b 3 B T 2 b 2 b 2 b 3 T B b 1 a 3 b 4 b 4 Page 35
디비전연산 (3/3) 질의 : Smith 가근무하는모든프로젝트에서근무하는사원들의이름을검색하라. = SMITH σ FNAME= John AND LNAME= Smith (EMPLOYEE) SMITH_PNOS Π PNO (WORKS_ON ESSN=SSNSMITH) SSN_PNOS Π ESSN, PNO (WORKS_ON) SSNS(SSN) SSNPNOS SMITH_PNOS RESULT Π FNAME, LNAME (SSNS * EMPLOYEE) Page 36
관계대수및관계해석강의요약 단항관계연산 : 실렉트와프로젝트 집합이론과관계대수연산 이항관계연산 : 조인과디비전연산 추가적인관계연산 관계대수질의의예 투플관계해석 도메인관계해석 Page 37
집단함수와집단화 (1/2) 집단함수 (aggregate function) 혹은집계함수 SUM, COUNT, AVERAGE, MIN, MAX 함수를의미함 이들은데이터베이스응용에서값들의집합또는투플들의집합에적용되며, 표준관계 대수로표현할수없음 다음과같이표현하며, 그룹화애트리뷰트들은선택적임 < 그룹화애트리뷰트들 > F < 함수리스트 > (R) 예제 1: 모든사원의평균급여를검색 ( 그룹화불필요 ) R(AVGSAL) F AVERAGE SALARY (EMPLOYEE) 예제 2: 각부서에대해, 부서번호와부서별사원수와평균급여를검색 R(DNO, NUMEMPS, AVGSAL) DNO F COUNT SSN, AVERAGE SALARY (EMPLOYEE) 위의예제에서 DNO 를그룹화애트리뷰트 (grouping attribute) 라고부름 Page 38
집단함수와집단화 (2/2) Page 39
순환적폐포 (Recursive Closure) 연산 (1/2) 동일한테이블에서투플들간순환적관계 (recursive relationship) 를질의하는데 사용됨 관계대수로서는표현할수없음 예 : Employee 테이블에서사원과상사간의관계에대하여특정사원의모든상사 ( 직간접상사관계 ) 에있는직원을모두검색하시오. 이러한질의는루핑 (looping) 을사용하여한단계상사들의집합을구하고, 이를바 탕으로다음단계상사를구하며, 이러한과정을더이상의상사집합이없을때까 지 ( 사장이나올때까지 ) 구해나가야하므로루핑처리가필요하게됨 호스트언어등을사용하여해결할수있음 Page 40
순환적폐포 (Recursive Closure) 연산 (2/2) Page 41
외부조인연산 (1/4) 외부조인 (OUTER JOIN) 정규 EQUIJOIN 이나자연조인 (NATURAL JOIN) 연산에서조인조건을만족하지않은 투플들은결과릴레이션에도나타나지않음 조인에참여하는릴레이션의모든투플들을조인결과릴레이션에나타내고싶은경우 외부조인을사용함 외부조인에서는상대방릴레이션에대응되는투플이없으면빈애트리뷰트들에 NULL 값을채워서결과에포함시킴 Page 42
외부조인연산 (2/4) 외부조인의종류 왼쪽외부조인 (LEFT OUTER JOIN) R1 R2 는 R1 의모든투플들이결과릴레이션이나타남 오른쪽외부조인 (RIGHT OUTER JOIN) R1 R2 는 R2 의모든투플들이결과릴레이션이나타남 완전외부조인 (FULL OUTER JOIN) R1 R2 는 R1 과 R2 의모든투플들이결과릴레이션이나타남 Page 43
외부조인연산 (3/4) S A 1 2 3 B1 가나다 T B2 C 나 # 다 % 라 $ 마 @ 조인왼쪽외부조인오른쪽외부조인양쪽외부조인 V S B1=B2 T V S B1=B2 T V S B1=B2 T V S B1=B2 T V V V V A B1 B2 C A B1 B2 C A B1 B2 C A B1 B2 C 2 나 나 # 1 가 null null 2 나 나 # 1 가 null null 3 다 다 % 2 나 나 # 3 다 다 % 2 나 나 # 3 다 다 % null null 라 $ 3 다 다 % null null 마 @ null null 라 $ null null 마 @ Page 44
외부조인연산 (4/4) null TEMP EMPLOYEE MGRSSN=SSN DEPARTMENT RESULT Π FNAME, MINIT, LNAME, DNAME (TEMP) null null null null Page 45
외부합집합 (Outer Union) 연산 (1/2) 합집합호환성이없는두릴레이션을합집합 (union) 하는데사용됨 데이터베이스구조와제약조건의정의방식 예제 STUDENT(Name, SSN, Department, Advisor) 와 FACULTY(Name, SSN, Department, Rank) 의 outer union 은 RESULT(Name, SSN, Department, Advisor, Rank) 임 RESULT 에서 STUDENT 투플은 Rank 속성의값이 null 이고, FACULTY 투플은 Advisor 속성의값이 null 임 Page 46
외부합집합 (Outer Union) 연산 (2/2) S 와 T 의외부합집합 S A 1 2 3 B 가나다 T B C D A B C 나 # a 1 가 null 다 % b 2 나 # 라 $ c 3 다 % 마 @ d null 라 $ null 마 @ D null a b c d Page 47