컴퓨터미디어융합학과

2013. 09. 02

가천대학교 IT대학

컴퓨터미디어융합학과

3.3 관계 모델 설계 3.4 관계 연산

3.5 관계 대수 3.6 관계 해석

3

관계 데이터베이스

IBM 연구소의 Ted Codd가 1970년 관계 DB 논문 발표.

집합론 기반의 관계로 만든 데이터베이스.

제품: DB2 계열, Oracle, Informix, Sybase, SQL/Server, Paradox 관계 자료 모델

관계를 이용하여 가상으로 데이터베이스를 만드는 도구.

관계란?

Why relation?

reflexive, symmetric, transitive,,

4

관계 모델

데이터베이스: 관계들의 집합 관계: 투플(행 row)들의 집합

투플: 속성(열 column)들의 집합 ER 모델

행: 엔티티에 해당하는 사실을 표현 열: 속성들을 표현

관계 스키마: 관계 이름과 속성 이름들의 집합과 제약조건.

관계 인스턴스: 테이블

5

File, Entity, Table & Relation

급여 파일, 급여 엔티티, 급여 테이블, 급여 관계

cid name addr credit

CUSTOMER

1 Kim seoul 1

2 Lee inchon 3

3 Park seoul 1

4 Lee suwon 2

Attributes Relation name

tuples

Columns Table name

rows Entity name

instance Data Fields

File name

record

Entity type

entity

6

Relation: a mathematical abstraction for a table.

can be viewed as a table of values.

Informal Terms Formal Terms

Table Relation

Column Header Attribute

All possible Column Values Domain

Row Tuple

Table Definition Schema of a Relation Populated Table State of the Relation

7

Relational Database Schema:

- 관계 스키마 집합 : S{R₁,R₂,…,R_n} - 제약조건의 집합

* relational database : a set of relations {r₁,…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, 구매, 구성,출고}

관계 데이터베이스 상태 관계 상태들의 집합.

1 Kim seoul 1

2 Lee inchon 3 3 Park seoul 1

4 Lee suwon 2

8

Domain: 속성에 포함될 수 있는 자료 값들의 범위.

a set of atomic values: data type으로 명시.

Relation Schema R(A₁,…,A_n) describes a relation.

관계 이름,

관계의 차수(속성의 수),

속성 Ai: 열이나 역할 의 이름,

domain(A₁) = D_i: 속성 Ai가 가질 수 있는 값들의 집합 ex. customer (cid: integer, name: string, address: string)

9

관계의 특성

관계: n-tuple들의 집합

관계 r = r(R) = {t₁, t₂,…,t_n} n-tuple t: n 개 값의 순서 리스트 t = <v₁,v₂,…,v_n>

v_i: dom(A_i)의 원소이거나 null 값 (1<= i <= n) t[A_i] : tuple t에서 속성 A_i에 대응되는 i 번째 값 관계:

r(R)은 도메인 dom(A₁),…,dom(A_n)의 카티션 곱의 부분집합

10

Relation schema:

R(A₁, A₂)

dom(A₁) = {0, 1}, dom(A₂) = {a, b, c}

dom(A₁) X dom(A₂) is all possible combinations:

{<0,a> , <0,b> , <0,c>, <1,a>, <1,b>, <1,c> } Relation state:

r(R) ⊂ dom(A₁) X dom(A₂)

example: r(R) could be {<0,a> , <0,b> , <1,c> } three 2-tuples: <0,a> , <0,b> , <1,c>

11

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:

cid cname address tel CUSTOMER

oid date rbooks Qty

ORDER

cid amount

oid bid price Qty ORDER_BOOKS

amount

title price

bid author

BOOK

publisher onhand credit

12

ER diagram: E-Commerce for Bookstore

ORDER 구성구성 교 수

oid amount oid price

ORDER_BOOK

1 n

CUSTOMER

구매

cid name

1

n

cid

addr

items qty bid qty amount

BOOK

bid title 1

onhand 출고

n

authors year

13

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

14

도메인 제약조건

- v_i belongs to dom(A_i)

* part of schema definition

- Domain map to standard data types

속성의 값들은 속성의 도메인에 속하는 원자값이어야

엔티티 무결성 제약조건

- 기본 키는 null 값이 아니어야 - 속성은 null 값이 아니어야

15

키 제약조건:

개체를 고유하게 식별할 수 있는 속성 후보 키

- 두 터플은 키에 속하는 필드 전체가 동일한 값을 갖지 못한 다.

- 키의 부분집합이 터플에 대한 유일한 식별자가 되지 못한 다.

기본 키: 후보 키 중에서 대표로 선정된 키 수퍼 키: super key

한 관계에서 투플을 고유하게 식별하는 속성들의 최소 집합 대체 키: 기본키가 아닌 후보키

16

키 제약조건:

수퍼 키 후보 키 대체 키기본 키

17

키 제약조건:

외부 키: foreign key. 자신 또는 다른 관계의 기본키가 되는 속성

보조 키: secondary key 중복을 허용하는 키 ex. 학과, 부서, 취미

CREATE TABLE customer ( EID char(10),

name char(30), …

mEID char(10),

PRIMARY KEY(EID),

FOREIGN KEY (mEID) REFERENCES customer(EID));

eid 부서

33 인사

36 영업

35 총무

name 김

박 이

관리자 20

36 30

33 최 영업 36

18

목 표:

두 관계의 터플들 사이에서 일관성 유지.

다른 테이블에서 기본키로 사용되는 속성.

* 한 속성이 수정되면 다른 테이블의 속성도 점검해야…

ex. 학생이 수강 신청하려면, 그 학생이 등록 테이블에 존재 해야,,,

cid name

cid

addr

oid date items qty

bid

amount

oid qty price amount

authors

bid title publisher year onhand credit

CUSTOMER

ORDER

ORDER_BOOK

BOOK

19

Semantic integrity constraints

명시하지 않지만 지켜야 하는 제약조건 Ex. 나이: 생년월일과 일치해야

졸업일자: 입학일자보다 늦어야, 복학일자: 휴학일자보다 늦어야

사원의 급여는 상사의 급여를 초과 할 수 없다.

사원의 근무 시간은 주당 58 시간 미만이어야

20

설계 절차

- 요구분석: 업무현황 + 요구사항

업무 흐름도: 업무를 수행하기 위하여 조직(원)간에 유통되 는 정보의 흐름을 표기한 그림. 상자, 파일, 문서, 화살표 등으로 작성.

- 관계 데이터베이스 스키마 : 관계 리스트와 제약조건 - ER Diagram :

- 관계 스키마 : 테이블 작성 - 관계 스키마 diagram

- 무결성 제약조건 : 기술

21

업무흐름도: 조직(원) 간의 정보의 흐름과 처리과정을 표현한 그림.

작성법: 맨 위에 조직(원)을 그리고 위에서 아래로 정보가 이동하고 처 리되는 순서대로 작성. 상자는 처리, 종이는 정보(서류), 화살표는 이 동, 디스크는 파일을 의미.

22

업무흐름도:

23

24

ER을 관계모델로 변환

개체집합을 테이블로 생성

CREATE TABLE student ( sid integer,

name char(25), addr char(100),

PRIMARY KEY(sid));

sid name addr

sid name addr 0804411 Kim 서울 0804415 Lee 인천 0804419 Park 성남

25

ER을 관계모델로 변환

관계집합을 테이블로

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 when cid

sid cid when

0804411 18 08-1

0804415 20 09-2 0804411 25 09-2

0804419 35 07-1

26

관계집합을 테이블로

CREATE TABLE manage ( manager_eid integer, worker_eid integer,

PRIMARY KEY (manager_eid, worker_eid ),

FOREIGN KEY (manager_eid) REFERENCES 직원(eid), FOREIGN KEY (worker_eid) REFERENCES 직원(eid));

직 원

eid name addr

보고 1

지시 관리 n

27

키 제약조건 : 관계집합을 테이블로

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));

사원 관리 부서

eid name addr from did title Loc

사원 관리

EID name addr DID title Loc from

외부 키로 관계 설정

mEID 외부 키

...

부서

28

CREATE TABLE DeptMgr ( DID integer,

title char(30), Loc char(15),

mEID integer NOT NULL, from DATE,

PRIMARY KEY (DID),

FOREIGN KEY (mEID) REFERENCES Employee(EID) ON DELETE NO ACTION);

사원

1

부서

관리 근무

1 n

1

eid name did title

from

29

참여 제약조건 : 관계집합을 개체집합에 통합

CREATE TABLE Works ( EID integer,

DID integer, from DATE,

PRIMARY KEY (EID,DID)

FOREIGN KEY (EID) REFERENCES Employee(EID),

FOREIGN KEY (DID) REFERENCES DEPARTMENT(DID), ON DELETE NO ACTION);

30

약개체 제약조건:

관계집합을 개체집합 테이블로 통합

CREATE TABLE Dependents (

dName char(30),

DOB date, cost REAL,

eid integer,

PRIMARY KEY (eid, dName),

FOREIGN KEY (eid) REFERENCES Employee(eid), ON DELETE CASCADE);

관리

부서 사 원

1

n

부양자

부양

DOB dName

cost

eid

31

순환 관계집합의 변환

조선소에서 부품을 관리하고자 한다.

한 부품은 다른 부품의 부품이 될 수 있다.

어떻게 구현할 것인가?

CREATE TABLE com_part ( subpart integer, superpart integer,

PRIMARY KEY (subpart, superpart),

FOREIGN KEY (subpart) REFERENCES Part(pid),

FOREIGN KEY (superpart) REFERENCES Part(pid));

부품의 순환 관계를 다른 방법으로 표현하라.

Part

n

1 포함

Madeup_of Consist_of pid pName price

Role

subpid superpid

eid pid

Department 소속

did 1

n

Employee Project

보고

지원

1

n

from 32

다대다 관계집합의 변환

본사 직원들은 부서에 소속되어 있다.

현장 프로젝트는 부서에 소속되어 있다.

직원들은 현장을 직무별로 지원한다.

ex. 인사, 장비, 경리, 공정관리,

현장은 여러 직원들의 지원을 받는다.

직원과 현장과의 관계는?

지원 관계집합은 어떻게 구현?

33

뷰 개요

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 에서는 지원하지 않는다.

34

View:

개념 스키마 중에서 사용자에게 접근이 허용되는 부분 = ^{외부 스키}

마

View 갱신: 필요할 때 허용.

SQL-92: 집단 연산을 사용하지 않는 뷰에만 갱신 허용.

삽입도 가능하나, 기본 키가 null이면 삽입 불가.

삭제: view를 통하여 삭제 가능

CREATE VIEW goodEmployee (eid, name, rate) as select eid, name, rate

from Employee where rate > 80;

35

Query: 사용자가 관계에게 정보 조작을 요청하는 문장.

관계 연산:

주어진 관계에서 새 관계를 만드는 것. 입•출력 : 모두 관계 관계 대수 + 관계 해석

관계? 연산?

두 사물의 연결? 테이블 조작?

관계 질의

관계에서 원하는 인스턴스를 추출하는 문장.

관계 연산을 통하여 원하는 결과를 추출하는 문장.

36

관계 표기

관계 스키마 R의 관계 인스턴스 r은 n-tupe의 집합.

터플 t는 n개의 속성 값들의 순서 리스트.

각 v_i(1<=i<=n)는 도메인 Dom(A_i)의 원소.

R = {A₁,A₂, … , A_n} n: 속성의 수 r = {t₁,t₂, … , t_m} m: 터플의 수 t = {v₁,v₂, … , v_n}

37

Tuple:

속성 값들의 순서 리스트 : entity, row, record 투플의 순서:

집합이므로 순서가 없으나, 디스크에는 저장 순서?

테이블에도 행을 순서로?

속성의 순서:

집합이므로 투플 안에서의 순서는 무의미,,, (<속성>,<값>) 쌍들의 집합

38

연산: 집합 연산, 레코드 연산 파일 연산

레코드 단위로 read, write, update, seek,,, 디스크에서는 레코드 단위로,,,

관계 연산

집합 단위로 select, insert, delete, update,, 메모리에서는 집합 단위로,,,

사용자 요구

레코드 단위 + 집합 연산

39

관계 연산: 관계 대수 + 관계 해석

관계 대수: 관계 모델의 절차적 연산. 기계의 내부 처리용

관계 해석: 관계 질의를 위한 선언적 연산. 사람의 집합 처리용

40

대수? 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)

41

해석? Calculus 解析 declarative

함수의 연속성에 관한 성질을 연구하는 학문.  미분,적분 연산 결과를 정의.  선언적

명제 해석

명제: 진위를 가릴 수 있는 문장

기존의 명제로 새 명제의 진위를 결정 술어 해석

술어: 변수 값에 의하여 진리 값이 결정되는 문장

술어를 이용하여 명제의 성질과 관계를 결정.  추론

42

명제와 술어 실례

- 형수는 학생이다. - 창수는 학생이다.

- 꽃은 아름답다. - 열심히 공부해라

- 만수는 베짱이다. - 영수는 프로그래머다.

추론: 기존의 명제로부터 새로운 지식을 얻는 논리기법 삼단논법: 두 개의 전제에서 새로운 판단을 얻는 추론기법 - 사람은 죽는다.

- 철수는 사람이다.

- 철수는 죽는다.

43

관계 대수 Relational algebra

관계 모델을 위한 기본적인 연산들의 집합.

2가지 관계 모델용 형식 질의어 중 하나.

- 관계 인스턴스를 매개변수로 받아서 관계 인스턴스를 반 환.

관계에서 검색할 정보를 얻는 방법을 절차(대수)로 표현 관계 대수식: 관계를 단항 또는 이항 대수 연산자로 정의 연산자:

관계 연산자: select, project, 집합 연산자: union, intersection, difference, cartesian product, join,,

피연산자: 관계 연산 규칙: 집합론

44

관계 연산자: 단항 연산자

1 2

5 6 7 8 3 4

1 2

5 6 7 8 3 4

Deposit

σ

select π

project

1 2

5 6 7 8 3 4

selection &

project

Deposit Deposit

45

Select

σ

학생들이 거주하는 주소는?

성남에 사는 학생들의 이름은?

STUDENT

sid name addr 0804411 Kim 서울 0804415 Lee 인천 0804419 Park 성남 0704419 Kim 성남

sid name addr 0804419 Park 성남 0704419 Kim 성남

addr 서울 인천

∏ 성남

σ

name Park

Kim

46

select σ STUDENT 관계에서 행을 선택

σ addr = ‘성남’ (student)  ? project _∏

관계에서 열을 선택.

∏ _addr (student)  ? Select & project

_{∏ name} σ addr = ‘성남’ (student)

sid name addr 0804411 Kim 서울 0804415 Lee 인천 0804419 Park 성남 0704419 Kim 성남

47

집합 연산자: 이항 연산자 합집합, 교집합, 차집합

1 2

5 6 7 8 3 4

9 10 11 12 5 6 7 8

Borrow Deposit

D ∩ B D - B

D U B union intersection

difference

Deposit Borrow B – D ? D – B ?

48

집합 연산자

합집합, 교집합, 차집합, 조인, 카티션 프로덕트 합집합 ∪

저축이나 대출한 고객의 이름?

∏ _name (deposit) U ∏ _name (borrow)

단 두 테이블의 속성과 순서가 동일해야 교집합 ∩

저축하고 대출한 고객의 이름?

_{∏ name} (deposit) ∩ _{∏ name} (borrow)

cid name amount

110 Kim 100

115 Lee 150

120 Park 200

133 Kim 50

deposit

cid name amount

005 Cha 100

110 Kim 500

120 Park 50

077 Yoon 150

borrow

49

차집합 -

저축하고 대출하지 않은 고객의 이름?

∏ _name (deposit) - ∏ _name (borrow) 대출하고 저축하지 않은 고객의 이름?

_{∏ name} (borrow) - _{∏ name} (deposit)

50

집합 연산자: 이항 연산자 Join, Cartesian Product

O C

3 1

2 3

3 4

1

2

3

3 4 1

2

3

3 4

3 4

O C

CO CO

3

(a) cartesian produect 연산

(b) join 연산

1 2 3

3 4

cid cid

cid oid cid oid

cid cid

51

Cartesian product: ×

실례

두 테이블의 연결

(공통 속성이 없을 때)

cid name addr

005 Cha 서울

077 Yoon 인천

Customer

oid items

33 사과

36 귤

35 배

cid 005

077 005

Order

amount 100

500 150

cid name addr

005 Cha 서울

077 Yoon 인천

oid items

33 사과

36 귤

35 배

cid 005

077 005

amount 100

500 150

077 Yoon 인천

077 Yoon 인천

005 Cha 서울

005 Cha 서울

33 사과

36 귤

35 배

005

077 005

100

500 150

52

Join

두 테이블의 연결(공통 속성 기준)

∏_D.name ((borrow) D.cid = B.cid (deposit)) 실례

“사과”를 구매한 고객의 이름은?

∏ _nameσ O.items = ‘사과’ ((customer) (order)) “성남”에 사는 고객이 주문한 상품은?

53

Join



cid name addr

005 Cha 서울

110 Kim 성남

120 Park 성남

077 Yoon 인천

Customer

oid items

33 사과

36 귤

40 감자

35 배

cid 005

077 120 005

Order

41 귤

43 사과

077 120

amount 100

500 250 150

100 250

005 Cha 서울 33 005 사과 100

005 Cha 서울 35 005 배 150

077 Yoon 인천

077 Yoon 인천

36 귤

40 감자

077

120

41 077 귤

500

250 100

120 Park 성남

120 Park 성남 43 120 사과 250

cid name addr oid cid items amount

CustomerOrder

54

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;

55

관계 해석 Relational calculus

관계 질의를 표현하기 위한 고급의 선언적 표기법 2가지 관계 모델용 형식 질의어 중 하나.

- 관계 인스턴스를 매개변수로 받아서 관계 인스턴스를 반 환.

관계에서 검색하여 얻을 정보를 기술  선언적, no 방법론

관계 해석 언어: 비절차적 종류: 기준 = 변수

투플 관계해석: SQL

도메인 관계해석 Q l

비절차언어 :

원하는 결과를 기술(선 언) :

절차언어:

원하는 결과와 얻는 방법 기술(절 차).

56

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}

57

{ 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 )}

58

3.6.2 Domain relational Calculus

{ X | p(X) } : {<x₁,x₂,…,x_n> | p(<x₁,x₂,…,x_n>}

X:도메인 변수: 어떤 속성의 도메인에 있는 값들을 취하는 변 수

p(X): X를 기술하는 공식

p(<x₁,x₂,…,x_n>에서 x_i는 도메인 변수이거나 상수로, 1<= i <=

n

식 p(X)가 참이 되는 모든 도메인 x의 집합 Query:

“사과”를 구매한 고객의 이름은?

name(customer.cid=order.cid and order.items = “사과”)

59

“사과”를 구매한 고객의 이름은?

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 = ‘사과’)