[DB] SQL 정규화

CS/DB

[DB] SQL 정규화

설기똥꼬 2023. 1. 10. 15:58

요즘 프로젝트하면서 erd 설계를 하고 있는데, 테이블 연관관계와 정규화에 대해 더 알고 싶어 블로그에 기록하게 되었다!

정규화에 대해 알아보자.

정규화

이상현상이 있는 릴레이션을 분해하여 이상현상을 없애는 과정, 이로써 여러 개의 릴레이션을 생성하게 된다.
이를 단계별로 구분하여 정규형이 높아질 수록 이상현상은 줄어든다.

여러 엔티티의 속성을 혼합하면 정보가 중복 저장된다. 정규화의 기본 목표는 테이블 간 중복된 데이터를 허용하지 않는다.

이러한 테이블을 분해하는 정규화 단계가 정의되어 있는데 여기서 테이블을 어떻게 분해하는지에 따라 정규화 단계가 달라진다.

그 전에, 이상현상과 함수 종속성에 대해 짚고 넘어가자.

이상현상

삽입 이상 (Insertion Anomaly) : 튜플 삽입 시 특정 속성에 해당하는 값이 없어 NULL을 입력해야 하는 현상
삭제 이상 (Deletion Anomaly) : 튜플 삭제 시 같이 저장된 다른 정보까지 연쇄적으로 삭제되는 현상
갱신 이상 (Update Anomaly) : 튜플 갱신 시 중복된 데이터의 일부만 갱신되어 일어나는 데이터 불일치 현상

위와 같은 테이블을 예로 들어보자.

1) 삽입 이상

만약 강의를 수강하지 않은 학생을 추가할 때 과목 번호와 성적에 null 값이 들어가거나 불필요한 데이터를 추가해야 삽입할 수 있는 문제점이 발생한다. 아래와 같은 데이터를 삽입할 수 없다.

학생이 수강신청 할 때 반드시 과목 번호를 알아야 삽입이 가능하다.

2) 삭제 이상

학번이 345인 학생이 수강 신청한 과목을 취소하면 해당 강의에 대한 정보 모두 삭제된다.

가 교수가 강의하는 과목을 취소하는 경우 해당 강의를 듣는 학생에 대한 정보 모두 삭제된다.

3) 갱신 이상

학번이 123인 학생의 지도교수가 라로 변경되면, 이 학생이 수강하는 모든 과목 (행)에서의 지도 교수를 변경시켜줘야 한다.

함수 종속성 (Functional Dependency)

어떤 속성 A의 값을 알면 다른 속성 B의 값이 유일하게 정해지는 관계
A -> B로 표기하며 A를 B의 결정자(Determinant)라고 한다.
A -> B이면 A는 B를 결정한다(Determine) 한다고 하고, B는 A에 종속한다(Dependent)라고 한다.

함수 종속성은 크게 세 가지로 나뉜다.

1) 완전 함수 종속 : 기본키를 구성하는 모든 속성에 종속되는 경우

2) 부분 함수 종속 : 기본키를 구성하는 속성의 일부에 종속되거나, 기본키가 아닌 다른 속성에 종속되는 경우

3) 이행적 함수 종속 : A, B, C 세 속성이 있고, A -> B, B -> C 종속 관계가 있을 때 A -> C가 성립하는 경우

위와 같은 테이블이 있다고 하자.

여러 학생들이 있고 각 학생별로 여러 과목을 수강한다고 하면 위 테이블의 기본 키는 (학번, 과목번호)이다.

그렇다면 이름과 학년은 학번만 알아도 유일하게 결정된다.

반면에 성적은 학번과 과목 번호를 모두 알아야 유일하게 결정된다.

따라서 이름과 학년은 (학번, 과목번호)에 대해 부분 함수 종속이고, 성적은 완전 함수 종속이다.

이렇게 엔티티를 구성하는 속성간의 함수 종속성을 판단하여 좋은 릴레이션인지 알 수 있다.

제1 정규형 (1NF)

제1 정규형은 다음과 같은 규칙들을 만족해야 한다.

1. 각 컬럼이 하나의 속성만을 가져야 한다.
2. 하나의 컬럼은 같은 종류나 타입(type)의 값을 가져야 한다.
3. 각 컬럼이 유일한(unique) 이름을 가져야 한다.
4. 칼럼의 순서가 상관없어야 한다.

상단의 테이블을 보면 '나'는 여러 개의 취미를 갖고 있으므로 제1 정규형을 만족하지 못한다. 그렇기에 제1 정규화하여 분해할 수 있다.

제2 정규형 (2NF)

제2 정규형은 다음과 같은 규칙을 만족해야 한다.

1. 제1 정규형을 만족해야 한다.
2. 모든 컬럼이 부분적 종속(Partial Dependency)이 없어야 한다. 즉 모든 칼럼이 완전 함수 종속을 만족해야 한다.

부분적 종속이란 기본키 중 특정 컬럼에만 종속되는 것이다. 제1 정규화를 진행한 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해하는 것이다.

즉 기본키의 부분집합이 결정자가 되어선 안된다는 것이다.

이 테이블의 기본 키는 (학번, 강좌 이름)로 복합키이다. 그리고 (학번, 강좌 이름)인 기본 키는 성적을 결정하고 있다.

(학번, 강좌이름) -> (성적)

여기서 강의실 컬럼은 기본키의 부분집합인 강좌이름에 의해 결정될 수 있다.

(강좌이름) -> (강의실)

즉, 기본키(학번, 강좌이름)의 부분키인 강좌이름이 결정자이기 때문에 위의 테이블의 경우 다음과 같이 기존의 테이블에서 강의실을 분해하여 별도의 테이블로 관리하여 제2 정규형을 만족시킬 수 있다.

제3 정규화 (3NF)

제3 정규형은 다음과 같은 규칙을 만족해야 한다.

1. 제2 정규형을 만족해야 한다.
2. 기본키를 제외한 속성들 간의 이행 종속성 (Transitive Dependency)이 없어야 한다.

여기서 이행적 종속이라는 것은 A -> B, B -> C가 성립할 때 A -> C가 성립되는 것을 의미한다.
예를 들어 아래와 같은 계절 학기 테이블을 살펴보자.

기존의 테이블에서 학번은 강좌 이름을 결정하고 있고, 강좌 이름은 수강료를 결정하고 있다.

그렇기 때문에 이를 (학생 번호, 강좌 이름) 테이블과 (강좌 이름, 수강료) 테이블로 분해해야 한다.

BCNF (Boyce-Codd Normal Form)

BCNF는 제 3정규형을 좀 더 강화한 버전으로 다음과 같은 규칙을 만족해야 한다.

1. 3정규형을 만족해야 한다.
2. 모든 결정자가 후보키 집합에 속해야 한다.

예를 들어 다음과 같은 특강수강 테이블이 존재한다고 하자.

특강수강 테이블에서 기본키는 (학번, 특강이름)이다. 그리고 기본키 (학번, 특강이름)는 교수를 결정하고 있다. 또한 여기서 교수는 특강이름을 결정하고 있다.
그런데 문제는 교수가 특강이름을 결정하는 결정자이지만 후보키가 아니다.

이처럼 후보키 집합이 아닌 칼럼이 결정자가 되어버린 상황을 BCNF를 만족하지 않는다고 한다.

(참고로 위 테이블은 제3 정규형까지는 만족하는 테이블이다 )

그렇기 때문에 BCNF 정규화를 만족시키기 위해서 위의 테이블을 (학번, 특강이름), (특강이름, 교수) 테이블로 분해해야 한다.

정규화 장단점

장점

데이터베이스 변경 시 각종 이상 현상들이 발생하는 문제점을 해결할 수 있다.
데이터베이스 구조 확장 시 정규화된 데이터베이스 구조에서는 새로운 데이터 형의 추가로 인한 확장 시, 그 구조를 변경하지 않아도 되거나 일부만 변경해도 된다. 이는 데이터베이스와 연동된 응용 프로그램에 최소한의 영향만을 미치게 되며 응용프로그램의 생명을 연장시킨다.
사용자에게 데이터 모델을 더욱 의미있게 제공
정규화된 테이블들과 정규화된 테이블들간의 관계들은 현실 세계에서의 개념들과 그들간의 관계들을 반영

단점

릴레이션의 분해로 인해 릴레이션 간의 연산(JOIN 연산) 증가되어 질의에 대한 응답 시간이 느려질 수 있다.

정규화를 수행한다는 것은 데이터를 결정하는 결정자에 의해 함수적 종속을 가지고 있는 일반 속성을 의존자로 하여 입력/수정/삭제 이상을 제거하는 것이다. 데이터의 중복 속성을 제거하고 결정자에 의해 동일한 의미의 일반 속성이 하나의 테이블로 집약되므로 한 테이블의 데이터 용량이 최소화되는 효과가 있다.

따라서 정규화된 테이블은 데이터를 처리할 때 속도가 빨라질 수도 있고 느려질 수도 있는 특성이 있다.

조회를 하는 SQL 문장에서 조인이 많이 발생하여 이로 인한 성능저하가 나타나는 경우에 반정규화 적용이 필요하다.

반정규화(De-normalization, 비정규화)

데이터베이스의 성능 향상을 위하여, 데이터 중복을 허용하고 조인을 줄이는 데이터베이스 성능 향상 방법이다.

반정규화는 조회(select) 속도를 향상시키지만, 데이터 모델의 유연성은 낮아진다.

반정규화의 대상

정규화에 충실하여 종속성, 활용성은 향상 되었지만 수행속도가 느려진 경우
다량의 범위를 자주 처리해야하는 경우
특정 범위의 데이터만 자주 처리하는 경우
요약/집계 정보가 자주 요구되는 경우
테이블에 지나치게 조인을 많이 사용하게 되어 데이터를 조회하는 것이 기술적으로 어려울 경우

반정규화 기법

계산된 컬럼 추가
배치 프로그램으로 총판매액, 평균잔고, 계좌평가를 미리 계산하고 그 결과를 특정 칼럼에 추가한다.

테이블 수직 분할
하나의 테이블의 두 개 이상의 테이블로 분할한다. 즉, 칼럼을 분할하여 새로운 테이블을 만드는 것이다.

테이블 수평분할
하나의 테이블에 있는 값을 기준으로 테이블을 분할하는 방법이다.

반정규화 주의사항

반정규화를 과도하게 적용하다 보면 데이터의 무결성이 깨질 수 있다.

또한 입력, 수정, 삭제의 질의문에 대한 응답 시간이 늦어질 수 있다.

레퍼런스
https://rebro.kr/159
https://mangkyu.tistory.com/110
https://github.com/JaeYeopHan/Interview_Question_for_Beginner/tree/master/Database#%EC%A0%95%EA%B7%9C%ED%99%94%EC%97%90-%EB%8C%80%ED%95%B4%EC%84%9C
https://code-lab1.tistory.com/48