[데이터베이스] 데이터베이스 정규화 - 정규형

복습하기 위해 학부 수업 내용을 필기한 내용입니다.
이해를 제대로 하지 못하고 정리한 경우 틀린 내용이 있을 수 있습니다.
그러한 부분에 대해서는 알려주시면 정말 감사하겠습니다.

지난 글에 이어서 작성해 보도록 하겠습니다.

2022.11.12 - [컴퓨터공학/데이터베이스] - [데이터베이스] 데이터베이스 정규화

[데이터베이스] 데이터베이스 정규화

▶비정규 릴레이션

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

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

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

각 지도교수도 한 학과에만 속한다.

 

▶제1정규형(1NF)

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

함수 종속 다이어그램

• 등록(학번, 성명, 학과, 지도교수, 과목 코드, 성적)
• 기본키 : {학번, 과목 코드}
• 함수 종속
  • {학번, 과목 코드} -> 성적
  • 학번 -> 성명
  • 학번 -> 지도교수
  • 학번 -> 학과
  • 지도교수 -> 학과

 

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

• 삽입 이상
  • 학번 7654 학생의 지도교수로 '김용주'라는 사실을 삽입하려 할 때, 어떤 과목을 등록하지 않는 한 삽입할 수 없다.
    • 기본키인 과목 코드가 NULL 값이기 때문이다.
• 삭제 이상
  • 학번 3654인 학생이 'CO234' 과목을 등록 취소할 때, 이 튜플이 삭제되는 경우 지도교수 '김재현' 정보까지 삭제된다.
• 갱신 이상
  • 학번 4653인 학생의 지도교수를 '이우정'에서 '고현주'로 변경할 때, 학번이 4654인 4개의 튜플 모두 변경하지 않고 일부만 변경될 때 문제가 발생한다.

 

1NF 이상 현상의 원인

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

 

1NF 이상의 해결

두 개의 릴레이션으로 분할하여 부분 함수 종속을 제거한다. => 2NF

 

▶제2정규형(2NF)

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

• 삽입 이상
  • 지도교수 '강성희'가 컴퓨터학과에 속한다는 사실을 삽입할 수 없다.
    • 지도받는 학생이 없으므로 -> NULL 값
• 삭제 이상
  • 학번 2587인 학생이 지도교수 '김용주'와 지도교수 관계를 취소(삭제)할 때 지도 교수가 속한 학과 정보까지도 삭제된다.
• 갱신 이상
  • 지도교수 '이우정'의 소속을 컴퓨터학과에서 전자과로 변경한다면 학번이 1234와 4653인 두 개의 튜플을 모두 변경해야 한다.
  • 그렇지 못하면 데이터 불일치 모순이 발생한다.

 

2NF 이상 현상의 원인

두 개의 상이한 정보를 하나의 릴레이션으로 혼합해서 표현하려고 하는데서 온다.

학과 애트리뷰트는 사실상 지도교수의 학과를 기술하는 것이다.

 

A가 기본키라면 A -> B와 A -> C가 존재한다.

그런데 C가 A 이외에 B에도 함수 종속한다면 C는 A에서 B를 거쳐서 이행적으로 종속한다.

따라서 애트리뷰트 C는 A에 이행적 함수 종속(Transitive FD)이라고 한다.

 

2NF 이상 현상의 해결

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

728x90

▶제3정규형(3NF)

학번, 성명, 학과, 지도교수를 학번, 성명, 지도교수와 지도교수, 학과로 분해한다.

 

지도 => 학생지도와 교수 학과로 분해한다.

• 학생 지도 (학번, 성명, 지도교수)
  • 기본키 : {학번}
  • 외래 키 : {지도교수}
  • 참조 : 교수 학과
• 교수 학과 (지도교수, 학과)
  • 기본키 : {지도교수}

 

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

• 제약조건
  • 한 학생은 여러 과목을 수강할 수 있다.
  • 한 교수는 한 과목만 강의할 수 있다.
  • 한 과목은 여러 교수가 강의할 수 있다.
• 기본키 : {학번, 과목}
• 함수 종속
  • {학번, 과목} -> 교수
  • 교수 -> 과목

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

• 삽입 이상
  • 교수 '김정희'가 '자료구조'과목을 강의하게 되었을 때, 삽입 불가능하다. (기본키인 학번이 NULL이라서)
• 삭제 이상
  
  • 학번 1234인 학생이 '자료구조'를 취소할 때, '이우정'이 '자료구조'를 맡고 있는 정보까지도 삭제된다.
• 갱신 이상
  • 교수 '김용주'의 담당 과목이 '데이터베이스'에서 '웹 프로그래밍'으로 변경되었을 때, 모든 튜플에 대해 변경되어야 한다.

 

3NF 이상 현상의 원인

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

즉, 교수 애트리뷰트는 후보 키가 아닌데 과목을 결정하는 결정자(determinant)다.

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

모든 결정자가 후보 키가 되도록 분할한다.

• 강의교수(학번, 교수)
  • 기본키 : {학번, 교수}
  • 외래 키 : {교수}
  • 참조 : 교수 과목
• 교수 과목(교수, 과목)
  • 기본키 : {교수}

 

다른 예시

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

이 비정 과목 리스트 릴레이션을 동등한 정규 릴레이션으로 변환시키면 아래 그림과 같은 과목 리스트 릴레이션으로 표현될 수 있다.

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

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

 

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

고현주 교수가 자료구조를 강의한다는 정보를 삽입하려면 자료구조 교재에 대해 맞게 3개의 튜플을 삽입해야 한다.

상당한 데이터 중복이 발생한다.

 

BCNF 이상 현상의 원인

과목은 교수나 교재의 값 하나를 결정하는 것이 아니라 몇 개의 값, 즉 값의 집합을 결정하기 때문이다.

이러한 종속을 다치 종속이라 하고 ->>로 표기한다.

        과목 ->> 교수

        과목 ->> 교재

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

데이터베이스 ->> {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정규형(5NF)

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

그러나 어떤 릴레이션은 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개의 프로젝트로 분해해야 한다.

보통은 3NF까지만 하면 충분히 해결된다.

하지만 이론적으로는 5NF까지 가는 경우가 존재한다.