Hi! My Name is...

데이터 베이스 정규화

14 min read

  • 각 단계별 기본조건은 이전 단계 조건들을 전부 만족해야 한다
  • 실제로는 BCNF 정규형 까지 정도만 지켜줘도 OK 이다

1정규형 (1NF)

  1. 모든 속성의 도메인은 원자값으로 구성되야 한다
  2. 순서가 보장되야 하는 작업에 경우에는 행 순서를 사용하지 않고 별도의 속성을 사용해야한다
  3. 반복그룹은 허용하지 않는다
  4. 기본키가 없는 테이블을 갖는건 허용하지 않는다

1번째

모든 속성의 도메인은 원자값으로 구성되야 한다
튜플에 키, 몸무게 이런식으로 여러값을 집어넣지 말라는거

예시

  • 문제: 토마토를 가진 사람을 찾으시오
Before
플레이어아이템
player1사과
player2감자
player3사과, 감자, 토마토
  • 이 경우 like 연산자를 쓸 순 있지만 로직이 복잡해지고 성능이 좋지 못하다
  • 또 하나는 삽입할 때 인데 일단은 update 연산을 써야한다 근데 그럴려면 기존에 player3 의 아이템 값을 불러와서 거기에 추가를 시키고 업데이트 해야 하는 극악의 효율을 보여준다
After
플레이어아이템
player1사과
player2감자
player3사과
player3감자
player3토마토
  • 그래서 각 값들로 행을 분리해 줘야 한다

2번째

순서가 보장되야 하는 작업에 경우에는 행 순서를 사용하지 않고 별도의 속성을 사용해야한다

예시

  • 문제: 나이순으로 오름차순으로 정렬하시오.
Before
맴버
혜인
하니
민지
다니엘
혜린
  • DB에서 튜플의 순서는 어떤 의미를 지니고 있질 않다.
  • 즉, 순서대로 저장했다고 해서 순서대로 반환 해줄거라는 기대를 하면 안된다
After
맴버나이
혜인16
하니19
민지20
다니엘19
혜린18
  • 이렇게 수정하면 나이를 기준으로 정렬하거나 하는게 가능하다

3번째

반복 그룹은 허용하지 않는다

예시

  • 문제: 플래이어5에 새로운 아이템을 추가한다
Before
플레이어아이템1아이템2아이템3
player1사과감자
player2감자토마토
player3사과
player4감자
player5사과감자토마토
  • 이 경우 다음 테이블인 아이템4 속성이 없는지 판단해서 있으면 해당 속성에 추가하고 없으면 속성을 만들고 거기다 값을 업데이트 한다
  • 이런 미친 비효율성
After
플레이어아이템
player1사과
player1감자
player2감자
player2토마토
player3사과
player4감자
player5사과
player5감자
player5토마토
player5옥수수
  • 그럼 새로운 아이템이 추가되면 그냥 insert 문 한번이면 끝난다.
  • 순서가 필요하면 속성 하나추가해서 부여해주면 끝이기에

2정규형 (2NF)

기본키가 아닌 모든 속성에 대하여 기본키에 종속적이어야 한다

  • 인벤토리 테이블에 갑자기 플레이어 랭크 속성이 있으면 안된다.

예시

  • 문제: player1에 랭크값을 업데이트 하라

Before

플레이어아이템플레이어 랭크
player1사과브론즈
player2감자실버
player3사과다이아
player1감자브론즈
  • 해당 플레이어가 모든 아이템을 잃으면 자연스럽게 해당 플레이어의 랭크값이 사라진다 (삭제이상)
  • 만일 랭크에 업데이트가 잘못되어 사과 가 있는 튜플만 실버가 됬다고 치자 그럼 player1 은 브론즈 이면서 실버라는 모순이 생겨 버린다 (갱신이상)
    • 또한 아이템이 막 100개라고 모든 값에 대해 업데이트 해줘야 한다, 즉 오버해드가 장난아니다
  • 만일 새로운 플레이어가 가입했다고 치자 그럼 자연스럽게 등급은 브론즈 로 부여되야 한다, 하지만 당연하게도 인벤토리에 어떤 아이템도 없는 상태일 것이다
    즉 삽입이 불가능한 모순이 생겨 버린다 (삽입이상)

After

플레이어아이템
player1사과
player2감자
player3사과
player1감자
플레이어랭크
player1브론즈
player2실버
player3다이아
  • 두 속성이 서로 종속되지 않도록 플레이어 테이블과 랭크 테이블을 분리 시킨다

3정규형 (3NF)

기본키가 아닌 모든 속성은 기본키에만 의존해야한다

  • 기본키를 제외한 속성간에 이행 족속성을 허용하지 않는다

예시

  • 스킬레벨: 14=브론즈, 59=실버, 10~15=다이아 라고 가정

Before

플레이어랭크스킬레벨
player1브론즈4
player2실버9
player3다이아14
  • 만일 player1 이 레벨을 올려 5레벨을 달성했다 치자 그러면 랭크도 실버로 바꿔야 하는데 문제가 생겨 업데이트가 안됐다면, 레벨은 5인데 브론즈라는 모순이 생긴다
  • 문제가 발생한 부분은 랭크가 스킬레벨에 종속적이라는 것이다
    • 플레이어 → 랭크 → 스킬레벨 의 관계가 성립한다
    • 즉 랭크와 스킬레벨은 분명 플레이어 에 종속적이지만 랭크 ←> 스킬레벨 의 관계가 성립하기 때문이다

After

플레이어스킬레벨
player14
player29
player314
스킬레벨랭크
1브론즈
2브론즈
3브론즈
4브론즈
5실버
6실버
7실버
8실버
9실버
10다이아
  • 즉 미리 스킬레벨에 따른 랭크 테이블을 만들어 두고
  • 플레이어 레벨이 올리면 레벨만 업데이트 해주고 랭크가 필요할땐 두 테이블을 Join 해서 사용한다

BCNF

  • 모든 결정자가 후보키가 되도록 테이블을 분리한다

예시

  • 학생은 여러 교수의 수업을 들을 수 있고 한 교수당 하나의 과목만 맞는다

Before

학번과목명담당교수
100C123P1
100C234P2
200C123P1
300C234P3
400C234P4
  • 과목명 이 바뀌면 무조건 담당교수가 바뀌거나 담당교수가 바뀌면 과목명이 달라져야 하는것은 아니라 3NF를 만족한다고 볼 수 있다
  • 여기서 문제는 학번을 담당교수가 결정한다라고 볼 수 없다 즉, 후보키가 될 수 없다
  • 새로운 교수가 와서 다른 과목이 추가되야 될 상황인데 문제는 수강생이 없는 상태라 이 상태에서는 추가 할 수 없다 (삽입이상)
  • 만약 100번 학생이 C234 과목을 취소한다면 P2가 C234를 담당한다는 정보도 같이 사라진다 (삭제이상)
  • 만일 과목명, 또는 담당교수가 변경되면 모든 행을 찾아 변경해줘야 하는데 이과정에서 오버해드가 장난 아닌게 발생한다 (갱신이상)

After

과목명담당교수
C123P1
C234P2
C234P3
C234P4
학번과목명
100C123
100C234
200C123
300C234
400C234
  • 새과목이 추가되면 담당교수 테이블에만 추가해두고
  • 수강취소를 하면 수강신청 테이블에서만 지우면되고
  • 과목명 수정의 경우 수강신청 테이블에 과목명은 담당교수 테이블에 과목명에 외레키로 참조 해두고 과목명이 변경되면 담당교수 과목명만 수정하면 그만이다

4정규형 (4NF)

다치 종속성을 제거한다

예시

  • 색상과 스토리지 조합은 일치해야한다
  • 다만 핑크 색상은 512GB 모델에만 존재한다

Before

모델색상스토리지
S24화이트512GB
S24화이트1TB
S24블루512GB
S24블루1TB
S24핑크512GB
S24+화이트512GB
S24+화이트1TB
S24+핑크512GB
  • 만약 S24+에 블루 색상이 추가된다고 치자, 그럼 512GB 모델 1TB 모델 둘다 지원해야 되니 행을 두게나 추가 해야한다.
  • 극단적인 예시로 만약 스토리지 종류가 100가지가 추가 되있다면 색상하나 추가 될때마다 100가지 행을 추가해야 되는 미친 상황이 나타난다
  • 분명 스토리지와 색상은 서로 독립적인 관계지만 하나의 속성을 삽입하려면 그것에 종속되어 여러값을 추가해야 하는 다치종속성 관계가 생겨버린다
  • 다만 예를들어 S24+ 의 512GB 에서만 오렌지 색상이 추가된다고 가정하면 이는 다치종속 관계로 볼 수 없다 즉 해당 정규형 적용이 불가하다

After

모델스토리지
S24512GB
S241TB
S24+512GB
S24+1TB
모델색상
S24화이트
S24블루
S24핑크
S24+화이트
S24+핑크
S24+블루
  • 다치 종속되는 두 부분을 분리하여 필요할때 마다 Join 하여 사용한다

5정규형 (5NF)

Join 종속성 제거

예시

  • 4NF 에서 분리한 테이블을 다시 Join 하였다

Before

모델색상스토리지
S24블루1TB
S24핑크1TB
S24화이트1TB
S24블루512GB
S24핑크512GB
S24화이트512GB
S24+블루1TB
S24+핑크1TB
S24+화이트1TB
S24+블루512GB
S24+핑크512GB
S24+화이트512GB
  • 이전에는 없던 1TB, 핑크 모델이 생겨났다
  • 스토리지의 색상 조합의 전부 일치해야만 하는 경우에는 상관없지만 일부만 유효한 (핑크색상은 512GB 모델에만 존재하는 것처럼) 데이터는 무시되고 모든 조합이 추가가 된다

After

모델스토리지
S24512GB
S241TB
S24+512GB
S24+1TB
모델색상
S24화이트
S24블루
S24핑크
S24+화이트
S24+핑크
S24+블루
색상스토리지
화이트512GB
블루512GB
핑크512GB
화이트1TB
블루1TB
  • 스토리지에 따른 색상을 추가하여 문제를 해결한다
  • 즉 모든 경우의 대하여 테이블을 분리해준다

용어정리

키

  • 후보키 (Candidate Key):
    • 기본 키로 쓸 수 있는 속성의 집합을 의미
    • 테이블의 모든 튜플을 고유하게 식별할 수 있는 속성들의 집합
  • 기본키 (Primary Key)
    • 주 키
  • 대체키 (Alternate Key)
    • 기본키가 되지못한 나머지 후보키
  • 슈퍼키 (Super Key)
    • 속성들의 집합으로 구성된 키 (주민번호 +성명)
  • 외래키(Foreign Key)
    • 다른 테이블 기본키를 참조하는 키