데이터베이스

11. 데이터베이스 정규화

잘못 설계된 데이터베이스

• 테이블내에서 중복이 일어나게 되면 데이터베이스는 잘못 설계된 것이다.
  • 삭제 삽입 갱신에 문제가 생기기 때문이다.
    • 이상(anomaly)현상
• 여기서 기본키는 학번과 과목코드다

이상(anomaly) 현상

• 검색을 제외한 기능에서 일어난다.

삭제 이상 deletion anomaly

• 위의 테이블에서 학번이 3654 고영준 학생이 과목 등록을 취소하면
  • 학생의 정보가 아예 삭제 되어버린다

연쇄 삭제(triggered deletion)에 의해 정보가 영원히 손실되는 현상

삽입 이상 insertion anomaly

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

  • 과목을 등록해야지 학생의 정보가 삽입 가능하다.
    • 학번과 과목코드가 기본키이기 때문이다.
    • 여기서 입력을 하려고해도 무결성으로 인해 등록을 해야지만 입력할 수 있는 현상이 발생한다.

과목 정보가 없으니 입력할 수 없는 현상이 발생

갱신 이상 update anomaly

• 학번 4652인 학생의 학년을 4에서 3으로 변경하려고 할 때
  • 학번 4652에 대한 4개의 투플 모두를 변경해야한다.

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

이상현상의 원인과 해결책

이상 현상이 발생되는 이유는?

• 많은 정보를 무리하게 하나의 릴레이션으로 표현했기 때문이다.

해결 방법

• 애트리뷰터 간의 종속성을 분석해 하나의 릴레이션에 하나의 종속성만 표현하도록 테이블을 분해
  • 위의 테이블에서 현재 학번에 대해 성명과 학년이 종속관계를 나타내고 학번과 과목코드에 대해 성적이 종속관계를 나타내고 있다.

그래서 위의 테이블을

• 학번, 성명 , 학년을 가진 수강생 테이블과
• 학번, 과목코드, 성적을 가진 성적 테이블로 나누면 종속관계를 해결할 수 있다

이렇게 하나의 릴레이션을 두개 이상의 릴레이션으로 분해하는 과정을 정규화라고 한다.

함수 종속성

정의

• 학생(학번,성명,학과,전화번호)에서 성명, 학과, 전화번호는 학번에 각각 함수 종속(FD)이다.
  • 학번이 정해지면 이에 대응하는 값들은 하나기 때문이다.
  • 기호로 표시: 학번 -> (성명,학과,전화번호)

함수 종속

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

함수 종속 다이어그램 FD Diagram

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

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

• 위의 등록 릴레이션의 함수 종속 관계는 총 세개
  • {학번,과목코드} -> 성적
  • 학번 -> 성명
  • 학번 -> 학년

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

• 성적은 {학번,과목코드}에 완전 함수 종속되었다고 함
• 성명과 학년은 학번에 완전 함수 종속
• 성명과 학년은 {학번,과목코드}에 부분 함수 종속 되었다고 함

완전은 완벽하게 구성되었다고, 부분은 아직 불완전함을 의미

비정규 릴레이션

위의 릴레이션은 정규화되지 않은 릴레이션이다.

릴레이션 전제 조건

• 기본키: 학번, 과목코드
  • 학번과 과목코드의 조합으로 학생이 등록한 과목의 성적을 식별 가능
• 한 학생은 한사람의 지도교수, 한 학과에만 속함
• 각 지도교수도 한 학과에만

제 1 정규형 1NF

릴레이션에 속한 모든 도메인이 원자값으로 되어 있는 경우

• 값들이 중복된다.
• 위의테이블에서 많은 값들이 중복되는 모습을 볼 수 있다.

이상 현상

삽입 이상

• 어떤 과목을 새로 등록하지 않는 이상 학번이 새로운 값을 추가할 수 없다.
  • 기본 키인 과목코드가 NULL이기 때문에 무결성 제약조건에 위배된다.

삭제 이상

• 과목 등록을 취소할 때 학생의 다른 정보들(지도교수, 성적)등 의 정보까지 삭제된다.

갱신 이상

• 한 학번의 세부 값들을 변경할 때 릴레이션 내의 해당 학생에 대한 세부 값들을 모두 변경해야한다.
  • 일부만 변경될 때 문제가 발생한다.

1NF 이상 현상의 원인

• 키가 아닌 속성들이 기본키에 완전 함수 종속되지 않고 부분 함수 종속되기 때문

해결 방법

• 두개의 릴레이션으로 분할해 부분 함수 종속을 제거
  • 2NF

제 2 정규형 2NF

• 1 정규형에서 성명 지도교수 학과 속성이 기본키에 부분 함수 종속되는 문제를 해결

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

삽입 이상

• 새로운 지도교수가 담당 학생이 없다면 릴레이션에 삽입되지 않는다.

삭제 이상

• 학생과 지도교수의 관계를 취소할 때 지도교수의 학과 정보도 소실

갱신 이상

• 지도교수의 소속을 변경하기 위해서는 투플들을 모두 변경해야함
  • 그렇지 않다면 모순이 발생

2NF 이상현상의 원인

• 두개의 상이한 정보를 하나의 릴레이션으로 혼합해 표현하기 때문
  • 학과 애트리뷰트가 학생과 지도교수 둘다 기술하기 때문이다.

이행적 함수 종속(Transitive Fd)

• A가 기본키라면 A->B와 A->C가 존재
  • C가 A이외에 B에도 함수 종속한다면 A->B->C하게 이행적으로 종속
    • 이때 C는 A에 이행적 함수 종속이라고 한다.

2NF 이상현상 해결

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

제 3 정규형 (3NF)

• 학번 성명 학과 지도교수를 학번 성명 지도교수와 지도교수 학과로 분해
• 지도 릴레이션을 학생 지도와 교수학과로 분해
• 학생지도 (학번, 성명, 지도교수)
• 교수학과 (지도교수, 학과)

3NF는 거의 완벽한 설계라 할 수 있지만, 문제점이 있을 수 있다.
-> 보이스 코드 정규형

보이스/코드 정규형(BCNF)

새로운 테이블로 가정...

강의 과목에 대한 제약조건 가정

• 한 학생은 여러 과목 수강 가능
• 한 교수는 한 과목만 강의 가능
• 한 과목은 여러 교수가 강의

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

• 학번,과목과 교수의 관계는 완전해보이나, 교수와 과목 관계가 불완전

삽입이상

• 교수가 새 과목을 강의하게 되었을 때 삽입이 불가하다.
  • 학번 정보가 없기 때문

삭제 이상

• 학번에 해당하는 학생이 과목을 취소할 때 교수가 과목을 맡고 있는 정보도 삭제된다.

갱신 이상

• 교수의 강의 과목이 변경되었을 때 모든 투플이 변경되어야한다

이상현상이 일어나는 원인

• 키가 아닌 애트리뷰트가 다른 애트리뷰트를 결정하기 때문
  • 즉 교수는 후보키가 아닌데 과목을 결정하는 결정자이다.

해결 방법

• BCNF
  • 모든 결정자가 후보키가 되도록 분할

강의 과목 테이블을 강의 교수(학번, 교수)와 교수과목(교수,과목)으로 분할

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

제 4 정규형 (다치 종속)

다치 종속

• 릴레이션에서 어떤 A,C값에 대응되는 B의 집합이 A값에만 종속되고 C값엔 독립적이면 B는 A에 다치 종속된다라고 한다.
  • A->>B로 표현

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

• A->>B|C로 한번에 표현 가능

다치 종속을 해결하기 위해 릴레이션 A,B와 A,C로 나누자.

제 5 정규형 (5NF, 조인 종속)

4정규형까지 문제의 릴레이션은 2개의 릴레이션으로 분해했다.
하지만 5정규형 문제는 3개 이상의 릴레이션으로 분해해야하는 경우다.

• 이는 납품관리 릴레이션이다.
• 어떤 공급자가 어떤 부품을 어떤 작업장에 공급하는걸 나타냈다.

조인 종속

• 어떤 릴레이션에 대해 프로젝트한 N개의 부분집합에서, 모두 다시 조인했을 때 릴레이션 R과 조인의 결과가 같다면 R은 조인종속을 만족시킨다고 한다.
  • 조인종속(JD)*(속성1,속성2,...속성4)를 만족시킨다고 함
  • 위의 릴레이션을 프로젝트 후 조인한다면 결과는 원래 릴레이션과 같다.
    • {공급자,부품} {부품,작업장} {공급자,작업장}으로 프로젝트 후 합쳐

그래서 릴레이션을 JD를 만족키는 프로젝트로 분해하면된다.

• 별 이점은 없다.

정규형 과정

비정규 릴레이션 -> 1NF -> 2Nf -> 3NF->BCNF -> 4NF -> 5NF

비정규 -> 1NF

• 모든 도메인이 원자값이 되도록 수정

1NF -> 2NF

• 부분 함수 종속을 제거

2NF -> 3NF

• 이행 함수 종속을 제거

3NF -> BCNF

• 모든 결정자가 후보키가 되도록 분해

BCNF -> 4NF

• 다치 종슥을 갖지 않는 테이블로 분해

4NF -> 5NF

• 조인 종속을 만족시키지 않는 n개의 릴레이션으로 분해