데이터베이스및설계 Chap 4. 관계데이터베이스 (#2/2) 2013.03.20. 오병우 컴퓨터공학과
Integrity rule Introduction To inform the DBMS of certain constraints in the real world, so that it can prevent impossible configuration of values from occurring (ex) (1) supplier numbers must be of the form Snnnn (2) part numbers must be of the form Pnnnn (3) supplier status values must be in the range 1-100 (4) part weights must be greater than zero (5) shipment quantities must be a multiple of 100 Two general integrity rules (apply to every database) Entity integrity rule primary key Referential integrity rule foreign key 무결성, 완전한상태, soundness 제약 배치, 형상 Department of Computer Engineering 2
일반적인의미 자물쇠를푸는열쇠 Key 중요하고핵심적인부분 (key man) Relation 에서의의미 투플을유일하게식별할수있는애트리뷰트집합 애트리뷰트전부를사용해도식별가능하기는함 일반적으로는애트리뷰트의일부를사용함 흔히혼용해서사용 Primary Key 또는 Candidate Key 로혼용하므로문맥의의미를파악하고유의해야함 Department of Computer Engineering 3
후보키 무엇에대한후보? Candidate key 정답 ) Primary Key Attribute K (possible composite) of R: candidate key it satisfies the following two time-independent properties: 릴레이션 R(A 1, A 2,..., A n ) 에대한애트리뷰트집합 K = { A i, A j,..., A k } 로서아래성질을만족하면후보키 1. uniqueness: (s#, CITY) At any given time, no two tuples of R have same value for K 2. minimality: (s#) If K is composite, then no component of K can be eliminated without destroying the uniqueness property 필요없는 attribute 가있으면 candidate key 가아님 (super key 라고부름 ) * every relation has at least one candidate key ( no duplicate tuples) Department of Computer Engineering 4
P 릴레이션에서 P# 가 Primary Key 일경우 Primary key Primary key 후보키 (candidate key) 중에서데이터베이스설계자가지정한하나의키 One (arbitrarily) chosen key from the set of candidate keys Every relation has a primary key Tuple-level addressing mechanism ( some specific tuple) (ex) P(P#, PNAME, COLOR, WEIGHT, CITY) SELECT * SELECT * FROM P WHERE P# = P3 ; FROM P WHERE CITY = Paris ; 임의로 (at most) one tuple unpredictable number of tuples Department of Computer Engineering 5
Alternate key Alternate key A candidate key that is not the primary key Example: S(S#, SNAME, STATUS, CITY) 1. candidate key: S#, SNAME 2. primary key: S# 3. alternate key: SNAME Relational model requires associative addressing 주소가아닌값으로 tuple을 select 가능 All relational addressing clearly is associative (i.e., value-based, not position-based) At logical level no associative hardware in physical level 정리하면 : Candidate Key = Primary Key + Alternate Key 대체 후보 Key 값을주소로사용 Department of Computer Engineering 6
The Entity Integrity Rule Entity integrity rule No component of the primary key of a base relation is allowed to accept nulls Primary key는 null 일수없음 Null (not exist, unknown) ( blank or zero) Information is missing for some reason (e.g., the property does not apply, or the value is unknown, etc.) A value or representation that is understood by convention not to stand for any real value of the applicable attribute 정보부재를명시적으로표현하는특수한데이터값 1 알려지지않은값 (unknown value) 2 해당없음 (inapplicable) Null Tag Value 관습, 관례 Department of Computer Engineering 7
R1 for S# Foreign key Foreign key (no converse: 반대로는안됨 ) An attribute (possibly composite) of one relation R2 whose values are required to match those of the primary key of some relation R1 R1 and R2 not necessarily distinct Attribute FK (possible composite) of base relation R2: foreign key it satisfies the following two time-independent properties: 1. each value of FK is either wholly null or wholly nonnull 2. there exists a base relation R1 with primary key PK such that each nonnull value of FK is identical to the value of PK in some tuple of R1 Referential diagram R2 Referencing relation Referenced relation (target relation) S# P# S(S#, SNAME, STATUS, CITY) SP(S#, P#, QTY) P(P#, PNAME, COLOR, WEIGHT, CITY) R1 for P# Department of Computer Engineering 8
Points Foreign key 1. foreign key and corresponding primary key: the same underlying domain 2. foreign key need not be a component of the primary key in the referencing relation DEPT (DEPT#,, BUDGET, ) : 여기에서는 primary key EMP (EMP#,, DEPT#,, SALARY, ) : 여기에서 DEPT# 는 primary key가아님 3. a given relation can be both a referenced relation and a referencing relation Rn R(n-1) R(n-2) R2 R1 : referential path 4. relations R1 and R2 are not necessarily distinct EMP(EMP#,, SALARY,, MGR_EMP#, ) : MGR_EMP# 는팀장 (6 번참조 ) 5. referential cycle Rn R(n-1) R(n-2) R2 R1 Rn 6. foreign keys sometimes have to accept null (not exist) Value of MGR_EMP# for the president ( 사장은 null) 7. foreign-to-primary-key match Certain relationships between tuples Unknown 이아니라 not exist 확실한, 틀림없는 Department of Computer Engineering 9
The Referential Integrity Rule Referential integrity rule Referential integrity rule The database must not contain any unmatched foreign key value (if B references A, then A must exist) S, P, SP 에서 S 의 S# 변경시에 SP 를어떻게처리? 특정 tuple 의 S# 를 S1 에서 S6 로변경할때처리방법 허락하지않음 S# 에서변경후 SP에서도변경 S# 에서변경후 SP에서대응값은 NULL로처리 S(S#, SNAME, STATUS, CITY) SP(S#, P#, QTY) P(P#, PNAME, COLOR, WEIGHT, CITY) Department of Computer Engineering 10
Foreign Key Rules Three questions for foreign key 1. can that foreign key accept nulls? possible 2. an attempt to delete the target of a foreign key reference 1) RESTRICTED: if no matching referencing tuples (otherwise, rejected) 2) CASCADES : cascades to delete the matching referencing tuples (ex) R3 R2 R1 with CASCADES DELETE on a given tuple of relation R1 3) NULLIFIES : set to null in the foreign keys of the matching referencing tuples 3. an attempt to update the primary key of the target of a foreign key reference 1) RESTRICTED: if no matching referencing tuples (otherwise, rejected) 2) CASCADES : cascades to update the matching referencing tuples 3) NULLIFIES : set to null in the foreign keys of the matching referencing tuples 연쇄, 폭포처럼흐르다 Department of Computer Engineering 11
example 대학 (University) 관계데이터베이스 학생 (STUDENT) 학번 (Sno) 이름 (Sname) 학년 (Year) 학과 (Dept) 100 나수영 4 컴퓨터 200 이찬수 3 전기 300 정기태 1 컴퓨터 400 송병길 4 컴퓨터 500 박종화 2 산공 과목 (COURSE) 과목번호 (Cno) 과목이름 (Cname) 학점 (Credit) 학과 (Dept) C123 프로그래밍 3 컴퓨터 C312 자료구조 3 컴퓨터 C324 화일구조 3 컴퓨터 C413 데이터베이스 3 컴퓨터 E412 반도체 3 전자 담당교수 (PRname) 김성국 황수관 이규찬 이일로 홍봉진 Department of Computer Engineering 12
example 대학 (University) 관계데이터베이스 (cont d) 등록 (ENROL) 학번 (Sno) 100 과목번호 (Cno) C413 성적 (Grade) A 중간성적 (Midterm) 90 기말성적 (Final) 95 100 E412 A 95 95 200 C123 B 85 80 300 C312 A 90 95 300 C324 C 75 75 300 C413 A 95 90 400 C312 A 90 95 400 C324 A 95 90 400 C413 B 80 85 400 E412 C 65 75 500 C312 B 85 80 Department of Computer Engineering 13
R1 R2 인경우 예제 교수 ( 교수번호, 교수이름, 학과번호, 직급 ) 학과 ( 학과번호, 학과이름, 학과장교수번호, 학생수 ) PK FK 학생 ( 학번, 이름, 학년, 학과 ) 과목 ( 과목번호, 과목이름, 학점, 학과, 담당교수 ) 등록 ( 학번, 과목번호, 성적 ) FK FK R1 = R2 인경우 교수1 ( 교수번호, 교수이름, 학과번호, 학장교수번호 ) PK FK Department of Computer Engineering 14
Summary : Integrity Constraint (1) 개체무결성 (entity integrity) Primary key 값은언제어느때고 null 값을가질수없다. Garbage ( 쓰레기 ) 개념과비슷 (2) 참조무결성 (referential integrity) Foreign key 값은참조된 relation 의 primary key 값이거나 null 이다. 데이터베이스상태 (database state) 가항상만족되어야함 DBMS 는데이터베이스상태의변화에도항상무결성제약을만족시키도록해야함 어느한시점에데이터베이스에저장된데이터값 Database instance 데이터베이스상태변화 : insert, delete, update 연산 Dangling Reference 개념과비슷 Department of Computer Engineering 15