[오라클 DB] 데이터베이스 실제 논리적 설계 및 알고리즘 적용

(개념적 설계 단계)

데이터베이스 설계 사례에 알고리즘 적용

 

아래는 개념적 설계 단계의 산출물인 개념 스키마 예시이다.

해당 개념 스키마 예시를 이용해 논리적 설계를 진행하여보자.

 

 

 

---

 

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

 

정규(강한) 엔티티 타입과 해당 엔티티 타입의 각 단일 속성들을 릴레이션으로 사상한다.

 

EMPLOYEE 엔티티 타입의 복합 애트리뷰트인 ADDRESS 애트리뷰트 대신, 해당 애트리뷰트를 구성하는 City, Ku, Dong 애트리뷰트 각각을 사상한다.

또한 각 엔티티 타입의 기본 키는 릴레이션의 기본 키로 그대로 사상한다.

 

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary)

PROJECT(Projno, Projname, Budget)

DEPARTMENT(Deptno, Deptname, Floor)

SUPPLIER(Suppno, Suppname, Credit)

PART(Partno, Partname, Price)

 

---

 

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

 

약한 엔티티 타입인 DEPENDENT 엔티티 타입을 릴레이션으로 사상한다.

이는 소유 엔티티 타입인 EMPLOYEE 엔티티 타입의 기본 키와 자신의 부분 키를 조합하여 기본 키로 사용하므로, 아래와 같이 사상할 수 있다.

 

DEPENDENT(Empno, Depname, Sex)

 

 

---

 

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

 

2진 1:1 관계 타입을 지니는 EMPLOYEE 엔티티 타입과 PROJECT 엔티티 타입 간의 MANAGES 관계에서, 전체 참여를 보이는 PROJECT 엔티티 타입에 외래 키(EMPLOYEE 엔티티 타입의 기본 키)를 삽입한다.

또한, 관계의 속성인 StartDate 애트리뷰트 또한 삽입한다.

 

PROJECT(Projno, Projname, Budget, StartDate, Manager)

 

 

---

 

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

 

1:N 관계를 보이는 두 강한 엔티티 타입 간의 관계에서는 1측의 엔티티 타입의 기본 키를 N측의 엔티티 타입의 외래 키로 삽입해주어야 한다.

 

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary, Dno)

 

또한, 순환 관계를 보이는 Part 엔티티 타입에 대해서도 자체 릴레이션의 기본 키를 외래 키로 삽입해주어야 한다.

 

PART(Partno, Partname, Price, Subpartno)

 

 

---

 

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

 

EMPLOYEE 엔티티 타입과 PROJECT 엔티티 타입은 WORKS_FOR에 대해 M:N 관계를 보인다.

이는 두 엔티티 타입 어디에도 외래 키를 삽입할 수 없기에, WORKS_FOR라는 릴레이션을 생성하여 두 엔티티 타입의 기본 키를 WORKS_FOR 릴레이션의 외래 키로 삽입하여, 해당 두 외래 키 조합으로 기본 키를 구성한다.

또한, 관계 속성을 추가하여 최종적으로 해당 릴레이션을 사상한다.

 

WORKS_FOR(Empno, Projno, Duration, Responsibility)

 

 

---

 

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

 

3진 이상의 관계를 보이는 SUPPLY 관계는 따로 릴레이션으로 사상하여 각 관계에 참여하는 엔티티 타입의 기본 키를 외래 키로 삽입하고 해당 모든 외래 키 조합으로 기본 키를 구성한다.

1:N:N 관계를 지닌다면 1 관계로 참여하는 엔티티 타입의 기본 키를 받은 외래 키는 해당 릴레이션의 기본 키 구성 조합에서 제외시켜야 하지만, 해당 SUPPLY 관계에 대해 세 엔티티 모두 N:N:N 관계를 보이기에 세 외래 키를 모두 기본 키 조합으로 구성한다.

또한 해당 관계의 속성을 추가하여 최종적으로 릴레이션을 사상한다.

SUPPLY(Suppno, Projno, Partno, Quantity)

 

 

---

 

7단계 : 다치 애트리뷰트

 

다치 애트리뷰트인 PROJECT 엔티티 타입의 Location 애트리뷰트는 따로 릴레이션으로 사상한다.

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

 

해당 릴레이션의 기본 키는 다치 애트리뷰트의 각 값과 외래 키(PROJECT 엔티티 타입의 기본 키)로 구성된다.

PROJ_LOC(Projno, Location)

 

---

 

예시 ER 스키마(개념 스키마)는 관계 데이터베이스에서 총 9개의 릴레이션으로 사상되었다.

 

EMPLOYEE(Empno, Empname, Title, City, Ku, Dong, Salary, Dno)

PROJECT(Projno, Projname, Budget, StartDate, Manager)

DEPARTMENT(Deptno, Deptname, Floor)

SUPPLIER(Suppno, Suppname, Credit)

PART(Partno, Partname, Price, Subpartno)

DEPENDENT(Empno, Depname, Sex)

WORKS_FOR(Empno, Projno, Duration, Responsibility)

SUPPLY(Suppno, Projno, Partno, Quantity)

PROJ_LOC(Projno, Location)

 

( 밑줄 표시는 기본 키이며, 파란색 글자는 외래 키를 의미 )

 

이는 논리적 설계의 산출물인 논리적 스키마이다.

 

 

만약 해당 논리적 스키마가 잘 만들어진 것인지 알고싶다면, 정규화 과정을 거치면 된다.