데이터베이스 설계는 데이터를 효율적으로 저장하고 관리하기 위한 구조를 만드는 과정이다. 기본키는 설계 과정에서 중요한 역할을 하며, 정규화는 데이터 무결성을 유지하고 중복을 최소화하는 데 필수적이다. 기본키의 설계 고려사항, 정규화 과정, 그리고 물리적 데이터베이스 설계의 핵심 요소들을 배워보자
기본키의 설계 고려사항
기본키를 선택할 때 고려해야 할 요소들은 다음과 같다.
최소성(Minimality)
기본키는 튜플을 식별하는 데 필요한 최소한의 속성만 포함해야 한다. 예를 들어, {학번, 이름} 대신 {학번}만으로 튜플을 식별할 수 있다면 {학번}을 기본키로 선택한다.
안정성(Stability)
기본키 값은 시간이 지나도 변경되지 않아야 한다. 예를 들어, "이름"은 변경될 가능성이 있으므로 기본키로 부적합하다. 반면, "학번"은 안정적이므로 적합하다.
단순성(Simplicity)
기본키는 가능한 한 단순하고 이해하기 쉬워야 한다. 예를 들어, 단일 속성(학번)이 복합 속성(이름+생년월일)보다 선호된다.
기본키와 관련된 실제 응용
기본키는 데이터베이스 운영과 관리에서 다양한 방식으로 활용된다.
인덱싱(Indexing)
기본키는 자동으로 인덱스가 생성되어 검색 속도를 향상시킨다. 예를 들어, "학번"으로 학생을 검색할 때 인덱스를 통해 빠르게 접근 가능하다. 인덱스의 추가와 삭제는 각각 CREATE INDEX와 DROP INDEX 명령어로 수행된다.
데이터 관계 설정
기본키와 외래키를 통해 릴레이션 간 관계(1:1, 1:N, N:M)를 설정한다. 예를 들어, 학생 테이블(학번)과 수강 테이블(학생ID)을 연결하여 학생별 수강 내역을 관리한다.
데이터베이스 정규화(Normalization)
기본키를 통해 데이터 중복을 제거하고 데이터 무결성을 유지한다. 예를 들어, 학생 정보를 별도의 테이블로 분리하고 "학번"을 기본키로 사용하여 중복을 방지한다.
물리적 데이터베이스 설계
물리적 데이터베이스 구조의 기본 데이터 단위인 저장 레코드의 양식을 설계할 때 다음과 같은 요소를 고려한다.
-
데이터 타입
- 저장 레코드에 저장할 데이터의 타입을 결정한다. 예를 들어, 학번은 정수형(INT), 이름은 문자열(VARCHAR)로 설정한다.
-
데이터 값의 분포
- 저장 레코드에 저장할 데이터의 값이 어떻게 분포되어 있는지를 파악한다. 예를 들어, 학생의 나이 분포를 분석하여 적절한 데이터 타입과 범위를 설정한다.
-
접근 빈도
- 저장 레코드에 저장된 데이터를 얼마나 자주 접근하는지를 고려한다. 예를 들어, 자주 조회되는 학번 필드는 인덱스를 추가하여 검색 속도를 높인다.
논리적 데이터베이스 설계 단계
논리적 데이터베이스 설계는 데이터베이스의 구조를 개념적, 논리적, 물리적 단계로 나누어 진행된다.
- 개념적 설계: 데이터베이스의 전체적인 구조를 정의한다. 엔터티와 관계를 식별하여 ER 다이어그램으로 표현한다.
- 논리적 설계: 개념적 설계를 관계형 모델로 변환한다. 테이블, 속성, 기본키, 외래키를 정의한다.
- 물리적 설계: 논리적 설계를 실제 저장 구조로 변환한다. 데이터 타입, 인덱스, 저장 방식 등을 결정한다.
DB정규화 설명 들어가기 전, 결정자란?
관계형 데이터베이스에서 어떤 속성 A가 속성 B를 항상 유일하게 결정한다면,
A → B라고 표현하고, 이때 A를 결정자, B를 종속자라고 한다.
즉, 결정자 A의 값이 같으면, 항상 B의 값도 같다는 의미이다.
데이터베이스 정규화(Normalization)
정규화는 데이터 중복을 제거하고 데이터 무결성을 유지하기 위한 체계적인 과정이다. 정규화는 비정규 릴레이션에서 시작하여 점진적으로 정규형(NF)을 만족하도록 변환한다.
비정규 릴레이션
비정규 릴레이션은 정규화되지 않은 상태로, 도메인이 원자 값(atomic value)을 가지지 않을 수 있다.
1NF (제1정규형) – 반복 속성 제거
도메인이 원자 값을 가지도록 한다. 즉, 각 속성 값은 더 이상 분해될 수 없는 단일 값이어야 한다.
예: 주소 속성을 시, 구, 동으로 분리.
정규화 전 (UNF)
| 학번 | 이름 | 과목 | 교재 |
|---|---|---|---|
| 101 | 홍길동 | 수학, 영어 | 수학책, 영어책 |
| 102 | 김영희 | 과학 | 과학책1, 과학책2 |
- 과목과 교재가 반복 속성 형태 (쉼표로 구분된 값)
정규화 후 (1NF)
| 학번 | 이름 | 과목 | 교재 |
|---|---|---|---|
| 101 | 홍길동 | 수학 | 수학책 |
| 101 | 홍길동 | 영어 | 영어책 |
| 102 | 김영희 | 과학 | 과학책1 |
| 102 | 김영희 | 과학 | 과학책2 |
- 모든 속성이 원자값(Atomic Value)
- 반복 속성 제거 → 1NF 만족
2NF (제2정규형) – 부분 함수 종속 제거
부분적 함수 종속을 제거한다.
모든 비키 속성이 기본키에 완전히 함수적으로 종속되어야 한다.
예: {학생ID, 강의ID}가 기본키인 수강 테이블에서 강의명은 강의ID에만 종속되므로 별도 테이블로 분리.
부분 함수 종속 이란
부분 함수 종속(Partial Functional Dependency) 이란:
- 기본키
A가 여러 속성(예:A = {X, Y})으로 구성된 경우,A → B가 성립하더라도X → B처럼 기본키의 일부 속성만으로 비키B를 결정할 수 있는 경우 - 기본키의 전체가 아닌 일부 속성이 비기본키 속성을 결정하면 문제가 됨
부분적 함수 종속은 기본키의 일부 속성(예: X ⊂ A)이 비기본키 속성 B를 결정하는 경우로, 데이터 중복을 유발한다.
정규화 전 (1NF는 만족)
| 주문ID | 상품ID | 상품명 | 고객명 | 수량 | 단가 |
|---|---|---|---|---|---|
| 001 | A101 | 무선마우스 | 김현준 | 2 | 15000 |
| 001 | B202 | 키보드 | 김현준 | 1 | 30000 |
| 002 | A101 | 무선마우스 | 이지은 | 1 | 15000 |
- 기본키: (주문ID, 상품ID)
- 고객명은 주문ID에만 종속
- 상품명과 단가는 상품ID에만 종속
부분 함수 종속 → 2NF 위배
정규화 후 (2NF)
주문&고객 테이블
| 주문ID | 고객명 |
|---|---|
| 001 | 김현준 |
| 002 | 이지은 |
상품 테이블
| 상품ID | 상품명 | 단가 |
|---|---|---|
| A101 | 무선마우스 | 15000 |
| B202 | 키보드 | 30000 |
주문 상세 테이블
| 주문ID | 상품ID | 수량 |
|---|---|---|
| 001 | A101 | 2 |
| 001 | B202 | 1 |
| 002 | A101 | 1 |
부분 종속 제거 → 완전 함수 종속만 남김 → 2NF 만족
3NF (제3정규형) – 이행 함수 종속 제거
이행적 함수 종속을 제거한다.
모든 비키 속성이 기본키에 직접 종속되어야 하며, 다른 비키 속성에 종속되어서는 안 된다.
예: 학생 테이블에서 학과명이 학과ID에 종속되므로 학과 테이블로 분리.
이행적 함수 종속이란?
이행적 함수 종속(Transitive Dependency)이란:
A → B,B → C가 성립하면A → C도 성립하는 경우- 이때
A는 후보키,B와C는 비키(non-prime attribute)이며,B를 통해 간접적으로C가 결정되면 문제가 됨
예시
이행적 함수 종속은 기본키 A가 비키 B를 통해 다른 비키 C를 간접적으로 결정하는 경우로, 데이터 중복을 유발한다.
정규화 전
| 학번 | 학과코드 | 학과명 |
|---|---|---|
| 101 | C01 | 컴퓨터공학 |
| 102 | M02 | 기계공학 |
- 학번 → 학과코드 → 학과명
⇒ 학과명은 학번을 거쳐 종속됨 → 이행적 함수 종속
⇒ 3NF 위배
정규화 후 (3NF)
학생 테이블
| 학번 | 학과코드 |
|---|---|
| 101 | C01 |
| 102 | M02 |
학과 테이블
| 학과코드 | 학과명 |
|---|---|
| C01 | 컴퓨터공학 |
| M02 | 기계공학 |
이행 종속 제거 → 3NF 만족
BCNF (보이스-코드 정규형) – 모든 결정자가 후보키
결정자가 후보키가 아닌 경우를 제거한다. 모든 결정자가 후보키이어야 한다.
예: 특정 강의의 교수와 강의ID가 관련된 경우, 교수ID가 후보키가 아니면 별도 테이블로 분리.
정규화 전
학생-강의 관계 테이블
| 학생ID | 강의명 | 강의실 |
|---|---|---|
| S01 | DB | 101호 |
| S02 | OS | 102호 |
| S03 | DB | 101호 |
| S04 | OS | 102호 |
함수 종속성(FD):
- 강의명 → 강의실 (하나의 강의는 항상 동일한 강의실에서 진행됨)
- 학생ID, 강의명 → 다른 속성들 (복합 후보키)
강의명은 결정자지만 후보키가 아님 → BCNF 위배
정규화 후 (BCNF 만족)
강의 테이블 (강의명 → 강의실 분리)
| 강의명 | 강의실 |
|---|---|
| DB | 101호 |
| OS | 102호 |
수강 테이블 (학생이 어떤 강의를 듣는지)
| 학생ID | 강의명 |
|---|---|
| S01 | DB |
| S02 | OS |
| S03 | DB |
| S04 | OS |
- 강의명 → 강의실: 이제 별도 테이블로 분리되어, 강의명이 후보키가 되므로 BCNF 만족
모든 결정자가 후보키 → BCNF 만족
4NF (제4정규형) – 다치 종속 제거
다치 종속을 제거한다. 다치 종속은 하나의 키가 여러 독립적인 값 집합에 종속되는 경우를 의미한다.
예: 학생이 수강하는 강의와 취미를 별도 테이블로 분리.
정규화 전
| 학번 | 자격증 | 외국어 |
|---|---|---|
| 101 | 정보처리기사 | 영어 |
| 101 | 정보처리기사 | 일본어 |
| 101 | 컴퓨터활용능력 | 영어 |
| 101 | 컴퓨터활용능력 | 일본어 |
- 학번 →→ 자격증
- 학번 →→ 외국어
자격증과 외국어는 서로 독립적인 다치 종속 → 4NF 위배
정규화 후 (4NF)
자격증 테이블
| 학번 | 자격증 |
|---|---|
| 101 | 정보처리기사 |
| 101 | 컴퓨터활용능력 |
외국어 테이블
| 학번 | 외국어 |
|---|---|
| 101 | 영어 |
| 101 | 일본어 |
다치 종속 제거 → 4NF 만족
5NF (제5정규형)
조인 종속성을 이용하여 모든 조인이 무손실 분해가 되도록 한다. 이는 매우 복잡한 경우에 적용된다.
요약
1 ~ 4NF 정규형과 이상 제거 대상 표
| 정규형 | 제거 대상 | 핵심 조건 |
|---|---|---|
| 1NF | 반복 속성 | 원자값 (Atomic Value) |
| 2NF | 부분 함수 종속 | 모든 비기본키는 전체 기본키에 종속 |
| 3NF | 이행 함수 종속 | 비기본키는 기본키에 직접 종속 |
| BCNF | 후보키가 아닌 결정자 | 모든 결정자는 후보키여야 함 |
| 4NF | 다치 종속 | 다치 종속 제거 |
데이터베이스 설계에서 기본키는 최소성, 안정성, 단순성을 고려하여 선택된다. 정규화는 비정규 릴레이션에서 1NF, 2NF, 3NF, BCNF, 4NF, 5NF로 단계적으로 데이터 중복을 제거하고 무결성을 유지한다. 물리적 설계에서는 데이터 타입, 값 분포, 접근 빈도를 고려하여 저장 레코드를 설계한다. 이러한 과정은 효율적이고 신뢰성 있는 데이터베이스를 구축하는 데 필수적이다.
