What is Database Normalization? What are the way of Normalization?
In simple words, Normal forms in a database or the concept of Normalization make a Relation or Table of RDMS free from insert/update/delete anomalies and saves space by removing redundant data.
Anomalies/Problems occur if a table is not properly normalized. Three types of anomalies can occur
1. Insert anomalies
2. Update anomalies
3. Delete anomalies.
we can normalize( remove the redundant value) the table in three ways.
1. 1st Normal Form(1NF) An attribute/field/column of the table can't hold multiple values. it should hold atomic/Unique value. In student, the table holds multiple values but dividing table there is no multiple values.
2. 2nd Normal Form (2NF) will be 1st Normal Form + All non-key columns( without primary key/foreign key/composite key etc) are fully dependent on the primary key. In this table, there are no partial dependencies.2NF removes the partial dependency. In the faculty table holds a composite key means two-column primary key(teacher id, faculty code) see below figure. The main purpose of 2NF is making one primary key.
NB: Partial dependency table means when a table primary key made by two columns/attributes. when we create a primary key by two columns it's called composite key. The composite key table also called partial dependency because some non-key columns depend on one column and some non-key columns depend on another column.
Here Faculty name and Subject column depend on the faculty code key which is duplicate.
but Teacher Name, Class hour depends on teacher Id that shows partial dependency. We have to simplify the duplicate and make it unique so, the table should divide. Finally, The new faculty table has one primary key is teacher id is created.
3. 3rd Normal Form(3NF): A table said to be 3NF it must have 1NF+2NF then, it doesn't have a transitive dependency. In a table when non-primary key column depends on other no primary key columns its called transitive dependency. if the non-primary key column depends on primary key column there is no problem. see the example.
Transitive dependency: Above the table, STUD_STATE(non-primary column) determine by STUD_NO(primary key column) that is ok.
but STUD_COUNTRY(non-primary key column) determine by STUD_STATE(non-primary key column) this is not ok. STUD_COUNTRY does not determine by STUD_NO( primary key) this is the primary dependency. STUD_NO --> STUD_STATE --> STUD_COUNTRYthat we should remove in 3NF.
3. 3rd Normal Form(3NF): A table said to be 3NF it must have 1NF+2NF then, it doesn't have a transitive dependency. In a table when non-primary key column depends on other no primary key columns its called transitive dependency. if the non-primary key column depends on primary key column there is no problem. see the example.
but STUD_COUNTRY(non-primary key column) determine by STUD_STATE(non-primary key column) this is not ok. STUD_COUNTRY does not determine by STUD_NO( primary key) this is the primary dependency. STUD_NO --> STUD_STATE --> STUD_COUNTRYthat we should remove in 3NF.
