DATABASEch2

2020-03-01 161浏览

  • 1.DATABASE Principles, Programming, and Performance Patrick O’Neil and Elizabeth O’Neil Lectured by Dr. Lican HuangEmail:licanhuang@zist.edu.cn huang_lican@yahoo.co.ukhttp://virgo.sourceforge.net/licanhuang.htmlZhejiang Sci-Tech University Lican Huang 1
  • 2.2.2 Domains and Datatypes  Table column is declared to have a ce rtain Datatype eg. Char(13), Integer  Domain values from A set eg. City (hangzhou, …..) Discount 0—20% Zhejiang Sci-Tech University Lican Huang 2
  • 3.2.2 Tables and Relations  Cartesian Product The Cartesian product of a sequence of k sets S1, S2,… Sk is represented by S1XS2 X…X Sk, and consists of all possi ble k-tuples(e1,e2,…ek), where e1 is an y element in S1, e2 is any element in S 2, …, and ek is any element in Sk. Zhejiang Sci-Tech University Lican Huang 3
  • 4.2.2 Tables and Relations  Relation Subset of Cartesian Product Head(T) = A1…An T is a subset of the Cartesian product of thesets:Domain(A1)… Domain(An) Zhejiang Sci-Tech University Lican Huang 4
  • 5.2.3 Relation Rules Rule 1: First Normal Form Rule. In defining tables, the relational model insists that columns that have multi-valued attributes( some time called repeating fields) or have any internal structure (like a record) are not permitted! A Table that obeys this is said to be in first normal form. Zhejiang Sci-Tech University Lican Huang 5
  • 6.2.3 Relation Rules Rule 2: Access Rows by Content Only Rule A second rule of the relational model states that we can only retrieve rows by their content, the attribute values that exist in each row. Zhejiang Sci-Tech University Lican Huang 6
  • 7.2.3 Relation Rules Rule 3: The Unique Row Rule A third rule of the relational mode l is that two tuples in a relation (row s in a table) cannot be identical in all column values at once. Zhejiang Sci-Tech University Lican Huang 7
  • 8.2.4 Keys, Superkeys and NULL val ues Page 37: Superkey Key, ( minimum subset) Zhejiang Sci-Tech University Lican Huang 8
  • 9.2.4 Keys, Superkeys and NULL val uesDefinition:Primary key of a table Page 39 Zhejiang Sci-Tech University Lican Huang 9
  • 10.2.4 Keys, Superkeys and NULL val ues Null Values Unknown, not yet defined. Different from zero or space. Zhejiang Sci-Tech University Lican Huang 10
  • 11.2.4 Keys, Superkeys and NULL val ues Rule 4: Entity Integrity Rule. No Column belonging to a primary key of a table T is allowed to take on NULL values for any row in T. Zhejiang Sci-Tech University Lican Huang 11
  • 12.2.5 Relational Algebra Operations of relational algebra Fundamental Operations of Relational Algebra Set-theoretic operations Union, INTERSECTION, DIFFERENCE,PRODUCT Zhejiang Sci-Tech University Lican Huang 12
  • 13.2.5 Relational Algebra Native Relational operations project select join division Zhejiang Sci-Tech University Lican Huang 13
  • 14.2.6 Set-theoretic operations Def. Compatible tables Zhejiang Sci-Tech University Lican Huang 14
  • 15.2.6 Set-theoretic operations Def. Union R UNION S all rows in R or S or both example p43 Zhejiang Sci-Tech University Lican Huang 15
  • 16.2.6 Set-theoretic operations Def. Intersection R INTERSECT S both in R and S example page 43 Zhejiang Sci-Tech University Lican Huang 16
  • 17.2.6 Set-theoretic operations Def. Difference R DIFFRENCE S in R but not in S example page 44 Zhejiang Sci-Tech University Lican Huang 17
  • 18.2.6 Set-theoretic operations Def. Assignment, Allias p44 example p 45 Zhejiang Sci-Tech University Lican Huang 18
  • 19.2.6 Set-theoretic operations Product Operation Def. Zhejiang Sci-Tech University Lican Huang 19
  • 20.2.6 Set-theoretic operations Product example P46-47 Zhejiang Sci-Tech University Lican Huang 20