Second normal form in RDBMS

 

SECOND NORMAL FORM

A table is said to be in its second normal form, when a relation must be in first normal form and each column in the record is not partially dependent which means fully dependent on its primary key.

We follow some steps a table converting into a second normal form:

·         Group remove items in the another table

·         Assign the new table with a key i.e part of a whole composite key.  

·         All non-key attributes are fully dependent on the primary key.

Example:- Suppose, a college can store the data of teachers and the subject name they teach. A teacher can teach more than one subject.  

Teacher_id	Subject_name	Teacher age
1	C programming	38
1	SAD	38
2	JAVA	40
3	Math	40
3	JAVA	50

Candidate Keys: {teacher_id, subject_name}
Non prime attribute: teacher_age

It is not in II^nd NF because non-prime attribute teacher_age is dependent on teacher_id which is a proper subset of a candidate key.

College of Engineering Roorkee

To make the table complies with 2^nd NF we can break the table into two tables:
teacher_details table:

Teacher_id	Teacher_age
1	38
2	40
3	40

 

teacher_subject table:

Teacher_id	subject
1	C programming
1	SAD
2	JAVA
3	Math