tailieunhanh - Lecture Database system concepts (6/e): Chapter 8 - Silberschatz, Korth, Sudarshan

Chapter 8 - Relational database design. This chapter introduces the theory of relational database design. The theory of functional dependencies and normalization is covered, with emphasis on the motivation and intuitive understanding of each normal form. This chapter begins with an overview of relational design and relies on an intuitive understanding of logical implication of functional dependencies. | Chapter 8: Relational Database Design Database System Concepts, 6th Ed. ©Silberschatz, Korth and Sudarshan See for conditions on re-use 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 ©Silberschatz, Korth and Sudarshan Combine Schemas? Suppose we combine instructor and department into inst_dept (No connection to relationship set inst_dept) Result is possible repetition of information Database System Concepts - 6th Edition ©Silberschatz, Korth and Sudarshan 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 ©Silberschatz, Korth and Sudarshan 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