7장 : 데이터 모델링과 ERD
ER 모델을 릴레이션으로 변환
ERD를 설계했으면 이제 실제 테이블로 변환해야 합니다. 개념적 모델의 개체·관계·속성을 릴레이션 스키마로 바꾸는 일정한 규칙이 있습니다. 이 절에서는 7가지 변환 규칙을 하나씩 적용하며, 쇼핑몰과 학사 관리 시스템을 완전한 SQL DDL로 구현합니다.
변환 규칙 전체 흐름
┌──────────────────────────────────────────────────────────┐
│ ER → 릴레이션 변환 순서 │
├──────────────────────────────────────────────────────────┤
│ │
│ 1단계: 강한 개체(Strong Entity) → 테이블 │
│ 2단계: 약한 개체(Weak Entity) → 테이블 │
│ 3단계: 1:1 관계 → FK 배치 │
│ 4단계: 1:N 관계 → N쪽에 FK │
│ 5단계: M:N 관계 → 교차 테이블 │
│ 6단계: 다중값 속성 → 별도 테이블 │
│ 7단계: 복합 속성 / 파생 속성 처리 │
│ │
│ ※ 순서가 중요: 강한 개체부터 만들어야 │
│ FK 참조 대상이 존재 │
│ │
└──────────────────────────────────────────────────────────┘1단계: 강한 개체의 변환
강한 개체는 그대로 하나의 테이블이 됩니다. 키 속성은 기본키(PK)가 되고, 나머지 속성은 컬럼이 됩니다.
강한 개체 → 테이블
* 개체명 → 테이블명
* 키 속성 → PRIMARY KEY
* 단순 속성 → 일반 컬럼
* 복합 속성 → 하위 속성들을 개별 컬럼
* 파생 속성 → 저장하지 않거나 별도 컬럼(반정규화)ER 모델
개체: 회원
속성: 회원번호(PK), 이름, 이메일, 가입일
↓ 변환
릴레이션
users (
id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) UNIQUE,
created_at DATE DEFAULT SYSDATE
)ER 모델
개체: 상품
속성: 상품번호(PK), 상품명, 가격, 재고수량
↓ 변환
릴레이션
products (
id NUMBER PRIMARY KEY,
name VARCHAR2(200) NOT NULL,
price NUMBER(10,2) NOT NULL CHECK (price >= 0),
stock NUMBER DEFAULT 0 CHECK (stock >= 0)
)강한 개체 변환 시 주의할 점입니다.
1. PK 전략
* 자연키 (학번, 주민번호 등) → ER 모델의 키 속성 그대로
* 대리키 (일련번호) → 실무에서 선호
* 복합키 → 2개 이상 속성으로 구성된 키
2. NOT NULL 판단
* 필수 속성 → NOT NULL
* 선택 속성 → NULL 허용
* ER 모델의 참여 제약 참고
3. 도메인 제약
* 가격 ≥ 0, 수량 ≥ 0 등 CHECK 제약
* 이메일 형식 등 패턴 제약
* UNIQUE 제약 (후보키)
4. 네이밍 규칙
* 개체명: 단수 → 테이블명: 복수 (관례)
* 속성명: snake_case 통일 (실무 표준)2단계: 약한 개체의 변환
약한 개체는 소유 개체의 기본키를 외래키(FK)로 포함하고, 부분 키(Partial Key)와 합쳐서 복합 기본키를 만듭니다.
약한 개체 → 테이블
* 소유 개체의 PK → FK + PK의 일부
* 부분 키 → PK의 나머지 부분
* 결과 PK = {소유 개체 PK, 부분 키}
* 식별 관계(Identifying Relationship) 통해 연결ER 모델
주문(Order) ──식별관계──→ 주문상세(OrderItem, 약한 개체)
OrderItem의 부분 키: item_seq
↓ 변환
릴레이션
order_items (
order_id NUMBER REFERENCES orders(id),
item_seq NUMBER,
product_id NUMBER NOT NULL,
quantity NUMBER NOT NULL,
price NUMBER(10,2) NOT NULL,
PRIMARY KEY (order_id, item_seq)
)ER 모델
건물(Building) ──식별관계──→ 강의실(Room, 약한 개체)
Room의 부분 키: room_number
↓ 변환
릴레이션
buildings (
id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
addr VARCHAR2(200)
)
rooms (
building_id NUMBER REFERENCES buildings(id),
room_number VARCHAR2(10),
capacity NUMBER,
has_projector CHAR(1) DEFAULT 'N',
PRIMARY KEY (building_id, room_number)
)복합 기본키 vs 대리키
복합키 방식
order_items (
order_id NUMBER,
item_seq NUMBER,
...
PRIMARY KEY (order_id, item_seq)
)
대리키 방식 (실무에서 더 흔함)
order_items (
id NUMBER PRIMARY KEY, ← 대리키
order_id NUMBER NOT NULL REFERENCES orders(id),
...
UNIQUE (order_id, item_seq) ← 유일성은 UNIQUE로
)
대리키를 사용하는 이유
* ORM(JPA, TypeORM 등)과의 호환성
* FK 참조 시 조인 컬럼이 단일 → 쿼리 단순화
* 키 변경 시 영향 범위 감소
* 단, 이론적 정석은 복합키3단계: 1:1 관계의 변환
1:1 관계는 세 가지 방법으로 변환할 수 있습니다.
방법 1: 한쪽 테이블에 FK 추가 (가장 일반적)
* 전체 참여 쪽에 FK를 놓는 것이 유리
* FK + UNIQUE 제약으로 1:1 보장
회원(user_id PK) ── 1:1 ── 프로필(profile)
users (id, name, email)
profiles (id PK, user_id FK UNIQUE, bio, avatar)
→ user_id에 UNIQUE 제약 = 1:1 보장
방법 2: 양쪽 테이블 병합
* 두 개체를 하나의 테이블로 통합
* 속성이 적거나 항상 함께 사용될 때
users (id, name, email, bio, avatar)
→ 프로필 속성을 users에 직접 포함
방법 3: 관계 테이블 생성 (거의 사용 안 함)
* 양쪽 PK를 FK로 갖는 별도 테이블
* 관계에 속성이 많거나 선택적 참여일 때
user_profiles (user_id FK UNIQUE, profile_id FK UNIQUE, created_at)┌──────────────────────────────────────────────────┐
│ FK를 어디에 놓을 것인가? │
├──────────────────────────────────────────────────┤
│ │
│ 원칙: 전체 참여(Total Participation) 쪽에 FK │
│ │
│ 사원 ─(1:1)─ 주차공간 │
│ 사원: 주차공간이 없을 수도 (부분 참여) │
│ 주차공간: 반드시 사원에게 배정 (전체 참여) │
│ │
│ → 주차공간 테이블에 employee_id FK │
│ → NULL이 발생하지 않아 공간 낭비 없음 │
│ │
│ 양쪽 모두 부분 참여이면: │
│ → 어느 쪽이든 가능, 접근 빈도가 높은 쪽 선택 │
│ │
└──────────────────────────────────────────────────┘4단계: 1:N 관계의 변환
1:N 관계는 가장 흔한 관계이며, 변환 규칙도 가장 단순합니다.
N쪽 테이블에 1쪽의 PK를 FK로 추가
부서(1) ──(1:N)── 직원(N)
departments (id PK, name, location)
employees (id PK, dept_id FK, name, hire_date)
→ dept_id가 departments.id를 참조ER 모델
교수(1) ──(1:N)── 과목(N)
관계 속성: 담당학기, 강의실
변환 방법 1: N쪽에 관계 속성도 포함
courses (
id NUMBER PRIMARY KEY,
prof_id NUMBER REFERENCES professors(id),
name VARCHAR2(100),
semester VARCHAR2(10), ← 관계 속성
room VARCHAR2(20) ← 관계 속성
)
변환 방법 2: 별도 테이블 (관계 속성이 많을 때)
course_assignments (
prof_id NUMBER REFERENCES professors(id),
course_id NUMBER REFERENCES courses(id),
semester VARCHAR2(10),
room VARCHAR2(20),
PRIMARY KEY (prof_id, course_id, semester)
)ER 모델
사원(1) ──관리──→ 사원(N)
(상사 1명이 부하 직원 N명을 관리)
변환
employees (
id NUMBER PRIMARY KEY,
name VARCHAR2(50),
manager_id NUMBER REFERENCES employees(id)
)
→ 최상위 관리자의 manager_id = NULL
SQL 조회
-- 김부장의 직속 부하
SELECT e.name
FROM employees e
WHERE e.manager_id = (
SELECT id FROM employees WHERE name = '김부장'
);5단계: M:N 관계의 변환
M:N 관계는 반드시 교차 테이블(Junction Table)을 생성하여 해소합니다.
양쪽 개체의 PK를 FK로 포함하는 새 테이블 생성
* 관계 속성이 있으면 교차 테이블의 컬럼으로 추가
* PK = 양쪽 FK의 복합 (또는 대리키)ER 모델
학생 ──M:N──→ 과목 (수강 관계, 관계 속성: 학점, 수강년도)
↓ 변환
students (
id NUMBER PRIMARY KEY,
name VARCHAR2(50) NOT NULL
)
courses (
id NUMBER PRIMARY KEY,
title VARCHAR2(100) NOT NULL,
credit NUMBER NOT NULL
)
enrollments ( ← 교차 테이블
student_id NUMBER REFERENCES students(id),
course_id NUMBER REFERENCES courses(id),
grade VARCHAR2(2), ← 관계 속성
year NUMBER, ← 관계 속성
semester VARCHAR2(10),
PRIMARY KEY (student_id, course_id, year, semester)
)ER 모델
사용자 ──M:N──→ 사용자 (팔로우 관계)
↓ 변환
users (
id NUMBER PRIMARY KEY,
username VARCHAR2(50) UNIQUE
)
follows ( ← 교차 테이블
follower_id NUMBER REFERENCES users(id),
following_id NUMBER REFERENCES users(id),
created_at DATE DEFAULT SYSDATE,
PRIMARY KEY (follower_id, following_id),
CHECK (follower_id != following_id) ← 자기 팔로우 방지
)양쪽 테이블명을 조합하거나 관계명을 사용
students + courses → enrollments (관계명)
users + users → follows (관계명)
posts + tags → post_tags (양쪽 조합)
users + roles → user_roles (양쪽 조합)
orders + products → order_items (관계명)
네이밍 원칙
* 관계에 의미가 있으면 관계명 사용 (enrollments, follows)
* 단순 연결이면 양쪽 조합 (post_tags, user_roles)
* 알파벳 순서로 조합하는 관례도 있음6단계: 다중값 속성의 변환
다중값 속성(Multi-valued Attribute)은 별도의 테이블로 분리합니다.
다중값 속성 → 새 테이블
* 원래 개체의 PK를 FK로 포함
* 다중값 속성을 일반 컬럼으로
* PK = {FK, 속성값} 또는 대리키ER 모델
회원 ── 전화번호 (다중값: 집, 회사, 휴대폰)
↓ 변환
users (id, name, email)
user_phones (
user_id NUMBER REFERENCES users(id),
phone VARCHAR2(20) NOT NULL,
type VARCHAR2(10), ← '집', '회사', '휴대폰'
PRIMARY KEY (user_id, phone)
)ER 모델
사원 ── 취미 (다중값)
↓ 변환
employees (id, name, dept_id)
employee_hobbies (
employee_id NUMBER REFERENCES employees(id),
hobby VARCHAR2(50) NOT NULL,
PRIMARY KEY (employee_id, hobby)
)7단계: 복합 속성과 파생 속성
복합 속성은 하위 속성들을 개별 컬럼으로 분해합니다.
ER 모델
주소 = { 시, 구, 동, 상세주소, 우편번호 }
↓ 변환
users (
id,
name,
addr_city VARCHAR2(50),
addr_district VARCHAR2(50),
addr_dong VARCHAR2(50),
addr_detail VARCHAR2(200),
addr_zipcode VARCHAR2(10)
)파생 속성(Derived Attribute)은 다른 속성에서 계산되는 값입니다.
파생 속성 예시
* 나이 ← 생년월일에서 계산
* 주문총액 ← SUM(수량 * 단가)
* 재직기간 ← CURRENT_DATE - 입사일
처리 방법
┌──────────────────────────────────────────────────┐
│ 방법 1: 저장하지 않음 (정규화 원칙) │
│ * 조회 시 매번 계산 │
│ * SELECT EXTRACT(YEAR FROM AGE(birth_date)) │
│ AS age FROM users; │
│ * 장점: 항상 최신 값, 갱신 이상 없음 │
│ * 단점: 계산 비용 │
│ │
│ 방법 2: 저장 (반정규화) │
│ * 컬럼으로 추가하되 일관성 유지 필요 │
│ * orders.total_amount = SUM(items.qty * price) │
│ * 장점: 조회 성능 │
│ * 단점: 갱신 시 동기화 필요 (트리거/배치) │
│ │
│ 방법 3: 가상 컬럼 / 계산 컬럼 (DBMS 기능) │
│ * Oracle: GENERATED ALWAYS AS (expr) │
│ * MySQL: AS (expr) STORED / VIRTUAL │
│ * PostgreSQL: Generated Column │
│ * 장점: 자동 계산, 인덱싱 가능 │
│ │
└──────────────────────────────────────────────────┘상속(일반화/특수화) 관계의 변환
ER 모델에서 포괄·부분 관계(ISA 관계, Generalization/Specialization)를 변환하는 3가지 전략이 있습니다.
ER 모델
사용자(User)
/ | \
학생(Student) 교수(Prof) 직원(Staff)users (
id NUMBER PRIMARY KEY,
type VARCHAR2(10) NOT NULL, ← 구분자
name VARCHAR2(50),
-- 학생 속성
student_id VARCHAR2(20),
gpa NUMBER(3,2),
-- 교수 속성
prof_id VARCHAR2(20),
department VARCHAR2(50),
-- 직원 속성
staff_id VARCHAR2(20),
position VARCHAR2(50)
)
장점: 조인 없이 조회, 단순한 구조
단점: NULL이 많아짐, 테이블이 비대해짐
적용: 하위 타입 속성이 적을 때users (
id NUMBER PRIMARY KEY,
name VARCHAR2(50)
)
students (
user_id NUMBER PRIMARY KEY REFERENCES users(id),
student_id VARCHAR2(20),
gpa NUMBER(3,2)
)
professors (
user_id NUMBER PRIMARY KEY REFERENCES users(id),
prof_id VARCHAR2(20),
department VARCHAR2(50)
)
장점: NULL 없음, 정규화된 구조
단점: 조회 시 조인 필요
적용: 공통 속성이 많고 타입별 속성도 많을 때students (
id NUMBER PRIMARY KEY,
name VARCHAR2(50), ← 공통 속성 반복
student_id VARCHAR2(20),
gpa NUMBER(3,2)
)
professors (
id NUMBER PRIMARY KEY,
name VARCHAR2(50), ← 공통 속성 반복
prof_id VARCHAR2(20),
department VARCHAR2(50)
)
장점: 조인 불필요, 타입별 독립적
단점: 공통 속성 중복, 전체 사용자 조회 시 UNION 필요
적용: 타입 간 공통점이 적을 때┌──────────────┬──────────────┬──────────────┬───────────────┐
│ 항목 │ 단일 테이블 │ 타입별 테이블│ 구체 테이블 │
├──────────────┼──────────────┼──────────────┼───────────────┤
│ NULL 발생 │ 많음 │ 없음 │ 없음 │
│ 조인 필요 │ 없음 │ 있음 │ 없음 │
│ 공통 속성 │ 한 곳 │ 한 곳 │ 반복 │
│ 타입 추가 │ 컬럼 추가 │ 테이블 추가 │ 테이블 추가 │
│ 전체 조회 │ 단순 │ 조인 │ UNION │
│ 타입별 조회 │ WHERE 필터 │ 단순 │ 단순 │
│ ORM 지원 │ 기본 전략 │ JPA JOINED │ JPA TABLE_PER │
└──────────────┴──────────────┴──────────────┴───────────────┘실습 — 쇼핑몰 ERD를 SQL로 변환
앞 절에서 설계한 쇼핑몰 ERD를 Oracle SQL로 구현합니다.
-- 1단계: 강한 개체 변환
CREATE TABLE categories (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR2(100) NOT NULL,
parent_id NUMBER REFERENCES categories(id)
);
CREATE TABLE users (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
name VARCHAR2(50) NOT NULL,
email VARCHAR2(100) NOT NULL UNIQUE,
phone VARCHAR2(20),
address VARCHAR2(300),
created_at DATE DEFAULT SYSDATE
);
-- 4단계: 1:N 관계 (카테고리-상품)
CREATE TABLE products (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
category_id NUMBER NOT NULL REFERENCES categories(id),
name VARCHAR2(200) NOT NULL,
price NUMBER(10, 2) NOT NULL CHECK (price >= 0),
stock NUMBER DEFAULT 0 CHECK (stock >= 0)
);
-- 4단계: 1:N 관계 (회원-주문)
CREATE TABLE orders (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id NUMBER NOT NULL REFERENCES users(id),
order_date DATE DEFAULT SYSDATE,
status VARCHAR2(20) DEFAULT 'pending',
total_amount NUMBER(12, 2)
);
-- 2단계: 약한 개체 변환 (주문-주문상세)
CREATE TABLE order_items (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id NUMBER NOT NULL REFERENCES orders(id)
ON DELETE CASCADE,
product_id NUMBER NOT NULL REFERENCES products(id),
quantity NUMBER NOT NULL CHECK (quantity > 0),
unit_price NUMBER(10, 2) NOT NULL
);
-- 추가 개체: 리뷰
CREATE TABLE reviews (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
user_id NUMBER NOT NULL REFERENCES users(id),
product_id NUMBER NOT NULL REFERENCES products(id),
rating NUMBER(1) CHECK (rating BETWEEN 1 AND 5),
content VARCHAR2(2000),
created_at DATE DEFAULT SYSDATE
);
-- 3단계: 1:1 관계 (주문-배송)
CREATE TABLE shipping (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
order_id NUMBER NOT NULL UNIQUE REFERENCES orders(id),
status VARCHAR2(20) DEFAULT 'preparing',
tracking_no VARCHAR2(50),
carrier VARCHAR2(50),
shipped_at DATE
);CREATE TABLE categories (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
parent_id INT,
FOREIGN KEY (parent_id) REFERENCES categories(id)
) ENGINE=InnoDB;
CREATE TABLE users (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
email VARCHAR(100) NOT NULL UNIQUE,
phone VARCHAR(20),
address VARCHAR(300),
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB;
CREATE TABLE products (
id INT AUTO_INCREMENT PRIMARY KEY,
category_id INT NOT NULL,
name VARCHAR(200) NOT NULL,
price DECIMAL(10,2) NOT NULL CHECK (price >= 0),
stock INT DEFAULT 0,
FOREIGN KEY (category_id) REFERENCES categories(id)
) ENGINE=InnoDB;
CREATE TABLE orders (
id INT AUTO_INCREMENT PRIMARY KEY,
user_id INT NOT NULL,
order_date DATETIME DEFAULT CURRENT_TIMESTAMP,
status ENUM('pending','paid','shipped','delivered','cancelled')
DEFAULT 'pending',
total_amount DECIMAL(12,2),
FOREIGN KEY (user_id) REFERENCES users(id)
) ENGINE=InnoDB;
CREATE TABLE order_items (
id INT AUTO_INCREMENT PRIMARY KEY,
order_id INT NOT NULL,
product_id INT NOT NULL,
quantity INT NOT NULL CHECK (quantity > 0),
unit_price DECIMAL(10,2) NOT NULL,
FOREIGN KEY (order_id) REFERENCES orders(id) ON DELETE CASCADE,
FOREIGN KEY (product_id) REFERENCES products(id)
) ENGINE=InnoDB;변환 과정 검증 체크리스트
□ 1. 모든 강한 개체가 테이블로 변환되었는가?
□ 2. 약한 개체의 PK에 소유 개체의 PK가 포함되었는가?
□ 3. 1:1 관계의 FK에 UNIQUE 제약이 있는가?
□ 4. 1:N 관계의 FK가 N쪽 테이블에 배치되었는가?
□ 5. M:N 관계가 교차 테이블로 해소되었는가?
□ 6. 관계 속성이 적절한 테이블에 배치되었는가?
□ 7. 다중값 속성이 별도 테이블로 분리되었는가?
□ 8. 복합 속성이 하위 속성으로 분해되었는가?
□ 9. 모든 FK의 참조 대상 테이블과 PK가 존재하는가?
□ 10. ON DELETE 정책이 업무 규칙에 맞게 설정되었는가?┌──────────────┬──────────────────────────────────┐
│ 정책 │ 사용 시점 │
├──────────────┼──────────────────────────────────┤
│ CASCADE │ 부모 삭제 시 자식도 삭제 │
│ │ 예: 주문 삭제 → 주문상세 삭제 │
├──────────────┼──────────────────────────────────┤
│ SET NULL │ 부모 삭제 시 FK를 NULL로 │
│ │ 예: 부서 삭제 → 사원.dept_id=NULL│
├──────────────┼──────────────────────────────────┤
│ RESTRICT │ 자식이 있으면 부모 삭제 불가 │
│ (NO ACTION) │ 예: 회원 삭제 시 주문이 있으면 X │
├──────────────┼──────────────────────────────────┤
│ SET DEFAULT │ 부모 삭제 시 FK를 기본값으로 │
│ │ 예: 카테고리 삭제 → '미분류'로 │
└──────────────┴──────────────────────────────────┘변환 규칙 종합 정리
┌──────────────────────────────────────────────────────────┐
│ 변환 규칙 종합 정리 │
├──────────────────────────────────────────────────────────┤
│ │
│ 1. 강한 개체 → 테이블 (PK = 키 속성) │
│ 2. 약한 개체 → 테이블 (PK = 소유PK + 부분키) │
│ 3. 1:1 → 전체 참여 쪽에 FK + UNIQUE │
│ 4. 1:N → N쪽에 FK │
│ 5. M:N → 교차 테이블 (양쪽 PK를 FK로) │
│ 6. 다중값 속성 → 별도 테이블 │
│ 7. 복합 속성 → 하위 속성을 개별 컬럼으로 │
│ 8. 파생 속성 → 저장 여부 판단 (성능 vs 무결성) │
│ 9. 상속 → 단일/타입별/구체 테이블 전략 선택 │
│ │
│ 변환 순서: │
│ 강한 개체 → 약한 개체 → 1:1 → 1:N → M:N │
│ → 다중값 → 복합/파생 → 제약조건 → 인덱스 │
│ │
└──────────────────────────────────────────────────────────┘ER 모델을 릴레이션으로 변환하는 과정에서 데이터 무결성을 보장하기 위해 적절한 제약 조건(PK, FK, UNIQUE, CHECK, NOT NULL)을 함께 부여하는 것이 핵심입니다.
다음 장에서는 이렇게 만든 테이블이 올바른 구조인지 검증하는 정규화를 다루겠습니다.