《数据库系统概念 Database System Concepts》原书教学资源（第五版，PPT课件讲稿，英文版）Chapter 7 Relational Database Design

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

A Lossy Decomposition emplovee_id emplowee_name telephone nunther start_date 123-45-6789 Kim 882-0000 198403-29 987-65-4321 Kim 869-9999 1981-01-16 emplovee emploveeid enplovee_name enploveename telephonenumber startdate 123-45-6789 Kim Kim 882-0000 1984-03-29 987-65-4321 Kim Kim 869-9999 1981-01-16 emploveeid emplovee name telephone_number start_date 12345-6789 Kim 882-0000 1984-03-29 123-45-6789 Kim 869-9999 1981-01-16 987-65-4321 Kim 882-0000 198403-29 987-65-4321 Kim 869-9999 1981-01-16 Database System Concepts -5th Edition,Oct 5,2006 7.7 @Silberschatz,Korth and Sudarshan

Database System Concepts - 5 7.7 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 A Lossy Decomposition

First Normal Form Domain is atomic if its elements are considered to be indivisible units Examples of non-atomic domains: Set of names,composite attributes Identification numbers like CS101 that can be broken up into parts A relational schema R is in first normal form if the domains of all attributes of R are atomic Non-atomic values complicate storage and encourage redundant (repeated)storage of data Example:Set of accounts stored with each customer,and set of owners stored with each account We assume all relations are in first normal form (and revisit this in Chapter 9) Database System Concepts-5th Edition,Oct 5,2006 7.8 @Silberschatz,Korth and Sudarshan

Database System Concepts - 5 7.8 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 First Normal Form Domain is atomic if its elements are considered to be indivisible units Examples of non-atomic domains:  Set of names, composite attributes  Identification numbers like CS101 that can be broken up into parts A relational schema R is in first normal form if the domains of all attributes of R are atomic Non-atomic values complicate storage and encourage redundant (repeated) storage of data Example: Set of accounts stored with each customer, and set of owners stored with each account We assume all relations are in first normal form (and revisit this in Chapter 9)

First Normal Form (Cont'd) Atomicity is actually a property of how the elements of the domain are used. Example:Strings would normally be considered indivisible Suppose that students are given roll numbers which are strings of the form CS0012 or EE1127 If the first two characters are extracted to find the department,the domain of roll numbers is not atomic Doing so is a bad idea:leads to encoding of information in application program rather than in the database. Database System Concepts-5th Edition,Oct 5,2006 7.9 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 5 7.9 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 First Normal Form (Cont’d) Atomicity is actually a property of how the elements of the domain are used. Example: Strings would normally be considered indivisible Suppose that students are given roll numbers which are strings of the form CS0012 or EE1127 If the first two characters are extracted to find the department, the domain of roll numbers is not atomic. Doing so is a bad idea: leads to encoding of information in application program rather than in the database

Goal-Devise a Theory for the Following Decide whether a particular relation R is in"good"form. In the case that a relation R is not in "good"form,decompose it into a set of relations {R1,R2,...,R}such that each relation is in good form the decomposition is a lossless-join decomposition Our theory is based on: functional dependencies multivalued dependencies Database System Concepts-5th Edition,Oct 5,2006 7.10 @Silberschatz,Korth and Sudarshan

Database System Concepts - 5 7.10 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Goal — Devise a Theory for the Following Decide whether a particular relation R is in “good” form. In the case that a relation R is not in “good” form, decompose it into a set of relations {R1 , R2 , ..., Rn } such that each relation is in good form the decomposition is a lossless-join decomposition Our theory is based on: functional dependencies multivalued dependencies

Functional Dependencies Constraints on the set of legal relations. Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. A functional dependency is a generalization of the notion of a key. Database System Concepts-5th Edition,Oct 5,2006 7.11 ©Silberschat乜，Korth and Sudarshan

Database System Concepts - 5 7.11 ©Silberschatz, Korth and Sudarshan th Edition, Oct 5, 2006 Functional Dependencies Constraints on the set of legal relations. Require that the value for a certain set of attributes determines uniquely the value for another set of attributes. A functional dependency is a generalization of the notion of a key