[오라클 DB] 릴레이션 분해와 결정자, 함수적 종속성

릴레이션 분해

 

하나의 릴레이션을 두 개 이상의 릴레이션으로 나누는 것으로,

릴레이션의 분해는 필요한 필요한 경우에는 분해된 릴레이션들로부터 원래의 릴레이션을 다시 구할 수 있음을 보장해야 한다는 원칙을 기반으로 한다.

 

릴레이션 분해 후의 잠재적인 문제

 

릴레이션이 분해되기 전에는 조인이 필요 없는 질의가 분해 후에는 조인을 필요로 하는 질의로 바뀔 수 있다. 즉, 분해를 했을 때 성능상에 문제가 있는지(테이블 수 증가에 따른 성능 저하) 체크하고 분해를 결정해야 한다.

 

분해를 잘못하면 두 개의 릴레이션으로부터 얻을 수 있는 정보가 원래의 릴레이션이 나타내던 정보보다 적을 수도 있다.

즉, 정보의 손실을 의미하며 이런 경우에는 분해된 릴레이션들을 사용하여 원래 릴레이션을 재구성하지 못할 수도 있다.

 

 

무손실 분해(Lossless Decomposition)

 

분해된 두 릴레이션을 조인하면 원래의 릴레이션에 들어 있는 정보를 완전하게 얻을 수 있음을 의미한다.

 

정보의 손실은 원래의 릴레이션을 분해한 후에 생성된 릴레이션들을 조인한 결과에 들어 있는 정보가 원래의 릴레이션에 들어 있는 정보보다 적은 것을 의미한다.

 

 

---

 

나쁜 설계의 예를 보자.

 

사원이름	사원번호	주소	전화번호	부서번호	부서이름
김창섭	2106	우이동	726-5869	1	영업
김창섭	2106	우이동	726-5869	2	기획
박영권	3426	사당동	842-4538	3	개발
이수민	3011	역삼동	579-4685	2	기획
이수민	3011	역삼동	579-4685	3	개발

 

위의 나쁜 설계를 해결하고자 정규화를 진행하여야 한다.

정규화를 진행하면 릴레이션 스키마를 함수적 종속성과 기본 키를 기반으로 분석하여 원래의 릴레이션을 분해하는 과정을 거치게 된다.

 

사원이름	사원번호	주소	전화번호	부서번호	부서이름
김창섭	2106	우이동	726-5869	1	영업
김창섭	2106	우이동	726-5869	2	기획
박영권	3426	사당동	842-4538	3	개발
이수민	3011	역삼동	579-4685	2	기획
이수민	3011	역삼동	579-4685	3	개발

 

사원 릴레이션과 부서 릴레이션으로 다음과 같이 분해할 수 있다.

 

사원 릴레이션

사원이름	사원번호	주소	전화번호	부서번호
김창섭	2106	우이동	726-5869	1
김창섭	2106	우이동	726-5869	2
박영권	3426	사당동	842-4538	3
이수민	3011	역삼동	579-4685	2
이수민	3011	역삼동	579-4685	3

 

부서 릴레이션

 

부서번호	부서이름
1	영업
2	기획
3	개발

 

사원 릴레이션과 부서 릴레이션을 자연 조인하면 원래의 릴레이션을 얻을 수 있어, 무손실 분해임을 알 수 있다.

 

이와 같이 릴레이션을 분해하면 갱신 이상 문제는 다음과 같이 해결된다.

 

1. 부서 이름을 수정하는 경우 (수정 이상)
어떤 부서에 근무하는 사원이 여러 명 있더라도 사원 릴레이션에는 부서 이름이 포함되어 있지 않으므로, 수정 이상이 나타나지 않는다.

2. 새로운 부서를 삽입하는 경우 (삽입 이상)
만일 어떤 신설 부서에 사원이 한 명도 배정되지 않았더라도, 부서 릴레이션의 기본 키가 부서번호이므로 이 부서에 관한 정보를 부서 릴레이션에 삽입할 수 있다.

3. 마지막 사원 튜플을 삭제하는 경우 (삭제 이상)
만일 어느 부서에 속한 유일한 사원에 관한 튜플을 삭제하더라도, 이 부서에 관한 정보는 부서 릴레이션에 남아 있다.

 

하지만 이는 갱신 이상만 해결하였다.

 

데이터 중복 문제를 해결하기 위해, 더 나아가 효율적인 릴레이션 분해를 위해 우리는 결정자와 함수적 종속성에 대해 먼저 알 필요가 있다.

 

---

 

결정자(determinant)

 

어떤 애트리뷰트의 값은 다른 애트리뷰트의 값을 고유하게 결정할 수 있다.

결정자는 주어진 릴레이션에서 다른 애트리뷰트(또는 애트리뷰트들의 집합)를 고유하게 결정하는 하나 이상의 애트리뷰트를 의미한다.

 

 

 

위와 같은 경우 'A가 B를 결정한다' , 'A는 B의 결정자이다'  또는 'B는 A의 종속자이다' 라고 한다.

 

아래의 예시 릴레이션을 보자.

 

사원 릴레이션

사원번호	사원이름	주소	전화번호	직책	부서번호	부서이름
4257	정미림	홍제동	731-3497	팀장	1	홍보
1324	이범수	양재동	653-7412	프로그래머	2	개발
1324	이범수	양재동	653-7412	웹 디자이너	1	홍보
3609	안명석	양재동	425-8520	팀장	3	기획

 

위 릴레이션에서, 사원번호는 사원이름, 주소, 전화번호를 고유하게 식별할 수 있다.

 

예로, 후보 키인 사원번호가 1324라면, 해당 튜플의 사원이름, 주소는 각각 '이범수' , '양재동' 으로 고유하게 식별된다. 전화번호 또한 마찬가지이다.

하지만 사원번호가 1324라고 직책과 부서이름이 특정되지는 않는다.

직책이 프로그래머 혹은 웹 디자이너일 수도 있고, 부서가 개발 부서 혹은 홍보 부서일 수도 있기 때문이다.

 

하지만 반대로, 주소는 사원이름을 고유하게 식별하지 못한다.

주소가 양재동이라면 사원이름은 '이범수' 일수도, '안명석' 일수도 있기 때문이다.

 

그러므로, 다음과 같은 결정 관계를 알 수 있다.

 

사원번호 -> 사원이름

사원번호 -> 주소

사원번호 -> 전화번호

 

 

또한 다른 후보 키인 부서번호는 부서이름을 고유하게 식별할 수 있다.

 

예로, 부서번호가 1 이라면 부서이름이 홍보임을 알 수 있지만, 부서번호가 1이라고 사원이름이 '정미림' 일지, '이범수'일지 고유하게 식별할 수 없기 때문에 사원이름은 특정할 수 없다.

 

그러므로, 다음과 같은 결정 관계를 알 수 있다.

 

부서번호 -> 부서이름

 

 

---

 

함수적 종속성

 

정규화 이론의 핵심이며,

갱신 이상과 중복을 제거하기 위해 데이터베이스를 설계하는데 필수적이다.

 

속성들 간의 관련성을 의미하며, 함수적 종속성은 제 2정규형부터 BCNF까지 적용된다.

 

만일 애트리뷰트 A가 애트리뷰트 B의 결정자이면,
B가 A에 함수적으로 종속한다고 말한다.

 

다른말로 표현하면,

 

주어진 릴레이션 R에서, 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하는 필요 충분 조건은 각 A 값에 대해 반드시 한 개의 B 값이 대응된다는 것

 

또는 이렇게 말할 수 있다.

 

임의의 두 튜플 t1과 t2에 대해서 t1[A] = t2[A]이면 t1[B] = t2[B]이다.
이는, 릴레이션 내의 모든 튜플에서 하나의 t[A] 값에 대한 t[B] 값이 항상(무조건) 하나이다.

 

 

다음의 예시를 다시 살펴보자.

 

 

사원 릴레이션

사원번호	사원이름	주소	전화번호	직책	부서번호	부서이름
4257	정미림	홍제동	731-3497	팀장	1	홍보
1324	이범수	양재동	653-7412	프로그래머	2	개발
1324	이범수	양재동	653-7412	웹 디자이너	1	홍보
3609	안명석	양재동	425-8520	팀장	3	기획

 

 

위 릴레이션에서 결정자와 종속자는 위에서 밝혀냈듯, 다음과 같다.

 

사원번호가 사원이름, 주소, 전화번호의 결정자
 = 사원이름, 주소, 전화번호는 사원이름에 함수적으로 종속

부서번호는 부서이름의 결정자
 = 부서이름은 부서번호에 함수적으로 종속

 

또한, 직책 애트리뷰트는 복합 키 형태의 (사원번호, 부서번호) 에 함수적으로 종속한다.

사원번호만으로 직책 애트리뷰트 값을 결정할 수 없고, 부서번호만으로 직책 애트리뷰트 값을 결정할 수 없기 때문이다.

 

 

---

 

함수적 종속성의 종류로는 완전 함수적 종속성(Full Functional Dependency), 부분 함수적 종속성(Partial Functional dependency), 이행적 함수적 종속성(Transitive Functional Dependency)이 존재한다.

 

 

 

완전 함수적 종속성(FFD : Full Functional Dependency)

 

A -> B는 임의 애트리뷰트 X ∈ A에 대해서
(A - {X}) -> B가 성립하지 않는 경우의 함수적 종속성(FD)

 

종속자가 기본 키에만 종속된다는 의미이다.

만약 기본 키가 여러 속성으로 구성되어 있을 경우(복합 키) 기본 키를 구성하는 모든 속성이 포함된 기본 키에 종속된 경우이다.

 

 

부분 함수적 종속성(Partial Functional dependency)

 

A -> B는 임의 애트리뷰트 X ∈ A에 대해서
(A - {X}) -> B가 성립하는 경우의 함수적 종속성(FD)

 

종속자가 기본 키가 아닌 다른 속성에 종속하거나 기본 키가 여러 속성으로 구성되어 있을 경우 기본 키를 구성하는 속성 중 일부에만 종속된 경우를 의미한다.

 

기본 키 쌍 (X, Y)가 존재하고, 다른 애트리뷰트 A, B가 존재할 때,

(X, Y) -> A , (X, Y) -> B와 같이 기본 키 쌍 전체가 각 애트리뷰트에 종속되는 것이 아닌,

X -> A, X -> B, Y -> B와 같이 기본 키를 구성하는 속성 중 일부에만 종속되거나

A -> B와 같이 기본 키가 아닌 다른 속성에 애트리뷰트가 종속되는 경우, 부분 함수적 종속성이라고 한다.

 

부분 함수적 종속성을 띄면 중복, 갱신 이상이 발생할 수 있기에 릴레이션 분해를 통해 제거해야 한다.

 

완전 함수적 종속성과 부분 함수적 종속성은 기본 키가 복합 키 형태일 때에만 따진다.

단일 애트리뷰트면 논하지 않는다.

 

 

 

fd1은 사원이름, 주소, 전화번호 애트리뷰트가 기본 키 쌍인 (사원번호, 부서번호) 중 일부분인 사원번호에 대해서만 종속되기에 부분 함수적 종속성을 띄고 있다.

 

fd2는 부서이름 애트리뷰트가 기본 키 쌍인 (사원번호, 부서번호) 중 일부분인 부서번호에 대해서만 종속되기에 부분 함수적 종속성을 띄고 있다.

 

fd3는 직책 애트리뷰트가 기본 키 쌍인 (사원번호, 부서번호) 집합 전체에 대해 종속되어있음과 동시에, 부분 키 각각에 대해서는 종속되지 않기에, 완전 함수적 종속성을 띄고 있다.

 

 

정리하면 다음과 같다.

 

직책은 (사원번호, 부서번호)에 완전하게 함수적으로 종속한다.

사원이름, 주소, 전화번호는 사원번호에 대해서만 함수적으로 종속한다.

부서이름은 부서번호에 대해서만 함수적으로 종속한다.

 

 

이행적 함수적 종속성(Transitive Functional Dependency)

 

한 릴레이션의 애트리뷰트 A, B, C가 주어졌을 때, 애트리뷰트 C가 이행적으로 A에 종속한다(A -> C)는 것의 필요 충분 조건은 다음과 같다.

 

 

A가 릴레이션의 기본 키라면 키의 정의에 따라 A -> B와 A -> C가 성립한다.
만일 C가 A 외에 B에도 함수적으로 종속한다면 C는 A에 직접적으로 함수적으로 종속하면서 B를 거쳐서 A에 이행적으로 종속한다고 말한다. (B가 C를 결정)

 

이행적 함수적 종속성 또한 중복 및 갱신 이상이 발생할 수 있어서 릴레이션 분해를 통해 없애야 한다.

 

 

 

fd1은 학과이름, 학과전화번호 애트리뷰트가 기본 키 쌍인 (학번, 과목번호) 중 일부분인 학번에 대해서만 종속되기 때문에 부분 함수적 종속성을 띄고 있다.

 

fd2는 학과전화번호 애트리뷰트가 학번에 의해 부분 함수적 종속성을 가지면서 학번의 또 다른 부분 함수적 종속성인 학과이름에 함수적 종속성을 가진다( 학과이름 -> 학과전화번호 )

그러므로, 학과전화번호는 학번에 이행적 함수적 종속성을 가진다.

 

fd3은 학점 애트리뷰트가 기본 키 쌍인 (학번, 과목번호) 집합 전체에 대해 종속되어있음과 동시에, 부분 키 각각에 대해서는 종속되지 않기에, 완전 함수적 종속성을 띄고 있다.