데이터베이스를 설계하기 위한 과정은 다음과 같습니다.
1. 요구분석
2. 개념적 설계
3. 논리적 설계
4. 물리적 설계
고객의 요구사항을 분석하여 추상화 과정을 통해 개념적 설계를 합니다.
개념적 설계의 바탕은 세상의 만물을 Entity(개체)와 Relationship(관계)로 표현하는 것이며
Entity와 Relationship은 각각 속성(property)을 가질 수 있습니다.
예를들면 학생, 교수, 학과 등이 Entity가 되고 지도교수관계, 소속학과 관계 등이 Relationship이 됩니다.
이렇게 Entity와 Relationship으로 모델링하는 방법을 ER Model 이라고 합니다.
(이때 ER diagram을 그리게 됩니다.)
이렇게 추상화 과정을 통해 ER Model을 완성하면 논리적 설계를 하게 되는데
일반적으로 Entity와 Relationship을 테이블(Relation) 형태로 매핑하게 됩니다.
예를 들어 학생의 경우,
학생의 속성이 학번, 이름, 나이, 주소, 전화번호 등이 되고
학생 테이블
| 학번 |
이름 |
나이 |
주소 |
전화번호 |
이를 좀 더 간단히 표현할 경우,
학생(학번, 이름, 나이, 주소, 전화번호)
와 같이 설계를 합니다.
이와 같이 설계를 하면 때때로 여러 테이블에 중복된 데이터가 저장되는 경우가 생깁니다.
이 경우 데이터를 삽입, 수정, 삭제 할때 이상증상(Anomaly)이 발생하게 되는데
대표적인 경우가 두 테이블에 동일한 정보가 저장되어 있고
한쪽 테이블에만 데이터가 수정되었을때 두 테이블에서 얻는 정보가 서로 상의하게 되는 증상입니다.
즉, 한쪽 테이블에는 이름=브라운, 나이=20 이라고 기록되어 있고
다른 테이블에는 이름=브라운, 나이=21와 같은 경우입니다.
이때, 어떤 데이터가 맞는 것인지 알 수 없게 되어버립니다.
이러한 증상을 없애기 위해서 정규화(Nomalization)라는 과정을 거치게 됩니다.
정규화는 기본적으로 하나의 테이블을 여러개의 테이블로 분리시키는 과정이므로
정규화가 진행될 수록 테이블 수가 늘어나게 됩니다.
정규화의 장점은 위와 같이 중복된 데이터를 제거하고 각종 이상증상을 없애는데 있지만
이에 반해 테이블 수가 늘어나게 되어 "조회"시에 여러 테이블을 참조해야만 하므로
"조회" 성능이 저하되는 단점이 있습니다.
그런데 이 JOIN 명령이 테이터베이스에서 가장 부하가 많이 걸리는 작업 중에 하나입니다. 참고로 JOIN 작업을 줄이기 위해서 Subquery (쿼리 속의 쿼리)를 사용하기도 합니다.
따라서 정규화 과정은 대체적으로 성능과 이상증상의 제거를 서로 고려하여 적당한 선에서 결정합니다. (Trade off)
정규화는 1NF, 2NF, 3NF, BCNF, 4NF, 5NF 등 많이 있으나 (NF=Normal Form, 정규형)
성능 등을 고려하여 적당히 3NF 또는 BCNF 정도에서 멈추게 됩니다.
(성능을 위해 이미 정규화가 진행된 테이블을 서로 합치는 것을 역정규화라고 합니다.)
1. 1NF : 모든 속성은 원자값을 가진다.
예를들어 이름에 두가지 이름을 넣을 수 없다는 뜻입니다.
반드시 한 속성에는 하나의 값만 넣을 수 있습니다.
이것만 만족하면 1NF를 충족합니다.
2. 2NF : Key 외의 다른 속성들은 Key에만 종속적이어야 한다. (완전 함수 종속)
즉, Key의 일부 속성에 의해 결정되는 속성 들이 있으면 안됨.
Key : 테이블에서 많은 인스턴스(튜플, 레코드, Row) 중에서 단 하나의 레코드를 유일하게 특정 지을 수 있는 속성 혹은 속성의 조합
결정자 : 다른 속성의 값을 결정지을 수 있는 속성
* 결정자 중에서 모든 속성을 결정지을 수 있는 경우 Key
3. 3NF : 이행 종속이 있으면 안됨
단, Key가 아닌 어느 속성이 Key의 일부를 결정지을 수 있다면 3NF를 만족
이행 종속 : A->B, B->C 결정하는 경우 A->C를 결정
4. BCNF : 결정자는 반드시 Key 이어야 하며 키가 아닌 결정자는 있을 수 없음
BCNF는 2NF, 3NF와 무관하게 1NF에서 바로 생성가능. BCNF는 3NF를 만족하나 모든 3NF가 BCNF는 아님. BCNF는 3NF의 부분집합.
예) 학교(학번, 학생이름, 학년, 학과, 수강과목, 학점, 교수, 개설학과)
조건 : 전공은 하나. 교양 및 다른과 수업 수강 가능
1NF 정규화
* 1NF : 각 속성은 반드시 원자값을 가지므로 1NF 만족
2NF 정규화
Key는 {학번, 수강과목) : 이 두가지 속성으로 하나의 레코드를 결정할 수 있음
학번->이름, 학년, 학과
과목->학점, 교수, 개설학과
(교수->개설학과)
학번은 이름, 학년, 학과를 결정
과목은 학점, 교수, 개설학과를 결정
따라서 Key의 일부 속성에 의해 결정되는 속성들이 존재하므로 2NF 불만족
=> Key에만 종속되도록 분리
학생(학번, 이름, 학년, 학과)
수강과목(과목, 학점, 교수, 개설학과)
수강(학번,과목) : 이 테이블은 테이블 분리로 인한 두 테이블간의 관계를 표현하기 위해 생성됨 (N:M 관계)
* 이제 모든 속성들이 Key에만 종속됨. 2NF 만족
3NF 정규화
수강과목(과목, 학점, 교수, 개설학과) 에서 교수가 개설학과를 결정지음 (이행 종속)
(교수는 개설학과에 소속되어 있으므로)
=> 이행종속을 제거
수강과목(과목, 학점, 교수)
교수(교수, 개설학과)
모든 테이블을 모으면,
학생(학번, 이름, 학년, 학과)
수강과목(과목, 학점, 교수)
교수(교수, 개설학과)
수강(학번,과목)
* 이행 종속이 없으므로 3NF 만족
1NF에서 곧바로 BCNF 정규화
모든 결정자는 Key이어야 함.
학번->이름, 학년, 학과
과목->학점, 교수
교수->개설학과
따라서 결정자를 기준으로 테이블 분리
학생(학번, 이름, 학년, 학과)
수강과목(과목, 학점, 교수)
교수(교수, 개설학과)
수강(학번,과목)
이 예는 3NF이면서 BCNF를 만족
3NF이면서 BCNF가 아닌 예
ABCD 속성을 가진 테이블에서
ABC : Key
ABC->D, D->A
Key 이외의 속성 D는 Key에 의해서만 결정됨 : 3NF 만족
그러나 결정자 D는 Key가 아님 : BCNF 아님