LECTURE 3_NORMALIZATION.pdf

Department of Computer Science & Engineering UNIVERSITY OF MINES AND TECHNOLOGY BY: DR ERIC AFFUM  Read the following materials CHAPTER 6 Coronel, C. and Morris, S. (2018), Database Systems: Design, Implementation, & Management, Cengage Learning, Boston, U.S.A., 13th Edit...

Department of Computer Science & Engineering UNIVERSITY OF MINES AND TECHNOLOGY BY: DR ERIC AFFUM  Read the following materials CHAPTER 6 Coronel, C. and Morris, S. (2018), Database Systems: Design, Implementation, & Management, Cengage Learning, Boston, U.S.A., 13th Edition, 816 pp UNIVERSITY OF MINES AND TECHNOLOGY Database Normalization ۞Normalization - It is the process of removing redundant data from tables in a given database in order to improve storage efficiency, data integrity. UNIVERSITY OF MINES AND TECHNOLOGY Database Normalization There are currently five normal forms that have been defined and these are: ۞First Normal Form(1NF) ۞Second Normal Form(2NF) ۞Third Normal Form(3NF) ۞Boyce-Codd Normal Form(4NF) ۞Domain-key Normal Form (5NF) UNIVERSITY OF MINES AND TECHNOLOGY 1NF ۞Eliminate duplicative columns from the same table. ۞Every table should have a primary key OR ۞A relation R is in first normal form (1NF) if and only if all underlying domains contain atomic values only UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form How to solve this? UNIVERSITY OF MINES AND TECHNOLOGY Four basic rules to achieve 1st normal form How to solve this? UNIVERSITY OF MINES AND TECHNOLOGY 2NF ۞Meet all the requirements of the first normal form. ۞A relation R is in second normal form (2NF) if and only if it is in 1NF and every non-key attribute is fully dependent on the primary key 2NF UNIVERSITY OF MINES AND TECHNOLOGY 2NF UNIVERSITY OF MINES AND TECHNOLOGY 2NF UNIVERSITY OF MINES AND TECHNOLOGY  This is partial dependency 2NF UNIVERSITY OF MINES AND TECHNOLOGY  2NF UNIVERSITY OF MINES AND TECHNOLOGY Move teacher column to Subject table UNIVERSITY OF MINES AND TECHNOLOGY 3NF There are two basic requirements for a database to be in third normal form: ۞Already meet the requirements of both 1NF and 2NF ۞Remove columns that are not fully dependent upon the primary key 3NF UNIVERSITY OF MINES AND TECHNOLOGY 3NF UNIVERSITY OF MINES AND TECHNOLOGY  In 3NF 3NF UNIVERSITY OF MINES AND TECHNOLOGY Department of Computer Science & Engineering Thanks