12. DB :: DB모델링 정리

1. 개요

모델링?

실체와 모델이 있다면, 실체를 모델로 구현해내기 위해 모델링이 필요하다.
실체 → 모델링 ⇒ 모델
실제 <= 구현 ← 모델

1) 소프트웨어 개발과정

사실상 빨간 선 위에 있는 영역은 DB개발자가 진행하는 편이다.

클라이언트가 요청하는 서비스를 구현해내기 위하여 DB를 구축하는데, 그 과정에서 논리적 모델링과 물리적 모델링을 거치게 된다.

2) 모델링의 주요 개념

(1) 엔티티(Entity)

업무의 관심 대상이 되는 정보를 갖고 있어나 그에 대한 정보를 관리할 필요가 있는 유형/무형의 개체 (학생, 사원 등)

즉, 데이터 집합이 될 테이블

• 조건
  1. 업무 관심 대상이 되는 사물이어야 함
  2. 두개 이상의 인스턴스가 있어야한다(단일값이면 이용 가치가 없음)
  3. 속성을 하나 이상 가져야한다.

(2) 속성(Attribute)

엔티티에서 관리해야할 최소 단위 정보의 항목.

엔티티에는 하나 이상의 속성이 있다.

즉, 데이터에 존재하는 칼럼

예) 학생 → 학번, 이름, 주소, 전공 등

• 명명규칙
  1. 속성 의미가 드러나게 작명
  2. 업무에서 사용하는 이름을 활용
  3. 서술식(수식어, 소유격)이나 약어는 사용하지 말 것
  4. 엔티티 내에서 식별이 되도록 지정할 것 (중복 X)

(3) 인스턴스(Instance)

엔티티의 속성으로 실제 구현된 하나의 값(row)

예) 학생 → 학번, 이름, 주소, 전공 → 20150933, 김댕댕, 경기도, 환경공학

(4) 관계(Relationship)

두 엔티티 사이의 관련성

예) 학생 ↔ 수강과목 수강한다

(5) 카디널리티(Cardinality)

각 엔티티에 속한 인스턴스들 간 어떤 관계가 있는지를 나타내는 것

종류 : 1:1 / 1:N / M:N 관계

예) 학생 테이블의 1개 row(1명의 학생)는 학점 엔티티에서 학기별로 다양한 row를 갖고있다. → 1:N 관계

관계 한눈에 보기

1:1 관계	1:N 관계
M:N 관계 : 완성도가 낮다고 볼 수 있는 데이터 구조이기 때문에 중간에 join 테이블을 만들어주는 등의 방법으로 해소가 필요하다.

 

(6) 주식별자(Primary Identifier)

엔티티 내 각 인스턴스를 구별하는 기준이 되는 속성

유일값, 최소성, 불변성, 존재성

중복 없는 값 = 기본키

단, 하나가 아닌 여러 속성일 수 있음(복합 키)

주 식별자가 없다면 새로운 속성을 만들어서 넣어줌(인위적 주식별자)

예) 학생 → 학생번호

(7) 외래식별자

관계가 있는 두 엔티티 간의 연결고리 역할을 하는 속성

즉, 부모의 주 식별자와 공통 속성이 자식에게도 존재하는 속성

없다면 자식에게 속성을 추가한 뒤 지정함

예) 물품 테이블의 물건 번호

 

2. 개념적 모델링

요구분석 단계에서 정의된 핵심개체와 그들의 관계를 바탕으로 ERD(데이터) 생성하는 단계

1) 엔티티 도출

업무분석

• 현장에서 사용하는 문서들을 통한 분석(거래 명세서, 업무 기술서 등)
• 실무자를 대상으로 한 인터뷰 등

엔티티 도출 과정

: 정해진 공식은 없으나 경험이 없으면 아래 방법을 참조하여 엔티티 기술서를 작성한다.

1. 엔티티 후보 풀과 엔티티 리스트를 그린다
2. 분석 대상 문서를 보고 명사를 찾아 표시한다. (예) 고객, 대출, 등
3. 명사 하나별 속성인지 엔티티인지 구분한다.
4. 중복된 명사나 유사 의미 명사는 하나로 정리한다.
5. 위 내용들을 엔티티 후보 풀에 추가하여 검토한다.
6. 도출된 엔티티에 대해 구축될 시스템에서 데이터를 관리할 필요가 있는지 판단한다.

엔티티 도출 예시

1. 도서 대출을 위한 회원가입
2. 회원가입: 이름, 전화번호, 집주소, 이메일 등
3. 회원증 발급 후 회원증 바코드로 신분확인
4. 회원 관리를 위한 회원 번호가 있다
5. 회원의 대출 가능 도서는 최대 5권⇒ 도서 대출전표를 참고하여 최종 엔티티 도출엔티티 명 설명 포함속성  

   엔티티명	설명	포함속성
   도서	대출 도서에 대한 상세정보	도서 관리번호, 제목
   대출	회원이 도서를 대출한 내역 정보	대출일, 회원번호, 이름, 대출번호, 반납예정일

2) ER-Diagram (ERD) :: 개체 관계도

요구분석에서 얻은 엔티티와 속성들을 그림으로 그려 관계를 도출한 것

사원 엔티티의 ERD 예시

(1) 표기법(관계)

• 참여도
  • 필수(||)와 선택(O) 중 선택할 수밖에 없음
  • 어떤 기준 엔티티를 기점으로 반드시 대응되야하는 엔티티가 있다어야한다면 필수, 그외는 선택에 해당한다.
• 카디널리티
  • 인스턴스 1개와 대응된다면 실선으로 표시, 다수와 대응된다면 꺽쇠가 있는 실선으로 표시
• 카디널리티와 참여도에 따른 관계 종류 표시

 

(2) 식별 - 비식별 관계

식별관계

• 1:N 관계에서 외래 식별자가 자식 엔티티의 주식별자의 일부가 되는 관계
  • 중복 또는 null이 들어오면 안되는 값이 있는 경우에 사용
• PFK로 표시(외래 식별자면서 주식별자로 사용하고 있다면 식별관계)
• 실선 사용
• 예) 구매 테이블에서 회원과 상품을 외래키면서 메인키로 사용하고 있다면 식별관계이다.

비식별 관계

• 거의 대부분 비식별 관계
• 1:N관계에서 외래 식별자가 주 식별자가 되지 못하고 일반 속성으로 추가되는 관계
• FK로 표시(외래 식별자의 역할만 수행)
• 점선 사용

 

3. 논리적 모델링

개념설계에서 추상화된 데이터를 구체화하여 개체, 속성을 테이블화하고 상세화 (테이블 명, 제약조건 등을 설정)하는 정규화 과정

1) 정규화(DB normalization)

관계형 데이터베이스에서 데이터를 구조화하는 작업

정규화를 거치지 않은 테이블은 모든 속성이 원자값을 가지고 있다.

💡 원자값: 나눌 수 없는 단일의 값(칼럼 안에)
예) 취미: 운동, 영화 코딩 → 원자값X
      운동, 영화, 코딩이 각 row로 분리되어 각 데이터가 원자값이 된다

정규화의 목적

• 데이터 중복 방지, 효율 저장
• 삽입, 삭제, 갱신 이상의 발생 가능성 감소

정규화의 단계

• 1정규화
  : 기본적으로 데이터베이스를 설계할 때, 테이블이 다음 조건을 충족하도록 설정하는 것이다.
  
  • 원자성 : 각 칼럼이 더이상 나눌 수 없는 값을 가져야함
    • 하나의 속성이 단일값을 갖도록 하는 것 (모든 속성이 기본키 그룹에 완전하게 종속되도록 설정)
  • 중복된 행 없음 : 각 고유한 행
  • 예) 사원 취미 테이블에 동일 사원의 다수의 취미 row를 생성하고 있음 → 취미 테이블을 분리하고 사원 테이블에는 사번을 PK로, 취미테이블에는 취미를 PK, 사번을 PEK로 설정하여 참조 관리 진행

• 2정규화
  부분적 종속성을 제거하는 단계 : 한 테이블에 주식별자가 2개 이상의 컬럼으로 이루어져있을 때 필요. 즉, 주식별자 전체가 아닌 일부에만 종속된 속성을 제거하는 것
  
  • 예) 학생테이블에서 과목코드와 학번 pk가 있음. ⇒ 과목코드에 종속된 칼럼들을 과목 테이블로 분리
  • 예) 주문품목 테이블에서 품목단가는 품목코드에 종속. 품목코드를 주식별자로 품목 테이블을 분리하고 기존 테이블에는 품목코드를 PFK 관계 설정한다.
  • 2정규화의 목표 = 완전함수 종속 상태를 만들어야한다 : 테이블의 모든 속성이 기본키 전체에 종속되는 것

• 3정규화
  : 1정규화와 2정규화를 준수하면서 이행적 종속을 제거하는 단계
  
  • 이행적 종속성 제거 : 하나의 테이블에서 주 식별자가 아닌 칼럼중에서 칼럼끼리 종속관계가 있을 때 필요 → 각 테이블이 기본키에만 전체 종속되도록 테이블 분리
  • 예) 주문 테이블 안에 고객번호, 고객명, 고객주소 칼럼이 있다. → 고객 테이블을 따로 만들어서 고객번호만 참조키FK로 가져오도록 변경

반응형

2) 이상(Anomaly)

(1) 삽입이상

불필요한 정보를 무조건 같이 저장하게 하는 것

이를 방지하기 위해 테이블 분리가 필요하다

예) 제품 테이블(제품번호, 제품명, 단가, 주문수량)에 주문번호 칼럼이 같이 있는경우, 제품 정보만 입력하고 싶어도 주문번호를 null로라도 입력해야함

(2) 갱신이상

하나를 바꾸었을 때 데이터의 불일치가 발생하는 것 → 정변처리, 테이블 분리

예) 동일한 제품명을 중복등록해야하는 테이블의 의자 상품의 가격을 바꿨는데, 1개 의자 상품만 가격이 바뀌고 다른 의자 가격은 변하지 않기 때문에 중복되어있는 수만큼 수정을 해야함

(3) 삭제 이상

유용한 정보가 무조건 같이 삭제되는 것

예) 제품 테이블(제품번호, 제품명, 단가, 주문수량)에 주문번호 칼럼이 같이 있는경우, 주문 취소시 제품번호, 제품명, 단가, 주문수량이 같이 삭제된다.

3) 정규화가 필요없는 설계기법

• 원칙 1
  • 장부나 전표에서 머리부(상단 설명에 나오는 용어)와 서술부(하단 표 등에 등장하는 세부 컬럼)는 별도의 엔티티로 저장한다.
• 원칙 2
  • 코드-코드 관계에 있는 속성들은 별도의 엔티티로 구성한다.

4. 물리적 모델링

논리적 설계 단계에서 표현된 데이터를 실제 컴퓨터의 저장장치에 어떻게 표현할 것인가 고민하는 단계

즉, 논리적 설계의 산출물인 ERD의 요소들을 관계형 데이터베이스의 요소들로 전환하는 것

논리적 DB설계 VS 물리적 DB 설계

각 요소별 비교

반정규화(De-Nomalization)

정규화 완료 후 물리 모델링 중 시스템 성능 향상, 편의성, 운영 단순화를 위해 반정규화를 진행하게 될 수도 있다.

정규화를 통한 데이터 무결성 유지도 중요하지만, 일정 성능을 유지하는 것도 중요하기 때문

반정규화를 하게 되는 경우를 간단히 살펴보자

 

경우1 :: 엔티티 통합

검색이 빈번한 두 엔티티를 대상으로 join을 활용하여 엔티티를 통합한다.

 

경우2 :: 수직분할에 의한 반정규화

엔티티의 속성 수가 너무 많고 속성들이 그룹화되어 각 그룹이 특정 경우에만 사용되는 경우 수직분할을 통해 분리가 가능하다.

 

경우3 :: 수평분할에 의한 반정규화

조회빈도에 따라 엔티티를 분할하여 자주 검색되는 20%와 나머지 80%를 분리하는 것

 

경우4 :: 속성 중복에 의한 반정규화

join을 이용하여 가져다 쓰는 속성이 적을 때, 각 엔티티에 속성을 중복하여 저장하는 방법

 

경우5 :: 관계에 대한 반정규화

중복되는 속성을 다른 엔티티와 관계를 맺어주기 위한 외래키로 사용 → 조인해야할 엔티티가 줄어듬

 

5. DB 모델링 툴 활용(ERD CLOUD)

www.erdcloud.com 접속 → 로그인 후 ERD 생성

 

ERD 작성방법

기본적으로 L/P모드로 논리적, 물리적 설계를 동시에 진행하게 되어있다.

좌측 버튼으로 전환 가능하다.

엔티티 생성시 좌측은 논리적 설계로 엔티티명, 우측은 물리적 설계로 테이블 명을 의미한다.

 

테이블 추가

• 테이블 아이콘 클릭 후 캔버스 추가할 위치 클릭, 테이블 명을 작성한다.

컬럼추가

• 생성된 테이블에서 + 버튼을 눌러 컬럼을 추가한다.
  • 노란색 + : 주식별자 추가
  • 파란색 + : 일반 칼럼 추가
• 컬럼명, 데이터 타입, 코멘트, 제약조건을 작성할 수 있다.

관계 설정

• 좌측 메뉴에서 도형으로 된 항목을 통해 다양한 관계를 설정할 수 있다.
  1:1 1:N 등

팀추가 방법

조장이 팀을 추가: 팀명 설정, 이메일로 팀원 초대

팀 ERD에 추가하면 됨