Normalization Third Normal Form Definition Transitive functional dependency

  • Slides: 11
Download presentation
Normalization

Normalization

Third Normal Form • Definition – Transitive functional dependency – if there a set

Third Normal Form • Definition – Transitive functional dependency – if there a set of attribute Z that are neither a primary or candidate key and both X Z and Y Z holds. • By transitive functional dependency, we mean we have the following relationships in the table: A is functionally dependent on B, and B is functionally dependent on C. In this case, C is transitively dependent on A via B.

• Consider the following example:

• Consider the following example:

Example • In the table above, [Book ID] determines [Genre ID], and [Genre ID]

Example • In the table above, [Book ID] determines [Genre ID], and [Genre ID] determines [Genre Type]. Therefore, [Book ID] determines [Genre Type] via [Genre ID] and we have transitive functional dependency.

3 rd Normal Form • 3 rd Normal Form Definition • A database is

3 rd Normal Form • 3 rd Normal Form Definition • A database is in third normal form if it satisfies the following conditions: • It is in second normal form • There is no transitive functional dependency

• To bring this table to third normal form, we split the table

• To bring this table to third normal form, we split the table into two as follows:

• Now all non-key attributes are fully functional dependent only on the primary

• Now all non-key attributes are fully functional dependent only on the primary key. • In [TABLE_BOOK], both [Genre ID] and [Price] are only dependent on [Book ID]. • In [TABLE_GENRE], [Genre Type] is only dependent on [Genre ID].

BCNF • • Boyce- Codd Normal form A relation is said to be in

BCNF • • Boyce- Codd Normal form A relation is said to be in BCNF 1) If it is in 3 NF 2) No dependency of an attribute of a multi attribute key on an attribute of another multi attribute key.

BCNF should have • It should have multiple candidate keys i. e, more than

BCNF should have • It should have multiple candidate keys i. e, more than one primary key. • Composite candidate keys. • No dependency of an attribute of a multi attribute key on an attribute of another attribute key i. e, overlapping.

Example Teacher_ID Department HOD Percent_time 100 Computers Vinod Kamboj 50 200 Maths S. S

Example Teacher_ID Department HOD Percent_time 100 Computers Vinod Kamboj 50 200 Maths S. S Sandhu 60 200 PHYSICS S. Prinja 40 300 History Armaan Kamboj 30 In the above table there are two composite keys 1) Teacher_ID, Department 2) Teacher_ID, HOD The above relation is in 3 NF but not in BCNF because Department and HOD which are the part of composite keys are functionally dependant.

• In order to normalize the relation into BCNF we have to create

• In order to normalize the relation into BCNF we have to create a new relation from old relation by breaking it. Teacher_ID 100 200 Department Computers Maths PHYSICS Percent_time 50 60 40 300 History 30 Department Computers Maths PHYSICS HOD Vinod Kamboj S. S Sandhu S. Prinja History Armaan Kamboj

Normalization Normalization Functional Dependency FDs Functional Dependency FirstNormalization Normalization Functional Dependency FDs Functional Dependency First
Transitive and Intransitive Verbs Transitive Verb A transitiveTransitive and Intransitive Verbs Transitive Verb A transitive
First Normal Form Second Normal Form Third NormalFirst Normal Form Second Normal Form Third Normal
Third Normal Form 3 NF Third Normal FormThird Normal Form 3 NF Third Normal Form
Functional Dependency Normalization 12142021 DB FD Normalization AhmedFunctional Dependency Normalization 12142021 DB FD Normalization Ahmed
Database Management System Normalization 12302021 Functional Dependency NormalizationDatabase Management System Normalization 12302021 Functional Dependency Normalization
Functional Dependencies and Normalization Normalization n n NormalizationFunctional Dependencies and Normalization Normalization n n Normalization
Functional Dependency and Decomposition INTRODUCTION FUNCTIONAL DEPENDENCY FDFunctional Dependency and Decomposition INTRODUCTION FUNCTIONAL DEPENDENCY FD
Normal Forms Chomsky Normal Form Griebach Normal FormNormal Forms Chomsky Normal Form Griebach Normal Form
Normalization Example Database Tables and Normalization Normalization ProcessNormalization Example Database Tables and Normalization Normalization Process