- 데이터베이스시스템 제 3 장관계모델 2013. 09. 02 가천대학교 IT 대학 컴퓨터미디어융합학과
목차 3.1 관계자료모델 3.2 무결성제약조건 3.3 관계모델설계 3.4 관계연산 3.5 관계대수 3.6 관계해석
3.1 관계자료모델 관계데이터베이스 IBM 연구소의 Ted Codd가 1970년관계 DB 논문발표. 집합론기반의관계로만든데이터베이스. 제품 : DB2 계열, Oracle, Informix, Sybase, SQL/Server, Paradox 관계자료모델관계를이용하여가상으로데이터베이스를만드는도구. 관계란? Why relation? reflexive, symmetric, transitive,, 3
3.1 관계자료모델 관계모델데이터베이스 : 관계들의집합관계 : 투플 ( 행 row) 들의집합투플 : 속성 ( 열 column) 들의집합 ER 모델행 : 엔티티에해당하는사실을표현열 : 속성들을표현관계스키마 : 관계이름과속성이름들의집합과제약조건. 관계인스턴스 : 테이블 4
3.1 관계자료모델 File, Entity, Table & Relation 급여파일, 급여엔티티, 급여테이블, 급여관계 File name Entity name Table name Relation name CUSTOMER record entity rows tuples Data Fields Entity type Columns Attributes cid name addr credit 1 Kim seoul 1 2 Lee inchon 3 3 Park seoul 1 4 Lee suwon 2 instance 5
3.1 관계자료모델 Relation: a mathematical abstraction for a table. can be viewed as a table of values. Informal Terms Table Column Header All possible Column Values Row Table Definition Populated Table Formal Terms Relation Attribute Domain Tuple Schema of a Relation State of the Relation 6
3.1 관계자료모델 Relational Database Schema: - 관계스키마집합 : S{R 1,R 2,,R n } - 제약조건의집합 CUSTOMER cid name addr credit 1 Kim seoul 1 2 Lee inchon 3 3 Park seoul 1 4 Lee suwon 2 * relational database : a set of relations {r 1, r n } such that - each r i is a relation R i, - r i satisfies the constraints in IC bookstore = {customer, order, order_book, book, 구매, 구성, 출고 } 관계데이터베이스상태관계상태들의집합. 7
3.1 관계자료모델 Domain: 속성에포함될수있는자료값들의범위. a set of atomic values: data type으로명시. Relation Schema R(A 1,,A n ) describes a relation. 관계이름, 관계의차수 ( 속성의수 ), 속성 A i : 열이나역할의이름, domain(a 1 ) = D i : 속성 A i 가가질수있는값들의집합 ex. customer (cid: integer, name: string, address: string) 8
3.1 관계자료모델 관계의특성관계 : n-tuple들의집합관계 r = r(r) = {t 1, t 2,,t n } n-tuple t: n 개값의순서리스트 t = <v 1,v 2,,v n > v i : dom(a i ) 의원소이거나 null 값 (1<= i <= n) t[a i ] : tuple t에서속성 A i 에대응되는 i 번째값관계 : r(r) 은도메인 dom(a 1 ),,dom(a n ) 의카티션곱의부분집합 9
3.1 관계자료모델 Relation schema: R(A 1, A 2 ) dom(a 1 ) = {0, 1}, dom(a 2 ) = {a, b, c} dom(a 1 ) X dom(a 2 ) is all possible combinations: {<0,a>, <0,b>, <0,c>, <1,a>, <1,b>, <1,c> } Relation state: r(r) dom(a 1 ) X dom(a 2 ) example: r(r) could be {<0,a>, <0,b>, <1,c> } three 2-tuples: <0,a>, <0,b>, <1,c> 10
3.1 관계자료모델 Relation schema: customer(cid, name, addr, tel,credit) order(oid, cid, date, rbooks, Qty, amount) order_book(oid, bid, Qty, price, amount) book(bid, title, authors, publisher, price, onhand) Relation schema diagram: CUSTOMER cid cname address tel ORDER oid cid date rbooks Qty credit amount ORDER_BOOKS oid bid price Qty amount BOOK bid title author publisher price onhand 11
3.1 관계자료모델 ER diagram: E-Commerce for Bookstore CUSTOMER BOOK cid 1 name addr bid title 1 authors year onhand n 구매 ORDER 1 n 구성 출고 n ORDER_BOOK 교수 oid cid items qty amount oid bid price qty amount 12
3.2 무결성제약조건 Integrity Constraint IC: conditions that must hold on all valid relation states. - Database 를설계목적대로사용하기위해속성, 투풀, 테이블간에부여하는조건 - 비효율적이거나부정확한정보의입력, 갱신, 삭제방지수단 - 스키마정의시 IC 명시, 집행시제약조건검사. Integrity Types Domain constraints Entity integrity constraints Key constraints Referential integrity constraints Semantic integrity constraints 13
3.2 무결성제약조건 도메인제약조건 - v i belongs to dom(a i ) * part of schema definition - Domain map to standard data types 속성의값들은속성의도메인에속하는원자값이어야 엔티티무결성제약조건 - 기본키는 null 값이아니어야 - 속성은 null 값이아니어야 14
3.2 무결성제약조건 키제약조건 : 개체를고유하게식별할수있는속성후보키 - 두터플은키에속하는필드전체가동일한값을갖지못한다. - 키의부분집합이터플에대한유일한식별자가되지못한다. 기본키 : 후보키중에서대표로선정된키수퍼키 : super key 한관계에서투플을고유하게식별하는속성들의최소집합 15
3.2 무결성제약조건 키제약조건 : 기본키 대체키 후보키 수퍼키 16
3.2 무결성제약조건 키제약조건 : 외부키 : foreign key. 자신또는다른관계의기본키가되는 속성 보조키 : secondary key 중복을허용하는키 ex. 학과, 부서, 취미 CREATE TABLE customer ( EID char(10), 35 이 총무 name char(30), meid char(10), PRIMARY KEY(EID), 36 박 영업 FOREIGN KEY (meid) REFERENCES customer(eid)); eid name 부서 33 김인사 관리자 20 30 36 33 최영업 36 17
3.2 무결성제약조건 Referential integrity constraints 외부키 : 목표 : 두관계의터플들사이에서일관성유지. 다른테이블에서기본키로사용되는속성. * 한속성이수정되면다른테이블의속성도점검해야 ex. 학생이수강신청하려면, 그학생이등록테이블에존재 해야,,, CUSTOMER cid name addr credit ORDER oid cid date items qty amount ORDER_BOOK oid bid qty price amount BOOK bid title authors publisher year onhand 18
3.2 무결성제약조건 Semantic integrity constraints 명시하지않지만지켜야하는제약조건 Ex. 나이 : 생년월일과일치해야졸업일자 : 입학일자보다늦어야, 복학일자 : 휴학일자보다늦어야사원의급여는상사의급여를초과할수없다. 사원의근무시간은주당 58 시간미만이어야 19
3.3 관계모델설계 설계절차 - 요구분석 : 업무현황 + 요구사항업무흐름도 : 업무를수행하기위하여조직 ( 원 ) 간에유통되는정보의흐름을표기한그림. 상자, 파일, 문서, 화살표등으로작성. - 관계데이터베이스스키마 : 관계리스트와제약조건 - ER Diagram : - 관계스키마 : 테이블작성 - 관계스키마 diagram - 무결성제약조건 : 기술 20
3.3 관계모델설계요구분석 : 업무흐름도 ( 전자상거래 ) 업무흐름도 : 조직 ( 원 ) 간의정보의흐름과처리과정을표현한그림. 작성법 : 맨위에조직 ( 원 ) 을그리고위에서아래로정보가이동하고처 리되는순서대로작성. 상자는처리, 종이는정보 ( 서류 ), 화살표는이동, 디스크는파일을의미. 21
3.3 관계모델설계요구분석 : 업무흐름도 ( 전자상거래 회사 ) 업무흐름도 : 22
3.3 관계모델설계요구분석 : 업무흐름도 ( 무역회사 ) 23
3.3 관계모델설계 ER 을관계모델로변환 학생 개체집합을테이블로생성 sid name addr CREATE TABLE student ( sid integer, name char(25), addr char(100), PRIMARY KEY(sid)); sid name addr 0804411 Kim 서울 0804415 Lee 인천 0804419 Park 성남 24
3.3 관계모델설계 ER 을관계모델로변환 학생 수강 과목 관계집합을테이블로 sid when cid CREATE TABLE sukang ( sid integer, cid integer, when char(4), PRIMARY KEY (sid, cid) FOREIGN KEY (sid) REFERENCES 학생 (sid), FOREIGN KEY (cid) REFERENCES 과목 (cid)); sid cid when 0804411 18 08-1 0804411 25 09-2 0804415 20 09-2 0804419 35 07-1 25
3.3 관계모델설계 seid meid 관계집합을테이블로 보고 1 관리 지시 n 직원 CREATE TABLE manage ( manager_eid integer, eid name addr worker_eid integer, PRIMARY KEY (manager_eid, worker_eid ), FOREIGN KEY (manager_eid) REFERENCES 직원 (eid), FOREIGN KEY (worker_eid) REFERENCES 직원 (eid)); 26
3.3 관계모델설계 키제약조건 : 관계집합을테이블로 from eid name addr did title Loc 사원 관리 부서 CREATE TABLE Manager ( eid integer, did integer, from DATE, PRIMARY KEY (did), //(eid,did)?? FOREIGN KEY (eid) REFERENCES Employee(eid), FOREIGN KEY (did) REFERENCES Department(did)); 27
3.3 관계모델설계 키제약조건 : 관계집합을개체집합테이블로통합 CREATE TABLE DeptMgr ( DID integer, title char(30), Loc char(15), meid integer NOT NULL, from DATE, PRIMARY KEY (DID), EID name addr DID title Loc from 사원... 관리 외부키로관계설정 FOREIGN KEY (meid) REFERENCES Employee(EID) ON DELETE NO ACTION); 부서 외부키 meid 28
3.3 관계모델설계 참여제약조건 : 관계집합을개체집합에통합 n from 근무 1 CREATE TABLE Works ( EID integer, DID integer, from DATE, PRIMARY KEY (EID,DID) 사원 1 1 관리 부서 eid name did title FOREIGN KEY (EID) REFERENCES Employee(EID), FOREIGN KEY (DID) REFERENCES DEPARTMENT(DID), from ON DELETE NO ACTION); 29
3.3 관계모델설계 약개체제약조건 : 관계집합을개체집합테이블로통합 eid name address 사원 1 부양관리 cost CREATE TABLE Dependents ( dname char(30), 부양자부서 DOB date, cost REAL, eid dname DOB eid integer, PRIMARY KEY (eid, dname), FOREIGN KEY (eid) REFERENCES Employee(eid), ON DELETE CASCADE); n 30
3.3 관계모델설계 순환관계집합의변환 pid pname price 조선소에서부품을관리하고자한다. 한부품은다른부품의부품이될수있다. 어떻게구현할것인가? Madeup_of Part Role Consist_of CREATE TABLE com_part ( subpart integer, superpart integer, PRIMARY KEY (subpart, superpart), 1 포함 n superpid subpid FOREIGN KEY (subpart) REFERENCES Part(pid), FOREIGN KEY (superpart) REFERENCES Part(pid)); 부품의순환관계를다른방법으로표현하라. 31
3.3 관계모델설계 다대다관계집합의변환본사직원들은부서에소속되어있다. 현장프로젝트는부서에소속되어있다. 직원들은현장을직무별로지원한다. ex. 인사, 장비, 경리, 공정관리, 현장은여러직원들의지원을받는다. 직원과현장과의관계는? 지원관계집합은어떻게구현? n 소속 1 Department did Employee 지원 1 보고 n Project eid from pid 32
3.3 관계모델설계 뷰개요 View: 필요할때계산되는가상의테이블. Create VIEW Bonus (eid, name, base, salary) as select eid, name, base, salary from payroll where performance_rate >80; 추상화 : 자료독립, Base table이바뀌어도보안 : base table은감춘다 * Access 에서는지원하지않는다. 33
3.3 관계모델설계 View: 개념스키마중에서사용자에게접근이허용되는부분 = 외부스키 마 View 갱신 : 필요할때허용. SQL-92: 집단연산을사용하지않는뷰에만갱신허용. 삽입도가능하나, 기본키가 null이면삽입불가. 삭제 : view를통하여삭제가능 CREATE VIEW goodemployee (eid, name, rate) as select eid, name, rate from Employee where rate > 80; 34
3.4 관계연산 Query: 사용자가관계에게정보조작을요청하는문장. 관계연산 : 주어진관계에서새관계를만드는것. 입 출력 : 모두관계관계대수 + 관계해석관계? 연산? 두사물의연결? 테이블조작? 관계질의관계에서원하는인스턴스를추출하는문장. 관계연산을통하여원하는결과를추출하는문장. 35
3.4 관계연산 관계표기관계스키마 R의관계인스턴스 r은 n-tupe의집합. 터플 t는 n개의속성값들의순서리스트. 각 v i (1<=i<=n) 는도메인 Dom(A i ) 의원소. R = {A 1,A 2,, A n } r = {t 1,t 2,, t m } t = {v 1,v 2,, v n } n: 속성의수 m: 터플의수 36
3.4 관계연산 Tuple: 속성값들의순서리스트 : entity, row, record 투플의순서 : 집합이므로순서가없으나, 디스크에는저장순서? 테이블에도행을순서로? 속성의순서 : 집합이므로투플안에서의순서는무의미,,, (< 속성 >,< 값 >) 쌍들의집합 37
3.4 관계연산 연산 : 집합연산, 레코드연산파일연산레코드단위로 read, write, update, seek,,, 디스크에서는레코드단위로,,, 관계연산집합단위로 select, insert, delete, update,, 메모리에서는집합단위로,,, 사용자요구 레코드단위 + 집합연산 38
3.4 관계연산 관계연산 : 관계대수 + 관계해석관계대수 : 관계모델의절차적연산. 기계의내부처리용관계해석 : 관계질의를위한선언적연산. 사람의집합처리용 39
3.4 관계연산 대수? Algebra 代數 procedural mathematics in which letters are used to represent quantities. - 숫자를대표하는일반적인문자를사용하여수를연구 - 처리절차를기술하는연산기법 - 연산자, 피연산자, 연산규칙들의집합체 순서가중요 대수계의실례 A = (C, Ω, E) C = (0,1,2,3) Ω = (+, -,*,/) E(+) = ((X,Y), Z) Z is the sum of X and Y) 40
3.4 관계연산 해석? Calculus 解析 declarative 함수의연속성에관한성질을연구하는학문. 미분, 적분연산결과를정의. 선언적명제해석명제 : 진위를가릴수있는문장기존의명제로새명제의진위를결정술어해석술어 : 변수값에의하여진리값이결정되는문장 술어를이용하여명제의성질과관계를결정. 추론 41
3.4 관계연산 명제와술어실례 - 형수는학생이다. - 창수는학생이다. - 꽃은아름답다. - 열심히공부해라 - 만수는베짱이다. - 영수는프로그래머다. 추론 : 기존의명제로부터새로운지식을얻는논리기법삼단논법 : 두개의전제에서새로운판단을얻는추론기법 - 사람은죽는다. - 철수는사람이다. - 철수는죽는다. 42
3.5 관계대수 관계대수 Relational algebra 관계모델을위한기본적인연산들의집합. 2가지관계모델용형식질의어중하나. - 관계인스턴스를매개변수로받아서관계인스턴스를반환. 관계에서검색할정보를얻는방법을절차 ( 대수 ) 로표현관계대수식 : 관계를단항또는이항대수연산자로정의연산자 : 관계연산자 : select, project, 집합연산자 : union, intersection, difference, cartesian product, join,, 43
3.5 관계대수 관계연산자 : 단항연산자 Deposit Deposit Deposit 1 2 3 4 5 6 7 8 σ select 1 2 3 4 5 6 7 8 π project 1 2 3 4 5 6 7 8 selection & project 44
3.5 관계대수 Select σ : 관계에서투플들을추출하는연산자 Project : 관계에서속성들을추출하는연산자질의 : 성남 에사는학생은? 학생들이거주하는주소는? 성남에사는학생들의이름은? STUDENT sid name addr 0804411 Kim 서울 0804415 Lee 인천 0804419 Park 성남 0704419 Kim 성남 σ sid name addr 0804419 Park 성남 0704419 Kim 성남 addr 서울인천성남 name Park Kim 45
3.5 관계대수 select σ 관계에서행을선택 σ addr = 성남 (student)? project 관계에서열을선택. addr (student)? STUDENT sid name addr 0804411 Kim 서울 0804415 Lee 인천 0804419 Park 성남 0704419 Kim 성남 Select & project name σ addr = 성남 (student) 46
3.5 관계대수 D B? 집합연산자 : 이항연산자 D B D B? 합집합, 교집합, 차집합 D B? Deposit Borrow B D? Deposit D - B 1 2 difference 3 4 Borrow D B intersection 5 6 7 8 5 6 7 8 D U B union 9 10 11 12 47
3.5 관계대수 집합연산자합집합, 교집합, 차집합, 조인, 카티션프로덕트 합집합 저축이나대출한고객의이름? name (deposit) U name (borrow) 단두테이블의속성과순서가동일해야교집합 저축하고대출한고객의이름? name (deposit) name (borrow) deposit cid name amount 110 Kim 100 115 Lee 150 120 Park 200 133 Kim 50 borrow cid name amount 005 Cha 100 077 Yoon 150 110 Kim 500 120 Park 50 48
3.5 관계대수 차집합 - 저축하고대출하지않은고객의이름? name (deposit) - name (borrow) 대출하고저축하지않은고객의이름? name (borrow) - name (deposit) 49
3.5 관계대수 집합연산자 : 이항연산자 Join, Cartesian Product cid oid cid oid 1 3 1 3 2 4 1 4 3 C O (a) cartesian produect 연산 2 2 3 3 CO 3 4 3 4 cid 1 2 C cid 3 4 O cid 3 CO cid 3 3 (b) join 연산 50
3.5 관계대수 Cartesian product: 실례 두테이블의연결 ( 공통속성이없을때 ) Customer cid name addr 005 Cha 서울 077 Yoon 인천 Order oid cid items 33 005 사과 35 005 배 36 077 귤 amount 100 150 500 cid name addr oid cid items amount 005 Cha 서울 33 005 사과 100 005 Cha 서울 35 005 배 150 005 Cha 서울 36 077 귤 500 077 Yoon 인천 33 005 사과 100 077 Yoon 인천 35 005 배 150 077 Yoon 인천 36 077 귤 500 51
3.5 관계대수 Join 두테이블의연결 ( 공통속성기준 ) D.name ((borrow) 실례 D.cid = B.cid (deposit)) 사과 를구매한고객의이름은? name σ O.items = 사과 ((customer) (order)) 성남 에사는고객이주문한상품은? 52
3.5 관계대수 Join Customer cid name addr oid Order cid items amount 005 Cha 서울 33 005 사과 100 077 Yoon 인천 35 005 배 150 110 Kim 성남 36 077 귤 500 120 Park 성남 40 120 감자 250 41 077 귤 100 CustomerOrder 43 120 사과 250 cid name addr oid cid items 005 Cha 서울 33 005 사과 100 005 Cha 서울 35 005 배 150 077 Yoon 인천 077 Yoon 인천 120 Park 성남 36 077 귤 41 077 귤 40 120 감자 amount 500 100 250 120 Park 성남 43 120 사과 250 53
3.5 관계대수 Equi-Join 조인조건이 D.name1 = B.name2 와같이등호로구성되는조 인. 이경우결과에두속성이모두들어간다. select D.CID, D.name from deposit AS D, borrow AS B where D.CID = B.CID ; Natural-Join 동일한이름의모든필드에대해동일성으로연결하는조인. 조건을생략하지만묵시적을공통필드로조인한다. select D.CID, D.name, B.addr from deposit AS D NATURAL JOIN borrow AS B; 54
3.5 관계해석 관계해석 Relational calculus 관계질의를표현하기위한고급의선언적표기법 2 가지관계모델용형식질의어중하나. - 관계인스턴스를매개변수로받아서관계인스턴스를반환. 관계에서검색하여얻을정보를기술비절차언어 : 원하는결과를기술 ( 선 선언적, no 방법론절차언어언 : ) : 관계해석언어 : 비절차적원하는결과와얻는방법기술 ( 절종류 : 기준 = 변수차 ). 투플관계해석 : SQL 55
3.6 관계해석 3.6.1 Tuple relational Calculus { T p(t) } T: tuple variable. 특정관계스키마의투플들을값으로갖는변수 p(t): T를기술하는식질의결과 : p(t) 가참이되는모든투플 t의집합 Query: amount가 1000이넘는주문을찾아라? { T T order and order.amount > 1000} 56
3.6.1 Tuple relational Calculus amount가 1000이넘는주문의 item과 qty는? { P T order(t.amount > 1000 P.item=T.item P.qty=T.qty} {P.item, P.qty T order(t.amount > 1000 )} P는 item과 qty라는두필드만가진투플변수로간주된다. amount가 1000이넘는주문을한고객의이름과주소? { P T order C customer (T.id=C.id T.amount > 1000 P.name=C.name P.addr=C.addr)} {P.name, P.addr O order C customer (O.id=C.id O.amount > 1000 )} 57
3.6 관계해석 3.6.2 Domain relational Calculus { X p(x) } : {<x 1,x 2,,x n > p(<x 1,x 2,,x n >} X: 도메인변수 : 어떤속성의도메인에있는값들을취하는변수 p(x): X를기술하는공식 p(<x 1,x 2,,x n > 에서 x i 는도메인변수이거나상수로, 1<= i <= n 식 p(x) 가참이되는모든도메인 x의집합 Query: 사과 를구매한고객의이름은? name(customer.cid=order.cid and order.items = 사과 ) 58
3.6 관계해석 사과 를구매한고객의이름은? name(customer.cid=order.cid and order.items = 사과 ) SQL: select C.name from customer as C, order as O where C.cid = O.cid and O.items = 사과 select name from customer where cid = (select cid from order where items = 사과 ) 59