잘못 설계된 테이블에 데이터 질의 (SELECT) 할 때는 문제가 없는데, 그 외의 데이터 조작 (삽입, 삭제, 수정)을 하면 문제가 발생하는 것을 ‘이상 현상’이라고 말한다.
이상현상이란, 테이블에 튜플 1) 삽입 시 부득이하게 NULL값 입력되거나 2) 삭제 시 연쇄 삭제 현상 발생하거나 3) 수정 시 데이터 일관성 훼손되는 현상
삽입 이상 : 튜플 삽입 시 특정 속성에 해당하는 값이 없어서 NULL 입력해야 하는 현상
삭제 이상 : 튜플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
수정 이상 : 튜플 수정 시 중복된 데이터의 일부만 수정되어 데이터 일관성 훼손되는 현상
2. 이상현상의 예시
한 릴레이션에 두 개 이상의 정보가 포함될 경우 이상현상이 나타난다.
이상현상 나타나는 테이블
1) 삽입 이상
삽입 이상 : 튜플 삽입 시 특정 속성에 해당하는 값이 없어 NULL값 입력해야 하는 현상
박세리 학생이 체육학과에 입학해서 INSRERT문으로 (학생번호,이름, 학과,주소)정보를 삽입했다.
→ 그런데, 아직 수강 신청은 하지 않은 상태라 (강좌이름, 강의실) 일부 속성에는 NULL값을 입력해야 한다.
→ NULL값은 테이블에 가능한 없어야 한다.
→ NULL값 문제 발생
2) 삭제 이상
삭제 이상 : 튜플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
장미란 학생이 스포츠경영학 과목 수강 취소하여 DELETE 문으로 3번째 튜플 삭제했다.
→ 스포츠경영학 과목 수강 취소 처리는 되었지만, 아예 장미란 학생의 기본 정보까지 없어진다.
→ ‘연쇄 삭제 문제 발생
3) 수정 이상
수정 이상 : 튜플 수정 시, 중복된 데이터 中 일부만 수정되어 데이터 일관성 깨지는 현상
박지성 학생의 주소가 (영국 맨체스터→ 대한미국 서울)로 바뀌었다.
→ 위 테이블에서 박지성 선수 관련 튜플은 2개 있는데, 하나의 튜플 주소값만 바뀔 경우, 이후 SELECT 조회 시 서로 다른 주소값이 조회된다.
→ 데이터 불일치 문제 발생
🎈 이상 현상 해결 방법 : 테이블 분리 (= 정규화)
테이블 분리시키자.
→ 정규화 하기 위해서는 (함수 종속성)과 (PK)를 파악해야 한다.
02. 함수 종속성
1. 종속성 개념
종속성이란, 어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게(unique) 정해지는 관계
A → B 로 표기하며, A를 B의 결정자라고 한다.
속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 의존 관계
2. 함수 종속성
A→B , 왼쪽 속성의 모든 값에 대하여 오른쪽 속성의 값이 유일하게 결정될 때 B는 A에 함수적으로 종속한다고 본다.
함수 종속성 : 릴레이션의 속성 간에 함수적으로 종속하는 성질
3. 함수 종속성 다이어그램
직사각형 : 릴레이션의 속성
화살표 : 속성 간 함수의 종속성 표현
직사각형으로 묶음 : 복합 속성
4. 이상현상과 결정자
이상현상은 한 릴레이션에 두 개 이상의 정보가 포함되어 있을 때 나타난다.
학생 수강성적 릴레이션의 경우 학생 정보와 강좌 정보가 한 릴레이션에 포함되어 있기 때문에 이상현상이 일어난다.
이상현상은 기본키가 아닌 속성이 결정자 역할을 하는 ‘종속적 관계’가 있을 때 발생한다.
→ 그래서, 함수 종속성을 파악하기 위해서는 PK(기본키)를 찾아야 한다. (PK) 기본키는 릴레이션의 모든 속성에 대한 결정자이다. 아래의 표에서 모든 속성을 결정짓는 애는 ‘복합키’ 형태의 (학생번호, 강좌이름) 이다.
이상현상 테이블 분리 모습
03. 정규화 | Normalization
1. 정규화의 개념
정규화 : 테이블 간 중복된 데이터 허용하지 않으면서 이상현상 일으키지 않도록 릴레이션 분해하는 과정
이상현상이 있는 릴레이션은 이상현상 일으키는 ‘함수 종속성 유형’에 따라 등급 구분 가능
릴레이션은 정규형 개념으로 구분하며, 정규형 높을수록 이상현상 줄어듬
정규형 분류
2. 정규화의 목적
중복 데이터 최소화하여 테이블 불일치 위험 최소화
수정,삭제 이상 현상 방지하여 데이터 구조의 일관성을 최대화
데이터 삽입 시 릴레이션 재구성에 대한 필요성 줄임
3. 정규화 한 눈에 보기
정규화 한 눈에 보기
기본 정규형 (1NF, 2NF, 3NF,BCNF)
(1) 1NF | 제 1 정규형 | 속성의 원자성 확보
릴레이션의 모든 속성값이 원자값을 가져야 한다.
→ 한 속성에 2개 이상 값을 갖는 경우 제거함
즉, 아래의 왼쪽 표처럼 한 속성에 2개 이상의 값을 가진 상태면 안된다.
오른쪽 표처럼 각 속성에 대한 값은 원자적인 릴레이션은 ‘제 1정규형’이다.
1NF 예
(2) 2NF | 제 2 정규형 | 부분 종속성 제거
릴레이션 내의 모든 칼럼에 부분적 종속이 없어야 한다. ( 완전 함수 종속 관계여야 한다.)
기본키가 2개 이상의 속성으로 구성된 복합키일 경우, 부분 함수 종속성을 제거해야 한다.
릴레이션이 제1정규형에 속하고, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속되면 제 2정규형에 속한다. (즉, 복합키의 부분집합 키가 결정자가 되어서는 안된다.)
→ 이상현상 원인 : 복합 PK의 일부 속성에 한 번 더 종속되는 속성이 있을 때 (=완전 종속X 부분 종속될 때) 이상 현상이 발생한다.
💡 부분 함수 종속 기본키가 복합키일 때, 복합키의 부분집합도 결정자가 되어 다른 속성이 종속되는 것 💡 완전 함수 종속 기본키가 복합키일 때, 오직 복합키 전체만이 결정자가 되는 것 (복합키 내부의 부분집합이 결정자가 되어서는 안된다.) 즉, 기본키에 대해서 완전 종속
부분 함수 종속성 제거
(3) 3NF | 제 3정규형 | 이행 종속성 제거
릴레이션이 제2정규형에 속하고 (기본키가 아닌 속성)이 기본키에 비이행적으로 종속 (= 기본키에 직접적으로 종속) 할 때 제 3 정규형을 만족한다.
→ 기본키 이외의 다른 속성 간 종속성을 제거하여 각 속성이 기본키에만 종속되도록 하는 것이 제 3정규형의 핵심이다.
즉, 기본키 이외의 속성이 다른 속성의 결정자가 될 경우 이행적 종속 관계에 있으므로 분리시켜줘야 한다.
💡 이행적 종속성: 어떤 속성이 PK에 직접 종속이 아닌, 그 외의 속성을 거쳐 간접적으로 종속되는 형태.
릴레이션의 속성 A, B, C가 주어졌을 때 (A→B, B→C)가 성립하면서, (A→C)까지 성립하게 된다. 이 경우, 속성 C가 이행적으로 A에 종속한다(A→C)고 본다.
왜? - 여기서 A가 릴레이션의 기본 키(PK)라고 한다면, (기본키는 모든 속성에 대한 결정자이므로) A→B, A→C 를 결정하는 건 문제가 없다. 그런데, B→C가 성립하게 됨으로써 C가 A(PK) 외에 B에도 함수적으로 종속하는 형상이 일어난다. (= 기본키 이외의 속성이 결정자가 된 상태) 이 경우, C는 A에 직접 함수적으로 종속하면서도 B를 거쳐 A에 이행적으로 종속되는 상태가 된다. 이를 두고, 이행적 종속성으로 칭한다.
이행적 종속성 제거
(4) BCNF | 보이스-코드 정규화
결정자가 후보키가 아닌 종속성 제거한다.
BCNF : 제 3정규형 만족하면서 모든 결정자가 후보키 집합에 속해야 한다.
릴레이션에 존재하는 함수 종속성에서 모든 결정자가 후보키인 상태
이상현상 원인 : 후보키가 아니면서 결정자인 속성이 존재할 경우 이상 현상이 발생한다.
이러한 이상현상이 생기는 이유는 후보키가 아닌 속성이 결정자로서 역할하기 때문이다.
후보키는 유일성 & 최소성을 갖는 키이므로 이 릴레이션에서는 (학번, 과목명)이나 (학번, 담당교수)가 후보키가 된다.
즉, 담당 교수만으로는 후보키가 될 수 없다.
그런데, (담당교수)가 후보키가 아님에도 과목명을 결정하고 있다. 즉, 후보키가 아닌 속성이 결정자 역할을 하는 상황이다.
이를 해결하기 위해서는 모든 결정자가 항상 후보키가 되도록 릴레이션을 분해해주면 BCNF를 만족하게 된다.
후보키 아닌 결정자
→ 대부분 BCNF까지 정규화를 하면 실질적인 이상 현상이 없어지므로 BCNF까지 정규화를 한다고 한다.
고급 정규형 (4NF, 5NF)
(5) 4NF | 제 4정규형 | 다치 종속 제거
다치 종속을 제거해야 한다.
BCNF를 만족해야 하고, 다치 종속(Multi-valued Dependency)이 없어야 한다.
한 릴레이션에서 한 속성이 값 하나를 결정하는 게 아니라, 1:N의 종속성을 갖는 경우 ‘다치 종속’ 이라고 본다.
이럴 경우에는 중심이 되는 속성을 기준으로 릴레이션을 분리시켜주어야 한다.
ex. 과목 -> 교재 , 과목 속성이 교재 속성값 하나를 결정짓는 게 아니라 여러 개의 값을 결정(N)짓는 상태이다.
(6) 5NF| 제 5정규형 | 조인 종속 제거
4NF를 만족하면서, 조인에 의한 종속성이 있는 경우도 분해한 상태이다. (조인 종속 제거)
Project Join Normal Form(PJNF)라고도 불린다.
중복을 제거하기 위해 분해할 수 있을 만큼 전부 분해하는 것이다.
❓ 조인 종속성 : 하나의 릴레이션을 여러 개의 릴레이션으로 무손실 분해했다가 다시 결합할 수 있다면 조인 종속이라고 한다. 그래서 A릴레이션을 B, C로 분해했다가 다시 조인했을 때에도 그대로 A가 된다면 A는 조인 종속성이 있다고 본다.
[궁금] 그렇다면 왜 조인 종속성을 제거시켜줘야 한다는 거지 ??????
→ 참고로 이렇게 정규형 과정을 거쳐 테이블을 쪼갠다고 해서 성능이 100% 좋아지는 것은 아니다.
→ 테이블을 쪼갤수록 추후 데이터 조회 시, Join(조인)을 유발하므로 CPU와 메모리를 많이 사용한다. 서비스에 따라 정규화 or 역정규화 과정을 진행해야 한다.
04. 역정규화 | De-Normalization
1. 역정규화 (=반정규화)
성능 향상을 위해 정규화된 데이터 모델에서 중복, 통합, 분리 등을 수행하는 모든 과정을 의미한다. 반정규화를 적용하면 데이터 무결성이 깨질 수 있는 위험이 있다.
의도적으로 정규화 원칙을 위배하는 행위
데이터베이스 성능 향상을 위해 데이터 중복을 허용하고 조인 줄이는 데이터베이스 성능향상 방법
— 조회 속도는 향상시키지만, 데이터 모델 유연성은 낮아짐
2. 수행하는 이유
정규화에 충실하여 종속성, 활용성이 향상되었지만, 되려 수행속도가 느려진 경우
다량의 범위 자주 처리해야 할 경우
특정 범위 데이터만 자주 처리해야 할 경우
요약/집계 정보가 자주 요구될 경우
3. 반정규화 절차
반정규화 대상 조사
다른 방법 유도 검토
반정규화 적용
4. 반정규화 기법
(1) 테이블 반정규화
테이블 병합
1:1 관계 테이블 병합 : 1:1 테이블을 병합하여 성능 향상
1:M 관계 테이블 병합 : 1:M 테이블을 통합하여 성능 향상
슈퍼/서브 테이블 병합 : 슈퍼/서브 관계를 통합하여 성능향상.
테이블 분할
수직분할 : 컬럼단위의 테이블을 1:1로 분리하여 성능향상
수평분할 : Row 단위로 집중 발생되는 트랜잭션을 분석하여 테이블을 분할
테이블 추가
중복테이블 추가 : 다른 업무 또는 서버가 틀린경우 동일한 테이블 추가 (원격 조인 제거)
통계테이블 추가 : 집계함수 등을 미리 수행하여 계산해둔 테이블을 추가 ( 쿼리 수행시에 계산하지 않는다.)
이력테이블 추가 : 마스터 테이블에서 자주 조회되는 레코를 중복하여 테이블 추가 ( 범위처리 최소화)
부분테이블 추가 : 자주 이용하는 집중화된 칼럼이 있는경우 디스크I/O를 줄이기위해 별도의 컬럼만 테이블 추가
(2) 컬럼 반정규화
중복칼럼 추가 : 조인시 성능저하를 예방하기위해, 중복되는 컬럼을 위치시킴
파생컬럼 추가 : 트랜잭션이 처리되는 시점에 계산에 의해 발생되는 성능저하를 예방하기 위해 미리 계산하여 컬럼에 보관
( 미리 계산한 컬럼 sum , sumtoValue)
이력테이블 컬럼 추가 : 대량의 이력데이터 처리시 기능성컬럼(최근값, 시작일자, 종료일자) 를 추가한다.
(3) 관계 반정규화
중복관계 추가 : 여러경로를 거쳐 조인 할 수 있지만, 성능저하를 예방하기위해 추가적인 관계를 맺는 방법(중복 FK)
📌 참고로 (테이블 반정규화 & 컬럼 반정규화) 는 데이터 무결성에 영향을 끼치지만, (관계의 반정규화)는 데이터의 무결성을 깨뜨리지 않는다
