DATABASE6 2

2020-03-01 165浏览

  • 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.6 Database Design  Section 6.5. Preliminaries for Normalization Rules of relatedness and a desire to avoid certain types of bad behavior (anomalies) causes us to factor this big table into smaller tables, achieving more and more restrictive forms (Normal Forms). 1NF, 2NF, 3NF, BCNF. Zhejiang Sci-Tech University Lican Huang 2
  • 3.6 Database Design       RunningExample:Employee Information. See pg. 354. Bad design. Anomalies. There is replication of employe e data on different rows of emp_info DELETE ANOMALY.-- Lost phone number INSERT ANOMALY --not insert, until a skill f or that employee exists . UPDATE ANOMALY,-- , change a lot of rows Zhejiang Sci-Tech University Lican Huang 3
  • 4.6. Database Design  Section 6.6. Functional Dependencies Def 6.6.1. Given a table T with at leas t two attributes A and B, we say that A -> B (A functionally determines B, o r B is functionally dependent on A) iff it is the intent of the designer that for any set of rows that might exist in th e table, two rows in T cannot agree o n A and disagree on B. More formally, given two rows r1 and r2 in T, if r1(A) = r2(A) then r1(B) = r2(B). Zhejiang Sci-Tech University Lican Huang 4
  • 5.6. Database Design  Def. 6.6.2. when one attribute A fu nctionally determines another B. No w sets ofattributes:X = A1 A2 . . . A k, and Y = B1 B2 . . . Bm. Say X -> Y iff it is the intention of the designer t hat two rows cannot simultaneously agree on X and disagree on Y. Zhejiang Sci-Tech University Lican Huang 5
  • 6.6. Database Design Theorem 6.6.3. Inclusion rule. Given T with Head(T). If X and Y are sets in H ead(T) and Y  X, then X -> Y (for AN Y content for T). (Venn Diagram.) Zhejiang Sci-Tech University Lican Huang 6
  • 7.6. Database Design   Def. 6.6.4. A trivial dependency is an FD of the form X -> Y that holds for any tabl e T where X UNION Y  Head(T). (Assu me ANY content for T.) Theorem 6.6.5. Given a trivial depende ncy X -> Y in T, it must be the case that Y  X. (Venn Diagram, X, Y disjoint, sho w A.) Zhejiang Sci-Tech University Lican Huang 7
  • 8.6. Database Design     Def. 6.6.6. Armstrong's Axioms. From the following small set of basic rules of implica tion among FDs, we can derive all others t hat are true. [1] Inclusionrule:'>rule: