ER 모델을 릴레이션으로 변환

ERD를 설계했으면 이제 실제 테이블로 변환해야 합니다. 개념적 모델의 개체·관계·속성을 릴레이션 스키마로 바꾸는 일정한 규칙이 있습니다. 이 절에서는 7가지 변환 규칙을 하나씩 적용하며, 쇼핑몰과 학사 관리 시스템을 완전한 SQL DDL로 구현합니다.

변환 규칙 전체 흐름

1단계: 강한 개체의 변환

강한 개체는 그대로 하나의 테이블이 됩니다. 키 속성은 기본키(PK)가 되고, 나머지 속성은 컬럼이 됩니다.

강한 개체 변환 시 주의할 점입니다.

2단계: 약한 개체의 변환

약한 개체는 소유 개체의 기본키를 외래키(FK)로 포함하고, 부분 키(Partial Key)와 합쳐서 복합 기본키를 만듭니다.

3단계: 1:1 관계의 변환

1:1 관계는 세 가지 방법으로 변환할 수 있습니다.

4단계: 1:N 관계의 변환

1:N 관계는 가장 흔한 관계이며, 변환 규칙도 가장 단순합니다.

5단계: M:N 관계의 변환

M:N 관계는 반드시 교차 테이블(Junction Table)을 생성하여 해소합니다.

M:N 관계 변환 규칙

양쪽 개체의 PK를 FK로 포함하는 새 테이블 생성
  * 관계 속성이 있으면 교차 테이블의 컬럼으로 추가
  * PK = 양쪽 FK의 복합 (또는 대리키)

6단계: 다중값 속성의 변환

다중값 속성(Multi-valued Attribute)은 별도의 테이블로 분리합니다.

다중값 속성 변환 규칙

다중값 속성 → 새 테이블
  * 원래 개체의 PK를 FK로 포함
  * 다중값 속성을 일반 컬럼으로
  * PK = {FK, 속성값} 또는 대리키

7단계: 복합 속성과 파생 속성

복합 속성은 하위 속성들을 개별 컬럼으로 분해합니다.

파생 속성(Derived Attribute)은 다른 속성에서 계산되는 값입니다.

상속(일반화/특수화) 관계의 변환

ER 모델에서 포괄·부분 관계(ISA 관계, Generalization/Specialization)를 변환하는 3가지 전략이 있습니다.

예시: 사용자 상속 구조

ER 모델
           사용자(User)
          /       |      \
학생(Student) 교수(Prof) 직원(Staff)

전략 2: 각 타입별 테이블 (Table per Type)

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 없음, 정규화된 구조
단점: 조회 시 조인 필요
적용: 공통 속성이 많고 타입별 속성도 많을 때

실습 — 쇼핑몰 ERD를 SQL로 변환

앞 절에서 설계한 쇼핑몰 ERD를 Oracle SQL로 구현합니다.

쇼핑몰 스키마 생성 (Oracle)

-- 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
);

쇼핑몰 스키마 (MySQL 버전)

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;

변환 과정 검증 체크리스트

ER → 릴레이션 변환 검증 10항목

□  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 정책이 업무 규칙에 맞게 설정되었는가?

변환 규칙 종합 정리

ER 모델을 릴레이션으로 변환하는 과정에서 데이터 무결성을 보장하기 위해 적절한 제약 조건(PK, FK, UNIQUE, CHECK, NOT NULL)을 함께 부여하는 것이 핵심입니다.

다음 장에서는 이렇게 만든 테이블이 올바른 구조인지 검증하는 정규화를 다루겠습니다.

ER 모델을 릴레이션으로 변환

목차