Skip to main content

Normalization in DBMS

Normalization

A large database defined as a single relation may result in data duplication. This repetition of data may result in:
  • Making relations very large.
  • It isn't easy to maintain and update data as it would involve searching many records in relation.
  • Wastage and poor utilization of disk space and resources.
  • The likelihood of errors and inconsistencies increases.

So to handle these problems, we should analyze and decompose the relations with redundant data into smaller, simpler, and well-structured relations that are satisfy desirable properties. Normalization is a process of decomposing the relations into relations with fewer attributes.

What is Normalization?
  • Normalization is the process of organizing the data in the database.
  • Normalization is used to minimize the redundancy from a relation or set of relations. It is also used to eliminate undesirable characteristics like Insertion, Update, and Deletion Anomalies.
  • Normalization divides the larger table into smaller and links them using relationships.
  • The normal form is used to reduce redundancy from the database table.

Why do we need Normalization?

The main reason for normalizing the relations is removing these anomalies. Failure to eliminate anomalies leads to data redundancy and can cause data integrity and other problems as the database grows. Normalization consists of a series of guidelines that helps to guide you in creating a good database structure.

Data modification anomalies can be categorized into three types:
  • Insertion Anomaly: Insertion Anomaly refers to when one cannot insert a new tuple into a relationship due to lack of data.
  • Deletion Anomaly: The delete anomaly refers to the situation where the deletion of data results in the unintended loss of some other important data.
  • Updation Anomaly: The update anomaly is when an update of a single data value requires multiple rows of data to be updated.

Types of Normal Forms:

Normalization works through a series of stages called Normal forms. The normal forms apply to individual relations. The relation is said to be in particular normal form if it satisfies constraints.

Following are the various types of Normal forms:

Normal Form ->  Description

1NF: A relation is in 1NF if it contains an atomic value.
2NF: A relation will be in 2NF if it is in 1NF and all non-key attributes are fully functional and dependent on the primary key.
3NF: A relation will be in 3NF if it is in 2NF and no transition dependency exists.
BCNF: A stronger definition of 3NF is known as Boyce Codd's normal form.
4NF: A relation will be in 4NF if it is in Boyce Codd's normal form and has no multi-valued dependency.
5NF: A relation is in 5NF. If it is in 4NF and does not contain any join dependency, joining should be lossless.

Advantages of Normalization
  • Normalization helps to minimize data redundancy.
  • Greater overall database organization.
  • Data consistency within the database.
  • Much more flexible database design.
  • Enforces the concept of relational integrity.

Disadvantages of Normalization
  • You cannot start building the database before knowing what the user needs.
  • The performance degrades when normalizing the relations to higher normal forms, i.e., 4NF, 5NF.
  • It is very time-consuming and difficult to normalize relations of a higher degree.
  • Careless decomposition may lead to a bad database design, leading to serious problems.
Labels:

Comments

Post a Comment

Please do not enter any spam link in the comment box.

Popular posts from this blog

Languages in DBMS

Languages in DBMS Structured Query Language(SQL) as we all know is the database language by the use of which we can perform certain operations on the existing database and also we can use this language to create a database. SQL uses certain commands like Create, Drop, Insert, etc. to carry out the required tasks. These SQL commands are mainly categorized into five categories as: DDL – Data Definition Language DQL – Data Query Language DML – Data Manipulation Language DCL – Data Control Language TCL – Transaction Control Language Now, we will see all of these in detail. DDL (Data Definition Language): DDL or Data Definition Language actually consists of the SQL commands that can be used to define the database schema. It simply deals with descriptions of the database schema and is used to create and modify the structure of database objects in the database. DDL is a set of SQL commands used to create, modify, and delete database structures but not data. These commands are normally ...
Post a Comment
Read more

Indexing in DBMS

Indexing in DBMS Indexing is used to optimize the performance of a database by minimizing the number of disk accesses required when a query is processed.  The index is a type of data structure. It is used to locate and access the data in a database table quickly. Index structure: Indexes can be created using some database columns. The first column of the database is the search key that contains a copy of the primary key or candidate key of the table. The values of the primary key are stored in sorted order so that the corresponding data can be accessed easily.  The second column of the database is the data reference. It contains a set of pointers holding the address of the disk block where the value of the particular key can be found. Indexing Methods Ordered indices The indices are usually sorted to make searching faster. The indices which are sorted are known as ordered indices. Example: Suppose we have an employee table with thousands of record and each of which is 10 byte...
Post a Comment
Read more

SQL Injection

SQL Injection The SQL Injection is a code penetration technique that might cause loss to our database. It is one of the most practiced web hacking techniques to place malicious code in SQL statements, via webpage input. SQL injection can be used to manipulate the application's web server by malicious users. SQL injection generally occurs when we ask a user to input their username/userID. Instead of a name or ID, the user gives us an SQL statement that we will unknowingly run on our database. For Example - we create a SELECT statement by adding a variable "demoUserID" to select a string. The variable will be fetched from user input (getRequestString). demoUserI = getrequestString("UserId"); demoSQL = "SELECT * FROM users WHERE UserId =" +demoUserId; Types of SQL injection attacks SQL injections can do more harm other than passing the login algorithms. Some of the SQL injection attacks include: Updating, deleting, and inserting the data: An attack can mo...
Post a Comment
Read more