23.05.23 / 데이터베이스 설계 & eXERD

[ 데이터베이스 설계 ]

• 사용자의 다양한 요구 사항을 고려하여 데이터베이스를 생성하는 과정
• 실무 능력, 경험에 따라 설계가 다를 수 있음. 단, 좋은 설계와 나쁜 설계는 분명히 존재

[[ 설계 단계 ]]
- 요구 사항 수집 및 분석
- 개념적 > 논리적 > 물리적
- 구현

[ 개념적 ]
- ERD, DBMS에 독립적이다.
- 개체와 속성을 추출한다.
- 업무와 관련이 깊은 명사를 추출한다.
   ex) 마트에서 상품을 구입한다. -> 상품, 마트, 고객, 직원, 관리자
         대학교 학사 관리 -> 사물 : 강의실, 교자재, 행정실 / 사람 : 행정 담당자, 학생, 교수
         쇼핑몰 구현 -> 쇼핑몰, 회원, 아이디, 비밀번호 - 쇼핑몰 : 광범위하고 일반적인 의미는 제외한다.
- 개체의 핵심 속성을 추출한다.
ex) 고객 : 이름, 주소, 연락처 등
      도서 : 도서 이름, 출판사, 단가 등
- 객체 간의 관계를 추출한다.
  관계 : 개체 간 의미있는 연관성
  추출 방법 : 요구 사항 문장에서 개체 간 연관성을 의미있게 표현한 동사를 추출한다.

[ 논리적 ]
- DBMS에 적합한 구조 설계
- 릴레이션 스키마로 변환
- 속성에 대한 세부 내용 결정 : 데이터 타입, 길이, NULL허용 여부, 기본 값, 제약 조건 등등 -> 문서화

< 릴레이션 변환 규칙 >
- 모든 개체는 릴레이션(테이블)으로 변환한다.
- 다 : 다 관계는 릴레이션으로 변환한다.  -> 정규화에 위반되는 다 : 다 관계는 반드시 관계를 재설정해야한다.
  정규화 : 중복을 제거하는 과정
  식별 관계 : 고객 테이블의 고객 번호키와 제품 테이블의 제품 번호키를 합쳐서 주문 테이블의 기본키로 사용할 때 ->
                   고객 한 명이 제품 번호가 같은 제품을 두 개 이상 구매 불가능 / 고객 여러 명이 제품 번호가 같은 제품 구매 불가능
  비식별 관계 : 주문 번호키와 고객 번호키, 제품 번호키가 관련이 없이 만들었을 때 -> 중복 구매, 여러 명 구매 가능
- 1 : 다 관계는 외래키로 표현한다. -> 제조 업체 - 상품 / 여러 상품을 공급한다.
  제조업체(업체명, 연락처, 주소, 담당자) / 상품(상품코드, 상품명, 재고량, 단가, 업체명)
  강한 개체 : 다른 개체의 도움 없이 존재할 수 있다. ex) 영화관 : 상영관(PK : 상영관번호, 이름, 좌석수)
   약한 개체 : 독자적으로 존재할 수 없다. 반드시 상위 개체가 필요함. ex) 영화관 : 상영관 - 좌석(PK : 상영관번호+좌석번호, 등급, 가격)
   * 약한 개체가 참여하는 1 : 다 관계는 외래 키를 포함하여 기본 키로 지정한다.
- 1 : 1 관계는 외래키로 표현한다. (많이 없음)
   일반적인 1 : 1 관계는 외래 키를 서로 주고 받는다.
   필수적으로 참여하는 개체의 릴레이션에 외래 키를 받는다.
   모든 개체가 1 : 1 관계에 필수적으로 참여한다면, 릴레이션을 합칠 수 있다. (남자이름,여자이름,남자나이,여자나이 => 이름, 나이)
- 다중 값 속성은 릴레이션으로 변환한다.

[ 물리적 ]
- 문서화한 내용을 실제로 만든다. CREATE TABLE 테이블_이름

[ 구현 ]  

 

[ eXERD ]

• 데이터 모델링 툴
• F4, F5 : 논리 보드, 물리 모드 변경 가능
• 논리 모드 : 테이블의 정보, comment 작성
• 물리 모드 : 실제 테이블의 이름, 컬럼 작성
• 리버스 엔지니어링 : DBMS의 객체들을 E-R 다이어그램으로 가지고 오는 기능
• 포워드 엔지니어링 : E-R 다이어그램으로 데이터베이스를 모델링한 후 DBMS에서 테이블을 생성, 등록해주는 기능

[[ 정규화 ]]
- 잘 못 설계된 테이블을 수정하여 정상으로 만든는 과정 (기본 원칙 : 하나의 테이블에 중복된 데이터가 없어야한다.)
- 데이터의 중복과 이상 현상을 최소화하기 위해 Normal Form(NF)에 따라서 관계형 데이터베이스를 구성하는 과정
- NF : 정규화되기 위해 지켜야 하는 규칙들
- 정규화 과정은 순차적으로 진행하며, NF를 만족하지 못하면 만족하도록 테이블의 구조를 조정해야 한다.
- 제 1 정규형(1NF)
  릴레이션의 모든 속성의 값(도메인)이 원자 값을 만족한다. (원자 값 : 속성값이 더 이상 논리적으로 분해될 수 없는 값) 
      ex) 취미 컬럼에 여행과 공부 두 개가 존재할 때 원자 값을 만족하지 않는다. (하나의 컬럼에는 하나의 값) 
  반복되는 속성을 제거한다.
- 제 2 정규형(2NF)
  릴레이션이 제 1 정규형 상태, 기본 키가 아닌 속성들이 기본 키에 완전 함수 종속일 때, 제 2 정규형을 만족하는 릴레이션이라고 말 할 수 있다.
  (부분 함수 종속/Partial Dependency 제거) -> 완전 함수 종속인 속성들을 떼서 테이블을 만들어 준다.
     ex)  학생 성적 테이블(PK : 학번, 과목명) -> 학생 테이블(PK : 학번) + 성적 테이블(PK : 과목명)
- 제 3 정규형(3NF)
  릴레이션이 2 정규형을 만족하고, 기본 키가 아닌 모든 속성 간에는 서로 종속될 수 있다.
  이행적 종속 : A -> B , B -> C , A  -> C (A -> C 관계가 잘 못 된 관계인지 판단해야 함)
  PK가 아닌 다른 일반 컬럼에 종속된 컬럼을 다른 테이블로 빼는 작업
- BCNF (강화된 3 정규형)
  모든 결정자는 후보 키에 속해야 한다.
- 반정규화
  시스템의 성능 향상, 개발 및 운영의 편의성을 위해 정규화된 데이터 모델을 통합, 중복, 분리하는 과정으로,
  의도적으로 정규화 원칙을 위배하는 행위
  테이블 통합, 테이블 분할, 중복된 테이블 추가, 중복 속성 추가
  과도한 반정규화는 오히려 성능을 저하시킬  수 있다.

[[ 이상 현상(Anomaly) ]]
- 데이터의 조작(삽입, 수정, 삭제)하는 경우, 이상 현상이 발생한다.  -> 데이터베이스의 일관성 훼손, 무결성이 깨진다.
[ 종류 ]
- 삽입 이상(insertion anomaly)
  릴레이션에 데이터를 삽입 시, 원하지 않는 데이터도 함께 삽입해야하는 현상
- 삭제 이상(deletion anomaly)
  릴레이션에서 튜플 삭제 시, 원하지 않는 값들도 함께 삭제되는 현상(연쇄 삭제)
- 갱신 이상(update anomaly)
  릴레이션의 튜플 속성 값 갱신 시, 일부 튜플의 정보만 갱신되어 정보의 모순이 발생(일관성이 깨짐 -> 데이터 불일치)

[[ 무손실 분해 ]]
릴레이션 분해 -> 이상 현상을 제거하는 과정
분해된 릴레이션을 다시 원래 릴레이션으로 조인할 때, 데이터 손실 없이 원래 릴레이션으로 합쳐지면, 무손실 분해라고 한다.
[규칙]
- 릴레이션의 관계 유지를 위해 분해된 릴레이션에 공통 속성을 한 개 이상 배치해야한다.


[[ 함수적 종속성(Functional Dependency, FD) ]]
- 속성들 간 관련성
- 정규화 진행 전, 테이블을 분석하여 기본 키와 함수 종속성을 파악해야 한다.
- FD를 이용해서 릴레이션을 연관성있는 속성들로만 구성되되도록 분해하여 이상 현상이 발생하지 않는 릴레이션으로 만들어야 한다.
- X(결정자)의 값에 따라서 Y(종속자)의 값이 유일하게 결정되는 것 -> X가 Y를 함수적으로 결정한다. / Y는 X에 함수적으로 의존한다.
  두 집합 사이의 제약 관계를 FD라고 한다. (X : 사번PK / Y : 이름, 직군, 연봉, 부서코드)
- 완전 함수 종속 : 어떤 속성이 기본 키에 대해 완전히 종속적인 경우
- 부분 함수 종속 : 어떤 속성이 기본 키에 대해 부분 종속적인 경우