What is Normalization in DBMS
Normalization
in DBMS is the systematic, step-by-step process of organizing the data in the database in such
a way that it minimizes the redundancy from a table or whole database. It is
also used to eliminate undesirable characteristics like Insertion, Update, and
Deletion Anomalies.
What is the role of Normalization in database design?
To reduce redundancy from the database or individual table.
How to achieve Normalization?
By dividing the larger table into the smaller table and linking them using
relationship.
What are the normal forms and their types?
For a table to be in certain normal form it has followed certain rules; if it does so
then the table is said to be in that particular normal form. Its types are 1st, 2nd, 3rd & Boyce-Codd Normal form.
What are anomalies and their types?
Relations that have redundant data may have problems called anomalies. They can of type: Insertion, Update, and Deletion Anomalies.
Different types of anomalies (w/o Norm.)
Insertion anomalies – When a tuple/record is tried to be
inserted in referencing relation without it being present in the referenced
table; it will cause an Insertion Anomaly.
Deletion & Update/ Modification anomalies – When a tuple is deleted or updated from
referenced relation and the referenced attribute value is used by referencing
attribute in referencing relation, it will not allow deleting the tuple from
referenced relation. To avoid such a situation: ON UPDATE/DELETE (SET
NULL/CASCADE) is used for setting referenced attribute null or update/delete
affected record/row/tuple.
To completely understand the concept of Normalization and normal forms, we need to understand what functional dependency is. So, let’s dive in:
Functional Dependency (FD) & its Types
It describes the interrelation of columns within a table; i.e. when values in one
field depend on 1 or more other fields within the same table.
For example, there’s a table/ relation to store data of people within a locality
who are eligible to get a driving license. The structure of the table is:
(name, age, eligibility<YES/NO>). It is clearly known that the field
eligibility depends on the age of the person, so “eligibility” is functionally
dependent on “age”. (age-> eligibility)
FD can be of types: Trivial (where 1 is a subset of other) and Non-trivial (when related field/s are not subsets of other). Another possible type is Transitive dependency (col1 ->col2 -> col3 indicates that if we know col1, col2 could be known, and if we know col2, col3 could be known.
Normal Forms
1st Normal Form (1 NF)
A relation is said to be in 1NF if it contains an atomic value for every attribute in a record, i.e. no attribute can be multi-valued.
Conversion Step needed: To enter multi-valued attributes in multi-rows by copying other single valued attributes.
2nd Normal Form (2 NF)
2 NF is a relation that is in 1 NF and every non-primary-key attribute is fully
functionally dependent on the primary key. This conversion involves the removal
of partial dependencies.
Conversion Step needed: Remove FD attributes from the table and place them in a new table.
3rd Normal Form (3 NF)
A relation is said to be in 3NF if it is in 2 NF and no non-primary-key attribute
is transitively dependent on the primary key. This conversion involves removal
of transitive dependencies if any.
Conversion Step needed: To remove transitive dependency. Eg:
address (Line 1,2,3, Street, City, State, PIN code) need to be made as
different table and indexed as a unique value and not included directly into
Person/ Student/ Employee table.
Boyce-Codd Normal Form (BCNF)
A relation is said to be in BCNF if it is in 3 NF and for each functional
dependency (X → Y), X should be a super Key.
Conversion Step needed: Decompose FD table further if needed.
Eg: for a dependency A → B, A cannot be a non-prime attribute, if
B is a prime attribute.
If you are interested to learn Digital marketing so kindly go to the World Stream SEO official website.
Post a Comment