[KT AIVLE] SQL 2일차 - 심화학습

0. 개요

---

오늘은 요구사항을 확인하여 ERD 다이어 그램을 작성하는 방법을 배워보았습니다.

같은 요구사항이여서 다양한 관점에서 ERD 다이어 그램이 나온다는것을 알 수 있었던 학습내용이였습니다!

그러면 정리 시작하도록 하겠습니다.

ERD 다이어 그램

---

다이어 그램을 그리는 도구는 다양할 수 있지만 이번에는 eXERD라는 프로그램을 사용했습니다.

요구분석시 고려사함

• 명확성
  • 요구사항은 이해하기 쉽게 명확하게 기술되어야 한다. 애매모호한 표현은 피해야됨
• 일관성
  • 요구사항 간에 모순이 없어야하며, 시스템의 다른 부분들과 일관성을 유지해야 됨
• 완전성
  • 요구사항은 시스템의 모든 측면을 포괄해야 하며, 누락된 부분이 없어야 함
• 추적 가능성
  • 각 요구사항은 그 출처를 명확히 할 수 있어야 하며, 시스템 설계 및 구현 단계에서 추적 가능해야 함
• 검증 가능성
  • 요구사항은 검증 가능해야 하며, 테스트나 검토를 통해 확인할 수 있어야 함

데이터 모델링이란

1. 개념적 데이터 모델링
2. 논리적 데이터 모델링
3. 물리적 데이터 모델링

일반적으로 이야하기하는 데이터 모델링이란 논리적 모델링임

개념적 모델링 주요 구성 요소

• 엔티티(Entity)
  • 실제세계 객체나 개념을 나타내며, 데이터베이스에 저장되는 주요 객체(예: 고객, 주문, 제품)
• 속성(Attribute)
  • 엔티티의 특성을 나타내는 데이터 요소(예: 고객의 이름, 주소, 전화번호)
    • 단순 속성: 더 이상 분해할 수 없는 속성 (예: 이름)
    • 복합 속성: 여러 하위 속성으로 구성된 속성 (예: 주소)
    • 다중 값 속성: 여러 값을 가질 수 있는 속성 (예: 전화번호)
    • 유도 속성: 다른 속성의 값에서 계산된 속성 (예: 총 금액)
• 관계(Relationship)
  • 엔티티 간의 연관성 (예: 고객이 주문을 한다, 주문이 제품을 포함한다)
    • 일대일(1:1) 관계: 한 엔티티가 다른 엔티티와 하나의 연관성 (예: 한 사람이 하나의 여권)
    • 일대다(1:M) 관계: 한 엔티티가 여러 다른 엔티티와 연관성 (예: 한 고객이 여러 주문)
    • 다대다(M:N) 관계: 여러 엔티티가 여러 다른 엔티티와 연관성 (예: 여러 학생이 여러 강의)
    • 필수(Mandatory) 관계: 모든 엔티티가 관계에 참여해야 함 (예: 모든 주문은 반드시 고객에 의해 만들어져야 함)
    • 선택적(Optional) 관계: 일부 엔티티는 관계에 참여하지 않아도 됨 (예: 고객은 주문을 할 수도 있고 하지 않을 수도 있음)
• 식별자(Identifier)
  • 엔티티를 고유하게 식별하는 하나 또는 그 이상의 속성(예: 고객 엔티티의 고객 ID, 주문 엔티티의 주문 ID)
• 식별자 종류
  • 기본 식별자(Primary Identifier): 각 인스턴스를 고유하게 식별하는 데 사용되는 주요 속성
    • 예: 고객 엔티티의 고객 ID, 주문 엔티티의 주문 ID
  • 대체 식별자(Alternate Identifier): 기본 식별자를 대신하여 각 인스턴스를 고유하게 식별할 수 있는 속성
    • 예: 이메일 주소가 고유할 경우 고객 엔티티의 대체 식별자로 사용
  • 복합 식별자(Composite Identifier): 두 개 이상의 속성을 조합하여 각 인스턴스를 고유하게 식별하는 경우
    • 예: 학번과 과목 ID를 조합하여 각 수강 기록을 고유하게 식별
  • 자연 식별자(Natural Identifier): 현실 세계에서 고유한 값을 갖는 속성을 식별자로 사용하는 경우
    • 예: 주민등록번호, ISBN 등
  • 인조 식별자(Surrogate Identifier): 시스템에 의해 생성된 고유 값을 식별자로 사용하는 경우
    • 예: 자동 증가 숫자, GUID

개념적 데이터 모델링 단계

1. 요구사항 분석
   • 시스템의 요구사항을 수집하고 분석하여 데이터 모델의 기초 자료를 마련
   • 이해관계자 인터뷰, 문서조사 등을 통해 데이터 요구사항 수집
2. 엔티티 도출
   • 비즈니스 업무에서 필요한 주요 엔티티를 식별하고, 엔티티 간의 명확한 관계를 정립 및 확장
3. 속성 정의
   • 각 엔티티에 필요한 속성을 정의하며, 엔티티 속성 규칙을 작성하고, 속성의 데이터 유형을 결정
4. 관계 설정
   • 엔티티간의 관계를 정의하고, 관계의 유형(일대일, 일대다, 다대다)을 명확히 설정
   • 관계의 기수성(Cardinality) 및 선택성(Optionality)을 결정
5. ER 다이어그램 생성
   • 엔티티, 속성, 관계를 포함하는 ERD를 작성하여 주요 엔티티 및 관계를 시각적으로 표현
6. 검증 및 피드백
   • 작성된 모델을 검토하고 이해관계자들로부터 피드백을 받아 수정하며, 모델의 완성도, 정확성, 정합성을 검토

개념적 데이터 모델링 장점

• 이해 용이성
  • 기술적인 세부사항 없이 데이터 구조를 시각적으로 표현하여, 기술적 배경이 없는 사용자도 쉽게 이해할 수 있음
  • 엔티티, 속성, 관계 등을 다이어그램으로 표현하여 데이터 구조를 직관적으로 파악할 수 있음
• 의사소통의 원활화
  • 개발자, 데이터베이스 설계자, 비즈니스 애널리스트, 사용자 등 다양한 이해관계자가 공통된 이해를 가질 수 있음
  • 비즈니스 요구사항을 명확하게 모델링하여, 데이터베이스 설계에 정확하게 반영할 수 있음
• 유연성 및 확장성
  • 데이터베이스 구현 전에 모델을 수정하거나 확장하기가 상대적으로 쉬움
  • 새로운 엔티티나 속성, 관계를 추가하기가 용이하며, 기존 모델을 쉽게 확장할 수 있음
• 데이터 품질 향상
  • 개념적 데이터 모델링을 통해 데이터의 정확성과 일관성을 높일 수 있음
  • 비즈니스 규칙과 제약조건을 데이터 모델에 포함시켜, 데이터의 품질을 향상시킴
• 재사용성
  • 동일하거나 유사한 데이터 구조가 필요한 다른 프로젝트나 시스템에서 개념적 데이터 모델을 재사용할 수 있음

개념적 데이터 모델링 그림

• 엔티티 : 사각형
• 속성 : 타원
• 관계 : 마름모
• 관계선 : 선으로 표현
• 식별자 : 밑줄

논리적 모델링 주요 구성 요소

• 엔티티(Entity)
  • 테이블로 변환되며, 데이터베이스에서 저장되는 주요 객체를 나타냄
• 속성(Attribute)
  • 열로 변환되며, 테이블 내에서 저장되는 데이터 요소를 나타냄
• 관계(Relationship)
  • 외래 키로 변환되며, 테이블 간의 연관성을 정의
• 식별자(Identifier)
  • 기본 키로 변환되며, 테이블 내에서 각 행을 식별하는 데 사용
• 제약 조건(Constraints)
  • 데이터의 정확성과 무결성을 유지하기 위한 규칙을 정의

논리적 모델링 단계

1. 개념적 모델 검토
   • 개념적 데이터 모델에서 도출된 엔티티, 속성, 관계를 검토
2. 엔티티를 테이블로 변환
   • 각 엔티티를 데이터베이스 테이블로 변환
3. 속성을 열로 변환
   • 각 속성을 테이블의 열로 변환하고 데이터 유형을 정의
4. 식별자 정의
   • 각 테이블의 기본 키를 정의하여 테이블 내 각 행을 고유하게 식별
5. 관계 설정
   • 테이블 간의 외래 키를 설정하여 관계를 정의
6. 제약 조건 정의
   • 데이터 무결성을 유지하기 위해 제약 조건을 정의 (예: 고유성 제약, 무결성 제약, 참조 무결성 제약 등)
7. 정규화(Normalization)
   • 데이터를 중복 없이 효율적으로 저장하기 위해 정규화를 수행
   • 정규화는 데이터베이스 설계의 품질을 높이고, 데이터 일관성을 유지함

논리적 모델링 장점

• 데이터 정확성 및 무결성 보장
  • 제약 조건과 키를 통해 데이터의 정확성과 무결성을 유지할 수 있음
• 효율적인 데이터 저장
  • 정규화를 통해 데이터 중복을 최소화하고, 저장 공간을 효율적으로 사용함
• 데이터베이스 구현의 기초 제공
  • 논리적 데이터 모델은 물리적 데이터 모델링과 데이터베이스 구현의 기초가 됨
• 비즈니스 규칙 반영
  • 데이터베이스 설계에 비즈니스 규칙을 명확히 반영하여, 시스템 요구사항을 충족함

논리적 데이터 모델링 그림

정규화

---

필요성

• 데이터 중복 감소
  • 데이터가 여러 테이블에 분산되어 저장됨으로써 중복되는 데이터를 최소화함 → 저장 공간 절약, 데이터 일관성 유지
• 데이터 무결성 유지
  • 데이터의 일관성과 정확성 보장. 중복 데이터가 줄어들어 데이터의 수정, 삭제, 삽입 시 무결성이 유지됨
• 데이터 이상(Anomaly) 방지
  • 추가 이상(Insertion Anomaly): 데이터 삽입 시 불필요한 중복 데이터를 피할 수 있음
  • 변경 이상(Update Anomaly): 데이터 갱신 시 모든 중복 데이터를 일관되게 수정할 수 있음
  • 삭제 이상(Deletion Anomaly): 데이터 삭제 시 중요한 정보가 의도치 않게 손실되지 않음
• 데이터베이스 설계의 효율성
  • 쿼리 성능을 향상시키고, 데이터 검색 및 조작을 더 효율적으로 수행할 수 있음
  • 각 테이블이 명확하게 정의되고, 특정한 역할을 수행하도록 함으로써 데이터베이스 설계를 쉽게 이해할 수 있게 됨
• 데이터베이스 유지보수 용이성
  • 데이터베이스 구조가 명확하여, 유지보수가 용이해짐. 새로운 요구사항에 따른 구조 변경도 쉽게 할 수 있음
• 확장성 및 유연성
  • 새로운 데이터 요구사항이 발생할 때 쉽게 확장할 수 있음. 새로운 엔터티나 속성을 추가하기가 용이함

1차 정규형

• 테이블이 정규화의 찻 반째 단계에 부합하는 것을 의미하며, 다음과 같은 조건을 충족해야 함
  • 원자성 : 모든 속성 값이 더 이상 분해할 수 없는 원자값
  • 중복 행 제거 : 모든 행이 고유해야 함
  • 칼럼 순서가 무의미 : 컬럼의 순서가 데이터 베이스 설계에 영향을 주지 않음

2차 정규형

• 1차 정규형을 만족하고, 기본 키가 아닌 모든 속성이 기본 키의 모든 부분 집합에 완전히 종속 되어야 함
  • 부분적 종속성 제거 : 기본 키의 일부분에만 종속적인 속성을 제거
  • 2NF는 복합 키를 가진 테이블 에서만 의미가 있음

3차 정규형

• 2차 정규형을 만족하고, 비 기본 키 속성이 다른 비 기본 키 속성에 이행적 종속을 갖지 않아야 함
  • 이행적 종속성 제거 : 비 기본 키 속성이 다른 비 기본 키 속성에 종속되지 않도록 함

역정규화

• 역정규화란
  • 정규화된 데이터베이스를 의도적으로 비정규화하여 데이터 중복을 허용하는 방식으로, 데이터베이스 성능을 향상시키는 과정
  • 데이터 액세스 속도를 높이고, 복잡한 쿼리의 응답 시간을 줄이기 위해 데이터 중복과 저장 공간의 증가를 허용함
• 역정규화 필요성
  • 특히 읽기 작업이 많은 데이터베이스에서 쿼리 성능을 최적화하기 위해 사용됨
  • 복잡한 조인(Join) 연산을 줄이고, 단일 테이블에서 데이터를 빠르게 조회할 수 있도록 함
  • 데이터를 중복 저장하여 복잡한 쿼리 구조를 단순화함
• 역정규화 장점
  • 데이터를 중복 저장함으로써 읽기 성능이 향상됨
  • 조인 연산을 줄이고, 데이터 조회 속도를 높임
  • 복잡한 쿼리를 단순화하여 유지보수와 관리가 용이함
• 역정규화 단점
  • 데이터 중복으로 인해 저장 공간이 증가됨
  • 데이터 중복으로 인해 데이터 일관성을 유지하기 어려워질 수 있음
  • 데이터 중복으로 인해 추가, 수정, 삭제 작업이 복잡해지고, 데이터 일관성을 유지하기 위한 추가 작업이 필요함

역정규화 방법

역정규화 그림

2. 후기

---

오랜만에 진행한 SQL는 역시 재미있었던것같습니다! 내일은 모델링으로 돌아오도록 하겠습니다!