01. DB 모델링

📅 2021.09.29 ~ 2021.10.04

DB 모델링

Chapter 01. DB 모델링 개요

• 모델링: 모델을 만드는 작업을 뜻하며, 현실 세계를 단순화하여 표현하는 기법이다.기술(쿼리)을 사용하기 위해서는 테이블과 테이블 간의 관계 등이 어떤 요구에 따라 발생하는지 알아야 하기 때문에 모델링을 통해 필요한 개념을 파악하고 구성한다.
• 모델링의 경우 규모가 큰 경우(배, 비행기, 도시 등)에 주로 사용되는데, 프로젝트의 경우는 규모가 작지만 모델링이 선행되어야 한다. 그 이유 중 가장 큰 이유는 결과물을 시각화하여 의사소통을 활발하기 위함이다. 그 외 문제점의 빠른 파악, 손실 최소화 등의 이유가 있다.
• 소프트웨어 개발 과정에서 데이터 관점에 해당하는 부분이 DB 모델링의 전반이며, 프로세스 관점은 프로그램 로직 설계인 클래스 다이어그램이다.

 

🟥 DB 모델링의 주요 개념

• 엔티티(Entity) : 업무의 관심 대상이 되는 정보를 가지고 있거나 그에 대한 정보를 관리할 필요가 있는 유형, 무형의 사물(개체)를 말한다. (ex. 학생) 엔티티를 통해 DB의 테이블이 만들어진다.
  업무의 관심 대상이 아닌 정보를 가진 엔티티를 생성할 수는 있으나 필요하지 않기 때문에 무의미하다.
  • 엔티티의 종류 
    • 유형 엔티티: 물리적으로 형태가 존재하는 엔티티를 의미한다. (ex. 학생, 강사, 고객, 사원, 교수 등)
    • 무형 엔티티: 물리적으로 형태가 존재하지 않는 엔티티를 의미한다. (ex. 생산계획, 부서, 직급 등)
    • 문서 엔티티: 문서에 대한 엔티티를 의미한다. (ex. 전표, 거래명세서, 주문서 등)
    • 이력 엔티티: 이력이 담긴 엔티티를 의미한다. (ex. 입고 이력, 출고 이력, 로그인 이력 등)
    • 코드 엔티티: 각종 코드가 담긴 엔티티를 의미한다. (ex. 국가 코드, 전화번호 코드 등)
  • 엔티티의 조건
    • 업무의 관심 대상이 되는 사물이어야 한다.
    • 두 개 이상의 인스턴스를 소유하여야 한다.
    • 마땅한 속성을 소유해야 한다.
• 속성(Attribute) : 엔티티에서 관리해야 할 관심이 있는 최소 단위 정보 항목을 말하며, 엔티티는 하나 이상의 속성을 포함한다. (ex. 학번, 이름, 주소, 전공 등) 속성을 통해 DB의 컬럼이 만들어진다.
  • 속성의 종류
    • 기본 속성: 이름, 주소, 전공 등 눈에 보이는 속성을 의미한다.
      • 유도 속성: 다른 값들에 의해 유도될 수 있는 속성을 의미한다. 예를 들어 각 과목의 점수에 따른 평균 점수의 경우 기본적인 값을 가진 속성(과목의 점수)에 의해 값이 변경될 수 있기 때문에 유도 속성이다.
      • 설계 속성: 눈에 보이지는 않으나 프로그램을 효과적으로 구성하기 위해서 강제적으로 추가하는 속성이다. (ex. 사원 번호, 부서 번호 등)
  • 속성 명명 규칙
    • 속성의 의미가 명확하게 드러나도록 작성하여야 한다.
    • 해당 업무에서 사용하는 이름을 부여하여야 한다.
    • 수식어나 소유격을 사용하여 서술식으로 작성되어서는 안 되고, 약어 또한 사용해서는 안 된다.
    • 엔티티에서 중복되지 않고 유일하게 식별 가능하도록 저장하여야 한다.
• 인스턴스(Instance) : 엔티티의 속성이 실제로 구성된 하나의 값을 말한다. 인스턴스를 통해 DB의 행(row)이 만들어진다.
• 관계(Relationship) : 두 엔티티 사이의 관계를 나타낸다.
• 카디널리티(Cardinality) : 각 엔티티에 속해 있는 인스턴스들이 수적으로 어떤 관계에 있는지를 나타낸다.
  • 카디널리티의 종류
    • 1:1 관계: X에 속하는 한 개체가 Y에도 속하는 한 개체에만 연결되고, Y에 속하는 한 개체도 X에 속하는 한 개체에만 연결되는 관계이다. (ex. 학과장-학과) 해당 관계가 나타나는 경우 두 엔티티를 따로 설계한 논리적인 이유가 존재하지 않는다면 하나의 엔티티로 합쳐야 한다.
    • 1:N 관계: X에 속하는 한 개체는 Y에 속하는 여러 개체에 연결되며, Y에 속하는 한 개체는 X에 속하는 한 개체와 연결되는 관계이다. (ex. 교수-수업과목) 가장 많고 기본적인 관계이다.
    • M:N 관계: X에 속하는 한 개체는 Y에 속하는 여러 개체와 연결될 수 있으며, Y에 속하는 한 개체도 X에 속하는 여러 개체와 연결될 수 있는 관계이다. (ex. 학생-수강과목)
      개념적으로는 존재하지만 실제 DB로는 나타낼 수 없는 관계이며 미완성된 모습이기 때문에 해당 관계가 나타나는 경우 두 엔티티 사이에 릴레이션 엔티티를 추가하여 해소햐여야 한다.
• 주식별자(Primary Identifier) : 엔티티 내 각 인스턴스를 구별하는 기준이 되는 속성이다. 주식별자를 통해 DB의 기본키가 생성된다.
• 외래식별자(Foreign Identifier) : 관계가 있는 엔티티 간에서 연결 고리 역할을 하는 속성이다. 외래식별자를 통해 DB의 외래키가 생성된다.

 

🟧 DB 설계 단계

• 개념적 설계: 요구 분석 단계에서 정의된 핵심 개체와 그들 간의 관계를 바탕으로 ERD를 생성하는 단계로 1차적으로 엔티티, 속성, 인스턴스 등을 생성한다.
• 논리적 설계: 개념적 설계에서 1차적으로 추상화된 데이터를 구체화하여 개체, 속성을 테이블화하고 상세화하는 과정으로 논리적으로 문제가 없도록 하는 과정이다.
• 물리적 설계: 논리적 설계 단계에서 표현된 데이터(ERD)를 가지고 DB 구현 직전에 실제 컴퓨터 저장 장치에 어떻게 표현할 것인지 결정한다. 데이터를 관계형 데이터베이스로 전환하는 과정이다.

 

🟨 DB 모델링 툴 활용

아래 사이트에서 모델링 툴 활용이 가능하다.

https://www.erdcloud.com/

해당 사이트를 사용하는 경우 팀 단위의 DB 모델링 수행이 가능하며, 모델링한 것을 그림 또는 sql 파일로 내보내는 것도 가능하다. sql 파일로 내보내는 경우 물리적 설계까지 완료했다면 Oracle에서 별도의 작성 없이 해당 파일을 이용하여 테이블 생성이 가능하다.

 

Chapter 02. 개념적 모델링

🟥 Part 1. Entity 도출하기

• 엔티티 도출: 업무 분석 단계 이후 분석 자료 문서를 참고하여 필요한 엔티티, 속성 등을 분석, 식별한다
• 엔티티 도출 과정: 정해진 공식은 없으나 경험이 없으면 다음의 과정을 거쳐 엔티티 기술서를 작성한다.
  1. 엔티티 후보 풀과 엔티티 리스트를 그린다.
  2. 분석 대상 문서를 보고 명사를 찾아 표시한다.
  3. 명사 하나하나를 속성인지 엔티티인지 구분한다.
  4. 중복된 명사나 유사한 의미의 명사는 하나로 정리한다.
  5. 엔티티 후보 풀에 있는 명사들을 검토한다.
  6. 도출된 엔티티에 대하여 구축될 시스템에서 데이터를 관리할 필요가 있는지 판단한다.
• 엔티티 기술서 작성 시 관련 속성 중 주식별자가 되는 속성에는 밑줄을 그어 표시한다.

 

🟧 Part 2. ER-Diagram

• ERD(Entity-Relationship Diagram): '개체 관계도'라고도 불리며 요구 분석 사항에서 얻은 엔티티와 속성들을 그림으로 그려 관계를 도출한 것이다.
  
  

  

• ERD 표기법
  ✅ 식별자 
  • 주식별자(Primary Identifier): 엔티티에 소속된 인스턴스들을 구별하는 기준 역할을 하는 속성으로 유일성, 최소성, 불변성, 존재성의 특징을 가진다.
    🔹 유일성: 값이 유일해야 하며, 중복되어서는 안 된다.
    🔹 최소성: 유일성을 만족하는 전제 하에 주식별자가 되는 속성의 수가 최소가 되어야 한다.
    🔹 불변성: 변할 수 있는 가능성이 있는 속성의 경우 주식별자로 사용해서는 안 된다.
    🔹 존재성: 값이 존재해야 한다. (NULL이면 안 된다.)
    
    주식별자는 하나가 아닌 여러 속성일 수 있으며, 이를 복합키라고 한다.
    엔티티의 속성 중 주식별자 속성이 없다면 새로운 속성을 만들어 주어야 하며 이를 인위적 주식별자라고 한다.
  • 외래 식별자(Foreign Identifier): 관계가 있는 두 엔티티를 부모, 자식 엔티티로 구분한 후 부모의 주식별자와 공통 속성이 자식에게도 존재하면 해당 속성을 외래 식별자로 지정한다.
    자식 엔티티에 부모 엔티티 주식별자 공통 속성이 없을 경우 자식에게 속성을 추가한 후 외래 식별자로 지정한다.
  ✅ 관계
  • 엔티티 간의 부모-자식 관계: 상호 관계가 있는 두 엔티티 중에서 어느 쪽의 정보가 먼저 생성이 되는가에 따라 결정되며, 부모 엔티티의 정보가 있어야 존재할 수 있는 것이 자식 엔티티이다.
    엔티티 간의 관계가 N:1인 경우 1인 쪽이 무조건 부모 엔티티가 된다.
  • 카디널리티: 두 개의 엔티티 간의 관계에서 엔티티에 속해 있는 인스턴스를 수적으로 표현한 것으로, 인스턴스가 1개와 대응된다면 '|'로 표시하고, 다수와 대응된다면 '까마귀발 표기법'으로 표시한다.
  • 참여도: 참여도에는 필수(mandatory)와 선택(optional) 두 가지가 존재하며 각각 '|'와 'O'로 표시한다.
    어떤 기준이 되는 엔티티가 있을 때 반드시 대응되는 엔티티가 존재해야 한다면 필수이며, 존재할 수도 있고, 존재하지 않을 수도 있다면 선택이다.
    ex. 사원은 무조건 직급을 가지기 때문에 필수이며, 학과에는 학생이 존재하지 않을 수도 있기 때문에 선택이다.
  • 카디널리티와 참여도에 따른 관계의 종류
    

    

    
    Symbol에서 앞부분이 관계의 참여도를 나타내며, 뒷부분이 카디널리티를 나타낸다.
  ✅ 식별-비식별 관계
  • 식별 관계(Identifying Relationship) : 1:N 관계에서 외래 식별자가 자식 엔티티의 주식별자의 일부가 되는 관계이다.
    외래 식별자이자 주식별자이기 때문에 PFK로 표기하며, 실선으로 관계를 표시한다.
  • 비식별 관계(Non-Identifying Relationship): 1:N 관계에서 외래 식별자가 자식 엔티티의 주식별자 역할을 하지 못하고 단순히 새로운 속성으로 추가되는 관계이다.
    단지 외래 식별자의 역할만 하기 때문에 FK로 표시하며, 점선으로 관계를 표시한다.
  • 식별 관계와 비식별 관계 파악 프로세스
    1. 엔티티 간의 부모-자식 관계를 판별한다.
    2. 자식 엔티티로 넘어온 부모 엔티티의 주식별자(속성)을 일반 속성으로 배치하며 비식별 관계로 먼저 설정해 둔다.
    3. 새로운 속성이 들어가 있는 상태에서 임의로 데이터를 삽입한다.
    4. 삽입 후 유일성 위배 등의 문제가 생기지 않는다면 그대로 비식별 관계를 유지하고, 문제가 생기는 경우 식별 관계로 변경한다.
    ☝ Tip. 자식 엔티티의 주식별자가 시퀀스에 의해 만들어지는 경우에는 무조건 비식별 관계가 가능하다. 하지만 해당 문장의 역은 성립하지 않는다.

 

Chapter 03. 논리적 모델링

• 논리적 모델링 단계
  개념적 모델링(ERD) → 정규화 (테이블 상세화)
• 정규화 (DB normalization) : 관계형 데이터베이스에서 데이터를 구조화하는 작업이다.
  정규화를 진행함에 따라 테이블이 더 여러 개로 쪼개지는 것이기 때문에 과도한 정규화가 진행되는 경우 데이터를 불러올 때 여러 번의 JOIN이 필요하고, 가져오는 시간이 증가할 수 있다.
  순서를 건너뛰는 것이 가능하지만, 차례대로 진행되어야 한다. 제1정규화를 건너뛰고 제2정규화 진행 후 제3정규화를 진행할 수 있으나, 제3정규화 진행 후 제2정규화로 되돌아올 수는 없다. 정규화 순서를 건너뛰는 경우에는 해당 정규화가 진행되어 있는 경우에만 가능하며, 건너뛰는 순서의 정규화가 필요한 경우에는 무시하고 건너뛸 수 없다.

🟥 정규화의 목적

• 데이터 중복 방지
• 데이터를 보다 효율적으로 저장
• 삽입, 삭제, 갱신 이상의 발생 가능성을 줄이기 위함이다.

🟧 이상 (Anomaly)

• 삽입 이상: 불필요한 정보를 함께 저장하지 않고는 어떤 정보를 저장하는 것이 불가능한 경우를 말한다.
  예를 들어 제품에 대한 정보를 저장하고자 할 때 같은 엔티티에 주문에 대한 속성이 존재하는 경우 주문에 대한 속성의 값까지 삽입해 주어야 한다.
• 갱신 이상: 반복된 데이터의 경우 데이터를 수정하고자 할 때 반복된 데이터들을 모두 수정해 주어야 하는데 해당 경우를 의미한다.
  예를 들어 제품 정보와 주문 정보가 하나의 엔티티에 존재하는 경우 제품 정보를 업데이트하면 해당 제품을 주문한 모든 경우에 대하여 수정이 필요하다.
• 삭제 이상: 유용한 정보를 함께 삭제하지 않고는 어떤 정보를 삭제하는 것이 불가능한 경우를 의미한다.
  예를 들어 주문이 취소되는 경우 주문에 대한 정보만 삭제하여야 하는데, 제품에 대한 정보가 동일한 엔티티에 존재하는 경우 제품에 대한 정보까지 삭제하여야 하고, 삭제되는 제품에 대한 주문이 해당 주문 한 건인 경우 제품의 정보는 사라지게 된다.

🟨 제 1 정규화

• 엔티티에서 하나의 속성이 복수의 값을 갖도록 설계되었을 때 하나의 속성이 단일 값을 갖도록 하는 것이다.
  예를 들어 사번이 주 식별자인 사원의 취미 엔티티의 경우 한 명의 사원이 여러 개의 취미를 가질 수 있기 때문에 주 식별자가 유일성에 위배된다. 따라서 테이블을 분리해 주어야 한다.
  
  ☝ Tip. 정규화 진행 중 따로 분리되는 엔티티가 항상 부모 엔티티가 된다.

🟩 제 2 정규화

• 주 식별자가 아닌 속성 중에서 주 식별자 전체가 아닌 일부 속성에 종속된 속성을 찾아 제거하는 것이다.
  주 식별자의 일부 속성에만 종속된 속성을 찾아 제거하는 것이기 때문에 제 2 정규화를 진행하기 위해서는 주 식별자가 2개 이상이어야 한다.
  
   ☝ Tip. 제 2 정규화를 진행할 때 엔티티가 분리되면서 기존 엔티티에서 주 식별자 하나를 가지고 분리된다. 따라서 기존 엔티티는 주 식별자가 하나가 부족한 상태가 되고, 이를 맞춰 주어야 한다. 따라서 기존 엔티티와 분리된 엔티티가 식별 관계를 이루는 경우가 많다.

 

🟦 제 3 정규화

• 주 식별자가 아닌 일반 속성들 중에서 종속 관계에 있는 속성을 찾아 제거하는 것이다.
  
  ☝ Tip. 제 3 정규화는 주 식별자인 속성을 분리하는 것이 아니라 일반 속성들 중 종속 관계에 있는 속성들을 찾는 것이기 때문에 주 식별자의 개수가 유지된다. 따라서 기존 엔티티와 분리된 엔티티가 비식별 관계를 이루는 경우가 많다.

 

Chaper 04. 물리적 모델링

• 물리적 모델링이란 논리적 설계의 산출물인 ERD의 요소들을 관계형 데이터베이스의 요소들로 전환하는 것을 말한다.
  논리적 DB 설계 (데이터 모델링): ERD는 어떤 데이터베이스를 사용하여도 적용 가능하기 때문에 DBMS의 종류/제품에 상관 없이 진행 가능하다.
  물리적 DB 설계: 특정 DBMS를 전제로 하여 해당 DBMS의 특성을 고려하여 진행하여야 한다.
• 물리적 모델링 과정
  

  

  
  
  
• ERD 요소 전환

  

• 반정규화(De-Nomalization): 정규화 작업이 완료된 후 데이터 물리 모델링 과정 중 시스템의 성능 향상, 개발 과정의 편의성, 운영의 단순화를 목적으로 하여 진행한다.
  정규화를 진행하는 경우 테이블을 여러 개로 나누기 때문에 데이터를 가지고 오려고 하는 경우 join을 여러 번 해야 하기 때문에 성능이 하락할 수 있다. 이를 보완하기 위하여 반정규화를 진행한다.
  반정규화를 하는 경우 중복을 감수하여 데이터베이스의 성능(검색 속도 등)을 향상시킨다.
  정규화를 통한 데이터 무결성 유지도 중요하지만, 다수 사용자가 동시 이용하는 환경에서 일정 성능을 유지하는 것도 매우 중요하기 때문에 반정규화가 중요하다.
• 반정규화 사례
  🟥 엔티티의 통합
  
  • 항상 혹은 대부분 조인에 의한 검색을 하고 검색이 빈번히 이루어지는 두 엔티티를 대상으로 한다.
    ex. 주문, 주문 내역은 항상 join을 이용하여 검색하기 때문에 두 테이블을 합치는 것이 성능에 유리하다.
  🟧 수직 분할에 의한 반정규화
  • 엔티티의 튜플 수 및 속성의 수가 매우 많고, 엔티티의 속성들이 그룹화되어 각 그룹이 특정 부서 혹은 응용프로그램에 의해서만 사용될 경우 반정규화를 진행한다.
    ex. 부서에 따라 사용되는 속성이 구분되는 경우 불러오고자 하는 부서에 맞지 않는 데이터는 검색하지 않아도 되기 때문에 테이블을 두 개로 나눌 수 있다. 이때 주 식별자는 변경되지 않고, 두 테이블 모두 하나의 주 식별자로 구분되는 관계이기 때문에 1:1 관계이다.
  🟨 수평 분할에 의한 반정규화
  • 한 테이블 내에서 튜플의 조회 빈도에 따라 엔티티를 분할한다.
    ex. 도서 정보 테이블에는 매우 많은 도서의 정보가 포함되어 있는데, 이때 자주 검색되는 상위 20% 도서 목록이 정해져 있다. 해당 경우 자주 검색되는 도서 목록의 테이블을 따로 분리하는 경우 많은 데이터를 다 검색하지 않아도 되기 때문에 검색 속도가 향상된다.
  🟩 속성의 중복에 의한 반정규화
  • JOIN을 이용하여 가져다 쓰는 속성의 수가 적을 경우 엔티티의 통합은 비효율적이기 때문에 속성을 중복하여 저장한다.
    ex. 주문 테이블 중 주문업체 속성만을 JOIN하여 사용하는 경우 다른 속성까지 모두 합치는 것은 불필요하게 데이터 양을 늘릴 수 있기 때문에 주문업체 속성만 두 테이블에 중복하여 저장한다.
  🟦 관계에 대한 반정규화
  • 속성의 중복에 의한 반정규화와 비슷하나 차이가 있다면 중복을 하는 속성이 다른 엔티티와의 관계를 맺어 주기 위한 외래키로 사용된다. 이를 통해 조인해야 할 엔티티를 줄여 준다.
    ex. 조인 경로가 여러 개인 경우 첫 번째 테이블과 마지막 테이블을 연결해 준다.