[Database] 6. ER, EER을 Mapping해 Relational DB로

REF: fundamentals of database systems 7th edition

miniworld를 표현하기위해 high-level의 ER, EER을 그렸다. 이 챕터에서는 conceptual database design을 logical database design으로 바꾸는 data model mapping에 대해 공부한다.

• 3장에서 계속 사용했던 이 ERD를 사용한다.

ER-to-Relational Mapping

1. Mapping Regular Entity Types

• ⭐️strong entity type
  • ER schema의 모든 strong entity type을 relation으로 만든다.
• ⭐️only simple attribute
  • simple attribute만을 포함
  • ⭐️composite attribute를 simple attribute로 변환한다.
• ⭐️primary key 식별
  • Key attribute 중 primary key를 하나 고른다.
• ⭐️multiple key
  • multiple key가 식별되면, indexing이나 다른 분석을 위해 key에대한 정보를 가지고있는다.
• ⭐️not FK, relationship attribute
  • FK와 relationship attribute는 이후 단계에서 표현한다.

결과

• Department의 다중속성 location은 여기에서 포함되지 않는다.
• Employee의 복합속성 이름은 simple attribute로 표현되어 들어온다.

2. Mapping Weak Entity Types

• ⭐️weak entity각각을 모두 relation으로 만든다.

• ⭐️only simple attribute
  • simple attribute만을 포함
  • composite attribute를 simple attribute로 변환한다.
• ⭐️foreign key
  • weak entity는 owner entity없이는 식별되지 않으므로, ⭐️owner entity의 pk를 fk화 하여 식별한다.
  • owner entity의 PK를 자신의 PK이자 FK로 attribute화한다.
• ⭐️primary key
  • weak entity의 PK는 owner entity의 FK와 자신의 partial key를 복합속성으로 갖게된다.
  • PK : primary key of Owner entity type + partial key of Weak entity type

결과

• ESSN은 rename한것에 지나지 않음.

3. Mapping Binary 1:1 Relationship Types

⭐️Foreign key approach

1. 1:1 관계가지는 relation 중 자식을 S, 부모를 T로 가정하고, 자식에 부모의 PK를 FK로 포함
2. 일반적으로 ⭐️total-participation하는 relation을 S(자식)로 둬야한다.(공간낭비 막기위해)
3. relation attribute는 simple attribute들을 (composite의 simple attr) S에 표현

• 예시로, 직원과 부서간의 부서장관계를 보자.
  1. 부서장과 부서는 1:1이며, 부서는 전체참여
  2. 따라서 total participate하는 부서를 자식으로 보고, 부서에 직원의 ssn을 FK로 넣어야한다.
  3. relationship attribute인 start_date도 department에 넣는다.
• total participate하지 않는 직원에 start_date와 Dnumber를 넣을수도 있지만, 이러면 부서장이 아닌 직원은 NULL값을 가지므로, 낭비를 하게된다.

⭐️Merged relation approach

• 2개의 entity type와 relationship을 단일 relation으로 통합할 수 있다.
• 단,
  1. ⭐️both total participate
  2. 다른 relation type이 없고
  3. ⭐️2개의 테이블이 모두 같은 수의 tuple을 가질때만 적용 가능하다.

결과

4. Mapping Binary 1:N Relationship Types

⭐️Foreign key approach

1. ⭐️N-side의 entity를 S(자식)으로 둔다.
2. 1-side의 entity를 T(부모)로 둔다.
3. relation attribute는 simple attribute들을 (composite의 simple attr) S에 표현

N-side의 entity(S)는 최소한 1번 1-side의 entity(T)와 관계를 가지기때문에 1-side(T)의 primary key를 ⭐️N-side(S)에 foreign key로 넣어준다.

1:1의 mapping에서는 total-participate하는 relation을 자식테이블로 봤지만,
1:N에서는 N-side를 자식으로 본다.

⭐️Cross-reference or relationship relation approach

• 두개의 entity를 모두 참조하는 ⭐️새로운 relation을 생성한다.
• 따라서 각 entity의 PK를 만들어진 relation의 FK, composite key로 사용한다.

결과

1. WORKS_FOR

• EMPLOYEE:DEPARTMENT = N:1
• EMPLOYEE를 자식으로하므로,
• DEPARTMENT의 PK를 EMPLOYEE의 FK로 넣어준다.

1. SUPERVISION

• SUPERVISEE:SUPERVISOR = N:1
• SUPERIVSEE를 자식으로
• SUPERVISOR의 PK를 SUPERVISEE의 FK로 넣어주지만, 같은 entity내에서 일어나게된다.

1. CONTROL

• PROJECT:DEPARTMENT = N:1
• PROJECT를 자식으로
• DEPARTMENT의 PK 부서번호를 PROJECT의 FK로 넣는다.

5. Mapping Binary M:N Relationship Types

• 전통적인 DB에서는 다중속성이 존재하지 않으므로, M:N을 표현하려면 ⭐️relationship relation(cross-reference)로만 표현해야한다.
• M:N relationship R이 있고, 이에 U, V가 참여할때,
  1. ⭐️새로운 relationship relation S를 정의한다.
  2. ⭐️Primary key는 반드시 U과 V의 주키를 모두 가지며
  3. ⭐️Foreign key또한 U와 V의 주키이다.
• 띠리사 relationship relation은 참조키를 최소 2개이상 가져야만한다.

WORKS_ON

• 위 그림에서 EMPLOYEE-PROJECT는 M:N의 관계이다.
  • 따라서 WORKS_ON을 표현하려면 별도의 relation을 정의해야하며
  • PRIMARY KEY는 EMPLOYEE의 SSN, PROJECT의 Pnumber이다.
  • FOREIGN KEY도 똑같이 정의된다.

6. Mapping Multi-valued Attributes

• multi-valued attribute를 표현하지 못하기때문에 이 또한 relationship relation(cross-reference)로만 표현해야한다.

• 단, M:N과 다른점은 M:N은 relationship이기때문에 연결되는 각 entity의 주키를 참조키이자 주키로 가질 수 있었지만, 다중속성은 entity의 속성으로 존재한다는 것이다.

• 따라서 M:N과는 다르게, 자신을 가지고 있는 entity의 primary key에 더해 자신의 속성값을 주키로 한다.

weak entity mapping과 유사성

• weak entity와 다중속성은 weak entity가 다른 entity와 관계가 없을때 상응하게되는데, 그래서 두 종류의 mapping은 위 조건 하에서 동일한 모양을 띄게된다.

DEPT_LOCATIONS

• 자신을 가지고있는 DEPARTMENT의 PK와 자신의 값으로 PRIMARY KEY를 형성, Dnumber만 DEPARTMENT의 FK이다.

7. Mapping N-ary Relationship Types

모두 M:N인 ternary

• ternary이상의 relationship도 ⭐️relationship relation을 만든다.
• 새로운 relation S를 만들고, 참여하는 ⭐️모든 entity의 주키를 FK로하여 자신의 PK화한다.

1개라도 1:N

• 하지만 만약 참여하는 ⭐️entity 중 1개라도 참여도가 1이라면, 해당 entity의 key를 제외하고 PK화한다.
  • 왜냐하면, T(a,b,c)에서 a가 1의 참여도를 가진다면, 해당 ternary relationship은 a에 상관없이 b와 c의 값으로만 relationship instance를 identify할 수 있기때문이다.

CAN SUPPLY

• 새로운 relation SUPPLY 생성
• 모든 entity의 주키를 FK로 하면서 주키로한다.

equijoin

• relationship type을 쪼개 relation으로 바꿨기 때문에, ⭐️equijoin을 사용해야 원래 relation을 가져올 수 있다.

• 가능한 이유는 equijoin의 기준값이 외래키 이기 때문에 equijoin의 조건으로 넣어주면 된다.

• 1:1, 1:N
  • entity간 ⭐️1번의 JOIN
• M:N, multi-valued attribute
  • 위 2가지 경우 모두 1개의 relation을 ⭐️추가적으로 생성하므로, ⭐️2번의 JOIN을 하게된다.
• n-ary
  • ⭐️n번 join
    • 새로운 relation을 하나 추가하기때문에, binary M:N -> 2번, ternary M:N -> 3번.

  

  R1 <- (SUPPLIER S equijoin(S.SName == SP.SName) SUPPLY SP)
  R2 <- (PROJECT PR equijoin(PR.ProjName == R1.ProjName) R1)
  RESULT <- (PART P equijoin(P.Part_no == R2.Part_no) R2)
  

  select * from 
  (
    select * from 
    (
      select * from (
        SUPPLIER S inner join on SUPPLY SP
        WHERE S.SName = SP.SName
      )
  ) R1 INNER JOIN ON PROJECT PR 
    WHERE PR.ProjName = R1.ProjName
  ) R2 INNER JOIN ON PART P
    WHERE P.PartNo = R2.PartNo
  

  총 3번의 JOIN으로 SUPPLY, SUPPLIER, PART, PROJECT를 relationship type으로 만들었다.

EER-to-Relational Mapping

generalization/specialization mapping

• specialization을 mapping하는 방법은
  1. 1개의 relation으로 표현하느냐,
  2. 여러개로 표현하느냐의 차이.
• 이를 결정하는 조건은 ⭐️constraint(Disjoint/Overlapping, Total/Partial)에 따라 나뉜다.

Options

1. ⭐️Multiple - Superclass & Subclass

• superclass의 PK를 subclass의 PK로 사용한다.
• ⭐️각 superclass, subclass의 attr은 따로 relation에 분리된다.
• ⭐️어느 constraint에서나 사용될 수 있다.

1. ⭐️Multiple - Sub relations

• ⭐️각 sub relation들이 superclass의 PK와 attr 모두를 가지고있다.
• ⭐️이에 더해 자신들의 specific(local) attr를 가진다.

• ⭐️주의
  • ⭐️disjoint-total에만 사용될 수 있다.
  • disjoint-partial : subclass에 속하지 않는 entity가 ⭐️유실된다.
  • overlapping : 각 subclass에 동시에 참여하는 entity는 ⭐️중복된다.
• superclass를 도출하기 위해선 모든 subclass를 equi-join on PK or outer-join해야한다.

1. ⭐️Single - one type attr

• ⭐️disjoint만 사용가능
• ⭐️1개 relation에 superclass와 subclass의 모든 정보 표현
• 해당 튜플이 어떤 type인지 표현하기위해 ⭐️typeinfo라는 attr을 1개추가한다.
• 이 typeinfo는 disjoint subclass의 집합이다.
• partial 참여인 경우, typeinfo를 NULL로 표현한다.
• attribute-defined subclass인 경우(1개 속성으로 모든 subclass를 구분할 수 있는 경우), ⭐️defining attr을 typeinfo로 사용한다.
• NULL값 개수가 많을 수 있다.

1. ⭐️Single - multipe type attr

• overlapping과 disjoint모두 사용가능
• 1개 relation에 superclass와 subclass의 모든 정보 표현
• typeinfo가 ⭐️boolean type이다.
• typeinfo는 overlapping subclass ⭐️개수만큼 존재한다.

category(union) mapping

• UNION은 2개이상의 superclass로 이뤄진 합집합의 subclass이다.
• 다양한 key를 가진 superclass로 정의된 category를 mapping하기위해선 ⭐️새로운 키가 필요하다.

• 이 key를 ⭐️surrogate key라고 부른다.

• -OWNER category relation을 보면, superclass들의 Owner_id를 새로 정의해 surrogate key로 한 것을 볼 수 있다.

• 
  • VEHICLE의 경우에는 ⭐️새로운 surrogate key 필요 없이 기존의 Vehicle_id를 사용하면 된다.