7장 릴레이션 정규화

 

- DB를 대충 설계하면 제어할 수 없는 데이터 중복이 일어남 -> 갱신 이상을 유발

- 정규화를 통해 이를 최소화 하나 정규화가 진행될수록 효율성이 떨어짐

-> 갱신 이상이 발생하지 않도록 한 다음 어디까지 정규화 할 것인지를 생각.

 

- 스키마가 (a,b,c)라면 (a,b)만 추가하는게 불가능. (b,c)도 추가해야 (a,b,c)가 만들어짐.

- 삭제도 마찬가지. (b,c)만 지우고 싶은데 (a,b)도 같이 지워짐.

-> 일부만 수정하면 데이터의 불일치 발생

 

- 사원+부서에 관한 컬럼들이 있음. 여기서 부서를 3개까지 속할 수 있다고 한다던가, 부서를 하나로 제한한다고 한다면 컬럼의 변화가 생김

-> 부서 문제인데 사원 릴레이션이 영향을 받음.

 

- 이번엔 김창섭 레코드가 2개가 된다. 부서 추가/삭제를 레코드 삽입/삭제로 해결 할 수 있으니 조금 나음

-> 중복이 많이 발생하나, 저장공간 낭비가 엄청 치명적이진 않음.

 

- 중복 : 많이 발생하겠지. 저장공간 낭비가 엄청 치명적이진 않다

- 수정 : 일부 레코드에서 부서 이름을 바꾸면 불일치 상태에 빠짐

- 삽입 : 새 부서를 신설하더라도 사원이 하나도 없다면 부서 정보 입력 불가

- 삭제 : 유일한 사원이 있는 부서의 레코드를 지우면 부서에 관한 정보도 다 사라짐

-> 항상 세트로 발생함

 

- 수정 : 2번 부서를 PR부서로 바꾸려면 부서 테이블의 부서 이름만 바꾸면 됨

- 석제 : 김창섭 지웠다고 부서가 사라지는건 아님

- 삽입 : 4번 부서 만들 수 있음. 사원이 없어도 만들 수 있음.

 

 

- 함수적 종속성 : 릴레이션의 컬럼이 갖는 의미.

-> 학번에 들어올 수 있는 문자열이 어떤 것들인가. 이름엔 무엇이 들어올 수 있는가. 지금 들어있는 값들 뿐만 아니라 앞으로 어떤 레코드들이 들어올 수 있는지, 그럴때도 성공적으로 들어올 수 있는지.

 

- 하나 이상의 컬럼을 고유히 결정하는 컬럼이 결정자. 키가 아닌데도 결정자 역할을 하는 컬럼이 있음. 

ex) 한 사원이 여러 부서에 있을 수 있기에 직책은 사원번호와 부서번호를 알아야 결정됨 : (사원번호, 부서번호) -> 직책

- 어떤 레코드들이 들어와도 성립해야함. 레코드가 채워진 상태에서 정규화를 하면 늦음.

 

- 함수적 종속성 : 각 A값에 대해 한 개의 B값이 대응. like f(a) = b

- 두 다이어그램으로 표시하는데, 위에 것으로 많이 함

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

 

- 주어진 릴레이션 R에서 애트리뷰트 B가 애트리뷰트 A에 함수적으로 종속하면서 애트리뷰트 A의 어떠한 진부분 집합에도 함수적으로 종속하지 않으면 애트리뷰트 B가 애트리뷰트 A에 완전하게 함수적으로 종속한다고 말한다.

ex) 사원 번호와 부서 번호를 알면 직책을 결정하지!

 

 

 

* 완전/부분 함수적 종속의 이해

• 완전 함수적 종속은 어떤 속성이 기본키에 대해 완전히 종속적일 때를 말함.
• 예를들어 수강 릴레이션이 (학번, 과목명, 성적, 학년)으로 되어 있고,

(학번,과목명)이 기본키일때, '성적'은 학번과 과목명이 같을 경우에는 항상 같은 성적이 온다.

즉 성적은 학번과 과목명에 의해서만 결정되므로 성적은 기본키에 완전 함수적 종속이 되는 것.

• 반면에 학년은 과목명에 관계없이 학번이 같으면 항상 같은 학년이 옴.

즉, 기본키의 일부인 학번에 의해서 학년이 결정되므로 학년은 부분함수적 종속이라고 함.

 

- 이러한 이행적 함수적 종속성이 있으면 갱신 이상이 나타난다

- 학번 알면 학과이름, 학과전화번호 결정.

- 근데 학과 이름을 알면 학과 전화번호가 식별됨. 즉 학과 전화번호는 학번으로, 또 학과 이름으로도 알 수 있다

→ 두 컬럼에 종속된다. 이런걸 이행적 함수성 종속성이라고 함.

 

- 릴레이션 분해는 위와 같은 문제를 야기하기에 만족해야하 조건이 있음

- 분해된 두 릴레이션을 JOIN하면, 오리지널 릴레이션들에 있는 레코드들과 동일한걸 얻을 수 있어야함

-> 쓸데없는 레코드가 발생하면 분해를 잘못한 것

- 손실 : 분해할 때 줄어든 것 뿐만 아니라 다시 조인했을 때 정보가 많아지는 것도 포함

-> 반드시 무손실 분해를 해야함!

 

- 학번이 외래키로 연결하는 역할

- 수강은 완전 함수적 종속성만 존재, 학생도 기본키가 컬럼 하나니까 완전 함수적 종속성만 존재

-> 학번을 사용하여 JOIN하면 오리지널 릴레이션이 완벽하게 구해짐

 

- 좌측은 오버한 것, 우측은 학번과 과목번호가 기본키인데 멍청하게 짤라서 조인하면 레코드가 8개가 될것

 

<제1 정규형>

- 모든 애트리뷰트가 원자값만 갖는다!

- 좌측 : 제1정규형을 만족하지만, 주소가 중복됨

- 우측 : 단일값이지만 주소가 반복되는 것도 repeating group으로 분류 -> 분해

-> 제1정규형의 정의가 애매하다. 

 

- 삽입 : 학생을 뽑기 전이면 중국어학과에 대한 학과이름과 전화번호를 넣을 수 없음. 키에 해당하는게 null이니까.

- 삭제 : 마지막 학생이 졸업하면 그 학과도 키가 null이 됨

- 수정 : 여러 학생이 있는 학과의 전화번호가 바뀌면 모두 다 수정해야함 -> DB 일관성 유지의 어려움

+ repeating group을 기준으로 보면 제1정규형도 만족하지 못함.

 

-> 우측 이미지와 같이 분해하여 부분 함수적 종속성이 없도록 한다면? : 제2정규형 만족

 

<제2 정규형>

- 제1정규형을 만족 + 후보 키에 속하지 않는 모든 컬럼이 기본키에 완전하게 함수으로 종속

-> 기본키가 1개의 컬럼이면 제1정규형을 만족하면 동시에 제2정규형도 만족하겠지?

 

- 삽입 : 새 학과 삽입시 동일 문제 발생

- 삭제 : 마지막 학생 졸업시 동일 문제 발생

- 수정 : 한 투플 수정시 다 수정안하면 일관성 문제

 

- 위는 제1,2 만족하지만 이행적 종속성을 금지하지 못함 : 학번이 아니어도 학과 이름으로 전화번호를 알 수 있으니!

-> 이행적 종속성이 없도록 분해! 학과 이름으로 JOIN하면 오리지널 릴레이션을 만들 수 있음
-> (b)와 같이 분해하면 제3 정규형 만족

 

<제3 정규형>

- 제2 정규형을 만족하면서, 이행적 함수적 종속성이 없어야 함. 키가 아닌 모든 컬럼은 기본키에 직접 완전히 종속!

 

- 이 스키마는 일단 개념 설계를 잘못한 예시.

- 한 학생이 여러과목 수강, 강사는 한 학기에 한과목만 맡음

-> 일단 제1, 2, 3종속성이 만족됨

- 물론 여전히 문제는 있음

- 수정 : 강사가 중간에 바뀌면 불일치 문제 발생할 수 있음

- 삽입 : 과목 신설되었는데 수강 학생이 없지? null 불가

- 삭제 : 마지막 학생이 수강취소하면 학생 null

However, 어떤 학생이 무슨 과목을 맡냐 하면 강사를 결정, 근데 강사를 알면 과목도 결정됨

- 강사는 기본키도 아닌 주제에 과목을 결정 -> 매우 드물다. BCNF를 만족하지 못함!

-> (학번,강사를 참조하는 외래키) (강사, 과목) 이렇게 독립시키면 됨.

 

<BCNF>

- (A,C)(C,B) / (A,C,D)(C,B)

- 컬럼이 많든 적든 이런 패턴을 보인다. 어떻게 얘를 분해할거냐?

 

 제1~3 정규형 만족하고, 모든 결정자가 후보 키여야 함. -> 앞의 사례에서 강사는 후보키가 아니었지?

-> 키가 아니면서 결정자 애트리뷰트 역할 하는 강사와, 그 강사에 종속되는 애트리뷰트 과목을 하나의 테이블로 분리. 그 릴레이션에서 결정자인 강사는 기본키가 된다.

 

<정리>

 

- 릴레이션이 1개가 있엇다. 제1 만족하려면 2개가 되고, 제2 만족하려면 3개가 되고, 제3 만족하도록 하면 4개가 되고. 그리고 BCNF 만족하도록 하면 5개가 됨.

→ 최소! 최초엔 조인도 안하고 다 골라낼 수 있었는데, 갱신 이상이 있으니까 분해하고 분해하다보니 이렇게 됨.

 

비정규 릴레이션 == 도메인이 원자값 ==> 1NF

== 부분적 함수 종속제거 ==> 2NF

== 이행적 함수 종속제거 ==> 3NF

== 결정자이면서 후보키가 아닌것 제거 ==> BCNF

== 다치종속제거 ==> 4NF

== 조인 종속성 이용 ==> 5NF

 

- 정규화가 진행될수록 갱신 이상이 발생되지 않기에 무결성 제약조건을 위한 코드의 양(C,PY등)도 감소됨.

- 실렉트의 비율이 70-80%정도라고 하는데, 20%정도의 인서트 업데이트 딜리트의 문제를 해결하기위해 정규화를 막 진행시키면 릴레이션이 막 늘어나고 → 실렉트문의 성능이 떨어짐.

→ 성능상으로는 높은 정규형을 갖는다고 해서 최선이 아니다.

 

- a가 제3 정규형을 만족하지 못하니 b로 쪼갬 -> 학번을 모르니까 둘을 JOIN해야함!

-> 성능저하

 

-