관계형 데이터베이스(RDB, Relational Database)란?

데이터를 테이블 형태로 저장하고 관리하는 데이터베이스이다.

  • SQL != Database
    • SQL과 Database는 다르다.
    • SQL은 Database를 구현하는 언어이다.

설계 과정

  1. 요구사항 분석
    • 시스템이 어떤 데이터를 관리하고, 어떤 연산을 수행해야 하는지 정의한다.
  2. 개념적 설계
    • 개체-관계 모델을 이용해서 데이터와 데이터간의 관계를 설계한다.
    • 개체-관계(Entity-Relation) 모델 : 데이터와 데이터 간의 관계를 개체와 관계로 설계한 것
  3. 논리적 설계
    • 개념적 설계를 관계형 데이터베이스 스키마(Schema)를 만든다.
    • 정규화를 통해 데이터의 중복을 최소화하는 등 설계를 최적화한다.
    • 스키마 : 데이터베이스의 구조를 정의한 것
      • 테이블, 속성, 키, 제약 조건 등
  4. 물리적 설계
    • 스키마를 바탕으로 실제 데이터베이스를 구현한다.

정규화를 하기 전에 알아야 할 개념

  • 함수적 종속성
    • 정규화는 함수적 종속성을 제거하는 것이다.
  • 슈퍼키(Super key)
    • 테이블 내의 레코드(행, 튜플)을 유일하도록 식별할 수 있는 속성 또는 속성들의 집합
    • 학번, 이름은 학생을 유일하게 식별할 수 있으므로 슈퍼키가 될 수 있다.
  • 후보키(Candidate key)
    • 슈퍼키에서 최소성(Minimality) 속성을 추가한 것
    • 테이블에서 레코드를 유일하도록 식별할 수 있는 최소한의 속성 집합
    • 학번, 이름에서 학번만으로도 학생을 유일하게 식별할 수 있으므로 후보키가 될 수 있다.
    • 후보키 중에서 기본키를 선정한다.
  • 후보키 ⊂ 슈퍼키
    • 모든 후보키는 슈퍼키이다.

함수적 종속성(Functional Dependency)

한 속성의 값이 다른 속성의 값을 결정하는 관계, 즉 한 속성이 다른 속성에 종속되어 있음을 의미한다.

Example, 학생 테이블

학번 이름 전공 학년
20190001 김이름 컴퓨터공학 2
20190002 름이김 컴퓨터공학 3
  • 학번이 다른 속성인 이름, 전공, 학년을 고유한 값으로 결정한다.
  • 이름, 전공, 학년은 학번에 종속된다.

Trivial-FD(자명한 함수적 종속)

A,B가 속성 집합 일때, A가 B에 종속적이고, B가 A에 포함되어 있거나 A와 B가 서로 같다면 Trivial-FD 라고 한다.
이러한 종속성은 모든 테이블에서 항상 성립한다.

학생 테이블에서, 학번, 이름 -> 이름의 경우, 이름이 학번, 이름에 포함되어 있으므로 Trivial-FD이다.

A set of FDs(SFD)

함수적 종속성의 집합

  • Example
    • 학생 테이블에 대한 SFD
    • 학번이 다른 속성을 결정하므로, 아래와 같이 나타낼 수 있다.
      • 학번 -> 이름
      • 학번 -> 전공
      • 학번 -> 학년

규범적 덮개(Canonical Cover, SFDc)

SFD를 나타내는 최소한의 집합

SFD에 대한 규범적 덮개는 여러 개 일수 있다.(유일하지 않음)

  • 조건
    • SFD+ = (SFDc)+
    • SFDc에 있는 함수적 종속에는 군더더기(잉여, Extraneous)속성이 없다.
      • 잉여 속성 : 함수적 종속의 왼쪽 또는 오른쪽의 집합에서 없애도 SFD+가 변하지 않는 속성
      • 학번 -> 이름, 전공, 학년에서 학번이 잉여 속성이다.
    • SFDc에 있는 함수적 종속의 왼쪽은 고유하다.
      • a->b, c->d 와 같이 두 종속성이 존재하여, a=c가 되는 경우는 없다.
  • 생성 과정
    1. 기본 키와 나머지 속성 집합으로 이루어지는 함수적 종속을 만든다.
    2. 반복
      1. a->b, a->d와 같은 형태의 종속성을 a->bd로 바꾼다.
      2. 함수적 종속성에서 잉여 속성을 없앤다.
    3. 규범적 덮개가 변경되지 않을 때까지 2번 과정을 반복한다.

함수적 종속을 찾는 예제

수강 신청 DB

  • 속성
    • 학번(PK), 이름, 과목번호(PK), 과목이름, 연락처, 성적등급
  • 함수적 종속 분석
    • FD1 : 학번, 과목번호 -> 이름, 과목이름, 연락처, 성적등급
    • FD2 : 학번 -> 이름, 연락처
    • FD1’ : 학번, 과목번호 -> 과목이름, 성적등급
    • FD3 : 과목번호 -> 과목이름
    • FD1’’ : 학번, 과목번호 -> 성적등급
  • 규범적 덮개로 정리
    • 잉여 속성이 함수적 종속에 없으므로 FD1'', FD2, FD3 가 규범적 덮개이다.

온라인 쇼핑몰 DB

  • 속성
    • 주문번호(PK), 회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소
  • 함수적 종속 분석
    • FD1 : 주문번호 -> 회원번호, 회원이름, 회원주소, 회원연락처, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소
    • FD2 : 회원번호 -> 회원이름, 회원주소, 회원연락처
    • FD1’ : 주문번호 -> 회원번호, 상품번호, 상품이름, 주문개수, 배송연락처, 배송주소
    • FD3 : 상품번호 -> 상품이름
    • FD1’’ : 주문번호 -> 회원번호, 상품번호, 주문개수, 배송연락처, 배송주소
  • 규범적 덮개로 정리
    • 잉여 속성이 함수적 종속에 없으므로 FD1'', FD2, FD3 가 규범적 덮개이다.

복수 전공 신청 DB

  • 속성
    • 학번, 학생이름, 학생연락처, 학생주소, 학과번호, 학과이름, 학과전화번호, 교수번호, 교수이름, 교수연락처
  • 기본키 분석
    • 학번, 학과번호, 교수번호
  • 함수적 종속
    • FD1 : 학번 -> 학과번호, 교수번호, 학생이름, 학생연락처, 학생주소, 학과이름, 학과전화번호, 교수이름, 교수연락처
    • FD2 : 학과번호 -> 학과이름, 학과전화번호
    • FD1’ : 학번 -> 학과번호, 교수번호, 학생이름, 학생연락처, 학생주소, 교수이름, 교수연락처
    • FD3 : 교수번호 -> 교수이름, 교수연락처
    • FD1’’ : 학번 -> 학과번호, 교수번호, 학생이름, 학생연락처, 학생주소
  • 규범적 덮개로 정리
    • 잉여 속성이 함수적 종속에 없으므로 FD1'', FD2, FD3 가 규범적 덮개이다.

정규화(Normalization)

데이터베이스의 데이터 중복을 최소화 하여, insert, update, delete 등의 연산을 할 때에 발생 가능한 문제를 줄인다.

  • 종류
    1. 제 1정규형 (1NF)
    2. 제 2정규형 (2NF) : 1형 만족
    3. 제 3정규형 (3NF) : 1, 2형 만족
    4. Boyce-codd 정규형 (BCNF) : 1, 2, 3형 만족
    5. 제 4정규형 (4NF) : 1형 만족
    6. 제 5정규형 (5NF) : 1, 4형 만족

학습에 사용할 Example, 주문목록

제품번호 제품명 재고수량 주문번호 고객번호 주소 주문수량
1001 모니터 2000 A345, D347 100, 200 서울, 부산 150, 300
1007 마우스 9000 A210, A345, B230 300, 100, 200 광주, 서울, 부산 600, 400, 700
1201 키보드 2100 D347 200 부산 300

제 1정규형(1NF)

테이블의 모든 속성이 원자 값(더 이상 분해할 수 없는 단일 값)을 갖도록 하는 정규형이다. 즉, 각 테이블의 각 열이 단일 값을 가져야 하며, 또한 각각의 행이 고유한 키로 식별될 수 있어야 한다.

  • 위의 주문목록 테이블은 1NF를 만족하지 않는다.
    • 속성에 대한 값이 2개 이상인 레코드(행, 튜플)가 존재하기 때문이다.
  • 1NF를 만족하도록 테이블을 분리
    • 2개의 기본키에 대한 테이블로 분리한다.

제품

제품번호 제품명 재고수량
1001 모니터 2000
1007 마우스 9000
1201 키보드 2100

제품 주문

주문번호 제품번호 고객번호 주소 주문수량
A345 1001 100 서울 150
D347 1001 100 부산 300
A210 1007 300 광주 600
A345 1007 100 서울 400
B230 1007 200 부산 700
D347 1201 200 부산 300

제 2정규형(2NF)

제1정규형을 만족하면서, 기본키가 아닌 모든 열이 기본키에 완전 함수적으로 종속되는 정규형이다. 즉, 테이블의 기본키를 제외한 나머지 속성이 각각의 행에 대해 유일한 값을 갖는 기본키에 대해 완전하게 종속되어야 한다.

부분 함수적 종속성이 없어야 한다.

  • 위의 제품 테이블은 2NF를 만족한다.
    • 제품번호가 다른 속성들을 결정하므로, 부분 함수적 종속이 없기 때문이다.
  • 위의 제품 주문 테이블은 2NF를 만족하지 않는다.
    • FD1 : 주문번호, 제품번호 -> 고객번호, 주소, 주문수량
    • FD1의 부분 함수적 종속인, 주문번호 -> 고객번호, 주소 가 존재하기 때문이다.
  • 2NF를 만족하도록 테이블을 분리
    • 부분 함수적 종속을 없앤다.

주문 목록

주문번호 제품번호 주문수량
A345 1001 150
D347 1001 300
A210 1007 600
A345 1007 400
B230 1007 700
D347 1201 300

주문

주문번호 고객번호 주소
A345 100 서울
D347 100 부산
A210 300 광주
A345 100 서울

제 3정규형(3NF)

제2정규형을 만족하면서, 기본키가 아닌 모든 칼럼이 기본키에 대해 이행적 함수 종속을 갖지 않는 정규형이다. 이는 비 Key속성 사이에 서로 종속적인 관계가 없음을 의미한다. 즉, 속성이 A -> B, B -> C 일 때, A -> C 가 성립하는 속성이 없어야 한다.

  • 위의 제품, 주문 목록 테이블은 3NF를 만족한다.
    • 기본키가 다른 속성들을 결정하고, 이행적 함수 종속이 없기 때문이다.
  • 주문 테이블은 3NF를 만족하지 않는다.
    • FD1 : 주문번호 -> 고객번호, 주소
    • FD2 : 고객번호 -> 주소
    • FD2에 의하여, 주문번호 -> 주소와 같은 이행적 함수 종속이 존재하기 때문이다.
  • 3NF를 만족하도록 테이블을 분리
    • 이행적 함수 종속을 없앤다.
    • 모든 함수적 종속에서 불필요한 속성을 제거한다.
      • FD1 : 주문번호 -> 고객번호
      • FD2 : 고객번호 -> 주소

주문

주문번호 고객번호
A345 100
D347 100
A210 300
A345 100

고객

고객번호 주소
100 서울
200 부산
300 광주

Boyce-Codd 정규형(BCNF)

제3정규형을 만족하면서, 각 결정자가 후보키의 부분집합이 되는 정규형이다. 3NF보다 더 엄격한 조건을 설정하여, 데이터 중복을 더욱 줄이는 데에 초점을 맞춘다. 즉, 테이블에서 모든 결정자가 후보키이다.

  • 결정자
    • 함수적 종속성을 가지는 속성 or 속성 집합
  • 후보키
    • 테이블 내의 각 행을 고유하게 식별하는 속성 or 속성 집합

BCNF를 만족하려면, 함수적 종속성에서 왼쪽에 있는 모든 결정자가 후보키를 구성해야 한다.

Example, 수강 교수

학번 과목명 담당교수
981746 데이터베이스 김교수
981747 네트워크 박교수
981748 인공지능 이교수
981749 데이터베이스 김교수
981747 데이터베이스 서교수
981749 네트워크 박교수
  • 부분/이행적 함수적 종속이 없으므로, 3NF를 만족한다.
    • FD1 : 학번, 과목명 -> 담당교수
    • FD2 : 담당교수 -> 과목명
  • BCNF는 만족하지 않는다.
    • FD2에서, 담당교수는 후보키가 아니기 때문이다.
  • BCNF를 만족하도록 테이블을 분리
    • 모든 결정자가 후보키가 되도록 테이블을 분리한다.
    • FD2 : 담당교수 -> 과목명 을 만족시키기 위해, 담당교수를 기본키로 하는 테이블을 생성한다.

수강

학번 담당교수
981746 김교수
981747 박교수
981748 이교수
981749 김교수
981747 서교수
981749 박교수

교수

담당교수 과목명
김교수 데이터베이스
박교수 네트워크
이교수 인공지능
서교수 데이터베이스

제 4정규형(4NF)

1NF를 만족하면서, 테이블에 다중값 상속(Multi Valued Dependency)으로 A -> B가 존재할 경우, 테이블의 모든 속성이 A에 함수적 종속성을 가진다.

제 5정규형(5NF)

테이블의 모든 조인 종속이 테이블의 후보키를 통해서만 성립되는 정규형이다.

거의 사용되지 않는다.

역 정규화(Denormalization)

과도한 정규화로 인해 성능이 떨어지는 경우, 분리한 테이블을 다시 합치는 방법이다.

정규화 예제

수강, 학생

  • 속성
    • 학번(PK), 과목번호(PK), 과목이름, 학과번호, 이름, 집전화
  • SFD
    • FD1 : 학번, 과목번호 -> 과목이름, 학과번호, 이름, 집전화
    • FD2 : 학번 -> 학과번호, 이름, 집전화
    • FD1’ : 학번, 과목번호 -> 과목이름, 학과번호
    • FD3 : 과목번호 -> 과목이름
    • FD1’’ : 학번, 과목번호 -> 학과번호
  • 1NF 만족
    • 모든 속성은 원자값을 가진다.
  • 2NF 만족
    • 부분 함수적 종속이 없다.
  • 3NF 만족
    • 이행적 함수적 종속이 없다.
  • BCNF 만족
    • 모든 결정자가 후보키이다.

쇼핑몰 회원

  • 속성
    • 회원번호(PK), 회원이름, 생년월일, 주소, 전화번호, 주민등록번호(CK), 가입일, 회원구분
  • SFD
    • FD1 : 회원번호 -> 회원이름, 생년월일, 주소, 전화번호, 주민등록번호, 가입일, 회원구분
    • FD2 : 주민등록번호 -> 회원번호
  • BCNF 만족

쇼핑몰 주문

  • 속성
    • 주문번호, 주문상세번호(PK), 회원번호(FK), 상품번호(FK), 주문일, 배송주소, 배송연락처, 개수
  • SFD
    • FD1 : 주문상세번호 -> 주문번호, 회원번호, 상품번호, 주문일, 배송주소, 배송연락처, 개수
    • FD2 : 주문번호 -> 회원번호, 주문일, 배송주소, 배송연락처
    • FD1’ : 주문상세번호 -> 주문번호, 상품번호, 개수

설계 예제

  • 아래의 조건대로 설계를 해봅시다.
    • 주제 : 회사 내부 직원 정보 관리
    • 요구 사항
      • 직원 구분을 위해 직원번호를 고유하게 부여한다.
      • 직원 정보 : 이름, 주소, 연락처, 입사연도
      • 직원은 하나의 부서에 소속된다.
      • 부서 구분을 위해 부서번호를 고유하게 부여한다.
      • 부서 정보 : 부서명, 대표전화번호, 사무실번호, 직무내역

1. 개념적 설계

  • 두 개의 개체(Entity)가 있다
    • 직원과 부서
    • 이 두 개체는 아래와 같은 속성을 가진다.
    • 직원
      • 이름(PK), 주소, 연락처, 입사연도, 부서번호(FK)
    • 부서
      • 부서번호(PK), 부서명, 대표전화번호, 사무실번호, 직무내역

직원과 부서 사이에는 N:1 관계가 존재한다. 여러 직원이 한 부서에 소속이 된다는 의미이다.

2. 논리적 설계

  • 스키마
    • 직원
      • 이름(PK), 주소, 연락처, 입사연도, 부서번호(FK)
    • 부서
      • 부서번호(PK), 부서명, 대표전화번호, 사무실번호, 직무내역

위 스키마는 BCNF를 만족한다. 이제 다음 단계는 SQL로 실제 데이터베이스 구축을 하는 것이다.