Post

관계형 DB 디자인

Posted Updated
By Jaebaek Lee
15 min read
관계형 DB 디자인

ER to Relational DB

ER(Entity-Relationship) 모델을 Relation으로 매핑해 관리할 수 있음

Step1: Mapping of Regular Entity Types

  • ER 스키마의 엔터티 타입 $E$에 대해서, $E$의 모든 속성을 포함하는 relation $R$을 작성
  • 단순 속성만 포함, 복합 속성은 단순 구성요소로 분해 후 포함
  • $E$의 key 특성 중 하나를 $R$의 primary key로 설정
  • 만약 복합 키(composite key)라면, 여러 개의 simple attribute을 모두 키로 사용
  • $E$의 각 속성은 $R$의 속성으로 매핑
  • e.g. 학생, 직원, 교수

Step2: Mapping of Weak Entity Types

  • weak entity type $W$에 대해, $W$의 모든 속성을 저장하는 새 relation $S$ 생성
  • owner entity $E$의 primary key를 $R$의 foreign key로 포함
  • $S$의 기본 키는, $E$의 primary key와 $W$의 partial key의 조합
  • weak entity type $W_1$이 또 다른 weak entity type $W_2$의 owner entity라면, $W_1$의 매핑이 선행되어야함
  • e.g. 부양 가족, 학부모

Step3: Mapping of Binary 1:1 Relationship types

  • relation $R$에 참여하는 두 엔터티 $E_1, E_2$ 사이의 관계에서, 각각의 인스턴스가 서로 하나에만 연결되는 경우
    1. Foreign Key Approach
    • $E_1, E_2$의 relation $S, T$에 대해, $T$의 primary key를 $S$의 기본 키로 포함
    • 예시:
      Student(StudentID, Name)과 StudentCard(CardID, IssueDate)의 관계에서, Student의 primary key를 StudentCard의 foreign key로 설정

      • Student
        | StudentID(PK) | Name |
        |—————-|————|
        | 101 | 홍길동 |
        | 102 | 김철수 |

      • StudentID
        | CardID(PK) | StudentID(FK) | IssueDate | |——————-|—————-|—————-| | C001 | 101 | 2025-01-01 | | C002 | 102 | 2025-01-02 |

      • 학생증 테이블의 학번(StudentID)은 학생 테이블의 학번(StudentID)을 참조하는 Foreign Key로 설정

  1. Merged Relation
    • 두 relation $S, T$를 하나의 relation $U$로 병합
    • $U$는 $S$와 $T$의 모든 속성을 포함
    • $U$의 primary key는 $S$와 $T$의 primary key 중 하나를 선택
    • 예시:
      Student(StudentID, Name)과 StudentCard(CardID, IssueDate)의 관계에서, 두 relation을 병합하여 하나의 relation 생성
      • StudentCard
        | StudentID(PK) | Name | CardID | IssueDate | |—————-|——–|———-|————-| | 101 | 홍길동 | C001 | 2025-01-01 | | 102 | 김철수 | C002 | 2025-01-02 |
  2. Relationship Relation
    • 관계를 별도의 relation $R$로 생성
    • $R$은 관계에 참여하는 두 엔터티 $E_1, E_2$의 primary key를 포함
    • $R$의 primary key는 $E_1$과 $E_2$의 primary key의 조합
    • 관계의 속성은 $R$의 속성으로 포함
    • 예시:
      Student(StudentID, Name)과 StudentCard(CardID, IssueDate)의 관계에서, 관계를 별도의 relation으로 생성

    • Student
      | StudentID(PK) | Name |
      |—————|——–|
      | 101 | 홍길동 |
      | 102 | 김철수 |

    • StudentCard
      | CardID(PK) | IssueDate |
      |————|————-|
      | C001 | 2025-01-01 |
      | C002 | 2025-01-02 |

    • Relationship
      | StudentID(FK) | CardID(FK) |
      |—————|————|
      | 101 | C001 |
      | 102 | C002 |

Step4: Mapping of Binary 1:N Relationship types

  • relation $R$에 참여하는 두 엔터티 $E_1, E_2$ 사이의 관계에서, $E_1$의 각 인스턴스가 $E_2$의 여러 인스턴스와 연결되는 경우
  • $E_1$의 relation $S$와 $E_2$의 relation $T$에 대해, $S$의 primary key를 $T$의 foreign key로 포함
  • Relationship에도 속성이 있다면, 왜래키와 함께 N쪽의 테이블에 포함
  • 예시:
    Department(DeptID, DeptName)과 Employee(EmpID, EmpName, DeptID)의 Works_For 관계에서, Department의 primary key를 Employee의 foreign key로 설정, 관계의 특성은 Employee의 특성으로 포함

    • Department
      | DeptID(PK) | DeptName |
      |————|————|
      | D001 | HR |
      | D002 | IT |

    • Employee
      | EmpID(PK) | EmpName | DeptID(FK) | StartDate | |———–|———–|————|———-| | E001 | 홍길동 | D001 | 2020-01-01 | | E002 | 김철수 | D002 | 2021-03-01 | | E003 | 이영희 | D001 | 2022-07-15 |

Step5: Mapping of Binary M:N Relationship types

  • relation $R$에 참여하는 두 엔터티 $E_1, E_2$ 사이의 관계에서, $E_1$의 여러 인스턴스가 $E_2$의 여러 인스턴스와 연결되는 경우
  • 관계를 별도의 relation $S$로 생성
  • $S$은 관계에 참여하는 두 엔터티 $E_1, E_2$의 primary key를 포함
  • $S$의 primary key는 $E_1$과 $E_2$의 primary key의 조합
  • 관계의 속성은 $S$의 속성으로 포함
  • 예시:
    Student(StudentID, Name)과 Course(CourseID, CourseName)의 Enrolls 관계에서, 관계를 별도의 relation으로 생성

    • Student
      | StudentID(PK) | Name |
      |—————|——–|
      | 101 | 홍길동 |
      | 102 | 김철수 |

    • Course
      | CourseID(PK) | CourseName |
      |————–|————–|
      | C001 | 데이터베이스 |
      | C002 | 운영체제 |

    • Enrolls
      | StudentID(FK) | CourseID(FK) | EnrollDate |
      |—————|————–|————–|
      | 101 | C001 | 2025-03-01 |
      | 102 | C002 | 2025-03-02 |
      | 101 | C002 | 2025-03-03 |

Step6: Mapping of Multivalued Attributes

  • 다중값 속성 $A$를 가진 엔터티 $E$에 대해, $A$를 저장하는 별도의 relation $R$ 생성
  • $R$은 $E$의 primary key와 $A$를 포함
  • $R$의 primary key는 $E$의 primary key와 $A$의 조합

  • 예시:
    Student(StudentID, Name) 엔터티가 다중값 속성 Club을 가진 경우, Club을 별도의 relation으로 생성

    • Student
      | StudentID(PK) | Name |
      |—————|——–|
      | 101 | 정재혁 |
      | 102 | 배용남 |

    • Student_Club
      | StudentID(FK) | Club(PK) |
      |—————|————|
      | 101 | 댄스부 |
      | 101 | 패션부 |
      | 102 | 패션부 |

Step7: Mapping of n-ary Relationship Types

  • 3개 이상의 entity가 하나의 관계에 동시에 참여하는 경우
  • 관계를 별도의 relation $R$로 생성
  • $R$은 관계에 참여하는 모든 엔터티의 primary key를 포함
  • $R$의 primary key는 참여 엔터티들의 primary key의 조합
  • 관계의 속성은 $R$의 속성으로 포함

  • 예시:
    Project(ProjectID, ProjectName)과 Employee(EmpID, EmpName) 그리고 Department(DeptID, DeptName)의 Manages 관계에서, 관계를 별도의 relation으로 생성

    • Project
      | ProjectID(PK) | ProjectName |
      |—————|—————|
      | P001 | 신입 공채 |
      | P002 | 데이터 분석 |

    • Employee
      | EmpID(PK) | EmpName |
      |———–|———–|
      | E001 | 홍길동 |
      | E002 | 김철수 |

    • Department
      | DeptID(PK) | DeptName |
      |————|————|
      | D001 | HR |
      | D002 | IT |

    • Manages
      | ProjectID(FK) | EmpID(FK) | DeptID(FK) | StartDate |
      |—————|———–|————|————-|
      | P001 | E001 | D001 | 2025-01-01 |
      | P002 | E002 | D002 | 2025-02-01 |

EER to Relational DB

EER은 ER에 specialization과 generalization이 추가된 개념.
이를 위한 매핑 전략이 따로 필요

Stpe8: Mapping of Specialization or Generalization

각 specialization을 $m$개의 subclass ${S_1, \dots, S_m}$로 변환, superclass $C$를 ${k,a_1, \dots, a_n}$의 relation으로 매핑

Option A: Superclass + Subclass

  • superclass $C$를 relation $R$로 변환, $R$은 $C$의 primary key $k$와 모든 속성 ${a_1, \dots, a_n}$을 포함
  • 각 subclass $S_i$를 relation $R_i$로 변환, $R_i$는 $S_i$의 primary key $k$와 $S_i$의 속성 ${b_1, \dots, b_p}$를 포함
  • $R_i$의 primary key는 $k$
  • $R_i$의 $k$는 $R$의 foreign key로 설정

  • 예시:
    Person(PersonID, Name)이 superclass이고, Student(StudentID, Major)와 Employee(EmpID, DeptID)가 subclass인 경우

    • Person
      | PersonID(PK) | Name |
      |————–|——–|
      | 101 | 정재혁 |
      | 102 | 배용남 |

    • Student
      | StudentID(PK, FK) | Major |
      |——————-|———|
      | 101 | CS |

    • Employee
      | EmpID(PK, FK) | DeptID |
      |—————|———-|
      | 102 | D001 |

Option B: Subclass Only

  • 각 subclass $S_i$를 relation $R_i$로 변환, $R_i$는 $S_i$의 primary key $k$와 $S_i$의 속성 ${b_1, \dots, b_p}$를 포함
  • $R_i$의 primary key는 $k$
  • superclass $C$는 별도의 relation으로 생성하지 않음
  • Total specialization(상위 클래스의 모든 개체가 반드시 어떤 하위 클래스에 속함)일 때만 가능

  • 예시:
    Person(PersonID, Name)이 superclass이고, Student(StudentID, Major)와 Employee(EmpID, DeptID)가 subclass인 경우

    • Student
      | PersonID(PK) | Major | Name | |—————|———|—-| | 101 | CS | 정재혁 |

    • Employee
      | PersonID(PK) | DeptID | Name | |———–|———-|—-| | 102 | D001 | 배용남 |

Option C: Single relation with 1 Type Attribute

  • superclass $C$와 모든 subclass ${S_1, \dots, S_m}$를 하나의 relation $R$로 병합
  • $R$은 $C$의 primary key $k$, 모든 속성 ${a_1, \dots, a_n}$, 각 subclass의 속성 ${b_1, \dots, b_p}$, 그리고 type attribute $t$를 포함
  • $t$는 각 튜플이 어떤 subclass에 속하는지 나타냄

  • 예시:
    Person(PersonID, Name)이 superclass이고, Student(StudentID, Major)와 Employee(EmpID, DeptID)가 subclass인 경우

    • Person
      | PersonID(PK) | Name | Type | Major | DeptID |
      |————–|——–|———–|———|———-|
      | 101 | 정재혁 | Student | CS | NULL |
      | 102 | 배용남 | Employee | NULL | D001 |

Option D: Single relation with Multiple Type Attributes

  • superclass $C$와 모든 subclass ${S_1, \dots, S_m}$를 하나의 relation $R$로 병합
  • $R$은 $C$의 primary key $k$, 모든 속성 ${a_1, \dots, a_n}$, 각 subclass의 속성 ${b_1, \dots, b_p}$, 그리고 각 subclass에 대한 type attribute ${t_1, \dots, t_m}$를 포함
  • 각 type attribute $t_i$는 튜플이 해당 subclass에 속하는지 여부를 나타냄

  • 예시:
    Person(PersonID, Name)이 superclass이고, Student(StudentID, Major)와 Employee(EmpID, DeptID)가 subclass인 경우

    • Person
      | PersonID(PK) | Name | IsStudent | IsEmployee | Major | DeptID |
      |————–|——–|———–|————|———|———-|
      | 101 | 정재혁 | TRUE | FALSE | CS | NULL |
      | 102 | 배용남 | FALSE | TRUE | NULL | D001 |

Mapping of Shared Subclasses

  • 두 개 이상의 Superclass에 동시에 속하는 공통 Subclass
  • 매핑 방법이 단독 상속일때와 다르지 않음, 다만 키 관리에 유의
  • 예시: Student(StudentID, Major)와 Employee(EmpID, DeptID)가 superclass 이고, StudentAssistant가 subclass인 경우

    • StudentAssistant
      | StudentID(PK, FK) | EmpID(PK, FK) | HoursWorked | Name |
      |——————-|—————|————-|——| | 101 | 201 | 20 | 정재혁 |

    • Student
      | StudentID(PK) | Major | Name | |—————|———|——| | 101 | 컴퓨터공학 | 정재혁 | | 102 | 경영학 | 조정구 |

    • Employee
      | EmpID(PK) | DeptID | Name | |———–|———-|——| | 201 | D001 | 정재혁 | | 202 | D002 | 이호창 |

Step9: Mapping of Categories

  • category는 여러 superclass의 공통 속성을 가진 subclass(슈퍼클래스들이 전혀 다른 종류일 수도 있음)
  • category를 별도의 relation $R$로 생성
  • $R$은 category에 참여하는 모든 superclass의 primary key를 포함
  • $R$의 primary key는 참여 superclass의 primary key의 조합
  • category의 속성은 $R$의 속성으로 포함
  • 예시:
    Person(PersonID, Name)와 Company(C_Name, C_Address)가 superclass이고, Vehicle(VehicleID, Type)이 category인 경우

    • Person
      | PersonID(PK) | Name |
      |————–|——–|
      | 101 | 정재혁 |
      | 102 | 배용남 |

    • Company
      | C_Name(PK) | C_Address |
      |————–|————-|
      | 삼성전자 | 서울 |
      | LG전자 | 경기 |

    • Vehicle
      | VehicleID(PK) | Type | PersonID(FK) | C_Name(FK) |
      |—————|————|————–|————|
      | V001 | 승용차 | 101 | NULL |
      | V002 | 트럭 | NULL | 삼성전자 |
      | V003 | 오토바이 | 102 | NULL |

Information System Life Cycle

정보 시스템 구축의 전반적 과정

  1. Feasibility Analysis (타당성 분석)
    비용-효과 분석, 범위 설정, 우선순위 결정

  2. Requirements Collection & Analysis (요구사항 수집/분석)
    사용자 인터뷰, 기대 기능 파악

  3. Design (설계)
    DB 시스템과 애플리케이션 시스템의 구조 설계

  4. Implementation (구현)
    실제 시스템 구축

  5. Validation & Acceptance Testing (검증/수용 테스트)
    성능 및 사양 충족 여부 테스트

  6. Deployment & Maintenance (배포/운영/유지보수)
    현장 적용 및 이후 업데이트

DB Application System Life Cycle

데이터베이스 고유의 생명 주기

  1. System Definition

  2. DB Design

  3. DB Implementation

  4. Loading / Data Conversion

  5. Application Conversion (가장 시간 많이 걸림)

  6. Testing & Validation

  7. Operation (기존 시스템과 병행 운영 가능)

  8. Monitoring & Maintenance

DB Design – 목표와 문제

  • 문제:
    사용자와 애플리케이션 요구를 반영하는 논리/물리적 구조 설계

  • 목표:

    • 정보 요구 만족

    • 이해하기 쉬운 구조

    • 성능 만족

  • Understandability vs Performance 사이의 균형 필요

DB Design & Implementation Phases

  • Phase 1
    Requirement Collection and Analysis (요구사항 수집 및 분석)
  • Phase 2
    Conceptual DB Design (ER/EER 디자인)
  • Phase 3
    DBMS 선택
  • Phase 4
    Logical Design (Relation 모델)
  • Phase 5
    Physical Design (인덱스, 저장 구조 등)
  • Phase 6
    Testing, Operation, Maintenance

해당 포스트는 서울대학교 산업공학과 박종헌 교수님의 데이터관리와 분석 25-1학기 강의를 정리한 내용입니다.

데이터베이스
데이터베이스 DB 강의정리
This post is licensed under CC BY 4.0 by the author.