3. 데이터베이스 구축 - 논리 데이터베이스 설계(정규화와 반정규화)

87. 정규화 (Normalization)

: 잘못 설계된 릴레이션 스키마(관계형 스키마)를 더 작은 속성 집합으로 분해하여 바람직한 형태의 스키마로 변환하는 것

• 릴레이션 내 속성들 간의 종속성(dependency)를 분석하여 하나의 릴레이션은 하나의 종속성만 표현하도록 분해한다.
• 하나의 릴레이션이 여러 상이한 정보를 표현하여 많은 종속성을 가지게 되면 릴레이션 처리에 이상(anomaly)가 발생하고 정규화의 목적은 이상의 발생을 막는 것이다.
• 정규화는 데이터베이스의 논리적 설계 단계에서 수행한다.
• 정규화는 논리적 처리와 품질에 영향을 미친다.
• 장점
  • 정규화된 데이터 모델은 일관성, 정확성, 단순성, 안정성 등을 보장한다
  • 졍규화 수준이 높을수록 유연한 데이터 구축이 가능하고 데이터의 정확성이 증가한다.
• 단점
  • 정규화 수준이 너무 높으면 데이터의 물리적 접근이 복잡해지고 조회 성능이 저하된다.

 

- 정규화의 목적

• 데이터 구조의 안정성 및 무결성 유지
• 어떠한 릴레이션도 표현 가능
• 효과적인 검색 알고리즘 생성
• 이상(anomaly)의 발생 방지 및 저장 공간의 최소화
• 데이터 삽입 시 릴레이션 재구성 필요성 감소
• 데이터 모형의 단순화
• 속성의 배열 상태 검증이 가능
• 개체와 속성의 누락 여부 확인 가능
• 자료 검색과 추출의 효율성 증가

 

- 정규화의 원칙

• 정보의 무손실 표현 (Nonloss representation of information) : 한 스키마를 다른 스키마로 변환할 때 정보의 손실이 있어서는 안된다.
• 분리의 원칙 (principle of separation) : 독립된 하나의 관계성은 독립된 하나의 릴레이션으로 표현해야 한다.
• 최소의 데이터 중복성 (Minimal data redundancy) : 데이터 중복이 최소화되어야 한다.

 

- 이상 (Anomaly)

: 데이터베이스 내에서 데이터들이 불필요하게 중복되어 릴레이션 조작 시에 발생하는 예기치 못한 곤란한 현상.

 

1. 삽입 이상 (Insertion Anomaly)

: 릴레이션에 데이터를 삽입할 때 원하지 않는 값들도 함께 삽입되거나, 삽입해야하는 현상

 

• 위 릴레이션은 학생 정보를 담고 있으며, {과목 번호, 학번, 이름, 학년} 속성을 가진다.
• {과목 번호, 학번}은 기본 키이다.
• 위 릴레이션에 새로운 학생 정보를 삽입할 때 해당 학생이 과목을 하나도 등록하지 않았다면 기본 키에 속하는 과목 번호가 Null값이 되어 삽입할 수 없다. 

 

2. 삭제 이상 (Deletion Anomaly)

: 릴레이션에서 튜플을 삭제할 때 의도치 않은 값들도 함께 삭제되어 유지해야할 정보도 연쇄 삭제되고 따라서 정보의 손실이 일어나는 현상

 

• 위 릴레이션은 학생 정보와를 담고 있으며, {과목 번호, 학번, 이름, 학년} 속성을 가진다.
• {과목 번호, 학번}은 기본 키이다.
• Connie M. 학생이 E102 과목의 수강을 취소하여 해당 튜플을 삭제한다면, 해당 학생의 학적 정보도 함께 사라진다.

 

3. 갱신 이상(Update Anomaly)

: 릴레이션 내의 한 튜플의 속성 값을 갱신할 때 중복된 여러 튜플 중 일부 튜플만 값이 바뀌어서 정보에 모순이 일어나는 현상

 

• 위 릴레이션은 학생 정보와를 담고 있으며, {과목 번호, 학번, 이름, 학년} 속성을 가진다.
• {과목 번호, 학번}은 기본 키이다.
• Arim N. 학생의 이름이 바뀌어 Name 속성의 값을 바꾸어야 할때, 오류로 인해 두 개의 튜플 중 하나의 튜플만 이름 정보가 갱신되면 정보의 모순이 발생한다.

 

1) 제 1 정규형 (1NF)

: 릴레이션의 속한 모든 도메인이 원자값으로만 되어있는 정규형

 

제 1 정규화의 예시

• 가장 위쪽 릴레이션은 학생들의 전공 정보를 담고 있으며, 전공이 원자값으로 이루어져 있지 않으므로 제 1 정규형에 속하지 않는다.
• 위쪽의 릴레이션의 전공을 1전공과 2전공으로 나누어 두 개의 릴레이션으로 분해하였다.
• 분해한 두 릴레이션은 제 1 정규형에 속한다.

 

2) 제 2 정규형 (2NF)

: 제 1 정규형에 속하는 릴레이션 중 기본 키가 아닌 모든 속성이 기본 키에 대해 완전 함수 종속인 릴레이션

 

- 함수 종속 (Functional Dependency)

: 릴레이션 R에서 X와 Y를 R이 가진 속성들의 부분 집합이라고 할 때, 시간에 관계 없이 속성 X의 값 각각에 대해 속성 Y의 값이 하나만 연관되어 있을 때 Y는 X에 함수 종속이다. (X → Y)

• 즉, 튜플이 X 값이 같다면 Y의 값도 같아야 한다.
• X가 기본 키라면 릴레이션 R의 모든 속성 Y는 X에 함수 종속이어야 한다.
• X → Y 에서 X를 결정자(determinant), Y를 종속자(dependent)라고 한다. 

 

• {학번, 이름, 학년, 전공} 을 속성으로 가지는 위 릴레이션에서 학번 하나에 대해서 학생의 이름, 학년, 전공은 하나만 대응됨을 알 수 있다.
• 따라서 이름, 학년, 전공은 학번에 함수 종속이며 학번 → (이름, 학년, 전공) 으로 표시한다.

 

 

- 완전 함수 종속 (Full Functional Dependency)

: 릴레이션 R에서 X와 Y를 R이 가진 속성들의 부분 집합이라고 할 때, Y가 X에 함수 종속이면서 X의 진부분집합에는 함수 종속이 아닐 때 Y는 X에 완전 함수 종속이다.

 

• {과목 번호, 학번, 이름, 학년, 성적}을 속성으로 가지는 위 릴레이션 C가 있을 때,
  • '성적'은 기본 키인 {과목번호, 학번}에 함수 종속이며, 기본 키의 진부분집합인 '과목번호'나 '학번'에는 함수 종속이 되지 않는다. 따라서 '성적'은 {과목 번호, 학번}에 완점 함수 종속이다. 
• 부분 함수 종속 (Partial Functional Dependency) : Y가 X에 함수 종속이면서 X의 진부분 집합에도 함수 종속일 때.
  • 이때, X는 진부분집합이 존재해야 하므로, 두 개 이상의 속성으로 이루어진 결정자이다.
  • '이름'과 '학년'은 기본 키인 {과목번호, 학번}에 함수 종속이지만, 기본 키의 진부분집합인 '학번'에도 함수 종속이다. => '이름'과 '학년'은 {과목 번호, 학번}에 부분 함수 종속이다. 
• 위 릴레이션에서 나타나는 함수 종속을 다이어그램으로 나타내면 다음과 같다.

 

 

- 제 2 정규화

: 제 1 정규형이면서 제 2 정규형이 아닌 릴레이션은 올바른 프로젝트 연산을 통해 두 개의 제 2 정규형 릴레이션으로 분해할 수 있다.

• 이때 분해된 두 릴레이션은 자연 조인을 통해 다시 원래의 릴레이션으로 만들 수 있다.
• 무손실 분해 (Nonloss Decomposition) : 하나의 릴레이션을 정보 손실 없이 의미상 동등한 릴레이션들로 분해하는 것

 

• 가장 위쪽 릴레이션은 속성 '학년'이 릴레이션 기본 키인 {과목번호, 학번}에 완전 함수 종속이 아니므로 제 2 정규형에 속하지 않는다.
• 위쪽 릴레이션을 프로젝트를 통해 분해한 두 릴레이션은 기본 키가 아닌 모든 속성이 기본 키에 대해 완전 함수 종속을 만족하며 따라서 제 2 정규형 릴레이션이다.

 

3) 제 3 정규형 (3NF)

: 제 2 정규형에 속하는 릴레이션 중 기본 키에 속하지 않는 모든 속성이 기본 키에 대해 이행적 함수 종속이 아닌 릴레이션

 

- 이행적 함수 종속 (Transitive Functional Dependency)

: 릴레이션 내의 속성들의 부분 집합 A, B, C에 대해, 함수 종속 관계 A → C가 성립할 때, A → B와 B → C가 성립하는 경우 C는 A에 이행적 함수 종속이다.

• 즉, A가 B를 결정하면서, B가 C를 결정하게 되어, A가 C를 결정하는 관계가 성립하는 것이다.

 

• {학번, 이름, 학년, 지도 교수, 전공}을 속성으로 가지는 위 릴레이션 S가 있을 때,
  • '지도 교수'와 '전공'은 '학번'에 함수 종속이지만, 또한 '전공'이 '지도 교수'에 함수 종속이기도 하다.
  • 즉, '학번' → '전공'일 때, '학번' → '지도 교수'와 '지도 교수 → '전공'이 성립하므로 '전공'은 '학번'에 대해 이행적 함수 종속이다.
• 위 릴레이션이 나타내는 함수 종속을 다이어그램으로 나타내면 다음과 같다.

 

 

- 제 3 정규화

: 속성 A가 기본 키인 릴레이션에서 속성 집합 B, C에 대해 A → B, B → C, A → C가 성립한다면 {A, B}, {B, C}로 분해하는 것이 올바른 분해이다.

• 무손실 분해 : 제 2 정규형이면서 제 3 정규형이 아닌 릴레이션 역시 정보 손실 없이 의미상 동등한 제 3 정규형 릴레이션 들로 분해할 수 있다.

 

• 가장 위쪽 릴레이션은 속성 '전공'이 릴레이션 기본 키인 '학번' 에 이행적 함수 종속이므로 제 3 정규형에 속하지 않는다.
• 위쪽 릴레이션을 프로젝트를 통해 분해한 두 릴레이션은 기본 키가 아닌 모든 속성이 기본 키에 이행적 함수 종속이 아니므로 제 3 정규형에 릴레이션이다.

 

4) 보이스/코드 정규형 (BCNF; Boyce-Codd Normal Form)

: 함수 종속 관계에서 모든 결정자가 후보 키인 릴레이션

• 제 3 정규형은 후보 키가 여러 개이고 서로 중첩되는 경우에는 적용할 수 없는 정의이다.
• 따라서 일반적인 경우에도 적용할 수 있는 보이스/코드 정규형이 제안되었다.
• 강한 제 3 정규형 (Strong 3NF) : 보이스/코드 정규형에 속하는 모든 릴레이션이 제 3 정규형에 속하고, 그 역은 성립하지 않는다. 
• 모든 보이스/코드 정규형이 종속성을 가지는 것은 아니다. 그러나 종속성이 존재한다면 다음의 제약을 따른다.
  • 후보 키가 아닌 모든 속성은 후보 키에 대해 완전 종속이다.
  • 후보 키가 아닌 모든 속성은 자신이 포함되지 않은 모든 후보 키에 대해 완전 종속이다.
  • 후보 키가 아닌 속성은 결정자가 될 수 없다.

 

• {과목 번호, 학번, 교수}를 속성으로 가지는 릴레이션 C가 있을때,
  • '과목 번호'와 '학번'이 정해지면 '교수'를 알 수 있으므로 {과목번호, 학번}은 후보 키이며 기본 키로 선정되었다.
  • '교수'가 정해지면 그 교수가 담당하는 '과목 번호'를 알 수 있으므로 '과목 번호'는 '교수'에 함수 종속이다.

 

• 위 릴레이션이 나타내는 함수 종속을 다이어그램으로 나타내면 다음과 같다.

• '과목 번호'는 기본 키에 속하므로 기본 키가 아닌 속성 중에 이행적 함수 종속을 가지는 속성은 없다. 따라서 위 릴레이션은 제 3 정규형 릴레이션이다.
• 그러나 '교수'는 후보 키가 아닌 일반 속성이지만 '과목 번호'와 함수 종속 관계를 가지는 결정자이다.
• 따라서, 모든 결정자가 후보 키가 아니므로 위 릴레이션은 보이스/코드 정규형에는 속하지 않는다.

 

- 보이스/코드 정규화

 

• 가장 위쪽 릴레이션은 속성 '교수'가 '과목 번호'를 결정하는 결정자이므로 보이스/코드 정규형이 아니다
• 위쪽 릴레이션을 프로젝트를 통해 분해한 두 릴레이션은 후보 키가 아닌 결정자가 없으므로 보이스/코드 정규형 릴레이션이다.

 

5) 제 4 정규형 (4NF)

: 다치 종속 A -≫ B 가 성립하는 릴레이션 내에 모든 속성이 A에 함수 종속이라면 제 4 정규형에 속한다.

• 또는, 보이스/코드 정규형에 속하는 릴레이션 내의 모든 다치 종속이 함수 종속이라면 해당 릴레이션은 제 4 정규형에 속한다.
• 어떤 릴레이션이 제 4 정규형에 속한다면, 릴레이션 내에 종속성이 없거나, 함수 종속 만을 가지며, 결정자는 후보 키이다. 

 

- 다치 종속 (Multivalued Dependency)

: 릴레이션 내의 속성들의 부분 집합 A, B, C에 대해, 속성 쌍 (A, C) 값에 대응되는 B 값의 집합이 A 값에 만 종속되고 C 값에는 독립이라면 B는 A에 다치 종속이다. (A -≫ B)

• 다치 종속은 적어도 속성이 3개 이상인 릴레이션에만 존재한다.
• 다치 종속은 항상 쌍으로 존재한다. 즉, 릴레이션 R(A, B, C)에서 A -≫ B 가 성립하면 A -≫ C 도 동시에 성립한다. 
  • 이를 A -≫ B | C 로 표현한다.
• 다치 종속은 함수 종속의 일반형이다. 즉, 함수 종속은 대응되는 값의 집합이 항상 원소 하나 만을 가지는 특수한 형태의 다치 종속이다.

 

• {회사명, 팀장, 제품} 을 속성으로 가지는 릴레이션 C가 있을 때,
  • 속성 '회사명'이 결정되면 '팀장' 속성의 값의 집합이 정해진다.
  • 속성 '회사명'이 결정되면 '제품' 속성의 값의 집합이 정해진다.
  • 그러나 '팀장'과 '제품'은 아무런 관련이 없다.
• 따라서, '팀장'과 '제품'은 '회사명'에 다치종속되며, '회사명' -≫ '팀장' | '제품' 이라고 할 수 있다.

 

- 제 4 정규화

: [Fagin의 정리] 릴레이션 R(A,B,C)에 다치 종속 A -≫ B | C 를 만족하는 속성 집합 A, B, C 가 존재하기만 하면, 두 프로젝션 R1(A, B)와 R2(A,C)는 무손실 분해이다.

 

• 가장 위쪽 릴레이션은 '팀장'과 '제품' 속성이 후보 키인 '회사명' 속성에 다치 종속이면서 함수 종속은 아니므로 (즉, '회사명'에 의해 '팀장'과 '제품'의 값 하나가 아니라 값의 집합이 정해지므로) 제 4 정규형이 아니다.
• 위쪽 릴레이션을 프로젝트를 통해 분해한 두 릴레이션은 두 개의 속성만을 가져 다치 종속이 성립하지 않으므로 제 4 정규형 릴레이션이다.

 

6) 제 5 정규형 (5NF)

: 존재하는 모든 조인 종속이 후보 키를 통해서만 만족되는 릴레이션

 

- 조인 종속 (Join Dependency)

: 릴레이션 R의 속성에 대한 n개의 부분집합 A1, A2, ..., An이 있을 때, 이 릴레이션 R이 자신의 프로젝션 A1, A2, ..., An을 모두 조인한 결과와 똑같다면 R은 조인 종속 *(A1, A2, ..., An)을 만족한다.

• n-분해 릴레이션 : n개 이상의 릴레이션으로 분해해야만 무손실 분해가 가능한 릴레이션
  • 어떤 릴레이션 R이 조인 종속 *(A1, A2, ..., An)을 만족한다면, R은 n-분해 릴레이션이다.
  • 제 1 정규형에서 제 4 정규형까지는 모두 2-분해로 무손실 분해가 가능한 2-분해 릴레이션이다.

 

 

 

• {회사명, 팀장, 제품} 을 속성으로 가지는 위 릴레이션 C가 있을 때, C는 3분해 했을 때만 무손실 분해이며, 두 개의 릴레이션으로만 분해한다면 정보의 손실이 생긴다.
  • 2개의 릴레이션으로만 분해했을 때는 위와 같이 원래의 릴레이션에는 없는 위조 튜플이 생겨난다.
• 따라서, 릴레이션 C는 3개의 릴레이션으로 분해했을때만 모두 조인했을때 원본과 같아지는 3분해 릴레이션이다.

 

- 제 5 정규화

• 가장 위쪽 릴레이션은 후보 키({회사명, 팀장, 제품})을 통하지않은 조인종속이 존재하므로 제 5 정규형이 아니다.
  • 조인 종속에 참여하는 '회사명', '팀장', '제품'은 후보 키의 부분집합이지 후보 키 그 자체는 아니다.
• 위쪽 릴레이션을 프로젝트를 통해 분해한 세 릴레이션은 어떠한 조인 종속도 포함하지 않으므로 제 5 정규형 릴레이션이다.

---

88. 반정규화 (Denormalization)

: 시스템 성능 향상, 개발 및 운영상 편의성 등을 위해 정규화된 데이터 모델을 통합, 중복, 분리하는 것

• 의도적으로 정규화의 원칙을 위배하는 행위이다.
• 반정규화 수행 시 시스템 성능이 향상되고 관리 효율성은 증가하나, 데이터의 일관성 및 정합성이 저하될 수 있다.
• 과도한 반정규화는 오히려 성능을 저하시킨다.
• 반정규화를 결정할 때에는 데이터의 일관성 및 무결성과 데이터베이스의 성능과 단순화 중 우선순위를 택하여 결정한다.

 

1) 테이블 통합

: 두 테이블이 조인되는 경우가 많아서 하나의 테이블로 합쳐 사용하는 것이 성능 향상에 도움이 될 경우 수행하는 반정규화

• 두 테이블에서 발생하는 프로세스가 자주 처리되는 경우, 항상 두 테이블을 이용해 조회하는 경우에 고려한다.
• 종류 : 1:1 관계 테이블 통합, 1:N 관계 테이블 통합, 슈퍼타입/서브타입 테이블 통합
• 테이블 통합 시 고려 사항
  • 데이터 검색은 간편하지만, 레코드 증가로 인해 처리량이 증가할 수 있다.
  • 테이블 통합으로 인해 입력, 수정, 삭제 규칙이 복잡해질 수 있다.
  • 제약 조건 설계가 어렵다.

테이블 통합의 예시

 

 

2) 테이블 분할

: 테이블을 수직 또는 수평으로 분할하여 수행하는 반정규화

• 수평 분할 (Horizontal Partitioning) : 레코드를 기준으로 수행하는 분할
  • 레코드 별로 사용 빈도 차이가 큰 경우 사용 빈도에 따라 테이블을 분할한다.
• 수직 분할 (Vertical Partitioning) : 특정 속성을 기준으로 수행하는 분할
  • 갱신 위주의 속성 분할 : 갱신이 자주 일어나는 속성들을 분할하여 사용
  • 자주 조회되는 속성 분할 : 테이블에서 자주 조회되는 속성이 일부일 경우 분할하여 사용
  • 크기가 큰 속성 분할 : 이미지나 긴 텍스트 등 크기가 큰 속성을 분할하여 사용
  • 보안 적용 분할 : 테이블 내 특정 속성에만 보안을 적용할 수 없으므로 보안을 적용해야 하는 속성만 분할하여 사용
• 테이블 분할 시 고려사항
  • 기본 키의 유일성 관리가 어려워진다.
  • 분할된 테이블로 인해 수행 속도가 느려질 수 있다.
  • 데이터 검색에 중점을 두어 분할 여부를 결정한다.

수평 분할의 예시

 

3) 중복 테이블 추가

: 여러 테이블에서 데이터를 추출해서 사용해야 하거나, 다른 서버에 저장된 테이블을 이용해야 하는 경우 중복 테이블을 추가하는 반정규화

• 중복 테이블을 추가하는 경우
  • 정규화로 인해 수행 속도가 느려지는 경우
  • 많은 범위의 데이터를 자주 처리해야 하는 경우
  • 특정 범위의 데이터만 자주 처리해야 하는 경우
  • 처리 범위를 줄이지 않고는 수행 속도 개선이 어려운 경우
• 중복 테이블 추가 방법
  • 집계 테이블 추가 : 원본 테이블에 트리거를 설정하여 원본 테이블의 집계 데이터를 위한 테이블을 생성한다.
  • 진행 테이블 추가 : 이력 관리 등의 목적으로 추가하는 테이블
  • 특정 부분만을 포함하는 테이블 추가 : 테이블의 특정 부분만 사용되는 경우 해당 부분만을 위한 테이블을 생성한다.

 

4) 중복 속성 추가

: 데이터를 처리할 때 데이터를 조회하는 경로를 단축하기 위해 자주 사용되는 속성을 추가하는 반정규화

• 중복 속성을 추가하면 데이터 무결성 확보가 어렵고 추가 저장 공간이 필요해질 수 있다.
• 중복 속성으로 추가하는 속성
  • 조인이 자주 발생하는 속성
  • 접근 경로가 복잡한 속성
  • 접근 조건으로 자주 이용되는 속성
  • 기본 키의 형태가 적절하지 않거나 다중 속성인 경우
• 중복 속성 추가 시 고려 사항
  • 테이블 중복과 속성의 중복을 고려한다.
  • 데이터 일관성 및 무결성에 유의한다.
  • SQL 그룹함수를 이용해 처리가 가능해야 한다.
  • 저장 공간이 지나치게 낭비되지 않는지 고려한다.