Second Normal Form (2NF)

Definition of second normal form is:

A relation R is in second normal form (2NF) if and only if it is in INF and very non-key attribute is fully dependent on the primary key.

A resultant database of first normal form COURSE_CODE does not satisfy above rule, because non-key attributes Name, System_Used and Hourly_Rate are not fully dependent on the primary key (Course_Code, Rollno) because Name, System_Used and Hourly_Rate are functional dependent on Rollno and Rollno is a subset of the primary key so it does not hold the law of fully functional dependence as shown in figure. In order to convert COURSE_CODE database into second normal form following rule is used.

Rule to convert First Normal Form to Second Normal Form

Consider a relation where a primary key consists of attributes A and B. These two attributes determine all other attributes. Attribute C is fully dependent on the key. Attribute D ‘is partially dependent on the key because we only need attribute A to functionally determine it. Attributes C and D are non prime or non-key attributes. Here the rule is to replace the original relation by two new relations as shown in figure. The first new relation has three attributes: A, B and C. The primary key of this relation is (A,B) i.e. the primary key of the original relation. The second relation has A and D as its only two attributes. Observe that attribute A has been designated, as the primary key of the second relation and that attribute D is now fully dependent on the key.

Although the figure only shows four attributes, we can generalize this procedure for any relation that we need to transform to 2NF if we assume that C stands for the collection of attributes that are fully dependent on the key and D stands for the collection of attributes that are partially dependent on the key. In our case study A stands for Course_Code and B for Rollno. Total_Hrs acts as C and (Name, System_Used, Hourly_Rate) acts as D which depends on Rollno; (Course_Name, Teacher_Name) also acts as D which depends on only Course_Code.

Example

Transformation of STUDENT (Course_Code, Course_Name, Teacher_Name, RoIIno,
Name, System_Used Hourly_Used, Total-Hours) into a 2NF
The above rule calls for breaking this relation into three new relations. The primary key of STUDENT (Course Code, Rollno) and the remaining attributes of this relation that fully depends on this composite key is Total_Hours. The scheme of this new relation that we have named HOURS ASSIGNED is as follows:
HOURS_ASSIGNED (Course_Code, Rollno, Total_Hours)
 
The second relation contains Rollno as its primary key, because Rollno fully determine the Name, System_Used, and Hourly_Rate. The scheme of this relation is as follows:
STUDENT_SYSTEM_CHARGE (Rollno, Name, System_Used, Hourly_Rate)
 
The third relation contains Course_Code as its primary key, because Course_Code fully determine the Course_Name, Teacher_Name. The scheme of this relation is as follows:
COURSE (Course Code, Course_Name, Teacher_Name)
 
Use of 2NF to remove anomalies of First Normal form
Insert Anomalies
It is now possible to insert the information about the student who does not join any course e.g. we can store the information about the RoIIno 110 who not join any course in STUDENT _SYSTEM_CHARGE database as shown above. Similarly now we are able to store the information about the course which has no enrolled student 0.g we can store that CI course is of Visual Basic in COURSE database. It does not matter that whether it has an enrolled student or not.
Update Anomalies
In the revised structure, it is possible to change the teacher for a particular course in the COURSE database through a single modification.
Delete Anomalies
In the revised structure, we can delete the information of student having Rollno 109 without losing the information about his course i.e. C4
Data Anomalies in 2NF Relations
Relations in 2NF are still subject to data anomalies. For sake of explanation, let us assume that the system on which a student works functionally determines the hourly rate charged from the student. That is, System_Used ~ Hourly_Rate. This fact was not considered in the explanation of the previous normal form but it is not an unrealistic situation. If this functional dependence exists then the following anomalies will occur:
Insertion anomalies
Insertion anomalies occur the STUDENT_SYSTEM_CHARGE relation. For example, consider a situation where we would like to set in advance the rate to be charged from the students for a particular system. We cannot insert this information until there is a student assigned to that type of system. Suppose we want to store the hourly late of laptop we cannot insert it until some student use that type of system because roll no is primary key and we cannot insert null into it. Notice that the rate that is charged from student for a particular system is independent of whether or not any student uses that system or not.
Update anomalies
Update anomalies will also occur in the STUDENT _SYSTEM_CHARGE relation because there may be several students which are working on the same type of the system. If the Hourly_Rate for that particular system changes, we need to make sure that the corresponding rate is changed for all students that work on that type of system. Otherwise the database may end up in an inconsistent state. In case of any updation on hourly late of any particular type of system we need to make multiple updations which are equal to the number of students using that type of system.
Delete anomalies
The STUDENT_SYSTEM_CHARGE relation is also susceptible to deletion anomalies. This type of anomaly occurs whenever we delete the tuple of a student who happens to be the only student left which is working on a particular system. In this case we will also lose the information about the rate that we charge for that particular system.
Solution of above problems
The anomaly discussed above occurs due to transitive dependence of Hourly_Rate on the primary key (RoIIno) of STUDENT_SYSTEM_CHARGE database.
The solution of all above anomalies is provided by the third normal form, which deals with the problem of transitive dependence.
Practice Session:
Consider the relation scheme and FD shown below. What is the highest normal form of this relation? Transform this relation to its next higher form. Can the information of the given relation be recovered? What operation is necessary to recover it?
Programmer-Task (Programme- ID, Programming-Package-ID,Programming-Package-Name, 
Total-Hours- Worked-on-Package). Programming-Package-ID, Programming-Package-Name
The highest form of this relation is INF because there are partial dependence on the composite key. Consider for example, Programming-Package-ID Programming-Package- Name.
The next highest form of this relation is to 2NF. To transform it we can use Figure as a guide. According to this figure, we need to create two new relations. The first relation has as its key the primary key of the given relation: Programmer-ID, Programming Package- ID the scheme of this first relation is.
Programmer-Activity (Programmer-ID, Programmer-Package-ID, Total-Hours- Worked-on- Package)
The second relation has as its primary key the attribute: Programming-Package-ID. ‘The scheme of this relation is:
Package-Info (Programming-Package-ID, Programming-Package-Name)
The information of the original relation can be recovered by means of a join operation on the common attribute: Programming-Package-ID.
You’ll also like:
First Normal Form (1NF) 
Third Normal Form (3NF) 
Fifth Normal Form(5NF) 
Fourth Normal Form (4NF) 
Boyce-Codd Normal Form (BCNF) 
   
Next →
← Prev