[오라클 DB] 논리적 설계 단계

논리적 설계 단계에서는 ER 스키마를 관계 데이터 모델의 릴레이션들로 사상한다.

 

관계형 DBMS에서는 ER 스키마를 릴레이션들로 사상한다.

 

ER 스키마를 관계 모델의 릴레이션으로 사상

 

ER 스키마에는 엔티티 타입과 관계 타입이 존재하지만, 관계 데이터베이스에는 엔티티 타입과 관계 타입을 구분하지 않고 릴레이션만 존재한다.

 

ER 모델을 릴레이션들로 사상하는 과정에는 총 7단계로 이루어져 있다.

 

단계	사상할 대상	알고리즘 구분
1단계	엔티티 타입과 단일 값 애트리뷰트	정규(강한) 엔티티 타입
2단계		약한 엔티티 타입
3단계	관계 타입	2진 1:1 관계 타입
4단계		2진 1:N 관계 타입
5단계		2진 N:M 관계 타입
6단계		N진 관계 타입
7단계	다치 애트리뷰트	다중 값 애트리뷰트

 

---

 

1단계 : 정규 엔티티 타입과 단일 값 애트리뷰트

 

ER 스키마의 각 정규 엔티티 타입에 대해 하나의 릴레이션을 생성한다.

 

 

엔티티에 있던 단순 애트리뷰트들을 릴레이션에 모두 포함시킨다.

 

엔티티에서의 복합 애트리뷰트는 그 복합 애트리뷰트를 구성하는 단순 애트리뷰트들만 릴레이션에 포함시킨다.

만약 주소 애트리뷰트가 복합 애트리뷰트이고 해당 애트리뷰트를 구성하는 시, 구, 동 애트리뷰트가 존재한다면,

릴레이션으로 사상할 때에는 주소 애트리뷰트를 포함하는 대신, 주소 애트리뷰트를 구성하고 있는 시, 구, 동 애트리뷰트를 릴레이션에 포함시킨다는 의미이다.

 

또한, 엔티티의 기본 키가 릴레이션의 기본 키가 된다.

 

---

 

2단계 : 약한 엔티티 타입과 단일 값 애트리뷰트

 

 

소유 엔티티 타입 E1과 약한 엔티티 타입 E2에 대하여 릴레이션을 생성한다.

 

약한 엔티티에 있던 모든 단일 애트리뷰트들을 릴레이션에 포함시키고, 소유 엔티티 타입에 해당하는 릴레이션의 기본 키를 약한 엔티티 타입에 해당하는 릴레이션에 외래 키로 포함시킨다.

위 그림에서, E2 릴레이션은 E1 릴레이션의 기본 키 애트리뷰트를 참조하는 외래 키 K를 포함시킨 것을 알 수 있다.

 

약한 엔티티 타입에 해당하는 릴레이션의 기본 키는 약한 엔티티 타입의 부분 키와 소유 엔티티 타입에 해당하는 릴레이션을 참조하는 외래 키의 조합으로 이루어진다.

 

---

 

3단계 : 2진 1:1 관계 타입

 

 

위 그림과 같이 1:1 관계를 보이는 ER 스키마를 릴레이션으로 사상하는 방법은 총 4가지가 존재한다.

 

방법 1

 

방법 1은, E1과 E2의 관계를 표현하는 외래 키를 E2 릴레이션에 포함시킨 방법이다.

E1과 E2의 관계를 E2의 외래 키로 표현하고 설명할 수 있다.

 

방법 2

 

방법 2는, E1과 E2의 관계를 표현하는 외래 키를 E1 릴레이션에 포함시킨 방법이다.

E1과 E2의 관계를 E1의 외래 키로 표현하고 설명할 수 있다.

 

방법 1과 방법 2는 가장 기본적인 관계 표현 방법이다.

 

 

방법 3

 

방법 3은, E1과 E2의 관계를 새로운 릴레이션으로 사상하는 방법이다.

새로운 테이블을 생성하게 되는 것이기에, 이렇게 해야만 하는 명확한 이유가 존재해야 한다.

( 예 : 트랜잭션에서 각 키를 자주 참조해야 할 때 )

불필요하게 방법 3을 채택할 경우, 수행시간 증가 등 성능적인 면에서 불이익을 받을 수 있다.

 

 

방법 4

 

방법 4는, 두 엔티티 타입을 하나의 릴레이션으로 사상시키는 방법이다.

잘 쓰이지 않으니 참고만 하는 것이 좋다.

 

방법 1과 방법 2는 가장 기본적인 표현 방법이라고 위에서 설명하였다.

그렇다면, 방법 1과 방법 2중 아무 방법이나 채택하면 되는가? 꼭 그렇지만은 않다.

 

아래의 경우를 보자.

 

 

EMPLOYEE 엔티티 타입과 PROJECT 엔티티 타입은 MANAGES라는 관계를 맺고 있으며 해당 관계에 대한 카디널리티 비율은 1:1이다.

또한, EMPLOYEE 엔티티 타입은 MANAGES 관계에 부분 참여 관계를 보이고 있으며 PROJECT 엔티티 타입은 MANAGES 관계에 전체 참여 관계를 보이고 있다.

 

위의 ER 스키마를 릴레이션으로 사상하고자 할 때, 다음 3 가지 설명 중 맞는 설명은 무엇인가?

 

1. EMPLOYEE 릴레이션에 외래 키를 삽입하는 것이 효율적이다.

2. PROJECT 릴레이션에 외래 키를 삽입하는 것이 효율적이다.

3. 두 릴레이션 어디에나 외래 키를 삽입하나 효율성은 같다.

 

답은 2번이다. 왜일까?

 

PROJECT 엔티티 타입은 전체 참여이다. 이는 PROJECT 엔티티 타입의 각 엔티티는 무조건 EMPLOYEE 엔티티 타입의 특정 엔티티와 관계를 맺고 있기 때문에, 외래 키 애트리뷰트에 NULL 값이 삽입될 일이 없기에 더욱 효율적이라고 볼 수 있다.

 

반대의 경우를 생각해보면 이해가 쉽다.

 

EMPLOYEE 엔티티 타입의 각 엔티티는 최소 0의 관계를 지니는 부분 참여를 보인다. 이는 관계에 참여하지 않을 수도 있다는 의미이기 때문에 EMPLOYEE 릴레이션에 외래 키를 삽입하면 외래 키 애트리뷰트에 NULL값이 삽입되는 경우가 존재한다.

이는 외래 키 애트리뷰트에 NULL값이 전혀 삽입될 가능성이 없는 전자보다 효율성이 떨어진다.

 

---

 

4단계 : 2진 1:N 관계 타입

 

 

위 그림과 같이 1:N 관계를 보이는 ER 스키마를 릴레이션으로 사상하는 방법은 총 2가지가 존재한다.

 

방법 1

 

방법 1은, 1측의 엔티티 타입 E1의 기본 키를 N측의 엔티티 타입 E2에 외래 키로 포함시키는 방법이다.

왜 이 방법만 가능하고, 반대의 경우는 안될까?

이유는 다치 애트리뷰트는 관계 데이터 모델에서 허용하지 않기 때문이다.

 

N측의 엔티티 타입의 기본 키를 1측의 엔티티 타입에 외래 키로 포함시키면 어떻게 되는가에 대한 예를 들어보자.

 

 

위 그림을 보고, EMPLOYEE 엔티티 타입의 기본 키를 DEPARTMENT 엔티티 타입의 외래 키로 삽입했다고 상상해보자.

DEPARTMENT의 각 부서는 여러 사원들이 속해있을 것이다. 이는 단적인 예로, 부서이름이 '개발'인 부서에 10명의 사원이 속해있다고 가정한다면 부서이름이 '개발'인 튜플의 외래 키 값은 10가지가 존재할 것이다. 이는 다치 애트리뷰트 형태를 띄게 되므로, 관계 데이터 모델에서 허용하지 않는 표현이다.

 

하지만 반대로, DEPARTMENT 엔티티 타입의 기본 키를 EMPLOYEE 엔티티 타입의 외래 키로 삽입했다고 상상해보자.

EMPLOYEE의 각 사원들이 속해 있는 부서가 외래 키로써 존재할 것이고, 각 사원은 무조건 1개의 부서에 속해있으므로 외래 키 값은 단일 애트리뷰트일 수 밖에 없다.

 

그러므로, 두 엔티티 타입 중 어느 엔티티 타입에 외래 키를 삽입해야 하는지 궁금하다면 카디널리티 비율이 N인 방향의 엔티티 타입에 외래 키를 삽입해야 한다는 점을 숙지하면 된다.

 

 

방법 2

 

방법 2는, E1과 E2의 관계를 새로운 릴레이션으로 사상하는 방법이다.

 

이 또한, 이렇게 해야만 하는 이유가 명확할 때에만 채택하는 것이 좋다.

 

 

1:N 관계에는 위의 예시의 관계가 아닌 다음과 같은 관계 구조에서도 보일 수 있다.

 

순환 관계

 

자체 릴레이션에 대해 1:N 관계를 보이는 위와 같은 순환 관계에서는, 다음과 같이 릴레이션으로 사상할 수 있다.

 

 

자체 릴레이션의 기본 키를 외래 키로 지정하는 방법이다.

 

---

 

5단계 : 2진 M:N 관계 타입

 

 

위 그림과 같은 2진 M:N 관계 타입을 릴레이션으로 사상하는 방법에는 오직 1가지 존재한다.

 

 

M:N 관계에서는 E1, E2 어느 엔티티 타입에 외래 키를 삽입하여도 다치 애트리뷰트가 될 수 밖에 없기 때문에,

무조건 별도 릴레이션을 따로 만들어서 관계를 표현해야 한다.

 

---

 

6단계 : 3진 이상의 관계 타입

 

일반적으로 외래 키들의 조합이, 관계를 표현하는 릴레이션 R의 기본 키가 된다.

여기서, 관계 타입 R에 참여하는 엔티티 타입들의 카디널리티 비율이 1:N:N이면 카디널리티가 1인 릴레이션의 기본 키를 참조하는 외래 키는 제외된다.

 

 

3진 이상의 N진 관계에서, N이 커질수록 R 릴레이션이 변화에 상당히 민감해진다.

N의 개수가 늘어날수록 R 릴레이션의 차수 또한 비례하여 늘어날 것이고, 이는 기본 키를 구성하는 애트리뷰트 수가 증가한다는 말과 동일하기 때문이다.

 

---

7단계 : 다치 애트리뷰트

 

각 다치 애트리뷰트에 대하여 릴레이션을 따로 생성한다.

 

 

릴레이션의 기본 키는 다치 애트리뷰트와 외래 키의 조합으로 구성된다.