ch8-Relational Database Design要点.ppt

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * 3NF Decomposition Algorithm (Cont.) Above algorithm ensures: each relation schema Ri is in 3NF decomposition is dependency preserving and lossless-join Proof of correctness…… 3NF Decomposition: An Example Relation schema: cust_banker_branch = (customer_id, employee_id, branch_name, type ) The functional dependencies for this relation schema are: customer_id, employee_id ? branch_name, type employee_id ? branch_name customer_id, branch_name ? employee_id We first compute a canonical cover branch_name is extraneous in the r.h.s. of the 1st dependency FC ={customer_id, employee_id ? type, employee_id ? branch_name, customer_id, branch_name ? employee_id} 3NF Decompsition Example (Cont.) The for loop generates following 3NF schema: (customer_id, employee_id, type ), (employee_id, branch_name), (customer_id, branch_name, employee_id) Observe that (customer_id, employee_id, type ) contains a candidate key of the original schema, so no further relation schema needs be added At end of for loop, detect and delete schemas, such as (employee_id, branch_name), which are subsets of other schemas result will not depend on the order in which FDs are considered The resultant simplified 3NF schema is: (customer_id, employee_id, type) (customer_id, branch_name, employee_id) Comparison of BCNF and 3NF It is always possible to decompose a relation into a set of relations that are in 3NF such that: the decomposition is lossless the dependencies are preserved It is always possible to decompose a relation into a set of relations that are in BCNF such that: the decomposition is lossless it may not be possible to preserve dependencies. Design Goals Goal for a relational database design is: BCNF\Lossless join\Dependency preservation. If we cannot achieve this, we accept one of Lack of dependency preservation Redundancy d