12. 정규화

---

 

 

1. 이상 현상

잘못 설계된 테이블로 삽입, 삭제, 수정 같은 데이터 조작을 하면 이상 현상이 일어난다.

이상 현상이란, 테이블에 투플을 삽입할 때, 부득이하게 NULL 값이 입력되거나, 삭제 시 연쇄 삭제 현상이 발생하거나, 수정 시 데이터 일관성이 훼손되는 현상을 의미한다.

위의 테이블은 필요한 정보를 검색할 때는 문제가 없지만, 튜플을 삭제, 삽입, 수정할 경우 문제가 발생한다.

● 삭제 이상

튜플 삭제 시, 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상.

장미란 학생이 스포츠경영학 수업의 수강을 취소하여 테이블에서 튜플을 삭제해야 할 때, 위의 테이블 설정대로 튜플을 그냥 삭제해버리면 장미란 선수의 기본 정보도 통째로 삭제되어 버린다.

이는 튜플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 연쇄 삭제 문제에 해당한다.

● 삽입 이상

튜플 삽입 시, 특정 속성에 해당하는 값이 없어서 NULL 값을 입력해야 하는 현상.

박세리 학생이 체육학과 신입생으로 들어와서 INSERT문으로 (학생번호, 학생이름, 학과, 주소) 정보를 삽입하였다.

아직 수강 신청은 하지 않은 상태여서 (강좌이름, 강의실) 속성에는 NULL이 입력되었다.

여기서 NULL 값은 특별한 값으로, 테이블에는 가능한 없어야 하는 값이다.

● 수정 이상

튜플 수정 시, 중복된 데이터의 일부분만 수정되어 데이터 불일치 문제가 일어나는 현상.

박지성 학생의 주소가 대한민국 서울로 바뀌었고, 2개 이상의 강좌를 수강했다고 가정하자.

보통 UPDATE 문을 사용해 주소를 수정할 경우 2군데 모두 값이 바뀌지만, 이때 1군데만 값이 바뀐다면 문제가 발생한다.

SELECT 문으로 값을 조회했을 때, 서로 다른 값이 조회되어 데이터의 일관성이 깨지게 된다.

 

---

 

2. 잘못 설계된 계절 학기 수강 테이블

DROP TABLE Summer; /* 기존 테이블이 있으면 삭제하고 새로 생성하기 위한 준비 */
CREATE TABLE Summer( 
	sid 	NUMBER,
	class 	VARCHAR2(20),
	price 	NUMBER
);
INSERT INTO Summer VALUES (100, 'FORTRAN', 20000);
INSERT INTO Summer VALUES (150, 'PASCAL', 15000);
INSERT INTO Summer VALUES (200, 'C', 10000);
INSERT INTO Summer VALUES (250, 'FORTRAN', 20000);
/* 생성된 Summer 테이블 확인 */
SELECT *
FROM Summer;

위의 코드를 통해 생성된 Summer 테이블은 SELECT 문을 이용한 조회 작업은 문제없이 수행할 수 있지만, 데이터를 조작하기 시작하면 이상 현상이 발생한다.

삭제 이상

• 200번 학생의 수강이 취소되었다고 가정한다.
• DELETE 문을 사용해 해당 튜플을 삭제할 경우, C 강좌에 대한 수강료 조회에 대한 정보도 한꺼번에 날아가버리는 연쇄 삭제 현상이 발생한다.

삽입 이상

• Java 강좌가 새로 개설되었고, 수강료는 25,000원이다.
• 막 개설되었기 때문에 아직 신청한 학생은 없는 상태이다.
• Java 강좌를 추가하기 위해 INSERT 문을 사용하는데, 학생이 없어서 sid에 NULL 값을 입력했다.

수정 이상

• FORTRAN 강좌의 수강료가 20,000원에서 15,000원으로 변경되었다.
• UPDATE 문을 사용해 값을 변경하였는데, 조건을 잘못 주게 되면 모든 값이 일괄적으로 변경되는 것이 아니라 특정 값만 변경되어 데이터 불일치 문제가 발생할 수 있다.

 

---

 

3. 제대로 설계된 계절 학기 수강 테이블

파트 2에서 살펴본 계절 학기 수강 테이블의 문제를 해결하였다.

먼저 Summer 테이블을 SummerPrice, SummerEnroll 테이블로 분리하였다.

SummerPrice 테이블은 과목에 대한 수강료 정보만을 저장한다.

SummerEnroll 테이블은 학생들의 수강 신청 정보만을 저장한다.

 

---

 

4. 함수 종속성

이상 현상이 발생하는 테이블을 수정하여 정상화시키는 과정을 정규화라고 부른다.

이 정규화를 위해서는 먼저 테이블을 분석하여 기본키와 함수 종속성을 파악해야 한다.

위의 릴레이션은 각 속성 사이에 의존성이 존재한다.

어떤 속성 A의 값을 알면, 다른 속성 B의 값이 유일하게 정해지는 의존 관계를 '속성 B는 속성 A에 종속한다' 혹은 '속성 A는 속성 B를 결정한다'라고 표현한다.

표기 시에는 'A -> B' 형태로 표기하며, A를 B의 결정자라고 부른다.

학생수강성적 릴레이션에서 종속 관계에 있는 예

• 학생번호 -> 학생이름
• 학생번호 -> 주소
• 강좌이름 -> 강의실
• 학과 -> 학과사무실

종속하지 않는 예

• 학생이름 -> 강좌이름
• 학과 -> 학생번호

종속하는 것처럼 보이지만 주의 깊게 보면 그렇지 않은 예

• 학생이름 -> 학과

종속 관계는 O 표기를, 종속 관계가 아니라면 X 표기를 했다.

수학에서 변수 x, y 사이에 x 값이 정해지면, y 값이 정해지는 관계를 'y는 x의 함수'라고 한다.

마찬가지로 '학생번호 -> 주소'와 같이 왼쪽 속성의 모든 값에 대하여 오른쪽 속성의 값이 유일하게 결정될 때, '함수적으로 종속한다'라고 부른다.

릴레이션의 속성 간에 함수적으로 종속하는 성질을 '함수 종속성' 혹은 '함수적 종속성'이라고 부른다.

여기서 학생번호에 해당하는 X는 결정자, 주소에 해당하는 Y는 종속 속성이라고 부른다.

함수 종속성은 보통 릴레이션 설계 때 속성의 의미로부터 정해진다.

 

4.1) 함수 종속성 다이어그램

함수 종속성 다이어그램은 함수 종속성을 나타내는 표기법이다.

릴레이션의 속성은 직사각형, 속성 간의 함수 종속성은 화살표, 복합 속성은 직사각형으로 묶어서 그린다.

 

4.2) 함수 종속성 규칙

X, Y, Z가 릴레이션 R에 포함된 속성의 집합이라고 할 때, 함수 종속성에 관한 다음과 같은 규칙이 성립한다.

위의 3가지 규칙을 통해 아래의 규칙을 얻을 수도 있다.

 

---

 

5. 정규화

정규화란, 이상 현상이 발생하는 릴레이션을 분해하여 이상 현상을 제거하는 과정이다.

이상 현상이 있는 릴레이션은 이상 현상을 일으키는 함수 종속성의 유형에 따라 등급을 구분할 수 있다.

릴레이션은 정규형 개념으로 구분하며, 정규형이 높을수록 이상 현상이 줄어든다.

 

5.1) 제1 정규형

릴레이션 R의 모든 속성 값이 원자 값을 가지면 제1 정규형이라고 부른다.

고객취미들(이름, 취미들) 릴레이션을 고객취미(이름, 취미) 릴레이션으로 바꿔서 저장하면, 제1 정규형을 만족한다.

 

5.2) 제2 정규형

릴레이션 R이 제1 정규형이고, 기본 키가 아닌 속성이 기본 키에 완전 함수 종속일 때, 제2 정규형이라고 부른다.

완전 함수 종속이란, A, B가 릴레이션 R의 속성이고 A -> B 종속성이 성립할 때, B가 A의 속성 전체에 함수 종속하고, 부분 집합 속성에 함수 종속하지 않는 경우를 말한다.

 

5.3) 제3 정규형

릴레이션 R이 제2 정규형이고 기본키가 아닌 속성이 기본키에 비 이행적(non-transitive)으로 종속할 때(직접 종속) 제3 정규형이라고 한다.

이행적 종속이란, A -> B, B -> C가 성립할 때 A -> C가 성립되는 함수 종속성이다.

학생 릴레이션을 아래와 같이 2개의 릴레이션으로 분해하여 제3 정규형을 만족시킨다.

● 학생 릴레이션

(학생번호, 학생이름, 학과, 주소)

● 학과 릴레이션

(학과, 학과사무실)

 

5.4) BCNF

릴레이션 R에서 함수 종속성 X → Y가 성립할 때 모든 결정자 X가 후보 키이면 BCNF 정규형이라고 한다.

학생 릴레이션을 아래와 같이 2개의 릴레이션으로 분해하여 BCNF을 만족시킨다.

● 학생 릴레이션

(학생번호, 학생이름, 학과, 주소)

● 학과 릴레이션

(학과, 학과사무실)

 

---

 

---

수고하셨습니다!

---