티스토리 뷰

CS/인터넷DB응용

[인터넷DB응용] 데이터베이스 전체 설계 과정

JeongeunChoi 2023. 6. 9. 11:14

관계형 DB 설계 과정

 

  • 개념적 설계(E-R 모델) - E-R Diagram
    • E-R 모델을 이용해서 E-R 다이어그램을 만든다.
  • 논리적 설계 Step1 - 매핑 룰
  • 논리적 설계 Step2 - 정규화(3NF or BCNF)
  • 물리적 데이터 모델링
    • 데이터베이스를 하드디스크와 같은 저장장치에 저장할 때 어떤 식으로 저장하여 테이블 형태로 사용자들에게 보여줄 것인가를 결정

 

개체 관계 모델(E-R Model)

개체-관계(Entity-Relationship) 모델

  • 현실 세계의 많은 데이터 중에서 관심의 대상이 되는 데이터를 언어보다 좀 더 형식화된 다이어그램을 사용하여 표현한 것
  • 개체들 간의 관계를 나타냄.
    1. 개체 선택: 요구사항 명세서가 필요. 여기서 명사가 해당됨.
    2. 관계: 요구사항 명세서에서 동사에 해당됨.
    3. 속성: 개체를 규정하기 위해 필요한 속성들을 찾아낸다.
  • 관계에서는 Mapping Cardinality가 중요
  • 1976년 Chen에 의해 제안
    • 데이터에 대해 관리자, 사용자, 프로그래머들이 서로 다르게 인식되고 있는 뷰들을 하나로 통합할 수 있는 단일화된 설계안을 만들기 위해서 사용.
    • 서로 다른 뷰들을 충족시킬 수 있는 데이터 처리와 제약 조건 등의 요구사항 들을 정의하기 위해 사용.

 

개체(Entity), 관계(Relation), 속성(Attribute)

  • 업무가 관여하는 어떤 것(ENTITY)
  • 업무가 관여하는 어떤 것 간의 관계(RELATION)
  • 어떤 것이 가지는 성격(ATTRIBUTE)
  • 예문: 이도령과 성춘향이 있고 둘은 사랑하는 사이다. 이도령은 키가 186이고, 성격이 까칠하고, 성춘향은 키가 163이고 세심하고 활달한 성격이다.
개념 예문에 해당하는 내용 관계형 모델링
어떤 것 이도령, 성춘향 엔티티(ENTITY)
어떤 것 간의 관계 사랑하는 사이 관계(RELATION)
어떤 것의 성격 "이도령", "키가 186", "까칠"
"성춘향", "키가 163", "세심, 활달"		 속성(ATTRIBUTE)

 

개체-관계(Entity-Relationship) 모델의 구성 요소

  • 개체
    • 실제 세상에 존재하는 객체들
    • 물리적으로 존재하기도 하고 개념적으로 존재하는 것들도 표현 가능
  • 개체 집합
    • 같은 종류의 개체들을 모아 놓은 것
    • 예) 학생 개체 집합, 교수 개체 집합, 과목 개체 집합
    • E-R Diagram에서는 개체 집합을 직사각형으로 표시
  • 속성
    • 개체를 구성하거나 개체의 특징을 나타내는 여러 가지 요소들
    • 개체는 속성의 집합으로 표현한다.
    • E-R Diagram에서는 속성을 원 모양으로 표시
  • 도메인
    • 속성이 가질 수 있는 값의 범위
    • 예) 학생 객체 집합의 이름이라는 속성은 한글 문자 5개 이하로 표현, 성적 개체 집합의 중간고사 점수 속성은 0이상 100이하의 정수
  • 관계
    • 개체 사이의 연관성을 나타내는 요소
    • Mapping Cardinality가 존재한다.
  • 관계 집합
    • 같은 형태의 관계들을 모아놓은 것
    • E-R Diagram에서는 관계 집합을 마름모 모양으로 표시

 

사상 크기(Mapping Cardinality)

  • 관계 집합의 양 쪽에 있는 개체 집합 사이의 정수 비율
  • 1:1, 1:N, M:1, M:N
  • 주어진 조건에 따라 결정된다.
  • 예) 수강 신청
    • 조건1 - 어떤 학교는 엘리트 전문 학교로 수업 하나당 한 명의 학생만 수강 신청을 할 수 있고, 한 학생은 한 과목만 수강할 수 있다.
      • 학생 : 과목 = 1 : 1
    • 조건2 - 어떤 학교는 1:1 과외 전문 학교로 모든 수업은 1:1 과외 형식으로 진행된다. 각 학생은 여러 과목을 수강할 수 있다.
      • 학생 : 과목 = 1 : N
    • 조건3 - 어떤 학교는 집중 교육 전문 학교로 모든 학생은 한 과목만 수강할 수 있다. 각 수업에는 여러 명의 학생이 들어올 수 있다.
      • 학생 : 과목 = N : 1
    • 조건4 - 일반적으로 대학교에서는 한 학생이 여러 과목을 수강할 수 있고 한 과목에 여러 명의 학생이 들어온다.
      • 학생 : 과목 = M : N

 

E-R Diagram 표준 도형

 

 

E-R Diagram 해석하기

 

 

매핑 룰(Mapping Rule)

  • E-R 모델링(개념적 데이터 모델링)의 결과물인 E-R Diagram을 이용하여 관계형 모델링(논리적 데이터 모델링)을 위해 테이블로 변환하는 과정
  • 사상 크기에 따라 테이블 개수가 정해진다.(불필요한 중복 방지)

 

 1:1

  • 하나 또는 두 개의 테이블, 두 테이블로 나눌 경우, 기본 키가 같은 경우가 많다. 이 경우 공통 속성은 서로가 서로의 외래 키가 되는데 실제 설정은 하나만 한다.
학생명단 : 생활기록부 = 1 : 1
학생명단 = {학번, 이름, 성별, 생년월일, 휴대폰번호, 주소, 우편번호}, PK ={학번}
생활기록부 = {학번, 취미, 특기, 통학수단, 음주, 흡연}, PK = {학번}, FK = {학번}

하나의 테이블로 만들어도 되지만 편의상 두 개의 테이블로 나눈 경우
학생 명단 테이블의 {학번}을 외래 키로 잡을 수도 있다. 하지만 두 테이블의 {학번}을 모두 외래 키로 설정할 수는 없다. 이렇게 되면 참조 무결성에 의해 데이터 입력에 문제가 생긴다.


조건1 – 학생 : 과목 = 1 : 1
학생_과목 = {학번, 이름, 주민등록번호, 연락처, 과목번호, 과목이름, 개설학과, 학점}, PK = {학번} ({과목번호}도 후보 키임)

학생 = {학번, 이름, 주민등록번호, 연락처}, PK = {학번}
과목 = {과목번호, 과목이름, 개설학과, 학점, 학번}, PK = {과목번호}, FK = {학번}
또는  
학생 = {학번, 이름, 주민등록번호, 연락처, 과목번호}, PK = {학번}, , FK = {과목번호}
과목 = {과목번호, 과목이름, 개설학과, 학점}, PK = {과목번호}

 

1:N

  • 두 개의 개체 집합으로 가각 테이블을 만든다. 1의 기본키가 N의 외래 키가 된다.
조건2 – 학생 : 과목 = 1 : N
학생 = {학번, 이름, 주민등록번호, 연락처}, PK = {학번}
과목 = {과목번호, 과목이름, 개설학과, 학번, 학점}, PK = {과목번호}, FK = {학번}
조건3 – 학생 : 과목 = N : 1
학생 = {학번, 이름, 주민등록번호, 연락처, 과목번호, 학점}, PK = {학번}, FK = {과목번호}
과목 = {과목번호, 과목이름, 개설학과}, PK = {과목번호}

 

 M:N

  • 두 개의 개체 집합으로 테이블을 만들고 관계 집합에 해당하는 테이블을 만든다. 각 개체 집합으로 만든 테이블의 기본 키가 관계 집합으로 만든 테이블의 외래 키가 된다.
조건4 – 학생 : 과목 = M : N
학생 = {학번, 이름, 주민등록번호, 연락처}, PK = {학번}
과목 = {과목번호, 과목이름, 개설학과}, PK = {과목번호}
수강 = {학번, 과목번호, 학점}, PK = {학번, 과목번호}, FK = {학번}, FK = {과목번호}

 

매핑 룰을 이용하여 테이블을 만들면 논리적 데이터 모델링까지 끝난 것일까?

 

  • 꽤 괜찮은 결과가 나오기는 한다.
  • 하지만 불필요한 중복이 완전히 배제되었다고 보기는 힘들다.
  • 예를 들어, 개체 집합과 관계 집합의 사상 크기가 M:N이 되는 경우가 있다.
  • 예) 한 번에 여러 개의 물건을 주문할 수 있는 쇼핑몰 데이터베이스
    • 회원 개체와 상품 개체 사이의 관계인 주문과 상품 사이에 M:N 관계가 되어 주문의 속성에 해당하는 배송 주소, 배송일, 배송 연락처 등의 정보가 불필요하게 중복될 수 있다.
    • 따라서 매핑 룰을 이용하여 테이블을 만든 후에도 논리적 데이터 모델링(정규형에 따른 정규화) 과정을 거쳐야 한다.

 

✖️ 항상 생각할 점 : 데이터가 쓸데없이 중복되어서는 안된다.

 

 

  • 회원={회원번호, 이름, 연락처, 주소}, PK={회원번호}
  • 주문={회원번호, 상품번호, 주문번호, 주문일, 배송주소}, PK={주문번호}, FK={회원번호}, FK={상문번호}
  • 상품={상품번호, 상품명, 단가}, PK={상품번호}

 

논리적 설계 Step2 - 정규화(3NF or BCNF)

정규형

  • 관계형 DB의 정규형: 관계형 DB의 형태를 정의해 놓은 것
  • 정규형은 함수적 종속을 이용해서 정의하며 정규형에 맞지 않는 스키마는 Decomposition을 이용해서 정규형에 맞게 고친다. -> 정규화
  • 제 1정규형
    • 관계형 DB의 정의. "모든 속성은 simple하고 single-valued"이다.
  • 어떤 데이터베이스 스키마에 속한 모든 테이블 스키마가 어떤 정규형에 속할 때, 그 데이터베이스 스키마는 그 정규형에 있다고 한다.

 

 

보이스-카드 정규형(BCNF)

  • F+에 있는, a->b 형태로 된 모든 함수적 종속(FD) 각각에 대하여, 다음 조건 가운데 하나라도 만족하면, 주어진 함수적 종속의 집합 F에 관하여 스키마 R은 BCNF에 속한다.
    • a->b가 뜻 없는 함수적 종속
    • a가 스키마 R의 수퍼키이다.
  • BCNF에 속하지 않는 스키마 R이 있는 한, 나누기 과정을 되풀이한다.
  • BCNF 나누기 알고리즘의 특징
    • 가짜 투플을 만들지 않는 나누기이다.
    • 함수적 종속을 유지한다는 보장은 없다.
    • 한 번의 나누기 과정에서 두 개의 스키마로 나누어진다.
    • 나누기 한 번으로 끝난다는 보장이 없다.
    • 모든 테이블 스키마가 BCNF에 있을 때까지 나누어야 한다.

 

예시 1)  수강: {학번, 과목번호, 성적등급, 이름}, PK: {학번, 과목번호}

SFDc
1. FD1: {학번, 과목번호} -> {성적등급, 이름}
2. FD2: {학번} -> {이름}

 

FD1만 있다면 BCNF 조건을 만족한다. 모든 FD가 조건을 만족해야 한다.
{학번}은 수퍼키가 아니기 때문에 BCNF 조건을 만족하지 않는다.

 

기준 FD: FD2 -> 문제가 되는 FD

R1: {학번, 이름}, PK: {학번},

  • FD2(알파가 수퍼키가 돼버림, 쪼갬으로 인해 문제를 일으키는 FD가 더 이상 문제를 일으키지 않게 됨.)

R2: {학번, 과목번호, 성적등급}, PK: {학번, 과목번호}

 

테이블을 쪼갠다고 해서 새로운 FD가 나타나지 않는다. 원래의 FD가 어떻게 갈라지는지만 확인하면 된다.

 

✖️ SFDc: 규범적 덮개를 뜻함.

 

예시 2) R = {학번, 과목번호, 이름, 주소, 성적}, PK = {학번, 과목번호}

SFDc :
1. {학번, 과목번호} -> {성적}
2. {학번} -> {이름, 주소}

 

2번 FD 때문에 스키마 R은 BCNF에 있지 않다.

 

R1 = {학번, 이름, 주소}, PK = {학번}
R2 = {학번, 과목번호, 성적}, PK = {학번, 과목번호}

 

예시 3) 수강_학생 = {학번, 과목번호, 과목이름, 학과번호, 이름, 집전화}

PK  = {학번, 과목번호}

SFDc
FD1 : {과목번호} -> {과목이름}
FD2 : {학번} -> {학과번호, 이름, 집전화}

 

FD2의 알파가 수퍼키가 아니기 때문에 주어진 스키마는 BCNF에 있지 않다.(O) -> 한개만 적으면 됨.

  • 아래와 같은 표현 안됨.
    • FD1의 알파가 수퍼키가 아니고 FD2의 알파가 수퍼키가 아니기 때문에 스키마는 BCNF에 있지 않다.(X)

R1 = {학번, 학과번호, 이름, 집전화}, PK = {학번}

R2 = {과목번호, 과목이름}, PK = {과목번호}

 

제3정규형(3NF)

  • F+에 있는, a->b 형태로 된 모든 함수적 종속(FD) 각각에 대하여, 다음 조건 가운데 하나라도 만족하면, 주어진 함수적 종속의 집합 F에 관하여 스키마 R은 3NF에 속한다.
    • a->b 가 뜻 없는(trivial) 함수적 종속.
    • a가 스키마 R의 수퍼키이다.
    • b가 후보 키의 일부이다.
  • 이행적 종속 관계를 갖지 않는다.
  • 3NF에 속하지 않는 스키마 R에 대해 다음 나누기 과정을 수행한다.
    • 규범적 카버(=덮개) SFDc에 있는 형태의 모든 함수적 종속 각각에 대하여
      • 가) 만일 ab로 이루어진 스키마가 이미 결과에 들어가 있지 않으면, ab로 이루어진 스키마를 결과에 더한다.
      • 나) R의 후보 키로 된 스키마가 이미 결과에 들어가 있지 않으면, 후보 키로 된 스키마를 결과에 넣는다.
      • 위의 가)와 나)에서 만들어진 스키마의 집합이, 3NF으로 나눈 결과이다.
  • 3NF 나누기 알고리즘의 특징
    • 가짜 투플을 만들지 않는 나누기이다.
    • 함수적 종속을 유지하는 나누기이다.
    • 한 번의 나누기 과정에서 두 개 이상의 스키마로 나누어진다.
    • 나누기 한 번으로 끝난다.
    • 한 번만 나누기를 잘하면 나뉘어진 스키마들은 3NF에 있음을 보장한다.
  • 모든 FD에 대해 테이블을 만든다.

 

예시 1) R = {학번, 과목번호, 이름, 주소, 성적}, PK = {학번, 과목번호}

SFDc :
1. {학번, 과목번호} -> {성적}
2. {학번} -> {이름, 주소}

 

2번 FD 때문에 스키마 R은 3NF에 있지 않다.

 

  • 규범적 덮개에 있는 함수적 종속들의 왼쪽과 오른쪽으로 이루어진 스키마들을 만든다.
    • R1 = {학번, 과목번호, 성적}, PK = {학번, 과목번호}
    • R2 = {학번, 이름, 주소}, PK = {학번}

 

  • R의 후보키를 포함하는 스키마가 있는지 확인한다. R1에 R의 후보키가 포함되어 있으므로 R의 후보키로 이루어진 스키마는 만들 필요가 없다.

 

R1, R2

 

예시 2) 수강_학생 = {학번, 과목번호, 과목이름, 학과번호, 이름, 집전화}, PK = {학번, 과목번호}

SFDc
1. {과목번호} -> {과목이름}
2. {학번} -> {학과번호, 이름, 집전화}

 

2번 FD 때문에 스키마 수강_학생은 3NF에 있지 않다.

 

  • 규범적 덮개에 있는 함수적 종속들의 왼쪽과 오른쪽으로 이루어진 스키마들을 만든다.
    • R1 = {과목번호, 과목이름}, PK = {과목번호}
    • R2 = {학번, 학과번호, 이름, 집전화}, PK = {학번}

 

  • 수강_학생의 후보키를 포함하는 스키마가 있는지 확인한다. 없으므로 수강_학생의 후보키로 이루어진 스키마를 하나 더 만든다.
    • R3 = {학번, 과목번호}, PK = {학번, 과목번호}

 

R1, R2, R3

 

설계 실습

BCNF

수강_학생 = {학번, 과목번호, 과목이름, 학과번호, 이름, 집전화}
PK = {학번, 과목번호}

주어진 FD
FD1 : {과목번호} -> {과목이름}
FD2 : {학번} -> {학과번호, 이름, 집전화}

 

FD1의 과목번호가 수퍼키가 아니다.(FD1까지 적으면 안됨. 하나만!!!)

  • BCNF은 조건이 모든 FD가 만족해야 하므로 하나만 잘못되면 바로 수강_학생 스키마는 BCNF에 속하지 않는다.

R1={과목번호, 과목이름}, PK={과목번호}
R2={학번, 과목번호, 학과번호, 이름, 집전화}, PK={학번, 과목번호}

  • FD는 새로 만들어지지 않으므로 FD1이 R1에서 발견됨을 확인
  • FD2의 학번이 수퍼키가 아니다.

R2-1={학번, 학과번호, 이름, 집전화}, PK={학번}

R2-2={학번, 과목번호}, PK={학번, 과목번호}

 

3NF

수강_학생 = {학번, 과목번호, 과목이름, 학과번호, 이름, 집전화}
PK = {학번, 과목번호}

주어진 FD
FD1 : {과목번호} -> {과목이름}
FD2 : {학번} -> {학과번호, 이름, 집전화}

 

1단계: 규범적 덮개에 있는 알파 베타로 스키마를 만든다.

2단계: 1단계에서 만들어진 스키마에 후보키를 포함하는 스키마가 있으면 넘어가고 없으면 하나 더 만든다.

3단계: 만들어진 스키마들 중에서 한쪽이 다른쪽에 중복되는게 없는지 확인한다.

  • BCNF처럼 다시 정규화를 점검할 필요가 없다.

1단계

R1={과목번호, 과목이름}, PK={과목번호}

R2={학번, 학과번호, 이름, 집전화}, PK={학번}

2단계

R3={학번, 과목번호}, PK={학번, 과목번호}

 

BCNF

수강_학생={학번, 주번, 과목번호, 과목이름, 이름, 성적등급}
PK={학번, 과목번호}, CK={주번, 과목번호}

주어진 FD
FD1 : {학번} -> {주번, 이름}
FD2 : {주번} -> {학번}
FD3 : {과목번호} -> {과목이름}
FD4 : {학번, 과목번호} -> {성적등급}

 

FD1의 학번이 수퍼키가 아니다.

R1={학번, 주번, 이름}, PK={학번}

R2={학번, 과목번호, 과목이름, 성적등급}, PK={학번, 과목번호}

 

FD3의 과목번호가 수퍼키가 아니다. R2가 BCNF에 있지 않다.

R2-1={과목번호, 과목이름}, PK={과목번호}

R2-2={학번, 과목번호, 성적등급}, PK={학번, 과목번호}

 

3NF

수강_학생={학번, 주번, 과목번호, 과목이름, 이름, 성적등급}
PK={학번, 과목번호}, CK={주번, 과목번호}

주어진 FD
FD1 : {학번} -> {주번, 이름}
FD2 : {주번} -> {학번}
FD3 : {과목번호} -> {과목이름}
FD4 : {학번, 과목번호} -> {성적등급}

FD1의 알파가 수퍼키가 아니고 FD1의 베타가 후보키가 아니다........?

R1={학번, 주번, 이름}, PK={학번}

R2={주번, 학번}, PK={주번}

R3={과목번호, 과목이름}, PK={과목번호}

R4={학번, 과목번호, 성적등급}, PK={학번, 과목번호}

 

R2는 R1에 포함되므로 삭제

 

R1={학번, 주번, 이름}, PK={학번}

R2={과목번호, 과목이름}, PK={과목번호}

R3={학번, 과목번호, 성적등급}, PK={학번, 과목번호}

 

BCNF

학생_지도교수 = {학번, 학과번호, 교수번호}
PK = {학번, 학과번호}

주어진 FD
FD1 : {교수번호} -> {학과번호}
FD2 : {학번, 학과번호} -> {교수번호}

 

FD1의 교수번호가 수퍼키가 아니다.

R1={교수번호, 학과번호}, PK={교수번호}

R2={학번, 교수번호}, PK={학번, 교수번호}

 

3NF

학생_지도교수 = {학번, 학과번호, 교수번호}
PK = {학번, 학과번호}

주어진 FD
FD1 : {교수번호} -> {학과번호}
FD2 : {학번, 학과번호} -> {교수번호}

 

제3정규형을 만족한다.

 

BCNF

주문 : {주문번호, 주문상세번호, 회원번호, 상품번호, 주문일, 배송주소, 배송연락처, 개수}
PK : {주문상세번호}, FK : {회원번호}, FK : {상품번호}

주어진 FD
FD1 : {주문상세번호} -> {주문번호, 상품번호, 개수}
FD2 : {주문번호} -> {회원번호, 주문일, 배송주소, 배송연락처}

 

FD2의 주문번호가 수퍼키가 아니다.

R1={주문번호, 회원번호, 주문일, 배송주소, 배송연락처}, PK={주문번호}
R2={주문번호, 주문상세번호, 상품번호, 개수}, PK={주문상세번호}

 

3NF

주문 : {주문번호, 주문상세번호, 회원번호, 상품번호, 주문일, 배송주소, 배송연락처, 개수}
PK : {주문상세번호}, FK : {회원번호}, FK : {상품번호}

주어진 FD
FD1 : {주문상세번호} -> {주문번호, 상품번호, 개수}
FD2 : {주문번호} -> {회원번호, 주문일, 배송주소, 배송연락처}

 

FD1의 알파가 수퍼키가 아니고 FD2의 베타가 후보키의 일부가 아니다..?

R1={주문번호, 회원번호, 주문일, 배송주소, 배송연락처}, PK={주문번호}
R2={주문번호, 주문상세번호, 상품번호, 개수}, PK={주문상세번호}

 

규범적 덮개 만들기부터 BCNF까지

주어진 스키마
{주문번호, 회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}
PK={주문번호}

주어진 FD
FD1: {주문번호} -> {주문번호, 회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}

주어진 스키마와 FD로 판단할 수 있는 특별한 규칙
한 번에 여러 상품을 주문했을 때, 상품 하나마다 주문번호를 다르게 부여한다.

 

FD1: {주문번호} -> {주문번호, 회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}

FD2: {회원번호} -> {회원이름, 회원주소, 회원연락처}

FD3: {상품번호} -> {상품이름}

FD1': {주문번호} -> {회원번호, 상품번호, 주문개수, 배송연락처, 배송주소}

규범적덮개: FD1', FD2, FD3

 

FD2의 회원번호가 수퍼키가 아니다.

R1={주문번호, 회원번호, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소}, PK={주문번호}

R2={회원번호, 회원이름, 회원주소, 회원연락처}, PK={회원번호}

 

FD1: {주문번호} -> {회원번호, 상품번호, 주문개수, 배송연락처, 배송주소}

FD2: {회원번호} -> {회원이름, 회원주소, 회원연락처}

FD3: {상품번호} -> {상품이름}

규범적덮개: FD1, FD2, FD3

 

FD3의 상품번호가 수퍼키가아니다.

R1={주문번호, 회원번호, 상품번호, 주문개수, 배송연락처, 배송주소}, PK={주문번호}

R2={회원번호, 회원이름, 회원주소, 회원연락처}, PK={회원번호}

R3={상품번호, 상품이름}, PK={상품번호}

 

FD1: {주문번호} -> {회원번호, 상품번호, 주문개수, 배송연락처, 배송주소}

FD2: {회원번호} -> {회원이름, 회원주소, 회원연락처}

FD3: {상품번호} -> {상품이름}

규범적덮개: FD1, FD2, FD3

 

결정자가 모두 후보키이므로 BCNF를 만족한다.

 

R1={주문번호, 회원번호, 상품번호, 주문개수, 배송연락처, 배송주소}, PK={주문번호}

R2={회원번호, 회원이름, 회원주소, 회원연락처}, PK={회원번호}

R3={상품번호, 상품이름}, PK={상품번호}

 

주어진 스키마
학생_지도교수 : {학번, 학생이름, 학생연락처, 학생주소, 학과번호, 학과이름, 학과전화번호, 교수번호, 교수이름, 교수연락처}

주어진 FD
FD1 : 

주어진 스키마와 FD로 판단할 수 있는 특별한 규칙
이 학교는 학생들이 복수전공을 신청할 수 있다.
학생들이 복수전공을 신청할 경우 지도교수는 학과별로 한 명씩 배정된다.
교수는 하나의 학과에만 소속되어 있다.

 

PK: {학번, 학과번호}, CK: {{학번, 학과번호}, {학번, 교수번호}}

FD1: {학번} -> {학생이름, 학생연락처, 학생주소}

FD2: {학과번호} -> {학과이름, 학과전화번호}

FD3: {교수번호} -> {학과번호, 학과이름, 학과전화번호, 교수이름, 교수연락처}

FD4: {학번, 학과번호} -> {교수번호, 교수이름, 교수연락처}

 

FD1의 학번이 수퍼키가 아니다.

R1: {학번, 학과번호, 학과이름, 학과전화번호, 교수번호, 교수이름, 교수연락처}, PK={학번, 학과번호}

R2: {학번, 학생이름, 학생연락처, 학생주소}, PK={학번}

 

FD1: {학번} -> {학생이름, 학생연락처, 학생주소}

FD2: {학과번호} -> {학과이름, 학과전화번호}

FD3: {교수번호} -> {학과번호, 학과이름, 학과전화번호, 교수이름, 교수연락처}

FD4: {학번, 학과번호} -> {교수번호, 교수이름, 교수연락처}

 

FD2의 학과번호가 수퍼키가 아니다.

R1: {학번, 학과번호, 교수번호, 교수이름, 교수연락처}, PK={학번, 학과번호}

R2: {학번, 학생이름, 학생연락처, 학생주소}, PK={학번}

R3: {학과번호, 학과이름, 학과전화번호}, PK={학과번호}

 

FD1: {학번} -> {학생이름, 학생연락처, 학생주소}

FD2: {학과번호} -> {학과이름, 학과전화번호}

FD3: {교수번호} -> {교수이름, 교수연락처}

FD4: {학번, 학과번호} -> {교수번호, 교수이름, 교수연락처}

 

FD3의 교수번호가 수퍼키가 아니다.

R1: {학번, 학과번호, 교수번호}, PK={학번, 학과번호}

R2: {학번, 학생이름, 학생연락처, 학생주소}, PK={학번}

R3: {학과번호, 학과이름, 학과전화번호}, PK={학과번호}

R4: {교수번호, 교수이름, 교수연락처}, PK={교수번호}

 

결정자가 모두 후보키이므로 BCNF를 만족한다.

 

데이터베이스 설계 과정 정리

온라인 쇼핑몰 데이터베이스

  • 요구 분석
    • 이 쇼핑몰은 회원 가입을 받아서 회원들만 상품을 주문한다.
    • 한 종류의 상품은 하나의 거래처로부터 구입한다. 상품 구입 내역을 굳이 기록하지는 않는다.
    • 한 번의 주문에 여러 종류의 상품을 주문할 수 있다.(Hint. 이런 경우 한 번의 주문에 붙이는 주문번호와 하나의 주문 내에서도 상품의 종류에 따라 주문상세번호를 따로 붙여주는 게 좋다.)
  • 해야 할 일들
    • 개체 집합과 관계 집합들을 찾아보자.
    • 각 개체 집합과 관계 집합들마다 필요한 속성들을 찾아보자.(속성들은 현실에 비추어서 스스로 찾아보자.)

 

온라인 쇼핑몰 데이터베이스 설계 과정

  • 개체
    • 회원: 회원번호, 회원이름, 생년월일, 주소, 전화번호, 주민등록번호, 가입일, 회원구분
    • 상품: 상품번호, 상품명, 공급단가, 판매단가, 재고량
    • 거래처: 거래처번호, 거래처명, 주소, 전화번호, 담당자명
  • 관계
    • 주문: 주문번호, 주문상세번호, 주문일, 배송주소, 배송연락처, 개수
    • 구입
  • 특이사항
    • 한 번의 주문에 여러 개의 상품을 주문할 수 있다.
    • 한 종류의 상품은 하나의 거래처로부터 구입한다.
    • 구입 내역은 굳이 기록하지 않는다.
    • 주문번호는 한 회원이 주문을 할 때마다 붙는 번호이고 주문상세번호는 주문번호와 같은 주문 내에서 상품마다 다르게 붙는 번호의 조합이다.
      • 예) 한 사람이 한 번에 세 종류의 상품을 주문했을 때 주문번호가 15라면 주문상세번호는 15-1, 15-2, 15-3이 되는 것이다.
  • 해야 할 일들
    1. E-R Diagram 그리기
    2. 사상 크기를 따져본 다음 테이블들을 만들기
      • 1:1이면 하나, 1:N이면 둘, M:N이면 세 개의 테이블
      • 테이블마다 기본 키와 외래 키를 정의한다.
    3. 여기까지 한 다음 각각의 테이블에서 함수적 종속들을 찾아서 규범적 덮개 형태로 정리한다.
    4. BCNF나 3NF 중 적절한 정규형을 선택하여 정규형에 맞는지 검사하고 정규형에 맞지 않으면 정규화를 한다.

 

  • E-R Diagram 그리기

  • 회원 : 상품 = M : N
  • 상품 : 거래처 = N : 1

 

  • 사상 크기를 따져본 다음 테이블들을 만들기
- 회원: {회원번호, 회원이름, 생년월일, 주소, 전화번호, 주민등록번호, 가입일, 회원구분}, PK: {회원번호}
- 상품: {상품번호, 상품명, 공급단가, 판매단가, 재고량, 거래처번호}, PK: {상품번호}, FK: {거래처번호}
- 거래처: {거래처번호, 거래처명, 주소, 전화번호, 담당자명}, PK: {거래처번호}
- 주문: {주문번호, 주문상세번호, 회원번호, 상품번호, 주문일, 배송주소, 배송연락처, 개수}, PK: {주문상세번호}, FK: {회원번호}, FK: {상품번호}

 

  • 각 테이블들이 BCNF에 있는가? -> 모든 결정자가 후보키에 있는가??
    • 회원: {회원번호, 회원이름, 생년월일, 주소, 전화번호, 주민등록번호, 가입일, 회원구분}, PK: {회원번호}, CK: {주민등록번호}
      • SFDc
        • FD1 : {회원번호} -> {회원이름, 생년월일, 주소, 전화번호, 주민등록번호, 가입일, 회원구분}
        • FD2 : {주민등록번호} -> {회원번호}
      • BCNF에 있다.
    • 상품: {상품번호, 상품명, 공급단가, 판매단가, 재고량, 거래처번호}, PK: {상품번호}, FK: {거래처번호}
      • SFDc
        • FD1 : {상품번호} -> {상품명, 공급단가, 판매단가, 재고량, 거래처번호
      • BCNF에 있다.
    • 거래처 : {거래처번호, 거래처명, 주소, 전화번호, 담당자명}, PK : {거래처번호}
      • SFDc
        • FD1 : {거래처번호} -> {거래처명, 주소, 전화번호, 담당자명}
      • BCNF에 있다.
    • 주문: {주문번호, 주문상세번호, 회원번호, 상품번호, 주문일, 배송주소, 배송연락처, 개수}, PK: {주문상세번호}, FK: {회원번호}, FK: {상품번호}
      • SFDc
        • FD1 : {주문상세번호} -> {주문번호, 상품번호, 개수}
        • FD2 : {주문번호} -> {회원번호, 주문일, 배송주소, 배송연락처}
      • FD2 때문에 BCNF에 있지 않다.
      • 따라서 테이블을 나누어야 한다.
      • 주문: {주문번호, 회원번호, 주문일, 배송주소, 배송연락처}, PK: {주문번호}, FK: {회원번호}
        • SFDc 
          • FD1 : {주문번호} -> {회원번호, 주문일, 배송주소, 배송연락처}
      • 주문상세내역: {주문번호, 주문상세번호, 상품번호,  개수}, PK: {주문상세번호}, FK: {주문번호}, FK: {상품번호}
        • SFDc
          • FD1 : {주문상세번호} -> {주문번호, 상품번호, 개수}
        • BCNF에 있다.
  • 온라인 쇼핑몰 데이터베이스 최종 결과
회원 : {회원번호, 회원이름, 생년월일, 주소, 전화번호, 주민등록번호, 가입일, 회원구분}, PK : {회원번호}, CK : {주민등록번호}
상품 : {상품번호, 상품명, 공급단가, 판매단가, 재고량, 거래처번호}, PK : {상품번호}, FK : {거래처번호}
거래처 : {거래처번호, 거래처명, 주소, 전화번호, 담당자명}, PK : {거래처번호}
주문 : {주문번호, 회원번호, 주문일, 배송주소, 배송연락처}, PK : {주문번호}, FK : {회원번호}
주문상세내역 : {주문번호, 주문상세번호, 상품번호, 개수}, PK : {주문상세번호}, FK : {주문번호}, FK : {상품번호}  

 

실습 과제

  • 아래의 글을 보고 데이터베이스 설계 과정에 따라 데이터베이스를 완성해 보세요.
    • DB 상사는 회사 내부 직원들의 정보를 관리하기 위해 다음과 같은 요구 분석을 통해 데이터베이스를 설계하고자 한다.
    • 직원들을 구분하기 위해 직원번호를 각각 다르게 부여할 것이며 직원들에 대한 정보 중 저장해야 할 값들은 이름, 주소, 연락처, 입사연도이다.
    • 직원들은 하나의 부서에 소속된다.
    • 부서를 구분하기 위해 부서번호를 각각 다르게 부여할 것이며 부서들에 대한 정보 중 저장해야 할 값들은 부서명, 대표전화번호, 사무실번호, 직무내역이다.
  • 개체
    • 직원: 직원번호, 이름, 주소, 연락처, 입사연도, 부서번호
    • 부서: 부서번호, 부서명, 대표전화번호, 사무실번호, 직무내역
  • 관계
    • 소속
  • 특이사항
    • 직원들은 하나의 부서에 소속된다.
  • E-R 모델

  • E-R 모델에 따라 만들어지는 테이블들
    • 직원: {직원번호, 주소, 부서번호, 이름, 연락처, 입사연도}, PK: {직원번호}, FK: {부서번호}
    • 부서: {부서번호, 대표전화번호, 부서명, 직무내역, 사무실번호}, PK: {부서번호}
    •  
  • 각 테이블들이 BCNF에 있는가?
    • 직원: {직원번호, 주소, 부서번호, 이름, 연락처, 입사연도}, PK: {직원번호}, FK: {부서번호}
    • SFDc
      • FD1: {직원번호} -> {주소, 부서번호, 이름, 연락처, 입사연도}
    • BCNF에 있다.
    • 부서: {부서번호, 대표전화번호, 부서명, 직무내역, 사무실번호}, PK: {부서번호}
    • SFDc
      • FD1: {부서번호} -> {대표전화번호, 부서명, 직무내역, 사무실번호}
    • BCNF에 있다.
  • 회사 데이터베이스 최종 결과
    • 직원: {직원번호, 이름, 입사연도, 주소, 부서번호}, PK: {직원번호}, FK: {부서번호}
    • 부서: {부서번호, 부서명, 사무실번호, 대표전화번호, 직무내역}, PK: {부서번호}

'CS > 인터넷DB응용' 카테고리의 다른 글

[인터넷DB응용] 웹 서비스의 발전과 모바일 웹 서비스  (0) 2023.06.11
[인터넷DB응용] 데이터베이스 설계 - 1  (0) 2023.06.09
[인터넷DB응용] PL-SQL - 2  (0) 2023.04.17
[인터넷DB응용] PL-SQL - 1  (0) 2023.04.16
[인터넷DB응용] SQL 활용 - 3  (0) 2023.04.14