PDF Version of this document Search Help Glossary

Lesson Navigation IconRelational database model

Unit Navigation IconConcepts

Unit Navigation IconTransforming ER-schema

Unit Navigation IconData integrity

Unit Navigation IconNormalization

LO Navigation IconDependencies

LO Navigation IconFirst normal form (1NF)

LO Navigation IconSecond normal form (2NF)

LO Navigation IconThird normal form (3NF)

LO Navigation IconExercise normalization

LO Navigation IconUnit summary

Unit Navigation IconSummary

Unit Navigation IconRecommended Reading

Unit Navigation IconGlossary

Unit Navigation IconBibliography

Unit Navigation IconMetadata

GITTA/CartouCHe news:

Go to previous page Go to next page

Second normal form (2NF)

Second normal form:
A relation is in second normal form if it is in 1NF and every non key attribute is fully functionally dependent on the primary key.

A university uses the following relation:

Student(IDSt, StudentName, IDProf, ProfessorName, Grade)

The attributes IDSt and IDProf are the identification keys.
All attributes a single valued (1NF).

The following functional dependencies exist:

1. The attribute ProfessorName is functionally dependent on attribute IDProf (IDProf --> ProfessorName)

2. The attribute StudentName is functionally dependent on IDSt (IDSt --> StudentName)

3. The attribute Grade is fully functional dependent on IDSt and IDProf (IDSt, IDProf --> Grade)

Example Second normal formExample Second normal form

The table in this example is in first normal form (1NF) since all attributes are single valued. But it is not yet in 2NF. If student 1 leaves university and the tuple is deleted, then we loose all information about professor Schmid, since this attribute is fully functional dependent on the primary key IDSt. To solve this problem, we must create a new table Professor with the attribute Professor (the name) and the key IDProf. The third table Grade is necessary for combining the two relations Student and Professor and to manage the grades. Besides the grade it contains only the two IDs of the student and the professor. If now a student is deleted, we do not loose the information about the professor.

Top Go to previous page Go to next page