Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Vivek's SQL Notes

Vivek johari is currently a Analyst and have more that 5.5 yeras of experience in database. He has Master degree in Computer and also he is Microsoft certified Sql DBA (MCTS)& Microsoft certified SQl BI professional(MCTS). He is also Oracle certified profession(OCP)DBA in ORACLE 10g and ORACLE 9i.He has the experience of working in PL/SQL, T-SQL and SSIS/SSRS. His work basically involved designing and optimization of the Database.He has also published many database articles on his blog Technologies with Vivek Johari.

Second Normal Form (2NF)


Second Normal Form (2NF) :-A table is said to be in its Second Normal Form if it satisfied the following conditions:-
1) It satisfies the condition for the First Normal Form (1NF),
2) It do not includes any partial dependencies where a column is dependent only a part of a primary key.

For example suppose we have a table EmpProjDetail, which contains the employee details and its project details like projected, project name and durations in terms of days on which he/she is allocated to the project.

Table Name:-EmpProjDetail
Primary Key :- EmpId + projectid

EmpId
ProjectId
EmpName
ProjectName
Days
1
1
Vivek
Abc Bank
35
2
2
Sudeep
AbeBook
10

In this table, the primary key is composition of two columns EmpId and ProjectId. Now this table is in 1NF but it is not in the 2NF since the column EmpName can be depended on the column EmpId and the column ProjectName can be depended on the column ProjectId which violates the second condition for the 2NF.

We can break this table into three different tables to convert it into the 2NF. These tables are given below:-

Table name:- EmpDetails
Primary Key: - EmpId

EmpId
EmpName
1
Vivek
2
Sudeep

Table name:- ProjDetails
Primary Key: - ProjectId

ProjectId
ProjectName
1
Abc Bank
2
AbeBook



Table name:- EmpProjdetails
Primary Key: - empId + ProjectId

EmpId
ProjectId
Days
1
1
35
2
2
10

Now all the three tables are in 1NF and all the columns of these tables are fully depended on their respective primary keys.

Comments

No comments.

Leave a Comment

Please register or log in to leave a comment.