함수적 종속과 정규화 함수적 종속 데이터 중복의 문제점 정규형.

Presentation on theme: "함수적 종속과 정규화 함수적 종속 데이터 중복의 문제점 정규형."— Presentation transcript:

1 함수적 종속과 정규화 함수적 종속 데이터 중복의 문제점 정규형

2 함수적 종속(functional dependency)
논리적 설계단계에서 데이터 중복 문제 테이블을 분해함으로써 달성가능 함수적 종속 무결성 제약의 한 종류 테이블 내 필드 간의 관계성을 표현 데이터 중복의 발생 여부를 파악하는데 사용 기본 정의 테이블 R에서 필드 X의 값이 동일한 임의의 레코드에 대해 필드 Y의 값도 동일하다면 “Y는 X에 함수적 종속된다” 라고 하며 다음과 같이 표현 X : 결정자(determinant) Y : 종속자(dependent) “Y는 X에 함수적으로 종속된다.” = “X는 Y를 함수적으로 결정한다” X Y

3 student 테이블의 함수적 종속 name은 stu_id에 함수적으로 종속된다.
resident_id는 stu_id에 함수적으로 종속된다. stu_id는 resident_id에 함수적으로 종속된다. dept_name은 stu_id에 함수적으로 종속된다. dept_name은 resident_id에 함수적으로 종속된다. office는 dept_name에 함수적으로 종속된다.

4 함수 종속의 특징 함수 종속의 관계 필드 집합 간의 함수 종속 다대일 : stu_id → dept_name
일대일 : stu_id → resident_id resident_id → stu_id 필드 집합 간의 함수 종속 결정자와 종속자가 두 개 이상의 필드로 구성 (stu_id, dept_name) → office stu_id → (name, dept_name)

5 학사 데이터베이스의 함수적 종속의 예

6 학사 데이터베이스의 함수적 종속

7 함수적 종속의 특성 포함 규칙 분해 규칙 테이블 R의 필드 집합 X, Y에 대해 X ⊃ Y이면 X → Y가 항상 성립
예) X → X (X, Y) → X 분해 규칙 테이블 R의 필드 집합 X, Y, Z에 대해 X → (Y, Z)이면 X → Y, X → Z가 각각 항 상 성립한다. 예) class 테이블에 대한 함수적 종속 (course_id, year, semester, division) → (prof_id, classroom, enroll) 다음이 만족됨 (course_id, year, semester, division) → prof_id (course_id, year, semester, division) → classroom (course_id, year, semester, division) → enroll 주의) 반대는 성립하지 않음 (A, B) → C가 성립한다고 A → B, B → C가 만족되지는 않음

8 함수적 종속의 특성 합성 규칙 테이블 R의 필드 집합 X, Y, Z에 대해 X → Y, X → Z가 성립하면 X → (Y, Z) 도 성립한다. 예) (course_id, year, semester, division) → prof_id (course_id, year, semester, division) → classroom (course_id, year, semester, division) → enroll 다음이 만족됨 (course_id, year, semester) → (prof_id, classroom, enroll)

9 함수적 종속의 특성 이행 규칙 테이블 R의 필드 집합 X, Y, Z에 대해 X → Y, Y → Z가 성립하면 X → Z도 성립한다. 예) stu_id → dept_name dept_name → office 다음이 만족됨 stu_id → office

10 키와 함수적 종속의 관계 테이블의 모든 필드는 키(수퍼키 포함)에 함수적으로 종속된다.
테이블의 필드 X가 나머지 필드들을 함수적으로 결정하면 필드 X는 수퍼키이다. 예) 테이블 R이 A, B, C로 구성되어 있고 A → (B, C)면 A는 키가 된다. 단, 중복된 레코드가 없다는 관계형 데이터 모델의 규칙을 따라야 함

11 함수적 종속의 유지 방법 해결방법 함수적 종속은 무결성 제약의 일종
테이블에 데이터를 삽입할 때 함수적 종속이 계속 유지되어야 함 RDBMS에서의 함수적 종속 유지 방법 기본키의 경우 함수적 종속이 계속 유지될 수 있음 결정자가 기본키인 함수적 종속들은 자동적으로 만족됨 기본키가 아닌 필드에 대해서는 함수적 종속 유지가 어려움 예) dept_name → office를 항상 만족하기 위한 적합한 방법은 없음 해결방법 데이터베이스를 설계할 때 이러한 문제가 발생하지 않도록 테이블들을 정의 이러한 과정을 정규화(normalization)라고 함

12 정규화 불필요한 데이터의 중복을 피하기 위해 스키마를 분해(decomposition) 하는 과정
함수적 종속이 중요한 역할을 함

13 데이터 중복의 문제점 데이터가 중복 저장하는 경우에는 데이터 변경에 의해 원 하지 않던 결과가 발생할 수 있음 이상현상의 종류
이상현상(anomaly) 이상현상의 종류 삽입이상 삭제이상 수정이상

14 삽입 이상(insertion anomaly)
student 테이블에 새로운 학과 정보(‘930’, ‘물리학과’, ‘303호’)를 입력 할 경우 stu_id는 기본키이므로 null 삽입이 불가능함 임의의 값을 생성해서 넣어야 함 삽입 이상 데이터를 삽입할 수 없거나 원치 않는 데이터를 삽입 stu_id resident_id name year address dept_id dept_name office 900424‐ 김광식 3 서울 920 컴퓨터공학과 201호 900305‐ 김정현 891021‐ 김현정 4 대전 890902‐ 2 대구 923 산업공학과 207호 910715‐ 박광수 광주 921011‐ 김우주 부산 900825‐ 박철수 925 전자공학과 308호 xxxxxx null 930 물리학과 303호

15 삭제 이상(deletion anomaly)
학번이 ‘ ’인 레코드를 삭제할 경우 ‘전자공학과’의 office가 ‘308호’라는 사실도 함께 삭제 이 학생이 ‘전자공학과’의 유일한 학생이라면 문제가 발생 삭제 이상 삭제되지 말아야 할 정보까지 함께 삭제되는 현상 stu_id resident_id name year address dept_id dept_name office 900424‐ 김광식 3 서울 920 컴퓨터공학과 201호 900305‐ 김정현 891021‐ 김현정 4 대전 890902‐ 2 대구 923 산업공학과 207호 910715‐ 박광수 광주 921011‐ 김우주 부산 900825‐ 박철수 925 전자공학과 308호

16 수정 이상(update anomaly) ‘컴퓨터공학과’의 office가 ‘201호’에서 ‘211호’로 변경되는 경우 수정 이상
dept_name이 ‘컴퓨터공학과’인 모든 레코드를 수정해야 함 일부만 변경한다면? 수정 이상 중복된 정보의 일부만 수정하여 정보의 불일치(inconsistency)가 발생하는 현상 stu_id resident_id name year address dept_id dept_name office 900424‐ 김광식 3 서울 920 컴퓨터공학과 201호 900305‐ 김정현 891021‐ 김현정 4 대전 890902‐ 2 대구 923 산업공학과 207호 910715‐ 박광수 광주 921011‐ 김우주 부산 900825‐ 박철수 925 전자공학과 308호

17 해결 방안 student 테이블에서 학과정보를 분리하여 department 테이블 생성
dept_id(학과번호) 필드를 이용하여 외래키로 학과정보 연결 앞서보았던 삽입, 삭제, 수정이상 현상들이 더 이상 발생하지 않음

18 정규화 정규화(normalization)란? 정규화의 효과
불필요한 데이터 중복을 피하기 위해 스키마를 분해 (decomposition) 정규화의 효과 테이블에 대한 삽입, 삭제, 수정 등의 연산으로 인해 발생할 수 있 는 이상현상을 방지하는 수단을 제공 데이터 중복으로 발생하는 문제 해결 단, 함수적 종속이 유지 될 수 있도록 데이터베이스 설계해야 함

19 정규형(normal form) 정규형이란? 정규형의 종류 각 단계별 정규화 과정을 통해 분해된 테이블들
1차 정규형(1NF: first normal form) 2차 정규형(2NF : second normal form) 3차 정규형(3NF : third normal form) 보이스-코드 정규형(BCNF : Boyce-Codd normal form) 4차 정규형(4NF : forth normal form) 5차 정규형(5NF : fifth normal form)

20 정규형의 관계 데이터베이스 설계시 일반적으로 4, 5차 정규형은 고려하지 않음

21 1차 정규형 정의 관계형 데이터 모델의 정의를 따르는 모든 테이블은 1차 정규형 다음의 register 테이블도 1차 정규형
테이블 R에 속한 모든 도메인이 원자값(atomic value)만으로 구성되어 있다면 R은 1차 정규형이다. 관계형 데이터 모델의 정의를 따르는 모든 테이블은 1차 정규형 다음의 register 테이블도 1차 정규형 register 테이블에서의 함수적 종속들 (stu_id, title) → grade stu_id→ dept_name dept_name → office register 테이블

22 1차 정규형의 문제점 삽입 이상 학번이 ' '인 학생이 '전자공학과'에 소속된다는 사실을 삽입 하려면 최소한 그 학생이 하나의 과목을 수강해야만 가능 삭제 이상 stu_id가 ' '인 학생이 '자료구조'를 수강한다는 사실을 삭제하 면 이 학생이 '산업공학과'에 소속된다는 정보까지 동시에 삭제 수정 이상 학번이 ' '인 학생의 소속이 '컴퓨터공학과'에서 '산업공학과' 로 변경하면 모든 해당 레코드를 변경해야 함

23 1차 정규형의 문제점 문제 발생 원인 register 테이블
부분 종속(partial dependency) : 키가 아닌 필드(A)가 키의 일부인 필드(X) 에 종속되는 경우 register 테이블 기본키 : (stu_id, title) dept_name 필드는 stu_id에 함수적 종속 stu_id가 동일한 레코드에 대해 dept_name이 중복해서 나타남

24 2차 정규형 부분 종속의 제거 부분 종속에 해당하는 결정자와 종속자를 별도의 테이블로 분리

25 2차 정규형 부분 종속이 존재하는 경우 부분 종속에 해당하는 결정자와 종속자를 별도의 테이블로 분리 takes 테이블
student 테이블

26 2차 정규형 student와 takes 테이블에는 다음과 같이 함수적 종속이 그대로 보존 됨
stu_id→ dept_name dept_name → office takes 테이블의 함수적 종속 (stu_id, title) → grade

27 2차 정규형 정의 하나의 필드로 키가 되는 경우 모두 2차 정규형에 해당
테이블 R에서 키가 아닌 모든 필드가 키에 함수적으로 종속되며, 키의 부 분집합이 결정자가 되는 부분 종속이 존재하지 않으면 R은 2차 정규형이 다. 하나의 필드로 키가 되는 경우 모두 2차 정규형에 해당 1차 정규형의 문제(삽입/수정/삭제 이상)가 발생하지 않는다.

28 테이블 분해 조건 종속성 보존 분해(dependency-preserving decomposition)
분해되기 전의 함수적 종속들이 분해 후에도 유지되어야 한다.

29 테이블 분해조건 무손실 조인 분해(lossless-join decomposition)
일반적으로 테이블 R이 R1, R2로 분해되었을 때 R1 ⋈ R2 ⊇ R 이 항상 성립 .. 즉, 분해된 테이블들을 자연 조인하면 원래의 테이블과 일치되어야 함 무손실 분해 조건 즉, 분해된 테이블의 공통 필드 집합이 둘 중 하나의 테이블에서 키가 된다면 무 손실 조인 분해가 된다.

30 무손실 조인 분해의 예 공통 필드 : stu_id 기본키

31 손실 조인 분해의 예 공통 필드 : title 기본키 아님

32 2차 정규형의 문제점 삽입 이상 삭제 이상 수정 이상
student 테이블에서 ‘물리학과’의 사무실이 ‘930호’라는 정보를 삽입할 경 우 ‘물리학과’에 소속된 학생이 존재하지 않으면 삽입이 불가능 삭제 이상 학번이 ‘ ’인 학생의 정보를 삭제할 경우 ‘산업공학과’ 정보도 함께 삭제됨 수정 이상 ‘컴퓨터공학과’의 사무실을 변경 할 경우 3 개의 레코드에 대해 모두 변경 해야 함 student 테이블

33 2차 정규형의 문제점 문제 발생의 원인 해결 방안 기본키의 이행 종속 이행 종속에 참여한 필드들을 다른 테이블로 분해
stu_id → dept_name dept_name → office stu_id → office office 필드가 기본키 stu_id에 이행 종속 student 테이블 department 테이블 종속성 보존과 무손실 분해가 모두 만족됨

34 3차 정규형 정의 테이블 R이 2차 정규형이면서 키에 속하지 않은 모든 필드가 기본키에 이 행 종속되지 않는다면 R은 3차 정규형이다. 3차 정규형으로 분해하는 방법

35 3차 정규형의 문제점 takes2 테이블 기본키는 (stu_id, title)라고 가정
부분 종속 및 이행 종속이 존재하지 않음 즉, 3차 정규형을 만족함 기본키는 (stu_id, title)라고 가정 (stu_id, prof_name)은 후보키가 됨 함수적 종속 (stu_id, title) → prof_name prof_name → title 교수는 하나의 과목만을 강의한다고 가정

36 3차 정규형의 문제점 삽입 이상 삭제 이상 수정 이상
‘홍길동’ 교수가 ‘자료구조’를 강의한다는 사실을 저장할 경우 수강생이 없 다면 저장할 수 없다. 삭제 이상 학번이 ‘ ’인 학생이 ‘운영체제’를 수강한다는 사실을 삭제할 경우 ‘장민석’ 교수가 ‘운영체제’를 강의한다는 사실도 함께 삭제된다. 수정 이상 ‘박철재’교수가 강의하는 과목이 ‘운영체제’로 변경되는 경우 2개의 레코 드를 변경해야 한다.

37 3차 정규형의 문제점 문제 발생의 원인 키에 포함되는 필드 집합 A와 키에 포함되지 않은 필드 집합 X에 대하여
X → A라는 함수적 종속이 존재할 경우 데이터 중복 발생 A X 키 : (stu_id, title) 키가 아닌 prof_name과 키에 포함되는 title에 대해 prof_name → title 성립

38 보이스-코드 정규형 정의 보이스-코드 정규형으로 분해하는 방법
테이블 R에 존재하는 모든 함수적 종속에서 결정자가 후보키이면 R은 보 이스-코드 정규형이다. 보이스-코드 정규형으로 분해하는 방법

39 보이스-코드 정규형으로 분해 student_prof 테이블 외래키 student 테이블 prof_title 테이블
기본키는 (stu_id, prof_name) 외래키 student 테이블 prof_title 테이블 기본키는 (prof_name)

40 보이스-코드 정규형의 문제점 함수적 종속이 보존되지 않는다. 분해 전 분해 후
(stu_id, title) → prof_name prof_name → title prof_name → title

41 관계형 데이터베이스 설계의 원칙 무엇을 선택할 것인가? 이상 현상 방지보다 종속성 보존이 더 중요함
함수적 종속성 보존이 가능하다면 보이스-코드 정규형 선택 함수적 종속성이 보존되지 않는다면 3차 정규형 선택 3차 정규형 장점 무손실 조인 분해, 종속성 보존 단점 데이터 중복으로 인한 이상 현상 보이스-코드 정규형 이상 현상 방지 종속성 보존이 안됨

42 정규형을 감안한 논리적 설계 과정