Chapter 9 Relational Database Design by ER and

Chapter 9 Relational Database Design by ER- and EER-to-Relational Mapping 2014/04/16 B 4 halken 1

Outline • Relational Database Design Using ER-to-Relational Mapping - ERモデルを用いた関係データベース設計 • Mapping EER Model Constructs to Relations - EERモデルから関係データベースへのマッピング 2

9. 1 Relational Database Design Using ER-to-Relational Mapping ERモデルを用いた関係データベース設計 4

ER-to-Relational Mapping Algorithm • 例として COMPANY database を使用 - マッピングでは、single-valued属性を持つテーブルが 作られると仮定する 5

6

7

ER-to-Relational Mapping Algorithm • 7つのステップ Ø Step 1: Mapping of Regular Entity Types. Ø Step 2: Mapping of Weak Entity Types. Ø Step 3: Mapping of Binary 1: 1 Relationship Types. Ø Step 4: Mapping of Binary 1: N Relationship Types. Ø Step 5: Mapping of Binary M: N Relationship Types. Ø Step 6: Mapping of Multivalued Attributes. Ø Step 7: Mapping of N-ary Relationship Types. 8

10

Example Step 1 ER schema EMPLOYEE DEPARTMENT PROJECT Relational database schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Name DEPARTMENT PROJECT Dname Pname Dnumber Plocation 11

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Depandent_name Sex Bdate Relationship 12 Step 1

14

Example Step 2 EMPLOYEE ER schema DEPENDENTS_OF Relational database schema DEPENDENT Essn Dependent_name Sex Bdate Relationship EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary 15

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Depandent_name Sex Bdate Relationship 16 Step 2 Step 1

ER-to-Relational Mapping Algorithm Step 3: Mapping of Binary 1: 1 Relationship Types. • ERスキーマにある1: 1関連(relationship)Rに対して、Rに関 与している実体と対応している関係SとTに着目 • 3つのアプローチ方法が存在 1. 外部キーのアプローチ(Foreign key approach) 2. 関係を併合するアプローチ(Merged relation approach) 3. 相互参照または関連関係からのアプローチ (Cross-reference or relationship relation approach) 17

19

Example Step 3 ER schema 1 EMPLOYEE 1 MANAGE DEPARTMENT Relational database schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date 20

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Depandent_name Sex Bdate Relationship 21 Step 3 Step 2

ER-to-Relational Mapping Algorithm Step 3: Mapping of Binary 1: 1 Relationship Types. 3. 相互参照または関連関係からのアプローチ (Cross-reference or relationship relation approach) • 実体からなる2つの関係SとTのそれぞれの主キーを相互参照する ために 3番目の関係Rを作る • M: N関連のときに必要とされる • 関連関係(relationship relation)と呼ばれる 24

26

Example Step 4 ER schema N EMPLOYEE 1 WORKS_FOR DEPARTMENT Relational database schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date 27

Example Step 4 ER schema 1 DEPARTMENT N CONTROLS PROJECT Relational database schema DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date PROJECT Pname Pnumber Plocation Dnum 28

Example Step 4 ER schema EMPLOYEE 1 Supervisor SUPERVISION N Supervisee Relational database schema EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno 29

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Depandent_name Sex Bdate Relationship Step 4 Step 3 30

32

Example Step 5 ER schema M EMPLOYEE PROJECT Relational database schema EMPLOYEE Fname WORKS_ON N Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours 33

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Depandent_name Sex Bdate Relationship 34 Step 5

36

Example Step 6 ER schema DEPARTMENT Locations Relational database schema DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation 37

EMPLOYEE Fname Minit Lname Ssn Bdate Address Sex Salary Super_ssn Dno DEPARTMENT Dname Dnumber Mgr_ssn Mgr_start_date DEPT_LOCATIONS Dnumber Dlocation PROJECT Pname Pnumber Plocation Dnum WORKS_ON Essn Pno Hours DEPENDENT Essn Depandent_name Sex Bdate Relationship 38 Step 6 Step 5

Example Step 7 ER schema Relational database schema 40

Discussion and Summary of Mapping for ER Model Constructs 41

9. 2 Mapping EER Model Constructs to Relations EERモデルから関係データベースへのマッピング 42

Mapping of Specialization or Generalization • ERモデルの拡張であるEERモデルから関係モデルにする • 追加ステップ Ø Step 8: Options for Mapping Specialization or Generalization. Ø Step 9: Mapping of Union Types (Categories) 43

Mapping of Specialization or Generalization Step 8: Options for Mapping Specialization or Generalization • m個のサブクラス{S 1, S 2, …, Sm}と、そのスーパークラスC で構成されている専化(specialization)を関係スキーマに変 える • 以下のオプションの 1つを使用 Ø Option 8 A: Multiple relations—superclass and subclasses. Ø Option 8 B: Multiple relations—subclass relation only. Ø Option 8 C: Single relation with one type attribute. Ø Option 8 D: Single relation with multiple type attributes. 44

Mapping of Specialization or Generalization Step 8: Options for Mapping Specialization or Generalization • Option 8 A: Multiple relations—superclass and subclasses. • スーパークラスCに対応する関係Lを作る Ø 属性はCの属性 Ø 主キーはCの主キー属性 • それぞれのサブクラスSiに対応する関係Liを作る Ø 属性はCの主キー属性kとSiの属性 Ø 主キーはk Ø Cの主キー属性を外部キーとして含む • どの専化にも使える Ø 全体専化(total) or 部分専化(partial) Ø 素集合(disjoint) or 複合(overlapping) 45

Example Step 8 A EER schema Relational database schema 46

Mapping of Specialization or Generalization Step 8: Options for Mapping Specialization or Generalization • Option 8 B: Multiple relations—subclass relations only. • それぞれのサブクラスSiに対応する関係Liを作る Ø 属性はSiの属性とスーパークラスCの属性 Ø 主キーはCにある主キー属性k • サブクラスが全体専化(total specialization)のときのみ使える • 素集合制約(disjointedness constraint)のときのみ、このオプシ ョンは推奨される 47

Example Step 8 B EER schema Relational database schema 48

Example Step 8 C EER schema Relational database schema 50

Mapping of Specialization or Generalization Step 8: Options for Mapping Specialization or Generalization • Option 8 D: Single relation with multiple type attributes. • 関係Lを作る Ø 属性はスーパークラスCの属性と、全てのサブクラスSの属性と、 各Siに対応する2値タイプ属性(Boolean type attribute)ti Ø tiはタプルがSiに属するかどうかを示す • サブクラスが複合(overlapping)専化のとき使われる Ø 素集合専化のときでも使える 51

Example Step 8 D EER schema Relational database schema 52

Example Step 9 EER schema Relational database schema 56