Lecture Database system concepts - Chapter 7: Relational database design

In this chapter you will learn: 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. | Chapter 7: Relational Database Design Database System Concepts, 5th Ed. ©Silberschatz, Korth and Sudarshan See ­ for conditions on re­use Chapter 7: Relational Database Design s Features of Good Relational Design s Atomic Domains and First Normal Form s Decomposition Using Functional Dependencies s Functional Dependency Theory s Algorithms for Functional Dependencies s Decomposition Using Multivalued Dependencies s More Normal Form s Database­Design Process s Modeling Temporal Data Database System Concepts ­ 5th Edition, July 28, 2005. 7. ©Silberschatz, Korth and Sudarshan The Banking Schema s branch = (branch_name, branch_city, assets) s customer = (customer_id, customer_name, customer_street, customer_city) s loan = (loan_number, amount) s account = (account_number, balance) s employee = (employee_id. employee_name, telephone_number, start_date) s dependent_name = (employee_id, dname) s account_branch = (account_number, branch_name) s loan_branch = (loan_number, branch_name) s borrower = (customer_id, loan_number) s depositor = (customer_id, account_number) s cust_banker = (customer_id, employee_id, type) s works_for = (worker_employee_id, manager_employee_id) s payment = (loan_number, payment_number, payment_date, payment_amount) s savings_account = (account_number, interest_rate) s checking_account = (account_number, overdraft_amount) Database System Concepts ­ 5th Edition, July 28, 2005. 7. ©Silberschatz, Korth and Sudarshan Combine Schemas? s Suppose we combine borrower and loan to get bor_loan = (customer_id, loan_number, amount ) s Result is possible repetition of information (L­100 in example below) Database System Concepts ­ 5th Edition, July 28, 2005. 7. ©Silberschatz, Korth and Sudarshan A Combined Schema Without Repetition s Consider combining loan_branch and loan loan_amt_br = (loan_number, amount, branch_name) s .