1 CHAPTER 9 RELATIONAL DATABASE DESIGN BY ER

1 CHAPTER 9 RELATIONAL DATABASE DESIGN BY ER- AND EER-TO-RELATIONAL MAPPING 2022/2/15 2016/04/15 tabata

Chapter 9の目次 2 9. 1 Relational Database Design Using ER-to. Relational Mapping � 9. 1. 1 ER-to-Relational Mapping Algorithm � 9. 1. 2 Discussion and Summary of Mapping for ER Model Constructs 9. 2 Mapping EER Model Constructs to Relations � 9. 2. 1 Mapping of Specialization or Generalization � 9. 2. 2 Mapping of Shared Subclasses (Multiple Inheritance) � 9. 2. 3 Mapping of Categories (Union Types) 9. 3 Summary 2022/2/15

Chapter 9の目次 4 9. 1 Relational Database Design Using ER-to. Relational Mapping � 9. 1. 1 ER-to-Relational Mapping Algorithm � 9. 1. 2 Discussion and Summary of Mapping for ER Model Constructs 9. 2 Mapping EER Model Constructs to Relations � 9. 2. 1 Mapping of Specialization or Generalization � 9. 2. 2 Mapping of Shared Subclasses (Multiple Inheritance) � 9. 2. 3 Mapping of Categories (Union Types) 9. 3 Summary 2022/2/15

COMPANYデータベースのER図 6 2022/2/15

実際の例 16 ・DEPARTMENTとEMPLOYEE あるMANAGESについて行う。・ER図を見るとDEPTが完全参加 →全てのDEPTにmanagerがいる 2022/2/15

Step 4: Mapping of Binary 1: N Relationship Types. 21 この場合には key approach） � 相互参照または関連関係によるアプローチ（Crossreference or relationship relation approach） � 外部キーを用いたアプローチ（Foreign の二つがある 2022/2/15

結果（EMPとDEPT） 24 ・EMPのDnoがDnumberを参照している 2022/2/15

結果（EMP自身） 25 ・SUPERVISEEとしてのEMPがN側なのでSUPERVISORのSsnを外部キーとして持 2022/2/15

Step 5: Mapping of Binary M: N Relationship Types. 28 外部キーを用いたアプローチ（Foreign key approach）しかできない。 2022/2/15

COMPANYデータベースのER図 40 2022/2/15

Chapter 9の目次 42 9. 1 Relational Database Design Using ER-to. Relational Mapping � 9. 1. 1 ER-to-Relational Mapping Algorithm � 9. 1. 2 Discussion and Summary of Mapping for ER Model Constructs 9. 2 Mapping EER Model Constructs to Relations � 9. 2. 1 Mapping of Specialization or Generalization � 9. 2. 2 Mapping of Shared Subclasses (Multiple Inheritance) � 9. 2. 3 Mapping of Categories (Union Types) 9. 3 Summary 2022/2/15

9. 2 Mapping EER Model Constructs to Relations 43 EER図にある専化や汎化を関係モデルで表す手法を考える。 2022/2/15

Step 8: Options for Mapping Specialization or Generalization. 45 以下の四つの手法がある。 � Option 8 A: Multiple relations—superclass and subclasses. � Option 8 B: Multiple relations—subclass relations only. � Option 8 C: Single relation with one type attribute. � Option 8 D: Single relation with multiple type attributes. 2022/2/15

Option 8 Aの例 47 2022/2/15

結果 48 SECRETARYとTECHNICIANとENGINEERがEMPのSsnを受け取っている。 2022/2/15

Option 8 Bの例 50 2022/2/15

Option 8 Cの例 53 2022/2/15

Option 8 Dの例 56 2022/2/15

例 59 2022/2/15

結果 60 2022/2/15

例 64 2022/2/15

結果 65 ・sはOwner_idとVehicle_id ・RはOWNER関係とREGISTERED_VEHICLE 2022/2/15

Chapter 9の目次 66 9. 1 Relational Database Design Using ER-to. Relational Mapping � 9. 1. 1 ER-to-Relational Mapping Algorithm � 9. 1. 2 Discussion and Summary of Mapping for ER Model Constructs 9. 2 Mapping EER Model Constructs to Relations � 9. 2. 1 Mapping of Specialization or Generalization � 9. 2. 2 Mapping of Shared Subclasses (Multiple Inheritance) � 9. 2. 3 Mapping of Categories (Union Types) 9. 3 Summary 2022/2/15