DBMS Normalization
-
Process of organizating data in the database.
-
Used to minimize the redundancy from the relation or set of relation.
-
Divides the larger table into smaller table and links them using relationship.
-
Normal Form is used to reduce the redundancy from the database table.
Some Terminology:
1. Partial Dependency
- Partial Dependency occurs when a non-prime attribute is functionally dependent on part of a candidate key.
- In other words, A functional dependency X->Y is a partial dependency, if Y is functionally dependent on X, and Y can be determined by any proper subset of X.
2. Transitive Dependency
- When an indirect relationship causes functional dependency, it is called transitive dependency.
- Example: P -> Q and Q -> R then P -> R is a transitive dependency.
Types of Normal Form:
1NF
- A relation is in 1NF if it contains an atomic value.
Example:
Employee Table:
-----------------------------------------------------------
EMP_ID EMP_NAME EMP_PHONE EMP_STATE
-----------------------------------------------------------
14 John 7272826385,9064738238 UP
20 Harry 8574783832 Bihar
12 Sam 7390372389,8589830302 Punjab
-----------------------------------------------------------
The decomposition of the EMPLOYEE table into 1NF has been shown below:
-----------------------------------------------------------
EMP_ID EMP_NAME EMP_PHONE EMP_STATE
-----------------------------------------------------------
14 John 7272826385 UP
14 John 9064738238 UP
20 Harry 8574783832 Bihar
12 Sam 7390372389 Punjab
12 Sam 8589830302 Punjab
-----------------------------------------------------------
2NF
- A relation will be in 2NF if it is in 1Nf and all the non-key attributes are fully functional dependent on the primary key i.e it has no partial dependency.
Example: Let's assume, a school can store the data of teachers and the subjects they teach. In a school, a teacher can teach more than one subject.
TEACHER table
-----------------------------------------
TEACHER_ID SUBJECT TEACHER_AGE
-----------------------------------------
25 Chemistry 30
25 Biology 30
47 English 35
83 Math 38
83 Computer 38
-----------------------------------------
-
In the given table, non-prime attribute TEACHER_AGE is dependent on TEACHER_ID which is a proper subset of a candidate key. That's why it violates the rule for 2NF.
-
To convert the given table into 2NF, we decompose it into two tables:
TEACHER_DETAIL table:
------------------------
TEACHER_ID TEACHER_AGE
------------------------
25 30
47 35
83 38
-----------------------
TEACHER_SUBJECT table:
-----------------------
TEACHER_ID SUBJECT
-----------------------
25 Chemistry
25 Biology
47 English
83 Math
83 Computer
-----------------------
3NF
- A relation will be in 3NF if it is in 2NF and it deoesn't contain any transitive dependency for non-prime attributes.
- 3NF is used to reduce the data duplication.
Boyce Codd Normal Form (BCNF)
- A relation is in BCNF if it is in 3NF and for every functional dependency X -> Y, X must be the super key of the table.