|
The accelerating growth of the Indian economy has allowed numerous Startups in the market. Most of these startups have gained good recognition from consumers across the country. Thus, India is the world's third-largest startup ecosystem after the US and China. In the last decade, millions of startups have been developed globally. Every year, 305 million startups are created across the globe among the best startups survive. Tech startups are the most emerging startups in India today. Among the top 100 unicorns globally, six Indian startups took the top position. They became the top startups in India today with the highest value and good growth.
Normalization in DBMS or databases is a technique to organize data perfectly in a database and reduce data redundancy. It also helps to increase data integrity with the database table. Further, database normalization reduces unwanted features such as INSERT, UPDATE, and DELETE exceptions. Moreover, this method divides large tables into multiple small tables and connects them using several relationships. However, the use of Normalization in DBMS reduces the data repetition and ensures the data is logically stored correctly.
The theory of normalization in DBMS was proposed by Edgar Codd, who introduced the first normal form. Later, several normal forms, like second, third, fourth, etc., were introduced in this regard.
Normalization is required because repetitive data reduces disk space and it results in maintenance issues. If there is already data in different locations, it needs to be altered and changed as required.
Wish to make a career in the world of SQL Server DBA? Then Start with HKR'S SQL Server DBA Training Course!
Now, let us know the various normal forms in the database.
Database normalization or Normalization in DBMS is a technique of organizing data within a relational database to minimize data duplication. It also includes the application of several rules, which are normalization forms, and they ensure data integrity and help to optimize DBMS performance. There are five database normal forms, also known as "normal forms".
The following are the different types of database normalization forms.
In the first normal form or 1NF, each column in a table includes only atomic values, which cannot be further divided. It removes the repeating groups and ensures that each column has only one value. In addition, each row in the table should be unique, and each column in the table should contain a unique name.
Example of 1NF
Customer Table: This is a common table where we maintain customer information.
| Customer ID | Name | Email |
------------------------------------------------
| 1 | John | [email protected] |
| 2 | Kumar | [email protected] |
| 3 | Bob | [email protected] |
Here is the conversion into 1NF, where it contains only atomic values.
Customer Table:
| Customer ID | Name|
---------------------------------
| 1 | John|
| 2 | Alice|
| 3 | Bob |
Email Table:
| Customer ID | Email |
-----------------------------
| 1 | [email protected] |
| 2 | [email protected]|
| 3 | [email protected] |
The second normal form extends the first normal form (1NF). The table must be in 1NF, and all non-key attributes to be entirely reliant on the primary key. In another way, each non-key property must practically rely on the complete primary key rather than just a subset of it. The following is an example of 2NF:
Here we have two tables: Course Table and Instructor Table.
Course Table:
| Course ID | Course Name | Instructor ID |
-------------------------------------------------------------------
| 1 | Math | 101 |
| 2 | Physics | 102 |
| 3 | English | 101 |
Instructor Table:
-------------------------------------------------
| Instructor ID | Instructor Name |
-------------------------------------------------
| 101 | John Smith |
| 102 | Alice Johnson |
-------------------------------
Now, to get 2NF results, we will split the above table as follows:
Course Table:
---------------------------------------------
| Course ID | Course Name |
----------------------------------------------
| 1 | Math |
| 2 | Physics |
| 3 | English |
-----------------------------------------------
Instructor Table:
--------------------------------------------
| Instructor ID | Course ID |
---------------------------------------------
| 101 | 1 |
| 102 | 2 |
| 101 | 3 |
---------------------------------------------
Instructor Name Table:
----------------------------------------------------
| Instructor ID | Instructor Name |
-----------------------------------------------------
| 101 | John Smith |
| 102 | Alice Johnson |
-------------------------------------------------------
Lets's get started with SQL Server DBA Tutorial online!
The third normal form extends the second normal form (2NF) in the database normalization. It demands that the table is in 2NF with no transitive dependencies. When a non-key attribute is dependent on another non-key attribute dependent on the main key, this is referred to as a transitive dependency. Such dependencies must be removed to achieve 3NF by developing separate tables.
Example
The below data contains students and course enrollments in various departments.
Enrollment Table:
-----------------------------------------------------------------
| Student ID | Course ID | Department |
-------------------------------------------------------------------
| 1 | 1 | Math |
| 1 | 2 | Physics |
| 2 | 1 | Math |
| 2 | 3 | English |
------------------------------------------------------------------
Now to achieve 3NF, we will split the table into two sections.
Enrollment Table:
------------------------------------
| Student ID | Course ID |
------------------------------------
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
--------------------------------------
Course Table:
--------------------------------------
| Course ID | Department |
---------------------------------------
| 1 | Math |
| 2 | Physics |
| 3 | English |
The fourth normal extends the third normal form (3NF) in the database normalization. The table must be in 3NF, and it lacks multivalued dependencies. When a non-key attribute depends on a combination of values from the main key but not on the individual values itself, this is referred to as a multi-valued dependency. Such dependencies should be avoided by constructing separate tables to achieve 4NF.
Example:
Here the table Book contains the data book title and its authors.
Book Table:
----------------------------------------------
| Book ID | Book Title | Authors |
------------------------------------------------
| 1 | Book A | John |
| 2 | Book B | John, Bob|
| 3 | Book C | Bob, Alice|
--------------------------------------------------
Now to get the results of 4NF, we will divide the table.
Book Table:
| Book ID | Book Title |
----------------------
| 1 | Book A |
| 2 | Book B |
| 3 | Book C |
----------------------
Author Table:
-------------------------------------
| Book ID | Author Name |
--------------------------------------
| 1 | John |
| 2 | John |
| 2 | Bob |
| 3 | Bob |
| 3 | Alice |
---------------------------------
The fifth normal form builds upon the 4NF in the normalization forms.
It demands that the table is in 4NF and free of join dependencies. When a table may be dissected into many tables without losing any information, this is referred to as a join dependency. Such dependencies should be avoided to achieve 5NF by developing separate tables.
Example:
Employee Project Table:
------------------------------------------------------------------------------------------------------------
| Employee ID | Employee Name | Project ID | Project Name |
-------------------------------------------------------------------------------------------------------------
| 1 | John | 1 | Project A |
| 1 | John | 2 | Project B |
| 2 | Alice | 1 | Project A |
| 2 | Alice | 3 | Project C |
--------------------------------------------------------------------------------------------------------------------------
Now, to get the results of 5NF, we will divide the table as follows.
Employee Table:
--------------------------------------------------
| Employee ID | Employee Name |
-----------------------------------------------------
| 1 | John |
| 2 | Alice |
-------------------------------------------------------
Project Table:
----------------------------------------------
| Project ID | Project Name |
-----------------------------------------------
| 1 | Project A |
| 2 | Project B |
| 3 | Project C |
-----------------------------------------------
Employee Project Table:
--------------------------------------------
| Employee ID | Project ID |
----------------------------------------------
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 2 | 3 |
---------------------------------------------------
Advantages:
Disadvantages:
Conclusion
Thus, DBMS normalization helps reduce data redundancy and organizes the data in the database. It also promotes data integrity by removing anomalies in the database. Moreover, the normalization process can improve business performance and group analysis. Also, it provides a higher level of data security and improves database design. Therefore, it has many uses. Stay tuned for updates on databases in this space.
Batch starts on 28th Sep 2023, Weekday batch
Batch starts on 2nd Oct 2023, Weekday batch
Batch starts on 6th Oct 2023, Fast Track batch
.
©2023 HKR Trainings. All rights Reserved.
On Our Website all Courses, Technologies, logos, and certification titles we use are their respective owners' property, Trademarks & their intellectual Property belong to them. All the firm, service, or product names on our website are solely for identification purposes. We do not own, endorse or have the copyright of any brand/logo/name in any manner. Few graphics on our website are freely available on public domains. we use all these just for the purpose of training only.
