[2021 정보처리기사-3과목] #정규화(Normalization)1,2,3,4차

 

[정보처리기사 3과목 필기 예상 키워드] 목록으로 돌아가기
과목: 3. 데이터베이스 구축 챕터: 1장 논리 DB 설계 키워드: 정규화(1, 2, 3, 4차)	#1차 - 반복그룹 제거 #2차 - 부분 함수적 종속성 제거 #3차 - 이행적 종속성 제거 #BCNF (3차 강화버전) #4차 - 다치 종속 제거

 

 

 

데이터베이스 정규화란?

데이터베이스 정규화는 어렵지만 중요한 파트이다. 관계형 데이터베이스의 설계에서 중복을 최소화하고 데이터를 구조화하는 프로세스를 정규화(Normalization)라고 한다. 정규화의 목표는 이상이 있는 관계를 재구성하고 작고 잘 조직된 관계를 생성하는 것에 있다.

 

일반적으로 정규화란 크고, 제대로 조직되지 않은 테이블들과 관계들을 작고 잘 조직된 테이블과 관계들로 나누는 것을 포함한다. 정규화의 목적은 하나의 테이블에서 데이터의 삽입, 삭제, 변경이 정의된 관계들로 인하여 데이터베이스의 나머지 부분들로 전파되게 하는 것이다.

 

즉, 논리 데이터 모델링을 쪼개는, 상세화 단계가 바로 정규화이다. 모델링 단계 중 가장 중요한 파트로, 이 정규화를 통해 품질 및 성능이 향상된다. 

 

✔ 저장공간 최소화
✔ 무결성 유지 극대화
✔ 안전성 최대화

 

 

 

 

정규화를 이해하기 위한 용어 정리

http://dev-ahn.tistory.com/66

Relation(= Table):
관계형 데이터베이스(RDBMS)에서 정보를 구분하여 저장하는 기본 단위를 말한다. 예로 부서에 관한 정보를 저장하는 부서 Relation(혹은 부서 Table)이 있다.

 

 

Domain(도메인):
Relation에서 각각의 속성(Attribute)들이 취할 수 있는 같은 타입의 원자 값들의 집합. 즉, 한 속성(Attribute)에 나타날 수 있는 값의 범위. 예를 들면 학년 속성은 1~6까지의 값을 가질 수 있고 이를 도메인이라 한다.

 

 

Atomic Value(원자 값):
더 이상 분리될 수 없는 값.

 

 

스키마(Schema): 
데이터를 설명하는 데이터, 메타데이터(Metadata)라고도 한다. 예를 들어 이오니, 2020-02-02 라는 데이터가 있을 때 이는 이름, 생년월일 이라는 스키마로 설명할 수 있다.

 

 

엔티티(Entity):
사람, 장소, 사물, 사건 등과 같이 독립적으로 존재하면서 고유하게 식별이 가능한 실세계의 객체를 의미한다. 예를 들어 사원번호가 2106이고 이름이 이오니인 사원, 과목 코드가 CS372인 OS등이 있다. 이렇게 실체가 있는 것 뿐만 아니라 추상적인 것도 있다. 프로젝트 번호가 P101인 프로젝트, 학과 번호가 D315인 학과는 추상적인 개념의 엔티티가 될 수 있다. 

 

 

엔티티 집합(Entity Set): 
동일한 속성(attribute)를 가진 엔티티들의 집합이다. 예를 들어, 한 회사의 모든 사원들은 "Employee"라는 엔티티 집합을 이루고, 모든 부서들은 "Department" 엔티티 집합에 속한다. 어떤 사원은 Employee와 Manager 라는 두 가지 엔티티 집합에 속할 수 있기 때문에, 엔티티 집합에 속한 요소들이 항상 서로 다르지는 않다. 엔티티 타입(Entity Type) 이라고도 한다.

 

 

키(Key): 
데이터베이스에서 조건에 만족하는 튜플을 찾거나 순서대로 정렬할 때 튜플들을 서로 구분할 수 있는 기준이 되는 속성(Attribute)를 말한다. (한 Relation에서 각각의 튜플(행)을 유일하게 식별하기 위해 사용하는 하나 혹은 그 이상의 속성들의 집합)

 

 

 

 

 

1, 2, 3, 4차 정규화

✔ 1차 - 반복그룹 제거
✔ 2차 - 부분 함수적 종속성 제거
✔ 3차 - 이행적 종속성 제거
✔ BCNF - 모든 결정자가 후보키 집합에 속한다
✔ 4차 - 다치 종속 제거

 

 

 

 

정규화 [ # 1차 -  반복그룹 제거 ]

스크린샷 출처: 유튜브 주간컴공

ex. 
철수는 6월 25일, 도서관에서 파이썬 책과 정보처리 책 두 권을 대출하였다. 그러면 테이블에는 도서정보를 제외한 모든 데이터가 중복된다(식별자들의 유일성을 해침). 이러한 반복그룹을 제거하는 것이 1차 정규화이다. 1차 정규화를 위해 도출된 개체에는 "대출번호" 식별자를 추가해서 식별자들의 유일성을 해치지 않도록 한다.

제 1 정규화(First Normal Form, 1NF) 테이블(Relation)이 제 1 정규형(1차 정규화)을 만족했다는 것은 아래의 세 가지 조건을 만족했다는 것을 의미한다.

1. 어떤 Relation에 속한 모든 Domain이 원자값(Atomic Value)으로만 되어 있다.

2. 모든 Attribute에 반복되는 그룹(Repeating Group)이 나타나지 않는다.

3. 기본 키를 사용하여 관련 데이터의 각 집합을 고유하게 식별할 수 있어야 한다.

 

 

예제

 

예제 출처: 위키피디아

 

고객들의 신용카드 사용 내역을 표현한 테이블을 가정하자. 이 테이블이 제 1차 정규화가 안 되었을 경우, 데이터의 질의와 조작은 필요 이상으로 복잡해진다. 현재 이 테이블은 각 고객들마다 거래의 "반복"이 존재하기 때문에 제 1정규화를 만족하기 위한 3가지 조건 중 2번을 위반한 사례이다.

제 1 정규화(First Normal Form, 1NF)
만족	1. 어떤 Relation에 속한 모든 Domain이 원자값(Atomic Value)만으로 되어 있다. (=각 속성마다 하나의 값 존재)
위반	2. 모든 Attribute에 반복되는 그룹(Repeating Group)이 나타나지 않는다.
만족	3. 기본 키를 사용하여 관련 데이터의 각 집합을 고유하게 식별할 수 있어야 한다. (=거래번호)

 

1차 정규화를 통한 "반복그룹 제거"를 진행하기 위해 2가지 단계가 필요하다.

1. 각 거래들을 조사하기 위해 하나 이상의 고객들의 거래들의 모임으로부터 각 거래를 추출하여 그룹화한다.
2. 위 첫 단계의 결과로부터 질의의 결과를 도출한다.

예를 들어  모든 고객들에 대하여 2010년 10월에 이루어진 모든 거래의 거래량(금액)의 합을 구하기 위해서는:

1. 각 고객들로부터 고객들의 거래의 모임(거래번호, 일자, 잔고)을 각 거래들로 추출한다.
2. 일자가 2010년 10월인 거래들의 잔고의 합을 구한다

이 구조를 정규화하면 아래와 같다.

결과물

 

이제 각 행은 각 신용카드 거래를 의미하며, DBMS는 위 질의에 대해서 2010년 10월에 해당하는 모든 행을 구해서 그들의 잔고를 합치면 된다. 이 데이터 구조는 모든 값이 동등한 입장을 가지며, DBMS에 직접적으로 반영되어 질의에 잠재적으로 참여할 수 있게 된다. 

 

 

 

 

 

정규화 [# 2차 -  부분 함수적 종속성 제거]

스크린샷 출처: 유튜브 주간 컴공

2차 정규화를 진행하기 위해서는 함수적 종속성과 결정자를 알아야 한다. 예를들어 직원명은 동명이인이 있을 수 있기 때문에, 직원코드를 통해 직원을 구별한다. 따라서 직원코드가 먼저 결정되고(결정자) 각 코드에 직원명이 종속된다(함수적 종속). 이것을 "직원명 속성은 직원코드 속성에 종속된다(직원코드에는 반드시 직원명이 붙는다)" 라고 표현한다.

대출일자, 직원코드, 고객코드는 스스로 기록되는 결정자이다. 나머지 속성인 직원명과 고객명, 연락처, 대출금액은 이 결정자들에 의한 종속자이다. 대출금액 같은 경우 3가지의 결정자 중 하나라도 없다면 식별이 불가능하다(결정자를 모두 필요로 하는 경우). 이와 같은 경우를 함수 완전 종속이라고 한다. 

 

반면에 직원명은 대출일자와 고객코드가 없어도 식별할 수 있고, 고객명과 연락처는 대출일자와 직원코드가 없어도 식별이 가능하다. 이런 경우를 부분 함수 종속이라고 한다. 이 부분함수 종속을 별도의 개체로 분리하여 모든 컬럼이 완전 함수적 종속을 만족하게끔 만드는 것이 2차 정규화이다. (부분 함수적 종속을 모두 제거한다)

 

 

 

예제

https://wkdtjsgur100.github.io/

 

제 2 정규화(Second Normal Form, 2NF)

모든 컬럼이 완전 함수적 종속을 만족한다 (= 부분 함수적 종속을 제거한다)

 

위 [Electric Toothbrush Models] 표를 살펴보면, Model과 Manufacturer를 알면 Model Full Name 필드를 아예 유지하지 않거나 참조하지 않아도 결정된다. (Manufacturer 또는 Model만 아는 상태에서는 두 속성 모두 고유한 결정자 역할을 하지 못하기 때문에 Model Full Name을 결정할 수 없다.)

따라서 {Manufacturer, Model }➡Model Full Name 의 형태로 완전 함수 종속된다고 할 수 있다. 

 

하지만 {Manufacturer, Model}➡Manufacturer Country 에서 Model과 Manufacturer Country는 아무 연관 관계가 없기 때문에 Manufacturer Country는 Manufacturer와만 종속관계에 있게 된다.

이를 부분 함수 종속이라고 하게 되는 것이다. 

 

 

 

따라서, 부분 함수 종속을 제거한 이후의 테이블은 아래와 같고, 이는 제 2 정규형을 만족한(2차 정규화) 테이블이다.

 

 

 

 

 

정규화 [# 3차 -  이행적 종속성 제거]

스크린샷 출처: 유튜브 주간컴공

표를 살펴보면, C는 B에 종속되어있고, B는 A에 종속되어있다. 이를 다시 표현하면 C는 A에 간접적으로 종속되어있다고 할 수 있다. 이것을 이행적 종속이라고 말한다. 이러한 현상이 나타났을 때 발생할 수 있는 문제는, 특정 책의 가격이 변경되었을 경우 관련 데이터를 모두 수정하지 않으면 일관성이 훼손(*수정이상)된다. 

 

이를테면 "n개의 동일한 정보처리" 책 이지만, 도서 번호(B01,B04...) 로 구분해놓았기 때문에 각각의 도서 번호에 할당되어있는 단가를 모두 수정해야 일관성이 유지되는 것이다. 다음으로, 새로운 책을 구매했는데 대출기록이 아직 없다면 책의 정보를 기록할 곳이 없다(*삽입이상) 같은 이유로 대출기록을 삭제할 때 책의 정보가 사라질 우려도 있다(*삭제이상) 이러한 이상현상이 생기는 이유가 바로 이행적 종속성 때문이다. 이행적 종속을 막기 위해서는 A와 B, B와 C를 각각 다른 개체로 분리하면 된다. 

*수정이상(갱신이상, Update): 속성이 변경되었을 때 여러 줄의 데이터를 갱신해야 한다. 이로 인해 데이터의 불일치(incosistency)가 발생할 수 있다

*삽입이상(Insert): 만약 속성값이 비게 될 경우 NULL값이 들어간다.

*삭제이상(Deletion): 만약 특정 속성값을 비우게 되면 해당 레코드가 아예 테이블에서 지워질 수 있다.

 

 

예제

제 3 정규화(Third Normal Form, 3NF)

1. Relation이 제 2정규화 되었다 (=완전 함수 종속만 남아있다)

2. 기본 키(Primary Key)가 아닌 속성(Attribute)들은 기본 키에만 의존해야 한다. (= 기본 키가 아닌 컬럼에 종속되지 않아야 한다)

 

https://wkdtjsgur100.github.io/

 

위 릴레이션(테이블)에서는 {Tournament, Year}가 후보키가 될 수 있다. 그러나 Winnder Date of Birth 속성은 우선 기본키가 아닌 속성인 Winner에 종속된 채 {Tournament, Year}에 의존하고 있는 것을 알 수 있는데, 이는 3NF의 첫번째 속성을 위반한 것이 된다. 3차 정규화는 2NF를 만족하며, 기본키 컬럼이 아닌 컬럼들이 서로 종속되지 않아야 한다. 따라서 릴레이션을 둘로 나눠준다.

다시 말해 Winners가 (Tournament, Year)에 의해 결정되고, Date of Birth가 Winner에 의해 결정되는 것이기 때문에 결정자를 기준으로 두 개의 릴레이션으로 나눠주는 것이다.

 

https://wkdtjsgur100.github.io/

 

 

 

 

 

정규화 [BCNF]

BCNF(Boyce and Codd Normal Form)은 3차 정규형을 조금 더 강화한 버전이라고 볼 수 있다. 이는 3차 정규형으로 해결할 수 없는 이상현상을 해결할 수 있다. BCNF는 3차 정규형을 만족하며 모든 결정자가 후보키 집합에 속한 정규형이다. 아래와 같은 경우를 생각해보면 후보키는 수퍼키중에서 최소성을 만족하는데, 이 경우 (학생, 과목) 이다. 그리고 이 릴레이션은 한 명의 교수는 한 개의 과목만 담당한다는 제약조건이 있다.

 

 

(학생, 과목)은 해당 Low를 유일하게 구분할 수 있다. 그런데 이 테이블의 경우 (교수) 가 결정자이다. 즉, 교수가 정해지면 과목이 결정된다. 하지만 교수는 후보키가 아니다. 그럼에도 사전에 정의된 제약조건(한 명의 교수는 한 개의 과목만 담당한다)에 의해서 과목을 결정짓는다. 이 경우에 BCNF를 만족하지 못한다고 할 수 있다. 3차 정규형을 만족하면서 BCNF는 만족하지 않는 경우는 바로 일반 컬럼이 후보키를 결정하는 경우(교수가 과목코드를 결정)이다.

위와 같이 테이블이 구성된 경우에는 다음과 같은 문제가 발생한다.

 

(1) 삽입이상

새로운 교수인 이오니 교수가 DE123 과목을 맡게되면 아직 모집된 학생이 없을 수 있기 때문에 학생 번호에 NULL 이 들어가게 된다. 하지만 (학생, 과목)은 우리가 정의한 기본키이므로 참조 무결성 제약조건에 의해 기본키에는 NULL값이 들어가면 안된다.

(2) 삭제이상

김인영 교수가 맡고있는 AB123 과목을 학생1이 수강포기했다. 그러면 AB123 과목에 대한 정보를 모두 잃게 된다.

 

해결방법: 아래와 같이 릴레이션을 두 개로 분해한다.

 

 

✔ 이렇게 교수-과목 릴레이션, 학생-과목-학점 릴레이션으로 분해하고나면 새로운 교수가 들어올 경우 교수 릴레이션에 데이터를 추가할 수 있다. 삽입이상이 해결된다.

✔ 그리고 학생 1번이 AB123 과목을 수강 포기하더라도 우리는 김인영 교수의 정보를 잃지 않을 수 있다. 교수 테이블에 그대로 남아있기 때문이다. 삭제이상이 해결된다.

 

 

 

 

정규화 [# 4차 -  다치 종속 제거]

스크린샷 출처: 유튜브 주간컴공

스크린샷 출처: 유튜브 주간컴공

 

속성(Attribute)가 원자값(Atomic Value)이 아닌 값을 여러개 가지는 릴레이션을 분해하여 속성(Attribute)가 원자값(Atomic Value)을 갖도록 하는 정규형이다. (제 4 정규형은 이전 단계인 BCNF를 만족한 상태여야 한다)

성향이 "중립"인 사람을 찾으면, 세 명의 길동이가 나타난다. 이 경우에, (이름,학번) 속성이 성향 속성에 "다치 종속" 되어 있다고 표현한다. 다치 종속이 전혀 없을 수는 없지만 둘 이상 존재할 경우 문제가 발생하기 때문에 최소 하나의 다치 종속만 존재하도록 개체 속성을 분리하는 작업이 필요하다.

 

 

예제

 

출처: https://mozi.tistory.com/110

 

 

 

 

정규화 키워드 정리

#데이터 종속성

✔ 함수적 종속(가장 대표적): A 속성이 B 속성을 유일하게 식별한다 = B는 A에 종속됨 (A->B)
✔ 다치 종속(Multi-Valued Dependency): 둘 이상의 같은 원자값이 종속 됨


#결정자 / 종속자

✔ 결정자: 기준이 되는 값, 스스로 정해짐
✔ 종속자: 결정자에 의해 정해지는 값
✔ 결정자 X는 반드시 Y와 연결된다 (함수적 종속)
✔ 이행종속: 직/간접 종속이 한 개체에서 이루어짐(X->Y->Z)
✔ 직접종속: X->Y->Z 에서 X와 Y의 관계
✔ 간접종속: X->Y->Z 에서 X와 Z의 관계

 

#이상현상
릴레이션 조작 시 데이터들이 불필요하게 중복되어 예기치 않게 발생하는 곤란한 현상: anomaly
갱신이상 / 삭제이상 / 삽입이상

 

 

 

 

 

 

 

 

---

 

[2021 정보처리기사 필기 총정리] 3과목 (상시업데이트)

참고: 시나공 정보처리기사 필기 (저자: 강윤석, 김용갑, 김우경, 김정준 | 출판사: 길벗), 유튜브 주간컴공TV