데이터베이스 설계
데이터베이스 설계 (Database Design)
데이터베이스 설계는 사용자들의 요구 사항을 고려하여 데이터베이스를 생성하는 과정이다.
1. 데이터베이스 설계 단계
┌─────────────────────────────────────────────────────────────────┐
│ 데이터베이스 설계 5단계 │
├─────────────────────────────────────────────────────────────────┤
│ │
│ [1단계] 요구 사항 분석 │
│ │ └─ 결과물: 요구 사항 명세서 │
│ ↓ │
│ [2단계] 개념적 설계 ←──────────────────┐ │
│ │ └─ 결과물: E-R 다이어그램 │ (피드백 루프) │
│ ↓ │ │
│ [3단계] 논리적 설계 ←──────────────────┤ │
│ │ └─ 결과물: 릴레이션 스키마 │ │
│ ↓ │ │
│ [4단계] 물리적 설계 ←──────────────────┘ │
│ │ └─ 결과물: 물리적 스키마 │
│ ↓ │
│ [5단계] 구현 │
│ └─ 결과물: 실제 데이터베이스 │
│ │
└─────────────────────────────────────────────────────────────────┘단계별 개요
| 단계 | 주요 작업 | 결과물 | DBMS 의존성 |
|---|---|---|---|
| 1. 요구 사항 분석 | 사용자 요구 수집 및 분석 | 요구 사항 명세서 | 독립적 |
| 2. 개념적 설계 | E-R 다이어그램 작성 | 개념적 스키마 | 독립적 |
| 3. 논리적 설계 | 릴레이션 스키마 변환 | 논리적 스키마 | 모델 의존 |
| 4. 물리적 설계 | 저장 구조, 인덱스 설계 | 물리적 스키마 | DBMS 의존 |
| 5. 구현 | DDL로 DB 생성 | 실제 DB | DBMS 의존 |
2. 1단계: 요구 사항 분석
목적
데이터베이스에 대한 사용자들의 요구 사항을 수집하고 분석하여 개발할 데이터베이스의 용도를 명확히 파악
주요 작업
1. 사용자 범위 결정
└─ 주요 사용자가 누구인지 정의
2. 업무 분석
└─ 필요한 데이터와 처리 방법 파악
3. 요구 사항 수집
└─ 인터뷰, 설문, 문서 분석 등
4. 요구 사항 명세서 작성
└─ 분석 결과 문서화요구 사항 명세서 예시
┌─────────────────────────────────────────────────────────────┐
│ 온라인 서점 요구 사항 명세서 │
├─────────────────────────────────────────────────────────────┤
│ │
│ [데이터 요구 사항] │
│ • 고객: 고객번호, 이름, 연락처, 주소, 가입일 │
│ • 도서: 도서번호, 제목, 저자, 출판사, 가격, 재고수량 │
│ • 주문: 주문번호, 주문일, 배송상태, 총금액 │
│ │
│ [처리 요구 사항] │
│ • 고객은 여러 도서를 한 번에 주문할 수 있다 │
│ • 한 도서는 여러 고객에게 판매될 수 있다 │
│ • 주문 시 재고 수량이 자동으로 감소해야 한다 │
│ • 고객별 주문 이력을 조회할 수 있어야 한다 │
│ │
│ [제약 조건] │
│ • 재고가 0인 도서는 주문할 수 없다 │
│ • 한 주문의 최대 금액은 1,000만원을 초과할 수 없다 │
│ │
└─────────────────────────────────────────────────────────────┘3. 2단계: 개념적 설계
목적
요구 사항을 **DBMS 독립적인 개념적 데이터 모델(E-R 모델)**로 표현
개념적 모델링 과정
요구 사항 명세서
│
↓
┌───────────────┐
│ 1. 개체 추출 │ ← 명사 찾기
└───────────────┘
│
↓
┌───────────────┐
│ 2. 속성 정의 │ ← 개체의 특성
└───────────────┘
│
↓
┌───────────────┐
│ 3. 키 속성 선별 │ ← 유일하게 식별
└───────────────┘
│
↓
┌───────────────┐
│ 4. 관계 결정 │ ← 동사 찾기
└───────────────┘
│
↓
┌───────────────┐
│ 5. E-R 다이어그램│
└───────────────┘개체 추출 방법
요구 사항 명세서에서 명사를 찾아 개체 후보로 선정
"고객은 여러 도서를 한 번에 주문할 수 있다"
↑ ↑ ↑
개체 개체 개체
(고객) (도서) (주문)E-R 다이어그램 예시
┌─────────────────────────────────────────────────────────────┐
│ 온라인 서점 E-R 다이어그램 │
├─────────────────────────────────────────────────────────────┤
│ │
│ ┌──────────┐ ┌──────────┐ ┌──────────┐ │
│ │ │ │ │ │ │ │
│ │ 고객 │────<주문>────│ 주문 │────<포함>────│ 도서 │ │
│ │ │ 1:N │ │ N:M │ │ │
│ └──────────┘ └──────────┘ └──────────┘ │
│ │ │ │ │
│ ┌────┴────┐ ┌────┴────┐ ┌────┴────┐ │
│ │ 고객번호 │ │ 주문번호 │ │ 도서번호 │ │
│ │ 이름 │ │ 주문일 │ │ 제목 │ │
│ │ 연락처 │ │ 배송상태 │ │ 저자 │ │
│ │ 주소 │ │ 총금액 │ │ 가격 │ │
│ └─────────┘ └─────────┘ │ 재고수량 │ │
│ └─────────┘ │
│ │
│ ※ 밑줄: 기본키 속성 │
│ │
└─────────────────────────────────────────────────────────────┘4. 3단계: 논리적 설계
목적
개념적 스키마(E-R 다이어그램)를 **DBMS가 처리할 수 있는 논리적 스키마(릴레이션)**로 변환
E-R 다이어그램 → 릴레이션 변환 규칙
규칙 1: 개체 → 릴레이션
개체 "고객" 릴레이션 "고객"
┌───────────────┐ ┌────────────────────────────────┐
│ • 고객번호 (PK) │ → │ 고객(고객번호, 이름, 연락처, 주소) │
│ • 이름 │ └────────────────────────────────┘
│ • 연락처 │
│ • 주소 │
└───────────────┘규칙 2: 1:N 관계 → 외래키
고객 ────< 주문 >──── 주문
(1) (N)
→ N쪽 릴레이션에 1쪽의 기본키를 외래키로 추가
주문(주문번호, 주문일, 배송상태, 총금액, 고객번호)
↑
외래키규칙 3: N:M 관계 → 새 릴레이션 생성
주문 ────< 포함 >──── 도서
(N) (M)
→ 관계를 위한 새 릴레이션 생성 (양쪽 기본키를 외래키로)
주문상세(주문번호, 도서번호, 수량)
↑ ↑
외래키 외래키
(복합 기본키)규칙 4: 1:1 관계 → 외래키 (어느 쪽이든)
직원 ────< 관리 >──── 부서
(1) (1)
→ 어느 한쪽에 다른 쪽의 기본키를 외래키로 추가
부서(부서번호, 부서명, 관리자사번)
↑
외래키변환 결과: 릴레이션 스키마
-- 온라인 서점 논리적 스키마
고객(고객번호, 이름, 연락처, 주소, 가입일)
PK: 고객번호
도서(도서번호, 제목, 저자, 출판사, 가격, 재고수량)
PK: 도서번호
주문(주문번호, 주문일, 배송상태, 총금액, 고객번호)
PK: 주문번호
FK: 고객번호 → 고객(고객번호)
주문상세(주문번호, 도서번호, 수량, 단가)
PK: (주문번호, 도서번호)
FK: 주문번호 → 주문(주문번호)
FK: 도서번호 → 도서(도서번호)5. 4단계: 물리적 설계
목적
논리적 스키마를 기반으로 실제 저장 장치에 효율적으로 저장하기 위한 구조 설계
물리적 설계 요소
| 요소 | 설명 | 예시 |
|---|---|---|
| 저장 구조 | 데이터 저장 방식 | 힙 파일, 순차 파일, 해시 파일 |
| 인덱스 | 빠른 검색을 위한 구조 | B-Tree, Hash, Bitmap |
| 파티셔닝 | 대용량 테이블 분할 | 범위, 해시, 리스트 파티션 |
| 클러스터링 | 관련 데이터 물리적 근접 배치 | 클러스터 인덱스 |
인덱스 설계 기준
인덱스 생성이 유리한 경우:
├─ WHERE 절에 자주 사용되는 컬럼
├─ JOIN에 사용되는 컬럼 (외래키)
├─ ORDER BY, GROUP BY에 사용되는 컬럼
└─ 선택도(Selectivity)가 높은 컬럼
인덱스 생성이 불리한 경우:
├─ 자주 변경되는 컬럼 (INSERT/UPDATE/DELETE)
├─ 선택도가 낮은 컬럼 (예: 성별)
└─ 테이블 크기가 작은 경우물리적 설계 예시
-- 인덱스 설계
CREATE INDEX idx_주문_고객번호 ON 주문(고객번호);
CREATE INDEX idx_주문_주문일 ON 주문(주문일);
CREATE INDEX idx_도서_제목 ON 도서(제목);
-- 파티셔닝 (주문 테이블을 연도별로 분할)
CREATE TABLE 주문 (
주문번호 INT,
주문일 DATE,
...
) PARTITION BY RANGE (YEAR(주문일)) (
PARTITION p2023 VALUES LESS THAN (2024),
PARTITION p2024 VALUES LESS THAN (2025),
PARTITION p2025 VALUES LESS THAN (2026)
);6. 5단계: 구현
목적
설계 결과를 바탕으로 DDL을 사용해 실제 데이터베이스 생성
SQL DDL 예시
-- 데이터베이스 생성
CREATE DATABASE 온라인서점;
USE 온라인서점;
-- 고객 테이블
CREATE TABLE 고객 (
고객번호 VARCHAR(10) PRIMARY KEY,
이름 VARCHAR(50) NOT NULL,
연락처 VARCHAR(20),
주소 VARCHAR(200),
가입일 DATE DEFAULT CURRENT_DATE
);
-- 도서 테이블
CREATE TABLE 도서 (
도서번호 VARCHAR(13) PRIMARY KEY, -- ISBN
제목 VARCHAR(200) NOT NULL,
저자 VARCHAR(100),
출판사 VARCHAR(100),
가격 INT CHECK (가격 > 0),
재고수량 INT DEFAULT 0 CHECK (재고수량 >= 0)
);
-- 주문 테이블
CREATE TABLE 주문 (
주문번호 VARCHAR(20) PRIMARY KEY,
주문일 DATETIME DEFAULT CURRENT_TIMESTAMP,
배송상태 VARCHAR(20) DEFAULT '주문접수',
총금액 INT CHECK (총금액 <= 10000000),
고객번호 VARCHAR(10) NOT NULL,
FOREIGN KEY (고객번호) REFERENCES 고객(고객번호)
ON DELETE RESTRICT
ON UPDATE CASCADE
);
-- 주문상세 테이블
CREATE TABLE 주문상세 (
주문번호 VARCHAR(20),
도서번호 VARCHAR(13),
수량 INT NOT NULL CHECK (수량 > 0),
단가 INT NOT NULL,
PRIMARY KEY (주문번호, 도서번호),
FOREIGN KEY (주문번호) REFERENCES 주문(주문번호)
ON DELETE CASCADE,
FOREIGN KEY (도서번호) REFERENCES 도서(도서번호)
ON DELETE RESTRICT
);
-- 인덱스 생성
CREATE INDEX idx_고객_이름 ON 고객(이름);
CREATE INDEX idx_주문_고객번호 ON 주문(고객번호);
CREATE INDEX idx_주문_주문일 ON 주문(주문일);
CREATE INDEX idx_도서_제목 ON 도서(제목);7. 설계 품질 체크리스트
좋은 데이터베이스 설계 기준
| 기준 | 설명 | 확인 사항 |
|---|---|---|
| 완전성 | 모든 요구 사항 반영 | 필요한 데이터가 모두 포함되었는가? |
| 무결성 | 데이터 정확성 유지 | 제약조건이 적절히 정의되었는가? |
| 일관성 | 데이터 모순 없음 | 정규화가 적용되었는가? |
| 확장성 | 향후 변경 용이 | 구조 변경이 쉬운가? |
| 성능 | 효율적 접근 | 인덱스가 적절한가? |
설계 시 주의사항
[개념적 설계]
□ 모든 개체가 식별되었는가?
□ 개체 간 관계가 명확한가?
□ 속성이 적절한 개체에 할당되었는가?
[논리적 설계]
□ 모든 개체가 릴레이션으로 변환되었는가?
□ N:M 관계가 별도 릴레이션으로 분해되었는가?
□ 기본키와 외래키가 정의되었는가?
□ 정규화를 통해 이상(Anomaly)을 제거했는가?
[물리적 설계]
□ 자주 사용되는 쿼리에 맞는 인덱스가 있는가?
□ 대용량 테이블에 파티셔닝이 필요한가?
□ 저장 공간이 효율적으로 사용되는가?8. 설계 방법 비교
| 방법 | E-R 모델 + 변환 규칙 | 정규화 |
|---|---|---|
| 접근 방식 | Top-Down (하향식) | Bottom-Up (상향식) |
| 시작점 | 요구 사항 명세서 | 기존 데이터/속성 |
| 주요 작업 | 개체 추출 → 관계 설정 → 변환 | 함수 종속성 분석 → 분해 |
| 장점 | 직관적, 전체 구조 파악 용이 | 이론적, 이상 현상 제거 보장 |
| 단점 | 대규모 시스템에서 복잡 | 전체 구조 파악 어려움 |
정리
데이터베이스 설계 5단계
1. 요구 사항 분석 → 요구 사항 명세서
2. 개념적 설계 → E-R 다이어그램 (개념적 스키마)
3. 논리적 설계 → 릴레이션 스키마 (논리적 스키마)
4. 물리적 설계 → 저장 구조, 인덱스 (물리적 스키마)
5. 구현 → 실제 데이터베이스E-R → 릴레이션 변환 규칙
| 변환 대상 | 변환 방법 |
|---|---|
| 개체 | 릴레이션으로 변환 |
| 1:N 관계 | N쪽에 외래키 추가 |
| N:M 관계 | 새 릴레이션 생성 (양쪽 FK) |
| 1:1 관계 | 한쪽에 외래키 추가 |
| 다중 값 속성 | 별도 릴레이션 생성 |
핵심 포인트
- 요구 사항 분석이 설계의 기초 - 잘못된 분석은 전체 설계에 영향
- 개념적/논리적 설계가 핵심 단계 - DBMS 독립적 설계 후 변환
- 정규화로 이상 현상 제거 - 삽입/삭제/수정 이상 방지
- 인덱스 설계로 성능 최적화 - 조회 패턴 분석 필수