데이터베이스 정규화이론 - BCNF 정규형

오늘은 보이스/코드 정규형이라 불리는 BCNF 정규형에 대해서 알아보겠습니다.

BCNF는 Boyce/Codd Normal Form의 약자인데요.

BCNF는 제 3정규형보다 강한 정규형입니다.

즉, BCNF를 만족한다면 제 3정규형도 만족하지만,

제 3정규형만 만족한다면 BCNF를 만족할지는 살펴볼 필요가 있습니다.

그런데 다행히도 제 3정규형을 설명할 때 사용했던 예제는 모두

BCNF를 만족하고 있습니다.

그래서 오늘은 조금은 다른 예로 BCNF를 알아보려고 합니다.

오늘 살펴볼 강좌신청 릴레이션 입니다.

이 릴레이션에는 제약사항이 있는데요.

바로 한명의 강사는 한 개의 인터넷 강좌만 담당할 수 있다는 것입니다.

참고로 고객은 여러 강좌를 선택할 수 있습니다.

이 전제조건으로 생각을 했을 때 위 릴레이션을 살펴보면,

{고객아이디, 인터넷강좌}로 담당강사번호를 결정할 수 있다는 것을 알 수 있습니다.

또한 {고객아이디, 담당강사번호}로 인터넷강좌를 결정할 수도 있습니다.

즉 후보키가 두 개가 되는 것이죠.

그러나 P.K는 하나만 되어야하기 때문에 둘 중에서

하나를 선택해야만 합니다. (원하는 것을 선택하시면 됩니다.)

저는 {고객아이디, 담당강사번호}로 하겠습니다.

여기서 바로 문제점이 발생합니다. {인터넷강좌}는 후보키가 아님에도

사전에 정의된 제약조건에 의해서 (한 명의 강사는 한 개의 강좌만 담당할 수 있다.)

담당강사번호를 결정짓고 있습니다.

이럴 경우, 삽입 이상, 갱신 이상, 삭제 이상이 발생할 수 있습니다.

삽입 이상은 새로운 강사 P005가 새로 고용되었다고 했을 때

이 강사가 '중국어'를 담당한다고 해봅시다.

그럴경우, 아직 모집된 학생이 없을 수 있기 때문에

고객 아이디에는 NULL이 들어가게 됩니다.

그런데 우리가 정의했던 것은 {고객아이디, 인터넷강좌} 가 기본키였습니다.

참조무결성 제약조건에 의해서 기본키에는 NULL값이 들어가서는 안됩니다.

즉, 삽입 이상이 발생하게 됩니다.

다음으로 갱신 이상에 대해 살펴보겠습니다.

P001이라는 강사가 영어회화 강좌를 그만두고,

일본어 강좌를 맡기로 합니다. 위 릴레이션에서는 총 2개의

튜플을 변경해야 하는데, 하나만 변경하게 될 경우,

P001 강사는 일본어와 영어회화를 담당하게 됩니다.

이는 전제조건 ( 한 명의 강사는 한 개의 강좌만 담당해야 한다.)는 조건을 위배합니다.

그러므로 갱신 이상을 야기합니다.

삭제 이상은 기존과 비슷합니다.

딱 하나의 강좌만 맡고 있는 P003 강사가 있는데 orange 고객이 강좌를 취소한다면?

P003 강좌에 대한 정보를 잃어버리게 됩니다.

즉, 삭제이상이 발생하게 되는 것입니다.

그러면 위 릴레이션을 분해해보겠습니다.

이렇게 고객-담당강사 릴레이션과 강좌담당 릴레이션으로 분해할 수가 있습니다.

이렇게 하면 새로운 강사가 들어올 경우 강좌담당 릴레이션에 데이터를 추가하면 됩니다.

그러면 삽입 이상을 해결할 수가 있죠.

P001 강사의 강좌가 영어회화에서 일본어로 

변경이 되더라도 문제가 되지 않습니다. 강좌담당 릴레이션만 바꿔주면 되기 때문이죠

마지막으로 orange 고객이 P003 강사의 강좌를 취소하더라도

우리는 P003 강사의 정보를 잃지 않을 수 있습니다.

강좌담당 릴레이션에 그대로 남아있기 때문이죠.

이로써 BCNF 정규형을 만족하는 릴레이션 분해에 대해서 알아보았습니다.

실제로 제 3정규형과 BCNF 정규형은 현업에서도 가장 많이

사용이 되기 때문에 꼭 숙지하시기 바라겠습니다.