2. 관계형 모델 (Relational Model)

2. 관계형 모델 (Relational Model) 서울시립대학교 전자전기컴퓨터공학부 김한준

데이터 모델 (Data Model) • 정의 • 데이터와 그들간의 관계, 서로의 의미, 제약조건 등을 어떻게 나타낼 것인가에 대한 개념적인(논리적인) 틀. • “real world” (실세계)의 묘사에 유용하게 쓰임 • user, 시스템분석, DBA등의 의사소통에 중요한 도구로 이용되고 있음 • 주의: 구현 모델이 아님 • ER 모델을 기반으로 하는 DBMS는 없음 • Data Model의 종류 • Entity Relationship Model (개체관계형 모델) • Relational Model (관계형 모델) • Object-Oriented Model (객체지향 모델) • 기타 • Hierarchical Model (계층적 모델) • Network Model (네트워크 모델) 실세계를 반영한 데이터모델은 요구사항을 반영한 ‘데이터’와 ‘관계(의미)’를 표현한 것임 실세계(자전거)를 컴퓨터에서 표현하고자 할 때, 요구사항 (DB구축 목적)과 관련된 부분을 identify하는 것이 중요함 * 실세계의 반영 Frame size wheel size gear material . . . 오늘날 거의 사용되지 않는 모델임

Entity-Relationship Model (개체관계형 모델) • 정의 • 모든 것을 Entity (개체)와 그들 간의 relationship (관계)으로만 표현 • Entity: 실세계에 실제로 또는 개념적으로 존재하는 것을 의미하며, 그것의 특성을 설명하는 속성(attribute)들을 가짐 • Relationship: Entity들이 상호 연결된 관계 크기 재질 Wheel ‘Wheel’ 개체와 ‘Frame’개체와의 관계(Relationship)을 ‘connect’로 표현 connect 재질 Frame 크기

Object-Oriented Model (객체지향 모델) • 정의 • 유일한 식별, 내포된 성질, 그리고 다른 객체나 자기 자신과 상호 작용(관계)을 할 수 있는 실세계 개체를 추상적으로 표현 • Objects = Attribute + action(behavior) • Entity와 유사한 개념이나 능동성 (즉, behavior)을 포함 • Attribute: object의 state를 표현 • Action: message 에답하는 procedure • Attribute • 객체의 상태 표현 • 하나의 데이터 타입 • Object의 상태 • 한 시점에서 객체의 attribute값들의 집합 • attribute변경하기 위해서는 그 객체에게 message를 보냄 • Method • attribute값 변경 및 검색 • Object의 behavior표현 • Object의 상태값 접근 • 수신 Object, method 이름, 입력매개변수를 지정 Method Shift size Gears material Method Move Method Update Method Repair

Relational Model (관계형모델) • 정의 • 모든 데이터는 relation(table)에 의해 표현 • 보통 n-ary Relation을의미하며, 이는결국 Set (집합)임 • 예를 들어, 어떤 relation이 학생성명, 과목명, 시험점수 라는 attribute (속성)을 가질 때, 아래와 같은 relation을 만들 수 있음. • {<홍길동, 수학, 98>, <김철수, 수학, 90>, <홍길동, 영어, 88>, <김철수, 영어, 95>} • 여기서, ‘홍길동’은 ‘수학’ 과목에서 98점을 받았는데, 이는 일종의 relation을 가지는 것임 • 데이터중복성 방지를 위해 relation normalization (정규화)을 수행 • 용어 • Relation (relation): table • Tuple (튜플): record, row • Attribute (속성): column

Relational Model (관계형모델) • E. F. Codd, "a Relational Model of Data for Large Shared Data Banks," Communications of the ACM, June 1970, pp.377-387. • Relation이라는 한 개의 구조만을 이용 • Physical data independence • 수학적 이론 기반 • Set oriented model => “A relation is a set.”

Structure of Relational Databases • Relation의 정의 • Domain : set of values • Shoe-Color ={ white, red, blue, green, purple} • Person ={ john, marry, joe, tom } • Relation R: D1, ...,Dn이 domain일 때 R ⊂ D1, X ….. XDn(n: degree of R) R = { <d1, ...,dn> | d1 ∈ D1, ...,dn ∈ Dn} -> “set of tuples” • A tuple in a relation • represents relationship among set of values • 예) Person_shoe ⊆ Person X Shoe-Color Person_shoe = {<john, white>, <marry, red>, <joe, blue>, <tom, white>} • relation은 set이다. • 중복은 의미가 없음 { a, a } = { a } • 원소(tuple)의 순서도 무의미 { a, b } = { b, a }

Structure of Relational Databases • Relational model의 구현 Relation R = { <a1, b1, c1>, <a2, b2, c2>, , <an, bn, cn> } => Table R • col1 col2 col3 <---- column (attribute) name <- row (행, record) *용어 정리 (이론, 설계) (구현) tuple -> row (record) attribute -> column

Structure of Relational Databases • A relational database • a set of relations. • a collection of tables • Tuple variable : • a tuple variable t represents a tuple (row) in a relation (table) t[a1] 의의미: the value of t on attribute a1 • Null value • a special value which signifies that the value is unknown or does not exist => implemented with a special character that cannot appear in regular data (DBMS specific)

Structure of Relational Databases • Relational Scheme 과 relation • relational scheme : • schema of the relation (cf. data type) Customer = (C_name,C_number,C_phone) (set or list of attributes) • relation variable : • a particular relation having the respective relation scheme • cf. data variable customer(Customer) : Customer scheme을 갖는 relation customer

Structure of Relational Databases • relation instance : • value of the relation variable at a particular time (데이타 값들의 상태) • 어느 시점에서의 customer relation의 값 • 일반적으로 relation이라 하면, 특정 relation variable을 의미함.

Relational Database의 설계 이슈 • Normalization (정규화) • First Normal Form (1NF) : 모든 tuple의 모든 값이 atomic(원자)이면, 상태이다. ==> no sets, no lists, no compound structures • 모든 relation은 1NF이라고 가정. • 뒤에 2NF, 3NF, BCNF등의 정규형을 학습할 것임

Keys • Keys: 하나의 entity를 식별할 수 있는 attribute (또는 attribute 집합)를 의미 • 종류 • Superkey (수퍼키) • Relation에서 하나의 tuple를 유일하게 지정할 수 있는 attribute또는 attribute set • 예) 고객 relation의 ‘주민등록번호’, 학생 relation의 {학과, 이름, 학번} • 이론적 표현: Let a subset K of R be a superkey of R => Keys are restrict consideration to relations r(R) such that if t1 and t2 are in r and t1<>t2, then t1[K]<>t2[K] • Candidate key (후보키) • 부분집합을 가지는 않는 superkey를의미 • 예) 고객 relation의 ‘주민등록번호’, 학생 relation의 {학번} • Primary key (주키) • candidate key중 주된 key로 사용되도록 선택된 것(designer가 선택) • Composite key • attribute가 복합되어 이루어진 키 • 예) 고객 relation의 ‘성명’+’주소’ • Foreign Key (외래키) • 필드값이pointer의 역할 • 예) foreign key “교수번호”가 pointer  교수테이블에 “102”가 반드시 존재해야  No dangling reference ! 교수 학생 지도

Relational Algebra • Query language (질의어) • 사용자가 DB상의 정보를 보고자 할 때 사용하는 언어 • 해당 모델에 따라 query language의 성격도 달라짐 • Relational database를 위한 언어 • Relational algebra • Relational calculus • SQL • QUEL

Procedural Query Language • Procedural vs Declarative query • Procedural query language • 일반 C, Java 등의 프로그램과 같은 방식으로 data를 어떤 순서로 어떻게 조작해야 하는가의 「절차적」사항을 명시 • relational algebra • ∏c_name(σb_name= “Perryridge” (borrow▷◁ deposit))

Declarative Query Language • Declarative (Nonprocedural) query language) • Data들의 관계와 조건만을 명시함으로써 무엇을 요구하는가의「선언적」사항을 명시 • Relational calculus, SQL, QUEL, ……… SELECT borrow.c_name FROM borrow, deposit WHERE b_name="Perryridge" AND borrow.c_name = deposit.c_name

Relational Algebra • 예제 scheme • Branch = (b_name, b_city, assets) • Customer = (C_name, C_street, C_city) • Account = (b_name, acc#, balance) • Depositer = (c_name, acc#) • Loan = (b_name, loan#, amount) • Borrower = (c_name, loan#) • Algebra : operators and operands • Relational algebra • operands : relations • operators : fundamental operations + additional operations

Relational Algebra의 기본 연산 • Select • 주어진 조건에 맞는 tuple들만 select • σP(r) : relation r의 tuple중 조건 P를 만족하는 tuple들로 새로운 relation을 만듦. (예) b_name="Perryridge"(loan) (예) σamount>1200(loan) σamount>1200( b_name="Perryridge"(loan)) σamount>1200íⁿb_name="Perryridge"(loan)

Relational Algebra의 기본 연산 • Project • 나열된 attribute만을 취함 • ∏A(r) : relation r의 tuple들에서 A에 나열된 attribute값들만을 취해 새로운 relation을 만듦. (예) ∏branch_city(branch) (예)∏b_name( amount>1800(loan)) (예) ∏amount>1800( b_name(loan)) : error!! • Select: horizontal partition Project: vertical partition

A B C  10 1  20 1  30 1  40 2 A C  1  1  1  2 A C  1  1  2 Projection • relationr : • A, C(r) =

Relational Algebra의 기본 연산 • Cartesian Product • rs = {trts | tr∈r,ts∈ s} • r의 모든 tuple과 s의 모든 tuple들을 concatenate • r x r  x a b 1 2 a b 1 2 c d 3 4 a b 3 4 c d 3 4 c d 3 4 • m개의 record가 있는 relation과 n개의 record가 있는 relation을 cartesian product하면 (mn)개의 record 생성

Relational Algebra의 기본 연산 • Rename • 같은 relation이름이 여러 번 나올 때 ambiguity를 제거하기 위해 사용. (1) ρX(r) : relation r과 똑같은 relation을 X라는 이름으로 만듦. (예) σB.a1=A.a2(A ρB(A)) (2) ρX(A1, ..., An) (r) : relation r과 똑같은 relation을 X라는 이름으로 만들고 그 attribute 이름을 A1, ..., An으로 바꿈. (예) σb1=a2(A ρB(b1,b2)(A))

Relational Algebra의 기본 연산 • Union • 합집합 연산 • r ∪ s = { t | t ∈r ∨ t ∈ s } • r과 s가 union compatible해야 함 • 같은 degree(# of attributes) • r의 i번째 attribute와 s의 i번째 attribute가 같은 domain을 가져야 함.

A B  1  2  1 A B  1  2  1  3 A B  2  3 Union 연산의 예제 • relationr, s • r  s s r

Relational Algebra의 기본 연산 • Set difference (예) ∏c_name(depositer) ─ ∏c_name(borrower) Johnson Turner Lindsay (예) "Find the largest account balance in the bank” Temp ← ∏ account.balance(σaccount.balance<d.balance(account ρd(account))) 자신보다 큰 balance가 존재하는 모든 balance ∏balance(account) ─ Temp

A B  1  2  1 A B  2  3 A B  1  1 Difference 연산의 예제 • relationr, s • r - s s r

Formal Definition of Relational Algebra • Expression in relational algebra 1) A relation is an expression (base) 2) E1∪ E2 3) E1─ E2 4) E1 E2 5) P(E1 ) : P is a formula on attributes in E1 6) S(E1 ) : S is a list of attributes in E1 7) X(E2 ) : X is a new name E2 where, E1 and E2 are relational algebra expressions. Operators : 연산자 Operands : 피연산자 * constant relation : a relation whose instance does not vary in time

A B C D E  1  10 +  2  10 +  2  20 - A B C D E  1  10 +  1  10 +  1  20 -  1  10 -  2  10 +  2  10 +  2  20 -  2  10 - 복합 연산 • 여러 연산을 사용해 표현식을 만들 수 있다. • 예 : A=C(r  s) • r  s • A=C(r  s)

예제 질의 • 1,200불을 초과하는 모든 대출을 찾아라.  amount >1200(loan) • 1,200불을 초과하는 각 대출의 대출 번호를 찾아라. loan-number( amount >1200(loan))

예제 질의 • 은행에 예금, 대출 또는 모두를 가진 고객의 이름을 찾아라. customer-name(borrower)  customer-name(depositor) • 은행에 예금과 대출을 가진 모든 고객의 이름을 찾아라. customer-name(borrower)  customer-name(depositor)

예제 질의 • Perryridge지점에 대출이 있는 모든 고객의 이름을 찾아라. customer-name(branch-name=“Perryridge” (borrower.loan-number=loan.loan-number(borrower  loan))) • Perryridge지점에 대출이 있으면서 은행의 어떤 지점에도 예금이 없는 모든 고객의 이름을 찾아라. customer-name(branch-name=“Perryridge” (borrower.loan-number=loan.loan-number(borrower  loan))) - customer-name(depositor)

예제 질의 • Perryridge지점에 대출이 있는 모든 고객의 이름을 찾아라. - 질의 1 customer-name(branch-name = “Perryridge” (borrower.loan-number = loan.loan-number(borrower  loan))) - 질의 2 customer-name(borrower.loan-number=loan.loan-number( (branch-name = “Perryridge”(borrower))  loan))

예제 질의 • 가장 큰 예금 잔고를 찾아라. • account relation을 d로 재명명한다. balance(account) - account.balance (account.balance < d.balance(account d(account)))

예제 질의 • 적어도 Downtown과 Uptown 지점에 예금을 가진 모든 고객을 찾아라. - 질의 1 CN(BN=“Downtown” (depositor account))  CN(BN=“Uptown” (depositor account)) 여기서 CN은 customer-name을 나타내고 BN은 branch-name을 나타 낸다. - 질의 2 customer-name, branch-name(depositor account)  temp(branch-name)({(“Downtown”),(“Uptown”)})

예제 질의 • Brooklyn에 위치한 모든 지점에 예금을 가진 모든 고객을 찾아라. customer-name,branch-name(depositor account)  branch-name(branch-city=“Brooklyn”(branch))

Additional Operator • Relational Algebra에 어떠한 능력도 보태지는 않지만 질의를 단순화하는 additional operator연산공통 집합 • Natural Join • Division • Assignment

Relational Algebra의 추가 연산 • Intersection r ∩ s = r-(r-s) (예) “customers who have both a loan and an account” ∏c_name(borrower) ∩∏c_name(depositor)

Relational Algebra의 추가 연산 • Natural Join r s : r과 s를 Cartesian product한 후 r과 s에 동시에 있는 attribute의 값이 같은 tuple만 select한 후 중복된 column은 drop(delete)함 (예) loan이 있는 customer들의 이름과 도시 Õb.c_name,c_city (sb.c_name=cust.c_name(borrower  customer)) => Õ c_name,c_city (borrower customer) (예) customers who have a loan and the amount Õ c_name,amount (s b.l#=l.l# (borrower  loan)) => Õ c_name,amount (borrower customer)

A B C D  1  a  2  a  4  b  1  a  2  b B D E 1 a  3 a  1 a  2 b  3 b  A B C D E  1  a   1  a   1  a   1  a   2  b  Natural join 연산의 예제 • relationr, s • r s r s

Relational Algebra • Formally: • Let R, S be two relation schemes such that R∩ S = {A1,...,Ak} (중복되는 column) then r ▷◁ s =∏R∪S(σr.A1=s.A1 ∧... ∧ r.Ak=s.Ak (rs)) (예) “Harrison”에 사는 예금고객을 가진 지점의 이름 ∏b_name,assets (σc_city=“Harrison” (customer ▷◁ account ▷◁ depositor)) (예) “customers who have both a loan and an account” ∏c_name (borrower ▷◁ depositor)

Relational Algebra • ∏A1,…,Ak(r) ∩ ∏A1,…,Ak(s) = ∏A1 ,…,Ak (r s) • (r ▷◁ s) ▷◁ t = r ▷◁ (s ▷◁ t) • If R∩S= then r ▷◁ s = r  s (중요) • If R=S then r ▷◁ s = r ∩ s • Theta Join : combine selection with Cartesian product r ▷◁ s = σ(r  s) where  is a predicate on RS

Relational Algebra의 추가 연산: Division • 표기법 • r  s • “모두에 대한”이라는 구절을 내포한 질의에 적합 • r과 s를 각각이 다음과 같은 스키마 R, S상의 relation이라 하자. - R = (A1, …, Am, B1, …, Bn) - S = (B1, …, Bn) r  s의 결과는 스키마 R-S = (A1, …, Am)상의 relation r  s = {t | t  R-S(r) u  s(tu  r)}

Relational Algebra의 추가 연산: Division • Division (예) “customers who have accounts at all branches in Brooklyn”. r1= ∏b_name (σ b_city=“Brooklyn”(branch)) // all branches in Brooklyn r2 = ∏ c_name, b_name(depositor ▷◁ account) // <customer, branch> tuples r1 = (b_name), r2 = (c_name, b_name) • r2 r1 : r1에 있는 b_name모두와 관계가 있는 c_name들

A B  1  2  3  1  1  1  3  4  6  1  2 B 1 2 A   Division 연산의 예제 • relationr, s : • r  s s r

A B C  a   a .  A B C D E  a  a 1  a  a 1  a  b 1  a  a 1  a  b 3  a  a 1  a  b 1  a  b 1 D E a 1 b 1 Division 나누기 예제 • relationr, s: • r  s s r

Relational Algebra의 추가 연산: Division • Division ∏c_name, b_name(depositor ▷◁ account) ÷∏b_name(σb_city=“Brooklyn” (branch)) • r ÷ s = ∏R-S(r) - ∏R-S((∏R-S(r)ⅹs)-r)

Relational Algebra의 추가 연산 • Assignment 새로운 임시 relation의 사용. (예) r1 ← b_name(b_city=“Brooklyn” (branch)) r2← c_name, b_name(depositor ▷◁ account) result = r2 r1

Extended Relational Algebra: Aggregate functions • Aggregate function은 값의 모임을 취해 하나의 값을 결과로 돌려줌 • avg: 평균값 • min: 최소값 • max: 최대값 • sum: 합계 • count: 값의 개수 • G1, G2, …, GngF1 A1, F2 A2, ..., Fm An(E) • - E는 관계형 대수 표현식이다. - G1, G2, …, Gn은 그룹핑할 애트리뷰트리스트이다. - Fi는 집계 함수이다. - Ai는 애트리뷰트명이다.

A B C   7  .7   3   10 sum-C 27 Extended Relational Algebra: Aggregate functions 예제 • relationr : • sumC(r)

branch-name account-number balance Perryridge A – 102 400 Perryridge A – 201 900 Brighton A – 217 750 Brighton A – 215 750 Redwood A – 222 700 branch-name sum-balance Perryridge 1300 Brighton 1500 Redwood 700 Aggregate functions 예제 • relationaccount를 branch-name으로 그룹핑 • branch-nameg sumbalance(account)

2. 관계형 모델 (Relational Model)