[DB] 논리적 설계 모델

 

지난 포스팅에서 개념적 설계 모델을 알아보았다. 개념적 설계 모델은 인간이 이해하기 쉽도록 DB를 설계하는 방법이다. 그럼 이를 DBMS에 맞는 논리적인 설계로 변환해야한다. 관계형 데이터 모델에 맞게 DB를 설계해보자.

 

종속적 관계

 

개념적 설계 모델에서 종속적 관계는 약성 개체 집합이 강성 개체 집합에 종속됨을 표현했다. 그럼 이를 테이블로 나타내어 보자.

 

개체 집합 => 테이블

개체 집합의 속성 => 필드

기본키 => 기본키

 

약성 개체 집합은 강성 개체 집합에 종속된 관계로 독자적인 기본키를 갖지 못한다. 종속된 강성 개체에 따라 기본키가 달라진다. 기본키는 강성 개체를 특정 지을 수 있는 여러 속성을 묶은 부분키와 종속된 개체의 기본키를 묶어 만들어 진다.

 

강성 개체 집합 : 강좌 ( course_id , title, credit )

약성 개체 집합 : 강의 ( course_ id, year, semester, division, classroom, enroll )

 

관계 집합 변환

관계 집합의 테이블의 기본키를 정의해보자.

 

관계 집합은 두 개체 집단 혹은 세 개의 개체 집단 사이의 동일한 유형의 개체간 관계의 집합이다. 그러므로 관계 집합의 하나의 관계를 특정 지으려면 해당 관계가 연결하고 있는 개체들의 기본키를 묶어 관계의 기본키로 만들어야 한다.

 

강성 개체 집합 : 강좌 ( course_id , title, credit )

약성 개체 집합 : 강의 ( course_ id, year, semester, division, classroom, enroll )

관계 : 강좌와 강의는 '개설' 관계

'개설' 관계 집합 : ( course_ id, year, semester, division)

 

자기 연관 관계 집합

자기 연관 관계는 하나의 개체 집합 안에서 관계로 대응되는 경우다. 직원 개체 집합에서 상사 개체와 부하 개체로 나뉘어 '관리(manage)' 관계로 연관되는 원리이다.

 

자기 연관 관계의 경우, 개체의 역할을 특정 지을 수 있는 기본키를 가져야 한다.

 

직원 ( employee_id, name )

관리 ( employee_id, manaer_id )

 

 

관계 집합의 생략

개체 집합은 테이블로 만들어 줘야 하지만 관계 집합은 굳이 테이블로 만들어 줄 필요가 없는 경우가 존재한다. 관계 테이블을 생략하는 경우를 살펴보자.

 

- 테이블 중복

 

강성 개체 집합 : 강좌 ( course_id , title, credit )

약성 개체 집합 : 강의 ( course_ id, year, semester, division, classroom)

'개설' 관계 집합 : ( course_ id, year, semester, division)

 

'개설' 관계 집합은 약성 개체 집합과 필드가 중복된다. 이렇게 중복이 되는 경우, 관계 집합을 테이블로 변환하지 않고 생략 가능하다.

 

- 테이블 결합

 

다 대 일 대응의 경우, 두 개체 집합의 기본키를 묶어 하나의 관계 집합을 만들고 이를 테이블로 표현할 수 있다.

 

학생 개체 집합 (stu_id, name, address)

학과 개체 집합 (dept_id, dept_name, office)

소속 관계 집합 ( stu_id, dept_id )

 

하지만 이런 경우 일반적으로 '외래키'를 사용한다.

 

학생 개체 집합 (stu_id, name, address, dept_id)

학과 개체 집합 (dept_id, dept_name, office)

 

다 대 일 대응에서는 많은 쪽이 하나인 쪽의 기본키를 외래키로 갖는다. 외래키를 생성하고 관계 집합 테이블은 생략한다.

 

다 대 다 대응인 경우 결합이 불가능하다. 다수의 개체가 다수의 다른 개체와 대응되기에 하나의 외래키를 갖는 것이 불가능하다. 그러므로 결합을 하면 다 대 다 관계를 표현할 수 없다. 둘 사이의 관계 집합은 테이블로 변환해주어야 한다.

 

정리하면, '중복'이란 필드가 중복되는 관계 집합을 생략하는 것이고 '결합'이란 외래키를 사용하여 관계 집합을 생략하는 것을 의미한다.

 

일반화 관계 변환

일반화 관계란 여러 개체 집합에서 공통된 특성을 뽑아 하나의 일반화 된 개체를 생성한 관계를 의미한다. '학생 개체' 집합과 '교수 개체' 집합은 '학교 구성원' 개체 집합으로 일반화 될 수 있다.

 

그럼 이런 일반화 관계를 테이블로 어떻게 표현할까?

 

1) 상위 개체 집합 유지

 

member ( member_id, name ) [ 상위 개체 집합 ]

student ( stu_id, address, year, member_id) [ 하위 개체 집합 ]

professor ( prof_id, position, member_id) [ 하위 개체 집합 ]

 

이렇게 외래키를 사용하여 일반화 관계를 표현할 수 있다. 외래키는 NULL 값을 가질 수 있다. 한 마디로 하위 개체 집합은 상위 개체 집합의 기본키를 외래키로 안 가질 수도 있다. A 학교 구성원의 학생 중에는 타 학교 학생이 포함되어 있는 경우도 존재할 수도 있다는 의미다. 이런 대응을 '부분 참여'라고 한다. 부분 참여인 경우 외래키가 NULL 값이 존재하므로, 상위 개체 집합을 테이블로 변환하여 유지해주는 것이 좋다.

 

2) 상위 개체 집합 제거

 

student ( stu_id, name, address, year, member_id) [ 하위 개체 집합 ]

professor ( prof_id, name, position, member_id) [ 하위 개체 집합 ]

 

상위 개체 집합을 테이블로 변환하여, 하위 개체 집합의 테이블들이 상위 테이블의 외래키를 갖는 구조는 부분 참여인 경우 효율적이다. 하지만 이런 경우 join 연산이 빈번하게 발생한다. 만약 전체 참여인 경우, 상위 개체 집합을 제거해준다.

 

각 하위 테이블은 외래키가 아닌, 상위 개체 집합을 표현해 줄 필드를 갖는다. A 학생은 member_id로 2001을 갖고 B 교수는 member_id로 3001을 갖는다면 서로 다른 학교 구성원인 것이다. 이와 같이, 전체 참여인 경우 외래키가 NULL 값을 가지는 경우가 없으므로 불필요한 상위 개체 집합의 테이블은 제거해준다. 하지만 이 경우, 개체 집합 테이블 간의 중복되는 필드가 많아 UNION 연산이 빈번하게 일어난다.

 

다중값 속성의 변환

개념적 설계에서의 속성은 논리적 설계에서 필드가 된다. 속성은 다중값을 가지고 복합적인데 반해, 필드는 원자값을 가진다. 그러므로 속성을 필드로 변환 시, 다중값과 복합속성을 해결해주어야 한다.

 

1) 다중 값 속성 변환

 

학생 개체는 가족 속성을 갖는다. 문제는 가족 구성원이 하나가 아니다. 가족 속성은 다중값을 가진다. 그러므로 이는 일 대 다 대응이다. 하나의 학생은 여러 가족 구성원을 갖는다. 다중 값 속성 변환은 테이블 결합을 사용하여 외래키를 통해 이루어진다.

 

student (stu_id, name, address, memer_id )

family_member ( stu_id, member_name )

 

2) 복합 속성의 변환

 

이름 속성은 성과 이름으로 나뉜다. 그러므로 속성을 원자값을 가질 수 있는 단위로 분리하여 필드로 만들어 준다.

 

student (stu_id, name, address) =>

student (stu_id, firstname, lastname, address )

 

 

---

참고자료

 

Understanding of Database

저자 : 이상구, 장재영, 김한준, 정재헌

출판 : 이한미디어발매2012.08.20.