DBMS - (16) relational design overview

DBMS - (16) relational design overview

Good Schema를 만드는 방법?
Designing a database schema
- Usually many designs possible.
- Some are much better than others.
- How do we choose?

Often use higher-level design tools, but, ...
- Some designer go straight to relations.
- Useful to understand why tools produce certain schemas.

Very nice theory for relational database design => Normal form.

학번	과목코드	이름	연락처
1601	C01	강하늘	010-1234-0000
1601	C02	강하늘	010-1234-0000
1602	A01	서현진	010-3333-0000
1603	B01	최유정	010-5555-0000
1603	C01	최유정	010-5555-0000

Database anomaly(=데이터베이스 이상현상)
1. update anomaly(=갱신 이상)
- 데이터 갱신시 일관성 유지가 안되는 현상, 데이터 불일치(=inconsistency) 발생
- 최유정 학생이 연락처를 변경하는 경우 B01, C01 튜플의 정보를 수정해야 하는데 1개의 튜플만 수정할 경우 발생

2. insert anomaly(=삽입 이상)
- 튜플 삽입시 지정하지 않은 속성값이 NULL을 갖는 현상
- 1604 홍길동 010-7777-3333 학생이 있으나 아직 수강하는 과목이 존재하지 않는 경우에는 과목코드에 NULL 값이 저장되는 현상

3. delete anomaly(=삭제 이상)
- 튜플 삭제시 의도치 않은 정보의 손실이 발생하는 현상(=연쇄 삭제)
- 1602 서현진 학생이 A01 과목을 수강취소 하는 경우 해당 튜플이 삭제되어 의도치 않게 서현진 학생의 연락처 정보가 삭제되는 현상

Design by decomposition
1. start with 'mega' relations containing everything.
2. decompose into smaller, better relations with some info.
3. can do decomposition automatically.

Automatic decomposition
1. Mega relations + properties of the data.
2. System decomposes based on properties.
3. Final set of relations satisfies normal form.
* Normal form? no anomalies, no lost information

Functional Dependency
- Apply(SSN, sName, cName)
- SSN->sName
- Same SSN always has same name, 한 속성(attribute)이 다른 속성값을 결정짓는 현상

Normal Form

1차 정규형

<그림 2: 1차 정규형이 아닌 릴레이션>

1차 정규형은 각 로우마다 컬럼의 값이 1개씩만 있어야 합니다. 이를 컬럼이 원자값(Atomic Value)를 갖는다고 합니다. 예를 들어, 아래와 같은 경우 Adam의 Subject가 Biology와 Maths 두 개 이기 때문에 1차 정규형을 만족하지 못합니다.

<그림 3: 1NF>

위의 정보를 표현하고 싶은 경우 이렇게 한 개의 로우를 더 만들게 됩니다. 결과적으로 1차 정규화를 함으로써 데이터 redundancy는 더 증가하였습니다. 데이터의 논리적 구성을 위해 이 부분을 희생하는 것으로 볼 수 있습니다.

2차 정규형(2NF)

<그림 4: 1NF이지만 2NF이 아닌 릴레이션>

2차 정규화부터가 본격적인 정규화의 시작이라고 볼 수 있습니다. 2차 정규형은 테이블의 모든 컬럼이 완전 함수적 종속을 만족하는 것입니다. 이게 무슨 말이냐면 기본키중에 특정 컬럼에만 종속된 컬럼(부분함수 종속성)이 없어야 한다는 것입니다. 위 테이블의 경우 기본키는 (Student, Subject) 두 개로 볼 수 있습니다. 이 두 개가 합쳐져야 한 로우를 구분할 수가 있습니다. 근데 Age의 경우 이 기본키중에 Student에만 종속(Student->Age)되어 있습니다. 즉, Student 컬럼의 값을 알면 Age의 값을 알 수 있습니다. 따라서 Age가 두 번 들어가는 것은 불필요한 것으로 볼 수 있습니다.

<그림 5: 2NF, Student Table>

 

<그림 6: 2NF, Subject Table>

이를 해결하기 위한 방법은 위처럼 테이블을 쪼개는 것입니다. 그러면 두 테이블 모두 2차 정규형을 만족하게 됩니다. 위 테이블의 경우 삽입/갱신/삭제 이상을 겪지 않게됩니다. 하지만 조금 더 복잡한 테이블의 경우, 갱신 이상을 겪기도하는데 이를 해결하는 것이 바로 3차 정규화입니다


3차 정규형(3NF)

<그림 7: 2NF이지만 3NF가 아닌 릴레이션>

이와 같은 데이터 구성을 생각해봅시다. Student_id가 기본키이고, 기본키가 하나이므로 2차 정규형은 만족하는 것으로 볼 수 있습니다. 하지만 이 데이터의 Zip컬럼을 알면 Street, City, State를 결정할 수 있습니다. 또한 여러명의 학생들이 같은 Zip코드를 갖는 경우에 Zip코드만 알면 Street, City, State가 결정(Zip->Street, city, State)되기 때문이 이 컬럼들에는 중복된 데이터가 생길 가능성이 있습니다. 정리하면 3차 정규형은 기본키를 제외한 속성들 간의 이행적 함수 종속이 없는 것 입니다. 풀어서 말하자면, 기본키 이외의 다른 컬럼이 그외 다른 컬럼을 결정할 수 없는 것입니다. 

<그림 8: 3NF>

3차 정규화는 2차정규화와 마찬가지로 테이블을 분리함으로써 해결할 수 있는데, 이렇게 두 개의 테이블로 나눔으로써 3차 정규형을 만족할 수 있습니다. 이를 통해 데이터가 논리적인 단위(학생, 주소)로 분리될 수 있고, 데이터의 redundancy도 줄었음을 알 수 있습니다.

BCNF(Boyce-Codd Normal Form)

학생	과목	교수	학점
1	CS123	스미스	A
2	AB123	톰	B
3	AB123	톰	C

BCNF는 (Boyce and Codd Normal Form) 3차 정규형을 조금 더 강화한 버전으로 볼 수 있습니다. 이는 3차 정규형으로 해결할 수 없는 이상현상을 해결할 수 있습니다. BCNF란 3차정규형을 만족하면서 모든 결정자가 후보키 집합에 속한 정규형입니다. 아래와 같은 경우를 생각해보면, 후보키는 수퍼키중에서 최소성을 만족하는 건데, 이 경우 Primary Key(학생, 과목) 입니다. (학생, 과목)은 그 로우를 유일하게 구분할 수 있습니다. 근데 이 테이블의 경우 교수가 결정자 입니다. (교수가 한 과목만 강의할 수 있다고 가정) 즉, 교수가 정해지면 과목이 결정(교수->과목)됩니다. 근데 교수는 후보키가 아닙니다. 따라서 이 경우에 BCNF를 만족하지 못한다고 합니다. 3차 정규형을 만족하면서 BCNF는 만족하지 않는 경우는 언제일까요? 바로 일반 컬럼이 후보키를 결정하는 경우입니다. 

BCNF? If A->B then A is a key. (교수->과목)

교수	과목
스미스	CS123
톰	AB123

 

학생	과목	학점
1	CS123	A
2	AB123	B
3	AB123	C

 

<그림 9: Normal form 계층 구조>

Reference
https://www.youtube.com/watch?v=DFnAakJ4FDg&list=PL6hGtHedy2Z4EkgY76QOcueU8lAC4o6c3&index=19