ch7关系数据库设计理论.pptx

Chapter 7: Relational Database Design;Chapter 7: Relational Database Design;First Normal Form;First Normal Form (Contd.);Pitfalls in Relational Database Design;Example;Decomposition;Example of Non Lossless-Join Decomposition ;Goal — Devise a Theory for the Following;Functional Dependencies;Functional Dependencies (Cont.);Functional Dependencies (Cont.);Use of Functional Dependencies;Functional Dependencies (Cont.);Closure of a Set of Functional Dependencies;Example;Procedure for Computing F+;Closure of Functional Dependencies (Cont.);Closure of Attribute Sets;Example of Attribute Set Closure;Uses of Attribute Closure;Canonical Cover;Extraneous Attributes无关属性;Testing if an Attribute is Extraneous;Canonical Cover;Example of Computing a Canonical Cover;Goals of Normalization;Decomposition;Example of Lossy-Join Decomposition ;Normalization Using Functional Dependencies;Example;Testing for Dependency Preservation;Boyce-Codd Normal Form;Example;Testing for BCNF;BCNF Decomposition Algorithm;Example of BCNF Decomposition;Testing Decomposition for BCNF;BCNF and Dependency Preservation;Third Normal Form: Motivation;Third Normal Form;3NF (Cont.);Testing for 3NF;3NF Decomposition Algorithm;3NF Decomposition Algorithm (Cont.);Example;Applying 3NF to Banker-info-schema;Comparison of BCNF and 3NF;Comparison of BCNF and 3NF (Cont.);Design Goals;Testing for FDs Across Relations;Multivalued Dependencies;Since there are non-trivial dependencies, (course, teacher, book) is the only key, and therefore the relation is in BCNF Insertion anomalies – i.e., if Sara is a new teacher that can teach database, two tuples need to be inserted (database, Sara, DB Concepts) (database, Sara, Ullman);Therefore, it is better to decompose classes into:;Multivalued Dependencies (MVDs);MVD (Cont.);Example;Example (Cont.);Use of Multivalued Dependencies;Theory of MVDs;Fourth Normal Form;Restriction of Multivalued Dependencies;4NF Decomposition Algorithm;Example;Further Normal Forms;Overall Database