데이터 모델링 실습

예제 1 - 학적과 수강신청

---

요구 사항 명세서

1. 학적과에는 각 과목을 강의하는 강사, 등록한 학생과 강의 시간, 요일, 장소 등의 데이터가 유지된다.

2. 한 강사가 여러 개의 과목을 강의할 수 있으며, 각 과목과 학생 간에는 학점이 부여된다.

3. 과목에 대해서는 과목번호, 과목명 등의 정보가 유지되어야 한다.

4. 강사에 대해서는 강사번호, 이름, 나이, 성별 등의 정보가 유지되어야 한다.

5. 학생에 대해서는 학번, 이름, 주소 등의 정보가 유지되어야 한다.

---

개념적 데이터 모델링

1. 먼저 개체와 속성을 추출합니다.

   주로 요구사항 명세서에서 명사들을 추출합니다.

   추출한 명사들을 조합해 개체와 속성을 작성합니다.

   • 요구사항 1번에서 강사, 과목, 학생, 강의 시간, 강의 요일, 강의 장소를 얻을 수 있습니다.

   • 요구사항 3번에서 과목 엔티티의 속성으로 과목 번호, 과목명 등을 얻을 수 있습니다.

   • 요구사항 4번에서 강사 엔티티의 속성으로 강사 번호, 이름, 나이, 성별 등을 얻을 수 있습니다.

   • 요구사항 5번에서 학생 엔티티의 속성으로 학번, 이름, 주소 등을 얻을 수 있습니다.

   얻어낸 정보를 표로 정리해주겠습니다.

   

2. 관계를 추출합니다.

   일반적으로 요구 사항 명세서의 동사를 추출합니다.

   개체 간의 연관성을 표현한 동사를 선택합니다.

   • 요구사항 2번에서 강사와 과목의 관계는 1 : N이고 학생과 과목의 관계는 N : M임을 알 수 있습니다.

     M : N 관계는 중간 엔티티를 두어 표현합니다.
     중간 엔티티에 학점 속성을 부여합니다.

   • 강의 시간, 강의 요일, 강의 장소를 강의 정보 개체로 묶어주겠습니다.

     강의시간, 강의요일을 기본키로 강의 정보 개체를 만듭니다.

   

3. 작성된 데이터를 바탕으로 ER 다이어그램을 작성합니다.

   ER 다이어그램 작성 툴로는 웹 서비스인 Draw.io를 이용했습니다.

   과목 개체의 강의시간, 강의장소 속성을 강의 정보라는 개체로 따로 꺼내 주었습니다.
   과목과 강의 정보의 관계는 한 과목에 여러 강의 장소와 시간이 설정될 수 있으므로 1 : N 관계로 설정하겠습니다.

   

---

논리적 데이터 모델링

먼저 5가지 과정을 순서대로 적용하여 ER 다이어그램을 릴레이션 스키마로 변환합니다.

1. 모든 개체를 릴레이션으로 변환

   E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환합니다.

   • 개체의 이름을 릴레이션의 이름으로 하고, 개체가 가진 속성도 릴레이션의 속성으로 그대로 변환합니다.

   • 개체가 가지고 있는 속성이 복합 속성인 경우, 복합 속성을 구성하는 단순 속성만 릴레이션의 속성으로 변환합니다.

   • 개체가 가지고 있는 키 속성을 릴레이션의 기본 키로 변환합니다.

   

2. N : M 관계는 릴레이션을 변환

   개념적 데이터 모델링 단계에서 생햑했다면 N : M 관계는 중간 릴레이션을 생성해서 관계를 표현합니다.

   위 단계에서 학점부여 관계를 중간 개체로 생성한 것을 말합니다.

   • 관계를 맺고 있는 릴레이션들의 기본 키를 외래키로 가져와 기본 키로 사용합니다. (필요 시 별도의 기본 키 지정 가능)

   

3. 1 : N 관계는 외래 키로 표현

   1 : N 관계는 릴레이션으로 변환하지 않고 외래 키로만 표현합니다.

   • 일반적인 1 : N 관계는 외래 키로 표현합니다.

   • 약한 개체가 참여하는 1 : N 관계는 외래 키를 포함하여 기본 키로 지정합니다.

   약한 걔체란? 다른 개체의 존재여부에 의존하는 개체

   

4. 1 : 1 관계는 외래 키로 표현

   • 일반적인 1 : 1 관계는 외래 키를 주고 받습니다.

   • 한 개체만 필수적으로 참여할 경우 해당 개체의 릴레이션만 외래 키를 받습니다.

   • 모든 개체가 필수적으로 참여할 경우 릴레이션을 하나로 합쳐줍니다.

5. 다중 값 속성은 릴레이션으로 변환

   불필요한 중복 저장 문제를 해결하는 과정입니다.

   • 다중 값 속성과 함께 그 속성을 가지고 있던 개체 릴레이션의 기본 키를 외래 키로 가져와 새로운 릴레이션에 포함시킵니다.

   • 새로운 릴레이션의 기본 키는 다중 값 속성과 외래 키를 조합하여 지정합니다.

---

그런 다음 작성한 릴레이션 스키마를 테이블 명세서로 변환합니다.

속성의 데이터 타입, 길이, Null 값 허용 여부, 기본 값, 제약 조건 등을 세부적으로 결정합니다.

---

정규화

위 예시에서는 생략했지만 릴레이션 스키마를 테이블 명세서로 변환하기 전에 정규화 과정을 진행합니다.

정규화란 데이터베이스의 설계를 재구성하는 과정입니다.

정규화를 통해 불필요한 데이터를 삭제하거나 이상현상을 방지할 수 있습니다.

• 수정 이상 : 반복된 데이터 중에 일부만 수정하면 데이터 불일치 발생
• 삽입 이상 : 불필요한 정보 함께 저장하지 않으면 어떤 정보 저장 불가
• 삭제 이상 : 유용한 정보 함께 삭제하지 않고는 어떤 정보 삭제 불가

정규화는 1차 정규형(1NF), 2차 정규형(2NF), 3차 정규형(3NF), BCNF, 4차 정규형(4NF), 5차 정규형(5NF)로 나누어집니다.

실무에서는 주로 3NF 형태를 사용합니다.

• 1NF

  테이블의 모든 속성 값이 도메인 원자값만으로 되어 있는 정규형을 말합니다.

  • 만족 조건

    한 속성에 값이 여러개라면 또 하나의 도메인으로 분리합니다.

    Domain (도메인) : 하나의 속성이 갖는 값

    반복 그룹이 존재하면 테이블을 분리합니다.

    모든 행이 식별자로 완전하게 구분되도록 합니다.

• 2NF

  제1 정규화를 진행한 제1 정규형 테이블에 대해 완전 함수 종속을 만족하도록 테이블을 분해합니다.

  • 만족 조건

    기본키의 부분 집합이 결정자가 되어선 안된다는 것을 말합니다.

    결정자 : X의 값에 따라 Y 값이 정해지면 X는 Y의 결정자

  테이블의 기본 키가 2개 이상일 때만 진행합니다.

• 3NF

  제2 정규화를 진행한 제2 정규형 테이블에 대해 이행적 종속을 없애도록 테이블을 분해합니다.

  이행적 종속 : A -> B, B -> C 이면 A -> C 가 성립하는 것

  • 만족 조건

    모든 key가 아닌 속성은 key에만 완전히 종속 되어야 합니다.

• BCNF

  제3 정규화를 진행한 제3 정규형 테이블에 대해 모든 결정자가 후보 키가 되도록 테이블을 분해합니다.

---

물리적 데이터 모델링

먼저 위에서 작성한 테이블 명세서를 정해진 DB 명명 규칙에 따라 변환합니다.

참고 글 : DB 명명 규칙

이제 Workbench에 테이블 명세서를 바탕으로 ER 다이어그램을 작성합니다.

참고 글 : 물리적 데이터 모델링

Workbench의 Forward Engineer를 활용해 테이블을 생성합니다.

---

예제 2 - 자재 구매의뢰업무

---

요구 사항 명세서

1. 각 부서에서 구매의뢰를 요청한다.

2. 한 번의 구매의뢰로 여러 개의 자재를 구매 의뢰할 수 있다.

3. 자재는 자재코드로 관리된다.

4. 구매의뢰 내역에 따라 구매발주가 이루어진다.

5. 한 구매의뢰는 여러 번 구매 발주될 수 있다.

6. 한 구매 발주는 한 구매의뢰번호와 관련된다.

7. 한 장의 구매발주서는 한 거래처에 발행된다.

8. 한 구매발주서에는 여러 자재를 발주할 수 있다

9. 구매의뢰서에는 구매의뢰번호, 구매부서, 구매의뢰일자, 자재명, 요구수량 등을 관리한다

10. 구매 발주서에는 거래처, 구매 발주일자, 자재명, 발주수량, 단가, 납기일자 등을 관리한다.

11. 거래처는 거래처이름, 대표자, 연락전화번호, 사업자 등록번호, 주소 등을 관리한다

12. 부서는 부서명, 대표전화번호 등을 관리한다.

---

개념적 데이터 모델링

• 개체와 속성을 추출

  • 요구사항 명세서에서 명사들을 추출합니다.

    1. 부서, 구매의뢰
    2. 구매의뢰, 자재, 구매 의뢰
    3. 자재, 자재코드
    4. 구매의뢰, 구매발주
    5. 구매의뢰, 구매발주
    6. 구매발주, 구매의뢰번호
    7. 구매발주서, 거래처
    8. 구매발주서, 자재
    9. 구매의뢰서, 구매의뢰번호, 구매부서, 구매의뢰일자, 자재명, 요구수량
    10. 구매발주서, 거래처, 구매 발주일자, 자재명, 발주수량, 단가, 납기일자
    11. 거래처, 거래처이름, 대표자, 연락전화번호, 사업자 등록번호, 주소
    12. 부서, 부서명, 대표전화번호

  • 다음은 추출한 명사들을 대체하거나 삭제합니다.

    1. 부서, 구매의뢰
    2. 구매의뢰, 자재
    3. 자재, 자재코드
    4. 구매의뢰, 구매발주
    5. 구매의뢰, 구매발주
    6. 구매발주, 의뢰번호
    7. 구매발주, 거래처
    8. 구매발주, 자재
    9. 구매의뢰, 의뢰번호, 구매부서, 의뢰일자, 자재명, 요구수량
    10. 구매발주, 거래처이름, 발주일자, 자재명, 발주수량, 단가, 납기일자
    11. 거래처, 거래처이름, 대표자, 연락전화번호, 사업자 등록번호, 주소
    12. 부서, 부서명, 대표전화번호

  • 개체로 사용할 명사들을 선정합니다.

    부서, 구매의뢰, 자재, 구매발주, 거래처

  • 얻어낸 정보를 표로 정리해주겠습니다.

  

• 관계를 추출합니다.

  일반적으로 요구 사항 명세서의 동사를 추출합니다.

  개체 간의 연관성을 표현한 동사를 선택합니다.

  • 요구사항 1번을 보면 부서에서 구매의뢰를 합니다.

    부서에서 구매 의뢰를 등록하며 1 : N 관계임을 알 수 있습니다.

  • 요구사항 2번에서 의뢰목록과 자재의 관계는 N : M관계임을 알 수 있습니다.

    M : N 관계는 중간 엔티티를 두어 표현합니다.
    요구수량을 속성으로 구매정보 엔티티를 생성합니다.

  • 요구사항 5번에서 구매의뢰과 구매발주은 1 : N 관계임을 알 수 있습니다.

  • 요구사항 6번에서 구매발주과 거래처는 N : 1 관계임을 알 수 있습니다.

  • 요구사항 7번에서 구매발주과 자재은 N : M 관계임을 알 수 있습니다.

    M : N 관계는 중간 엔티티를 두어 표현합니다.
    발주수량을 속성으로 발주정보 엔티티를 생성합니다.

  

• 작성된 데이터를 바탕으로 ER 다이어그램을 작성합니다.

  

---

논리적 데이터 모델링

5가지 과정을 순서대로 적용하여 ER 다이어그램을 릴레이션 스키마로 변환해보겠습니다.

1. 모든 개체를 릴레이션으로 변환

   E-R 다이어그램의 각 개체를 하나의 릴레이션으로 변환합니다.

   

2. N : M 관계는 릴레이션을 변환

   개념적 데이터 모델링 단계에서 생햑했다면 N : M 관계는 중간 릴레이션을 생성해서 관계를 표현합니다.

   구매정보, 발주정보 개체를 말합니다.

   

3. 1 : N 관계는 외래 키로 표현

   1 : N 관계는 릴레이션으로 변환하지 않고 외래 키로만 표현합니다.

   

4. 1 : 1 관계는 외래 키로 표현

5. 다중 값 속성은 릴레이션으로 변환

---

이제 작성한 릴레이션 스키마를 테이블 명세서로 변환합니다.

속성의 데이터 타입, 길이, Null 값 허용 여부, 기본 값, 제약 조건 등을 세부적으로 결정합니다.

---

물리적 데이터 모델링

먼저 위에서 작성한 테이블 명세서를 정해진 DB 명명 규칙에 따라 변환합니다.

참고 글 : DB 명명 규칙

이제 Workbench에 테이블 명세서를 바탕으로 ER 다이어그램을 작성합니다.

참고 글 : 물리적 데이터 모델링

Workbench의 Forward Engineer를 활용해 테이블을 생성합니다.

---