Data Normalization

-

 

DATA NORMALIZATION

This is a technique of splitting a complex table in to simple meaningful tables. In here arrange tables, rows and relationships between tables in order to avoid data redundancy (අතිරික්තතාව) and to attain data integrity (සම්පූර්ණත්වය).Goal,

01.   To avid data redundancy (data duplicated).

02.   To attain data integrity - Data goes to inconsistence state (ස්ථිර නෑ, අදාළ නෑ).

03.   To put data in to correct table.

04.   To avoid CRUD anomalies (අක්‍රමිකතාව).

Duplicate වෙන data නවත්වන්න ඕනේ, අදාළ නැති data store වීම නවත්වන්න  ඕනේ.නියමිත table වලට පමණක් නියමිට data යවන්න ඕනේ සහ DB එක සමග කරන CRUD operation වලදී අක්‍රමිකතා ඇති වෙන්න බෑ (අනවශ්‍ය data delete වීම ,අනවශ්‍ය data add වීම ,data search කරගන්න බැරිවීම වගේ ඒවා)

Partial dependency

It is the dependency where non key attributes are functionally depends on any part of composite key.


Full dependency

It is the dependency where non key attributes are functionally depends on complete of composite key.


Transitive dependency

It is the dependency where a non key attribute become the determinant of any other non key attribute.

Database Normalization හෙවත් optimization කිරීම steps කීපයකින් සිදු කරනු ලබයි.

01.   1st normal form – 1NF

02.   2nd normal from – 2NF

03.   3rd normal form – 3NF

04.   4th normal form ….

නමුත් ,මුලිකවම කතා කරනුයේ 3NF දක්වා පමණක් වන අතර ඒ දක්වා normalization කිරීම බොහෝ දුරට ප්‍රමාණවත් වේ .

මෙය පහත example සමග විස්තර කරනු ලබයි.


Table one.




Table two.


Data redundancy (data duplicated)- Some columns values are repeated multiple times.a_gold,agency_n1etc..This should be avoided.

CURD anomalies.

CREATE - අලුත් agency එකක් දානවා.But එකට agent කෙනෙක් නෑ.එමනිසා agent id එකක්ද නෑ .

UPDATE - Agency name එක (gency_n1) update කරන විට මෙම name එක repeat වී ඇති නිසා repeat වී ඇති සෑම එකෙක්ම update කරන්න්ට වෙනවා.

DELETE-Agent කෙනෙක් delete කරද්දී agency සහ customer ලා විස්තරත් delete වෙනවා (ඉන්නේ එක agent කෙනෙක් නම් අවුලක් නෑ).Data goes to inconsistence state (ස්ථිර නෑ, අදාළ නෑ) – Data Integrity.

READ –

Select * from FilmAgent where customer_1=”aaa” or customer_1=”aaa” or customer_1=”aaa” - this is a problem.

Customer name එක අනුව short කරලා ගන්න ඕනෙනම් එක කරන්න බෑ customer felids 3ක් තියන නිසා.  

 

1NF

Each column should be atomic.

There should not be group of columns representing smaller information (customer_1, customer_2, customer_3).

There should be a key which uniquely identify each row I the table. It can be one column or combination of column (primary key or composite key).

According to the table one.



According to the table two.


2NF

1NF + All the non key column should be depend on primary key. මෙහිදී සියලු Transitive dependencies

ඉවත් කරයි.




3NF

2NF + all the non key columns should be none transitively depend on primary key. එනම් සෑම non key columns ක් ම primary key මත fully depend  විය යුතුයි .මෙහිදී සියලු Transitive dependencies ඉවත් කරයි. Transitive dependencies විය හැකි ආකාර දෙකකි.

01.   Determinant with single attribute.

02.   Determinant with multiple attribute.



 Determinant with single attribute



Determinant with multiple attribute.





Example for Normalization.

Employee කෙනෙක් assign කරනතුරුම new project එකක් දාන්න බෑ.Similarly, no employee cannot be added until a project is assign.

Update anomaly - When we update employee name or project title, we have to update some values in multiple places (A, Java).

Delete anomaly - When we delete an employee , project is also deleted.

 

1NF – All column values are atomic, no group of columns which represents similar information and each row have an id to uniquely identify the row.INF is Ok.

2NF-Removing Partial dependency


කලින් තිබු CRUD anomaly  දැන් OK.But Employee Table එකෙන් හෝ Project Table එකෙන් delete කරද්දී Task table වලට effect එකක් එනවා.This issue is control by integrity role.

3NF-No Transitive dependencies to remove.3NF is OK.

 

 

In Short,

There are main 3 styles in data table normalization (1NF,2NF,3NF).

1NF 

·        All column values should be atomic.

·        Removing group of columns which represents similar information.

·        Each row should have an id to uniquely identify the row.

 2NF

·        Removing Partial dependency.

3NF

·        Removing Transitive dependency.

 

Comments

Post a Comment

Popular posts from this blog

02. Spring – Creating spring project clone it with GIT step by step.

-
Image
  Creating spring project clone it with GIT step by step. 01. Create an empty GIT repository and select a repository name. 02.   Check to add README.md and technology to add it inside the gitignire file0- it will include all the generated class when we complete the application. 03.      Copy the github repository URL. 04 . Open eclipse and go to,             Window – Show View – Other - Type git on the folder and select git repositories. 05.   Now clone this repository at your desired location in which we are going to keep the project specific file . a)         a)  Step One.             b)  Step  Two .          c)    Step Three. Both projects are generated as bellows           d) Step Four  Open the gitingnore file and ignore the target folder of the project. All the class files which are generate with compile the code are store in target folder. Class file can be generated. Because of that we don’t commit that folder to git.           e) Step Five Open window – preference – filter fo
Read more

02.What is MicroService?

-
Image
What is MicroService? MicroService යනු තනි application එකක් (Monolithic Application එකක්), එකිනෙක communicate කරගන පුළුවන් වෙන වෙනම deploy වන කුඩා කුඩා independent services වලට කැඩීමයි. Simply it is new way to develop application rather than traditional monolithic application. Companies which are using microservice.   Features of MicroService Architecture Small focused – main aim of microservice is simplicity.it can be rewrite in maintained without extra effort. Loosely coupled – development as well as deployment is quick. Language Neutral – few services can be Java, few is c# few can be python.it can be use depend on the service. Bounded Context – each microservice does not need to understand the implementation of the other microservice.
Read more

06.Mongo DB - Query part 2 (Aggregation)

-
Image
    Project can be download from         https://github.com/LahiruPriyankara/SpringBootAppTwo-MongoDB MongoDB Aggregation This groups values from multiple documents together and can perform variety of operations on that grouped data.it is going to return single result after the aggregation. Stages In Aggregation: Those operation are used in group of documents ( Used inside aggregation stages).  Unary Operation -  Unary operator perform operation for each documents. Type - Given the type of the filed. In the SQL we query select count and in the bracket Astrix this count for aggregation. It return the number of rows. This is same in mongoDB. db . employee . aggregate ([ { $group : { _id : "$gender" , genderCount : { $sum : 1 } } } ]) // $sum: 1 :1 - true db . employee . aggregate ([ { $group : { _id : "$gender" , maxAge : { $max : "$age" } } } ]) Bellow example is from  https://docs.mongodb.com/manual/aggregation/ Single Purpose Aggregation
Read more