[DB] 데이터 모델링 실습 ERD 그리기

11월 1~3일 사이에 사전 과제를 해야하는데

DAE가 현업에서 자주 마주하는 업무를 간소화한 데이터 모델링 문제와 쿼리(SQL) 테스트라는 거임...

그거 어떻게 하는건데...

 

그래서 ERD 실습 문제를 풀어보려고 합니다^^

이 분이 올려둔 문제 5개를 풀어보려고 한다  

https://rang22.tistory.com/21

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

 

---

 

실습1. 학적과 수강신청

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

 

우선 강사, 학생, 과목 엔티티가 필요해보인다.

각 엔티티 간의 관계는 다음과 같다.

 

1강사 : M과목

M과목 : N학생

 

그리고 과목과 학생 간에 학점이 부여된다고 하니까 학점을 저장할 엔티티도 필요할 것 같다 

 

1과목 : M학점

1학생 : M학점

 

마지막으로 강의 시간과 장소를 저장할 엔티티가 필요할 것 같다 

 

1과목 : M시간

 

 

왼쪽은 내가 그린 ERD, 오른쪽은 블로그 주인장님이 그리신 ERD

강의정보 테이블에 강의시간까지 복합키로 사용하는 건 생각못했다 !

아마 시간에 따라 강의실이 바뀔 수 있어서 저렇게 하신게 아닐까 싶은데 .. 나는 강의실은 고정이라고 생각해서 과목 엔티티 속성에 강의실을 넣어버렸다. 

그리고 학점이 아니라 수강과목이라는 이름으로 엔티티를 만든 것도 배워야 될 것 같다

 

1번 문제를 푸니까 ERD 표기법하고 그리는 방법 좀 공부해야 할 것 같아서 아래 유튜브 영상을 봤다

간단한 예제로 실습해주시는데 한 번 보니까 그래도 어떻게 하는 건지 감이 잡히는 것 같기도 ...? 아닌 것 같기도.....

많이 연습해보라고 해서.. 많이 연습해봐야겠다고 생각했다면서...^^

 

 

>> 참고 유튜브

https://youtu.be/jsOPr3QfMW0?si=ONWaxxfb6k6kcj_x 

>> ERD 그리는 사이트

 

---

11.01

 

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

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

 

우선 업무 처리 규정을 봤을 때

고객, 고객 분류, 승용차, 사고기록 엔티티가 필요할 것 같다

엔티티 관계는 다음과 같이 나타내봤다

 

M고객 - 1회사원
M고객 - 1자유업자
1고객 - M승용차
1승용차 - M사고기록

 

• super-sub 관계
  • 부모의 속성 중에 더 작은 그룹으로 분리해서 관리할 필요가 있는 속성이 있으면 슈퍼타입 - 서브타입 단위로 모델링
  • 직종으로 분류되는 회사원 / 자유업자는 배타적인 관계이므로 Exclusion 카테고리임 
  • I/E 표기법에서는 반원 모양안에 X를 그리는 것으로 표기함 
  • 자동차 보험회사이므로 모든 고객은 승용차를 한 개 이상 소유해야 함 => 표기 수정 필요
  • 승용차 엔티티에 고객번호를 외래키로 주는 게 훨씬 간단함
• 식별관계(실선)
  • 부모 테이블의 기본키 또는 유니크키를 자식 테이블이 자신의 기본키로 사용하는 것
  • 반드시 부모 테이블에 데이터가 존재해야 자식 테이블에 데이터를 입력할 수 있음 
• 비식별관계(점선)
  • 부모 테이블의 기본키 또는 유니크키를 자신의 기본키로 사용하지 않고, 외래키로 사용하는 것 
  • 부모와의 의존성을 줄여 조금 더 자유로운 데이터 생성 및 수정이 가능함 

>> 참고 블로그

https://zaop.tistory.com/entry/%EB%8D%B0%EC%9D%B4%ED%84%B0%EB%AA%A8%EB%8D%B8%EB%A7%81-Barker%EB%B0%94%EC%BB%A4-IE-%ED%91%9C%EA%B8%B0%EB%B2%95

데이터모델링 Barker(바커), IE 표기법

 

---

 

실습3. 어학원 강사정보

[ 강사정보 관리 ] 강사정보는 아래의 강사지원서를 참고하여 등록함. 강사지원서는 내국인용과 외국인용이 있으며, 각 강사 별로 강사번호가 부여됨. 그 외에 내/외국인 모두 학력사항, 경력사항을 관리함  [ 강좌정보 관리 ] 학원에는 여러 강좌가 있으며 각 강좌별로 강좌코드, 강좌명, 수업료, 수업일수를 관리함 각 강사는 여러 강좌에 배정되며, 각 강좌는 단 한 명의 강사에 의해 진행됨

 

 

1) 강사정보 엔티티에 최대한 증복되는 속성들을 저장하고 내/외국 여부 속성을 두자 

    내국인, 외국인을 강사정보 엔티티의 서브 엔티티로 둬야할 듯 

2) 학력사항, 경력사항은 한 명의 강사에 여러 개 값이 생길 수 있음 

3) 강좌 엔티티 필요

 

강사정보, 학력사항, 경력사항, 내국인, 외국인, 강좌 엔티티가 필요하고 엔티티간 관계는 다음과 같다.

 

super강사정보 - sub내국인 / sub외국인

1강사정보 : M학력사항

1강사정보 : M경력사항

1강사정보 : M강좌정보

 

• 강사정보에 국적구분 안넣었네.. 
• 관리 정보에 나와있지 않은 부분까지 생각해야 할 듯 
  • 학력사항, 경력사항에 순번 추가
• 강좌가 비식별관계로 설정됨. 일단 강좌가 개설된 후 강사가 배정될 수 있기 때문 

 

---

 

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

[ 업무 처리 내용 ] 각 부서에서 구매의뢰를 함 한 번의 구매의뢰로 여러 개의 자재를 구매의뢰 할 수 있음 자재는 자재코드로 관리됨  구매의뢰 내역에 따라 구매발주가 이루어짐  한 구매의뢰는 여러 번 구매발주될 수 있음. 한 구매발주는 한 구매의뢰 번호와 관련됨  한 구매 당 한 장의 구매발주서는 한 거래처에 발행됨  한 구매발주서에는 여러 자재를 발주할 수 있음  [ 보충사항 ] 구매의뢰서에는 구매의뢰번호, 구매부서, 구매의뢰일자, 자재명, 요구수량 등 관리  구매발주서에는 거래처, 구매 발주일자, 자재명, 발주수량, 단가, 납기일자 등 관리 거래처는 거래처이름, 대표자, 연락 전화번호, 사업자 등록번호, 주소 등 관리 부서는 부서명, 대표 전화번호 등 관리

 

복잡해졌다 ...

일단 눈에 보이는 건 부서, 자재, 구매의뢰내역, 구매발주내역, 거래처 엔티티이다.

엔티티 관계를 생각해봤는데 넘 헷갈림 

 

1부서 : M구매의뢰서

1구매의뢰서 : M자재

1구매의뢰서 : M구매발주서

1구매발주서 : 1거래처

1구매발주서 : M자재

 

일단 도전해봤는데 영 감을 못잡겠다 ㅠㅜㅠㅠ

• 이렇게 엔티티가 많아지고 관계가 복잡해지면, 단계별로 생각하면서 풀자 
  • 엔티티간의 관계를 해석해보기
  • 엔티티 관계 정의서 작성 
• 한 구매의뢰(발주)에 여러 종류의 자재가 포함될 수 있고, 한 자재가 여러 구매의뢰(발주)에 포함될 수 있으므로 N:M 관계로 표현하는게 맞을 것 같음 

 

---

 

실습5. 회사 업무

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

 

이 문제는 정답이 없어서 내가 알아서 잘 풀어야 한다 o<-<                                                                                                                                                                                                                                                                                                   

아닌 것 같지만 일단 얼렁뚱땅 만들어봤다 

진짜 마구잡이구만 ;; 

관계정의서 다시 그려서 만들어봐야 할 것 같다 

 

---

 

일단은 여기까지 문제를 다 풀어봤다... 풀어보긴 했다............., 에휴

암튼간에 오랜만에 ERD 그려서 재밌었고 연습이 진짜진짜진짜 많이 필요하다는 걸 느꼈다 

 

어떤 엔티티가 있고, 엔티티간의 관계가 어떻게 되는지 관계정의서를 먼저 작성해보는게 중요하다는 걸 느꼈다..

그냥 눈으로만 보면 넘 복잡하고 감이 안잡힘 ㅠ