Chapter 8: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-6th Edition 8.2 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 8.2 ©Silberschatz, Korth and Sudarshan th Edition Chapter 8: 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
Combine Schemas? Suppose we combine instructor and department into inst_dept (No connection to relationship set inst_dept) Result is possible repetition of information ID name salary dept name building budget 22222 Einstein 95000 Physics Watson 70000 12121 Wu 90000 Finance Painter 120000 32343 El Said 60000 History Painter 50000 45565 Katz 75000 Comp.Sci. Taylor 100000 98345 Kim 80000 Elec.Eng. Taylor 85000 76766 Crick 72000 Biology Watson 90000 10101 Srinivasan 65000 Comp.Sci. Taylor 100000 58583 Califieri 62000 History Painter 50000 83821 Brandt 92000 Comp.Sci. Taylor 100000 15151 Mozart 40000 Music Packard 80000 33456 Gold 87000 Physics Watson 70000 76543 Singh 80000 Finance Painter 120000 Database System Concepts-6th Edition 8.3 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 8.3 ©Silberschatz, Korth and Sudarshan th Edition Combine Schemas? Suppose we combine instructor and department into inst_dept (No connection to relationship set inst_dept) Result is possible repetition of information
A Combined Schema Without Repetition Consider combining relations sec_class(sec id,building,room number)and section(course_id,sec_id,semester,year) into one relation section(course_id,sec_id,semester,year, building,room_number) No repetition in this case Database System Concepts-6th Edition 8.4 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 8.4 ©Silberschatz, Korth and Sudarshan th Edition A Combined Schema Without Repetition Consider combining relations sec_class(sec_id, building, room_number) and section(course_id, sec_id, semester, year) into one relation section(course_id, sec_id, semester, year, building, room_number) No repetition in this case
What About Smaller Schemas? Suppose we had started with inst_dept.How would we know to split up (decompose)it into instructor and department? Write a rule"if there were a schema(dept_name,building,budget),then dept_name would be a candidate key" Denote as a functional dependency: dept_name->building,budget In inst_dept,because dept_name is not a candidate key,the building and budget of a department may have to be repeated. This indicates the need to decompose inst_dept Not all decompositions are good.Suppose we decompose employee(ID,name,street,city,salary)into employee1 (ID,name) employee2(name,street,city,salary) 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-6th Edition 8.5 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 8.5 ©Silberschatz, Korth and Sudarshan th Edition What About Smaller Schemas? Suppose we had started with inst_dept. How would we know to split up (decompose) it into instructor and department? Write a rule “if there were a schema (dept_name, building, budget), then dept_name would be a candidate key” Denote as a functional dependency: dept_name → building, budget In inst_dept, because dept_name is not a candidate key, the building and budget of a department may have to be repeated. This indicates the need to decompose inst_dept Not all decompositions are good. Suppose we decompose employee(ID, name, street, city, salary) into employee1 (ID, name) employee2 (name, street, city, salary) 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 ID name street city salary 57766 Kim Main Perryridge 75000 98776 Kim North Hampton 67000 employee ID name name street city salary 57766 Kim Kim Main Perryridge 75000 98776 Kim Kim North Hampton 67000 natural join ID mame street city salary 57766 Kim Main Perryridge 75000 57766 Kim North Hampton 67000 98776 Kim Main Perryridge 75000 98776 Kim North Hampton 67000 Database System Concepts-6th Edition 8.6 @Silberschatz,Korth and Sudarshan
Database System Concepts - 6 8.6 ©Silberschatz, Korth and Sudarshan th Edition A Lossy Decomposition