[오라클 DB] 릴레이션 분해와 정규형(Normal Form)

 

다음 예시 릴레이션을 보자.

 

학번	이름	이메일	과목번호	학점
11002	이홍근	lee@naver.com	CS310	A0
11002	이홍근	lee@naver.com	CS313	B+
24036	김순미	kim@gmail.com	CS345	B0
24036	김순미	kim@gmail.com	CS310	A+

 

위의 릴레이션은 중복과 갱신 이상을 모두 일으키고 있다.

 

기본 키 쌍인 (학번, 과목번호) 모두에 종속된 학점 애트리뷰트는 완전 함수적 종속성을 띄고 있지만,

기본 키 쌍 중 학번 애트리뷰트가 이름, 이메일을 결정하고(부분 함수적 종속성), 이메일이 학번과 이름을 결정하고 있으며(이행적 함수적 종속성) , 이름이 학번을 결정하고 있다(부분 함수적 종속성).

 

릴레이션 분해를 통해 부분 함수적 종속성과 이행적 함수적 종속성을 해결하고, 최종적으로 중복과 갱신 이상을 해결해야 한다. 그러기 위해선 여러 정규형 과정을 거쳐야 한다.

 

 

---

 

정규형(Normal Form)의 종류

 

정규형의 종류는 다음과 같다.

 

제 1 정규형

제 2 정규형

제 3 정규형

BCNF (Boyce-Codd normal form)

제 4 정규형

제 5 정규형

 

 

일반적으로 산업계의 데이터베이스 응용에서 데이터베이스를 설계할 때 BCNF까지만 고려한다.

이 포스팅에서는 제 1~3 정규형과 BCNF까지만 설명하도록 하겠다.

 

---

 

제 1 정규형

 

한 릴레이션 R이 제 1 정규형을 만족할 필요 충분 조건은 다음과 같다.

 

릴레이션 R의 모든 애트리뷰트가 원자 값만을 갖는다는 것으로,
즉 릴레이션의 모든 애트리뷰트에 다치/복합 애트리뷰트가 나타나지 않으면 제 1 정규형을 만족한다.

 

논리 설계에서 보통 다치 및 복합 애트리뷰트는 빠지게 된다. 혹시나 빼지 않았을 때 제 1 정규형을 만족하지 않는다고 말할 수 있다.

 

 

학생 릴레이션

학번	이름	과목번호	주소
11002	이홍근	{CS310, CS313}	우이동
24036	김순미	{CS310, CS345}	양재동

 

위 학생 릴레이션에서, 과목번호 애트리뷰트가 다치 애트리뷰트 형태를 띄고 있다.

해당 릴레이션을 제 1 정규형으로 변환하는 방법은 다치 애트리뷰트에 나타나는 집합에 속한 각 값마다 하나의 튜플로 표현하면 된다.

 

 

학생 릴레이션

학번	이름	과목번호	주소
11002	이홍근	CS310	우이동
11002	이홍근	CS313	우이동
24036	김순미	CS310	양재동
24036	김순미	CS345	양재동

 

 

또한 다음과 같이 다치 애트리뷰트들을 분리해서 새로운 릴레이션으로 만드는 방법도 존재한다.

 

 

분리 - 학생 릴레이션

학번	이름	주소
11002	이홍근	우이동
24036	김순미	양재동

 

분리 - 수강 릴레이션

학번	과목번호
11002	CS310
11002	CS313
24036	CS345
24036	CS310

 

 

애트리뷰트에 원자값만 있는 학생 릴레이션으로, 제 1 정규형을 만족한다.

 

 

제 1 정규형에 존재하는 갱신 이상엔 무엇이 있을까?

아래의 다른 예시 릴레이션을 보자.

 

학생 릴레이션

학번	학과이름	학과전화번호	과목번호	학점
11002	컴퓨터과학	210-2261	CS310	A0
11002	컴퓨터과학	210-2261	CS313	B0
24036	정보통신	210-2585	IC214	B+

 

위 릴레이션 또한 모든 애트리뷰트가 원자값을 가지므로 제 1 정규형을 만족한다.

하지만 해당 릴레이션은 데이터 중복 발생과 모든 갱신 이상을 가지고 있다.

 

데이터 중복 : 학번, 학과이름, 학과전화번호 모두가 같은(중복된) 튜플이 존재

수정 이상 : 학과의 전화번호가 중복 저장되어 학과의 전화번호를 수정할 때에 데이터베이스의 일관성이 유지되지 않을 수 있음.
학과의 전화번호를 수정하려면 해당 학과에 소속되어 있는 모든 튜플의 학과전화번호를 수정해야하는 문제가 발생한다.

삽입 이상 : 학과에 소속된 학생이 없으면 학과에 대한 튜플을 삽입할 수 없음.
'생명화학과'라는 새로운 과를 신설하고, 아직 학생이 존재하지 않는 상태라면 학과이름, 학과전화번호 외에 모두 NULL 값을 채워야 하는데, 이 경우 학번이 부분 키이기 때문에 엔티티 무결성 제약조건에 따라 기본 키에 널 값을 입력할 수 없어 불가능하다.

삭제 이상 : 어떤 학과에 소속된 마지막 학생 튜플을 삭제하면 이 학생이 소속된 학과에 관한 정보도 삭제됨.
'정보통신과'의 마지막 학생인 학번 24036 학생 튜플을 삭제하면 정보통신과에 대한 학과전화번호 데이터도 모두 삭제되게 되어 의도치 않은 데이터 삭제를 일으킨다.

 

 

위와 같은 갱신 이상이 생기는 이유는 다음과 같다.

 

기본 키에 대한 부분 함수적 종속성이 학생 릴레이션에 존재함.

 

 

여기서 우리는 갱신 이상을 해결하기 위해 제 2 정규형을 알아야 한다.

 

---

 

제 2 정규형

 

한 릴레이션 R이 제 2 정규형을 만족할 필요 충분 조건은 다음과 같다.

 

릴레이션 R이 제 1 정규형을 만족하면서,
키가 아닌 모든 애트리뷰트들은 R의 기본 키에 완전하게 함수적으로 종속하는 것

 

기본 키가 두 개 이상의 애트리뷰트로 구성되었을 경우에만 제 1 정규형이 제 2 정규형을 만족하는가를 고려햘 필요가 있다.

즉, 기본 키가 한 개의 애트리뷰트로 이루어진 릴레이션이 제 1 정규형을 만족하면 제 2 정규형도 만족한다는 의미이다.

 

 

 

부분 함수적 종속성이 존재하는 릴레이션(제 1 정규형)

 

 

fd2는 기본 키 쌍인 (학번, 과목번호) 모두에 종속되어 완전 함수적 종속성을 띄고 있지만,

fd1은 기본 키 쌍 중 일부인 학번 애트리뷰트에 종속된 학과이름, 학과전화번호 애트리뷰트가 존재하는 부분 함수적 종속성을 띄고 있다.

 

제 2 정규형을 만족하기 위해선 부분 함수적 종속성이 존재하지 않도록 릴레이션을 분해해야 한다.

 

부분 함수적 종속성이 존재하지 않도록 분해된 두 릴레이션(제 2 정규형)

 

위와 같이 부분 함수적 종속성을 띄는 애트리뷰트끼리 릴레이션을 분해하여 제 2 정규형을 만족시킬 수 있다.

 

위의 예시인 학생 릴레이션에 대해 제 2 정규형을 만족하도록 릴레이션을 분해하면 다음과 같다.

 

 

학생 릴레이션 ( 제 1 정규형 만족 )

학번	학과이름	학과전화번호	과목번호	학점
11002	컴퓨터과학	210-2261	CS310	A0
11002	컴퓨터과학	210-2261	CS313	B0
24036	정보통신	210-2585	IC214	B+

 

 

< 릴레이션 분해 >

 

 

학생 릴레이션 ( 제 2 정규형 만족 )

학생	학과이름	학과전화번호
11002	컴퓨터과학	210-2261
24036	정보통신	210-2585

 

부분 함수적 종속성을 띄는 애트리뷰트끼리 릴레이션으로 분해한다.

 

수강 릴레이션 ( 제 2 정규형 만족 )

학번	과목번호	학점
11002	CS310	A0
11002	CS313	B0
24036	IC214	B+

 

완전 함수적 종속성을 띄는 애트리뷰트끼리 릴레이션으로 분해한다.

 

 

제 2 정규형을 만족하도록 릴레이션을 분해함으로써, 중복 문제를 해결하였다.

 

하지만 갱신 이상을 모두 해결하지는 못한 모습이다.

 

 

수정 이상 : 여러 학생이 소속된 학과의 전화번호가 변경되었을 때, 그 학과에 속한 모든 학생들의 튜플에서 전화번호를 수정하지 않으면 데이터베이스의 일관성이 유지되지 않는다.

삽입 이상 : 어떤 학과를 신설해서 아직 소속 학생이 없으면, 그 학과의 정보를 입력할 수 없다.(엔티티 무결성 제약조건)

삭제 이상 : 어떤 학과에서 마지막 학생의 튜플이 삭제되면, 그 학과의 전화번호도 함께 삭제된다.

 

 

제 2 정규형을 만족함에도 갱신 이상이 생기는 이유는 무엇일까?

바로 분해하고 난 학생 릴레이션에서 이행적 함수적 종속성이 존재하기 때문이다.

 

이행적 종속성이 존재하는 릴레이션(제 2 정규형)

 

갱신 이상을 해결하기 위해선 이행적 종속성이 존재하지 않도록 릴레이션을 분해해야 한다.

 

---

 

제 3 정규형

 

한 릴레이션 R이 제 3 정규형을 만족할 필요 충분 조건은 다음과 같다.

 

릴레이션 R이 제 2 정규형을 만족하면서,
키가 아닌 모든 애트리뷰트들은 릴레이션 R의 기본 키에 이행적으로 종속하지 않는 것

 

 

이행적 종속성이 존재하지 않도록 분해된 두 릴레이션(제 3 정규형)

 

이행적 함수적 종속성이 존재하는 분해하고 난 학생 릴레이션을 제 3 정규형에 부합하도록 분해하면 다음과 같다.

 

학생 릴레이션 ( 제 3 정규형 만족 )

학번	학과이름
11002	컴퓨터과학
24036	정보통신
11048	컴퓨터과학

 

 

학과 릴레이션 ( 제 3 정규형 만족 )

학과이름	학과전화번호
컴퓨터과학	210-2261
정보통신	210-2585

 

 

위와 같이 제 3 정규형이 만족하도록 릴레이션을 분해하면 수정이상, 삭제이상, 삽입이상이 모두 해결된다.

 

수정 이상 : 학과 전화번호를 수정해야 할 때, 학과 릴레이션에서 바로 수정하면 된다.

삭제 이상 : 학생 릴레이션의 튜플을 삭제해도 학과 정보가 같이 삭제되지 않고 학과 릴레이션에 남아 있다.

삽입 이상 : 학과를 신설할 때, 학과 릴레이션에 튜플을 추가하기만 하면 된다.

 

 

 

제 3 정규형을 만족하면 무조건 갱신 이상이 존재하지 않을까? 그것은 또 아니다.

다음의 예시 릴레이션을 보자.

 

학번	과목	강사
11002	데이터베이스	이영준
11002	운영체제	고성현
24036	자료구조	엄영지
24036	데이터베이스	조민형
11048	데이터베이스	이영준

 

모든 애트리뷰트는 원자값을 지니고 있고(제 1 정규형 만족)

키가 아닌 강사 애트리뷰트가 기본 키 쌍(학번, 과목)에 완전하게 함수적으로 종속하고 있고(제 2 정규형 만족)

키가 아닌 강사 애트리뷰트가 기본 키에 직접 종속되고 있기에 이행적 함수적 종속성이 존재하지 않는다.(제 3 정규형 만족)

 

하지만 다음과 같은 갱신 이상이 존재한다.

 

수정 이상 : 어떤 과목의 강사가 변경되었을 때 그 과목을 수강하는 모든 학생들의 튜플에서 강사를 수정하지 않으면 데이터베이스의 일관성이 유지되지 않는다. ( 여러 튜플의 값을 바꿔야하는 문제 )

삽입 이상 : 어떤 과목을 신설하여 아직 수강하는 학생이 없으면 어떤 강사가 그 과목을 가르친다는 정보를 입력할 수 없다. ( 알고리즘 과목을 신설하고 싶어도, 과목 수강생이 없어 신설해둘 수 없음 )

삭제 이상 : 어떤 과목의 마지막 이수하는 학생을 삭제하면 그 과목을 가르치는 강사에 관한 정보도 함께 삭제된다.

 

 

왜 갱신 이상이 발생하는걸까?

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

 

위 릴레이션에서 후보 키는 학번, 과목 애트리뷰트이다.

하지만, 강사 애트리뷰트가 과목을 결정하고 있기 때문에 갱신 이상이 발생하고 있다는 것이다.

 

갱신 이상을 해결하기 위해 우리는 BCNF을 만족해야 한다.

 

---

 

BCNF(Boyce-Codd Normal Form)

 

한 릴레이션 R이 BCNF를 만족할 필요 충분 조건은 다음과 같다.

 

릴레이션 R이 제 3 정규형을 만족하고,
모든 결정자가 후보 키이어야 한다.

 

즉, 결정은 키 밖에 할 수 없다는 뜻이다.

 

수강 릴레이션의 강사 애트리뷰트는 후보 키가 아님에도 불구하고 과목 애트리뷰트를 결정하기 때문에 BCNF가 아니다.

 

 

BCNF의 성질은 다음과 같다.

 

제 3 정규형을 만족하는 대부분의 릴레이션들은 BCNF도 만족한다.

하나의 후보 키만을 가진 릴레이션이 제 3 정규형을 만족하면 동시에 BCNF도 만족한다.

 

 

BCNF 정규화

 

제 3 정규형을 만족하는 릴레이션을 BCNF으로 정규화하려면 키가 아니면서 결정자 역할을 하는 함수적 종속성을 독립된 릴레이션으로 만들어야 한다.

 

기존 릴레이션에는 결정자를 남겨서 기본 키의 구성요소가 되도록 한다.

이 결정자는 새로운 릴레이션에 대한 외래 키 역할도 한다.

 

제 3 정규형을 만족하지만 BCNF는 만족하지 않는 릴레이션

 

제 3 정규형을 BCNF로 분해

 

 

위의 수강 릴레이션을 BCNF로 정규화하면 다음과 같다.

 

수강1 릴레이션 ( BCNF 만족 )

학번	강사
11002	이영준
11002	고성현
24036	엄영지
24036	조민형
11048	이영준

 

 

수강2 릴레이션 ( BCNF 만족 )

강사	과목
이영준	데이터베이스
고성현	운영체제
엄영지	자료구조
조민형	데이터베이스

 

 

---

 

정규형 요약