Second normal form Examples in DBMS

A database is in second normal form if it must satisfy the following 2 conditions:

  1. All database tables must be in the first normal form. (no repeating groups)
  2. All non-key attributes are fully functionally dependent on the primary key attribute. The partial dependency of non-key attributes on the primary key attribute is not allowed.

First of all, let’s see partial and fully functional dependency.,

partial dependency vs full functional dependencyin 2NF

In this example, the baby is partially dependent on her father, and similarly partially dependent on her mother.

Now, let’s suppose that her father died. Now, she will fully depend on her mother.

Video Lecture with Animations

Don’t miss the video with full of animations. I am sure that it will clear your concept about second normal form.

Examples of Second Normal Form

second normal form 2NF Full functional dependency

Explanation of the  table at the top of the figure

The table on the top of the figure is not in 2nd normal form because it has a composite key (Here, the composite key means primary key on two attributes “Employee_id” and “Duty_shift_id”.) and non-key attributes are not fully functionally dependent on the primary key attribute.

For example,

  • The “Employee_name” depends on “Employee_id” and not depends on “Duty_shift_id”. (referred to a partial dependency)
  • The “Age” depends on “Employee_id” and not depends on “Duty_shift_id”. (referred to a partial dependency)
  • The “Duty_shift” depends on “Duty_shift_id” and not depends on “Employee_id”. (referred to a partial dependency)

Explanation of the bottom left of the figure

The table on the bottom-left of the figure is in 2nd normal form because all non-key attributes are fully functionally dependent on the primary key attribute.

For example,

  • “Duty_shift depends” only on “Duty_shift_id”. and the primary key is just on “Duty_shift_id”(no (partial dependency).

Explanation of the bottom-right table of the figure

The table on the bottom-right of the figure is in 2nd normal form because all non-key attributes are fully functionally dependent on the primary key attribute.

For example,

  • The “Employee_name” depends on “Employee_id” and the primary key is just on “Employee_id”(no partial dependency, and referred to a fully functional dependency).
  • The “Age” depends on “Employee_id” and the primary key is just on “Employee_id”(no partial dependency, and referred to a fully functional dependency).
  • The “Duty_shift” depends on “Employee_id” and the primary key is just on “Employee_id”(no partial dependency, and referred to a fully functional dependency).

Excercise 1NF to 5NF

If you are interested to watch a video covering a scenario for all 5th normal form, then don’t miss watching the video.

Examples of 2nd Normal Form

Example 1: 2NF

Normalize the following table into 2NF.

ProductID Product Brand
1 Cell phone Huawei
2 Cell phone Nokia
3 Battery EXIDE
4 Laptop Lenovo

After 2NF

Products Category Table

ProductID Product
1 Cell phone
2 Battery
3 Laptop

Brand Table

BrandID Brand
1 Huawei
2 Nokia
3 EXIDE
4 Lenovo

Product & Brand ID Table

PrdBndID ProductID BrandID
1 1 1

 

2 1 2
3 2 3
4 3 4

Example 2: 2NF

Before 2NF

Employee name skill Current work location
Ali Graphic designer United Kingdom
Ali Database United Kingdom
Ali Hand writing United Kingdom
Asad Typing speed United States
Khalid Fluent in English United States
Khalid Leadership United States
Haroon Typing speed United States

After 2NF

Employee name Current work location
Ali United Kingdom
Asad United States
Khalid United States
Haroon United States
Employee name skill
Ali Graphic designer
Ali Database
Ali Hand writing
Asad Typing speed
Khalid Fluent in English
Khalid Leadership
Haroon Typing speed

Example 3 : 2NF

Student_No Course_No Course_Fee
1 T4TUTORIALS1 1000
2 T4TUTORIALS2 5000
1 T4TUTORIALS4 4000
4 T4TUTORIALS3 1000
4 T4TUTORIALS1 4000
2 T4TUTORIALS5

After 2NF

Student Course Table

Student_No Course_No
1 T4TUTORIALS1
2 T4TUTORIALS2
1 T4TUTORIALS4
4 T4TUTORIALS3
4 T4TUTORIALS1
2 T4TUTORIALS5

Course Fee Table

Course_No Course_Fee
T4TUTORIALS1 1000
T4TUTORIALS2 5000
T4TUTORIALS4 4000
T4TUTORIALS3 1000
T4TUTORIALS5 4000

Example 4: 2NF

2nd  Normal  Form

EMLOYEES

Employee ID Last Name First Name
T4TUTORIALS-26 Akbar ali
T4TUTORIALS-26 Akbar ali
T4TUTORIALS-33 Ali Ahmad
T4TUTORIALS-33 Ali Ahmad
T4TUTORIALS-33 Ali Ahmad
T4TUTORIALS-35 tariq Raza
T4TUTORIALS-35 tariq Raza
T4TUTORIALS-36 jameel Awais
T4TUTORIALS-38 butt Junaid
T4TUTORIALS-40 shahid Rehan
T4TUTORIALS-40 shahid Rehan

Projects

Project number Project title
45-452-T3 Project California
45-457-T3 Project General
88-124-T3 Project Designing
45-482-T3 Project California
45-4241-TC Project United Kingdom
88-238-TC Project United States
35-152-TC Project California
36-272-TC Project 2
33-666-TC Project 3
88-238-TC Project 4
88-241-TC Project United States

EMPLOYEES-PROJECTS

Employee ID PROJECT NUMBER
T4TUTORIALS-26 45-452-T3
T4TUTORIALS-26 45-457-T3
T4TUTORIALS-33 88-124-T3
T4TUTORIALS-33 45-482-T3
T4TUTORIALS-33 45-4241-T3
T4TUTORIALS-35 88-238-TC
T4TUTORIALS-35 35-152-TC
ENE1-36 36-272-TC
T4TUTORIALS-38 88-238-TC
T4TUTORIALS-40 35-554-TC
T4TUTORIALS-40 88-241-TC

Example 4: 2NF

Item Colors Price Tax
Item1 Purple 8.0 0.70
Item1 Blue 8.0 0.70
Item8 Purple 8.0 0.70
Item8 Yellow 8.0 0.70
Item3 Blue 170.00 8.90
Item3 Orange 170.00 8.90
Item Price Tax
Item1 8.0 0.70
Item8 8.0 0.70
Item3 170.00 8.90
Item Color
Item1 Purple
Item1 Blue
Item8 Purple
Item8 Yellow
Item3 Blue
Item3 Orange

Topic Covered

second normal form in DBMS with example ppt.
tables in the second normal form (2nf) MCQ.
advantages of the second normal form.
define second and third normal forms.
second normal form foreign key.

full functional dependency in DBMS with example ppt.
define fully functional dependency with example.
discuss the difference between full functional dependency and partial dependency include an example of trivial functional dependency.
functional dependencies and normalization.
full key dependency.

partial dependency in DBMS.
partial dependency diagram.
partial dependency.
functional dependency example problems.
nontrivial functional dependencies.
what is a partial dependency with what normal form is it associated.
why are partial dependencies bad.

Prof. Fazal Rehman Shamil
Latest posts by Prof. Fazal Rehman Shamil (see all)

    Buy advertisement space on T4Tutorials

    For more details email [email protected]