데이터베이스 설계와 정규화

데이터베이스를 설계하다 보면 어떻게 설계하는 것이 좋을지 고민해 본 적이 있을 것이다.

이번 시간에는 좋은 데이터베이스가 무엇인지 또 그것을 위한 가이드라인을 알아보고 정규화와 역정규화에 대해서도 알아보겠다.

글에 나오는 릴레이션은 테이블이라고 생각해도 된다.

좋은 데이터베이스 설계란?

좋은 데이터베이스를 설계하기 위해서는 아래 4가지 항목을 지키기 위해 노력해야 한다.

• 속성의 의미를 명확하게 표현하고 있어야 한다.
• 튜플의 중복값을 최소화하여야 한다.
• 튜플의 null 값도 최소화하여야 한다.
• 불필요한 튜플 생성 가능성을 허용해서는 안된다.

 

좋은 데이터베이스 스키마 설계를 위한 가이드라인

1) 하나의 엔티티 또는 릴레이션 타입에는 하나의 릴레이션 스키마를 설계해라!

• 하나의 스키마에 여러 개체나 관계 타임을 섞어 놓아서는 안된다.
• 이를 지키지 않는다면 중복된 데이터가 많이 생기게 된다.
• 예시) CustomerOrder 라는 테이블은 고객과 주문 정보를 함께 담고 있는 테이블이다. 
• 한 고객이 여러 주문을 하면 테이블에 데이터가 쌓이는데 그렇게 되면 고객의 정보가 중복돼서 저장이 된다.

2) 이상 현상(anomaly) 이 생기지 않도록 해야 한다.

여기서 이상 현상이란 테이블 설계할 때 잘못 설계하여 데이터 삽입, 삭제, 수정할 때 생기는 논리적 오류를 말한다.

 

EMP_DEPT (Ename, Ssn, Bdate, Address, Dnumber, Dname, Dmgr_ssn)

라는 직원과 부서 정보를 담고 있는 테이블이 있다고 가정하자.

• 삽입 이상 : 특정 데이터가 존재하지 않아 중요한 데이터를 데이터베이스에 삽입할 수 없을 경우 발생한다.
  • (null, null, null, null, 6, Security, null) 이런 데이터를 삽입하려고 하면 어떻게 될까?
  •  부서 정보를 삽입하려고 하는데 관련 없는 직원 정보 때문에 삽입이 되지 않을 것이다. (Ssn 이 기본키라서 삽입이 안된다.)
• 삭제 이상 : 특정 정보를 삭제하면, 원치 않는 정보도 삭제되는 현상이다.
  • 특정 직원을 삭제 하려 할 때 만약에 그 직원이 어떤 부서의 마지막 직원이라면 해당 부서도 함께 삭제되어 버리는 문제가 생겨버린다.
  • 관련 없는 정보도 함께 삭제되는 연쇄삭제 현상이 일어난다.
• 갱신 이상 : 테이블의 특정 데이터를 갱신했는데, 정상적으로 갱신되지 않을 경우를 말한다.
  • 같은 부서에 있는 직원들의 부서명은 모두 같아 중복된 데이터가 들어가 있을 것이다.
  • 이때 부서명이 바뀐다면? 해당 부서의 직원들의 부서 명을 전부 변경해줘야 한다. 만약 하나라도 놓친다면 문제가 생긴다.

3) 릴레이션에 null 값이 자주 들어가도록 하지 마라

• null을 피할 수 없는 특정 예외 상황에만 null을 허용해야 한다.
• null 이 빈번하면 공간 낭비, 속성의 의미, 조인 연산 문제, 집단 함수 수행 시 해석상의 모호함 그리고 null 자체도 여러 해석으로 인한 모호성 문제들이 생긴다.

4) 조인시 반드시 PK-FK 쌍의 동등 조건으로 수행하도록 해라

• PK-FK 쌍의 동등 조건으로 조인해야 잉여 튜플이 발생하지 않는 것을 보장할 수 있다.
• 잉여 튜플은 조인 결과 의도하지 않은 튜플을 말한다.

---

정규화와 역정규화

정규화란 테이블 설계 시 발생하는 데이터 중복과 이상 현상들을 제거하기 위해서 더 작은 테이블로 분해하는 과정을 말한다.

 

테이블을 어떻게 분해되는지에 따라 정규화 단계가 달라진다.

• 제1 정규화
• 제2 정규화
• 제3 정규화
• BCNF (BOyce-Codd normal form)
• 제4 정규화
• 제5 정규화

보통 제 4 정규화와 5 정규화는 드문 케이스로 제 3 정규형이 BCNF 인지 까지만 확인한다.

따라서 이번 시간에는 BCNF 까지만 정리하겠다.

 

그럼 정규화는 항상 좋을까?

 

정규화로 인해 테이블은 더 작은 테이블로 분해되게 된다. 

그로 인해 테이블 간의 조인 연산이 더 빈번하게 나타나게 되고 데이터의 조회하는 데 걸리는 시간이 늘어날 수 있다.

 

따라서 상황에 따라 읽기 작업이 많이 필요한 경우에는 다시 하나의 테이블로 합치기도 하는데 이를 역정규화라 한다.

 

즉 쓰기 작업이 많을 경우에는 데이터의 중복을 관리하기 위해 정규화 작업이 유리하고 읽기 작업이 많다면 역정규화를 하는 것이 유리할 것이다.

 

(참고) 일상의 예시

주인공이 철수인 소설을 쓴다고 가정해보자.

300 페이지가 넘는 소설을 다 작성했는데 갑자기 소설의 주인공 이름이 바뀌게 되면 어떻게 되는가? 

300 페이지를 다 뒤져가며 주인공 이름을 바꿔줘야 할 것이다. 

만약 소설에 철수라는 이름을 바로 쓰는 대신에 {설정집 1페이지의 주인공 이름 참고} 식으로 써준다면 설정집의 주인공 이름만 변경해 줬으면 됐을 것이다.

 

제1 정규화

테이블의 속성이 원자값(Atomic Value)을 가져야 한다.

 

 

(a)의 EMP_PROJ는 복합 속성을 갖고 있다. 따라서 이를 원자값을 갖도록 테이블을 변경해줘야 한다.

 

이를 해결하는 방법에는 아래 3가지 방법이 있다.

 

1. (c) 번처럼 복합 속성을 별도의 테이블로 분리하여 설계하는 방법이 있다. 기존 테이블의 PK 함께 붙여서 분리한다.

 

2. (b) 번 처럼 여러 속성으로 나누는 방법이다. null 이 많이 생기는 문제가 생긴다.

• ex) {Dname, Dnumber, Dmgr_ssn, Dlocation1, Dlocaton2, Dlocation3 ... } 

3. set value를 갖고 있다면 여러 튜플로 분리하는 방법이 있다. -> 중복이 많이 생기는 문제가 생긴다.

 

제2 정규화

키에 속하지 않는 모든 속성은 기본키의 완전 함수 종속 이어야 한다.

 

제 2 정규화를 이해하기 위해 함수적 종속성과 완전 함수 종속이 무엇인지 먼저 이해해 보자.

함수적 종속성과 완전 함수 종속

임의의 두 튜플에서 X 속성이 결정되면 Y 속성도 결정될 때 X를 결정자, Y를 종속자라 부른다.

예를 들면 주민번호를 알면 직원의 이름은 결정된다. 

 

수식 : Ssn -> Ename , Pnumber -> {Pname, Plocation}

 

• 스키마만 보고 함수적 종속성이 있다 없다는 판단할 수 없다.
• 함수적 종속성은 속성의 의미에 관련된 성질이다.
• 모든 데이터가 따라야 하는 조건이다.

WORKS_ON(Ssn, Pnumber, Hours)는 어떤 직원이 어떤 프로젝트에서 얼마나 일했는지 나타내는 테이블이다.

이때 Hours를 결정하기 위해서는 Ssn과 Pnumber 가 모두 필요하다. {Ssn, Pnumber} -> Hours

이런 경우를 완전 함수 종속이라고 한다.

 

EMPLOYEE(Ssn, Ename, Bdate ... )에서 {Ssn, Ename} -> Bdate 일 때 Bdate는 Ssn 만 있어도 결정될 수 있다.

이런 경우를 부분 함수 종속이라 한다.

 

다시 제2 정규화로 돌아와 아래 그림을 보자

 

 

위 그림은 속성의 종속성을 나타내는 그림 인다.

 

제2 정규형은 키에 속하지 않는 모든 속성은 기본키의 완전 함수 종속 이어야 한다.

하지만 (a) 테이블은 Hours를 제외한 다른 속성들은 모두 기본키에 대해 부분 함수 종속이다. 

따라서 완전 함수 종속을 갖도록 테이블을 분리해야 한다.

 

{Ssn, Pnumber} -> Hours

Ssn -> Ename

Pnumbe -> {Pname, Plocation}

 

이렇게 3개의 완전 함수 종속을 갖기 때문에 3개의 테이블로 나눌 수 있다.

 

제3 정규형

제2 정규형을 만족하면서 어떤 non-prime 속성도 기본키에 이행적 종속성을 가져서는 안 된다.

 

이행적 종속성 (Transitive Dependency)

X -> Z AND Z -> Y 이면 X -> Y 인 성질을 말한다.

 

위의 그림에서는 Ssn -> Dnumber이고 Dnumber -> Dname 이기 때문에 Ssn -> Dnumber이다.

 

non-prime 속성인 Dnumber 가 이행적 종속성을 갖기 때문에 이를 제거해 주기 위해 아래와 같이 테이블을 분리해야 한다.

 

 

BCNC (Boyce - Codd normal form)

제3 정규화에서 좀 더 엄격해진 버전이다.

제 3 정규화 에서 모든 결정자가 후보 키(candidate key)이어야 한다.

 

 

LOTS1A 테이블은 제3 정규형은 만족하지만 BCNF는 만족하지 않는다.

Area 가 후보 키가 아니지만 결정자 이기 때문이다. 

{County_name, Lot#} 은 후보 키 이기 때문에 결정자일 수 있다.

 

따라서 그림과 같이 LOTS1AX와 LOTS1AY 테이블로 나눠야 한다.