[DB] 정규화

이상현상

 

잘못 설계된 테이블로 삽입, 삭제, 수정 같은 데이터 조작을 하면 이상현상이 일어난다. 이상현상이란 테이블에 튜플을 삽입할 때 부득이하게 NULL 값이 입력되거나, 삭제 시 연쇄삭제 현상이 발생하거나, 수정 시 데이터의 일관성이 훼손되는 현상을 말한다. 

 

이상현상 개념

건물을 지을 때 설계가 잘못되면 건물에 균열이 생겨 이용이 불편하거나 심지어 건물이 붕괴될 수 있다. 그렇다면 데이터베이스의 설계가 잘못되면 어떤 문제가 발생할까? 데이터베이스 설계가 잘못되면 이상현상, 즉 SQL 문의 결과가 틀리거나 원하는 결과가 나오지 않는 등의 문제가 발생한다.

 

학생번호	학생이름	학과	주소	강좌이름	강의실
501	박지성	컴퓨터과	영국 맨체스타	데이터베이스	공학관 110
401	김연아	체육학과	대한민국 서울	데이터베이스	공학관 110
402	장미란	체육학과	대한민국 강원도	스포츠경영학	체육관 103
502	추친수	컴퓨터과	미국 클리브랜드	자료구조	공학관 111
501	박지성	컴퓨터과	영국 맨체스타	자료구조	공학과 111

 

학생수강 테이블에서 필요한 정보를 찾을 때는 문제가 없다. 예를 들어 박지성 학생이 수강하는 과목이나 장미란 학생의 소속학과는 SELECT 문을 통해 찾을 수 있다. 그러나 삭제, 삽입, 수정 등 테이블에 저장된 내용을 조작할 때 문제가 발생한다.

 

삭제이상

장미란 학생이 스포츠 경영학과를 취소하여 DELETE 문으로 세 번째 튜플을 삭제한면 강좌 이름만 사라지는 것이 아닌 장미란 학생의 모든 정보가 사라지는 일이 발생한다.

-> 연쇄삭제 문제 발생

 

삽입이상

박세리 학생이 체육학과에 입학하여 INSERT 문으로 (학생번호, 학생이름, 학과, 주소) 정보를 삽입하였다. 그런데 아직 수강신청은 하지 않은 상태라 (강좌이름, 강의실) 속성은 NULL값이 들어갔다. 여기서 NULL 값의 경우 특별히 처리되므로 가능한 테이블이 없어야 한다. 삽입이상이란 튜플 삽입 시 특정 속성에 해당하는 값이 없어 NULL 값을 입력해야 하는 현상이다.

 

수정이상

박지성 학생의 주소가 대한민국 서울로 바뀌었다. 보통 UPDATE 문으로 주소를 수정할 경우 두 군데 모두 바뀌지만, 만약 한 군데만 바뀐다면 문제가 된다. SELECT 문으로 조회했을 때 두 군데가 조회되어 데이터의 일관성이 깨지는 불일치 문제가 발생하기 때문이다. 수정이상이란 튜플 수정 시 중복된 데이터의 일부만 수정되어 데이터의 불일치 문제가 일어나는 현상이다.

 

함수 종속성

 

함수 종속성의 개념

 

이상현상이 발생하는 테이블을 수정하여 정상으로 만드는 과정을 정규화라고 한다. 정규화를 하기 위해서는 먼저 테이블을 분석해 기본키와 함수 종속성을 파악해야 한다.

 

학생번호	학생이름	학과	주소	학과 사무실	강좌이름	강의실	성적
501	박지성	컴퓨터과	영국 맨체스타	공학관 101	데이터베이스	공학관 110	3.5
401	김연아	체육학과	대한민국 서울	체육관 101	데이터베이스	공학관 110	5.0
402	장미란	체육학과	대한민국 강원도	체육관 101	스포츠경영학	체육관 103	3.5
502	추친수	컴퓨터과	미국 클리브랜드	공학관 101	자료구조	공학관 111	4.0
501	박지성	컴퓨터과	영국 맨체스타	공학관 101	자료구조	공학과 111	3.5

 

학생수강성적 테이블이다. 각 속성 사이에는 의존성이 존재하는데 예를 들어 강좌이름이 데이터베이스인 경우 강의실은 공학관 110호 한 곳뿐이다. 반면 학생이름이 박지성인 경우 강좌이름은 데이터베이스도 있고 자료구조도 있다. 강의실은 강좌이름만 알면 바로 알 수 있지만 강좌이름은 학생이름만으로 바로 알 수 없다.

 

이와 같이 어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 의존관계를 속성 B는 A에 종속한다. 혹은 속성 A는 속성 B를 결정한다라고 한다. 이 관계를 A -> B로 표기하며, A는 B의 결정자라고 한다. 학생수강성적 릴레이션에서 종속관계에 있는 예는 다음과 같다.

 

종속관계

1. 학생번호 -> 학생이름
2. 학생번호 -> 주소
3. 강의이름 -> 강좌
4. 학과 -> 학과 사무실

비종속 관계

1. 학생이름 -> 강좌이름
2. 학과 -> 학생번호

종속같지만 종속이 아닌 예

1. 학생이름 -> 학과

학생이름은 현재 상태에서는 바로 학과를 알 수 있지만 동명이인이 다른 학과에 생기는 경우 학생이름만으로 학과를 알 수 없게 된다.

 

정규화

 

이상현상의 원인은 여러 가지가 있는데, 대부분 두 가지 이상의 정보가 한 릴레이션에 저장되어 있기 때문에 발생한다. 따라서 이상현상은 릴레이션을 분해하여 제거한다. 분해된 릴레이션에 이상현상이 남아 있다면 이상현상이 없어질 때까지 분해한다. 이상현상이 발생하는 릴레이션을 분해하여 이상현상을 없애는 과정을 정규화라고 한다.

 

 

정규화 과정

이상현상이 있는 릴레이션은 이상현상을 일으키는 함수 종속성의 유형에 따라 등급을 구분할 수 있다. 예를 들면 하단 그림과 같이 이동수단의 유형에 따라 안전도 등급을 구분할 수 있는 것과 비슷하다. 릴레이션은 정규형이라는 개념으로 구분한다. 정규형이 높을수록 이상현상은 줄어든다.

 

 

제 1 정규형

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

관계 데이터베이스에서 릴레이션의 속성 값은 반드시 원자값이여야 한다. 하단 릴레이션을 보면 추신수 선수의 취미는 영화, 음악이다. 이것은 원자 값이 아니다. 속성 값이 원자값이 아닐 경우 원자값을 갖도록 변환해주어야 한다.

 

고객취미들 릴레이션을 고객취미 릴레이션으로 바꾸어 저장하면 제 1 정규형에 부합하게 된다.

 

정리 : 릴레이션의 값들을 단일 값으로 바꿔 나중에 수정, 탐색, 삭제에 편의성 증가한다.

 

 

제 2정규형

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

제 2정규형은 릴레이션의 기본키가 복합키일 때, 복합키의 일부분이 다른 속성의 결정자인지 여부를 판단하는 것이다.

 

제 2정규형을 이해하기 위해서 완전 함수 종속의 개념을 이해해야 한다.

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

 

반면 A -> B 종속성에서 A의 속성 일부를 제거해도 종속성이 여전히 성립하는 경우 불완전 함수 종속 혹은 부분 함수 종속이라고 한다.

수강강좌 릴레이션

 

이상현상

수강강좌 릴레이션의 기본키는 (학생번호, 강좌이름)이며, 기본키의 일부인 강좌이름 속성이 강의실을 결정하는 '강좌이름 -> 강의실' 종속관계를 가지고 있다.

 

• 삭제이상 : 402번 학생이 수강을 취소하면 스포츠경영학 과목의 강의실 정보가 사라진다.
• 삽입이상 : 컴퓨터입문 과목이 개설되어 공학관 112호를 사용하게 됐지만 아직 학생이 없어 학생번호와 성적을 NULL 처리해야 한다.
• 수정이상 : 데이터베이스 강의실을 공학관 113호로 변경할 경우 데이터 불일치가 발생할 가능성이 있다.

이상현상의 원인

이상현상의 원인은 수강강좌 릴레이션의 함수 종속성 다이어그램을 보면 알 수 있다. 수강강좌 릴레이션의 기본키는 (학생번호, 강좌이름)이고, 기본키가 아닌 속성은 성적, 강의실이다. 성적과 강의실은 모두 기본키(학생번호, 강좌이름)의 부분집합인 강좌이름에 종속된다. 이와 같이 기본키가 아닌 속성이 기본키에 완전 함수 종속이 아닌 불완전 함수 종속되어 있으면 이상현상이 발생한다. (학생번호, 강좌이름) -> 강의실 종속성의 경우 학생번호를 제거해도 '강좌이름 -> 강의실' 종속성이 여전히 성립된다. 

 

이것을 해결하려면 강의실을 다른 릴레이션으로 분리해 버리면 된다. 기존에 응집되어 있던 값을 분리시키면서 제 2정규형으로 변환시킨다

 

정리 : 현재 테이블과 관계가 없는 값을 다른 릴레이션으로 분리한다. 데이터를 수정하는 과정이 더 편리하지만 릴레이션이 분리되었기 때문에 해당 릴레이션을 한번 호출해야 되는 단점이 있다. 

 

릴레이션 분리를 통한 해결

 

제 3정규형

정의 : 릴레이션 R이 제 2정규형이고 기본키가 아닌 속성이 기본키에 비이행적으로 종속할 때 제 3정규형이라고 한다. 이행적 종속이란

A -> B, B -> C가 성립할 때 A -> C가 성립되는 함수 종속성을 말한다.

제 3정규형은 속성들이 이행적으로 종속되어 있는지 여부를 판단하는 것이다.

 

계절학기 릴레이션이다. 해당 릴레이션에서는 학성번호, 강좌이름, 수강료를 저장하는데 계절학기 특성상 학생은 하나의 강의만 신청할 수 있다고 가정해 보자.

 

이상현상

이상현상은 릴레이션의 기본키가 아닌 강좌이름이 수강료를 결정해 버리는 종속관계에서 발생한다.

 

• 삭제이상 : 402 학생이 수강을 취소하면 스포츠경영학 정보가 사라지게 된다.
• 삽입이상 : 컴퓨터입문이라는 강의를 추가하면 신청한 사람이 없기 때문에 학생번호에 NULL 값이 들어간다.
• 수정이상 : 데이터베이스에 수강료를 15,000원으로 변경할 경우 데이터 불일치가 발생할 수 있다.

 

이상현상 원인

이상현상의 원인은 계절학기 릴레이션의 함수 종속성 다이어그램을 보면 알 수 있다. 계절학기 릴레이션의 기본키는 학생번호이고, 기본키가 아닌 속성은 강좌이름, 수강료이다. 강좌이름, 수강료는 모두 기본키에 함수적으로 종속되어 있다. 여기서 문제는 수강료는 강좌이름에 한 번 더 종속되어 있다는 것이 문제이다.

 

제 3정규형으로 변환

 

문제를 해결하려면 릴레이션을 분리해야 된다 여기서 문제는 수강료가 중복 종속되어 있다는 게 문제이다. 이것을 강좌이름에 묶어 새로운 릴레이션으로 분리해 생성하면 해결할 수 있을 것이다.

 

 

 

정리 : 기본키가 하나인 경우 일반 칼럼에만 종속된 칼럼은 다른 테이블로 분리한다. 데이터 수정, 삭제 등의 편의성이 증가한다. 변경 전에는 데이터의 중복 값이 1~n 점점 커질수록 데이터 수정하는 반복작업량이 커지게 된다