데이터베이스 설계, 정규화

용어 표준화

데이터베이스를 설계할 때 단어, 용어의 표준화도 일관성, 유지보수성 목적으로 미리 설정해 두면 좋다.

---

데이터베이스 설계 단계는 E-R 모델과 릴레이션 변환 규칙을 이용하여 진행되며, 총 5단계로 이루어진다. 이 단계들은 선형적으로 진행되지 않고, 필요에 따라 이전 단계로 돌아갈 수 있다.

E-R 모델과 릴레이션 변환 규칙

1. 요구 사항 분석

• 목적 파악
  • 데이터베이스의 사용 목적 이해 및 정의
• 데이터 종류 및 처리 방법 수집 및 분석
  • 필요한 데이터와 처리 방법 파악
  • 관련 요구 사항을 수집
• 요구사항 명세서 작성
  • 수집된 요구 사항 문서화 후 명세서 작성
• 사용자 범위 결정
  • 주요 사용자와 그들의 역할 식별
• 업무 분석 및 데이터 파악
  • 사용자의 업무 프로세스를 분석
  • 필요한 데이터와 관계 파악

2. 개념적 설계

• DBMS에 독립적인 개념적 데이터 모델 설계
• 요구 사항 명세서 E-R 모델로 표현
• 개체 및 속성 추출
  • 실세계에 존재하는 독립적이고 식별 가능한 개체 찾기
  • 각 개체의 특성을 나타내는 속성 분류
• 관계 추출
  • 개체 간 연관성 식별
  • 개체 간 관계 파악
  • 관계의 유형 결정: 일대일, 다대다 등
  • 참여 특성 결정 (예: 하나의 과목에 여러 학생이 수강)
• E-R 다이어그램 작성
  • 개체: 사각형
  • 속성: 타원, 개체와 연결
  • 관계: 마름모, 개체와 연결
  • 기본 키: 밑줄
  • 외래 키: 이탤릭체

3. 논리적 설계

• DBMS에 적합한 논리적 구조 설계 (관계 데이터 모델 활용)
• E-R 다이어그램 릴레이션 스키마로 변환
• 릴레이션 스키마 변환 규칙 적용
  • 모든 개체는 릴레이션으로 변환
    • 개체 유형은 릴레이션 형태의 테이블로 변환
    • 개체 유혀의 속성은 릴레이션의 열로 변환
    • 예: 학생 개체 유형을 'Students' 릴레이션으로 변환
  • 다대다 관계는 별도의 릴레이션으로 변환
    • 다대다 관계는 별도의 연결 테이블, 중간테이블로 변환
    • 이 테이블은 관련된 개체 유형의 기본키를 외래키로 포함
    • 예: 학생과 과목 사이의 다대다 관계를 'Enrollments; 릴레이션으로 변환
  • 일대다 관계에서
    • "다"쪽 릴레이션에 "일"쪽 릴레이션의 기본키를 외래키로 추가
    • 약한 개체 참여 시 외래키와 약한 개체의 기본키 함께 사용하여 새로운 기본키 구성
    • 예: 학생과 학점 사이의 일대다 관계에서 'Grades' 릴레이션에 학생의 기본키를 외래키로 추가
  • 일대일 관계에서 필수 참여하는 개체의 릴레이션에 외래키 추가
    • 한 쪽 개체가 다른 쪽 개체에 종속적일 때, 종속되는 개체의 릴레이션에 다른 쪽 개체의 기본키를 외래키로 추가
    • 모든 개체가 필수 참여인 경우 릴레이션을 합침
    • 예: 학생과 학생증 사이의 일대일 관계에서 'StudentCards' 릴레이션에 학생의 기본키를 외래키로 추가
  • 다중 값 속성은 별도의 릴레이션으로 변환
    • 하나의 엔티티에서 여러 개의 값을 가질 수 있는 속성
    • 별도의 릴레이션으로 변환, 원래 개체와 새로 생성된 릴렝션 간의 일대다 관계 형성
    • 예: 학생이 여러 전화번호를 가질 수 있는 경우 'PhoneNumbers' 릴레이션을 생성하고 학생의 기본키를 외래키로 사용하여 연결

4. 물리적 설계:

• DBMS 구현 가능 구조: 물리적 데이터 모델 설계
• 인덱스, 테이블 파티셔닝, 클러스터링 등 최적화 기법 적용

5. 구현:
   SQL문 작성 및 DBMS에서 실행하여 데이터베이스 생성
   데이터 입력, 수정, 삭제, 조회 등 데이터베이스 관리 작업 수행

정규화

• 데이터베이스 설계의 결과물 검증
• 함수 종속성을 이용해 릴레이션 분해
• 이상현상을 제거 가능
  • 삽입 이상: 데이터 삽입 시 불필요한 데이터가 포함
  • 갱신 이상: 중복 데이터가 있을 때 일부의 튜플만 수정되어 데이터 불일치
  • 삭제 이상: 튜플 삭제 시 필요한 데이터 소실
  • 함수 종속성: 속성 A가 B의 값을 결정할 때 속성 B가 속성 A에 함수적으로 종속
  • A가 B에 종속이라고 해서 B가 A에 종속은 아님
    • 결정자: A
    • 종속자: B
    • $\{\}\rightarrow\{attribute\}$: 속성의 값이 항상 하나일 때
    • trivial 함수 종속: $X \rightarrow Y$ 일 때 $Y \sub X$
    • non-trivial 함수 종속: 위가 아닐 때
    • 부분 함수 종속: 복합키가 있는 릴레이션에서 발생하는 종속 관계. 복합키의 일부분에 종속되는 속성
    • 완전 함수 종속: 속성 B가 속성 집합 A 전체에만 종속될 때

정규형

• 정규화의 정도

• 차수가 높아질수록 제약 조건이 많음

• 제 1 정규형: 모든 속성의 도메인이 원자 값으로 구성

  • 고객ID	상품ID	상품명	카테고리	주문ID	주문일자
    1	101	티셔츠	의류	1001	2023-01-01
    1	102	바지	의류	1002	2023-01-02
    2	103	신발	신발	1003	2023-01-03
    2	101	티셔츠	의류	1004	2023-01-04

• 제 2 정규형: 제 1 정규형에 속하며, 기본키가 아닌 모든 속성이 기본키에 완전 함수 종속

  • 상품명과 카테고리는 상품 ID에만 종속되므로 분리

  • 고객ID	상품ID	주문ID	주문일자
    1	101	1001	2023-01-01
    1	102	1002	2023-01-02
    2	103	1003	2023-01-03
    2	101	1004	2023-01-04

  • 상품ID	상품명	카테고리
    101	티셔츠	의류
    102	바지	의류
    103	신발	신발

• 제 3 정규형: 제 2 정규형에 속하며, 기본키가 아닌 모든 속성이 기본키에 이행적 함수 종속이 되지 않음

  • 이행적 함수 종속: $a\rightarrow b \rightarrow c$
    • 이 때 c가 a에 이행적 함수 종속

• 보이스/코드 정규형: 함수 종속 관계에서 모든 결정자가 후보키여야 합니다.

• 제 4 정규형: 함수 종속이 아닌 다치 종속을 제거해야 합니다.

• 제 5 정규형: 후보키를 통하지 않는 조인 종속을 제거해야 합니다.

정규화를 통해 릴레이션의 속성들이 관련성을 갖도록 분해하면, 데이터의 일관성과 무결성 유지 및 이상현상 방지 가능