Normal Forms information ( Normalization )

 Normalization

Normalization or data normalization is a process to organize the data into tabular format (database tables). A good database design includes the normalization, without normalization a database system may slow, inefficient and might not produce the expected result. Normalization reduces the data redundancy and inconsistent data dependency.

Normal Forms

We organize the data into database tables by using normal forms rules or conditions. Normal forms help us to make a good database design. Generally we organize the data up to third normal form. We rarely use the fourth and fifth normal form.

To understand normal forms consider the folowing unnormalized database table. Now we will normalize the data of below table using normal forms.

1.   First Normal Form (1NF)

A database table is said to be in 1NF if it contains no repeating fields/columns. The process of converting the UNF table into 1NF is as follows:

1.Separate the repeating fields into new database tables along with the key from unnormalized database table.

2.  The primary key of new database tables may be a composite key1NF of above UNF table is as follows:

2.                 Second Normal Form (2NF)

A database table is said to be in 2NF if it is in 1NF and contains only those fields/columns that are functionally dependent(means the value of field is determined by the value of another field(s)) on the primary key. In 2NF we remove the partial dependencies of any non-key field.

The process of converting the database table into 2NF is as follows:

1.     Remove the partial dependencies(A type of functional dependency where a field is only functionally dependent on the part of primary key) of any non-key field.

2.     If field B depends on field A and vice versa. Also for a given value of B, we have only one possible value of A and vice versa, Then we put the field B in to new database table where B will be primary key and also marked as foreign key in parent table.

2NF of above 1NF tables is as follows:

 

3.                 Third Normal Form (3NF)

A database table is said to be in 3NF if it is in 2NF and all non keys fields should be dependent on primary key or We can also said a table to be in 3NF if it is in 2NF and no fields of the table is transitively functionally dependent on the primary key.The process of converting the table into 3NF is as follows:

1.     Remove the transitive dependecies(A type of functional dependency where a field is functionally dependent on the Field that is not the primary key.Hence its value is determined, indirectly by the primary key )

2.     Make separate table for transitive dependent Field.

3NF of above 2NF tables is as follows:

4.                 Boyce Code Normal Form (BCNF)

A database table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.The process of converting the table into BCNF is as follows:

1.     Remove the non trival functional dependency.

2.     Make separate table for the determinants.

BCNF of below table is as follows:

5.                 Fourth Normal Form (4NF)

A database table is said to be in 4NF if it is in BCNF and primary key has one-to-one relationship to all non keys fields or We can also said a table to be in 4NF if it is in BCNF and contains no multi-valued dependencies.The process of converting the table into 4NF is as follows:

1.     Remove the multivalued dependency.

2.     Make separate table for multivalued Fields.

4NF of below table is as follows:

6.                 Fifth Normal Form (5NF)

A database table is said to be in 5NF if it is in 4NF and contains no redundant values or We can also said a table to be in 5NF if it is in 4NF and contains no join dependencies.The process of converting the table into 5NF is as follows:

1.     Remove the join dependency.

2.     Break the database table into smaller and smaller tables to remove all data redundancy.

5NF of below table is as follows:

 

Remove duplicate records from a table in SQL Server

Suppose we have below Employee table in SQL Server.

1.   CREATE TABLE dbo.Employee

2.  ( 

3.  EmpID int IDENTITY(1,1) NOT NULL, 

4.  Name varchar(55) NULL, 

5.  Salary decimal(10, 2) NULL, 

6.  Designation varchar(20) NULL

7.   ) 

The data in this table is as shown below:

Remove Duplicate Records by using ROW_NUMBER()

1.   WITH TempEmp (Name,duplicateRecCount)

2.  AS

3.  (

4.  SELECT Name,ROW_NUMBER() OVER(PARTITION by Name, Salary ORDER BY Name) 

5.  AS duplicateRecCount

6.  FROM dbo.Employee

7.  )

8.  --Now Delete Duplicate Records

9.  DELETE FROM TempEmp

10.WHERE duplicateRecCount > 1 

1.   --See affected table

Select * from Employee 

For more help about ROW_NUMBER(), please follow the MSDN link.

Get nth highest and lowest salary of an employee

One student of me asked "how can we get nth highest and lowest salary on an employee ?". In this article I am going to expose, how can we achieve this in SQL Server.

Suppose we have employee name and salary as shown in below fig.

Query to get nth(3rd) Highest Salary

1.  Select TOP 1 Salary as '3rd Highest Salary' 

2.  from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary DESC) 

3.  a ORDER BY Salary ASC 

Query to get nth(3rd) Lowest Salary

1.   Select TOP 1 Salary as '3rd Lowest Salary' 
2.  from (SELECT DISTINCT TOP 3 Salary from Employee ORDER BY Salary ASC) 
3.  a ORDER BY Salary DESC 






 


Summary

In this article, I explain how




can you get nth highest and lowest salary of an employee. I hope after reading

this article you will be able to do this. I would like to have feedback from my

blog readers. Please post your feedback, question, or comments about this



article

Inserted, Deleted Logical table in SQL Server

There are Inserted and Deleted logical tables in SQL Server. These tables are automatically created and managed by SQL Server internally to hold recently inserted, deleted and updated values during DML operations (Insert,Update,Delete) on a database table.

Use of logical tables

Basically, logical tables are used by triggers for the following purpose:

1.     To test data manipulation errors and take suitable actions based on the errors.

2.    To find the difference between the state of a table before and after the data modification and take actions based on that difference.

Inserted logical Table

The Inserted table holds the recently inserted or updated values means new data values. Hence newly added and updated records are inserted into the Inserted table.

Suppose we have Employee table as shown in fig. Now We need to create two triggers to see data with in logical tables Inserted and Deleted

1.   CREATE TRIGGER trg_Emp_Ins

2.  ON Employee

3.  FOR INSERT

4.  AS

5.  begin

6.  SELECT * FROM INSERTED -- show data in Inserted logical table

7.  SELECT * FROM DELETED -- show data in Deleted logical table

end

Now insert a new record in Employee table to see data with in Inserted logical table.

1.   INSERT INTO Employee(EmpID, Name, Salary) VALUES(3,'Avin',23000)

2.  SELECT * FROM Employee 

Deleted logical Table

The Deleted table holds the recently deleted or updated values means old data values. Hence old updated and deleted records are inserted into the Deleted table.

1.   CREATE TRIGGER trg_Emp_Upd

2.  ON Employee

3.  FOR UPDATE

4.  AS

5.  begin

6.  SELECT * FROM INSERTED -- show data in INSERTED logical table

7.  SELECT * FROM DELETED -- show data in DELETED logical table

8.   end 

1.   --Now update the record in Employee table to see data with in Inserted and Deleted logical tables

2.  Update Employee set Salary=43000 where EmpID=3

SELECT

We could not create the logical tables or modify the data with in the logical tables. Except triggers, When you use the OUTPUT clause in your query, logical tables are automatically created and managed by SQL Server. OUTPUT clause also has access to Inserted and Deleted logical tables just like triggers.