[MariaDB] 정규화

정규화

• 실습 데이터

https://docs.google.com/spreadsheets/d/18q1zXxHk69SwHiD6nc5fD_lLMqrX52gJNFLtzx6NSo8/edit#gid=138746620

• 함수적 종속성
  • f(x)=y
  • 함수적 종속이라는 표현은 수학의 함수에서 유래
  • y는 x의 값에 의해 결정되는 관계로서, y는 x에 종속적이고, 이를 수학적인 표현에서는 y는 x에 함수종속적이다 라고 표현
  • 정규화 관련해서 빈번히 사용되는 표현으로서, a가 b에 함수종속적이다 하면 b의 값에 의해 a가 결정됨을 의미
    • 예시)
    • 학번과 학부
      • 학번 -> 학부
      • 즉, 학번에 따라 학부가 결정되므로, 학부는 학번에 함수 종속적
    • 등록금과 학부
      • 학부 -> 등록금
      • 학부에 따라 등록금이 결정되므로, 등록금은 학부에 함수 종속적

• 정규화란 관계형 스키마를 더 좋은 구조로 정제해 나가는 과정
  • 몇단계의 정규화과정으로 나누는 여러 이론 존재
  • 여기서는 주요한 내용만을 학습
  • 몇단계의 정규화과정으로 나누는 여러 이론 존재
  • 여기서는 주요한 내용만을 학습
  • 도부이결다조

    

• 1차 정규화(도메인 분해)

• 모든 열의 값이 원자적이어야 함. 즉, 각 열에는 하나의 값만 있어야 함을 의미

• 하나의 컬럼에 여러 원자값이 존재시, 조회조건을 통한 조회 어려움 발생
  • 수정/삭제가 발생할때 특정 데이터를 찾아 수정/삭제의 어려움
  • FK 및 index 지정이 불가능함

• 즉, 1차 정규화는 여러 원자값의 컬럼을 쪼개는 과정

정규화 전

1차 정규화 후

• 2차 정규화(부분종속 제거)
  • 복합키로 이루어진 기본키 중에 모든 속성이 기본키(복합전체)에만 완전 함수 종속된 상태를 의미
  • 복합키로 이루어진 기본키 중에 특정 키에도 종속적이면(부분종속) 2차 정규화가 안된 상황

    ⇒ 테이블에 어울리지 않는 컬럼 빼내기

  

  • 2차 정규화를 위한 방법
    • 기본키가 아닌 키에 부분적으로 종속돼 있는 특정 컬럼값을 제거
    • 기본키에 종속적이지 않다면 해당 테이블에 어울리지 않는 컬럼이라는 생각 필요
    • 사실상 해당 테이블에 어울리지 않는 컬럼 분리 작업
  • 특정 컬럼이 특정컬럼에 종속적이다면, 해당 컬럼은 불필요한 값이 되어 불필요한 공간 차지 - 불필요한 컬럼을 제거하면 공간 효율적

    

    2차 정규화 전

    ⇒ STUDENT_NM, DEPARTMENT, fees는 STUDENT_ID에 부분종속됨

    

    

    2차 정규화 후

• 3차 정규화(이행종속(transitive depency)제거)
  • X ->Y 이고 Y->Z 이면 X->Z 가 성립. Z가 X에 이행적으로 함수 종속
  • 모든 속성이 기본키에 이행적 함수 종속이 되지 않아야 함
  • 즉, 이행종속이 되는 부분종속을 제거해야 하는 특성상, 부분종속과 구분 이 어려운 부분(기본키 빼고 다른키에 종속적인 것 빼기)
  • 만약 학번이 PK. pk -> 학부, 학부 -> fees. 즉, pk -> fees가 되어 fees가 pk에 이행종속적

  

  3차 정규화 전

  ⇒ fees는 DEPARTMENT(기본키 아님)에 종속적

  

  

  3차 정규화 후

• 그 외
  • 결정자이면서 후보키가 아닌 것 제거 (BCNF)
    • 후보키 집합에 속하지 않은 결정자가 존재하면 BCNF위반
    • 후보키란 테이블내에 유일성을 가질 수 있는 key중 하나
    • BCNF는 제 3 정규형을 조금 더 강화시킨 개념이다.

    

    정규화 전

    ⇒ 과목별담당교수의 과목이 바뀌면 관련 모든 튜플을 수정해야함 - 따로 떼어주면 한번만 수정하면 됨

    

    정규화 후

  • 다치 종속 제거(4NF)
    • 다치 종속성이란 하나의 릴레이션에서 여러 속성과의 관계가 1:N인 경우
    • A →→B 이중 화살표로 표시

    

    정규화 전

    ⇒학생은 과목과 동호회에 모두 1:N, 이 경우 중복이 N*M으로 발생

    

    정규화 후

  • 조인 종속성 제거 (5NF)
    • 조인종속성이란 하나의 릴레이션을 여러개의 릴레이션으로 분해하였다가, 다시 조인했을 때 데이터 손실이 없고 필요없는 데이터가 생기는 것

    ⇒ 조인 했을 때 정규화 전과 같아야 함

    위 예시 경우 조인 종속 위배 X

    

    정규화 전

    

    ⇒ 다치종속으로 정규화하면 조인 시 자바/.net 처럼 기존에 없는 데이터가 발생

    

    정규화 후

  • 역정규화
    • 자주 사용되는 컬럼이나 테이블은 일부러 정규화 하지 않는 것
    • ORDER테이블의 총금액은 items 테이블의 가격, order_details의 수량을 곱한 것(조인하여)으로 구할 수 있다. (따로 속성을 추가할 필요가 없음)
    • 단지, 자주 조회되며 조인이 빈번하게 일어날 경우 성능이 떨어지게 되므로 넣어준 것

---

• 정규화 추가

  이상현상과 정규화

  이상현상 : 쉽게말해 데이터를 삽입, 업데이트, 삭제할 때 문제가 발생하는 것

  정규화 : 이상현상이 발생하지 않도록 검증함

  (1) 이상현상

  이상현상의 종류

  • 삽입 이상 : 새 데이터를 삽입하기 위해 불필요한 데이터도 함께 삽입해야 하는 문제

  • 갱신 이상 : 중복된 레코드 값을 업데이트할 때 일부만 갱신되는 문제

  • 삭제 이상 : 레코드를 삭제할 때 꼭 필요한 데이터까지 함께 삭제 되는 문제

  삽입이상

  ex) 한 명당 여러이벤트 참여가능, 같은 이벤트는 한번만 참여가능, 유저 정보를 저장하는 테이블이 따로 없음→가입하면 기존 테이블 하나에 저장됨

  

  기존 테이블

  

  새로 가입한 고객

  

  이 테이블의 기본키는 고객아이디+이벤트번호이다.

  하지만 기본키의 속성은 NULL값을 가지면 안됨

  따라서, NULL값 대신 임시번호를 임의로 부여하여 테이블에 삽입하면 애초에 이상한 값을 넣게 되는 것으로 그 자체로 이상현상

  갱신이상 : 어떤 값이 갱신되었을 때, 실수로 특정 레코드의 컬럼값을 수정하지 않아 데이터가 불일치하는 경우

  ex) 정소화 고객의 등급을 vip로 바꿀 경우 정소화 고객의 모든 등급을 바꿔주어야 하는데 일부만 바뀌면 갱신이상

  

  삭제이상

  ex)orange 고객이 E004이벤트 참여를 취소했을 때 이벤트 번호와 당첨여부만 NULL로 변경 불가능 하다

  고객아이디와 이벤트번호는 기본키 이기 때문에.

  그래서 아예 삭제를 한다해도 모든 유저정보가 없어지기 때문에 삭제 또한 하면 안된다.

  (2) 정규화

  이상 현상들이 발생한 근본적인 원인은 테이블들이 적절히 나뉘어져있지 않았기 때문

  정규화를 통해 테이블들이 서로 관련있는 속성들로만 구성될 수 있도록 테이블들을 나눠줘야 함

  함수종속

  어떤 속성들이 다른 속성들에 의해 결정되는지, 어떤 속성이 어떤 속성에 종속되는지를 분석하는 것

  →특정 속성의 값을 알면 다른 속성의 값을 유추할 수 있다면 함수적 종속성을 가짐

  B가 A에게 종속된다 : A값에 대한 B값이 항상 하나다.

  A→B

  결정자: A

  종속자: B

  ex) 고객아이디 → 고객이름, 등급 / 고객이름은 결정자가 될 수 없다(동명이인 있을 수 있음)

  기본키는 항상 결정자이다.

  

  한개의 고객아이디는 한개의 고객이름만 갖는다(O)

  한개의 고객아이디는 한개의 당첨여부만 갖는다 X → 당첨여부의 결정자는 고객아이디+이벤트번호

  한개의 {고객아이디, 이벤트번호}는 한개의 당첨여부, 고객이름을 갖는다(O) - 기본키

  한개의 이벤트번호는 오직 한개의 당첨여부 혹은 고객이름을 갖는다(X)

  고객아이디 →고객이름

  {고객아이디, 이벤트번호} → (당첨여부, 고객이름) : 당첨여부는 완전 함수 종속 관계({고객아이디, 이벤트번호}전체에 의해 함수적으로 종속됨), 고객이름은 부분함수종속 관계(전체가 아닌 고객아이디(일부)에도 함수적으로 종속됨)

  정규형

  테이블이 정규화된 정도(테이블이 적절히 분해되어가는 정도)

  

  아래로 갈수록 복잡(복잡할 수 록 좋은 것은 아님 어느정도 복잡해져서 문제가 없으면 더 복잡해질 필요 X)

  • 각 정규형마다 만족시켜야하는 제약조건이 존재, 정규형의 차숙가 높아질수록 요구되는 제약조건이 많아지고 엄격해진다.

  • 특정 정규형의 제약조건을 만족하면 테이블이 해당 정규형에 ‘속한다’고 표현한다.

  ex)

  -제 3정규형에 속하는 테이블은 제 1,2정규형에 무조건 속한다.

  -제2정규형에 속한다고 해서 제 정규형에 속하는지는 장담할 수 없다.

  • 모든 테이블이 꼭 제 5정규형에만 속해야하는 것은 아니다

    →정규화의 궁극적 목표는 이상현상을 없애는 것

  (1) 제 1 정규형 (1NF)

  테이블에 속한 모든 속성의 도메인이 원자 값으로만 구성되어 있다.

  도메인: 속성이 가질 수 있는 값들의 집합

  ex) 나이 속성의 도메인 : 0 이상의 정수

  원자값: 쉽게말해 단일값

  

  위 테이블은 제 1 정규형이 아님 → 이벤트 번호, 당첨여부 컬럼이 다중 값 속성이기 때문

  이를 모든 속성이 원자 값을 가지도록 만들어줌(제 1 정규형)

  

  그 후 이상현상이 있는지 확인

  →삽입이상(o), 갱신이상(o), 삭제이상(o)

  이상현상이 있으므로 제 2정규형으로 업그레이드 시켜야 함

  (2) 제 2 정규형 (2NF)

  테이블이 제 1정규형에 속하고 기본키가 아닌 모든 속성이 기본키에 완전함수종속

  → 당첨여부, 등급, 할인율은 {고객아이디, 이벤트번호}에 완전 함수 종속 되는지 확인

  

  등급과 할인율은 부분 함수 종속(고객아이디에도 종속됨)

  완전 함수 종속이 되도록 분해하기(완전함수종속관계끼리 테이블 만들기) - 제2정규형

  

  이상현상 확인하기(이벤트 테이블)

  삽입이상(X)

  갱신이상(X) - 테이블당 한 레코드씩 바꿔도 문제가 없다

  삭제이상(X) - 이벤트 테이블에서 orange회원의 레코드를 삭제해도 고객테이블의 orange회원의 레코드는 남아있다.

  이상현상 확인하기(고객 테이블)

  삽입이상(O) - 브론즈 등급이 새로 생겨서 추가하면 해당되는 고객아이디는 없기 때문에 삽입이상 생김

  갱신이상(O) - gold등급의 할인율을 바꾸고 싶은데 다른 레코드의 골드 등급 할인율은 바뀌지 않을 수 있음

  삭제이상(O) - orange에 대한 정보를 삭제하면 silver 등급에 대한 정보도 사라져서 등급자체가 없어진다.

  (3) 제 3정규형(3NF)

  테이블이 제 2정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않으면 제 3정규형에 속한다.

  이행적 함수종속: X→Y 그리고 Y→Z일 때, 논리적으로 X→Z가 성립하는 것

  ex) 고객아이디→등급, 등급→할인율 따라서 고객아이디→할인율

  

  이행적 함수 종속이 되지않도록 나누기

  

  

  브론즈 등급이 생기면 등급테이블에 추가 해주면 됨

  삽입이상(x)

  할인율을 바꾸고싶으면 등급테이블에서 변경해주면 됨

  갱신이상(x)

  orange회원이 탈퇴해서 회원테이블에서 삭제해도 silver등급 자체는 남아있음

  삭제이상(x)

  최종적으로 나뉜 테이블

  

  기존 테이블