kc第8讲数据库规范化理论.ppt

The Banking Schema - 根据ER图获得的 branch = (branch_name, branch_city, assets) customer = (customer_id, customer_name, customer_street, customer_city) loan = (loan_number, amount) account = (account_number, balance) employee = (employee_id. employee_name, telephone_number, start_date) dependent_name = (employee_id, dname) - - - account_branch = (account_number, branch_name) loan_branch = (loan_number, branch_name) borrower = (customer_id, loan_number) depositor = (customer_id, account_number, access_date) cust_banker = (customer_id, employee_id, type) works_for = (worker_employee_id, manager_employee_id) - - - payment = (loan_number, payment_number, payment_date, payment_amount) - - - savings_account = (account_number, interest_rate) checking_account = (account_number, overdraft_amount) 随意地 Combine Schemas Suppose we combine borrower and loan to get bor_loan = (customer_id, loan_number, amount ) Result is possible repetition of information (L-100 in example below) A Combined Schema Without Repetition Consider combining loan_branch and loan loan_amt_br = (loan_number, amount, branch_name) No repetition重复 (as suggested by example below) What About Smaller Schemas? 不能随意分解：Not all decompositions are good. Suppose we decompose employee into employee1 = (employee_id, employee_name) employee2 = (employee_name, telephone_number, start_date) The next slide shows how we lose information -- we cannot reconstruct the original employee relation -- and so, this is a lossy decomposition(有损分解). 分解有价值、有标准：Suppose we had started with bor_loan 参考. How would we know to split up (decompose) it into borrower and loan? Write a rule “if there were a schema (loan_number, amount), then loan_number would be a candidate key” Denote it as a functional dependency: loan_number ? amount In bor_loan, because loan_number is not a candidate key, the amount of a loan may have to be repeated. This indicates the need to decompose bor_loan. A Lossy Decomposition有损分解的例示 First