DB (6) 정규화

관계 스킴 - 릴레이션의 표현과 속성들의 관계를 정의하는 방법

 

데이터베이스를 잘 못 설계하면 불필요한 데이터 중복이 발생하여 릴레이션에 대한 데이터 삽입, 수정, 삭제 연산시 부작용이 발생할 수 있다. 이러한 부작용을 이상(anomaly) 현상이라 한다.

 

이상 현상을 제거해나가며 데이터베이스를 올바르게 설계하는 것을 정규화라고 한다. 정규화를 통해 데이터의 중복과 의존성을 없앤다.

 

단계적 정규화

릴레이션 형태에 따라 제1정규형(First Normal Form), 제2정규형(Second Normal Form), 제 3정규형(Third Normal Form), 보이스-코드 정규형(Boyce-Codd Normal Form), 제 4정규형(Fourth Normal Form), 제 5정규형(Fifth Normal Form)으로 나뉘어진다.

 

정규화의 필요성

- 이상현상이 발생하는 이유

관련없는 속성들이 하나의 릴레이션이 모아져 있는 경우, 이상 현상이 발생한다. 따라서 관련있는 속성들로만 구성하여 릴레이션을 만들 필요가 있고 이를 릴레이션을 분해한다고 한다.

- 함수적 종속성 (FD: Functional Dependency)

정규화를 수행하기 위해 속성간의 관련성을 판단해야한다. 정규화 과정에서 고려해야하는 속성들간의 관련성을 함수적 종석성라고 한다. 일반적으로 릴레이션에 함수적 종속성이 하나 존재하도록 정규화를 통해 릴레이션을 분해한다.

 

이상 현상의 분류

 

다음 속성으로 이루어진 자격증 릴레이션이 있을 때, 

 

학번 / 자격증 코드 / 취득 여부 / 학년 / 가중치

 

기본키 : 학번, 자격증 코드

 

- 삭제 이상

어떤 릴레이션 R에서 특정한 튜플을 삭제할 경우 유지되어야할 정보까지 삭제되는 연쇄 삭제 현상을 말한다.

학번-자격증코드만 삭제하고 싶은데 튜플은 그보다 많은 속성을 가지고 있으므로 다른 속성 정보까지 삭제해한다.

 

- 삽입 이상

학번 / 학년 정보만 으로는 자격증 릴레이션에 새로운 튜플을 추가할 수 없다. 자격증 코드는 기본키이기 때문에 NULL값을 가질 수 없기 때문이다.

 

- 갱신 이상

임의의 데이터를 갱신했을 때 그것과 관계를 맺고 있는 데이터의 일관성을 유지할 수 없는 현상이다.

어떤 학생의 학년이 올라가 해당 학년 정보를 업데이트 해야하는데 연관된 모든 튜플을 같은 학년으로 업데이트 하지 않는다면 일관성을 잃게 된다.

 

속성 간의 다양한 관계를 고려하지 않고 릴레이션에 한꺼번에 표현했기 때문에 이상현상이 발생한다. 해결 방법은 이러한 종속적 관계를 분석하여 하나의 릴레이션에 하나의 종속 관계만 있도록 만들어줘야한다. 이러한 분해과정을 정규화라고 한다.

 

데이터의 관계성을 분석하는 목적은 데이터의 의미를 활용해서 좋은 데이터베이스 스키마를 설계하는데 있다. 스키마 설계는 관련된 속성들을 수집하고 이들 간에 존재하는 제약조건을 식별한 다음에 이 제약조건을 기본으로 해서 속성들을 릴레이션으로 그룹화한다. 이렇게 만드는 방식을 스키마 변환이라고 한다.

관계 데이터베이스 논리적 구조는 스키마 변환을 통해 얻어진 스키마를 말한다.

 

관계 데이터베이스의 논리적 구조는 이 스키마 변환을 통해 얻어진 스키마를 말한다. 이러한 스키마 변환은 다음 세가지 원리를 기초로 한다.

1) 정보 표현의 무손실, 다른 스키마로 변환하는 과정에서 정보의 손실이 있으면 안된다.

2) 최소의 데이터 중복이 허용되어야 한다. 중복으로 인한 여러가지 현상을 제거할 수 있기 때문이다.

3) 분리의 원칙, 하나의 독립된 관계성은 별도의 릴레이션으로 분리시켜 표현하는 것이다.

 

함수적 종속성 (Functional Dependency)

함수 종속(FD)이란 어떤 릴레이션 R에서 X와 Y를 각각 R의 속성 부분집합이고 X의 속성들에 대해 하나의 Y 속성이 연관되어 있을 때 Y는 X에 함수 종속이라 말한다.

 

X(결정자) -> Y(종속자)

 

X가 Y를 함수적으로 결정한다.

Y가 X에 함수적으로 종속되어 있다.

학생 릴레이션

학번 / 학생이름 / 학년 속성으로 이루어진 학생 릴레이션에서 학생이름과 학년 속성은 각각 학번에 종속되어 있다. 학번 -> 학생이름, 학번 -> 학년

 

학생 릴레이션에서 학번은 기본키 속성을 가지고 있기 때문에 학번 하나에 오직 하나의 학생이름과 학년이 존재한다.

 

함수 종속 관계 X->Y에서 기본키나 후보키만 X가 될 수 있는 것은 아니다. 기본키나 후보키가 아니더라도 속성 Y 값을 유일하게 결정하는 X는 함수 종속 관계에서 결정자가 될 수 있다.

 

자격증 릴레이션에서 함수 종속 관계를 파악해보자.

 

학번 / 자격증 코드 / 취득 여부 / 학년 / 가중치

기본키 : 학번, 자격증 코드

 

자격증 릴레이션에서 학번은 학생이름을 유일하게 결정한다. 또한 어떤 학번이 치룬 자격증에 대해 취득 여부가 결정되므로 속성 집합{학번, 자격증 코드}는 취득 여부를 유일하게 결정한다. 이 속성 집합은 취득 여부 뿐만 아니라 학생이름도 유일하게 결정한다.

 

포함하고 있는 함수 종속 관계

학번 → 학생이름

{학번, 자격증코드} → 취득여부

{학번, 자격증코드} → 학생이름

 

학생이름은 학번에 유일하게 결정되지만 {학번, 자격증 코드} 집합을 기준으로 부분적으로 함수 종속관계가 이루어 진다. 취득 여부는 {학번, 자격증 코드} 집합으로만 유일하게 결정되기에 완전한 함수 종속관계가 이루어 진다. 

 

기본 정규형

함수 종속성을 이용하여 릴레이션을 연관성이 있는 속성들로만 구성되도록 분해해서 이상 현상이 발생하지 않는 올바른 릴레이션으로 만들어 나가는 과정을 정규화 라고 한다. 정규화의 기본 목표는 관련이 없는 함수 종속성은 별개의 릴레이션으로 표현하는 것이다.

 

정규화는 중복과 이상 현상을 최소화 하기 위해 함수적 종속성과 기본키를 기반으로 주어진 관계 스키마를 분석하는 과정이다. 정규화 과정에서 데이터 손실이 없어야하며 각 정규화 과정은 해당 단계에서 만족해야하는 제약조건이 있다. 정규화 과정은 차수가 높을 수록 더 많은 제약 조건을 가지게 된다.

 

제 1정규형

 

릴레이션에 속한 모든 속성의 도메인이 원자값으로 구성되어 있으면 제 1 정규형에 속한다.

위와 같은 자격증 릴레이션에 자격증 코드 속성이 다중 속성값이 있다면 이는 분해 가능하므로 원자값을 갖는 다고 할 수 없다.

제 1정규화를 만족하는 자격증 릴리에션

자격증 릴레이션의 기본키는 학번과 자격증 코드이다. 학번은 학년과 가중치를 유일하게 결정한다. 또한 학번과 자격증 코드는 취득여부를 유일하게 결정한다. 따라서 자격증 릴레이션에 포함되어 있는 종속 다이어그램은 다음과 같다.

자격증 릴레이션의 함수 종속 다이어그램

자격증 릴레이션에는 학년과 가중치 속성이 중복되어 나타나며 이러한 데이터 중복으로 인해 삽입/갱신/삭제시 이상 현상이 발생할 수 있다.

1) 삽입 이상

기본키는 {학번, 자격증 코드}이므로 자격증에 무조건 참여해야 새 학생에 대한 데이터를 삽입할 수 있다.

2) 갱신 이상

동일 학번을 가지는 튜플이 2개 이상인 경우가 있다. 한 튜플에 대해 학년을 업데이트 할 때 일관성을 유지해야한다.

3) 삭제 이상

자격증 릴레이션에서 A003를 응시한 기록을 삭제한다면 20173028 학번에 대한 튜플이 삭제되고 릴레이션에서 이 학번 튜플이 유일하므로 나머지 학년과 가중치 정보가 사라지게 된다.

 

이러한 이상 현상이 있는 것은 기본키인 {학번, 자격증 코드}에 완전하게 종속되지 못 한 학년과 가중치 속성 때문이다. 학년과 가중치 속성은 또한 자격증 코드와 취득여부와 관련이 없으나 같은 릴레이션에 포함되어 있다.

 

따라서 부분 함수 종속이 제거되도록 자격증 릴레이션을 분해해야한다. 릴레이션을 분해하여 부분 함수 종속을 제거하면, 분해된 릴레이션들은 제2 정규형에 속하게 되고 앞서 제시한 이상 현상이 더 이상 발생하지 않게된다.

 

제 2정규형

 

릴레이션이 제 1정규형에 속하고 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2정규형에 속한다.

기존 자격증 릴레이션에서 기본키가 아니였던 학년과 가중치가 부분적으로 종속 관계를 이루어 두 릴레이션으로 분리한다. 이로써 학년과 가중치는 학번으로 유일하게 결정되는 학번 릴레이션으로 표현되며 기존 자격증 릴레이션에서 취득여부, 자격증 코드와 분리된다.

두 릴레이션에서 더 이상 부분적 종속 관계가 없으므로 제 2정규형에 속하게 된다.

분해된 두 릴레이션의 함수 종속 다이어그램

정규화 과정에서 주의해야할 점은 분해된 릴레이션들을 자연 조인하여 분해 전의 릴레이션으로 다시 복원할 수 있어야 한다는 것이다. 이 때 의미적으로 동등한 릴레이션들로 분해되어야하고 릴레이션의 분해로 이해 정보의 손실이 발생하지 않아야한다. 정규화 과정에서 자격증 릴레이션이 분해되었지만 검색을 할때 자연 조인을 통해 학번과 자격증 코드 기본키를 이용하여 나머지 속성들을 검색할 수 있어야 한다.

정규화 과정에서 수행되는 릴레이션 분해는 무손실 분해여야한다.

 

하지만 학번 릴레이션에서 아직 함수 종속 관계가 하나보다 많게 존재하므로 이상 현상이 발생할 수 있다.

1) 삽입 이상

학년과 가중치 속성 정보만 담긴 튜플을 생성할 수 없다.

2) 삭제 이상

어떤 학번을 가진 학생의 전학으로 해당 튜플을 삭제한다면, 필요한 가중치 정보가 삭제될 수 도 있다.

3) 갱신 이상 

학년과 연관된 가중치에 대한 업데이트가 발생시 학년과 가중치에 대한 일관성이 보장되지 않을 수 도 있다.

 

이러한 이상의 원인은 학년과 가중치에 대한 종속 관계로 "이행적 함수 종속"이 존재하기 때문이다.

 

제 3 정규형

릴레이션이 제 2 정규형에 속하고 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제3 정규형에 속한다.

이행적 함수 종속

이행적 함수 종속이란 세 개의 속성 집합 X, Y, Z에 대해 함수 종속 관계 X->Y와 Y->Z가 존재하면 논리적으로 X->Z가 성립하는데 이를 속성 집합 Z가 속성 집합 X에 이행적으로 함수 종속되었다고 한다.

학번 릴레이션에서 이행적 함수 종속

학번 릴레이션에서 학년과 가중치는 학번에 종속적이다. 동시에 가중치는 학년에 유일하게 결정되므로 학년에 종속되었다고 볼 수 있다. 따라서 학번이 학년을 통해 가중치를 결정하는 이행적 함수 종속이 존재하게 된다.

제 3정규화에서는 이행적 함수 종속을 분리하여 X->Y와 Y->Z의 함수 종속을 가지는 두 개의 릴레이션을 만들도록 한다.

제 3 정규화

이행적 함수 종속 관계 분리

 

보이스/코드 정규형

 

릴레이션의 함수 종속 관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속한다.

 

하나의 릴레이션에 후보키가 여러개 존재할 수 도 있다. 기존 제 3정규형에 속한 릴레이션 중 더 강력한 제약조건을 만족해야 보이스/코드 정규형이 될 수 있다.

 

자격증 릴레이션에서 {학번, 자격증 코드}는 취득 여부를 유일하게 결정하는 결정자 속성 집합이다. {학번, 자격증 코드}는 유일한 후보키이면서 기본키이다. 따라서 자격증 릴레이션은 보이스/코드 정규형에 속한다. 학번 릴레이션에서도 학번은 결정자이면서 기본키이고 학년 릴레이션에서 학년은 결정자이면서 기본키를 가진다. 두 릴레이션 모두 보이스/코드 정규형이다.

 

수강 과목 릴레이션

수강 과목 릴레이션과 함수 종속 다이어그램

왼쪽 위와 같은 수강 과목 릴레이션이 있다 하자. 각 학번은 여러 수강 과목을 수강신청할 수 있다. 그리고 교수 한 명은 하나의 수강 과목만을 담당한다. 위 릴레이션에서 튜플을 구분지을 수 있는 후보키는 {학번, 수강과목}과 {학번, 교수코드}가 있다. 이 중에서 {학번, 수강과목} 키를 기본키로 설정한다면, 오른쪽 그림과 같은 종속관계가 있다. 여기서 학번과 수강과목은 교수코드를 결정하면서 교수코드 역시 수강과목을 결정할 수 있다.

 

모든 속성은 원자적이며 (제1 정규형), 기본키가 아닌 속성은 기본키에 완전 종속되고 (제 2정규형) 이행적 종속 관계를 포함하고 있지 않다. (제3 정규형) 하지만 교수코드는 수강 과목을 결정하는 결정자이지만 후보키가 아니다. 따라서 수강 과목 릴레이션은 보이스/코드 정규형에 속하지 않는다.

 

따라서 다음과 같은 이상 현상이 발생한다.

1) 삽입 이상

어떤 교수가 네트워크 과목을 담당하여 {네트워크, C06} 튜플을 삽입하고 싶지만 기본키 요건(널 값)을 만족하지 못하기 때문에 삽입할 수 없다.

2) 갱신 이상

데이터베이스 담당 교수가 변경되어 코드가 C04로 바뀌면 관련 튜플을 모두 바꿔야한다.

3) 삭제 이상

학번이 20208122인 자료구조 수강 신청을 취소하면 릴레이션에서 유일한 튜플 자료구조와 담당 교수 코드 정보를 잃게 된다. 

기존 수강신청 릴레이션을 보이스/코드 정규형으로 만들기 위해 학번과 과목담당 릴레이션으로 분리한다. 분리하는 원리는 기존에 후보키가 아닌 결정자를 제거해 새로운 릴레이션에서 결정자 이자 후보키가 될 수 있도록 하는 것이다.

학번 릴레이션과 과목 담당 릴레이션의 함수 종속 다이어그램

 

기본 정규형 정리

제1 정규형 : 모든 속성의 값이 더 이상 분해될 수 없는 원자 값을 가져야한다.

제2 정규형 : 기본키가 아닌 속성은 기본키에 대해 완전한 함수 종속관계를 가져야한다.

제3 정규형 : 이행적 종속 관계가 존재하지 않아야한다.

보이스/코드 정규형 : 모든 함수 종속 관계에서 결정자는 후보키여야 한다. 

 

정규화 개요

• 정규화란 함수적 종속성을 바탕으로 잘못 설계된 관계형 스키마를 더 작은 속성으로 분리하여 바람직한 스키마로 만드는 것이다.
• 하나의 종속성이 하나의 릴레이션에 표현될 수 있도록 분해하는 과정이다.
• 정규형에는 제1정규형, 제2정규형, 제3정규형, BCNF, 제4정규형, 제5정규형이 있으며 차수가 높을수록 제약조건이 많아진다.
• 정규화는 데이터베이스의 논리적 설계 단계에서 수행한다.

정규화 목적

• 데이터 구조의 안정성을 최대화
• 어떠한 릴레이션이라도 데이터베이스 내에서 표현이 가능하게 만든다.
• 효과적인 검색 알고리즘을 생성할 수 있다.
• 중복을 배제하여 삽입, 삭제, 갱신 이상의 발생을 방지한다.
• 데이터 삽입 시 릴레이션을 재구성할 필요성을 줄인다.

정규화 원칙

• 정보의 무손실 표현: 스키마 변경과정에서 정보의 손실이 있으면 안된다.
• 분리의 원칙 : 하나의 독립된 관계성은 하나의 독립된 릴레이션으로 분리시켜 표현해야한다.
• 데이터의 중복성이 감소되어야한다.

정규화의 효과

• 데이터 중복을 제거하고 데이터 일관성을 유지할 수 있다.
• 데이터 모형의 단순화가 가능하다.
• 속성의 배열 검증이 가능하다. 즉, 식별자와 속성과의 종속성 여부를 판단할 수 있다.
• 데이터베이스 설계가 용이하며 엔티티와 관계의 누락을 방지할 수 있다.

정규화의 문제점

• 정규화로 인해 테이블 join 연산이 많아지며 응답속도의 지연이 발생할 수 있다.
• 특정시점의 정보를 표현하기 위해 엔티티의 발생 및 업무 규칙 수용 난이도가 증가할 수 있다.
• 데이터 공간의 비효율적인 활용이 발생할 수 있다.

진보된 정규형

제 4 정규형

릴레이션 R에 A ->> B가 성립하는 경우 R의 모든 속성이 A에 함수적 종속이면 이 릴레이션 R은 제 4정규형에 속한다.

 

제 4 정규형은 릴레이션에서 다치종속 관계가 성립되는 경우 분해하는 정규형을 말한다.

->> 는 A의 속성값은 B 속성값 하나를 결정해야한다는 의미이다.

ex) 과목 / 과목 코드 / 학과코드

과목 속성은 과목 코드를 결정하기도 하고 학과코드를 결정하기도 한다. 이런 다치종속 관계를 없애고 과목 -> 과목 코드와 과목 -> 학과 코드의 두 릴레이션으로 분리한다.

 

제 5 정규형

릴레이션 R의 모든 조인 종속성의 만족이 R의 후보키를 통해서만 만족될 때 그 릴레이션 R은 제 5정규형 또는 PJ/NF에 속한다.

 

기존에 두개의 릴레이션의 분리만 으로도 정보의 손실 없이 정규화를 할 수 있었지만 일반적으로 세 개 또는 그 이상의 릴레이션으로 분해해야만 정보 무손실 분해가 가능하다고 밝혀졌다. 후보키를 통하지 않는 조인 종속이 있다면 이를 제거해야한다.