5장 데이터베이스 설계와 ER모델(설계 사례 분석 및 릴레이션으로의 사상)

 

<엔티티>

- 각 사원에 대해서, 사원번호 = 쪼갤 수 없음, 이름 = 쪼갤 수 없음, 직책 = 쪼갤 수 없음, 급여 = 쪼갤 수 없음, 주소 = 세분 가능 → 사원은 훌륭한 엔티티다.

- 부양가족 엔티티 : 이름과 성별 저장

- 회사는 여러 프로젝트를 진행, 프로젝트는 여러 위치에서 진행 -> 위치는 다치 애트리뷰트

-> 총 3개의 엔티티

- 부품마다 이름, 가격 등의 정보

- 공급자마다 공급자 번호, 이름, 신용도를 나타냄

<관계>

- 사원과 부양가족의 관계 : 속한다

- 프로젝트마다 여러 사원이 일을 함 : M:N 관계

- 각 사원이 프로젝트마다 다른 역할 -> 관계가 갖는 컬럼 : 어떤 역할 수행했는가

- 각 프로젝트마다 사원 중에 관리자 -> 사원은 부분참여, 프로젝트는 전체 참여

- 한 부서에 여러 사원이 근무 : 1:N?

- 부품과 프로젝트 : M:N

- 파트와 파트 간 순환 관계

- 부품은 여러 곳에 공급 : 부품과 공급자는 M:N, 공급과 프로젝트도 M:N

 

이 내용들을 이제 설계해보자.

 

- 프로젝트 엔타입에 대해서 Location은 여러 곳에서. 위치(로케이션)가 다치 애트리뷰트.
→ 관계모델에서 허용하지 않음. 

 

 

- 파트(부품)와 파트사이의 순환관계, 즉 포함하는 경우에 그 부품의 정보도 나타낸다?
-> 이는 관계를 해결하면서 다루어야함.

 

- 부서마다 사원을 하나 이상, 사원은 반드시 한 부서에 속해야 함

-> 전체 참여로 나타냈으나, 꼭 그럴 필요는 없어 보임.

- 사원과 프로젝트의 관계는 서로 여러개 속할 수 있으니 M:N. 관계가 갖는 컬럼 2개.

- 관리자 관계도 있음. 1:1이나 임플로이는 부분참여(실선 하나). 프로젝트당 매니저 하나지만. 매니저는 임플로이의 일부

 

- 한 사원이 여러 부양가족을 둘 수 있고, 각 부양가족은 한 명의 사원에 속함 + 사원은 부양가족 없을수도 있지
-> 1:N, 사원은 부분참여. 그리고 DEPENDENT는 약한 엔타입이어서 이중실선.

- CONTAIN이란 관계를 통해 부품의 포함관계는  1:N으로 연결

- 3진 관계를 연결하고 나면, 최종적으로 우측과 같이 된다. ER 스키마 다이어그램.

- 정규 엔타입 : 임플로이, 프로젝트, 서플라이, 파트, 디파트먼트

- 약한 엔타입 : 폴리시 아래의 디펜던트

-> 엔타입이 6개라고 최종 테이블도 6갠 아님.

 

- 논리적 설계 하려면 개념적 모델을 관계DB의 릴레이션들의 모임으로 바꿔줘야함

 

- 완성한 개념적 스키마로부터 논리적 설계 단계를 거치면 논리적 스키마가 됨.

- 알고리즘은 같은 목적을 위한 7단계로 이루어짐.

- 1:N만 정규 2진이라고 함.

 

 

- 각 엔타입마다 릴레이션을 만들고, 단순 애트리뷰트들을 릴레이션에 포함시킴.

- 엔타입의 기본키와 릴레이션의 기본키는 같음.

 

- 약한 엔타입을 해결함. 부분키 + 얘를 소유하는 엔타입의 기본키 참조 외래키를 넣어서 1:N관계를 맺어줌.

- Dpname과 sex는 이미 갖고 있었고, Empno가 기본키를 참조하는 외래키.

이렇게 스키마부터 만들어놓고, 최종적으로 릴레이션을 만들어야 함.

 

 

- 단계 3에서는 1:1관계 : 하나임. Manages

-> 전체 참여하는 릴레이션에 외래키를 추가함 + 관계 타입이 갖는 애트리뷰트도 추가해준다
-> 나중에 → 조인을 한 번 해야함.

- 매니저는 empno를 참조하는 외래키.

 

- N쪽에 1쪽의 기본키를 참조하는 외래키 추가. 한 릴레이션 내에서 컬럼은 구분되어야하니, 서브 파트 넘버 : 파트넘버를 참조하는 외래키!

-> Emp쪽에서 proj를 참조시키면 100명에 프로젝트가 10개면 90개가 null이지. 굴러는 간다. 그러나 피하는게 좋음.

 

 

- M:N은 반드시 새 릴레이션으로 만든다…!! 방법이 없다!

-> 붙어있는 각 기본키를 참조하는 외래키들이 들어오고, 따로 가진 컬럼을 넣어줌.

새로운 릴레이션이 만들어 진 것. 각각의 외래키와 관계가 갖고 있던 컬럼 2개 넣어줌.

 

- 참여하는 엔타입이 3개 이상인데, 각각에 대해서는 단계1에서 릴레이션 변환 했음.

-> 그 기본키들을 다 외래키로 가져온다. 그리고 갖고있는 관계의 애트리뷰트들을 가져온다. 그 외래키를 다 모은 것이 기본키가 됨.

 

- location만 따로 만들면, 도저히 뭔지 알 수 없음

→ 어느 프로젝트와 연관된 도시냐, 로케이션이 붙어있었던 프로젝트는 이미 단계1에서 릴레이션으로 만들어졌음. 이 기본키를 참조하는 외래키를 넣어줌. 이 두개가 모여서 기본키가 된다.

-> 다치 자체 + 외래키의 조합 = 기본키

- 궁극적으로 이렇게 된다.