ch8_excerpts_normal_forms

ch8_excerpts_normal_forms - Database System Concepts, 6 th...

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

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

View Full DocumentRight Arrow Icon

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

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

Unformatted text preview: Database System Concepts, 6 th Ed . Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re-use Chapter 8: Relational Database Design Chapter 8: Relational Database Design Silberschatz, Korth and Sudarshan 8.2 Database System Concepts - 6 th Edition Chapter 8: Relational Database Design Chapter 8: Relational Database Design Features of Good Relational Design Atomic Domains and First Normal Form Decomposition Using Functional Dependencies Functional Dependency Theory [skipping details] Algorithms for Functional Dependencies [skipping details] Decomposition Using Multivalued Dependencies [skip] More Normal Form [skip] Database-Design Process Modeling Temporal Data [skip] Silberschatz, Korth and Sudarshan 8.3 Database System Concepts - 6 th Edition Combine Schemas? Combine Schemas? Suppose we combine instructor and department into inst_dept (No connection to relationship set inst_dept) Result is possible repetition of information Silberschatz, Korth and Sudarshan 8.4 Database System Concepts - 6 th Edition A Combined Schema Without Repetition 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 Silberschatz, Korth and Sudarshan 8.5 Database System Concepts - 6 th Edition What About Smaller Schemas? 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 . Silberschatz, Korth and Sudarshan 8.6 Database System Concepts - 6 th Edition A Lossy Decomposition A Lossy Decomposition Silberschatz, Korth and Sudarshan 8.7 Database System Concepts - 6 th Edition Example of Lossless-Join Decomposition Example of Lossless-Join Decomposition Lossless join decomposition Decomposition of R = (A, B, C) R 1 = (A, B) R 2 = (B, C) A B 1 2 A B 1 2 r B,C ( r ) A (r) B (r) A B 1 2 C A B B 1 2 C A B C A B A,B ( r ) Silberschatz, Korth and Sudarshan 8.8 Database System Concepts - 6 th Edition First Normal Form First Normal Form Domain is atomic if its elements are considered to be indivisible units...
View Full Document

Page1 / 33

ch8_excerpts_normal_forms - Database System Concepts, 6 th...

This preview shows document pages 1 - 9. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online