[오라클 DB] 데이터베이스 실제 설계 및 개념적 데이터 모델(ER 다이어그램)

다음은 가상의 기업에서 볼 수 있는 요구사항에 관한 내용이다.

 

데이터베이스 설계 요구사항

기업에는 다수의 사원들이 재직한다.
각 사원에 대해서 사원번호(고유함), 이름, 직책, 급여, 주소를 저장한다.
주소는 시, 구, 동으로 세분하여 나타낸다.

각 사원은 0명 이상의 부양가족을 가질 수 있다.
한 부양가족은 두 명 이상의 사원에게 속하지 않는다.
각 부양가족에 대해서 부양가족의 이름과 성별을 저장한다.

회사는 여러 개의 프로젝트들을 진행한다.
각 프로젝트에 대해서 프로젝트번호(고유함), 이름, 예산, 프로젝트가 진행되는 위치를 나타낸다.
한 프로젝트는 여러 위치에서 진행될 수 있다.
각 프로젝트마다 여러 명의 사원들이 일을 한다.
각 사원은 여러 프로젝트에서 근무할 수 있다.
각 사원이 해당 프로젝트에서 어떤 역할을 수행하고, 얼마 동안 근무해 왔는가를 나타낸다.
각 프로젝트마다 한 명의 프로젝트 관리자가 존재한다.
한 사원은 두 개 이상의 프로젝트의 관리자가 될 수는 없다.
프로젝트 관리자 임무를 시작한 날짜를 기록한다.

각 사원은 한 부서에서만 속한다.
각 부서에 대해서 부서번호(고유함), 이름, 부서 위치가 위치한 층을 나타낸다.

각 프로젝트에는 부품들이 필요하다. 한 부품이 두 개 이상의 프로젝트에서 사용될 수 있다.
하나의 부품은 다른 여러 개의 부품들로 이루어질 수 있다.
각 부품에 대해서 부품번호(고유함), 이름, 가격을 나타낸다.
그 부품이 다른 부품들을 포함하는 경우에는 그 부품들에 관한 정보도 나타낸다.

각 부품을 공급하는 공급자들이 존재한다.
한 명의 공급자는 여러 가지 부품들을 공급할 수 있고, 각 부품은 여러 공급자들로부터 공급될 수 있다.
각 공급자에 대해서 공급자 번호(고유함), 이름, 신용도를 나타낸다.
각 공급자에 대해서 그 공급자가 어떤 부품을 어떤 프로젝트에 얼마나 공급하는 가를 나타낸다.

 

 

요구사항에 따른 개념적 데이터베이스 설계를 위해, ER 다이어그램을 작성해보겠다.

 

ER 다이어그램 작성

 

엔티티 타입 및 애트리뷰트들을 식별

 

 

 

요구사항 : 각 사원에 대해서 사원번호(고유함), 이름, 직책, 급여, 주소를 저장한다. 주소는 시, 구, 동으로 세분하여 나타낸다.

 

 

 

 

사원을 뜻하는 EMPLOYEE 엔티티 타입 선언.
엔티티 타입의 각 애트리뷰트는 기본 키인 Empno(사원 번호)와 Empname(사원 이름), Title(직책), Salary(급여), Address(주소)가 존재하며, Address 애트리뷰트는 City(시), Ku(구), Dong(동) 3가지 애트리뷰트로 세분할 수 있다.

 

 

---

 

요구사항 : 회사는 여러 개의 프로젝트들을 진행한다. 각 프로젝트에 대해서 프로젝트번호(고유함), 이름, 예산, 프로젝트가 진행되는 위치를 나타낸다. 한 프로젝트는 여러 위치에서 진행될 수 있다.

 

 

프로젝트를 뜻하는 PROJECT 엔티티 타입 선언.
엔티티 타입의 각 애트리뷰트는 기본 키인 Projno(프로젝트 번호)와 Projname(프로젝트 이름), Budget(예산), Location(프로젝트가 진행되는 위치)가 존재하며, 한 프로젝트는 여러 위치에서 진행될 수 있기에 Location 애트리뷰트는 다치 애트리뷰트로 표현해야 한다.

 

---

 

요구사항 : 각 부서에 대해서 부서번호(고유함), 이름, 부서 위치가 위치한 층을 나타낸다.

 

 

부서를 뜻하는 DEPARTMENT 엔티티 타입 선언.
엔티티 타입의 각 애트리뷰트는 기본 키인 Deptno(부서번호)와 Deptname(부서 이름), Floor(부서가 위치해있는 층)가 존재한다.

 

---

 

요구사항 : 각 공급자에 대해서 공급자 번호(고유함), 이름, 신용도를 나타낸다.

 

 

공급자를 뜻하는 SUPPLIER 엔티티 타입 선언.
엔티티 타입의 각 애트리뷰트는 기본 키인 Suppno(공급자 번호)와 Suppname(공급자 이름), Credit(신용도)가 존재한다.

 

---

 

요구사항 : 각 부양가족에 대해서 부양가족의 이름과 성별을 저장한다.

 

 

부양가족을 뜻하는 DEPENDENT 엔티티 타입 선언.
엔티티 타입의 각 애트리뷰트는 Depname(부양가족의 이름), Sex(성별)가 존재하며, 두 애트리뷰트 모두 후보 키로는 적합하지 않다.(유일성을 만족하지 않음. 동명이인이 존재할 수 있기 때문) -> DEPARTMENT 엔티티 타입은 약한 엔티티 타입임을 알 수 있다.
EMPLOYEE 엔티티 타입을 소유 엔티티 타입으로 지정하고, 해당 엔티티 타입의 기본 키인 Empno(사원 번호) 애트리뷰트를 제공받아 Depname 애트리뷰트와 함께 조합하여, (사원번호 + 부양가족 이름) 의 복합 키 쌍을 통해 각 튜플들을 식별할 수 있도록 한다.

 

---

 

요구사항 : 각 부품에 대해서 부품번호(고유함), 이름, 가격을 나타낸다.

 

 

부품을 뜻하는 PART 엔티티 타입 선언.
엔티티 타입의 각 애트리뷰트는 기본 키로 Partno(부품 번호)와 Partname(부품 이름), Price(부품 가격)이 존재한다.

 

---

 

관계와 애트리뷰트들을 식별

 

 

요구사항

각 사원은 한 부서에만 속한다.

 

 

사원(EMPLOYEE) 엔티티 타입과 부서(DEPARTMENT) 엔티티 타입은 '소속'의 관계를 지니고 있다.
사원 한 명은 부서 하나에만 속할 수 있고, 각 부서에는 사원이 여러 명 존재할 수 있다.
이에 두 엔티티 타입은 1:N BELONG 관계를 지니고 있다고 볼 수 있다.

사원은 무조건 하나의 부서에 속해야 하고, 각 부서는 최소 1명의 사원이 존재해야 유지된다.
이에 두 엔티티 타입 모두 전체 참여 관계를 보이고 있다.

 

---

 

 

요구사항

각 프로젝트마다 여러 명의 사원들이 일을 한다.
각 사원은 여러 프로젝트에서 근무할 수 있다.
각 사원이 해당 프로젝트에서 어떤 역할을 수행하고, 얼마 동안 근무해 왔는가를 나타낸다.
각 프로젝트마다 한 명의 프로젝트 관리자가 존재한다.
한 사원은 두 개 이상의 프로젝트의 관리자가 될 수는 없다.
프로젝트 관리자 임무를 시작한 날짜를 기록한다.

 

 

각 프로젝트(PROJECT) 엔티티의 각 엔티티는 여러 명의 사원(EMPLOYEE) 엔티티의 각 엔티티와 일하는(WORKS_FOR) 관계를 지닌다.
또한 사원(EMPLOYEE) 엔티티 타입의 각 엔티티는 프로젝트(PROJECT) 엔티티 타입의 여러 엔티티에서 일할 수 있다. 이에 두 엔티티 타입은 일하는 관계에서 M:N 관계를 지닌다고 볼 수 있다.
또한, 해당 일하는 관계에서 각 사원이 어떤 역할(Responsibility)을 수행하고, 얼마 동안 근무(Duration)해 왔는가를 나타낼 수 있다.
각 프로젝트에는 최소 1명의 사원이 존재해야 프로젝트가 진행될 수 있으며, 사원들은 무조건 최소 1개의 프로젝트에 참가해야 한다. 이에 두 엔티티 타입 모두 전체 참여 관계를 보이고 있다.
(만약 사원들이 프로젝트에 참여할 의무가 없다는 요구사항이 추가되면, 사원은 WORKS_FOR 관계에 부분 참여 관계를 보일 것이다.)

각 프로젝트(PROJECT) 엔티티의 각 엔티티는 관리(MANAGES)해주는 한 명의 사원(EMPLOYEE) 엔티티의 엔티티가 존재한다.
또한 각 사원은 프로젝트를 관리할 수 있으나, 2개 이상의 프로젝트를 관리할 수는 없다. 이는 최대 1개의 프로젝트만을 관리할 수 있다는 소리이다. 이에 두 엔티티 타입은 관리 관계에서 1:1 관계를 지닌다고 볼 수 있다.
각 프로젝트에는 최소 1명의 담당 관리 사원이 필요하며, 각 사원은 무조건 1개의 프로젝트를 관리해야 할 의무는 없다. 이에 EMPLOYEE 엔티티 타입은 MANAGES 관계에 부분 참여하는 모습을 보이고, PROJECT 엔티티 타입은 전체 참여하는 모습을 보이고 있다.

 

---

 

요구사항

각 사원은 0명 이상의 부양가족을 가질 수 있다.
한 부양가족은 두 명 이상의 사원에게 속하지 않는다.

 

 

사원(EMPLOYEE) 엔티티 타입의 각 엔티티인 사원은 0명 이상의 부양가족을 부양(POLICY)할 수 있으며, 부양가족(DEPENDENT) 엔티티 타입의 각 엔티티는 무조건 한 명의 사원에게 부양받는다.
이에 두 엔티티 타입은 1:N의 관계를 지니며, EMPLOYEE 엔티티 타입은 해당 관계에 부분 참여, DEPENDENT 엔티티 타입은 해당 관계에 전체 참여 관계를 보이고 있다.
(약한 엔티티 타입은 소유 엔티티 타입의 키를 빌려야하기 때문에, 무조건 전체 참여 관계를 보이게 된다.)

 

---

 

요구사항

하나의 부품은 다른 여러 개의 부품들로 이루어질 수 있다.

 

자기 자신과 관계를 맺는 형태를 순환 관계라고 한다.

 

부품(PART) 엔티티 타입의 각 엔티티는 또 다른 부품 엔티티 타입의 여러 엔티티로 만들어질 수(Madeup_of) 있으며, 어떤 엔티티는 다른 엔티티를 구성(Consist_of)하는 엔티티일 수도 있다.
따라서 부품은 다른 부품들로 이루어지지 않을 수도 있기 때문에 Madeup_of 관계에 대해 부분 참여 관계를 보이고, 이는 무조건 다른 부품의 구성요소일 필요가 없다는 의미이므로 Consist_of 관계에 대해서도 부분 참여 관계를 보인다.
또한 한 부품은 다른 부품 여러 개의 조합으로 구성될 수도 있기 때문에, 1:N 관계를 보인다.

 

---

 

요구사항

각 부품을 공급하는 공급자들이 존재한다.
한 명의 공급자는 여러 가지 부품들을 공급할 수 있고, 각 부품은 여러 공급자들로부터 공급될 수 있다.
각 공급자에 대해서 그 공급자가 어떤 부품을 어떤 프로젝트에 얼마나 공급하는 가를 나타낸다.

 

3개의 엔티티 타입이 한 관계로 이어져 있다면 이를 3진 관계라고 한다.

 

공급자(SUPPLIER) 엔티티 타입의 각 엔티티는 프로젝트(PROJECT) 엔티티 타입의 여러 엔티티에 부품을 공급(SUPPLIER) 할 수 있다. 또한 프로젝트 엔티티 타입의 각 엔티티는 공급자 엔티티 타입의 여러 엔티티로부터 부품을 공급 받을 수 있다. 이에 M:N (위에서는 L:N) 관계를 지니고 있다.
또한, 공급자는 프로젝트에 무조건 공급할 의무가 없고 프로젝트 또한 무조건 공급받을 의무가 없기 때문에 두 엔티티 모두 공급 관계에 부분 참여 관계를 보인다.

공급자 엔티티 타입의 각 엔티티는 부품(PART) 엔티티 타입의 여러 엔티티를 공급할 수 있으며, 부품 엔티티 타입의 각 엔티티는 공급자 엔티티 타입의 여러 엔티티에 의해 공급될 수 있다. 이에 M:N(위에서는 L:M) 관계를 지니고 있다.
또한, 부품은 공급자에 의해 무조건 공급될 의무가 없고 공급자 또한 부품을 무조건 공급할 의무가 없기 때문에(아직 공급이 되지 않은, 공급자를 배정받지 않아 남아 있는 부품들을 생각하면 이해가 쉽다.) 두 엔티티 모두 공급 관계에 부분 참여 관계를 보인다.

부품 엔티티 타입의 각 엔티티는 프로젝트 엔티티 타입의 여러 엔티티에 공급할 수 있으며, 프로젝트 엔티티 타입의 각 엔티티는 부품 엔티티 타입의 여러 엔티티를 공급받을 수 있다. 이에 M:N 관계를 지니고 있다.
또한, 모든 부품을 프로젝트에 무조건 공급할 의무가 없고(아직 공급 배정을 받지 않은 부품들이 존재한다고 생각하면 이해가 편하다), 프로젝트에 무조건 부품이 공급된 상태일 필요가 없으므로 두 엔티티 모두 공급 관계에 부분 참여 관계를 보인다.

 

위와 같은 3진 관계에서는 관계를 따로 릴레이션으로 빼서 외래 키들을 관리하기도 한다.

(보통은 각 릴레이션의 외래 키로 관계를 표현한다.)

 

---

 

개념적 데이터베이스 설계 최종 결과물

 

요구사항에 따른 최종 ER 다이어그램은 다음과 같다.