Database Normalization:
Before we start DDL or DML operations we need to follow the certain rules. So we go for Normalization.
Purpose of Normalization:
The main purpose of normalization is to avoid redundancy and to improve the performance known as Scalability.
By using the different Normal Forms to follow the rules for creating a table to improve the scalability and it’s helpful for good design. There are six levels of normal form. They are,
First Normal Form: (1NF) [No Repeating Elements or Groups of Elements]
In first normal form, the rows cannot redundant to each other (the same Group of record is not repeating).
Simple Example:
Create a Table with Composite Primary Key for Two Columns:
create table customer(customer_id int not null, customer_name varchar(20),customer_address varchar(20),Product_id int not null, Product_name varchar(20),qty int,price int,total int,primary key(customer_id, product_id))
Procedure for insertion and calculate the Total Amount:
CREATE PROCEDURE sp_value_insert_into_customer (
@customer_id INT
,@customer_name VARCHAR(20)
,@customer_address VARCHAR(20)
,@Product_id INT
,@Product_name VARCHAR(20)
,@qty INT
,@price INT
)
AS
BEGIN
INSERT INTO dbo.customer (
customer_id
,customer_name
,customer_address
,Product_id
,Product_name
,qty
,price
,total
)
VALUES (
@customer_id
,@customer_name
,@customer_address
,@Product_id
,@Product_name
,@qty
,@price
,@qty * @price
);
END
EXEC dbo.sp_value_insert_into_customer 101
,’Mohan’
,’TamilNadu’
,1001
,’Bat’
,5
,200;
EXEC dbo.sp_value_insert_into_customer 101
,’Mohan’
,’TamilNadu’
,1002
,’Ball’
,10
,20;
EXEC dbo.sp_value_insert_into_customer 102
,’Sundaram’
,’TamilNadu’
,1001
,’Bat’
,2
,200;
EXEC dbo.sp_value_insert_into_customer 103
,’Karthik’
,’TamilNadu’
,1003
,’Stumps’
,2
,100;
EXEC dbo.sp_value_insert_into_customer 103
,’Karthik’
,’TamilNadu’
,1004
,’Mat’
,1
,1000;
Output:
First Normalization
In the above output cannot have redundancy because we gave composite primary key, it will check the two primary Key columns.
101 1001
101 1002. Both values are not same. So it satisfies First Normal form.
Second Normal Form: (2NF) [No Partial Dependency]
In second Normal form, it satisfies 1NF and to remove the unrelated column (i.e. The non-key fields which are depending upon the existing primary key).
In the above Result cannot satisfied Second Normal Form because Product_name, price, total, qty have no relationship with customer information as customer_id, customer_name, and customer_address. Here product information is also dependent upon the existing primary key as customer_id not depending upon the product_id. So we can split the table to satisfy the second Normal Form as,
Example:
Customer Table:
create table customer_info(customer_id int not null primary key, customer_name varchar(20),customer_address varchar(20))
ProductTable:
create table product_info(customer_id int not null references customer_info(customer_id),Product_id int not null ,Product_name varchar(20),Qty int,price int,total int,primary key(customer_id,product_id))
Procedure For Inserting Values in customerTable:
create PROCEDURE sp_value_insert_into_customer_info (
@customer_id INT
,@customer_name VARCHAR(20)
,@customer_address VARCHAR(20)
)
AS
BEGIN
INSERT INTO dbo.customer_info (
customer_id
,customer_name
,customer_address
)
VALUES (
@customer_id
,@customer_name
,@customer_address
);
END
EXEC dbo.sp_value_insert_into_customer_info 101
,’Mohan’
,’TamilNadu’
EXEC dbo.sp_value_insert_into_customer_info 102
,’Sundaram’
,’TamilNadu’
EXEC dbo.sp_value_insert_into_customer_info 103
,’Karthik’
,’TamilNadu’
Procedure for Insert values into ProductTable:
create PROCEDURE sp_value_insert_into_product_info (
@Product_id INT
,@Product_name VARCHAR(20)
,@price INT
)
AS
BEGIN
INSERT INTO dbo.product_info (
Product_id
,Product_name
,price
)
VALUES (
@Product_id
,@Product_name
,@price
);
END
EXEC dbo.sp_value_insert_into_product_info 1001
,’Bat’
,200;
EXEC dbo.sp_value_insert_into_product_info 1002
,’Ball’
,20;
EXEC dbo.sp_value_insert_into_product_info 1003
,’Stumps’
,100;
EXEC dbo.sp_value_insert_into_product_info 1004
,’Mat’
,1000;
Output:
Second Normalization
The above result satisfies the 1NF and Relationship between the tables and also non-key fields depending upon own primary key.
Third Normal Form (3NF): [No Dependency on non-key attributes]
In third normal form, there is no dependency on non-key attributes (i.e. Every non- key field are depending upon the primary key column in the table). It’s a way to improve the performance and know the relationship between the tables.
Example:
create table customer_info(customer_id int not null primary key, customer_name varchar(20),customer_address varchar(20))
create table product_info(Product_id int not null primary key,Product_name varchar(20),price int)
create table purchase_details(customer_id int not null references customer_info(customer_id),product_id int not null references product_info(product_id),Qty int,Total int,primary key(customer_id,product_id))
Procedure for insert the value:
CREATE PROCEDURE sp_purchaseDetails_insert (
@customer_id INT
,@product_id INT
,@Qty INT
)
AS
BEGIN
INSERT INTO purchase_details (
customer_id
,product_id
,Qty
,Total
)
VALUES (
@customer_id
,@product_id
,@Qty
,@Qty * (
SELECT price
FROM product_info
WHERE product_id = @product_id
)
);
END
EXEC sp_purchaseDetails_insert 101
,1001
,2
EXEC sp_purchaseDetails_insert 101
,1002
,3
EXEC sp_purchaseDetails_insert 102
,1004
,2
EXEC sp_purchaseDetails_insert 103
,1003
,1
Output:
Third Normalization
In the above result to satisfy the 1st, 2nd, 3rd normal form. No dependencies in the table every non-key column in a table is depending upon the primary key column.
Code Mustangs 