데이터 모델링, 무결성, 정규화, ERD작성

데이터 모델링

  비즈니스를  추상화, 단순화, 명확화 표기하기 위해서 분석하고 데이터화 시키는 것, 정보시스템을 구축하는 과정에서 계획.분석.설계 단계에서 업무를 분석하고 설계할 때 또한, 이후 구축.운영 단계에서 변경과 관리할 때 사용됨. 

 

데이터 모델링표기기법

• 1. 피터첸 : 엔터티를 사각형, 엔터티간 관계를 마름모, 엔터티의 속성을 타원형 으로 표현.
• 2. I/E Crow's Foot (까마귀발) : 

데이터 모델링 개념

• 도메인 - 도메인은 비즈니스 문제 영역 전체를 포괄하는 개념으로, 이를 모델링하는 과정에서 객체가 아닌 형태로 표현되는 요소(비즈니스 로직 등)도 있을 수 있습니다.
• 엔터티 - 엔터티는 도메인 모델을 구성하는 특정한 요소로서, 도메인 내에서 고유한 식별자를 가지는 개별 객체를 의미합니다. DB 테이블과 매핑되어 DB에 저장될 수 있는 객체로 여러가지 속성을 갖기도 함.
• 속성 -  업무에서 필요로 하는 인스턴스에서 관리하고자 하는 의미상 더이상 분리되지 않는 최소의 데이터 단위
• 관계 - 엔터티의 인스턴스 사이의 논리적인 연관성. 엔티티 간의 관계는 비즈니스 로직에서 어떤 엔티티가 다른 엔티티의 생성이나 행동에 선행하거나 영향을 미치는지를 표현합니다.
• 관계차수 : 관계는 전적으로 비즈니스 로직에 의해서 결정되므로, 같은 두 엔티티사이에 어떤 관계차수든 형성될 수 있다.
  • 1: 1(ONE TO ONE) : 한 사용자는 하나의 프로필만 가질 수 있으며, 각 프로필은 하나의 사용자에게만 속합니다.
  • 일대다 (One-to-Many) : 하나의 수업(Course)에 여러 명의 학생(Student)이 참여할 수 있지만, 각 학생은 하나의 수업만 듣는 경우.
    • 특정 수업에 배정된 학생들은 다른 수업을 들을 수 없고, 특정 수업에 소속되었을 때에만 존재 의미가 부여되는 경우. (일이 있어야 다가 있기때문에, 비즈니스 로직에 의하면 수업이 선행하는 구조)
  • 다대일 (Many-to-One)  : 여러 학생(Student)이 하나의 수업(Course)에 참여하는 경우.
    • 특정 수업에 배정된 학생들은 다른 수업을 들을 수 없고, 특정 수업에 소속되었을 때에만 존재 의미가 부여되는 경우. (일이 있어야 다가 있기때문에, 비즈니스 로직에 의하면 수업이 선행하는 구조)
  • M:M(MANY TO MANY) : 여러 학생(Student)이 여러 수업(Course)에 참여할 수 있으며, 각 수업에는 여러 학생이 등록될 수 있는 경우. 
    • 다대다(Many-to-Many) 관계는 실제로는 두 개의 일대다(One-to-Many) 관계로 분해되어 표현됩니다. 데이터베이스에서는 다대다 관계를 직접 표현할 수 없으므로, 중간에 조인 테이블을 추가하여 이를 구현합니다.
    • 여러 고객이 여러 상품을 주문할 수 있으며, 한 상품도 여러 고객이 주문할 수 있습니다. -> 이경우는 다대다 이긴하나, 직접적으로 연결된 관계가 아니기때문에 고려하지 않는다.

• 관계선택사양 : 필수관계(1), 선택관계(0)

데이터 테이블 관계 모형

• One (|-----): This indicates a mandatory one relationship. For every record in the entity on the left, there must be exactly one corresponding record in the entity on the right.
• Many (|-----<): This represents a mandatory many relationship. Each record in the entity on the left is related to one or more records in the entity on the right.
• Only One (|-----||): This is a mandatory and exclusive relationship. Each record on the left is associated with exactly one and only one record on the right. This typically represents a strict one-to-one relationship.
• One or Many (|-----<|) : This represents a mandatory one or many relationship. Every record in the entity on the left must be linked to at least one record in the entity on the right, but it could be linked to many.
• Zero or One (|-----o|) : This indicates an optional one relationship. Each record in the entity on the left can either be related to zero or one record in the entity on the right.
• Zero or Many (|-----o<) : This represents an optional many relationship. Each record in the entity on the left can be related to zero or many records in the entity on the right.
• Zero or One or Many (|-----o<|) : This represents an optional one or many relationship. Each record in the entity on the left can be related to zero, one, or many records in the entity on the right.

 

데이터 모델링 툴

• 사용툴 : erdCloud : https://www.erdcloud.com/
• 프로젝트 시작전에 데이터 모델링을 진행하여 엔티티, 속성, 관계들을 설계해두어야 DB와 함수들을 시행착오가 없도록 빠르게 구현할 수 있음.
• 엔티티를 생성하고 그 내부에 상단에 primarykey를 입력, 하단에 일반 key를 입력함.

 

데이터 모델링 예시

• 논리적데이터 모델링 : key, 속성, 관계 등을 정확하게 표현 
  • 1. 우선적으로 엔터티들을 쭉 define 하는 것이 가장 먼저 할일
  • 2. 엔터티 간의 관계를 FK를 통해서 설정해준다. 서로 식별관계를 맺을지 비식별 관계를 맺을지 결정 a)한 엔터티의 PK가 타 엔터티의 FK가 되거나 PK가 되는 방식으로 관계를 설정.
• 물리적데이터 모델링 : 실제로 데이터베이스에 이식할 수 있을정도로 성능, 저장 등 물리적 성격을 결정 
  • 3. 이름표의 역할을 하던 논리적 라벨링을 물리적으로 실질적인 테이블명을 define해준다.
  • 4. 각 엔터티의 도메인과 데이터 타입을 결정해준다.
  • 5. 포워드 엔지니어링을 통해서 sql문으로 변환시켜서 
• 엔티티 
  • 1. 회원 : 회원아이디(PK), 이름, 연락처
  • 2. 상품 : 상품아이디(PK), 상품내용, 상품가격, 상품명
  • 3. 주문 : 주문아이디(PK),  주문일, 주문수량, 주문가격, 

 

테이블의 대표 식별자 설정

• 프라이머리 키 PK : 테이블에서 각 행(row)을 유일하게 식별하는 속성(또는 속성들의 조합)입니다. 마치 사람의 주민등록번호와 같이 고유한 값을 가짐
• 비프라이머리 속성: 프라이머리 키가 아닌 나머지 모든 속성들을 말합니다. 예를 들어, '학생' 테이블에서 '학번'이 프라이머리 키라면, '이름', '나이', '학과' 등이 비프라이머리 속성이 됩니다.
• Unique Key : Unique Key는 단순히 중복 방지 목적으로 사용되며, 테이블의 식별자 역할과 비즈니스 요구사항에 따라 필요할 때만 지정됩니다.
  • 큰 문자열이나 복잡한 데이터 유형으로 PK를 설정하면, 인덱스의 성능에 부정적인 영향을 미칠 수 있으므로, 대신 **간단한 정수형 PK(id)**를 사용하고 다른 컬럼에 유니크 키를 설정하는 것이 성능 면에서 효율적입니다.
  • (클러스터드 인덱스로 설정되어, 물리적으로 정렬되며 데이터 조회 성능이 최적화됩니다. Unique Key는 기본적으로 비클러스터드 인덱스로 설정됩니다.)

• FK : 다른 테이블의 기본 키를 참조하는 열 또는 열들의 조합입니다. 외래 키는 테이블 간의 관계를 정의하는 데 사용됩니다.  일반적으로 FK는 참조 테이블의 PK와 연결하는 경우가 많지만, 참조 테이블의 다른 유니크 키(Unique Key)나 복합 유니크 제약 조건을 갖는 컬럼과도 연결할 수 있습니다. 
  • 식별관계 : 자식 테이블이 부모 테이블의 기본 키를 외래 키로 포함하고, 그 외래 키가 자식 테이블의 기본 키의 일부가 되는 관계입니다. 그리하여, 부모 테이블 없이는 자식 테이블의 데이터가 독립적으로 존재할 수 없습니다.
  • 비식별관계 :  : 자식 테이블이 부모 테이블의 기본 키를 외래 키로 참조하지만, 그 외래 키가 자식 테이블의 기본 키에 포함되지 않는 관계입니다. 그리하여, 자식 테이블은 부모 테이블과 연결되어 있지만, 그 관계는 단순한 참조 관계에 불과하며, 자식 테이블의 고유성을 정의하는 데 부모 테이블의 기본 키가 필요하지 않습니다.

• 예시 : 주문이 회원아이디라는 정보를 담고 있기에, 주문은 회원아이디를 FK로 갖는다. 마찬가지로 주문은 상품의 아이디도 담고있기에 상품아이디를 FK로 갖는다. 주문은 회원아이디와 상품아이디가 없으면 존재할 수 없으므로 식별관계이다.

 

데이터의 무결성

• 무결성(Integrity)은 데이터의 일관성, 정확성, 신뢰성을 유지하는 것. 즉, 데이터베이스 내의 데이터가 항상 올바르게 저장되고 유지되며, 잘못된 데이터가 들어오거나 데이터 간의 불일치가 발생하지 않도록 보장하는 원칙입니다.
  • 일관성(Consistency) : 데이터가 규칙과 제약 조건을 준수하여, 항상 일정한 상태를 유지하는 것을 의미합니다. 
  • 정확성(Accuracy) : 데이터가 현실 세계의 실제 상황을 얼마나 정확하게 반영하고 있는지
  • 신뢰성(Reliabilty)  : 데이터가 안정적이고 예측 가능하게 사용될 수 있으며, 시간이 지나도 일관되게 유지되는 것을 의미합니다. 데이터가 손상되지 않고, 시스템이 실패하더라도 안전하게 보존되어야 합니다.
    • PK를 사용하면 동일한 데이터가 여러 번 저장되는 일이 없으며, 데이터베이스 내에서 데이터의 고유성을 보장합니다.
    • FK는 반드시 부모 테이블의 PK에 존재해야 하므로, 데이터 간의 관계가 일관되게 유지됩니다. 이를 통해 부모 테이블에 없는 데이터가 자식 테이블에 존재하는 불일치 상태가 방지됩니다.
    • 데이터베이스 레벨에서 데이터의 무결성을 보장하고 검증하는 역할을 합니다. 즉, Java 애플리케이션 레벨에서 잘못된 값이 입력되더라도, 데이터베이스 레벨에서 이를 마지막으로 확인하고 잘못된 데이터를 차단하는 역할을 합니다.

 

데이터 베이스 정규화

• 데이터베이스 정규화의 주요 목적 중 하나는 데이터 중복을 방지하는 것입니다. 이를 통해 데이터 무결성을 유지하고 저장 공간을 절약하며, 데이터 갱신 시 일관성을 보장할 수 있습니다. 
  • 종속적인 칼럼은 테이블을 분리해서 중복을 방지한다

 

제1정규형 (1NF), 원자값으로 만들기

• 1. 각 컬럼이 원자값(더 이상 분해할 수 없는 단일 값)을 가져야 합니다
• 2 반복되는 그룹을 제거해야 합니다.
• 3. 각 레코드가 유일한 식별자(기본 키)를 가져야 합니다.

 

1. 각 컬럼이 원자값(Atomic Value)을 가져야 합니다

• 비정규화된 테이블:학생ID이름연락처 

  학생ID	이름	연락처
  1	홍길동	010-1234-5678, 02-123-4567

• 1NF를 만족하는 테이블:학생ID이름휴대전화집전화

  1

  홍길동

  010-1234-5678

  02-123-4567

2. 반복되는 그룹을 제거해야 합니다

• 비정규화된 테이블:

  주문ID	고객명	상품1	수량1	상품2	수량2
  1	김철수	사과	2	배	3

• 1NF를 만족하는 테이블:

  주문ID	고객명	상품	수량
  1	김철수	사과	2
  1	김철수	배	3

3. 각 레코드가 유일한 식별자(기본 키)를 가져야 합니다

• 비정규화된 테이블 : 

  이름	과목	점수
  이영희	수학	90
  이영희	영어	85

• 1NF를 만족하는 테이블

  학생ID	이름	과목	점수
  1	이영희	수학	90
  1	이영희	영어	85

 

 

제2정규형 (2NF), 부분함수종속제거

• 테이블의 모든 비프라이머리 속성(Primary Key가 아닌 속성)이 완전 함수 종속을 가져야 함. 즉, 복합키를 사용하는 경우, 비프라이머리 속성이 모든 프라이머리 키에 종속되어야 함. 다시말하면 부분 함수 종속이 없어야 합니다. 즉, 복합 키(기본 키가 두 개 이상의 컬럼으로 구성된 경우)의 일부에만 종속되는 컬럼을 분리합니다.
  • 함수 종속이란?  하나의 속성(또는 속성들의 집합)의 값이 다른 속성(또는 속성들의 집합)의 값에 의해 유일하게 결정될 때, 함수 종속이 성립한다고 합니다. 마치 "학번"이 결정되면 "이름"이 유일하게 결정되는 것처럼 말이죠.
  • 완전 함수 종속이란? 복합 키(여러 개의 속성으로 구성된 키)를 사용하는 경우, 비프라이머리 속성이 복합 키의 모든 부분에 의해서만 함수 종속될 때를 말합니다. 즉, 복합 키의 일부만으로는 비프라이머리 속성의 값을 유일하게 결정할 수 없어야 합니다.

예시 (2NF에 위배된 테이블): PK는 학생ID, 과목ID 복합키인데, 교수명과 과목명이 과목ID에 부분종속됨.

| 학생ID | 과목ID | 교수명 | 과목명 |
|--------|--------|--------|--------|
| 1           | 101       | 김철수    | 수학   |
| 1           | 102      | 이영희    | 과학   |
| 2          | 101       | 김철수    | 수학   |

 

수정된 테이블(O)

| 학생ID | 과목ID |
|--------|--------|
| 1           | 101    |
| 1           | 102    |
| 2          | 101    |

 

수정된 테이블(O)

| 과목ID | 교수명 | 과목명 |
|--------|--------|--------|
| 101       | 김철수   | 수학   |
| 102       | 이영희  | 과학   |

 

 

 제3정규형 (3NF), 이행함수종속제거

•  기본 키가 아닌 컬럼이 또 다른 기본 키가 아닌 컬럼에 종속될 때 이를 분리합니다. 즉, 한 레코드에 한 칼럼이 다른 칼럼에 데이터 종속성이 있는 경우 이를 분리하여 테이블을 나누는 것이 정규화의 핵심입니다
• 이행 함수 종속(Transitive Dependency) : X → Y, Y → Z라는 두 개의 함수 종속이 존재할 때, X → Z라는 새로운 함수 종속이 성립하는 것을 의미합니다. 즉, X가 Y를 결정하고, Y가 Z를 결정하면, 결국 X가 Z를 결정하게 되는 관계를 말합니다.

3NF를 위반하는 예시 ; PK는 주문번호이고 비PK인 고객이름에 이행종속적인 고객주소 데이터가 있음.

주문번호	고객 이름	고객 주소	상품 이름	상품 가격
1001	홍길동	서울시 강남구	노트북	100만원
1002	이순신	부산시 해운대구	스마트폰	80만원
1003	홍길동	서울시 강남구	태블릿	70만원

 

 

3NF를 만족하도록 분리

주문번호	고객 ID	상품 이름	상품 가격
1001	101	노트북	100만원
1002	102	스마트폰	80만원
1003	101	태블릿	70만원

 

기타 모델링

• 테이블 간의 관계는 primary key를 통해서 이루어지고, 다른 테이블의 특정 칼럼에 대한 조회는 primarykey를 통한 join문을 사용하는 것이 좋다. 
  
  • 단, PK ID는 내부적으로만 사용하는 것이며, 외부적으로 사용이 필요할때는 별도로 ID 칼럼을 하나 더 만드는 것이 좋다. ID에는 거래 건수와 같은 중요정보를 담고 있기 때문임.