1. DB 설계란

정규화 이론(Normalization Theory)

2 정규화

관계형 모델을 보완하는 이론

정규화 이론은 RDB를 잘 다루는 데 필요한 기술이며 관계형 모델을 전제로 구축된 DB 설계 이론이다.

변칙을 방지 할 수 있다

정규화의 가장 중요한 이점은 모순을 방지할 수 있다는 점이다.

모순: 데이터가 논리적인 불일치가 일어나는 상태 → 모순이 발생한 상태를 변칙(Anomalies)라고 한다.

변칙을 일으키는 원인은 중복

중복: 한 개의 릴레이션에 같은 데이터가 여러 개 존재하는 것을 말한다.

모순의 원인은 중복이다. 모순이 생기지 않으려면 중복을 제거하면 된다.

→ 중복을 제거하는 작업이 바로 정규화이다.

3. 정규형

정규화에는 몇 가지 단계가 있고 높은 단계로 갈수록 더 좋은 상태(중복이 적은 상태)가 된다. 정규화의 각 단계는 정규형(Normal Form, NF)라고 한다.

정규형의 종류

• 제1 정규형(1NF)
• 제2 정규형(2NF)
• 제3 정규형(3NF)
• 보이스코드 정규형(BCNF)
• 제4 정규형(4NF)
• 제5 정규형(5NF)
• 제6 정규형(6NF)

정규형의 특징 중 하나는 높은 단계의 정규형은 자동으로 그 이전의 정규형 조건을 만족한다.

제1 정규형(1NF)

1NF를 하기 위한 조건은

릴레이션이어야 할 것

테이블이 1NF가 되기 위한 요건은 다음과 같다.

1. 행이 위에서 아래로 정렬돼 있지 않다.
2. 열이 왼쪽에서 오른쪽으로 정렬돼 있지 않다.
3. 중복하는 행이 존재하지 않는다.
4. 각 행과 열의 교차점(즉 열의 값)은 도메인(데이터형)에 속하는 요소의 값을 딱 한 개만 가진다.
5. 모든 열의 값은 정의된 것이어야 하고 각 행은 항상 존재한다.

컬럼이나 행의 순서

SQL의 사양으로 테이블의 칼럼에 순서가 존재한다. 테이블은 1NF의 조건을 만족하지 않으나 이를 문제삼지않는 방법은 컬럼이나 행의 위치에 의존하는 쿼리를 작성하지 않는 것이다.

컬럼의 순서에 의존하는 처리는 다음과 같다.

• SELECT * 로 모든 컬럼의 값을 검색, 응용프로그램이 컬럼의 위치에 따라 데이터에 접근하는 것
• ORDER BY 절의 인수로 select list 내에서 컬럼의 위치를 지정하는 것(ex: ORDER BY 1)

이러한 기능을 사용하지 않으면 1NF 요건을 충족시킬 수 있다.

중복되는 행을 제거

똑같은 행의 값이 최대 1개 이상 포함되지 않으면 된다.

실제로 저장되는 값이 중복되지 않게 해야한다. 실제로 중복되지 않으면 있든 없든 관계형 모델은 성립한다.

NULL이 포함되면 안된다

테이블의 모든 행, 모든 컬럼은 구체적인 값을 가져야 한다.

NULL을 처리하는 가장 좋은 방법은 테이블을 나누는 것이다.

응용프로그램이 처리 내용에 대해 필요할 때에 필요한 테이블에 데이터를 저장한다. 컬럼의 값이 NULL이라는 것은 응용프로그램이 아직 그 데이터가 필요하지 않기 때문이므로 다른 단계에 필요한 데이터라고 생각한다.

값의 원자성

관계형 모델에서는 의미가 있는 한 묶음의 데이터를 한 단위로 취급해야 한다. 예를 들어 메일 주소를 분해해 버리면 그 부분의 문자열에는 의미가 없어지게 된다.

이처럼 값을 조금 더 세세한 단위로 분해하지 않고 전체가 응용프로그램에서 의미가 있는 것이 아니면 속성의 값이 될 수 없다.

의미가 있는 한 묶음의 데이터는 무엇일까? → 도메인

도메인이란 속성이 가질 수 있는 값의 집합이다. 집합의 요소는 더이상 분해할 수 없는 원자다.

값이 열의 값이 될 가능성이 있는 집합, 즉 도메인 중에서 골라낸 요소의 하나라고 생각하면된다.

→ 응용프로그램이 처리할 수 있는 데이터 형식이 어떤 것인지 생각하는 것

→ 도메인의 설계가 바로 DB 설계의 첫걸음이다.

반복 그룹

반복 그룹은 한개의 열(혹은 속성)에 여러 개의 값을 콤마와 같은 구분자 등의 형태로 할당한 것이다. 이와 같은 데이터 형식은 컬럼 내의 값을 나누어서 연산해야 하므로 관계형 모델을 기반으로 논리를 만들 수 없다.

후보키와 슈퍼키

후보키는 그 릴레이션에 포함된 튜플의 값을 고유하게 하는 속성의 집합으로 기약(Irreducible)을 말한다. 기약이란 더는 속성을 줄일 수 없는, 즉 여분의 속성이 없는 상태를 말한다.

후보키라고 부르는 이유는 튜플의 값을 고유하게 요구하는 속성의 집합이 한 종류밖에 없기 때문이다.

SQL은 테이블에 기본키(Primary Key)가 있지만, 관계형 모델에는 기본키라는 개념이 없다. 릴레이션에 여러 갭의 후보키가 있는 경우 이 키들은 어떤 것도 의미적으로나 기능적으로 차이가 없고 어떤 후보키를 기본키로 정할지는 주관적인 문제이기 때문이다.

→ 정규화를 올바르게 설명한다면 기본키라는 단어는 나오지 않는다.

릴레이션을 도식화할 때 후보키에 밑줄을 그어 표시한다. 또한 후보키에 포함되지 않은 속성은 키가 아닌 속성(Non-prime Attribute)이라고 한다.

슈퍼키는 후보키의 슈퍼셋, 추가 속성을 가지는 키를 말한다. 슈퍼키도 튜플의 값이 고유하다는 점은 후보키와 같다. 단순하게 추가(고유한 값을 구하는 데 도움되지 않는) 속성을 가지고 있는 것뿐이다.

→ 후보키는 슈퍼키의 일종이며 가지고 있는 속성의 수가 최소한이라고 할 수 있다.

릴레이션은 중복된 튜플이 없으므로 최소한 1개의 후보키가 존재한다. 제목 전체에는 모든 속성이 포함되므로 제목은 반드시 슈퍼키가 돼야 한다.

키(Key)의 종류

후보키(Candidate Key)

1. 후보키는 릴레이션을 구성하는 속성들 중에서 튜플을 유일하게 식별하기 위해 사용하는 속성들의 부분집합, 즉 기본키로 사용할 수 있는 속성들을 말한다.

2. 하나의 릴레이션내에서는 중복된 튜플들이 있을 수 없으므로 모든 릴레이션에는 반드시 하나 이상의 후보키가 존재한다.

3. 후보키는 릴레이션에 있는 모든 튜플에 대해서 유일성과 최소성을 만족시켜야 한다.

유일성 : 하나의 키값으로 하나의 튜플만을 유일하게 식별할 수 있어야한다.

최소성 : 모든 레코드들을 유일하게 식별하는데 꼭 필요한 속성만으로 구성되어 있어야한다.

기본키(Primary Key)

1. 기본키는 후보키 중에서 선택한 주키(Main Key)이다.

2. 한 릴레이션에서 특정 튜플을 유일하게 구별할 수 있는 속성이다.

3. Null값을 가질 수 없다.

4. 기본키로 정의된 속성에는 동일한 값이 중복되어 저장될 수 없다.

대체키(Alternate Key)

1. 후보키가 둘 이상일 때 기본키를 제외한 나머지 후보키들을 말한다.

2. 보조키라고도 한다.

슈퍼키(Super Key)

1. 슈퍼키는 한 릴레이션 내에 있는 속성들의 집합으로 구성된 키로서 릴레이션을 구성하는 모든 튜플들 중 슈퍼키로 구성된 속성의 집합과 동일한 값을 나타나지 않는다.

2. 슈퍼키는 릴레이션을 구성하는 모든 튜플에 대해 유일성을 만족시키지만, 최소성은 만족시키지 못한다.

최소성 : 학번 + 주민번호를 사용하여 슈퍼키를 만들면 다른 튜플들과 구분할 수 있는 유일성은 만족하지만 학번이나 주민번호 하나만 가지고도 다른 튜플들을 구분할 수 있으므로 최소성은 만족시키지 못한다.

외래키(Foregin Key)

1. 관계를 맺고 있는 릴레이션 R1,R2에서 릴레이션 R1이 참조하고 있는 릴레이션 R2의 기본키와 같은 R1 릴레이션의 속성을 외래키라고 한다.

2. 외래키는 참조되는 릴레이션의 기본키와 대응되어 릴레이션 간에 참조관계를 표현하는데 중요한 도구이다.

함수 종속성(Functional Dependency, FD)

2NF ~ BCNF는 함수 종속성에 관한 정의이며 전부 함수 종속성을 이용해 설명할 수 있다. 그리고 릴레이션 내의 함수 종속성을 최대한 배제한 것이 BCNF다. 2NF와 3NF는 함수 종속성을 릴레이션에서 배제할 수 있으므로 최적의 상태라고 말하기는 어렵다.

→ BCNF가 될 때까지 정규화를 수행하자.

함수 종속성이란?

어떤 릴레이션 R이 있고, 제목의 부분집합 두 개를 A, B. 릴레이션 R의 모든 튜플이 A의 값이 같다면 B의 값도 같은 경우가 있을 수 있다. 이 경우에 한해서 B는 A에 함수 종속한다고 하고, 이러한 관계성을 A → B라고 기술한다.

→ A의 값을 알면 B의 값을 알 수 있다는 의미다. B는 중복이 허용된다.

릴레이션의 임의의 속성은 그 슈퍼키에 함수 종속한다.

이러한 함수 종속성은 함수 종속성의 정의에서 필연적으로 도출되므로 자명한 함수 종속성(trivial FD)라고 한다.

자명한 함수 종속성은 릴레이션에서 제외할 수 없다.

그러나, 자명하지 않은 함수 종속성(Non trivial FD)이 문제가 된다.

함수 종속성이란 키의 성질을 정의한 것이다. 일반적으로 키의 값이 정해지면 같은 튜플에 포함된 임의의 속성의 값을 구할 수 있다. 릴레이션이 자명하지 않은 함수(Non FD), A → B라는 함수 종속성을 가지고 있으면 A는 그 릴레이션의 키가 아니므로 반복해서 나타낸다.

→ A의 값이 같다면 B의 값도 같다는 것이 숨겨진 키(Non FD)의 성질을 나타낸다.

2NF ~ BCNF에 관한 정규화는 이처럼 자명하지 않은 함수 종속성을 없애는 작업이다.

제2 정규형(2NF)

2NF는 후보키의 진부분집합에서 키가 아닌 속성(Non-prime Attribute)에 함수 종속성을 제거하는 작업이다. 진부분집합이란 부분집합 중에 원래 자신의 집합을 제외한 것을 말한다.

이런 함수 종속성을 부분 함수 종속성(Partial Functional Dependency)라고 한다.

릴레이션이 1NF이며 부분 함수 종속성을 갖지 않으면 그 릴레이션은 2NF가 된다. 키에 포함된 속성이 한 개밖에 없을 때는 공집합 외에 키의 진부분집합이 존재하지 않는다.

→ 1NF이며 후보키가 한 개의 속성으로 된 릴레이션은 자동으로 2NF가 된다.

중복이 릴레이션에 포함돼 있으므로 모순이 발생한 것이다. 이 릴레이션에는 {이름} → {학년} 이라는 함수 종속이 존재한다. → 같은 학생에 관해서 여러 번 학년이 등록되게 됐다.

이처럼 튜플 전체를 보면 값이 중복되지 않지만 그 일부에 중복이 존재하는 상태가 문제가 된다.

이것을 자명하지 않은 함수 종속성(Non FD)라고 한다. Non FD는 중복이 존재하고, 갱신할 때 변칙이 발생하는 원인이 된다. 중복되는 값을 한 번에 모두 갱신하는 것도 아니고 일부만 갱신하면 변칙이 생긴다.

무손실 분해

함수 종속성을 해결하려면 한 개의 릴레이션을 여러 개의 릴레이션으로 분해하는 작업을 수행한다. 분해할 때 필요한 작업은 프로젝션 이다. 한 개의 릴레이션에 대해 다른 두 개의 패턴의 프로젝션을 두 개의 릴레이션을 생성한다.

이 때 원래 릴레이션의 정보를 잃어버리지 않게 두 개의 릴레이션으로 분해할 수 있어서 이런 작업을 무손실 분해(Non-loss Decomposition)이라고 한다.

무손실 분해: 분해한 다음의 릴레이션에 포함된 정보를 사용해 원래의 릴레이션을 재구축할 수 있는 것을 말한다.

재구축할 대 사용되는 작업은 결합(JOIN)이다. 이와 같은 무손실 분해가 가능한지 아닌지의 기준이 되는 것은 함수 종속성과 같은 종속성이다.

함수 종속성이 존재할 때는 종속 관계가 있는 속성만 프로젝션해 한 개의 릴레이션으로 만든다.

{이름} → {학년} 으로 프로젝션을 수행한다. 또한, 종속된 속성을 제외하는 것 외에 다른 모든 속성에 프로젝션을 해 다른 한 개의 릴레이션을 만든다.

{학년}을 제외한 속성{이름, 수업}으로 프로젝션한다.

제3 정규형(3NF)

3NF는 추이 함수 종속성(Transitive Dependency)이라는 함수 종속성을 제거하는 작업이다. 추이 함수 종속성은 키가 아닌 속성 사이의 함수 종속성이다.

예를 들어 두 개의 키가 아닌 속성의 집합 X, Y에 함수 종속성 X → Y 가 존재한다고 하면, X의 값은 슈퍼키의 값을 알면 정해지고 X의 값이 정해지면 Y의 값도 정해진다.

{학과} → {대표번호} 라는 함수 종속성이 존재한다. 3NF의 무손실 분해는 2NF와 거의 같다.

보이스코드 정규형(BCNF)

BCNF는 자명하지 않은 함수 종속성(Non FD)이 모두 제거된 상태의 정규형이다. 따라서 이 이상 함수 종속성에 의한 무손실 분해는 할 수 없다. 3NF이며 BCNF가 아닌 정규형이란 어떤것일까?

2NF는 후보키의 진부분집합에서 키가 아닌 속성에 대한 함수 종속성을 제거했다. 3NF는 키가 아닌 속성에서 다른 키가 아닌 속성에 대한 함수 종속성을 제거했다. 남아있는 패턴은 키가 아닌 속성에서 후보키의 진부분함수에 대한 함수 종속성이다.

문제의 이 릴레이션은 진도 쪽이 수업보다 자세한 정보라는 점이다. 진도 쪽을 알면 수업을 알 수 있는 관계성을 가진다. 즉, {진도} → {수업}라는 함수 종속성이 존재한다.

→ {이름, 진도}, {이름, 수업} 라는 속성의 집합이 후보키가 될 수 있다.

따라서 이 릴레이션은 2NF와 마찬가지로 {이름, 진도}, {진도, 수업} 으로 프로젝션해 무손실 분해가 가능하다. 이처럼 3NF이지만 BCNF가 아닌 경우는 후보키가 또 없는지부터 검토해보는 게 좋다.

BCNF는 키만 함수 종속하는 릴레이션이다.

요약

실제로는 BCNF까지의 지식만으로도 충분히 위력을 발휘한다.