Lecture Fundamentals of Database Systems - Chapter 10: Functional dependencies and normalization for relational databases

Chapter 10 present functional dependencies and normalization for relational databases. This chapter includes contents: Informal design guidelines for relational databases, functional dependencies (FDs), normal forms based on primary keys, general normal form definitions (For Multiple Keys), BCNF (Boyce-Codd Normal Form).
Nội dung trích xuất từ tài liệu:
Lecture Fundamentals of Database Systems - Chapter 10: Functional dependencies and normalization for relational databases Chapter 10Functional Dependencies and Normalization for Relational Databases Copyright © 2004 Pearson Education, Inc. Chapter Outline1 Informal Design Guidelines for Relational Databases 1.1Semantics of the Relation Attributes 1.2 Redundant Information in Tuples and Update Anomalies 1.3 Null Values in Tuples 1.4 Spurious Tuples2 Functional Dependencies (FDs) 2.1 Definition of FD 2.2 Inference Rules for FDs 2.3 Equivalence of Sets of FDs 2.4 Minimal Sets of FDs Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-3 Copyright © 2004 Ramez Elmasri and Shamkant Navathe Chapter Outline(contd.)3 Normal Forms Based on Primary Keys 3.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 Form 3.5 Second Normal Form 3.6 Third Normal Form4 General Normal Form Definitions (For Multiple Keys)5 BCNF (Boyce-Codd Normal Form) Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-4 Copyright © 2004 Ramez Elmasri and Shamkant Navathe 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? Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-5 Copyright © 2004 Ramez Elmasri and Shamkant Navathe Informal Design Guidelines for Relational Databases (2) We first discuss informal guidelines for good relational design Then 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 11 Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-6 Copyright © 2004 Ramez Elmasri and Shamkant Navathe 1.1 Semantics of the Relation AttributesGUIDELINE 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 relation Only 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. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-7 Copyright © 2004 Ramez Elmasri and Shamkant Navathe Figure 10.1 A simplified COMPANY relational database schemaNote: The above figure is now called Figure 10.1 in Edition 4 Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-8 Copyright © 2004 Ramez Elmasri and Shamkant Navathe 1.2 Redundant Information in Tuples and Update Anomalies Mixing attributes of multiple entities may cause problems Information is stored redundantly wasting storage Problems with update anomalies – Insertion anomalies – Deletion anomalies – Modification anomalies Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-9 Copyright © 2004 Ramez Elmasri and Shamkant Navathe 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. Elmasri/Navathe, Fundamentals of Database Systems, Fourth Edition Chapter 10-10 Copyright © 2004 Ramez Elmasri and Shamkant Navathe 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 ...