데이터베이스 개론과 실습 (MySQL로 배우는) | Chapter 07 정규화

Aug, 2023

01. 이상현상

이상현상의 개념

• 이상현상(anomaly): 테이블에 투플을 삽입할 때 부득이하게 NULL 값이 입력되거나, 삭제 시 연쇄삭제 현상이 발생하거나, 수정 시 데이터의 일관성이 훼손되는 현상
  • 잘못 설계된 테이블로 삽입, 삭제, 수정 같은 데이터 조작을 하면 이상현상이 일어난다
• 이상현상의 종류
  • 삭제이상(deletion anomaly): 투플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
  • → 연쇄삭제(triggered deletion) 문제 발생
  • 삽입이상(insertion anomaly): 투플 삽입 시 특정 속성에 해당하는 값이 없어 NULL 값을 입력해야 하는 현상
  • → NULL 값 문제 발생
  • 수정이상(update anomaly): 투플 수정 시 중복된 데이터의 일부만 수정되어 데이터의 불일치 문제가 발생하는 현상
  • → 일관성없음(Inconsistency) 문제 발생

02. 함수 종속성

1. 함수 종속성의 개념

• 정규화(normalization): 이상현상이 발생하는 테이블을 수정하여 정상으로 만드는 과정
• 정규화를 하기 위해서는 먼저 테이블을 분석하여 기본키와 함수 종속성을 파악해야 한다
• ‘속성 B는 속성 A에 종속한다(=속성 A는 속성 B를 결정한다)’:어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 의존 관계
  • 표기: A → B
  • A는 B의 결정자
• 함수 종속성(FD, functional dependency, =함수적 종속성): 릴레이션의 속성 간에 함수적으로 종속하는 성질
  • 왼쪽 속성의 모든 값에 대하여 오른쪽 속성의 값이 유일하게 결정될 때, ‘함수적으로 종속한다’라고 한다
  • 릴레이션 R과 R에 속하는 속성의 집합 X, Y가 있을 때, X 각각의 값이 Y의 값 한 개와 대응이 될 때 ‘X는 Y를 함수적으로 결정한다’라고 하고, X → Y로 표기한다
  • 이때 X를 결정자(determinant)라고 하고, Y를 종속 속성(dependent attribute)이라고 한다
  • 함수 종속성은 보통 릴레이션 설계 때 속성의 의미로부터 정해진다

2. 함수 종속성 다이어그램

함수 종속성 다이어그램(functional dependency diagram): 함수 종속성을 나타내는 표기법

• 릴레이션의 속성을 직사각형, 속성 간의 함수 종속성을 화살표로 나타내고, 복합 속성의 경우 직사각형으로 묶어서 그린다

3. 함수 종속성 규칙(functional dependency rule)

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

• 부분집합(subset) 규칙: if Y ⊆ X, then X → Y
• 증가(augmentation) 규칙: if X → Y, then XZ → YZ
• 이행(transivity) 규칙: if X → Y and Y → Z, then X → Z
• 위 3가지 규칙에서 파생된 규칙
  • 결합(union) 규칙: if X → Y and X → Z, then X → YZ
  • 분해(decomposition) 규칙: if X → YZ, then X → Y and X → Z
  • 유사이행(pseudotransitivity) 규칙: if X → Y and WY → Z, then WX → Z

4. 함수 종속성과 기본키

• 릴레이션의 함수 종속성을 파악하기 위해서는 우선 기본 키를 찾아야 한다
• 기본키가 함수 종속성에서 어떤 역할을 하는지 알면 이상현상을 제거하는 정규화 과정을 쉽게 이해할 수 있다

릴레이션 R(K, A1, A2, A3,,, An)에서 K가 기본키이면, K → R 이 성립한다. 즉 기본 키는 릴레이션의 모든 속성에 대해 결정자이다.

5. 이상현상과 결정자

• 이상현상은 한 개의 릴레이션에 두 개 이상의 정보가 포함되어 있을 때 나타난다
• 릴레이션의 분해를 통해 이상현상을 해결할 수 있다
  • 부분 릴레이션의 결정자는 원래 릴레이션에 남겨두어야 한다
  • → 분해된 부분 릴레이션이 원래 릴레이션과 관계를 형성하기 위해서
• 최종적으로 분해된 릴레이션에서는 더 이상 이상현상이 발생하지 않는다
• 데이터 간의 관계를 유지하는 공통 속성을 가지고 있기 때문에 최종적으로 분해된 릴레이션에는 원래 릴레이션의 정보는 모두 포함된다

03. 정규화

정규화(normalization): 이상현상이 발생하는 릴레이션을 분해하여 이상현상을 없애는 과정

1. 정규화 과정

• 이상현상이 있는 릴레이션은 상현상을 일으키는 함수 종속성의 유형에 따라 등급을 구분할 수 있다
• 릴레이션은 정규형이라는 개념으로 구분한다
  • 정규형이 높을수록 이상현상은 줄어든다

정규형의 종류

• 제 1정규형: A relation in which the intersection of each row and column one and only one value(릴레이션의 R의 모든 속성 값이 원자값을 가지면 제 1정규형이라고 한다)
  • 관계 데이터베이스에서 릴레이션의 속성 값은 반드시 원자값이어야 하며, 속성 값이 원자값이 아닐 경우 원자값을 갖도록 변환해주어야 한다
• 제 2정규형: A relation that is in first normal form and every non-primary key attribute is fully functionally dependent on the primary key(릴레이션 R이 제 1정규형이고 기본키가 아닌 속성이 완전 함수 종속일 때 제 2정규형이라고 한다)
  • 완전 함수 종속(full functional dependency): A와 B가 릴레이션 R의 속성이고 A → B 종속성이 성립할 때, B가 A의 속성 전체에 함수 종속하고 부분 집합 속성에 함수 종속 하지 않을 경우 완전 함수 종속이라고 한다
  • 부분 함수 종속(partial functional dependency): A → B 종속성에서 A의 속성 일부를 제거해도 종속성이 여전히 성립하는 경우 불완전 함수 종속 혹은 부분 함수 종속이라고 한다
  • ex) (A1, A2) → B 종속성에서 A2를 제거했는데도 A1 → B가 여전히 성립한다면 불완전 함수 종속이다
  • 이상현상의 원인: 기본키가 아닌 속성이 기본키에 완전 함수 종속이 아닌 불완전 함수 종속되어 있으면 이상현상이 발생한다
• 제 3정규형: A relation that is in first and second normal and in which no non-primary key is transitively dependent on the primary key(릴레이션 R이 제 2정규형이고 기본키가 아닌 속성이 기본키에 비이행적(non-transitive)으로 종속할 때(직접 종속) 제 3정규형이라고 한다. 이행적 종속이란 A → B, B → C가 성립할 때 A → C가 성립되는 함수 종속성을 말한다)
  • 이상현상의 원인: 어떤 속성이 기본키에 이행적으로 종속된 경우 이상현상이 발생한다
• BCNF(Boyce Codd Normal Form): 릴레이션에 존재하는 함수 종속성에서 모든 결정자가 후보키이면, BCNF 정규형이다
  • 이상현상의 원인: 결정자이면서 후보키가 아닌 속성이 존재하면 이상현상이 발생한다

2. 무손실 분해

• 릴레이션을 분해할 때는 분해된 릴레이션 간의 관계를 유지하기 위해 분해된 릴레이션에 공통 속성을 한 개 이상 두어야 한다
• 공통 속성은 분해된 릴레이션을 다시 원래의 릴레이션으로 합성(조인)할 때 사용한다
• 무손실 분해(lossless-koin decomposition): 릴레이션 R을 릴레이션 R1과 R2로 분해할 때, R1 ⋈ R2 = R이면 무손실 분해라고 한다
  • 무손실 분해를 위한 조건은 R1 ∩ R2→ R1이나 R1 ∩ R2 → R2 중 하나를 만족해야 한다(분해할 때 공통된 속성 (R1 ∩ R2)이 릴레이션 R1의 키이거나 (R1 ∩ R2→ R1) 혹은 릴레이션 R2의 키(R1 ∩ R2→ R2)여야 한다)

3. 정규화 정리

• 정규형의 종류: 정규화되거나 되지 않은 모든 릴레이션부터 제 1 정규형, 제 2 정규형, 제 3 정규형, 제 4 정규형(다치종속성(multivated dependency)을 가진 릴레이션에 관한 내용), 제 5 정규형(프로젝트-조인 정규형, project-join normal form(PJ/NF), 조인종속성을 가진 릴레이션에 관한 내용)
• 대부분 릴레이션은 BCNF까지 정규화하면 실제적인 이상현상은 없어지므로, 보통 BCNF까지 정규화를 진행한다
• 정규형 상태인 릴레이션 R1을 분해하여 만든 R2, R3 릴레이션은 R1 다음 정규형이 아닌 더 높은 정규형이 될 수 있다
• ex) 이행적 종속성이 있는 릴레이션을 분해하여 두 개의 릴레이션을 만들었을 때 분해된 릴레이션은 제 3정규형이 아닌 BCNF 혹은 제 5정규형이 될 수 있다

---

ERD Practice

ER 모델 요구사항

회원(Member)
MEMBER_ID, 이름, 주소(city, street, zipcode)를 가진다.

주문(Orders)
ORDER_ID, 외래키 2개, 주문 날짜를 가진다.

주문상품(OrderItem)
ORDER_ITEM_ID, 외래키 2개, 주문 금액( orderPrice ), 주문 수량( count ) 을 가진다.

상품(Item)
ITEM_ID, 이름, 가격, 재고수량( stockQuantity )을 가진다.

카테고리(Category)
CATEGORY_ID, 이름을 가진다.

배송(Delivery)
DELIVERY_ID, 주소(city, street, zipcode)를 가진다.

연관 관계
회원과 주문
주문상품과 주문
주문상품과 상품
상품과 카테고리 -> 다대다 관계입니다!
주문과 배송

ER 모델 구현

 

References

ERD / 맥북 Mac ERD 그리기 & 윈도우 ERwin 다운로드 설치

DB구현-EER 모델링 관계 정의