• Skip to main content
  • Skip to primary sidebar
  • Skip to secondary sidebar
  • Skip to footer

Computer Notes

Library
    • Computer Fundamental
    • Computer Memory
    • DBMS Tutorial
    • Operating System
    • Computer Networking
    • C Programming
    • C++ Programming
    • Java Programming
    • C# Programming
    • SQL Tutorial
    • Management Tutorial
    • Computer Graphics
    • Compiler Design
    • Style Sheet
    • JavaScript Tutorial
    • Html Tutorial
    • Wordpress Tutorial
    • Python Tutorial
    • PHP Tutorial
    • JSP Tutorial
    • AngularJS Tutorial
    • Data Structures
    • E Commerce Tutorial
    • Visual Basic
    • Structs2 Tutorial
    • Digital Electronics
    • Internet Terms
    • Servlet Tutorial
    • Software Engineering
    • Interviews Questions
    • Basic Terms
    • Troubleshooting
Menu

Header Right

Home » Database » Rdbms » Second Normal Form (2NF)
Next →
← Prev

Second Normal Form (2NF)

By Dinesh Thakur

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.

Functional Dependence diagram

We’ll be covering the following topics in this tutorial:

  • Rule to convert First Normal Form to Second Normal Form
  • Use of 2NF to remove anomalies of First Normal form

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.

Rule-to-transform-first-normal-form-to-second-normal-form

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)

 

Hours_Assigned

Student_System_Charge

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.

Solution of student system change

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:

  1. First Normal Form (1NF)
  2. Third Normal Form (3NF)
  3. Fifth Normal Form(5NF)
  4. Fourth Normal Form (4NF)
  5. Boyce-Codd Normal Form (BCNF)
Next →
← Prev
Like/Subscribe us for latest updates     

About Dinesh Thakur
Dinesh ThakurDinesh Thakur holds an B.C.A, MCDBA, MCSD certifications. Dinesh authors the hugely popular Computer Notes blog. Where he writes how-to guides around Computer fundamental , computer software, Computer programming, and web apps.

Dinesh Thakur is a Freelance Writer who helps different clients from all over the globe. Dinesh has written over 500+ blogs, 30+ eBooks, and 10000+ Posts for all types of clients.


For any type of query or something that you think is missing, please feel free to Contact us.


Primary Sidebar

DBMS

Database Management System

    • DBMS - Home
    • DBMS - Definition
    • DBMS - What is
    • DBMS - Entity Sets
    • DBMS - Components
    • DBMS - Languages
    • DBMS - Normalization
    • DBMS - Data Models
    • DBMS - Processing System
    • DBMS - Advantages
    • DBMS - ER-Model
    • DBMS - Functional Dependence
    • DBMS - Relational Model
    • DBMS - Architecture
    • DBMS - Network Model
    • DBMS - Approach
    • DBMS - Data Independence
    • DBMS - Relational Schema
    • DBMS - Instance
    • DBMS - Functions and Service
    • DBMS - Server
    • DBMS - DBA
    • DBMS - Instance & Schemas
    • DBMS - System Type
    • DBMS - DDL, DML and DCL
    • DBMS - Users
    • DBMS - Model
    • DBMS - System Structure
    • DBMS - Role of DBA
    • DBMS - Metadata
    • DBMS - ER-Diagram
    • DBMS - E-R Model Problems
    • DBMS - DBMS Vs.RDBMS
    • DBMS - Basic Construction of E-R
    • DBMS - E-R Notation
    • DBMS - Database View
    • DBMS - Concurrency Control
    • DBMS - Schema
    • DBMS - Procedure for Access
    • DBMS - Object
    • DBMS - dBase
    • DBMS - Relational Algebra
    • DBMS - Deadlock
    • DBMS - Relational Database
    • DBMS - Query
    • DBMS - Schema

DBMS Normal Forms

    • Database - CODD’S Rules
    • Database - 1NF
    • Database - 2NF
    • Database - 3NF
    • Database - 4NF
    • Database - 5NF
    • Database - BCNF

Advance Database

    • Database - File Organization
    • Database - Type Lock
    • Database - Transaction
    • Database - Key Type
    • Database - Relational Algebra
    • Database - Components
    • Database - Deadlock Detect
    • Database - Design Methodology
    • Database - Relational Operators
    • Database - Relational Calculus
    • Database - Lock Granularity
    • Database - Deadlocks Handling
    • Database - Concurrent Control
    • Database - Denormalization
    • Database - Starvation
    • Database - OODB
    • Database - Data Warehouse
    • Database - Fragmentation
    • Database - Data Replication
    • Database - Distributed
    • Database - Transparences
    • Database - ORDBMSS
    • Database - Data Mining
    • Database - Security
    • Database - DBTG
    • Database - OLAP
    • Database - Integrity
    • Database - Data Encryption
    • Database - Recover
    • Database - Data Protection

Some Other Advance Articls

  • Adv of Distributed DBMS
  • Homogeneous and Heterogeneous
  • Causes for Database Failure
  • DBMS Architecture
  • Features for Any DBMS
  • OLTP Systems Vs Data Warehousing
  • Data Warehousing Architecture

Other Links

  • DBMS - PDF Version

Footer

Basic Course

  • Computer Fundamental
  • Computer Networking
  • Operating System
  • Database System
  • Computer Graphics
  • Management System
  • Software Engineering
  • Digital Electronics
  • Electronic Commerce
  • Compiler Design
  • Troubleshooting

Programming

  • Java Programming
  • Structured Query (SQL)
  • C Programming
  • C++ Programming
  • Visual Basic
  • Data Structures
  • Struts 2
  • Java Servlet
  • C# Programming
  • Basic Terms
  • Interviews

World Wide Web

  • Internet
  • Java Script
  • HTML Language
  • Cascading Style Sheet
  • Java Server Pages
  • Wordpress
  • PHP
  • Python Tutorial
  • AngularJS
  • Troubleshooting

 About Us |  Contact Us |  FAQ

Dinesh Thakur is a Technology Columinist and founder of Computer Notes.

Copyright © 2023. All Rights Reserved.