-
[SQL 전문가 가이드] 1과목 - 제 6장. 정규화(Nomalization) (이상현상, 1NF/2NF/3NF/BCNF, 함수 종속성, 키(Key) 슈퍼키/후보키/기본키/대체키/외래키, 반정규화(Denormalization))데이터베이스(DataBase)/SQL 전문가 가이드 (2020 개정판) 2023. 6. 20. 15:32728x90
1. 정규화(Normalization)
데이터 모델링에서 가장 기초적이며, 필수적으로 이루어져야 하는 작업
릴레이션(Relation)의 이상현상(Anomaly)을 제거하는 과정* 이상현상(Anomaly)
정규화를 거치지 않은 데이터베이스의 릴레이션(Relation, Table과 유사한 개념)에서 발생할 수 있는 현상으로,
[ 이상현상은 데이터베이스의 데이터의 중복, 무결성 저하, DB용량 증가 등의 원인이 됩니다. ]
* 삽입 이상(Insertion Anomaly) : 데이터 삽입 시, 삽입하고자 하는 데이터가 아닌 값들도 삽입되는 현상.
* 수정 이상(Modification Anomaly) : 데이터 수정 시, 수정하고자 하는 데이터 중, 일부만 수정되는 등 데이터 불일치 현상.
* 삭제 이상(Delete Anomaly) : 데이터 삭제 시, 삭제하고자 하는 데이터 뿐만 아니라 다른 정보까지 연쇄적으로 삭제되는 현상
이상 현상은 위 세 가지 유형으로 확인할 수 있습니다.
* 삽입 이상
- 휴학 및 강의를 아직 수강하지 않는 학생들에 대해 데이터를 삽입하는 경우, 강의코드 / 강의명이 Null 값으로 삽입된다.
* 수정 이상
- 'Java' 를 수강하는 '박수호' 학생의 연락처를 수정하는 경우, 해당 튜플(Tuple)만이 수정된다.
즉, '1003' 학번의 '박수호'임에도 연락처가 다르게 표기되는 것이다.
* 삭제 이상
- 강의코드가 'A05'이며 강의명이 '컴퓨터개론'인 강의가 인원 미달로 폐강되는 경우,
이를 수강하고자 한 '이현우' 학생의 데이터는 사라지게 된다.즉, 위처럼 문제되는 현상을 해결하기 위해서는 '릴레이션(또는 테이블)을 분리'해주어야 하며,
이러한 분리 작업을 정규화라고 하며, 단계별로 작업할 수 있다.
1. 제 1정규형(1NF)- 하나의 속성(Attribute)은 하나의 값(Value)을 가진다.
- 테이블의 컬럼(Column)이 원자값(Atomic Value)을 갖도록 테이블을 분해한다.* Tip. 속성(Attribute)의 특성 중, 하나로 속성은 단일값(atomic Value)을 가져야 한다.
[SQL 전문가 가이드] 1과목 - 제 3장. 속성(Attribute) 정리 ( + 정규화(Normalization))
* 정규화(Normalization) 정리를 보고 싶은 분은 [ Ctrl + F ] 를 통해 "3. 속성의 특징"으로 이동 바란다! 1. 속성의 개념 속성(Attribute)이란 사전적인 의미로는 사물(事物)의 성질, 특징 또는 본질적인 성
01-study-for-me.tistory.com
1차 정규형 이전의 데이터 [고객연락처 데이터] 고객의 연락처는 집전화, 직장 전화, 휴대폰 ... 등 다양하게 있을 수 있다.
위 [고객연락처 데이터]는 연락처 구분 없이, 고객이 사용하는 연락처를 모두 작성해두었다.
이러한 경우, 연락처가 어떤 번호를 나타내는지 구분하기가 어렵고, 구분을 위한 과정이 하나 더 필요해진다.
1차 정규화 후 데이터 [고객연락처 데이터] 이러한 경우, 속성(Attribute)이 원자값(또는 단일값, Atomic Value)이 될 수 있도록 테이블을 분리하여 적용한다.
2. 제2정규형(2NF)
- 엔터티(Entity)의 일반속성은 주식별자 전체에 종속적이여야 한다.
- 부분 함수 종속(Partial Functional Dependency)을 제거하여야 한다.
☞ 즉, 완전 함수 종속(Full Functional Dependency)여야 한다.이때, 제 1차 졍규형을 만족시킨 데이터여야 한다.
* 함수 종속성(Functional Dependency) 이란?
더보기: 데이터들이 어떤 기준값에 의해 항상 종속되는 현상으로,
속성 간의 함수 종속 관계를 도식으로 표현한 것을
"함수 종속 다이어그램(Functional Dependency Diagram)"이라 한다.
* 함수 종속성의 유형
- ⊂(부분집합)
1) 완전 함수 종속(Full Functional Dependency) X'⊂X 이고 X'→Y를 만족하는 Attribue X'이 존재하지 않음 2) 부분 함수 종속(Partial Functional Dependency, 2FN) X'⊂X 이고 X'→Y를 만족하는 Attribue X'이 존재함 3) 이행 함수 종속성(Transitive Dependence, 3FN) 속성 A→X이고 X→Y이면 A→Y 4) 결정자 함수 종속성(Boyce-code Normalization) X→Y에서 X가 후보키가 아님 * 함수 종속성의 주요 추론 규칙
구분 추론 내용 기본 재귀
(Reflective)Y가 X의 부분집합이면 X→Y이다 증가
(Augmentation)X→Y이면 XZ→YZ이다 이행
(Transitivity)X→Y이고 Y→Z이면 X→Z이다 부가 연합
(Union)X→Y이고 Y→Z이면 X→YZ이다 분해
(Decomposition)X→YZ이고 X→Y이고 X→Z이다 가이행
(Pseudo-Transitivity)X→Y이고 YW→Z이면 XW→Z이다 * 참고 : [ [Database] 함수 종속성(Functional Dependency)과 정규화(Normalization) ]
[Database] 함수 종속성(Functional Dependency)과 정규화(Normalization)
함수 종속성(Functional Dependency) 데이터들이 어떤 기준값에 의해 항상 종속되는 현상 Attribute X의 값 각각에 대해 Attribute Y의 값이 하나만 연관되면 "Y는 X에 함수적으로 종속된다" 또는 "X는 Y를 함수
yssa.tistory.com
2차 정규화 이전의 [주문상세 데이터] 위 데이터를 보면, 상품 "SQL 전문가 가이드"가 여러번 주문된 것을 확인할 수 있다.
또한, 해당 도서의 상품번호도 여러 개 존재하는 것을 확인할 수 있다.
하지만 데이터가 반복되어 출력된다고 하여 '중복(Duplication)된 데이터'라고 판단할 수는 없다.
[ 주문번호 ]라는 컬럼은 해당 엔터티의 식별자 의미를 가지고 있기에, 중복 데이터라 할 수 없는 것이다..
즉, 해당 테이블의 주요 식별자는 [ 주문번호 ]이다.
하지만 [ 상품명 ]은 주문번호와는 관계없이 오직 [ 상품번호 ]에 의해서만 결정된다. (종속적이다.)
이를 통해 [ 상품명 ]은 '주문번호 + 상품번호'가 아닌,
오직 [ 상품번호 ]에만 종속적인 것을 확인할 수 있다.
*** 이때, 완전 함수 종속은 주요 식별자에 의해서만 결정되어야 하므로, 해당 테이블을 분리해야 함을 알 수 있다. ***
함수의 종속성
2차 정규화를 수행한 [상품과 주문상세 데이터] 위처럼 '상품 엔터티'를 추가해 테이블을 구분하면서, 부분 종속성을 제거할 수 있게 된다.
이때, 데이터 조회를 위해 [ 상품번호 ]를 매핑키로 '상품 엔터티'에서 원하는 상품 정보를 가져오는 등의 행위를,
'조인(Join)'이라 한다.
더보기# Join(조인)
: 둘 이상의 테이블을 연결하여 데이터를 검색하고 조회하는 방법.
join을 사용하기 위해서는,
테이블들이 적어도 하나의 컬럼(Column)을 공유하고 있어야 한다.
즉, PK(기본키, Primary Key) 또는 FK(외래키, Forign Key)값으로 검색 및 조회 작업을 수행.
3. 제3정규형(3NF)
- 기본키(PK)를 제외한 속성들 간의 이행 종속성(Transitive Dependency)이 없어야 한다.
- 엔터티의 일반속성 간에는 서로 종속적이지 않는다.* 이행 종속성(Transitive Dependency)이란?
주문번호 → 고객번호 이고 고객번호 → 고객명이면, 주문번호 → 고객명 이다. 
이행 종속성 위처럼 A -> B일때, B -> C라면, A -> C가 성립되는 것을 '이행 종속성'이라고 한다.
위 컬럼들은 모두 [ 주문번호 ]에 종속하여 제 2차 정규형은 만족하나,
고객명이 식별자가 아닌 일반속성에 종속적이기에 제 3차 정규형을 위배하고 있다.
고객명이 '정세준'에서 '정우진'으로 변경되는 경우,
'정세준' 고객이 주문한 내역이 많다면 성능 부하와 특정 시점에서 정합성에 문제가 발생할 수 있다.
* 데이터 정합성(Data Consistency)
: 어떤 데이터들이 값이 서로 일치한 상태.
또한, 해당 트랜잭션(Transaction)은 '주문'과는 전혀 상관없는 트랜잭션으로,
주문과 관계 없는 트랜잭션을 주문 엔터티가 받을 이유가 없다는 것이다.
이외에도 자주 변경되는 데이터라면?
해당 데이터가흔하지 않은 경우라면?
.
.
.
즉, 분리한 엔터티들은 서로에게 영향을 주지 않도록 구성해야 중복에 대한 문제를 개선했다고 볼 수 있다.
제 3차 정규화 A -> B, B -> C이면 A -> C 구조인 테이블을 분리하여 이행 종속성을 제거하는 것이 3차 정규화의 목표이다.
+ BCNF 정규화
BCNF(Boyce and Codd Normal Form)
- 해당 정규형은 제3 정규형을 강화시킨 개념으로, 강한 제3 정규형이라고도 한다.
- 모든 결정자가 후보키가 되도록 테이블을 분해하는 것.* Key (슈퍼키, 후보키, 기본키, 대체키, 외래키)
더보기# Key(키)
: 무언가를 식별하는 고유한 식별자(Identifier)
즉, 키는 데이터베이스에서 조건(where)에 만족하는 관계(relation)의 행을 찾거나,
순서대로 정렬할 때, 다른 행과 구별할 수 있는 기준이 되는 '속성의 집합'이다.
이때, 키의 종류로는 [ 기본키 / 슈퍼키 / 후보키 / 대체키 / 외래키 ] 등이 있다.
JE, Dev-Study
Front-end, Back-end 공부하는 것을 기록합니다. :)
01-study-for-me.tistory.com
슈퍼키(Super Key) - 테이블에서 각 행을 유일하게 식별할 수 있는 하나, 또는 그 이상의 속성들의 집합
즉, 슈퍼키에 대한 조건은 아래와 같다.
1. 유일성(Uniqueness)을 만족해야 한다.
: 유일성이란, 하나의 키로 특정 행을 바로 찾아낼 수 있는 고유한 데이터 속성.후보키(Candidate Key) - 테이블에서 각 행을 유일하게 식별할 수 있는 최소한의 속성들의 집합.
( 슈퍼키와 비슷하나, 차이점이 있다.)
즉, 후보키에 대한 조건은 아래와 같다.
1. 유일성을 만족해야 한다.
2. 최소성(Minimality)을 만족해야 한다.
: 최소성이란, 키를 구성하는 속성들 중, 가장 최소로 필요한 속성들로만 키를 구성하는 성질을 뜻한다.기본키(Primary Key) - 후보키들 중에서 하나를 선택한 키.
즉, 기본키에 대한 조건은 아래와 같다.
1. 유일성을 만족해야 한다.
2. 최소성을 만족해야 한다.
3. 테이블에서 기본키는 오직 1개만 지정할 수 있다.
4. 기본키는 Null값을 가질 수 없고, 중복된 값도 가질 수 없다. (Not null, Unique)
: 각 행들을 구별하기 위한 키이므로, 유일해야 하고, 값이 없어서는 안되고, 중복되어서도 안된다.
대체키(Alternate Key) - 후보키가 두 개 이상일 경우, 기본키로 지정되지 못한 남은 키를 대체키라 한다.
즉, 대체키는 기본키로 선정되지 않은 후보키이다.외래키(Foreign Key) - 테이블이 다른 테이블의 데이터를 참조해 테이블간의 관계를 연결하는 키이다.
조인(Join)에서 사용된다.
( 다른 테이블의 데이터를 참조할 때 없는 값을 참조할 수 없도록 제약을 줌. )
( 참조될 열의 값은 참조될 테이블에서 기본키(PK)로 설정되어 있어야 한다. )
위 릴레이션은 제3 정규형을 만족하고 있다. (이행 함수종속성 제거)
하지만, 제3 정규형을 만족하고 있음에도 여전히 이상현상(Anomaly)이 발생할 수 있다.
- 삽입 이상 : 새로운 교수가 특정 과목을 담당한다는 새로운 정보를 추가할 수 없다. 적어도 한 명 이상의 수강 학생이 필요하다.
- 삭제 이상 : 학번 1001 학생이 C01 과목 수강취소를 할 경우, C02 과목까지 제거될 수 있다.
- 갱신 이상 : 해당 과목의 담당 교수가 변경되는 경우, 관련된 모든 행을 찾아 변경해야 한다.해당 이상현상의 이유로는,
결정자(Determinant)가 후보키(Alternative Key)로 취급되고 있지 않기 때문이다.
후보키는 슈퍼키(super key) 중에서 최소성을 갖는 키이므로 이 릴레이션에서는 (학번, 과목명)이나 (학번, 담당교수)가 후보키가 된다. 담당 교수만으로는 후보키가 될 수 없다.
하지만, 후보키가 아님에도 과목명을 결정할 수 있기 때문에 담당 교수는 결정자에 속한다.
BCNF 정규화 이 이상현상을 해결하기 위해서 모든 결정자는 항상 후보키가 되도록 릴레이션을 분해해주면 강한 제3 정규형, 즉 BCNF를 만족하게 된다.
참고 : [ [DB] 8.정규형 (1NF, 2NF, 3NF, BCNF) 중, BCNF ]
2. 반정규화(De-Normalization)
'反(돌이킬 반)'
즉, 정규화를 반대로 행하는 것으로,
역정규화라고도 한다.* Tip. 비정규화는 아예 정규화를 수행하지 않은 모델로, 반정규화와는 다르다!
반정규화를 정의하면,
정규화된 엔터티, 속성, 관계에 대해 시스템의 성능향상과 개발(Development)과 운영(Maintenance)의 단순화를 위해 중복, 통합, 분리 등을 수행하는 데이터 모델링의 기법
즉, 조회 속도 향상 및 데이터 처리량을 줄이기 위해 사용하는 기법으로,
성능을 위해 데이터의 중복을 허용하는 것이다.
# 반정규화의 목적
기본적으로 정규화 작업은,
입력/수정/삭제에 대한 성능 향상과 조회에 대한 성능 향상을 기대할 수 있으나,
정규화 작업으로 인해 과도하게 테이블이 분리되는 경우,
엔터티의 개수가 증가하고 관계가 많아져 여러개의 조인을 필요로 하며 처리 속도를 저하시킬 수 있다.
(관계의 개수는 보통 엔터티 개수 - 1 이다.)
즉, 처리성능이 중요하다고 판단되는 경우, '반정규화'를 사용해 속도를 높이는 것이다.
반정규화를 통해 [ 조회성능 ↑ / 데이터 처리 소요 시간 ↓ ] 이점을 누릴 수 있다. # 반정규화를 기술적으로 수행하지 않는 경우, 다음과 같은 현상 발생
- 성능이 저하된 데이터베이스가 생성될 수 있다.
- 구축단계나 시험단계에서 반정규화를 적용할 때 수정에 따른 노력비용이 많이 들게 된다.
즉, 반정규화는 필요한 상황에 적절하게 설계되고 사용되어야 이점을 볼 수 있다.
# 반정규화와 성능 중,
ㄱ. 반정규화를 적용한 모델에서 성능이 향상될 수 있는 경우 ( ↑ )
- 고객의 편의를 위해 최근 사용한 정보(데이터)가 자동으로 세팅하고자 할 때
- 데이터 조회가 여러 건 발생하는 경우
- 자주 사용되는 데이터가 있는 경우
등등..
위처럼 데이터를 자주(빈번히) '조회'해야 하거나, 조인이 너무 많이 발생하거나,
데이터 조회를 위한 쿼리문이 과도하게 긴 경우,
반정규화를 적용하면 성능 향상을 기대해볼 수 있다.
# 반정규화와 성능 중,
ㄴ. 반정규화를 적용한 모델에서 성능이 저하될 수 있는 경우 ( ↓ )
- 각 엔터티 간의 관계가 독립적일 때
- 데이터 비교가 필요한 경우
등등..
위처럼 엔티티간의 관계가 독립적이거나 서로 영향을 주어서는 안되는 경우,
또는 데이터를 조인하여 비교하는 과정이 필요한 경우는
반정규화를 적용하게 될 경우, 오히려 악영향을 끼칠 수 있다.
잘못된 반정규화는,
- 데이터 불일치로 인한 정합성 문제
- 불필요한 트랜잭션으로 인한 성능 문제
위와 같은 문제들을 일으킬 수 있다.
즉, 반정규화는 '그럼에도' 진행해야 할 만한 근거가 뒷받침될 때 비로소 진행되어야 한다.
[ 참고 : DATA ON-AIR, 반정규화와 성능 ]
반정규화와 성능
1. 반정규화를 통한 성능향상 전략 가. 반정규화의 정의 반정규화(=역정규화) 용어는 조금 다르게 표현되어도 그 의미는 동일하다. 여기에서 반정규화는 ‘반(Half)’의 의미가 아닌 한자로 반대
dataonair.or.kr
728x90'데이터베이스(DataBase) > SQL 전문가 가이드 (2020 개정판)' 카테고리의 다른 글