[DB][MySQL][개념] 🔥 데이터베이스 모델링 끝판왕! 개념부터 물리적 설계까지 한 번에 마스터 💥

김상욱·2024년 9월 16일

데이터베이스 모델링 📊

데이터베이스 모델링이란 정보화 시스템을 구축하기 위해 현실 세계의 데이터를 체계적으로 저장하고 관리하는 방법을 계획하는 과정입니다. 주로 데이터를 분석하고 설계하며, 이 과정은 크게 요구 사항 분석, 설계, 구현 단계로 나뉩니다. 각각의 단계는 데이터베이스의 구조와 성능에 중대한 영향을 미치므로 꼼꼼한 계획이 필요합니다. 이제 각 단계를 차근차근 살펴볼까요? 😊

1. 요구 사항 수집 및 분석 🔍

첫 단계는 시스템에서 필요한 데이터를 파악하고 분석하는 과정입니다. 현실 세계의 정보와 사용자의 요구 사항을 기반으로 데이터베이스에 담겨야 할 데이터를 정리하는 작업이에요.

사용자 식별: 시스템을 사용할 주요 사용자를 파악합니다. 누가 데이터를 생성하고, 수정하며, 조회할 것인지 알아야 시스템 설계에 반영할 수 있습니다.
데이터베이스 용도 식별: 데이터베이스가 시스템에서 어떻게 사용될지 정의하는 단계입니다. 예를 들어, 사용자가 데이터를 단순 조회만 할 것인지, 데이터를 입력하고 수정할 필요도 있는지 분석합니다.
사용자 요구 사항 수집 및 명세: 사용자 인터뷰, 설문 조사 등으로 필요한 데이터를 수집하고, 이를 바탕으로 명세서를 작성해 설계 단계에서 참고할 수 있도록 합니다.

2. 설계 🛠

설계 단계는 크게 개념적, 논리적, 물리적 설계로 나뉩니다. 각 설계는 데이터를 어떻게 구조화할지 구체화하는 과정입니다. 하나씩 살펴볼까요?

2-1. 개념적 모델링 💡

이 단계에서는 데이터베이스에 포함될 중요한 개념들을 정의하고, 이들을 어떻게 표현할지를 구상합니다. 데이터베이스 관리 시스템(DBMS)에 대한 구체적인 사항은 고려하지 않고, 현실 세계의 데이터를 어떻게 구조화할 것인지를 설계하는 것이 목표입니다.

핵심 Entity 도출: 현실 세계에서 중요한 개체들을 식별합니다. 예를 들어, 학교 시스템이라면 학생, 강사, 과목 등이 주요 개체가 될 수 있어요.
ERD 작성: ERD(Entity Relationship Diagram)는 엔티티, 관계, 속성을 시각적으로 나타낸 다이어그램입니다. 이 다이어그램을 통해 데이터 구조를 개념적으로 표현할 수 있습니다.
DBMS 결정: 마지막으로, 시스템 요구 사항에 맞는 DBMS를 결정합니다. 예를 들어, MySQL, PostgreSQL, Oracle 등이 사용될 수 있습니다.

2-2. 논리적 모델링 🧩

논리적 모델링은 개념적 모델링에서 구체화된 개념들을 실제 데이터베이스 관리 시스템에서 사용할 수 있도록 구체화하는 단계입니다.

ERD 매핑: 개념적으로 작성된 ERD를 특정 관계형 데이터베이스 시스템에 맞게 변환합니다. 예를 들어, 엔티티를 테이블로, 엔티티 간의 관계를 외래 키로 변환합니다.
상세 속성 정의: 각 테이블의 컬럼과 속성, 데이터 타입 등을 구체적으로 정의합니다. 컬럼 제약 조건, 기본값, 인덱스 설정 등도 포함됩니다.
정규화: 데이터를 중복 없이 저장하고 무결성을 유지하기 위해 테이블을 정규화합니다. 정규화는 데이터를 여러 테이블로 분리해 중복을 최소화하는 작업이에요.

2-3. 물리적 모델링 🏗

이 단계는 실제 데이터베이스를 구축하기 위한 물리적 설계입니다. DBMS의 특성에 따라 데이터베이스를 최적화하는 과정이 포함됩니다.

DB 개체 정의: 테이블, 인덱스, 뷰, 저장 프로시저 등의 데이터베이스 개체들을 설계하고 생성합니다.
테이블 및 인덱스 설계: 성능을 고려한 테이블의 물리적 저장 구조를 설계하고, 적절한 인덱스를 정의해 데이터베이스 성능을 최적화합니다.

3. 데이터베이스 구현 🚀

마지막 단계는 설계된 데이터베이스 모델을 실제 시스템에 구현하는 것입니다. 이제 본격적으로 데이터베이스가 구축되고 운영되기 시작해요!

쿼리 작성: 설계된 테이블과 인덱스, 트리거, 뷰 등을 생성한 후, 데이터를 삽입하고 쿼리를 작성합니다.
성능 최적화: 데이터베이스 성능을 높이기 위해 인덱스를 최적화하고, 성능 문제가 발생하는지 점검합니다.

개념적 데이터베이스 모델링 🎨

개념적 데이터베이스 모델링은 업무 분석 단계에서 얻어진 정보를 바탕으로 Entity(실체)를 추출하고, 각 Entity의 속성을 구성하며, Entity 간의 관계를 정의하여 ER-Diagram(ERD)를 작성하는 단계입니다. 이는 현실 세계의 데이터를 시각적으로 표현하여 데이터베이스 구조를 명확히 이해하고 설계할 수 있게 합니다. 🌐

1. 사용자 부문의 처리 현상 분석 🔍

처리 현상 분석은 실제 업무에서 어떤 데이터가 처리되는지를 분석하는 단계입니다. 이 분석은 시스템에서 사용자가 어떤 데이터를 다루고 처리하는지를 명확히 파악하는 중요한 출발점입니다.

예시: 온라인 쇼핑몰의 경우, 고객이 주문하고 결제를 완료하는 과정에서 발생하는 주문 정보, 결제 정보 등이 필요한 데이터입니다.

2. 실체와 관계 파악하여 ERD 작성 🧩

다음으로, 분석된 데이터를 바탕으로 시스템에서 중요한 실체(Entity)와 이들 간의 관계(Relationship)를 파악합니다. 이후, 각 실체와 관계를 시각적으로 나타낸 ERD를 작성합니다.

예시: 고객은 여러 주문을 할 수 있고, 하나의 주문에는 여러 상품이 포함될 수 있습니다. 이러한 관계를 ERD에 표현해 실체 간의 관계를 명확히 할 수 있습니다. 📈

3. 실체에 대한 상세 정의 📝

실체(Entity)를 구체적으로 정의하는 과정입니다. 이 단계에서는 실체가 데이터베이스에서 어떤 역할을 하고, 어떤 특성을 가질지 설명합니다.

예시: '고객' 실체에는 고객 ID, 고객 이름, 주소 등의 속성이 포함됩니다. 이러한 속성을 정의하여 데이터베이스 내에서 실체의 역할을 구체화합니다.

4. 식별자 정의 및 업무 규칙 설정 🔐

각 실체를 고유하게 구분할 수 있는 식별자(Identifier)를 정의합니다. 이 식별자는 데이터베이스 내에서 실체를 구분하는 중요한 속성이며, 업무 규칙에 따라 관리됩니다.

예시: '고객 ID'는 고객을 고유하게 식별하는 식별자입니다. 이 값은 중복될 수 없다는 규칙을 설정해 데이터베이스의 무결성을 유지합니다.

5. 실체별 속성 상세화 ✍️

실체가 가지는 속성(Attribute)을 구체적으로 정의합니다. 각 실체의 속성을 나열하고, 이 속성들의 데이터 타입과 제약 조건을 설정합니다.

예시: '고객' 실체의 속성으로는 고객 이름, 이메일, 전화번호가 있으며, 각각의 데이터 타입은 문자열과 숫자 형식으로 정의됩니다.

6. 속성 및 영역 상세 정의 📐

속성의 값이 가져야 할 범위(영역)를 정의하는 단계입니다. 속성 값이 가질 수 있는 최소값, 최대값 또는 특정 형식 등을 지정하여 데이터의 일관성을 유지합니다.

예시: 고객의 이메일 속성은 '@' 문자를 포함해야 하며, 전화번호는 10자리 숫자로 제한하는 방식으로 정의됩니다.

7. 속성에 대한 업무 규칙 정의 📜

각 속성에 대한 비즈니스 규칙을 설정하는 단계입니다. 이는 속성의 값에 대한 조건이나 값이 변경되는 규칙 등을 설정하여 데이터의 정확성과 무결성을 유지합니다.

예시: '주문' 실체에서는 주문 상태가 '처리 중', '배송 중', '완료' 중 하나로 제한되며, 주문 금액은 음수가 될 수 없다는 규칙을 설정할 수 있습니다.

8. 사용자와 함께 모델 검토 👥

마지막으로, 설계된 ERD와 속성, 규칙 등을 사용자와 함께 검토하는 단계입니다. 사용자의 요구 사항이 정확히 반영되었는지 확인하고, 누락된 부분이 없는지 검토합니다. 피드백을 반영해 모델을 수정하거나 보완할 수 있습니다.

예시: 온라인 쇼핑몰 시스템의 경우, 고객 서비스 담당자와 함께 데이터 모델을 검토하여 실무에 필요한 모든 데이터가 잘 정의되었는지 확인합니다.

1. 개체 (Entity) 🧑‍💼

개체(Entity)는 현실 세계에서 데이터로 관리되어야 할 사람, 사물, 장소, 사건 등을 의미해요. 이는 데이터베이스에 저장되고 관리되는 중요한 객체로, 예를 들어 고객, 제품, 주문 등이 이에 해당합니다.

Entity 찾는 법:
- 영속적으로 존재하는 것: 한 번 생성되면 지속적으로 관리되어야 할 대상이에요. 일시적인 것이 아니라, 장기간 존재하거나 추적할 필요가 있는 객체입니다.
  - 예: 고객, 제품, 주문 등은 시간이 지나도 꾸준히 관리되어야 하는 데이터입니다.
- 고유하게 식별할 수 있는 데이터 요소: 각 개체는 고유하게 식별할 수 있는 속성을 가져야 합니다. 이를 통해 같은 유형의 여러 개체를 구분할 수 있죠.
  - 예: 고객 ID, 제품 번호, 주문 번호 등이 개체를 고유하게 식별하는 속성입니다.
- 반드시 속성을 가져야 함: 개체는 반드시 하나 이상의 속성(Attribute)을 가져야 합니다. 속성은 개체에 대한 구체적인 정보를 나타내죠.
  - 예: '고객' 개체는 고객 이름, 전화번호 등의 속성을 가질 수 있습니다.
- 명사적 표현: 개체는 주로 명사로 표현돼요. 시스템 내에서 중요한 역할을 하는 객체이기 때문에, 명사형 단어로 표현하는 것이 일반적입니다.
  - 예: 고객(Customer), 주문(Order), 제품(Product) 등.

2. 속성 (Attribute) 🏷️

속성(Attribute)은 개체에 관한 구체적인 정보를 저장하는 요소입니다. 속성은 개체의 성질, 상태, 특성 등을 나타내며, 최종적으로는 테이블의 컬럼(Column)으로 사용됩니다.

속성의 특징:
- 저장할 필요가 있는 정보: 속성은 데이터베이스에 저장할 가치가 있는 정보를 담습니다.
  - 예: '고객' 개체의 속성으로는 고객 이름, 주소, 전화번호 등이 있습니다.
- 개체의 특성: 속성은 개체가 가진 다양한 정보를 나타내며, 이를 통해 개체의 특징을 파악할 수 있어요.
  - 예: 제품의 가격, 재고 상태, 주문 날짜 등이 속성으로 정의될 수 있습니다.
- 속성의 개수: 개체에 포함되는 속성은 10개 내외로 유지하는 것이 바람직합니다. 너무 많으면 관리가 복잡해질 수 있죠.
속성의 유형:
- 기초속성: 개체가 원래 가지고 있는 기본 속성입니다.
  - 예: '고객'의 이름, 주소, 전화번호.
- 추출속성: 기초 속성을 바탕으로 계산된 속성입니다.
  - 예: '주문'에서 상품 가격과 수량을 곱해 계산된 총 주문 금액.
- 설계속성: 효율성을 높이기 위해 추가된 속성입니다.
  - 예: 주문 상태를 나타내는 '주문 상태' 속성.

3. 식별자 (Identifier) 🆔

식별자(Identifier)는 데이터베이스에서 하나의 개체 내의 인스턴스를 유일하게 구분할 수 있는 속성입니다. 이를 통해 각 인스턴스를 정확히 식별할 수 있습니다.

4. 후보키 (Candidate Key) 🎯

후보키(Candidate Key)는 개체 내에서 각 인스턴스를 고유하게 구분할 수 있는 속성 또는 속성 그룹을 말해요. 그 중 하나는 기본키(Primary Key)로 선택됩니다.

특징: 중복되지 않고 각 인스턴스를 고유하게 구분할 수 있어야 합니다.
예시: 고객 ID와 이메일 주소가 고유할 경우, 둘 다 후보키가 될 수 있습니다.

5. 기본키 (Primary Key) 🔑

기본키(Primary Key)는 후보키 중에서 개체 내 인스턴스를 고유하게 식별하는 가장 적합한 키입니다. 각 레코드를 유일하게 구분하며, 데이터베이스의 무결성을 유지하는 데 중요한 역할을 해요.

기본키 선정 기준:
- 대표성: 실체를 대표할 수 있어야 합니다.
- 짧은 길이: 데이터베이스 성능을 고려해 가능한 짧은 길이로 설정하는 것이 좋습니다.
예시: 고객 ID는 고객을 고유하게 식별할 수 있는 대표적인 기본키입니다.

6. 대체키 (Alternate Key) 🔄

대체키(Alternate Key)는 후보키 중 기본키로 선택되지 않은 속성입니다. 여전히 각 인스턴스를 고유하게 구분할 수 있는 키예요.

예시: 고객 ID가 기본키로 선택되면, 이메일 주소는 대체키가 될 수 있습니다.

7. 복합키 (Composite Key) 🔗

복합키(Composite Key)는 둘 이상의 속성을 결합해 기본키로 사용하는 경우입니다. 여러 속성의 조합을 통해 유일한 식별자를 만드는 방식이죠.

특징: 개별 속성은 고유하지 않지만, 여러 속성을 합치면 고유해질 때 사용됩니다.
예시: 주문 번호와 제품 ID를 결합해 복합키로 설정할 수 있습니다.

8. 대리키 (Surrogate Key) 🧩

대리키(Surrogate Key)는 원래 데이터에 존재하지 않지만, 성능 최적화를 위해 추가된 인위적인 키입니다. 주로 데이터베이스에서 자동으로 생성되는 값으로 사용됩니다.

특징: 의미 없이 식별 목적으로만 사용됩니다.
예시: 자동 생성되는 주문 ID는 대리키로 사용될 수 있습니다.

1. 관계 (Relationship) 🔗

관계(Relationship)는 두 개 이상의 Entity(개체) 간의 업무적인 연관성을 의미합니다. 예를 들어, '고객'과 '주문'은 서로 관련이 있으며, 고객이 주문을 한다는 사실이 관계로 표현될 수 있어요. 관계는 실체들이 서로 어떻게 상호작용하고, 어떤 연관성을 가지는지를 명확히 정의하는 중요한 요소입니다.

2. 관계 분석 (Relationship Analysis) 🔍

관계 분석에서는 두 Entity 간의 연관성을 구체적으로 파악하고, 시스템의 현재와 미래까지 고려한 분석이 필요합니다. 시스템이 확장되더라도 이 관계가 유효한지 확인하는 것이 중요해요.

각 Entity 간 존재 여부 결정: 두 실체가 실제로 관계를 가지고 있는지, 또는 미래에 어떤 방식으로 관계를 가질 수 있는지 분석합니다.
- 예: '고객'은 '주문'을 할 수 있고, '주문'은 '고객'에 의해 발생하므로 두 Entity 간에 관계가 존재합니다.
미래 사용 고려: 현재뿐만 아니라, 시스템이 확장될 때에도 관계가 유효한지 검토합니다. 미래의 기능이나 요구사항을 염두에 두고 분석해야 합니다.

3. E-R Diagram으로 관계 설정하는 순서 📊

E-R Diagram(ERD)은 Entity와 그들 간의 관계를 시각적으로 표현하는 다이어그램이에요. Entity 간 관계를 설정할 때는 다음의 순서를 따릅니다.

1. 관계 있는 두 실체를 실선으로 연결하고 관계를 부여 🖇️

먼저, 두 개의 Entity가 어떤 관계에 있는지를 나타내기 위해 실선으로 연결하고, 그 관계에 대해 라벨을 붙여 설명합니다.

예시: '고객'과 '주문'을 실선으로 연결하고, "고객은 주문한다"라는 관계를 부여할 수 있어요.

2. 관계 차수(Cardinality)를 표현 🔢

차수성(Cardinality)은 하나의 Entity 인스턴스가 다른 Entity 인스턴스와 몇 개의 관계를 맺을 수 있는지 나타냅니다.

차수성의 종류:
- 1:1 (일대일): 두 실체의 인스턴스가 서로 하나씩만 대응되는 경우.
  - 예: '직원'과 '사원증'의 관계는 1:1일 수 있습니다. 각 직원은 하나의 사원증만 가지고, 사원증은 한 직원에게만 대응됩니다.
- 1:N (일대다): 한 실체의 인스턴스가 다른 실체의 여러 인스턴스와 연결될 수 있는 경우.
  - 예: '고객'과 '주문' 간의 관계는 1:N입니다. 한 고객은 여러 주문을 할 수 있지만, 각 주문은 한 고객에만 속합니다.
- N:N (다대다): 두 실체가 서로 여러 개의 인스턴스와 관계를 맺을 수 있는 경우.
  - 예: '학생'과 '강의' 간의 관계는 N:N입니다. 여러 학생이 여러 강의를 들을 수 있고, 한 강의에는 여러 학생이 참여할 수 있습니다.

3. 선택성(Optionality)을 표시 ✅

선택성(Optionality)은 두 Entity 간의 관계가 필수인지, 선택적인지 나타냅니다.

필수(Mandatory): 관계가 반드시 성립해야 하는 경우.
- 예: '주문'은 반드시 '고객'에 의해 생성되어야 하므로, 주문과 고객 간의 관계는 필수입니다.
선택적(Optional): 관계가 성립되지 않을 수도 있는 경우.
- 예: '고객'은 반드시 주문을 할 필요가 없으므로, 고객과 주문 간의 관계는 선택적일 수 있습니다.

선택성 설정 방법:

업무 규칙에 따라 선택성을 먼저 판단합니다.
관계가 항상 성립해야 하는지, 또는 성립되지 않아도 되는 경우가 있는지 고려합니다.
각 Entity에서 양쪽 방향으로 관계를 조사하여 선택성을 설정합니다.
- 예: 고객은 주문을 하지 않아도 되지만, 주문은 반드시 고객에 의해 생성되어야 하는 경우, 고객과 주문 간의 선택성은 다를 수 있습니다.

논리적 데이터베이스 모델링 🛠️

논리적 데이터베이스 모델링은 개념적 데이터베이스 모델링에서 정의된 ER-Diagram을 실제 관계형 데이터베이스(RDBMS)에서 구현할 수 있도록 구체화하는 과정이에요. 이 단계에서는 ERD를 테이블로 변환하고, 스키마 설계를 통해 데이터 구조를 세밀하게 다듬습니다. 또한, 데이터 정규화(Normalization)를 통해 중복을 최소화하고, 데이터베이스의 무결성을 유지합니다.

논리적 데이터베이스 모델링의 주요 개념 💡

개념적 데이터베이스 모델링에서 정의된 ER-Diagram 🗺️
- 개념적 모델링에서는 현실 세계의 실체(Entity)와 그들 간의 관계(Relationship)를 추상적으로 표현한 ERD를 작성합니다.
- ERD는 실체, 속성, 관계를 시각적으로 표현한 모델로, 논리적 모델링 단계에서는 이를 관계형 데이터베이스의 테이블로 변환합니다.
Mapping Rule 적용 🔄
- Mapping Rule은 ERD의 요소를 관계형 데이터베이스 구조에 맞게 변환하는 규칙이에요.
- 각 실체(Entity)는 테이블(Table)로, 속성(Attribute)은 컬럼(Column)으로, 관계(Relationship)는 외래키(Foreign Key)로 변환됩니다.
- 주요 Mapping Rule:
  - Entity -> Table: 실체(Entity)는 관계형 데이터베이스에서 테이블로 변환됩니다.
  - Attribute -> Column: 속성(Attribute)은 테이블의 컬럼으로 변환됩니다.
  - Relationship -> Foreign Key: 실체 간의 관계는 외래키를 통해 연결되며, 데이터의 참조 무결성을 유지하는 데 중요한 역할을 합니다.
예시:
- '고객'과 '주문'이 1:N 관계인 경우, 고객 테이블과 주문 테이블을 만들고, 주문 테이블에 고객 ID를 외래키로 추가하여 두 테이블 간의 관계를 구현할 수 있습니다.
정규화(Normalization) ⚙️
- 정규화는 데이터를 중복 없이 저장하고, 데이터 무결성을 유지하기 위한 과정입니다. 데이터베이스 성능을 개선하고 비일관성 문제를 방지하는 데 중요한 역할을 하죠.
- 정규화의 목적은 데이터 중복을 최소화하고, 데이터 변경 작업(업데이트, 삭제, 삽입)에서 발생할 수 있는 비일관성 문제를 방지하는 것입니다.
정규화 과정:
- 제1정규형(1NF): 각 컬럼은 단일 값을 가져야 해요. 여러 값을 컬럼에 저장하지 않고, 데이터는 원자성을 가져야 합니다.
- 제2정규형(2NF): 제1정규형을 만족하면서, 기본키가 아닌 모든 속성이 기본키 전체에 대해 완전 함수적 종속을 가져야 합니다.
- 제3정규형(3NF): 제2정규형을 만족하면서, 기본키가 아닌 속성들이 이행적 종속 관계에 있지 않아야 합니다.
정규화 예시:
- 1NF: '주문' 테이블에서 '제품' 컬럼에 여러 제품을 저장하는 대신, 각 주문에 대해 하나의 제품만 기록.
- 2NF: '주문' 테이블에서 '주문 번호'로 주문을 고유하게 식별하고, '고객 ID'는 해당 주문에 대해 완전하게 종속되어야 합니다.
- 3NF: '고객' 테이블에서 고객의 주소와 전화번호가 서로 종속적이지 않게 분리.
주의:
- 과도한 정규화는 데이터베이스의 성능을 저하시킬 수 있으므로, 실무에서는 적절한 수준에서 정규화를 적용하는 것이 중요해요.

기본키(Primary Key) 🔑

후보키 중에서 선정된 주 키: 후보키 중에서 한 개를 선택하여 각 레코드를 고유하게 식별하는 데 사용됩니다.
Null 값을 가질 수 없음: 기본키는 레코드를 고유하게 식별해야 하므로, 비어있는 값(Null)을 허용하지 않습니다.
중복 불가: 기본키는 각 레코드가 고유해야 하므로, 동일한 값이 중복되어 저장될 수 없습니다.

참조키, 외래키 (Foreign Key) 🔗

관계 설정을 위한 키: 두 엔터티(Entity) 간에 관계를 맺을 때, 한 엔터티의 속성(Attribute)을 다른 엔터티에서 참조하는 키로 사용됩니다.
릴레이션 간의 참조: 참조키는 릴레이션(테이블) 간의 연결을 유지하며, 데이터 무결성을 보장합니다. 예를 들어, '주문' 테이블의 고객 ID는 '고객' 테이블의 기본키를 참조하는 외래키가 될 수 있습니다.

Mapping Rule 🧩

Mapping Rule은 개념적 모델링에서 도출된 개체(Entity)와 관계(Relationship)를 실제 테이블과 컬럼으로 변환하는 규칙입니다.

단순 엔터티 -> 테이블(Table): 개체(Entity)는 관계형 데이터베이스에서 테이블로 변환됩니다.
속성(Attribute) -> 컬럼(Column): 개체 내의 속성은 데이터베이스 테이블의 컬럼으로 변환됩니다.
식별자(Identifier) -> 기본키(Primary Key): 개체의 식별자는 테이블의 기본키로 설정됩니다.
관계(Relationship) -> 외래키(Foreign Key): 개체 간의 관계는 참조키(외래키)를 통해 테이블 간의 연결로 구현됩니다.

1:1 (일대일) 관계 예시 👥

사원(Employee) 테이블

Employee_ID	Name	Department
1	John Doe	HR
2	Jane Smith	IT
3	Alice Brown	Marketing

사원증(Badge) 테이블

Badge_ID	Employee_ID (FK)	Issue_Date
101	1	2023-01-15
102	2	2023-02-01
103	3	2023-03-10

1:1 관계 설명: 각 사원(Employee)은 하나의 사원증(Badge)을 가지고 있으며, Employee_ID가 Badge 테이블에서 외래키(Foreign Key)로 사용됩니다. 즉, 각 Employee는 하나의 Badge만 가질 수 있습니다. 일대일 관계는 사원의 고유한 신분 증명과 같은 정보에 적합합니다.

1:N (일대다) 관계 예시 👤➡️📦

고객(Customer) 테이블

Customer_ID	Name	Phone
1	John Doe	010-1234-5678
2	Jane Smith	010-9876-5432
3	Alice Brown	010-5555-7777

주문(Order) 테이블

Order_ID	Customer_ID (FK)	Order_Date
1001	1	2024-01-10
1002	2	2024-01-11
1003	1	2024-01-12
1004	3	2024-01-13

1:N 관계 설명: 한 고객(Customer)은 여러 개의 주문(Order)을 할 수 있습니다. Customer_ID가 주문 테이블에서 외래키(Foreign Key)로 사용되어, 각 주문이 어떤 고객에 의해 발생했는지 명시됩니다. 이는 고객이 여러 번 구매할 수 있는 상황에 적합한 데이터 구조입니다.

M:N (다대다) 관계 예시 👩‍🎓↔️📚

학생(Student) 테이블

Student_ID	Name
1	John Doe
2	Jane Smith
3	Alice Brown

강의(Course) 테이블

Course_ID	Course_Name
101	Database Design
102	Data Structures
103	Operating Systems

학생-강의(Student_Course) 교차 테이블

Student_ID (FK)	Course_ID (FK)
1	101
1	102
2	101
3	103
3	102

M:N 관계 설명: 여러 학생(Student)이 여러 강의(Course)를 수강할 수 있습니다. 이를 구현하기 위해 교차 테이블(Student_Course)이 사용되며, 복합키(Student_ID, Course_ID)를 통해 M:N 관계가 설정됩니다. 이를 통해 학생과 강의 간의 다대다 관계를 관리하며, 강의를 듣는 여러 학생과, 여러 강의를 듣는 학생들을 쉽게 추적할 수 있습니다.

정규화(Normalization) 🛠️

정규화는 Attribute(속성) 간에 존재하는 함수적 종속성을 분석하여, 관계형 데이터베이스 스키마를 더 효율적이고 일관성 있게 정제해 나가는 일련의 과정입니다. 이를 통해 데이터의 중복을 줄이고, 속성들이 본래의 위치에 적절히 배치되도록 합니다.

정규화의 주요 목적 🎯

데이터 중복 제거: 데이터 중복을 최소화하여 저장 공간을 절약하고, 데이터 변경 시 발생할 수 있는 비일관성 문제를 방지합니다.
데이터 무결성 유지: 정규화를 통해 데이터의 정확성과 일관성을 유지할 수 있습니다.
속성 위치 최적화: 속성들이 해당 실체(Entity) 또는 테이블에 올바르게 배치되도록 하여 관리와 조회 효율성을 높입니다.

정규화는 여러 단계로 나뉘며, 각각의 단계에서 데이터 구조가 더욱 정교해지고, 중복이 제거됩니다. 각 단계는 상위 단계의 요구 사항을 만족하면서 더 강력한 데이터 구조를 제공합니다.

제1정규형(First Normal Form, 1NF) 🌟

제1정규형(1NF)은 데이터베이스 정규화의 첫 번째 단계로, 모든 속성값이 원자값(Atomic Value)이어야 하며, 중복된 데이터나 반복되는 그룹 속성이 제거된 상태를 의미합니다. 제1정규형에서는 각 컬럼이 분해할 수 없는 단일 값을 저장해야 합니다.

제1정규형에서 해결하고자 하는 문제점 ❗

부수적인 데이터 중복: 기본 데이터와 반복적인 데이터가 함께 저장되면서 데이터 중복이 발생합니다. 예를 들어, 하나의 컬럼에 여러 값이 저장될 경우 데이터가 불필요하게 중복됩니다.
반복 집합: 같은 유형의 데이터를 여러 컬럼에 나누어 저장하면 데이터의 일관성을 유지하기 어려우며, 중복된 데이터가 많아질 수 있습니다.

해결 방법 💡

반복되는 그룹 속성 제거: 같은 성격의 데이터가 여러 컬럼에 나누어 저장되는 상황을 피하기 위해 새로운 테이블을 생성해 데이터를 분리합니다.
새로운 테이블 생성: 반복되는 데이터를 별도의 테이블로 분리하고, 기본 테이블의 기본키를 이용해 두 테이블 간에 1:N 관계를 형성합니다.

제1정규형의 특징 🎯

반복되는 그룹 속성 제거: 동일한 성격의 데이터가 여러 컬럼에 나열되지 않도록 제거합니다.
원자값으로 구성: 각 컬럼의 값은 더 이상 분해할 수 없는 단일 값을 가져야 합니다.

예시 🔍

비정규 릴레이션 (1NF 적용 전)

학생과 그들이 수강하는 과목을 비정규 릴레이션으로 나타낸 테이블입니다.

학생_ID	학생명	과목1	과목2	과목3
1	John	수학	과학	영어
2	Alice	수학	과학	NULL
3	Bob	영어	NULL	NULL

문제점 🚩

중복된 데이터: 과목이 여러 개일 때 각 학생에 대해 중복된 정보가 저장됩니다.
반복 집합: 한 학생이 여러 과목을 듣는 경우, 동일한 데이터가 반복적으로 여러 컬럼에 나열됩니다.

제1정규형 적용 후 🛠️

데이터를 원자값으로 만들기 위해, 학생 정보와 수강 과목을 분리하여 두 개의 테이블로 관리합니다.

1. 학생 테이블

학생_ID	학생명
1	John
2	Alice
3	Bob

2. 학생_과목 테이블

학생_ID	과목
1	수학
1	과학
1	영어
2	수학
2	과학
3	영어

제1정규형 적용 결과 ✅

중복된 데이터 제거: 중복되는 과목 정보를 학생_과목 테이블로 분리하여 효율적인 데이터 저장 구조를 만들었습니다.
반복 집합 제거: 더 이상 같은 성격의 데이터를 여러 컬럼에 나누어 저장하지 않고, 각 데이터를 원자값으로 처리합니다.
1:N 관계 형성: 학생 테이블과 학생_과목 테이블 간에 1:N 관계가 성립되었습니다. 한 명의 학생이 여러 과목을 수강할 수 있으므로 두 테이블은 1:N 관계입니다.

제2정규형(Second Normal Form, 2NF) 🌟

제2정규형(2NF)은 제1정규형(1NF)을 만족하면서, 부분 함수 종속성을 제거한 상태입니다. 여기서 부분 함수 종속성이란 복합키(Composite Primary Key)의 일부에만 종속된 속성을 말합니다. 제2정규형에서는 이러한 부분 함수 종속성을 제거하여 모든 속성이 기본키 전체에 완전 종속되도록 만듭니다.

제2정규형에서 해결하고자 하는 문제점 🛠️

부분 함수 종속성: 테이블의 기본키가 여러 개의 컬럼으로 구성된 경우(복합키), 기본키의 일부 컬럼에만 종속된 속성이 존재할 수 있습니다. 이로 인해 데이터 중복 및 관리 비효율성이 발생할 수 있습니다.
불필요한 데이터 중복: 복합키의 일부에만 의존하는 속성들은 데이터 중복을 야기하고, 변경 시 비효율적인 구조를 만들 수 있습니다.

해결 방법 💡

복합키의 일부에만 종속된 속성 제거: 기본키의 일부 속성에만 종속된 속성들을 분리하여, 모든 속성이 기본키 전체에 완전 종속되도록 구조를 재구성합니다.
새로운 테이블 생성: 복합키의 일부에 종속된 데이터를 별도의 테이블로 분리해 부분 함수 종속성을 제거하고, 효율적인 구조로 변환합니다.

제2정규형의 특징 🎯

제1정규형(1NF)을 만족하면서, 기본키의 일부에만 종속된 속성을 제거하여 완전 함수 종속을 만족시킵니다.
복합키(Composite Key)가 존재하는 테이블에 주로 적용되며, 복합키가 아닌 경우는 제1정규형으로 충분한 경우가 많습니다.

예시 🔍

제2정규형 적용 전: 비정규 릴레이션 (1NF 상태)

아래는 주문 정보가 포함된 테이블입니다. 이 테이블은 복합키(Order_ID, Product_ID)로 구성되어 있지만, 일부 속성(상품명, 단위, 단가)은 Product_ID에만 종속되어 있는 상태입니다.

Order_ID	Product_ID	Product_Name	Unit	Price	Quantity
1001	1	노트북	대당	1,000,000	2
1001	2	마우스	개당	20,000	5
1002	1	노트북	대당	1,000,000	1
1002	3	키보드	개당	30,000	3

문제점 🚩

부분 함수 종속성: Product_Name, Unit, Price는 Product_ID에만 종속되어 있으며, Order_ID와는 무관합니다. 이는 부분 함수 종속성을 나타냅니다.
데이터 중복: 상품명, 단위, 단가 등의 정보가 중복되어 저장되고 있습니다. 예를 들어, 노트북의 정보가 여러 주문에 반복적으로 기록되고 있습니다.

제2정규형 적용 후 🛠️

부분 함수 종속성을 해결하기 위해 상품 정보를 별도의 테이블로 분리합니다. 이를 통해 Product_ID에만 종속된 속성들은 Product 테이블로 이동되고, 주문 상세 테이블에는 기본키 전체에 완전 종속된 속성만 남깁니다.

1. 주문 상세(Order_Detail) 테이블

Order_ID	Product_ID (FK)	Quantity
1001	1	2
1001	2	5
1002	1	1
1002	3	3

2. 상품(Product) 테이블

Product_ID	Product_Name	Unit	Price
1	노트북	대당	1,000,000
2	마우스	개당	20,000
3	키보드	개당	30,000

제2정규형 적용 결과 ✅

부분 함수 종속성 제거: Product_Name, Unit, Price는 이제 Product 테이블에 저장되며, Product_ID에 완전히 종속되었습니다.
데이터 중복 제거: 중복되던 상품 정보는 Product 테이블에서 한 번만 저장되고, 주문 정보는 주문 상세 테이블에서 관리됩니다.
1:N 관계 형성: Order_Detail 테이블은 주문과 상품 간의 1:N 관계를 유지하며, 상품 정보는 별도의 Product 테이블에서 관리됩니다.

제3정규형(Third Normal Form, 3NF) 🌟

제3정규형(3NF)은 제2정규형(2NF)을 만족하면서, 이행적 함수 종속성(Transitive Dependency)을 제거한 상태입니다. 이행적 함수 종속성이란 기본키가 아닌 속성이 다른 일반 속성에 종속되는 상황을 말합니다. 제3정규형에서는 기본키에 직접적으로 종속되지 않고, 다른 일반 컬럼에 종속된 속성을 분리하여 데이터베이스를 더 구조화하고 중복을 줄입니다.

제3정규형에서 해결하고자 하는 문제점 🛠️

이행적 함수 종속성: 기본키가 아닌 컬럼이 다른 일반 컬럼에 종속되는 경우, 데이터가 불필요하게 중복되고 관리가 어렵습니다.
데이터 중복: 이행적 종속성을 가진 컬럼이 여러 번 중복되어 저장되므로, 데이터의 비효율적인 관리로 이어집니다.

해결 방법 💡

이행적 함수 종속성 제거: 기본키에 직접 종속되지 않고, 다른 컬럼에 종속된 속성을 별도의 테이블로 분리하여 이행적 함수 종속성을 제거합니다.
새로운 테이블 생성: 이행적 종속 관계에 있는 속성들을 별도로 관리하여 기본 테이블은 기본키에 직접 종속된 속성만 남기고, 중복을 줄입니다.

제3정규형의 특징 🎯

기본키에 직접적으로 함수 종속: 모든 컬럼은 기본키에 직접적으로 종속되어야 하며, 다른 컬럼에 의존하는 이행적 종속성을 제거합니다.
제2정규형(2NF)을 만족하면서 이행적 종속성을 제거하여 데이터 중복을 최소화합니다.

예시 🔍

제3정규형 적용 전: 제2정규형 상태

다음은 주문 정보를 포함한 테이블입니다. 이 테이블은 제2정규형을 만족하지만, Shipping_ZipCode는 Shipping_Address에 종속되어 있으므로 이행적 종속성이 존재합니다.

Order_ID	Customer_ID	Shipping_Address	Shipping_ZipCode	Order_Date
1001	1	서울시 강남구	12345	2024-01-10
1002	2	서울시 강동구	54321	2024-01-11
1003	1	서울시 강남구	12345	2024-01-12
1004	3	서울시 중구	67890	2024-01-13

문제점 🚩

이행적 함수 종속성: Shipping_ZipCode는 Shipping_Address에 종속되어 있어, 같은 주소에 대한 우편번호가 반복 저장되고 있습니다.
데이터 중복: 동일한 주소의 경우 우편번호가 반복 저장되므로, 데이터 중복이 발생하고 관리의 비효율성이 생깁니다.

제3정규형 적용 후 🛠️

이행적 함수 종속성을 제거하기 위해, Shipping_Address와 Shipping_ZipCode를 배송지(Shipping) 테이블로 분리합니다. Order 테이블은 Shipping_ID를 외래키로 참조하여 배송 정보를 관리합니다.

1. 주문(Order) 테이블

Order_ID	Customer_ID	Shipping_ID (FK)	Order_Date
1001	1	1	2024-01-10
1002	2	2	2024-01-11
1003	1	1	2024-01-12
1004	3	3	2024-01-13

2. 배송지(Shipping) 테이블

Shipping_ID	Shipping_Address	Shipping_ZipCode
1	서울시 강남구	12345
2	서울시 강동구	54321
3	서울시 중구	67890

제3정규형 적용 결과 ✅

이행적 함수 종속성 제거: Shipping_ZipCode가 더 이상 Shipping_Address에 종속되지 않고, 별도의 배송지(Shipping) 테이블에서 관리됩니다.
데이터 중복 제거: 동일한 주소에 대한 우편번호가 더 이상 중복되지 않고, 중복된 데이터가 사라집니다.
관계 설정: Order 테이블과 Shipping 테이블 간에 1:N 관계를 형성하여, 여러 주문이 동일한 배송지를 참조할 수 있습니다.

BCNF (Boyce-Codd Normal Form) 🌟

BCNF (Boyce-Codd Normal Form)는 제3정규형(3NF)을 확장한 정규형입니다. 제3정규형이 해결하지 못하는 후보키가 아닌 결정자로 인한 종속성 문제를 해결하기 위해 도입되었습니다. BCNF는 모든 결정자(Determinant)가 후보키여야 한다는 규칙을 통해, 데이터베이스 종속성을 더 강하게 제한하여 데이터 무결성을 보장합니다.

BCNF에서 해결하고자 하는 문제점 🛠️

결정자(Determinant): 테이블에서 하나의 속성이 다른 속성의 값을 결정하는 속성을 결정자라고 합니다. 기본키는 결정자의 한 예로, 다른 컬럼의 값을 결정할 수 있습니다.
후보키가 아닌 결정자: 테이블에서 후보키가 아닌 속성이 결정자 역할을 하면, 데이터 무결성 문제가 발생할 수 있습니다. 이는 데이터 중복을 초래하고 비효율적인 구조로 이어집니다.
제3정규형의 한계: 제3정규형은 이행적 종속성을 해결하지만, 후보키가 아닌 결정자에 의한 종속성 문제는 해결하지 못하는 경우가 있습니다.

해결 방법 💡

후보키가 아닌 결정자를 제거: 후보키가 아닌 속성이 결정자 역할을 할 경우, 해당 속성을 별도의 테이블로 분리하여 모든 결정자가 후보키가 되도록 구조를 재설계합니다.

BCNF의 특징 🎯

모든 결정자는 후보키여야 함: 즉, 테이블 내에서 다른 속성을 결정하는 모든 속성은 반드시 후보키여야 하며, 후보키가 아닌 결정자는 제거되어야 합니다.
제3정규형보다 더 엄격한 데이터 무결성 보장: BCNF는 제3정규형보다 더 강한 제약을 두어 데이터베이스의 무결성을 보장합니다.

예시 🔍

BCNF 적용 전: 제3정규형 상태

다음은 제3정규형을 만족하는 테이블입니다. 이 테이블에서 Instructor_ID는 강의(Course_ID)와 강의실(Room_No)을 결정하지만, Instructor_ID는 후보키가 아닙니다. 또한, 강의실(Room_No)은 강의 시간(Time)을 결정합니다.

Course_ID	Instructor_ID	Room_No	Time
CS101	1	201	9:00 AM
CS102	2	202	10:00 AM
CS103	1	201	9:00 AM
CS104	2	202	10:00 AM

문제점 🚩

후보키가 아닌 결정자 존재: Instructor_ID는 후보키가 아니지만, Room_No와 Time을 결정합니다.
종속성 문제: 특정 강의실과 시간은 동일한 강사에 의해 반복적으로 사용되므로 데이터 중복이 발생합니다.
BCNF 위반: 이 테이블은 제3정규형은 만족하지만, BCNF 규칙에 따라 모든 결정자가 후보키여야 한다는 조건을 위반하고 있습니다.

BCNF 적용 후 🛠️

BCNF를 만족시키기 위해, 후보키가 아닌 결정자인 Instructor_ID가 결정하는 Room_No와 Time을 별도의 테이블로 분리합니다.

1. 강의(Course) 테이블

Course_ID	Instructor_ID (FK)
CS101	1
CS102	2
CS103	1
CS104	2

2. 강사-강의실(Instructor_Room) 테이블

Instructor_ID	Room_No	Time
1	201	9:00 AM
2	202	10:00 AM

BCNF 적용 결과 ✅

후보키가 아닌 결정자 제거: Instructor_ID에 의해 결정되는 Room_No와 Time을 Instructor_Room 테이블로 분리하여, 모든 결정자가 후보키가 되도록 설계했습니다.
중복된 데이터 제거: 강의에 관련된 정보는 Course 테이블에서 관리하고, 강의실과 시간 정보는 Instructor_Room 테이블에서 관리하여 중복을 줄였습니다.
데이터 무결성 보장: BCNF 규칙을 만족하게 되어, 모든 결정자가 후보키가 되었고, 데이터의 무결성이 보장됩니다.

물리적 데이터베이스 모델링 🌟

물리적 데이터베이스 모델링은 논리적 모델링에서 설계된 데이터를 실제 데이터베이스 시스템(DBMS)에서 구현하는 과정입니다. 이 과정에서는 데이터베이스 성능 최적화를 위한 여러 기법들이 적용되며, 데이터베이스의 구체적인 저장 구조와 성능을 고려하여 설계가 이루어집니다.

물리적 데이터베이스 모델링의 주요 개념 💡

DBMS 특성에 맞는 데이터베이스 개체 정의
- 도메인(domain): 테이블의 각 컬럼에 대한 구체적인 자료형을 정의합니다. 자료형은 DBMS에 맞는 타입으로 설정해야 합니다.
  - 예시: int, varchar, date, float와 같은 자료형을 사용해 데이터를 관리합니다.
효율적인 데이터베이스 설계를 위한 분석
- 데이터 사용량 분석: 자주 조회되거나 업데이트되는 컬럼을 파악하여, 최적의 성능을 도출할 수 있도록 설계합니다.
- 업무 프로세스 분석: 데이터베이스가 실제 업무 흐름에 맞게 최적화되어 있는지 분석하여, 데이터 처리 속도를 개선합니다.
효율적인 인덱스 정의
- 인덱스는 데이터 검색을 빠르게 하기 위한 구조입니다. 자주 조회되는 컬럼에 인덱스를 추가하면 성능이 크게 향상됩니다.
- 주의사항: 인덱스가 너무 많아지면 삽입 및 수정 시 성능이 저하될 수 있으므로 적절한 인덱스 개수를 유지해야 합니다.
Trigger(트리거) 설정
- 트리거(Trigger)는 특정 이벤트가 발생할 때 자동으로 실행되는 프로그램입니다. 데이터를 자동으로 처리하고 무결성을 유지할 수 있습니다.
  - 예시: 주문이 생성되면, 재고가 자동으로 업데이트되도록 트리거를 설정할 수 있습니다.
역정규화(Denormalization) 작업
- 성능 최적화를 위해 정규화된 데이터를 일부 역정규화하여 중복 저장하는 작업입니다. 성능이 중요한 경우 조회 속도를 향상시키기 위해 사용되지만, 데이터 일관성 문제를 야기할 수 있으므로 신중하게 사용해야 합니다.

역정규화(DeNormalization) 설명 🚀

역정규화(DeNormalization)는 정규화된 데이터베이스 구조에서 성능을 향상시키기 위해 데이터를 중복 저장하거나 테이블을 재구성하는 작업입니다. 조인 연산을 줄이거나 복잡한 연산을 피하기 위해 의도적으로 데이터 중복을 허용하여 성능을 높입니다.

역정규화 방법 🔧

데이터 중복 (컬럼 역정규화):
- 데이터 조인 작업을 줄이기 위해 중복된 데이터를 컬럼에 추가하여 조회 성능을 향상시킵니다.
- 예시: 주문 테이블에 고객 주소 정보를 중복 저장하여 조인 없이 조회할 수 있도록 설계.
파생 컬럼의 생성:
- 연산을 반복적으로 수행하지 않도록, 미리 계산된 값을 저장하는 파생 컬럼을 생성하여 조회 성능을 높입니다.
- 예시: 판매 금액(수량 * 단가)을 미리 계산해 저장하는 컬럼을 추가해 조회 속도를 높입니다.
테이블 분리:
- 컬럼 수나 레코드 수가 많은 테이블을 작은 테이블로 분리하여 성능을 최적화합니다.
- 예시: 고객 테이블에서 자주 조회되지 않는 주소 정보를 별도의 테이블로 분리하여 관리.
요약 테이블 생성(Summary Table):
- 자주 집계되는 데이터를 미리 계산하여 저장하는 요약 테이블을 생성하여 성능을 향상시킵니다.
- 예시: 일일 총 판매량을 미리 저장하여 복잡한 조회 작업을 줄임.
테이블 통합:
- 자주 조인되는 테이블을 통합하여 성능을 최적화합니다.
- 예시: 고객과 주문 테이블을 자주 조인해야 할 경우, 이를 하나의 테이블로 통합하여 조회 성능을 높입니다.

역정규화의 장점과 단점 ⚖️

장점: 조회 성능이 크게 향상되고, 복잡한 연산이나 조인 작업을 줄일 수 있습니다.
단점: 데이터 중복으로 인해 일관성 유지가 어려워지고, 업데이트 시 중복된 데이터를 모두 수정해야 하는 추가 작업이 발생할 수 있습니다.

이상으로 물리적 데이터베이스 모델링에 대한 설명을 마치겠습니다. 😊 물리적 모델링은 데이터베이스의 성능과 효율성을 최적화하는 중요한 단계입니다. 설계 단계에서의 작은 선택이 데이터베이스의 성능과 무결성에 큰 영향을 미치기 때문에, 각 기법을 상황에 맞게 신중하게 적용하는 것이 중요합니다.

데이터베이스 최적화를 통해 더욱 빠르고 효율적인 시스템을 구축해 보세요! 🚀

김상욱

이전 포스트

[코딩테스트][백준] 🔥 백준 19238번 "스마트택시" 문제: Python으로 완벽 해결하기! 🔥

다음 포스트