《数据库系统原理Database System Concepts》双语课件-ch6(RDB Design).pptVIP

2007-6-16 APWeb/WAIM2007 Huang Shan 2007-6-16 APWeb/WAIM2007 Huang Shan 2007-6-16 Relational Database Design Contents Background Functional Dependencies Functional Dependencies Theory Normal Form Schema Decomposition Background Design Choices: Small vs. Large Schemas Which design do you like better? Why? What’s wrong? What’s wrong? (cont.) Redundancy: The department is described redundantly. Insertion anomalies if you want to insert a department, you can’t...until there is at least one employee. Deletion anomalies if you delete an employee, is that department gone? was this the last employee in that dept.? Modification anomalies change DNAME, for example, need to change it everywhere! Design Choices: Small vs. Large Schemas Which design do you like better? Why? Design Choices: Small vs. Large Schemas Which design do you like better? Why? Loss of Information in Decomposition Goals of Relational Database Design A bad design may lead to Repetition of Information Inability to represent certain information Relational database design requires that we find a “good” collection of relation schemas Design Goals: Avoid redundant data Ensure that relationships among attributes are represented Enhance query efficiency Facilitate the checking of updates for violation of database integrity constraints Goal — Devise a Theory for the Following Design Goals: In the case that a relation R is not in “good” form, decompose it into a set of relations {R1, R2, ..., Rn} such that each relation is in good form lossless-join decomposition dependency preservation Our theory is based on: Functional dependencies Multi-valued dependencies Functional Dependencies Functional Dependencies FDs is another kind of integrity constraints – generalization of the notion of a key If two tuples of R agree on attributes A1, A2, …, An, then they must also agree on some other attribute B. A1, A2, …, An ? B If the value of the first attribute (s), A1, A2, …, An , is known,