PowerPoint プレゼンテーション

関係代数
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.
ＤＮＯが無いのでreferential integrityに抵触
4. Insert ' Cecilia' , ' F' , ' Kolonsky' , '677678989' , '1960 - 04 - 05' ,
'6357 Windy Lane, Katy, TX' , F, 28000, null, 4  into EM PLOYEE.
ＯＫ
対処方法
• ＵＳＥＲに注意を喚起
• 例３番目の場合、新しい部門レコードを
入れればＯＫ．
• しかしその時にマネジャのＳＳＮが再度
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
– ２番目の例の場合work_onのタプルも自動的に削除
• 当該値を参照している値を他の値に変更する。
• ３番目の場合、workonとdependentタプルは削除、
employeeとdepartmentはＮＵＬＬとしておく。
• ＳＱＬではこのような対処を記述することが出来
る。
1. Update the SALARY of the EM PLOYEE tuple with SSN  '999887777 ' to 28000.
ＯＫ
2. Update the
DNO of the EM PLOYEE tuple with SSN  '999887777 ' to 1.
ＯＫ
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違反
対処
• 通常アトリビュートの変更ＯＫ
• プライマリキー値の変更は delete & insert
と同値
• Foreign keyの変更は referential integrity
をチェック
関係代数
• データベースの構造、制約と同時にデータ
モデルはデータの操作系が必要
• Relational Algebra
• Set operators
– UNION, INTERSECT, DIFFERENCE,
CARTESIANPRODUCT
• Relational operators
– SELECT, PROJECT, JOIN
SELECT(選択演算）
ＤＮＯが４の従業員
 DNO  4 (EM PLOYEE)
給料が３００万円以上
 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 ANDSALARY30000) (EM PLOYEE)
選択演算規則
 cond1 ( cond 2 ( R))   cond 2 ( cond1 ( R))
 cond1 ( cond 2 (...( condn ( R))...))
  cond1 ANDcond 2 AND... ANDcondn ( R)
ＰＲＯＪＥＣＴＩＯＮ（射影）
 LNAME , FNAME, SALARY ( EMPLOYEE )
  attributelist ( R)
 SEX , SALARY ( EMPLOYEE )
 list1 ( list2 ( R ))   list1 ( R)
ＳＥＬＥＣＴとＰＲＯＪＥＣＴの混合表現
 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)
Ｓ：リレーション名
Ｂ：アトリビュート名
ＵＮＩＯＮ
ＤＥＰＴ５で働く人とその人のマネジャー
DEP 5 _ EMPS   DNO  5 ( EMPLOYEE )
RESULT 1 
 SSN ( DEP5 _
RESULT 2( SSN )
RESULT

EMPS )
  SUPERSSN ( DEP 5 _
RESULT 1  RESULT 2
ＵｎｉｏｎＣｏｍｐａｔｉｂｉｌｉｔｙ
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
ＪＯＩＮなぜ？
女性社員とその扶養家族を求めたい。
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 )
直積とＳＥＬＥＣＴの連続操作は頻繁に生ずる
ＪＯＩＮ結合演算
部門とそのマネジャの名前
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 )
情報のリンクをたどる操作に相当
完備性
｛ＳＥＬＥＣＴ，ＰＲＯＪＥＣＴ，ＵＮＩＯＮ，ＤＥＦＦＥＲＥＮＣＥ，Ｘ｝
R  S  ( R  S )  (( R  S )  ( S  R))
R
R  condition S   condition ( R  S )
ＤＩＶＩＳＩＯＮ
ジョンスミスが参加するプロジェクト全てに参加している
従業員を調べる。
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
Ｒ（Ｚ）÷Ｓ（Ｘ）
Ｘ⊆Ｚ、Ｙ＝Ｚ－Ｘ
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

Download Report