L19 - Design1

L19 - Design1 - Chapter 7 of the text Relational Database...

This preview shows pages 1–8. Sign up to view the full content.

Chapter 7 of the text: Relational Database Design Chapter 7 of the text: 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

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
Remember the Banking Schema Remember 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 )
What if We Combine Schemas? What if We 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)

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
A Combined Schema Without Repetition A Combined Schema Without Repetition ± Consider combining loan_branch and loan loan_amt_br = ( loan_number , amount , branch_name ) ± No repetition – why should this be so?
What About Smaller Schemas? What About Smaller Schemas? ± Suppose we had started with bor_loan. How would we know to split up (decompose) it into borrower and loan ? ± Can we find a rule that would tell us “if we make a smaller schema ( loan_number, amount ), then loan_number would be a candidate key” ² Staring at this, we can see (in bor_loan) a functional dependency : loan_number amount ² But we can also see that l oan_number (in bor_loan) is not a candidate key for bor_loan, so the amount of a loan may have to be repeated. ² Repeated data is bad, and this motivates us to decompose bor_loan . ² Not all decompositions are good. What happens if we decompose employee = ( employee_id . employee_name , telephone_number , start_date ) into employee1 = ( employee_id , employee_name ) employee2 = ( employee_name , telephone_number , start_date ) ² What happens? Can we reconstruct the original employee relation?

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
A A Lossy Lossy Decomposition Decomposition
Goal Goal Devise a Theory for the Following Devise a Theory for the Following ±

This preview has intentionally blurred sections. Sign up to view the full version.

View Full Document
This is the end of the preview. Sign up to access the rest of the document.

This note was uploaded on 04/08/2008 for the course EE 468 taught by Professor Conry during the Spring '08 term at Clarkson University .

Page1 / 20

L19 - Design1 - Chapter 7 of the text Relational Database...

This preview shows document pages 1 - 8. Sign up to view the full document.

View Full Document
Ask a homework question - tutors are online