
- DBMS Tutorial
- DBMS - Home
- DBMS - Overview
- DBMS - Architecture
- DBMS - Data Models
- DBMS - Data Schemas
- DBMS - Data Independence
- Entity Relationship Model
- DBMS - ER Model Basic Concepts
- DBMS - ER Diagram Representation
- DBMS - Generalization, Aggregation
- Relational Model
- DBMS - Codd's Rules
- DBMS - Relational Data Model
- DBMS - Relational Algebra
- DBMS - ER to Relational Model
- DBMS- SQL Overview
- Relational Database Design
- DBMS - Database Normalization
- DBMS - Database Joins
- Storage and File Structure
- DBMS - Storage System
- DBMS - File Structure
- Indexing and Hashing
- DBMS - Indexing
- DBMS - Hashing
- Transaction And Concurrency
- DBMS - Transaction
- DBMS - Concurrency Control
- DBMS - Deadlock
- Backup and Recovery
- DBMS - Data Backup
- DBMS - Data Recovery
- DBMS Useful Resources
- DBMS - Quick Guide
- DBMS - Useful Resources
- DBMS - Discussion
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 Articles
- 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 key attributes with an example in DBMS?
- Explain join operations with the help of an example in 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 about prism with example and minimum deviation
- Explain about triggers and active databases in DBMS
- Explain about single step income statement in accounting with example.
- Explain about logical not(!) operator in detail with example in javascript?
- Explain about the Time stamp ordering protocol in DBMS
- Explain about insert command in Structured query language in DBMS
