Oracle Normalization Quick References Guide for 1NF,2NF,3NF

Oracle Normalization Quick References Guide for 1NF 2NF 3NF NORMALIZATION is a database design technique that organizes tables in a manner that reduces redundancy and dependency of data. Normalization divides larger tables into smaller tables and links them using relationships. The purpose of Normalization is to eliminate redundant (useless) data and ensure data is stored logically.

Some Key Points For Database Normalization::
Problems of redundancy –
inconsistency :(when the same type of data have different value and different location).
Wastage of disk space
DML Queries becomes very slow.
1.Complexity to the developers (lots of extra coding for n-way table joins)
2.Run-time overhead (complex SQL pre-processing by the CBO)

Notes::
Some Oracle databases were modeled according to the rules of normalization that were intended to eliminate redundancy. However, a pure third-normal form (3NF) database can cause high overhead on the Oracle database especially in databases that were designed when disk cost was $250,000 per gigabyte.

If we introduce redundancy to reduce joins, we can retrieve high-volume queries with far less runtime overhead.
I’ve seen databases where a single query form (show me all items for an order) was 75% of system traffic.

Normalization & Object tables – Oracle has nested tables and varray table columns whereby repeating groups are stored within a row, violating 1NF.

Denormalization & Materialized Views – Tables are pre-joined together, queries are re-written to access the MV, and a method (Oracle snapshots) keeps the denormalization in-sync with the normalized representation of the data.

Oracle Normalization Quick References Guide for 1NF 2NF 3NF

Normalization Forms ::

Lets’s See One Example :: First Normal Form Example

i.Every row should contain a value and every record needs to be unique in our database. ii.Data in each column should be atomic i.e. there is no multiple values separated with comma. iii.That tables doesn’t have repeating column groups. iv.Identify a record uniquely by using a key.

After First Normal Form ::

Second Normal Form (2 NF) ::

i.It should follow the first normal form. ii.It should have single column primary key also non-key attribute are fully functionally dependent on total candidate key. Note :: Generally if a table is in 1NF and also that table having any partial non-key attributes then that table not in 2NF. Process :: Identify partial non-key attribute which depends on partial key attributes then those attributes put into separate table.

Functional Dependency :: If any given two tuples(columns) in a relation then x(can attribute set) agree then y(another attributes) then x -> y is called FD. (Determinant)x   –>  Y (Dependent) Here, Y is functional dependent on X or X, is functional determinant on Y.

Eg ::   A -> B B can take only one value for a given value of A then only B is functionally dependent on A or A is functionally determinant on B.

After Second Normal Form ::

Third Normal Form :: If a table is in 2NF and also non-key attributes are only dependent on primary key.

Process :: Identify non-key attributes which depends on another non-key attributes are put into separate table. By default this is master table .

Note ::  In database design if any non-key attribute which depends on any non-key attributes then it is called transitive dependency. In third normal form processed table there is no transitive dependency.

After third Normal Form ::

Share this post: on Twitter on Facebook on Google+