데이터베이스설계.md

데이터베이스 설계

• 데이터베이스 설계

  • 사용자의 다양한 요구 사항을 고려하여 데이터베이스를 생성하는 과정

• 관계 데이터베이스의 대표적인 설계 방법

  • E-R 모델과 릴레이션 변환 규칙을 이용한 설계

• E-R 모델과 릴레이션 변환 규칙을 이용한 설계의 과정

요구 사항 분석

• 설계 1단계: 요구 사항 분석

  • 사용자의 요구 사항을 수집하고 분석하여 개발할 데이터베이스의 용도를 파악
  • 업무에 필요한 데이터가 무엇인지, 그 데이터에 어떤 처리가 필요한지 등을 고려
  • 개발하기 전에 기획안을 보고 기획자 - 개발자의 소통하는 단계라고 생각하면 될 것 같다.

  • 요구 사항 분석 예 - 마트 데이터베이스

    

위의 요구 사항을 보고서 어떤 테이블이 필요하고 테이블 안에 어떤 컬럼이 필요하며(PK는 무엇인지 등), 테이블의 관계는 어떻게 되며(FK는 어떻게 되는지 등을 생각할 수 있다.)

개념적 설계

• 설계 2단계: 개념적 설계

  • DBMS에 독립적인 개념적 스키마 설계 (개념적 스키마: E-R 다이어그램)

  • 요구사항 분석결과를 기반으로 중요한 개체를 추출하고 개체간의 관계를 결정하여 E-R 다이어그램으로 표현

  • 작업 과정

    • STEP 1) 개체 추출, 각 개체의 주요 속성과 키 속성 찾기
    • STEP 2) 개체 간의 관계 결정
    • STEP 3) E-R 다이어그램으로 표현

  • (STEP 1) - 개체와 속성 추출

    • 개체: 저장할 만한 가치가 있는 중요 데이터를 가진 사람이나 사물 등을 의미한다.
      • 병원이라면 환저, 의사, 간호사, 수술실, 의료 장비 등이 있다.
    • 개체 추출 방법
      • 요구 사항 문장에서 업무와 관련이 깊은 의미 있는 명사를 최우선으로 찾아보자.
      • 업무와 관련이 적은 일반적이고 광범위한 의미의 명사는 제외

    

    • 7번: 회원이 상품을 주문하면 주문에 대한 주문 번호, 주문수량, 배송지, 주문일자 정보를 유지해야 한다.
      • 개체: 회원, 상품
      • 속성: 주문번호, 주문수량, 배송지, 주문일자
        • 회원이 상품을 주문을 해야 생기는 중요한 정보이기 때문에 회원이나 상품 개체의 속성으로 보기는 어렵고 이후 추출할 특정 관계의 속성으로 봐야할 것 같다.

    

  • (STEP 2) - 관계 추출

    • 관계: 개체 간의 관계를 찾기

      • 찾아낸 관계에 대해 매핑 카디널리티와 참여 특성을 결정하자.
        • 일대일(1:1), 일대다(1:n), 다대다(n:m)
        • 참여 특성: 필수적 참여 / 선택적 참여

    • 6번: 회원은 여러 상품을 주문할 수 있고, 하나의 상품을 여러 회원이 주문할 수 있다.

    • 7번: 회원이 상품을 주문하면 주문에 대한 주문 번호, 주문수량, 배송지, 주문일자 정보를 유지해야 한다.

      • 관계: 주문 (속성: 주문번호, 주문수량, 배송지, 주문일자)

        • 회원 개체와 상품 개체가 맺는 관계는 다대다(n:m) 관계
        • 회원 개체는 관계에 선택적으로 참여 / 상품 개체는 관계에 선택적으로 참여

    • 8번: 각 상품은 한 제조업체가 공급하고, 제조업체 하나는 여러 상품을 공급할 수 있다.

    • 9번: 제조업체가 상품을 공급하면 공급일자와 공급량 정볼르 유지해야 한다.

      • 관계: 공급(공급일자, 공급량)

        • 상품 개체와 제조업체 개체가 맺는 관계는 일대다(1:n) 관계
        • 상품 개체는 관계에 필수적으로 참여(제조되지 않으면 상품이 존재하지 않기 때문) / 제조업체 개체는 관계에 선택적으로 참여

    • 12번: 회원은 게시글을 여러 개 작성할 수 있고, 게시글 하나는 한 명의 회원만 작성할 수 있다.

      • 관계: 작성

        • 회원 개체와 게시글 개체가 맺는 관계는 일대다(1:n) 관계
        • 회원 개체는 관계에 선택적으로 참여 / 게시글 개체는 관계에 필수적으로 참여

• 회원 - 상품 관계

• 상품 - 제조업체 관계

• 회원 - 게시글 관계

• 3개의 E-R 모델 합치기

논리적 설계

• 목적

  • DBMS에 적합한 논리적 스키마 설계
  • 개념적 스키마를 논리적 데이터 모델을 이용해 논리적 구조로 표현
  • 논리적 모델링(데이터 모델링)
    • 일반적으로 관계 데이터 모델을 많이 이용
  • 개넘적 설계 단계(전체적으로 뷰를 보았을 때 어떤 개체들이 필요한지 정함)의 결과물인 E-R 다이어그램을 릴레이션 스키마로 변환
  • 릴레이션 스키마로 변환 후 속성의 데이터 타입, 길이, 넛 값 허용 여부, 기본 값, 제약조건 등을 세부적으로 결정하고 결과를 문서화시킨다.

• E-R 다이어그램을 릴레이션 스키마로 변환하는 규칙

  • 규칙1: 모든 개체는 릴레이션으로 변환한다. (개체를 테이블로 만든다는 뜻이고, 속성은 뭐고 타입은 뭐고 PK는 뭐고 등등)
  • 규칙2: 다대다(n:m) 관계는 릴레이션으로 변환한다.
  • 규칙3: 일대다(1:n) 관계는 외래키로 표현한다.
  • 규칙4:: 일대일(1:1) 관계는 외래키로 표현한다.
  • 규칙5: 다중 값 속성은 릴레이션으로 변환한다.

• 규칙1 - 모든 개체는 릴레이션으로 변환한다.

  • 개체의 이름 - 테이블(릴레이션) 이름
  • 개체의 속성 - 릴레이션의 속성
  • 개체의 키 속성 - 릴레이션의 기본 키
  • 개체의 속성이 복합 속성인 경우에는 복합 속성을 구성하고 있는 단순 속성만 릴레이션의 변환(ex: 전화번호 => 집전화, 핸드폰번호)

• 규칙2 - 다대다 관계는 릴레이션으로 변환한다.

  • E-R 다이어그램의 다대다 관계는 중간에 매핑테이블이 필요하다.
  • 주문 테이블에서 고객 번호 + 상품 번호를 PK로 놓는다면 주문 테이블의 PK가 없어도 될 것 같고, 주문번호라는 PK를 만들거라면 외래키를 굳이 PK를 설정하지 않아도 된다. 만드는 서비스에 어떤 것이 더 나을지 정하면 될 것 같다.

• 규칙3 - 일대다 관계는 외래키로 표현한다.

  • 일대다 관계는 외래키를 포함해서 기본키를 지정한다.
  • 일대다(1:n) 관계에서 1측 개체 릴레이션의 기본키를 n측 개체 릴레이션에 포함시켜 외래키로 지정한다.
  • n측 개체 릴레이션의 외래키를 포함하여 기본키를 지정한다.

• 규칙4 - 일대일 관계는 외래키로 표현한다.

  • E-R 다이어그램의 일대일 관계는 외래키로만 표현
  • 일반적인 일대인 관계는 외래키를 서로 주고받는다.
  • 하지만 위와 같이 저장을 하면 데이터가 중복이 되기 때문에 릴레이션 하나만 만들어서 데이터를 저장해도 될 것 같다.

  • 필수적으로 참여하는 개체 릴레이션만 외래키를 받는다.

    • 만약에 남자는 결혼을 필수로 해야 하고, 여자는 결혼이 필수가 아니라고 가정했을 때는 여자 테이블의 있는 주 키를 남자 테이블에 외래키로 설정해야 한다.
    - 여자 테이블에 외래키 + 결혼날짜 속성을 추가해두면 누구는 결혼을 했고, 누구는 안했기 때문에 비어 있는 것들이 있을텐데 메모리의 낭비가 있을 수 있기 때문에 효율적이지 못하기 때문이다.

  • 모든 개체가 필수적으로 참여하면 릴레이션 하나로 합친다.

    • 관계에 참여하는 개체 릴레이션들을 하나의 릴레이션으로 합쳐서 표현
    

• 규칙5 - 다중 값 속성은 릴레이션으로 변환한다.

  • 부하 직원 속성은 다중 값의 속성이다.
  - 따라서 위와 같이 다중 값으로 속성 하나에 저장하면 릴레이션 특성을 위반하는 것이다. - 이번에는 다중 값을 나눠서 저장을 했는데 어떤 문제가 있을까? `바로 데이터가 불필요하게 중복되어 저장된다는 것이다.` (부하 직원이 엄청나게 많다면 더욱 실감할 수 있을 것이다.) - 따라서 다중 값 속성을 `독립적인 릴레이션`으로 변환하면 불필요한 중복을 제거할 수 있다.

논리적 설계 - 테이블 명세서 작성

이제 릴레이션 스키마를 가지고 속성의 데이터 타입과 길이, 널 값 허용 여부, 기본 값, 제약조건 등을 세부적으로 결정하고 문서화 시킨다.

물리적 설계와 구현

• 하드웨어나 운영체제의 특성의 고려
• 테이블 사이즈, 인덱스, 시스템 테이블 등을 고려한 물리적 용량 확인
• 평균 트랜잭션 처리 속도 등 성능 확인
• 인덱스, View 등에 대한 필요성 고려

• SQL로 작성한 명령문을 DBMS에서 실행하여 데이터베이스를 실제로 생성한다.