데이터베이스 - 데이터베이스 정규화

수업/데이터베이스

데이터베이스 - 데이터베이스 정규화

MinDDokDDok 2022. 11. 27. 14:28

잘못 설계된 데이터베이스?

같은 학번이 나타날 때 마다, 학년 애트리뷰트 값도 똑같이 중복적으로 나타남

이상현상?

- 삭제이상

--> 학번 3654 고영준 학생이 과목 'CO234'의 등록을 취소할 때

--> 4학년이라는 정보도 함께 삭제됨

--> 연쇄 삭제에 의해 정보가 영원히 손실되는 현상

- 삽입이상

--> 학번이 7654이고 학년이 3인 학생을 삽입할 때

--> 어떤 과목을 등록하지 않는 한 삽입이 불가능(학번, 과목코드가 기본 키)

--> 과목 정보가 없는데도 불구하고 입력할 수 없는 이상 현상이 발생

- 갱신이상

--> 학번 4652인 학생의 학년을 4에서 3으로 변경하려고 할 때

--> 학번 4652에 대한 4개의 튜플 모두를 변경해야 함

--> 중복 데이터의 일부만 갱신으로 정보의 모순성이 발생

이상현상의 원인과 해결책

--> 이러한 현상이 발생되는 이유?

--> 여러가지 상이한 정보를 가지고 있는 애트리뷰트들을 무리하게 하나의 릴레이션으로 표현하였기 때문

문제해결방법

--> 애트리뷰트들 간의 종속성을 분석해서 하나의 릴레이션에는 하나의 종속성만 표현되도록 테이블을 분해하는 것

--> 학번에 대해 성명과 학년이 종속관계를 나타내고, 학번과 과목코드에 대해 성적이 종속관계를 나타내고 있다

--> 이러한 종속관계에 따라 두 개의 릴레이션으로 분해한다면 그 결과는 다음과 같다

이러한 두 개 이상의 릴레이션으로 분해하는 과정을 정규화(normalization)라고 한다

함수 종속성?

함수 종속의 정의

--> 학생(학번, 성명, 학과, 전화번호)에서 성명, 학과, 전화번호는 학번에 각각 함수 종속(FD: Functional Dependency)임

--> 왜냐하면, 학번이 정해지면 이에 대응하는 성명, 학과, 전화번호의 값은 오직 하나만 있기 때문

--> 기호로 표시 : 학번 -> (성명, 학과, 전화번호)

--> 그니까 철수의 학번이 하나 그냥 검색되면 철수라는 이름, 철수 학과, 철수의 전화번호가 학생 테이블 전체 나온다는 거 --> 이름, 학과, 전화번호가 학번에 의해 검색됨 --> 함수종속 --> 여기서 중요한건 값이 오직 하나만 연관되어 있어야

즉, 함수종속이란?

어떤 릴레이션 R에서 애트리뷰트 A의 값 각각에 대해 항상 애트리뷰트 B의 값이 오직 하나만 연관되어 있다면, B는 A에 함수종속된다고 하고 A -> B로 표기한다

* 함수 종속 다이어그램?

--> 한 릴레이션에서 애트리뷰트들 간의 함수 종속 관계를 쉽게 이해하기 위해 도식으로 표현한 것

완전 함수 종속과 부분 함수 종속

등록 릴레이션의 함수 종속 관계는 세 개가 존재

{ 학번, 과목코드 } -> 성적

학번 -> 성명

학번 -> 학년

즉, 성적은 학번, 과목코드에 함수 종속이 되고, 성명과 학년은 학번에 함수 종속이 됨

--> 1. 성적은 { 학번, 과목코드 }에 완전함수종속 되었다고 하고,

--> 2. 성명과 학년은 학번에 완전함수종속됨

--> 3. 그러나 성명과 학년은 { 학번, 과목코드 }에 부분함수종속 되었다고 함

기본키 : { 학번, 과목코드 }

--> 학번과 과목코드의 조합으로 학생이 등록한 과목의 성적을 식별할 수 있음

--> 한 학생은 한 사람의 지도교수를 가질 수 있고, 한 학과에만 속함

--> 각 지도교수도 한 학과에만 속함

여기서 제 1 정규형을 해야한다

제 1 정규형?

릴레이션 R에 속한 모든 도메인이 원자 값만으로 되어 있다면 제 1정규형(1NF)이라 함

함수 종속 다이어그램으로 표현?

등록(학번, 성명, 학과, 지도교수, 과목코드, 성적)

기본키 : { 학번, 과목코드 }

함수 종속 : { 학번, 과목코드 } -> 성적

학번 -> 지도교수

학번 -> 성명

학번 -> 학과

지도교수 -> 학과

등록 릴레이션에서의 이상 현상

1. 삽입이상

: 학번 7654 학생의 지도교수로 "김용주"라는 사실을 삽입하려 할 때 어떤 과목을 등록하지 않는 한 삽입 불가(기본키인 과목코드가 NULL이기에)

2. 삭제이상

: 학번 3654인 학생이 "CO234"라는 과목을 취소할 때, 이 튜플이 삭제되는 경우 지도교수 "김재현" 정보까지 삭제됨

3. 갱신이상

: 학번 4653인 학생의 지도교수를 "이우정"에서 "고현주"로 변경할 때, 학번이 4653인 4개의 튜플 모두 변경하지 않고 일부만 변경될 때 문제 발생

1NF(제 1 정규형) 이상 현상의 원인?

--> 키가 아닌 애트리뷰트들이 기본키에 완전함수종속되지 못하고 부분함수종속이 되기 댸문

1NF 이상의 해결

--> 두 개의 릴레이션으로 분할하여 부분 함수 종속을 제거

--> 2NF

제 2 정규형에서의 이상 현상

1. 삽입 이상

: 지도교수 "강성화"가 컴퓨터과에 속한다는 사실을 삽입하고 싶지만 지도받는 학생이 NULL이므로 삽입할 수 없다

2. 삭제 이상

: 학번 2587인 학생이 지도교수 "김용주"와 지도교수 관계를 취소할 때, 삭제할 때 지도교수가 속한 학과 정보까지도 삭제됨

3. 갱신 이상

: 지도교수 "이우정"의 소속을 컴퓨터과에서 전좌가로 변경한다면 학번이 1234와 4653인 두 개의 튜플을 모두 변경하여야 함, 그렇지 않으면 데이터 불일치 모순이 발생

2NF 이상 현상의 원인

--> 두 개의 상이한 정보를 하나의 릴레이션으로 혼합해서 표현하려고 하는 데서 옴, 학과 애트리뷰트는 사실상 지도교수의 학과를 기술하고 있는 것

--> A가 기본키라면 A -> B 와 A -> C가 존재함, 그런데 C가 A이외에 B에도 함수 종속한다면 C는 A에서 B를 거쳐서 이행적으로도 종속한다. 따라서 애트리뷰트 C는 A에 이행적 함수 종속이라고 한다

--> 즉, 지도 릴레이션에서 학번(A)이 기본키인데 이 학번이 지도교수(B)와 학과(C)를 결정, 근데 학과(C)가 학번(A)이외에 지도교수(B)에도 함수종속 하므 즉 학과는 학번과 지도교수를 거쳐 이행적으로도 종속한다는 것, 이를 이행적 함수종속이라 한다!

2NF 이상 현상의 해결

--> 이러한 이행적 함수 종속을 제거해 두 개의 릴레이션으로 분해 --> 3NF로

--> 즉, 학번, 성명, 학과, 지도교수를 한번에 나타내는 지도 릴레이션을 분해하는 것

지도 릴레이션

--> 학생지도와 교수학과로 분해

학생지도(학번, 성명, 지도교수)

기본키 : { 학번 }

외래키 : { 지도교수 } 참조 : 교수학과

교수학과(지도교수, 학과)

기본키: { 지도교수 }

보이스/코드(Boyce/Codd) 정규형

강의과목(학번, 과목, 교수) : 3NF이지만 문제점이 있는 예

제약조건(가정)

--> 한 학생은 여러 과목을 수강할 수 있다

--> 한 교수는 한 과목만 강의할 수 있다

--> 한 과목은 여러 교수가 강의할 수 있다

기본키: { 학번, 과목 }

함수종속 : { 학번, 과목} -> 교수

교수 -> 과목

이상현상?

1. 삽입이상

: 교수 "김정희"가 "자료구조"과목을 강의하게 되었을 때 삽입 불가(기본키인 학번이 NULL)

2. 삭제이상

: 학번 1234인 학생이 "지료구조"를 취소할 때, "이우정"이 자료구조를 맡고 있는 정보까지도 삭제

3. 갱신이상

: 교수 "김용주"의 담당과목이 '데이터베이스'에서 '웹프로그래밍'으로 변경되었을 떄, 모든 튜플에 대해 변경되어야 한다

--> 원인?

--> 키가 아닌 애트리뷰트가 다른 애트리뷰트를 결정하기 때문이다,

--> 즉, 교수 애트리뷰트는 후보키가 아닌데 과목을 결정하는 결정자이다

BCNF

--> 모든 결정자가 후보키가 되도록 분할해야

--> 먼저 학번과 교수를 분리시키고, 교수가 키가 되도록 교수와 과목을 분리한다. 여기서 강의교수(학번, 교수)의 기본키는 { 학번, 교수 }이고 외래키인 { 교수 }는 교수과목 릴레이션의 교수를 참조한다. 그리고 교수과목(교수, 과목)의 기본키는 { 교수 }이다

강의교수(학번, 교수)

기본키 : { 학번, 교수 }

외래키 : { 교수 } 참조 : 교수과목

교수과목(교수, 과목)

기본키 : { 교수 }

제 4 정규형

각 과목은 명시된 교수들 중에서 어떤 교수라도 가르칠 수 있으나, 교재는 기술된 것 전부 사용해야만 한다

위 비정규 과목 리스트 릴레이션을 동등한 정규 릴레이션으로 변환시키면 아래와 같다

과목리스트 릴레이션은 모든 애트리뷰트가 키가 되므로 BCNF가 된다

--> 그러나 이 릴레이션은 사실상 교수와 교재가 서로 무관한 것을 한 릴레이션으로 표현하였다

BCNF에서의 이상현상

--> 과목리스트 릴레이션에서의 갱신이상

--> 교수 "고현주"가 '자료구조'를 강의한다는 정보를 삽입하려면 3개의 자료구조 교재(즉, 자료구조기초, 자료구조실습, 그래프원리)에 대해 각각 튜플 하나씩 3개의 튜플을 삽입해야 함 --> 상당한 데이터 중복 발생

--> BCNF이상 현상의 원인?

--> 과목은 교수나 교재의 값 하나를 결정하는 것이 아니라 몇 개의 값, 즉 값의 집합을 결정하기 때문이다. 이러한 종속을 다치 종속이라 하고 '-->>' 로 표기한다

과목 -->> 교수

과목 -->> 교재

혹은 한꺼번에 표현하려면

과목 -->> 교수|교재

ex. 데이터베이스 -> { 김용주, 박정미 }

데이터베이스 -> { DB이론, MySQL 실습 }

제 4 정규형?

다치종속(MVD, Multivalued Dependency)

--> 릴레이션 R(A, B, C)에서 어떤 { A, C }값에 대응되는 B의 집합이 A값에만 종속되고 C값에는 독립적이면, "B는 A에 다치종속된다" 라고 하고 A -->> B로 표현한다

(= A multi-determines B, A double arrow B, B is multi-dependent on A)

A -->> B 이면 A -->> C 도 성립한다

--> 과목리스트 릴레잇녀에서 과목 -->> 교수가 성립하기 때문에 과목 -->> 교재가 성립된다

--> 이렇게 MVD는 항상 쌍으로 존재하므로 A -->> B|C로 한꺼번에 표현할 수 있다

제 5 정규형

지금까지 문제가 발생하는 릴레이션은 두 개의 릴레이션으로 분해

--> 그러나 어떤 릴레이션은 2개가 아니라 3개 또는 그 이상의 릴레이션으로 분해(즉, n-분해 릴레이션) 해야 하는 경우

납품관리 릴레이션

--> 어떤 공급자가 어떤 부품을 어떤 작업장에 공급하고 있다는 것을 표현

조인종속(JD, Join Dependency)

--> 어떤 릴레이션 R에 대해 프로젝트한 n개의 부분집합 (A, B, ,,,, Z)이 있다고 하자. 이때 만일 이 릴레이션 R이 그의 (A. B, ,,, Z)를 모두 조인한 결과와 똑같이 된다면 R은 조인종속을(JD)*(A, B, ,,,, Z) 만족시킨다고 한다

--> 납품관리 릴레이션은 JD*(공급부품, 부품작업, 작업공급)을 만족하고 있으므로 3-분해 릴레이션이다

--> 즉, JD를 만족하는 3-분해 릴레이션은 3개의 프로젝트로 분해해야 함

--> 다만 별다른 이점은 없다

요약해보자면,

1NF - 모든 도메인이 원자값이 되도록한 결과

2NF - 부분 함수 종속을 제거한 결과

3NF - 이행 함수 종속을 제거한 결과

BCNF - 모든 결정자가 후보키가 되도록 분해한 결과

4NF - 다치 종속(MVD)를 갖지 않은 테이블로 분해한 결과

5NF - 조인종속(JD)을 만족하는 n-분해 릴레이션으로 분해한 결과

저작자표시