[SQL] 데이터베이스 설계(2)

🚀 1. 정규화의 개념과 이상 현상

• 관계 데이터 모델에 기반을 두고 데이터베이스를 설계하는 방법에는 주로 다음의 두 방법이 사용된다. 이번 포스팅에서는 정규화에 대해 다룰 것이다.
  • E-R 모델과 릴레이션 변환
  • 정규화
• 정규화는 데이터베이스를 설계한 후 설계 결과물을 검증하기 위해 사용하기도 한다.
• 데이터베이스를 잘못 설계하면 불필요한 데이터 중복이 발생하여 삽입,수정,삭제 연산을 수행할 때 부작용이 발생할 수 있다. 이러한 부작용을 이상 현상이라고 한다.
• 이상 현상을 제거하면서 데이터베이스를 올바르게 설계해나가는 것이 정규화다.

1.1 이상 현상

---

이상 현상에는 다음의 3가지 종류가 있다.

• 삽입 이상 : 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제
• 갱신 이상(수정 이상) : 중복 투플 중 일부만 변경하여 데이터가 불일치하게 되는 모순의 문제
• 삭제 이상 : 투플을 삭제하면 꼭 필요한 데이터까지 함께 삭제되는 데이터 손실의 문제

1.1.1 이상 현상을 설명하기 위한 테이블 예

1.1.2 삽입 이상

• 테이블에 새 데이터를 삽입하기 위해 원치 않는 불필요한 데이터도 함께 삽입해야 하는 문제를 삽입 이상이라고 한다.
• 예를 들어, 이벤트 참여 기록이 없는 고객에 대한 데이터를 삽입해야 한다고 가정해보자. (이벤트번호, 당첨여부 = NULL)
• 이 고객의 데이터는 삽입될 수 없다. 이벤트참여 테이블의 기본키에 이벤트 번호 속성이 포함되기 때문이다. (개체 무결성 제약조건)
• 결국 임시 이벤트번호를 불필요하게 함께 삽입해야만 데이터 삽입이 가능하다. 이러한 경우 삽입 이상이 발생한다.

1.1.3 갱신 이상

• 테이블의 중복된 투플들 중 일부만 수정하여 데이터가 불일치하게 되는 모순이 발생하는 것을 갱신 이상이라고 한다.
• 예를 들어, Apple 고객의 등급이 gold에서 vip로 변경된다면, Apple 고객과 관련된 투플 3개에서 등급 속성이 모두 변경되어야 한다.
• 그렇지 않고 투플 2개에서만 등급이 수정된다면, Apple 고객이 서로 다른 등급을 가지는 모순이 생겨 갱신 이상이 발생한다.

1.1.4 삭제 이상

• 테이블에서 투플을 삭제하면 꼭 필요한 데이터까지 함께 삭제하여 데이터가 손실되는 연쇄 삭제 현상을 삭제 이상이라 한다.
• 예를 들어, Orange 고객이 이벤트 참여를 취소한다면 이벤트 참여 테이블에서 관련 투플을 삭제해야 한다.
• 그런데 이 투플은 orange 고객의 이벤트 참여에 대한 정보뿐만 아니라, 해당 고객에 대한 정보도 유일하게 가지고 있다.
• 따라서 이 투플이 삭제되면 해당 고객에 대한 고객아이디,이름,등급 데이터까지 원치 않게 손실되는 삭제 이상이 발생한다.

1.2 정규화의 필요성

---

이벤트 참여 테이블에서 여러 이상 현상이 발생하는 이유는 관련이 없는 데이터, 즉 관련 없는 속성들을 하나의 테이블에 모아두었기 때문이다. 정규화는 이상 현상이 발생하지 않도록, 테이블을 관련이 있는 속성들로만 구성하기 위해 테이블을 분해하는 과정이다.

🔗 2. 함수 종속

2.1 함수 종속을 설명하기 위한 테이블 예

2.2 완전 함수 종속

• 완전 함수 종속은 테이블에서 속성 집합 Y가 속성 집합 X에 함수적으로 종속되어 있을 때 속성 집합 X 전체에 종속된 것이다.
• e.g) 이벤트 참여 테이블에서 고객이름은 고객아이디에 완전 함수 종속되어 있다. (고객아이디(결정자) -> 고객이름(종속자))
• e.g) 당첨여부는 {고객아이디, 이벤트번호}에 완전 함수 종속되어 있다. ({고객아이디, 이번트번호} -> 당첨여부)

2.3 부분 함수 종속

• 부분 함수 종속은 테이블에서 속성 집합 Y가 속성 집합 X에 함수적으로 종속되어 있을 때 속성 집합 X 전체가 아닌 일부분에 종속된 것을 말한다.
• e.g) 이벤트 참여 테이블에서 고객이름은 {고객아이디, 이벤트번호}에 부분 함수 종속되어 있다. ({고객아이디, 이벤트번호} -> 고객이름)

🔎 3. 정규화의 개념과 정규형의 종류

• 함수 종속성을 이용하여 테이블을 연관성이 있는 속성들로만 구성되도록 분해해서, 이상 현상이 발생하지 않는 올바른 테이블으로 만들어나가는 과정을 정규화라고 한다.
• 정규화의 기본 목표는 관련이 없는 함수 종속성은 별개의 테이블으로 표현하는 것이다.
• 테이블이 정규화된 정도는 정규형으로 표현한다. 정규형은 크게 기본 정규형과 고급 정규형으로 나뉜다.
• 각 정규형마다 만족시켜야 하는 제약조건이 존재한다. 제약조건을 만족하면 테이블이 해당 정규형에 속한다고 표현한다.
• 일반적으로 기본 정규형에 속하도록 테이블을 정규화하는 경우가 대부분이다. 테이블의 특성을 고려해서 적합한 정규형을 선택해야 한다.

3.1 기본 정규형

---

1. 제 1 정규형
2. 제 2 정규형
3. 제 3 정규형
4. 보이스/코드 정규형

3.1.1 제 1 정규형

• 테이블에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있으면 제 1 정규형에 속한다.
• 다중 값을 가지는 속성을 포함한 테이블은 제1 정규형의 제약조건을 만족하지 못하므로 제1 정규형에 속하지 않는다.
• 다음의 그림은 제1 정규형에 속하는 이벤트 참여 테이블을 만드는 과정이다.

다중 속성 값을 제거하여 제1 정규형을 만족하였지만, 해당 테이블은 여전히 여러 이상 현상이 발생한다. 이 테이블이 부분 함수 종속을 포함하고 있기 때문인데, 이러한 문제를 해결하기 위해서는 먼저 부분함수 종속을 제거하고 테이블을 분해해야 한다. 테이블을 분해하여 부분함수 종속을 제거하면, 분해된 테이블들은 제2 정규형에 속하게 된다.

3.1.2 제 2 정규형

• 테이블 제 1 정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2 정규형에 속한다.
• 위 그림의 이벤트 참여 테이블은 제1 정규형에 속하지만 기본키인 {고객아이디, 이벤트번호}에 완전 함수 종속되지 않는 등급,할인율 속성이 존재하므로 제2 정규형에 속하지 않는다.
• 등급, 할인율 속성이 관련 없는 이벤트번호,당첨여부 속성과 같은 테이블에 존재하지 않도록 다음의 그림처럼 2개의 테이블로 분해하면 분해된 각 테이블은 제 2 정규형에 속한다.

제2 정규형에 속하더라도 이상 현상이 발생할 수 있다. 위 그림의 이벤트 참여 테이블은 함수 종속성을 단 하나만 포함하므로 이상 현상이 발생하지 않는다. 하지만 고객 테이블은 부분 함수 종속이 아닌 함수 종속성을 아직도 여러 개 포함하고 있고 결과적으로 이행적 함수 종속이 생기기 때문에 이상 현상이 발생한다. 이행적 함수 종속을 제거하면, 분해된 테이블들은 제3 정규형에 속하게 된다.

3.1.3 제 3 정규형

• 테이블이 제 2 정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3 정규형에 속한다.
• 위에서 분리된 고객 테이블은 고객아이디가 기본키이므로 등급과 할인율 속성이 고객아이디에 함수적으로 종속된다.
• 그런데 고객아이디가 등급을 결정하고 등급이 할인율을 결정하는 함수 종속 관계로 인해, 고객아이디가 등급을 통해 할인율을 결정하는 이행적 함수 종속 관계도 존재한다.
• 고객 테이블에 이상 현상이 발생하지 않도록 하려면 이행적 함수 종속이 나타나지 않도록 2개의 테이블로 분해해야 한다.

새로 분해된 고객 테이블과 고객등급 테이블은 모두 제3 정규형을 만족하게 되었다. 이렇게 테이블을 분해하면 하나의 테이블에 하나의 관계만 존재하게 되어 이행적 함수 종속으로 인한 이상 현상이 발생하지 않게 된다. 고객 테이블은 기본키인 고객아이디가 등급을 직접 결정하므로 제3 정규형에 속한다. 마찬가지로 고객등급 테이블도 기본키인 등급이 할인율을 직접 결정하므로 제3 정규형에 속한다.

3.1.4 보이스/코드 정규형

• 테이블의 함수 종속 관계에서 모든 결정자가 후보키이면 보이스/코드 정규형에 속한다.
• 후보키를 여러 개 가지고 있는 테이블에서 발생할 수 있는 이상 현상을 해결하기 위해 제3 정규형보다 좀 더 엄격한 제약조건을 제시한 것이 보이스/코드 정규형이다.
• 앞선 테이블들은 모두 제3 정규형이면서 보이스/코드 정규형에 속했다.
• 이제 제3 정규형에는 속하지만 보이스/코드 정규형에는 속하지 않는 신청 강좌 테이블의 예를 보자.
  1. 아래 그림의 신청강좌 테이블은 고객이 인터넷강좌를 신청하면 해당 강좌의 담당강사에 대한 데이터를 저장한다.
  2. 신청강좌 테이블은 한 고객이 인터넷강좌를 여러 개 신청할 수 있지만 동일한 인터넷 강좌는 여러 번 신청할 수는 없다.
  3. 그리고 강사 한 명이 인터넷강좌를 하나만 담당할 수 있고, 하나의 인터넷강좌는 여러 강사가 담당할 수 있다.
  4. 그러므로 투플을 구별할 수 있는 후보키는 {고객아이디, 인터넷강좌}와 {고객아이디, 담당강사번호}가 있고, 이중에서 {고객아이디, 인터넷강좌}를 기본키로 선정했다.

• 신청강좌 테이블에서 이상 현상이 발생하는 이유는, 후보키가 아니면서 함수 종속 관계에서 다른 속성을 결정하는 담당강사번호 속성이 존재하기 때문이다.
• 그러므로 이상 현상이 발생하지 않도록 하려면 모든 결정자가 후보키가 될 수 있도록 신청강좌 테이블을 아래와 같이 2개의 테이블로 분해해야 한다.

3.2 고급 정규형

---

1. 제 4 정규형
2. 제 5 정규형

3.2.1 제 4 정규형과 제 5 정규형

• 고급 정규형으로 분류되는 제4 정규형은 테이블이 보이스/코드 정규형을 만족하면서, 함수 종속이 아닌 다치 종속을 제거해야 만족할 수 있다.
• 그리고 제5 정규형은 테이블이 제4 정규형을 만족하면서 후보키를 통하지 않는 조인 종속을 제거해야 만족할 수 있다.
• 제4 정규형과 제5 정규형을 만족할 때까지 테이블을 분해하면 오히려 비효율적이고 바람직하지 않은 경우가 많다고 한다.
• 먼저 제3 정규형이나 보이스/코드 정규형에 속하도록 테이블을 분해하여 데이터 중복을 줄이고 이상 현상이 발생하는 문제를 해결하는데 많은 연습이 필요하다.

📌 출처

[데이터베이스 개론2판_한빛아카데미_김연희 지음_page 356~384]

---

👍 개인 공부 기록용 블로그입니다. 오류나 조언이 있으시면 언제든지 댓글 혹은 메일로 남겨주시면 감사하겠습니다! 😄

맨 위로 이동하기

SQL 카테고리 내 다른 글 보러가기