

- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Explain about 2NF with an example in DBMS
In the 2NF, relation must be in 1NF. In the second normal form, all the non-key attributes are fully functional dependent on the primary key. Or a relation is in 2NF, when it is in 1NF and there is no partial dependency.
Example
Consider a relation R (rollno, name, subjectcode, subjectname, duration)
Rollno | Name | Subjectcode | Subjectname | Duration |
---|---|---|---|---|
1 | Hari | S1 | C | 60 days |
1 | Hari | S2 | Java | 90 days |
2 | Pinky | S1 | C | 60 days |
2 | Pinky | S2 | Java | 90 days |
3 | Rakhi | S3 | Database | 45 days |
F: {rollno -> name, subjectcode -> (subjectname, duration).
{rollno, subjectcode}+ = {rollno, name, subjectcode, subjectname, duration}
=> {rollno, subjectcode } is candidate key.
The above table is in 1NF because no multivalued attributes are present. But it is not in 2NF because the following two partial dependencies are present.
Rollno->name is a partial dependency {since name depend on apart of key}
Subjectcode->{subjectname, duration} {since subjectname and duration depend on a part of key}
Anomalies
The table also suffers with three anomalies, which are as follows −
Insertion anomaly − We cannot insert a new course such as 'PHP' to the table unless we have a student who has to take the subject.
Updation anomaly − If we change the subject from C to C++ we have to make changes in more than one place otherwise the table will be inconsistent.
Deletion anomaly − If Rakhi is deleted from the table we also lose information we had on the "database" subject.
To convert the given table to 2NF we need to decompose the table first.
Decomposition for 2NF − To overcome these anomalies table R should be divided to smaller tables.
If partial dependency is X->Y then divide R into R1(X+) and R2(R-Y+)
Rollno -> rollno+ = {rollno, name}
R2 =R-name+ = {rollno, subjectcode, subjectname, duration}
Subjectcode -> {subjectname, duration} is a partial dependency [since subjectname and duration depend on part of the key].
So divide R2 table onto R3 and R4 as −
R3=subjectcode+= {subjectcode, subjectname, duration}
R4= R2- {subjectname, duration}+ = {rollno, subjectcode}.
It is represented diagrammatically as shown below −
R1
Rollno | Name |
---|---|
1 | Hari |
2 | Pinky |
3 | rakhi |
R3
Subjectcode | Subjectname | Duration |
---|---|---|
S1 | C | 60 days |
S2 | Java | 90 days |
S3 | Database | 45 days |
R4
Rollno | subjectcode |
---|---|
1 | S1 |
1 | S2 |
2 | S1 |
2 | S2 |
3 | S3 |
The above three tables are free from all anomalies. Let's see clearly why there are no anomalies.
No insertion anomaly: Now a new course "php" can be inserted to the subject table without any student information.
No deletion anomaly: If we delete the rakhi record from both R1 and R4 then it does not have any effect. Because "database" is untouched in the subject table.
No updation anomaly: To change any subject only one change is needed in the subject table.
- Related Questions & Answers
- Explain 3NF with an example in DBMS
- Explain BCNF with an example in DBMS
- Explain serial execution or transaction with an example(DBMS)
- Explain the concept of primary key with an example (DBMS)?
- Explain about nested queries in DBMS
- Explain about concurrent transactions in DBMS
- Explain about conflict serializability in DBMS
- Explain the concept of key attributes with an example in DBMS?
- Explain join operations with the help of an example in DBMS
- Explain about triggers and active databases in DBMS
- Explain about single step income statement in accounting with example.
- Explain about two phase locking (2PL) protocol(DBMS)
- Explain about logical not(!) operator in detail with example in javascript?
- Explain about the Time stamp ordering protocol in DBMS
- Explain trial balance with an example