[DB] 데이터 모델링 - ERD 실습 문제

실습1. 학적과 수강신청

 

<업무 처리 규정>

▷ 학적과에는 각 과목을 강의하는 강사, 등록한 학생, 강의가 이루어지는 시간(여러개의 값) 및 장소 등의 데이터가 유지된다.
▷ 한 강사가 여러 개의 과목을 강의할 수 있으며, 각 과목과 학생 간에는 학점이 부여된다.
▷ 과목에 대해서는 과목번호, 과목명 등의 정보가 유지되어야 한다.
▷ 강사에 대해서는 강사번호, 이름, 나이, 성별 등의 정보가 유지되어야 한다.
▷ 학생에 대해서는 학번, 이름, 주소 등의 정보가 유지되어야 한다.

 

◆ 문제 풀이

 

먼저 문제 속에서 개념을 추출해보면 강사, 과목, 학생 크게 세가지가 보인다.

또한 강의가 이루어지는 시간이 여러 타임이 있고, 과목을 수강한 학생들은 학점을 부여받는다.

 

세가지 엔티티 간의 관계에 대해 정리를 해보자.

강사 - 과목 => 1 : M
과목 - 학생  => M : N

 

이외에 강의시간을 추가해야하며, M : N의 관계에 Mapping 엔티티를 만들어줘야 한다.

과목  -강의 시간 => 1 : M
학생 - 수강하는 과목 => 1 : M

 

이제 만들어야 하는 엔티티는 총 5가지이다.

강사, 과목, 수강과목, 학생, 강의정보(강의시간 포함)

 

엔티티의 각 속성을 기반으로 관계와 선택성(옵션)를 판단하여 ERD를 작성한다.

 

참고로, 식별자가 같은 엔티티가 있으면 안된다.

왜? 정보를 구분할 수 있는 키가 없기 때문에 항상 엔티티의 식별자를 확인하자!

 

 

 

---

 

 

실습2. 자동차 보험회사 고객관리

<업무 처리 규정>

▷ 자동차 보험회사는 많은 고객을 관리하고 있다.
▷ 고객에 대해서는 고객번호, 이름, 최초보험가입일과 주소 정보를 관리한다.
▷ 고객은 회사원과 자유업자로 분류된다. 
▷ 회사원은 회사명과 차량용도, 자유업자는 업종과 주당운행거리 등의 정보를 유지한다. 
▷ 각 고객은 승용차를 보유한다. 이때 각 차는 사고기록을 유지한다.
▷ 승용차에 대해서는 등록번호, 제작 년도 및 색상 정보를 관리하고
   사고 기록을 위해서는 사고번호, 사고 발생 일시, 사고 장소 등의 정보를 관리한다

 

◆ 문제 풀이

문제 속에서 개념을 추출해보면 크게 고객, 승용차, 사고기록 으로 나눌 수 있다.

또한 각 고객의 직종에 따라 추가로 받아야하는 정보가 다르게 입력 받아야한다.

 

먼저, 세가지 엔티티 간의 관계에 대해 정리를 해보자.

고객 - 승용차 => 1 : M
승용차 - 사고기록  => 1 : M

 

이제 직종에 따라 추가로 받아야하는 정보는 super - sub 관계로 정리할 수 있다.

직종으로 분류되는 회사원과 자유업자는 배타적인 관계이므로 Exclusive 카데고리다.

Super : 고객
Sub : 회사원, 자유업자

 

이제 만들어야 하는 엔티티는 sub엔티티까지 포함하여 5가지이다.

고객, 회사원, 자유업자, 승용차, 사고기록

 

각 엔티티의 속성을 기반으로 관계와 선택성(옵션)를 판단하여 ERD를 작성한다.

 

이때, 식별관계와 비식별관계에 대해서는 하나의 정답이 없다!

사용자가 원하는 요구에 따라 작성하는 것이 사용자에게 정답이다.

 

 

 

---

 

 

실습3. 어학원 강사정보

<강사정보 관리>

▷  강사정보는 아래의 강사지원서를 참고하여 등록한다. 강사지원서는 내국인용과 외국인용이 있으며, 각 강사 별로 강사번호가 부여된다.
▷ 그 외에 내/외국인 모두 학력사항, 경력사항을 관리한 다.

<강좌정보 관리>

▷ 학원에는 여러 강좌가 있으며 각 강좌별로 강좌코드, 강좌명, 수업료, 수업일수를 관리한다.
▷ 각 강사는 여러 강좌에 배정되며, 각 강좌는 단 한 명 의 강사에 의해 진행된다.

 

◆ 문제 풀이

문제 속 개념을 추출해보면 크게 강사와 강좌로 나타난다.이 두 가지 개념의 관계를 정리해보자.

강사 - 강좌 => 1 : M

 

그리고 강사에 대한 학력사항, 경력사항 정보입력을 위해 관련 엔티티가 각각 필요하다.

(학력의 경우, 마지막 학력만 입력한다면 1 : 1 관계가 성립된다.)

강사 - 학력사항 => 1 : M
강사 - 경력사항 => 1 : M

 

그 중 강사에 대한 여러 가지 정보가 표현되야 하는데 국적에 따라 받아야하는 정보가 다르다.

국적 카데고리를 기반으로 내국인/외국인의 정보를  표현하면 super - sub 관계로 나타낼 수 있다. 

또한 국적은 배타적인 관계이므로 Exclusive 카데고리다.

Super : 강사
Sub : 내국인, 외국인

 

이제 만들어야 하는 엔티티는 sub엔티티까지 포함하여 6가지이다.

강좌, 강사, 학력사항, 경력사항, 내국인, 외국인

 

각 엔티티의 속성을 기반으로 관계와 선택성(옵션)를 판단하여 ERD를 작성한다.

 

복합키(PK가 두개 이상)인 경우, 

유니크한 값, 검색에 자주 쓰여지는 값이 되는 엔티티를 앞에 줘야 한다.

( EX. 학력사항 - > 강사번호를 먼저 주는 것이 좋다.)

 

 

 

---

 

 

실습4. 자재 구매의뢰업무

<업무 처리 내용>

▷ 각 부서에서 구매의뢰를 한다
▷ 한 번의 구매의뢰로 여러 개의 자재를 구매 의뢰할 수 있다.
▷ 자재는 자재코드로 관리된다
▷ 구매의뢰 내역에 따라 구매발주가 이루어진다.
▷ 한 구매의뢰는 여러 번 구매 발주될 수 있다. 한 구매 발주는 한 구매의뢰번호와 관련된다.
▷ 한 구매 당 한 장의 구매발주서는 한 거래처에 발행된다.
▷ 한 구매발주서에는 여러 자재를 발주할 수 있다

<보충사항>

▷ 구매의뢰서에는 구매의뢰번호, 구매부서, 구매의뢰일자, 자재명, 요구수량 등을 관리한다
▷ 구매 발주서에는 거래처, 구매 발주일자, 자재명, 발주수량, 단가, 납기일자 등을 관리한다.
▷ 거래처는 거래처이름, 대표자, 연락전화번호, 사업자 등록번호, 주소 등을 관리한다
▷ 부서는 부서명, 대표전화번호 등을 관리한다.

 

◆ 문제 풀이

이번 문제에서는단계별로 사고하여 문제를 풀어보자

제일 먼저 엔티티(개념) 간의 관계를 해석하는 관계 매트릭스 작성해보자.

 

	부서	구매의뢰	자재	구매발주	거래처
부서	-	요청한다	-	-	-
구매의뢰	요청받다	-	여러 자재를 의뢰한다	여러 번 구매 발주 될 수 있다	-
자재	-	의뢰된다	-	발주된다	-
구매발주	-	발주된다	여러 자재를 발주한다	-	한 거래처에 발행된다
거래처	-	-	-	여러 발주서가 발행된다	-

 

다음 단계로 엔티티의 관계를 정리하는 관계 정의서를 작성하자.

 

기준 엔티티	관계 형태	참여 방법	관련 엔티티
부서	1 : M	--- 선택 --->  <--- 필수 ---	구매의뢰
구매의뢰	N : M	--- 필수 --->  <--- 선택 ---	자재
구매의뢰	1 : M	--- 필수 --->  <--- 필수 ---	구매발주
자재	N : M	--- 선택 --->  <--- 필수 ---	구매발주
구매발주	N : 1	--- 필수 --->  <--- 선택 ---	거래처

 

 

위에 작성한 관계 정의서를 보고 ERD를 만들어보자.

이때 N : M관계인 구매의뢰 - 자재, 구매발주 - 자재의 경우 Mapping 엔티티를 만들어 작성하자. 

 

 

관계정의서를 통해 엔티티 간의 관계를 파악하고 ERD를 작성한다면

보다 탄탄한 데이터 모델링이 될 것같다!

 

 

---

 

 

실습5. 회사업무

<업무처리규정>

▷ 회사는 부서로 조직되어 있다. 각 부서는 부서명, 부서번호, 부서장에 관한 정보를 유지한다. 또한 부서장의 취임한 날짜도 유지한다. 한 부서는 여러 지역에 위치할 수도 있다.
▷ 부서는 여러 프로젝트를 통제한다. 프로젝트는 하나의 부서에 의해 통제된다.
▷ 프로젝트는 프로젝트명, 프로젝트 번호, 프로젝트가 수행되는 지역(단일 지역)에 관한 정보가 있어야 한다.
▷ 한 회사원에 대해서는 이름, 주민등록번호, 주소, 봉급, 성별, 생년월일 등을 유지한다.
▷ 회사원은 각각 한 부서에 소속된다.
▷ 회사원은 여러 프로젝트에서 일할 수 있다. 각 회사원이 각 프로젝트에서 주당 근무시간을 관리한다.
▷ 회사원에 대해 바로 상위 등급의 관리자가 누구인지 알 필요가 있다.
▷ 회사원은 기술자와 사무원으로 나뉘고 기술자에 대해서는 기술등급, 사무원에 관해서는 타이핑 속도가 중 요한 정보이다.
▷ 보험 혜택을 위해 각 회사원의 부양 가족에 관한 정보도 유지한다. 여기에는 부양 가족의 이름, 성별, 생년 월일, 그리고 회사원과의 관계 등이 포함된다

 

◆ 문제 풀이

-