Normalization

Database normalization is the process of organizing the fields and tables of a relational database to minimize redundancy and dependency.

 am going to show you one simple E-R model database.

Student Details	Course Details	Result details
1001   Ram               11/09/1986	M4       Basic Maths                       7	11/11/2004       89           A
1002   Shyam           12/08/1987	M4       Basic Maths                       7	11/11/2004       78           B
1001   Ram               23/06/1987	H6                                                    4	11/11/2004       87           A
1003   Sita                16/07/1985	C3        Basic Chemistry                 11	11/11/2004       90           A
1004   Gita               24/09/1988	B3                                                     8	11/11/2004       78           B
1002   Shyam           23/06/1988	P3        Basic Physics                     13	11/11/2004       67           C
1005   Sunita           14/09/1987	P3        Basic Physics                      13	11/11/2004       78           B
1003   Sita                23/10/1987	B4                                                      5	11/11/2004       67           C
1005   Sunita           13/03/1990	H6                                                     4	11/11/2004       56           D
1004   Gita               21/08/1987	M4      Basic Maths                         7	11/11/2004       78           B

In first look the above table is looking so arranged and well in format but if we try to find out what exactly this table is saying to us , we can easily figure out the various anomalies in this table . Ok let me help you guys in finding out the same.

1. Insert Anomaly: We cannot insert prospective course which does not have any registered student or we cannot insert student details that is yet to register for any course.
2. Update Anomaly: if we want to update the course M4’s name we need to do this operation three times. Similarly we may have to update student 1003’s name twice if it changes.
3. Delete Anomaly: if we want to delete a course M4 , in addition to M4 occurs details , other critical details of student also will be deleted. This kind of deletion is harmful to business. Moreover, M4 appears thrice in above table and needs to be deleted thrice.
4. Duplicate Data: Course M4’s data is stored thrice and student 1002’s data stored twice .This redundancy will increase as the number of course offerings increases.

A relation is in first normal form if the domain of each attribute contains only atomic values,


CustomerCustomer IDFirst NameSurnameTelephone Number123RobertIngram555-861-2025456JaneWright555-403-1659
555-776-4100789MariaFernandez555-808-9633

Customer

Customer ID	First Name	Surname	Telephone Number
123	Robert	Ingram	555-861-2025
456	Jane	Wright	555-403-1659 555-776-4100
789	Maria	Fernandez	555-808-9633

A design that is unambiguously in first normal form makes use of two tables: a Customer Name table and a Customer Telephone Number table.

Customer Name

Customer ID	First Name	Surname
123	Robert	Ingram
456	Jane	Wright
789	Maria	Fernandez

Customer Telephone Number

Customer ID	Telephone Number
123	555-861-2025
456	555-403-1659
456	555-776-4100
789	555-808-9633

There are no duplicate rows.

Neither of these tables can suffer from update anomalies.

No partial dependency exists between non-key attributes and key attributes.


Third Normal Form


all attributes that are not dependent upon the primary key must be eliminated.