[데이터베이스] 7. 릴레이션 정규화

릴레이션 정규화

• 정규화(Normalization): 주어진 릴레이션 스키마를 함수적 종속성과 기본 키를 기반으로 분석하여, 원래의 릴레이션을 분해함으로써 중복과 세 가지 갱신 이상 최소화
• 갱신 이상
  - 수정 이상: 반복된 데이터 중에 일부만 수정하면 데이터 불일치 발생
  - 삽입 이상: 불필요한 정보 함께 저장하지 않으면 어떤 정보 저장 불가
  - 삭제 이상: 유용한 정보 함께 삭제하지 않고는 어떤 정보 삭제 불가

• 정보의 중복
  각 사원이 속한 부서 수만큼 동일한 사원의 튜플들이 존재하므로 사원이름, 사원번호, 주소, 전화번호 등이 중복되어 저장 공간이 낭비됨
• 수정 이상
  만일 어떤 부서의 이름이 바뀔 때 이 부서에 근무하는 일부 사원 튜플에서만 부서이름을 변경하면 데이터베이스가 불일치 상태에 빠짐
• 삽입 이상: 불필요한 데이터 함께 입력 않고는 정보 입력 불가
  만일 어떤 부서를 신설했는데 아직 사원을 한 명도 배정하지 않았다면 이 부서에 관한 정보를 입력할 수 없음
• 삭제 이상: 데이터 손실 발생 가능
  만일 어떤 부서에 속한 사원이 단 한 명이 있는데, 이 사원에 관한 투플을 삭제하면 이 사원이 속한 부서에 관한 정보도 릴레이션에서 삭제됨

• 릴레이션 분해: 하나의 일레이션을 두 개 이상의 릴레이션으로 나누는 것
  - 필요한 경우에는 분해된 릴레이션들로부터 원래의 릴레이션을 다시 구할 수 있을 보장
  - 분해 잘못할 경우 두 릴레이션으로부터 얻을 수 있는 정보가 원래의 릴레이션이 나타내는 정보보다 적을 수도 많을 수도 있음
  - 함수적 종속성을 기반으로 분해

  
  부서 이름 수정: 수정 이상 X
  새로운 부서 삽입: 삽입 이상 X
  마지막 사원 튜플 삭제: 삭제 이상 X

• 정규형
  : 제1정규형, 제2정규형, 제3정규형, BCNF, 제4정규형, 제5정규형
  일반적으로 BCNF까지만 고려

• 관계데이터베이스 설계 비공식적 지침

1. 이해하기 쉽고 명확한 스키마 생성
   : 여러 엔티티 타입이나 관계 타입에 속한 애트리뷰트들을 하나의 릴레이션에 포함 X
   => 각 릴레이션은 독자적 의미를 갖는 단위. 정보 중복 피하기.
2. 널값 피하기
   : 저장공간 늘어남. 집단함수 적용 어려움
3. 가짜 튜플 생기지 않도록
4. 스키마 정제

함수적 종속성

* 릴레이션 스키마에 관한 주장

• 결정자 (determinant)
  : 주어진 릴레이션에서 다른 애트리뷰트(또는 집합)를 고유하게 결정하는 하나 이상의 애트리뷰트
  A → B : A가 B를 결정한다. (A는 B의 결정자이다.)

  
  사원번호 → 사원이름, 사원번호 → 주소, 사원번호 → 전화번호, 부서번호 → 부서이름 / 직책 → 사원번호 X (여러 사원이 팀장이 될 수 있음)

• 함수적 종속성
  A → B : B가 A에 함수적으로 종속한다.
  (충분 조건: 각 A값에 대해 반드시 한 개의 B값이 대응)

  사원번호: 사원이름, 주소, 전화번호의 결정자
  => 사원이름, 주소, 전화번호: 사원번호에 함수적 종속
  직책: (사원번호, 부서번호)에 함수적 종속. 사원번호에 함수적 종속 X.
  => (사원번호, 부서번호)가 직책을 결정하는 결정자이고, 사원번호는 직책을 결정하는 결정자가 아니다.

  • 완전 함수적 종속성
    : 주어진 릴레이션 R에서 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하면서 애트리뷰트 A의 어떠한 진부분 집합에도 함수적으로 종속하지 않으면, B가 A에 완전하게 함수적 종속
    A는 복합 애트리뷰트. A → B

    (사원번호, 부서번호) → 직책 : 완전 함수적 종속성
    (사원번호, 부서번호) → 사원이름, 주소, 전화번호, 부서이름 : 부분 함수적 종속성

  • 이행적 함수적 종속성
    한 릴레이션의 애트리뷰트 A, B, C가 주어졌을 때 애트리뷰트 C가 이행적으로 A에 종속한다(A → C)의 필요 충분 조건
    : A → B ∧ B → C
    A가 릴레이션의 기본 키, 키의 정의에 따라 A → B와 A → C가 성립. 만일 C가 A외에 B에도 함수적으로 종속한다면 C는 A에 직접 함수적으로 종속하면서 B를 거쳐서 A에 이행적으로 종속

    (학번, 과목번호) → 학점
    학번(A) → 학과이름(B), 학과전화번호(C)
    학과이름 → 학과전화번호

릴레이션 분해

: 하나의 릴레이션을 두 개 이상의 릴레이션으로 나누는 것
(+) 중복 감소, 갱신 이상 감소
(-) 조인이 필요 없는 질의 -> 조인을 필요로 하는 질의, 분해된 릴레이션들로 원래 릴레이션 재구성 불가

• 무손실 분해
  : 분해된 두 릴레이션을 조인하면 원래의 릴레이션에 들어 있는 정보를 완전하게 얻을 수 있음
  * 손실: 정보의 손실

  
  학번, 이름 / 학번, 이메일 => 불필요한 분해. 학번만 두 번 정의
  학번, 과목번호 / 학번, 학점 => 나쁜 분해. 과목번호와 학점 연관성 안 보임. 재조인 시, 기존 릴레이션에 존재하지 않던 튜플들이 생성됨

정규형

제1정규형

: 모든 애트리뷰트가 원자값만을 가짐. 반복 그룹 X
* 반복 그룹: 한 개의 기본키 값에 대해 두 개 이상의 값을 가질 수 있는 애트리뷰트

• 반복 그룹 애트리뷰트에 나타나는 집합에 속한 각 값마다 하나의 튜플로 표현
  => 중복 발생. 갱신 이상 발생 가능
  
• 모든 반복 그룹 애트리뷰트들을 분리해서 새로운 릴레이션에 넣음. 원래 릴레이션의 기본 키를 새로운 릴레이션에 애트리뷰트로 추가.
  

학생 릴레이션: 모든 애트리뷰트 원자값 가짐 => 제1정규형 만족. 기본 키(학번, 과목번호)

• 삭제 이상
  : 어떤 학과에 소속된 마지막 학생 튜플 삭제 시, 이 학생이 소속된 학과에 관한 정보도 삭제
• 수정 이상
  : 한 학과에 소속한 학생 수만큼 그 학과의 전화번호 중복 저장. 여러 학생이 소속된 학과의 전화번호 변경 시, 그 학과에 소속한 모든 학생들의 튜플에서 전화번호를 수정하지 않으면 일관성 유지 X
• 삽입 이상
  : 한 명의 학생이라도 어떤 학과에 소속되지 않으면 이 학과에 관한 튜플 삽입 불가. 학번이 기본 키의 구성요소인데 엔티티 무결성 제약조건에 따라 기본 키에 널값 입력 불가

• 갱신 이상 생기는 이유?
  기본 키에 대한 부분 함수적 종속성 존재.
  (학번, 과목번호) → 학과이름, 학과전화번호
  (학번, 과목번호) → 학점
  => 학번, 학과이름, 학과전화번호 / 학번, 과목번호, 학점
  위와 같은 방식으로 분해 시, 부분 함수적 종속성 존재 X & 제2정규형 만족

제2정규형

: 제1정규형 만족 + 어떤 후보 키에도 속하지 않는 모든 애트리뷰트들이 릴레이션 R의 기본 키에 완전하게 함수적으로 종속
제1정규형의 기본 키가 두 개 이상의 애트리뷰트로 구성되었을 경우에만 고려.
학생 릴레이션: 기본 키(학번). 제2정규형 만족.

• 수정 이상
  : 여러 학생이 소속된 학과의 전화번호 변경 시, 그 학과에 소속한 모든 학생들의 튜플에서 전화번호를 수정하지 않으면 일관성 유지 X
• 삽입 이상
  : 어떤 학과를 신설해서 아직 소속 학생이 없으면 그 학과의 정보를 입력할 수 X. 학번이 기본 키인데 엔티티 무결성 제약조건에 따라 기본 키에 널값을 입력할 수 X
• 삭제 이상
  : 어떤 학과에 소속된 마지막 학생 튜플 삭제 시, 그 학과 전화번호도 함께 삭제

• 갱신 이상 발생 이유?
  이행적 종속성 존재
  학과전화번호: 학번에 이행적 종속.
  학번 → 학과이름
  학과이름 → 학과전화번호
  => 학번 → 학과전화번호
  학번, 학과이름 / 학과이름, 학과전화번호
  위와 같은 방식으로 분해 시, 이행적 종속성 존재 X & 제3정규형 만족

제3정규형

: 제2정규형 만족 + 모든 애트리뷰트가 릴레이션 R의 기본 키에 이행적으로 종속 X
수강 릴레이션: 각 학생은 여러 과목 수강 가능, 각 강사는 한 과목만 가르침. 기본 키(학번, 과목).
키가 아닌 강사 애트리뷰트가 기본 키에 완전하게 함수적 종속 => 제2정규형 만족
기본 키에 직접 종속 => 제3정규형 만족
함수적 종속성: (학번, 과목) → 강사 , 강사 → 과목

• 수정 이상
  여러 학생이 수강 중인 과목의 강사 변경 시, 그 과목을 수강하는 모든 학생들의 튜플에서 강사를 수정하지 않으면 일관성 유지 X
• 삽입 이상
  : 어떤 과목을 신설해서 아직 수강 학생이 없으면 강사가 가르치는 과목 정보를 입력할 수 X. 학번이 기본 키인데 엔티티 무결성 제약조건에 따라 기본 키에 널값을 입력할 수 X
• 삭제 이상
  : 어떤 과목 수강하는 마지막 학생 튜플 삭제 시, 그 과목 강사 정보도 함께 삭제

• 갱신 이상 발생 이유?
  키가 아닌 애트리뷰트가 다른 애트리뷰트 결정
  릴레이션 후보 키(학번, 과목), (학번, 강사)

BCNF

: 제3정규형 만족 + 모든 결정자가 후보 키
ex) 수강 릴레이션에서 강사 애트리뷰트는 후보 키가 아님에도 불구하고 과목 애트리뷰트를 결정 => BCNF X
제3정규형 만족하는 대부분 릴레이션: BCNF도 만족
(하나의 후보 키만을 가진 릴레이션이 제3정규형 만족 시, BCNF도 동시에 만족)

• 키가 아니면서 결정자 역할을 하는 애트리뷰트, 그 결정자에 함수적으로 종속하는 애트리뷰트를 하나의 테이블에 넣음. 이 릴레이션에서 결정자는 기본 키.
  (기존 릴레이션에 결정자를 남겨서 기본 키의 구성요소가 되도록. 새ㄹ운 릴레이션에 대한 외래키 역할)

역정규화

분해 ⬆️, 조인 필요성 ⬆️

학생(학번, 학과이름, 학과전화번호)
제2정규형 만족. 조인 필요 X

SELECT	학과이름, 학과전화번호
FROM	학생
WHERE	학번 = '11002';

학생(학번, 학과이름) / 학과(학과이름, 학과전화번호)

SELECT	학과이름, 학과전화번호
FROM	학생, 학과
WHERE	학번 = '11002'
		AND 학생.학과이름 = 학과.학과이름;

검색질의 >> 갱신질의 사용

• 역정규화: 빈번하게 수행되는 검색 질의들의 수행 속도를 높이기 위해, 이미 분해된 두 개 이상의 릴레이션들을 합쳐서 하나의 릴레이션으로 만드는 작업