1 / 27

Installment 7

Presented by rexmen 2001. Installment 7. Tables With No Column. 資管所．林彥廷． 690530031. Installment 7 流程大綱. Relational Algebra Operator Empty set in RDB Tables and Predicate Tables with No column TABLE_DUM and TABLE_DEE Effect on Relational Algebra. Relational Algebra Operator.

joseph-rosario
Download Presentation

Installment 7

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Presented by rexmen 2001 Installment 7 Tables With No Column 資管所．林彥廷．690530031

  2. Installment 7 流程大綱 • Relational Algebra Operator • Empty set in RDB • Tables and Predicate • Tables with No column • TABLE_DUM and TABLE_DEE • Effect on Relational Algebra

  3. Relational Algebra Operator • Select (Restrict) • Project • Cartesian Product • Union • Difference • Join • Intersection • Divide 原始運算子 非原始運算子

  4. Select (Restrict) • 從一個 Table 中取出符合條件的資料，也就是取得 Row 的資料，請注意這裡的 Select 是運算子並不是指 SQL 語法中的 Select。 Select * From T1 Where <expression>

  5. Project • 從一個 Table 中取出所需要的欄位，也就是取得 Column 的資料。 Select C1, C2 From T1

  6. Cartesian Product(卡氏積) • 將兩個 Table 以普通算術中的乘法原理相乘後組合成一個 Table 。 ╳ ＝＞ Select * From T1, T2

  7. Union • 從兩個 Table 中取出聯集的資料，重複的資料只取出一筆。 ＝＞ Select * From T1UnionSelect * From T2

  8. Difference • 從一個 Table 中刪除另一個 Table 中有的資料 。 ＝＞ Select * From T1 Where not exists ( Select * From T2 Where T1.C1 = T2.C1 and T1.C2 = T2.C2 )

  9. Set of Relational DB Column / Attribute TABLE / RELATION Row / Tuple

  10. Empty Set • 空集合(empty set)仍然是一個集合，所以RDB集合定理所構成，自然也要能符合含有空集合的性質。 • Empty set of rows • Empty set of column ？

  11. Tables and Predicate • Predicate： • a Truth-valued Function(真值函數) • Return True or False • Ex. • F( e, set ) • F( 3, {1,3,5,6} ) Ans： True True, if e contained by set False, otherwise Proposition(命題) / Instantiation(實例)

  12. Predicate of Tables • 可以說predicate是table的所表示的意義 • 每一個table有一個確切的predicate • Ex. Table EMP with heading {E#, SAL, D#} • “Employee E# earns salary SAL and works in department D#. ” • E.g. EMP(E1, 50K, D1) • 如果存在此列(E1, 50K, D1)，則此proposition的結果為True，反之為False。

  13. TABLE EMP(E#, SAL, D#) • “Employee E# earns salary SAL and works in department D#. ” EMP

  14. Project Away D# From EMP • Table ES is derived from table EMP(E#, SAL, D#) by “Projecting away” column D# • Get table ES with heading {E#, SAL} • Predicate： • “THERE EXISTS a department D# SUCH THAT employee E# earns salary SAL and works in department D# .”

  15. TABLE ES(E#, SAL) • “THERE EXISTS a department D# SUCH THAT employee E# earns salary SAL and works in department D# .” Project away D2 From EMP ES EMP

  16. Project Away E# From ES • Table S is derived from table ES(E#, SAL) by “Projecting away” column E# • Get table S with heading {SAL} • Predicate： • “THERE EXISTS a department D# SUCH THAT THERE EXISTS an employee E# SUCH THAT employee E# earns salary SAL and works in department D#”

  17. TABLE S(SAL) • “THERE EXISTS a department D# SUCH THAT THERE EXISTS an employee E# SUCH THAT employee E# earns salary SAL and works in department D#” Project away D2 From EMP Project away E4From ES S ES EMP

  18. Project Away SAL From S • Table Z is derived from table S(SAL) by “Projecting away” column SAL • Get table Z with heading{ } • Predicate： • “THERE EXISTS a department D# SUCH THAT THERE EXISTS an employee E# SUCH THAT THERE EXISTS a salary SAL SUCH THAT employee E# earns salary SAL and works in department D#.”

  19. Table Z • “THERE EXISTS a department D# SUCH THAT THERE EXISTS an employee E# SUCH THAT THERE EXISTS a salary SAL SUCH THAT employee E# earns salary SAL and works in department D#.” Project away D2 From EMP Project away E4From ES Project away 40kFrom S ？ S ES EMP

  20. Table with No Column！ • 此種Table是否能存在任何的列？ • 在此種Table中存在著一種為0-tuple的列 • 因為一個列也是一種欄位的集合 • 0-tuple是一種欄位為空集合的一個欄位集合 • 此種Table只有一種列，即為0-tuple(一般正規的關聯式資料庫中的列是要消除重複性的，因為0-tuple與其他0-tuple表示相同的列，故此種table只能至多有一個列。) • Table With No Column is useful？ Of Course, YES！

  21. TABLE_DUM and TABLE_DEE • Table with No Column只會有兩種情形： • 只有一個列(TABLE_DEE) → True/Yes • 沒有任何列(TABLE_DUM) →False/No • SQL難以精確的處理Yes/NO問題 ，因為SQL並不支援Table with No Column。 • Ex. ”Does employee E1 work in department D1?” • 畫出 TABLE_DUM與TABLE_DEE的圖形是困難的。

  22. Effect on Relational Algebra Identity • 普通算術中數字1為一個Identity • a*1 = 1*a = a, for all numbers a • 在關聯式代數中TABLE_DEE為一個Identity • T TIMES DEE = DEE TIMES T = T for all tables T

  23. Product (Cartesian product)DEE • T TIMES DEE = DEE TIMES T = T for all tables T Product TABLE_DEE =

  24. Product (Cartesian product)DUM • T TIMES DUM = DUM TIMES T = a table with the same heading as T but with no rows at all. Product TABLE_DUM =

  25. Join DEE • T JOIN DEE = DEE JOIN T = a table with the same heading as T but with no rows at all. JOIN TABLE_DEE =

  26. Join DUM • T JOIN DUM = DUM JOIN T = a table with the same heading as T but with no rows at all. JOIN TABLE_DEE =

  27. THE END rexmen 2001

More Related