tailieunhanh - Lecture Fundamentals of database systems: Chapter 10 - Emasri, Navathe

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). | 1 Informal Design Guidelines for Relational Databases Semantics of the Relation Attributes Redundant Information in Tuples and Update Anomalies Null Values in Tuples Spurious Tuples 2 Functional Dependencies FDs Definition of FD Inference Rules for FDs Equivalence of Sets of FDs Minimal Sets of FDs 3 Normal Forms Based on Primary Keys Normalization of Relations Practical Use of Normal Forms Definitions of Keys and Attributes Participating in Keys First Normal Form Second Normal Form Third Normal Form 4 General Normal Form Definitions For Multiple Keys 5 BCNF Boyce-Codd Normal Form Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 10-3 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 10-4 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 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe lide 10 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 Copyright 2007 Ramez Elmasri and Shamkant B. Navathe Slide 10-6 1 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 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 .