L19 - Design1

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

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

View Full Document Right Arrow Icon
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
Background image of page 1

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

View Full DocumentRight Arrow Icon
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 )
Background image of page 2
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)
Background image of page 3

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

View Full DocumentRight Arrow Icon
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?
Background image of page 4
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?
Background image of page 5

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

View Full DocumentRight Arrow Icon
A A Lossy Lossy Decomposition Decomposition
Background image of page 6
Goal Goal Devise a Theory for the Following Devise a Theory for the Following ±
Background image of page 7

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

View Full DocumentRight Arrow Icon
Image of page 8
This is the end of the preview. Sign up to access the rest of the document.

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 Right Arrow Icon
Ask a homework question - tutors are online