Chapter 7:Relational Database Design Features of Good Relational Design Atomic Domains and First Normal Form Decomposition Using Functional Dependencies Functional Dependency Theory Algorithms for Functional Dependencies Decomposition Using Multivalued Dependencies More Normal Form Database-Design Process Modeling Temporal Data Database System Concepts-5th Edition,Oct 5,2006 72 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 7.2 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Chapter 7: Relational Database Design Features of Good Relational Design Atomic Domains and First Normal Form Decomposition Using Functional Dependencies Functional Dependency Theory Algorithms for Functional Dependencies Decomposition Using Multivalued Dependencies More Normal Form Database-Design Process Modeling Temporal Data
The Banking Schema 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) 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) Database System Concepts-5th Edition,Oct 5,2006 7.3 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 7.3 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 The Banking Schema 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) 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) loan_number foan number mnoinE : 23-652 L-100 L-100 10000 15-202 L-100 23-521 L-100 Joayt borroier c里stomer_i loan_mmber amoimt 23-652 L-100 10000 15-202 L-100 10000 23-521 L-100 10000 bor_loun Database System Concepts -5th Edition,Oct 5,2006 7.4 @Silberschatz,Korth and Sudarshan
Database System Concepts - 5 7.4 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 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) loan_mumber mount loanmonber Ianchnamie L-100 10000 L-100 Springfield loan lomn_bmnch foan_nionber 0士 brduante L-100 10000 Springfield lon_omt_br Database System Concepts-5th Edition,Oct 5,2006 7.5 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 7.5 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 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? 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 as a functional dependency: loan_number→amount In bor loan,because loan_numberis not a candidate key,the amount of a loan may have to be repeated.This indicates the need to decompose bor loan. 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. Database System Concepts-5th Edition,Oct 5,2006 7.6 ©Silberschat乜，Korth and Sudarshan
Database System Concepts - 5 7.6 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 What About Smaller Schemas? 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 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. 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