PowerPoint PresentationChapter 10 Functional Dependencies and Normalization for Relational DatabasesChapter OutlineChapter Outline(contd.)1 Informal Design Guidelines for Relational Databases (1)Informal Design Guidelines for Relational Databases (2)1.1 Semantics of the Relation AttributesFigure 10.1 A simplified COMPANY relational database schema1.2 Redundant Information in Tuples and Update AnomaliesEXAMPLE OF AN UPDATE ANOMALY (1)EXAMPLE OF AN UPDATE ANOMALY (2)Figure 10.3 Two relation schemas suffering from update anomaliesFigure 10.4 Example States for EMP_DEPT and EMP_PROJGuideline to Redundant Information in Tuples and Update Anomalies1.3 Null Values in Tuples1.4 Spurious TuplesSpurious Tuples (2)2.1 Functional Dependencies (1)Functional Dependencies (2)Examples of FD constraints (1)Examples of FD constraints (2)2.2 Inference Rules for FDs (1)Inference Rules for FDs (2)Inference Rules for FDs (3)2.3 Equivalence of Sets of FDs2.4 Minimal Sets of FDs (1)Minimal Sets of FDs (2)3 Normal Forms Based on Primary Keys3.1 Normalization of Relations (1)Normalization of Relations (2)3.2 Practical Use of Normal Forms3.3 Definitions of Keys and Attributes Participating in Keys (1)Definitions of Keys and Attributes Participating in Keys (2)3.2 First Normal FormFigure 10.8 Normalization into 1NFFigure 10.9 Normalization nested relations into 1NF3.3 Second Normal Form (1)Second Normal Form (2)Figure 10.10 Normalizing into 2NF and 3NFFigure 10.11 Normalization into 2NF and 3NF3.4 Third Normal Form (1)Third Normal Form (2)4 General Normal Form Definitions (For Multiple Keys) (1)General Normal Form Definitions (2)5 BCNF (Boyce-Codd Normal Form)Figure 10.12 Boyce-Codd normal formFigure 10.13 a relation TEACH that is in 3NF but not in BCNFAchieving the BCNF by Decomposition (1)Achieving the BCNF by Decomposition (2)Chapter 10Functional Dependencies and Normalization for Relational DatabasesCopyright © 2004 Pearson Education, Inc.Chapter 10-3Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter Outline1 Informal Design Guidelines for Relational Databases1.1Semantics of the Relation Attributes1.2 Redundant Information in Tuples and Update Anomalies1.3 Null Values in Tuples1.4 Spurious Tuples2 Functional Dependencies (FDs)2.1 Definition of FD2.2 Inference Rules for FDs2.3 Equivalence of Sets of FDs2.4 Minimal Sets of FDsChapter 10-4Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter Outline(contd.)3 Normal Forms Based on Primary Keys3.1 Normalization of Relations 3.2 Practical Use of Normal Forms 3.3 Definitions of Keys and Attributes Participating in Keys 3.4 First Normal Form3.5 Second Normal Form3.6 Third Normal Form4 General Normal Form Definitions (For Multiple Keys)5 BCNF (Boyce-Codd Normal Form)Chapter 10-5Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition 1 Informal Design Guidelines for Relational Databases (1)What is relational database design?The grouping of attributes to form "good" relation schemas Two levels of relation schemas–The logical "user view" level–The storage "base relation" level Design is concerned mainly with base relations What are the criteria for "good" base relations?Chapter 10-6Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Informal Design Guidelines for Relational Databases (2)We first discuss informal guidelines for good relational designThen we discuss formal concepts of functional dependencies and normal forms- 1NF (First Normal Form)- 2NF (Second Normal Form)- 3NF (Third Normal Form)- BCNF (Boyce-Codd Normal Form)Additional types of dependencies, further normal forms, relational design algorithms by synthesis are discussed in Chapter 11Chapter 10-7Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition 1.1 Semantics of the Relation Attributes GUIDELINE 1: Informally, each tuple in a relation should represent one entity or relationship instance. (Applies to individual relations and their attributes).Attributes of different entities (EMPLOYEEs, DEPARTMENTs, PROJECTs) should not be mixed in the same relationOnly foreign keys should be used to refer to other entities Entity and relationship attributes should be kept apart as much as possible. Bottom Line: Design a schema that can be explained easily relation by relation. The semantics of attributes should be easy to interpret.Chapter 10-8Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Figure 10.1 A simplified COMPANY relational database schemaNote: The above figure is now called Figure 10.1 in Edition 4Chapter 10-9Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition 1.2 Redundant Information in Tuples and Update Anomalies Mixing attributes of multiple entities may cause problemsInformation is stored redundantly wasting storageProblems with update anomalies–Insertion anomalies–Deletion anomalies–Modification anomaliesChapter 10-10Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition EXAMPLE OF AN UPDATE ANOMALY (1) Consider the relation:EMP_PROJ ( Emp#, Proj#, Ename, Pname, No_hours) Update Anomaly: Changing the name of project number P1 from “Billing” to “Customer-Accounting” may cause this update to be made for all 100 employees working on project P1.Chapter 10-11Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition EXAMPLE OF AN UPDATE ANOMALY (2)Insert Anomaly: Cannot insert a project unless an employee is assigned to . Inversely - Cannot insert an employee unless an he/she is assigned to a project. Delete Anomaly: When a project is deleted, it will result in deleting all the employees who work on that project. Alternately, if an employee is the sole employee on a project, deleting that employee would result in deleting the corresponding project.Chapter 10-12Copyright © 2004 Ramez Elmasri and Shamkant NavatheElmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Figure 10.3 Two relation schemas suffering from update
View Full Document
Unlocking...