関係代数 Semantic integrity constraints • 分類 – State constraints – Transition constraints • State constraintsの例 – 従業員の給料はその上司の給料を超えることは無い。 – プロジェクトでの労働時間は最大X時間 • SQL – Trigger, alerter – CREATE ASSERTION文 1. Insert ' Cecilia' , ' F' , ' Kolonsky' , null, '1960 - 04 - 05' , '6357 Windy Lane, Katy, TX' , F, 28000, null, 4 into EM PLOYEE. キーがないので駄目 2. Insert ' Alicia' , ' J' , ' Zelaya' , '999887777' , '1960 - 04 - 05' , '6357 Windy Lane, Katy, TX' , F, 28000, '987654321' , 4 into EM PLOYEE. キーが同じものが既にあるので駄目 3. Insert ' Cecilia' , ' F' , ' Kolonsky' , '677678989' , '1960 - 04 - 05' , '6357 Windswept , Katy, TX' , F, 28000, '987654321' , 7 into EM PLOYEE. DNOが無いのでreferential integrityに抵触 4. Insert ' Cecilia' , ' F' , ' Kolonsky' , '677678989' , '1960 - 04 - 05' , '6357 Windy Lane, Katy, TX' , F, 28000, null, 4 into EM PLOYEE. OK 対処方法 • USERに注意を喚起 • 例 3番目の場合、新しい部門レコードを 入れればOK. • しかしその時にマネジャのSSNが再度 referential integrityに抵触する可能性があ る。Cascade operation. 1. Delete the WORKS_ON tuple with ESSN '999887777 ' and PNO 10. OK 2. Delete the EMPLOYEE tuple with SSN '999887777 ' これを消すとwork_on tupleがreferential integrityに抵触 3. Delete the EM PLOYEE tuple with SSN '333445555 '. これを消すのは更に問題が多い。Employee, Department, Work_on, Dependent全てのリレーションから参照されている。 対処 • Deleteを拒否し、アラームを出す。 • Cascade deletion – 2番目の例の場合work_onのタプルも自動的に削除 • 当該値を参照している値を他の値に変更する。 • 3番目の場合、workonとdependentタプルは削除、 employeeとdepartmentはNULLとしておく。 • SQLではこのような対処を記述することが出来 る。 1. Update the SALARY of the EM PLOYEE tuple with SSN '999887777 ' to 28000. OK 2. Update the DNO of the EM PLOYEE tuple with SSN '999887777 ' to 1. OK 3. Update the DNO of the EM PLOYEE tuple with SSN '999887777 ' to 7. Referential integrity違反 4. Update the SSN of the EM PLOYEE tuple with SSN '999887777 ' to '987654321 '. Primary key 違反、referential integrity違反 対処 • 通常アトリビュートの変更OK • プライマリキー値の変更は delete & insert と同値 • Foreign keyの変更は referential integrity をチェック 関係代数 • データベースの構造、制約と同時にデータ モデルはデータの操作系が必要 • Relational Algebra • Set operators – UNION, INTERSECT, DIFFERENCE, CARTESIANPRODUCT • Relational operators – SELECT, PROJECT, JOIN SELECT(選択演算) DNOが4の従業員 DNO 4 (EM PLOYEE) 給料が300万円以上 SALARY 30000(EM PLOYEE) 一般形 selection condition ( R) <attribute name><comparison op><constant value>, or <attribute name><comparison op><attribute name> ( DNO 4 ANDSALARY 25000)OR( DNO 5 ANDSALARY30000) (EM PLOYEE) 選択演算規則 cond1 ( cond 2 ( R)) cond 2 ( cond1 ( R)) cond1 ( cond 2 (...( condn ( R))...)) cond1 ANDcond 2 AND... ANDcondn ( R) PROJECTION(射影) LNAME , FNAME, SALARY ( EMPLOYEE ) attributelist ( R) SEX , SALARY ( EMPLOYEE ) list1 ( list2 ( R )) list1 ( R) SELECTとPROJECTの混合表現 FNAME, LNAME , SALARY ( DNO 5 ( EMPLOYEE )) 分解 DEP5 _ EMPS DNO 5 ( EMPLOYEE )) REASULT FNAME, LNAME , SALARY ( DEP5 _ EMPS ) アトリビュートのリネームが可能 TEMP DNO 5 ( EMPLOYEE ) R ( FIRSTNAME , LASTNAME , SALARY ) FNAME, LNAME , SALARY (TEMP ) リネームオペレータ S ( B1, B 2,..., Bn) ( R)or S ( R)or ( B1, B 2,... Bn) ( R) S:リレーション名 B:アトリビュート名 UNION DEPT5で働く人とその人のマネジャー DEP 5 _ EMPS DNO 5 ( EMPLOYEE ) RESULT 1 SSN ( DEP5 _ RESULT 2( SSN ) RESULT EMPS ) SUPERSSN ( DEP 5 _ RESULT 1 RESULT 2 Union Compatibility EMPS ) ・ UNION : The result of this operation, denoted by R S, is a relation t hat includes all tuples that are either in R or in S or in both R and S. Duplicate tuples are eliminated . ・ INTERSECTI ON : The result of this operation, denoted by R S, is a relation t hat includes all tuples that are in both R and S. ・ SET DIFFERENCE : The result of this operation, denoted by R - S, is a relation t hat includes all tuples that are in R but not in S. R S S R, and R S S R R (S T) (R S) T, and (R S) T R (S T) R -S S- R JOIN なぜ? 女性社員とその扶養家族を求めたい。 FEMALE_EMP S EMPNAMES σSEX 'F'(EMPLOYEE ) FNAME, LNAME , SSN ( FEMALE _ EMPS ) EMP _ DEPENDENTS EMPNAMEA DEPENDENT ACTUAL _ DEPENDENTS RESULT SSN ESSN ( EMP _ DEPENDENTS ) FNAME, LNAME , DEPENDENT _ NAME ( ACTUAL _ DEPENDENTS ) 直積とSELECTの連続操作は頻繁に生ずる JOIN 結合演算 部門とそのマネジャの名前 DEPT _ MGR RESULT DEPARTMENT MGRSSN SSN DNAME, LNAME , FNAME ( DEPT EMPLOYEE _ MGR) 女性社員とその扶養家族 EMP _ DEPENDENTS EMPNAMEAS DEPENDENT ACTUAL _ DEPENDENTS SSN ESSN ( EMP _ DEPENDETS ) ACTUAL _ DEPENDENTS EMPNAMES SSN ESSN DEPENDENT 一般形 R join condition S condition AND condition AND... AND condition 多重ジョイン (( PROJECT DNUM DNUMBER DEPARTMENT ) MGRSSN SSN EMPLOYEE ) 情報のリンクをたどる操作に相当 完備性 {SELECT,PROJECT,UNION,DEFFERENCE,X} R S ( R S ) (( R S ) ( S R)) R R condition S condition ( R S ) DIVISION ジョンスミスが参加するプロジェクト全てに参加している 従業員を調べる。 SMITH FNAME ' John' AND LNAME 'Smith' ( EMPLOYEE ) SMITH _ PNOS SSN _ PNOS ESSN , PNO (WORKS _ ON ) SSN SMITH _ PNOS FNAME, LNAME ( SSNS * EMPLOYEE ) SSNS ( SSN ) RESULT PNO (WORKS _ ON ESSN SSN _ PNOS T1 Y ( R ) T2 Y (( S T1 ) R ) T T1 T2 R(Z)÷S(X) X⊆Z、Y=Z-X SMITH ) 集計演算(Aggregate Function) 一般形 groupingattributes functionlist ( R ) R ( DNO, NO _ OF _ EMPLOYEES, AVERAGE_ SAL) ( DNO COUNTSSN, AVERAGE SALARY ( EMPLOYEE )) DNO COUNTSSN , AVERAGE SALARY ( EMPLOYEE ) COUNTSSN , AVERAGE SALARY ( EMPLOYEE ) 左に何もつけないとscalar aggregate OUTER JOIN(left outer join) TEMP ( EMPLOYEE SSN MGRSSN DEPARTMENT ) RESULT FNAME, MINIT , LNAME , DNAME (TEMP) QUERY1 Retrieve the name and address of all employees who work for the ' Research' department . RESEARCH_DEPT DNAME' Re serch' ( DEPARTMENT ) RESEARCH _ EMPS RESULT ( RESEARCH _ DEPT FNAME, LNAME , ADDRESS ( RESEARCH DNUMBER DNO EMPLOYEE _ EMPS ) ) QUERY2 For eery project located in ' Stafford' , list the project number, the controllin g department number, and the department manager' s last name, address, and birthdate. STAFFORD_PROJS PLOCATION ''Stafford' (PROJECT ) CONTR _ DEPT ( STAFFORD _ PROJS PROJ _ DEPT _ MGR (CONTR _ DEPT RESULT DNUM DNUMBER DEPARTMENT MGRSSN SSN EMPLOYEE ) PNUMBER, DNUM , LNAME , ADDRESS, BDATE ( PROJ _ DEPT _ MGR) ) QUERY3 Find the names of employees who work on all the projects controlled by department number. DEPT5_PROJS(PNO) PNUMBER ( DNUM 5 ( PROJECT )) EMP _ PRJO ( SSN , PNO ) ESSN , PNO (WORKS _ ON ) EMP _ PRJO DEPT 5 _ PROJS LNAME , FNAME ( RESULT _ EMP _ SSNS * EMPLOYEE ) RESULT _ EMP _ SSNS RESULT QUERY4 Make a list of project numbers for projects that involve an employee whose last name is ' Smith' , either as a worker or a manager of the department that controls the project. SMITHS(ESSN) SSN ( LNAME 'Smith' ( EMPLOYEE )) SMITH _ WORKER _ PROJ MGRS PNO (WORKS _ ON * SMITHS ) LNAME , DNUMBER ( EMPLOYEE SSN MGRSSN DEPARTMENT ) SMITH _ MANAGED _ DEPTS ( DNUM ) DNUMBER ( LNAME 'Smith' ( MGRS )) SMITH _ MGR _ PROJS ( PNO ) PNUMBER ( SMITH _ MANAGED _ DEPTS * PROJECT ) RESULT ( SMITH _ WORKER _ PROJS SMITH _ MGR _ PROJS ) QUERY5 List the names of all employees with two or more dependents . Strictly speaking, this query cannot be done in the basic rela tional a lg ebra. We have to use the AGGREGATE FUNCTION operation with the COUNT aggregate function. We assume that dependents of the same employee have distinct DEPENDENT_NAM E values. T1 ( SSN , NO _ OF _ DEPTS ) ESSN COUNT DEPENDENT _ NAME( DEPENDENT ) T2 NO _ OF _ DEPS 2 (T1 ) RESULT LNAME , FNAME (T2 * EMPLOYEE ) QUERY6 Retrieve the names of employees who have no dependents . ALL _ EMPS SSN ( EMPLOYEE ) EMP _ WITH _ DEPS ( SSN ) ESSN ( DEPENDENT ) ( ALL _ EMPS EMPS _ WITH _ DEPS ) RESULT LNAME , FNAME ( EMPS _ WITHOUT _ DEPS * EMPLOYEE ) EMPS _ WITHOUT _ DEPS QUERY7 List the names of managers who have at last one dependent. MGRS(SSN) πMGRSSN(DEPARTMENT ) EMPS _ WITH _ DEPS ( SSN ) ESSN ( DEPENDENT ) ( MGRS EMPS _ WITH _ DEPS ) LNAME , FNAME ( MGRS _ WITH _ DEPS * EMPLOYEE ) MGRS _ WITH _ DEPS RESULT
© Copyright 2024 ExpyDoc