The Relational Algebra and Relational calculus 20110309 matsuzaki

教育輪講第６回 The Relational Algebra and Relational calculus 2011/03/09 matsuzaki 1

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 2

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 4

選択(SELECT)演算 l σ(d_name=“toy” AND E_salary>25000) OR (d_name=“book” AND E_salary>30000) (EMPLOYEE) EMPLOYEE I D E_name 1 gasho toy 40000 F 2 obunai book 25000 M 3 kosuda d_name E_salar y smoke 30000 E_se x M 4 fujii book 35000 M 5 matsuzaki toy 10000 F I D E_name d_name E_salary E_Sex 1 gasho toy 40000 F 4 fujii book 35000 M 　　　　　　　　 7

選択(SELECT)演算 σ(d_name=“toy” AND E_salary>25000) (EMPLOYEE) SQL SELECT * FROM EMPLOYEE WHERE d_name=“toy” AND E_salary>25000; 9

射影(PROJECT)演算 πd_name, E_sex(EMPLOYEE) EMPLOYEE I D E_name d_name E_salary E_sex 1 gasho toy 40000 F 2 obunai book 25000 M d_name E_sex 3 kosuda smoke 30000 M toy F 4 fujii book 35000 M book M 5 matsuzaki toy 10000 F smoke M 　　　　 11

射影(PROJECT)演算 <attribute list>は交換できない 　　○ πd_name(πd_name, E_sex(EMPLOYEE)) 　　 × πd_name, E_sex(πd_name(EMPLOYEE)) πd_name, E_sex(EMPLOYEE) SQL SELECT DISTINCT d_name, E_sex FROM EMPLOYEE 12

シーケンス操作と属性名変更(RENAME) 　　　TOY_EMPS←σd_name=“toy” (EMPLOYEE) 　　　RESULT←πname, salary(TOY_EMPS) 名前の変更 　　　TOY_EMPS←σd_name=“toy” (EMPLOYEE) 　　　E(name, salary)←πE_name, E_salary(TOY_EMPS) rename 　　　ρE(name、salary)(TOY_EMPS) 15

シーケンス操作と属性名変更(RENAME) SQL文では　’AS’ を用いる SELECT E_name AS name, E_salary AS salary FROM EMPLOYEE AS E WHERE E. d_name=“toy”; 16

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 17

集合論に基づく関係演算子 和(UNION), 交わり(INTERSECTION), 差(MINUS) 直積(CARTESIAN PRODUCT) 18

STUDENT INSTRUCTOR L_name F_name gasho yumi obunai sensei toyama motomichi obunai sensei kosuda tatsuya fukuzawa yukichi kosuda tatsuya fujii yotaro matsuzaki tomoko toyama motomichi fukuzawa yukichi 　　 UNION MINUS(S−I) L_name F_name gasho yumi 　INTERSECTION L_name F_name kosuda tatsuya obunai sensei fujii yotaro L_name F_name toyama motomichi matsuzaki tomoko fukuzawa yukichi MINUS(I−S) 23

集合論に基づく関係演算子 和(UNION), 交わり(INTERSECTION), 差(MINUS) 直積(CARTESIAN PRODUCT) 25

DEPENDENT F_EMPS I D E_name Ssn 1 gasho 1010 5 matsuzaki 3333 Essn D_name Sex birth 1000 taro M 1999 1000 hanako F 2011 1010 jiro M 1943 2222 yoshiko F 1865 F_EMPS×DEPENDENT ID E_name Ssn Essn D_name Sex birth 1 gasho 1010 1000 taro M 1999 1 gasho 1010 1000 hanako F 2011 1 gasho 1010 jiro M 1943 1 gasho 1010 2222 yoshiko F 1865 5 matsuzaki 3333 1000 taro M 1999 5 matsuzaki 3333 1000 hanako F 2011 5 matsuzaki 3333 1010 jiro M 1943 5 matsuzaki 3333 2222 yoshiko F 1865 F_EMPS × DEPENDENT 属性の数 =３＋４ =７ タプルの数 =２×４ =８ 27

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 30

F_EMPS DEPENDENT I D E_name Ssn 1 gasho 1010 5 matsuzaki 3333 Essn D_name Sex birth 1000 taro M 1999 1000 hanako F 2011 1010 jiro M 1943 2222 yoshiko F 1865 ACTUAL_DEPENDENTS ID E_name Ssn Essn D_name Sex birth 1 gasho 1010 jiro M 1943 ACTUAL_DEPENDENTS ←　F_EMPS▷◁Ssn=Essn. DEPENDENT 32

等結合(EQUIJOIN)演算 θ結合において比較演算子が「＝」である結合演算 各タプルに、同一の値をもつ属性が存在する。 ACTUAL_DEPENDENTS ←　F_EMPS▷◁Ssn=Essn. DEPENDENT ACTUAL_DEPENDENTS ID E_name Ssn Essn D_name Sex birth 1 gasho 1010 jiro M 1943 34

F_EMPS DEPENDENT I D E_name Ssn 1 gasho 1010 5 matsuzaki 3333 Essn D_name Sex birth 1000 taro M 1999 1000 hanako F 2011 1010 jiro M 1943 2222 yoshiko F 1865 ACTUAL_DEPENDENTS ID E_name Ssn D_name Sex birth 1 gasho 1010 jiro M 1943 ACTUAL_DEPENDENTS ←　F_EMPS*ρ(Ssn, D_name, Sex, birth)DEPENDENT 36

FAMILY father mother child age taro hanako hiroshi 5 taro hanako yuko 4 taro atsuko toru 2 tatsuya minami goro 7 CHILD tatsuya minami yuko 4 child age tatsuya minami toru 2 yuko 4 saburo mariko aiko 9 toru 2 PARENTS father mother tatsuya minami PARENTS←FAMILY÷CHILD 38

Query Tree 関係代数演算を木構造で表現できる 例 　　　　　　　 πP. Pnumber, P. Dnum, E. Lname, E. Address, E. Bdate ▷◁D. Mgr_ssn=E. Ssn ▷◁P. Dnum=D. Dnumber σP. PLOCATION=“Hiyoshi” P PROJECT E D PROJECTの場所がStaffordである 事業部長の、プロジェクト番号・事業 部番号・名前・住所・誕生日 EMPLOYEE DEPARTMENT 40

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 41

一般射影(Generalized projection)演算 射影演算の拡張で、射影リストに算術関数を用いることができる。 πF 1, F 2, …, Fn(R) 例：EMPLOYEE(Ssn, Salary, Deduction, Years_service) Net Salary = Salary − Deduction Bonus = 2000 * Years_service Tax = 0. 25 * Salary REPORT←ρ(Ssn, Net_Salary, Bonus, Tax)(πSsn, Salary-Deduction, 2000*Years_service, 0. 25*Salary(EMPLOYEE)) 42

(a) ρR(Dno, No_of_employees, Aerage_sal)(Dno. FCOUNT Ssn, AVERAGE Salary(EMPLOYEE)) (b) Dno. FCOUNT Ssn, AVERAGE Salary(EMPLOYEE) (c) FCOUNT Ssn, AVERAGE Salary(EMPLOYEE) (a)R (b) Dno No_of_employees Average_sal Dno Count_ssn Average_salary 5 4 10000 4 3 20000 1 1 60000 (c) Count_ssn Average_salary 8 20000 例：デパート番号、そのデパートの従業員の人数、　　 　　 従業員の平均給料を検索する 44

BORG_SSN←πSsn(σFname=‘James’AND Lname=‘Borg’(EMPLOYEE)) SUPERVISION(Ssn 1, Ssn 2)←πSsn, Super_ssn(EMPLOYEE) RESULT 1(Ssn)←πSsn 1(SUPERVISION▷◁Ssn 2=Ssn. BORG_SSN) RESULT 2(Ssn)←πSsn 1(SUPERVISION▷◁Ssn 2=Ssn. RESULT 1) RESULT←RESULT 2 ∪ RESULT 1 SUPERVISION (Borg’s Ssn is 00000) (Ssn) (Super_ssn) Ssn 1 Ssn 2 12345 00000 RESULT 1 22222 33333 00000 44444 12345 55555 33333 66666 null RESULT 2 RESULT Ssn Ssn 12345 22222 12345 33333 44444 33333 (Borgに 監督されている人) 55555 22222 (Borgの部下に 監督されている人) 44444 55555 （RESULT 1∪RESULT 2 46 ）

SUTUDENT INSTRUCTOR Names Ssn Depart ment Advisor Names Ssn Departme nt Rank turuno 0000 chemist ry 0101 turuno 0000 chemistry A_Profess or kamiji 1111 comput er 0000 kamiji 1111 math Professor nokubo 2222 english 3333 STUDENT_OR_INSTRUCTOR Names Ssn Depart ment Advisor Rank turuno 0000 chemistr y 0101 A_Profes sor kamiji 1111 compute r 0000 (null) nokubo 2222 english 3333 (null) kamiji 1111 math (null) Professor 52

SUTUDENT Names turuno Ssn 0000 INSTRUCTOR Major. D ept Advisor chemist ry 0101 kamiji 1111 comput er 3333 nokubo 2222 math 1111 Names Ssn Work. Dept Rank turuno 0000 chemistry A_Profess or kamiji 1111 math Professor 外結合を適用するなら rename STUDENT▷◁Names=Names, Ssn=Ssn. INSTRUCTOR Names Ssn Major Dept Advisor Work. D ept Rank turuno 0000 chemi stry 0101 chemist ry A_Profe ssor kamiji 1111 comp uter 3333 math (null) nokubo 2222 math 1111 (null) 53

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 54

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 56

組関係論理(The Tuple Relational Calculus) タプル変数(Tuple variables) tがタプル変数で、COND(t)がtを含む条件式(Boolean)のとき {t| COND(t)} 57

組関係論理(The Tuple Relational Calculus) 例 　　　{t| EMPLOYEE(t) AND t. Salary>50000} t. Fname, t. Lname のように記述することもできる 58

組関係論理(The Tuple Relational Calculus) formula: predicate calculus atomsで構成されている atomsは次のうちの形式になりうる ① R(ti) ② ti. A op tj. B opは比較演算子 {=, <, ≦, >, ≧, ≠} ③ ti. A op c　または　c op tj. B 61

組関係論理(The Tuple Relational Calculus) atomの真理値 特定のcombination of tuplesについて、TRUEかFALSEか 判断 62

組関係論理(The Tuple Relational Calculus) formula(boolean condition) • 論理演算AND, OR, NOTでつながれた、１つあるいはそれ以上の atoms Rule 1, Rule 2によって再帰的に定義される • Rule 1: 全てのatomがformula • Rule 2: もしF 1とF 2がformulasであるなら (F 1 AND F 2), (F 1 OR F 2), NOT(F 1), NOT(F 2) a. (F 1 AND F 2)is TRUE if both F 1 and F 2 are TRUE; otherwise FALSE b. (F 1 OR F 2)is FALSE if both F 1 and F 2 are FALSE; otherwise TRUE c. NOT(F 1) is TRUE if F 1 is FALSE; it is FALSE if F 1 is TRUE d. NOT(F 2) is TRUE if F 2 is FALSE; it is FALSE if F 2 is TRUE 64

組関係論理(The Tuple Relational Calculus) 例 71

組関係論理(The Tuple Relational Calculus) l Query Graphs 72

組関係論理(The Tuple Relational Calculus) 例 (∀x)(P(x))≡NOT(∃x)(NOT(P(x))) (∃x)(P(x))≡NOT(∀x)(NOT(P(x))) (∀x)(P(x))⇒(∃x)(P(x)) 75

組関係論理(The Tuple Relational Calculus) クエリーで全称記号を使う 76

組関係論理(The Tuple Relational Calculus) 基本的要素に分解 Q 3: {e. Lname, e. Fname|EMPLOYEE(e) AND F’} F’=((∀x)(NOT(PROJECT(x)) OR F 1)) F 1 =NOT(x. Dnum=5) OR F 2 =((∃w)(WORKS_ON(w) AND w. Essn=e. Ssn AND x. Pnumber=w. Pno)) 77

OUT LINE 関係代数 単項関係演算(SELECT, PROJECT) 集合論に基づく関係演算子(UNION, INTERECTION, MINUS, CARTESIAN PRODUCT) 二項関係演算(JOIN, DIVISION) 追加的な関係演算 関係論理 組関係論理(The Tuple Relational Calculus) 定義域関係論理(The Domain Relational Calculus) 80

定義域関係論理( The Domain Relational Calculus ) 例 名前が’John B. Smith’である従業員の誕生日と住所をリス トする {u, v|(∃q)(∃r)(∃s)(∃t)(∃w)(∃x)(∃y)(∃z)(EMPLOYEE(qrstuvwxyz)AN D q=‘John’ AND r=‘B’ AND s=‘Smith’)} QBE {u, v| EMPLOYEE(‘John’, ‘B’, ‘Smith’, t, u, v, w, x, y, z)} 84