PPT: Database Design (Integrity Constraints, Normalization)

 Page 1


Basics of Functional 
Dependencies and 
Normalization for 
Relational Databases
Page 2


Basics of Functional 
Dependencies and 
Normalization for 
Relational Databases
Chapter Outline
? 1 Informal Design Guidelines for Relational Databases
? 1.1 Semantics of the Relation Attributes
? 1.2 Redundant Information in Tuples and Update Anomalies
? 1.3 Null Values in Tuples
? 1.4 Spurious Tuples
? 2 Functional Dependencies (FDs)
? 2.1 Definition of Functional Dependency
Page 3


Basics of Functional 
Dependencies and 
Normalization for 
Relational Databases
Chapter Outline
? 1 Informal Design Guidelines for Relational Databases
? 1.1 Semantics of the Relation Attributes
? 1.2 Redundant Information in Tuples and Update Anomalies
? 1.3 Null Values in Tuples
? 1.4 Spurious Tuples
? 2 Functional Dependencies (FDs)
? 2.1 Definition of Functional Dependency
Chapter Outline
? 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 Form
? 4 General Normal Form Definitions for 2NF and 3NF (For 
Multiple Candidate Keys)
? 5 BCNF (Boyce-Codd Normal Form)
? 6 Multivalued Dependency and Fourth Normal Form
Page 4


Basics of Functional 
Dependencies and 
Normalization for 
Relational Databases
Chapter Outline
? 1 Informal Design Guidelines for Relational Databases
? 1.1 Semantics of the Relation Attributes
? 1.2 Redundant Information in Tuples and Update Anomalies
? 1.3 Null Values in Tuples
? 1.4 Spurious Tuples
? 2 Functional Dependencies (FDs)
? 2.1 Definition of Functional Dependency
Chapter Outline
? 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 Form
? 4 General Normal Form Definitions for 2NF and 3NF (For 
Multiple Candidate Keys)
? 5 BCNF (Boyce-Codd Normal Form)
? 6 Multivalued Dependency and Fourth Normal Form
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?
Page 5


Basics of Functional 
Dependencies and 
Normalization for 
Relational Databases
Chapter Outline
? 1 Informal Design Guidelines for Relational Databases
? 1.1 Semantics of the Relation Attributes
? 1.2 Redundant Information in Tuples and Update Anomalies
? 1.3 Null Values in Tuples
? 1.4 Spurious Tuples
? 2 Functional Dependencies (FDs)
? 2.1 Definition of Functional Dependency
Chapter Outline
? 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 Form
? 4 General Normal Form Definitions for 2NF and 3NF (For 
Multiple Candidate Keys)
? 5 BCNF (Boyce-Codd Normal Form)
? 6 Multivalued Dependency and Fourth Normal Form
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?
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 Noferferferfewrmal Form)
? - BCNF (Boyce-Codd Normal Form)
? Additional types of dependencies, further normal forms.