Second normal form Examples in DBMS

By: Prof. Dr. Fazal Rehman Shamil | Last updated: November 5, 2022

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.

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).

Advantages of 2nd Normal Form

After converting tables into the second normal form, the following are the advantages;

  1. Efficient reduction of redundant data in the database tables.
  2. Improves the overall organization of data in the database.
  3. Consistency of data is in the database tables.
  1. More Database Security
  2. More flexibility in designing a Database.

Exercise 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.

ProductIDProductBrand
1Cell phoneHuawei
2Cell phoneNokia
3BatteryEXIDE
4LaptopLenovo

After 2NF

Products Category Table

ProductIDProduct
1Cell phone
2Battery
3Laptop

Brand Table

BrandIDBrand
1Huawei
2Nokia
3EXIDE
4Lenovo

Product & Brand ID Table

PrdBndIDProductIDBrandID
111

 

212
323
434

Example 2: 2NF

Before 2NF

Employee nameskillCurrent work location
AliGraphic designerUnited Kingdom
AliDatabase United Kingdom
AliHand writingUnited Kingdom
AsadTyping speedUnited States
KhalidFluent in EnglishUnited States
KhalidLeadershipUnited States
HaroonTyping speedUnited States

After 2NF

Employee nameCurrent work location
AliUnited Kingdom
AsadUnited States
KhalidUnited States
HaroonUnited States
Employee nameskill
AliGraphic designer
AliDatabase
AliHand writing
AsadTyping speed
KhalidFluent in English
KhalidLeadership
HaroonTyping speed

Example 3 : 2NF

Student_NoCourse_NoCourse_Fee
1T4TUTORIALS11000
2T4TUTORIALS25000
1T4TUTORIALS44000
4T4TUTORIALS31000
4T4TUTORIALS14000
2T4TUTORIALS5

After 2NF

Student Course Table

Student_NoCourse_No
1T4TUTORIALS1
2T4TUTORIALS2
1T4TUTORIALS4
4T4TUTORIALS3
4T4TUTORIALS1
2T4TUTORIALS5

Course Fee Table

Course_NoCourse_Fee
T4TUTORIALS11000
T4TUTORIALS25000
T4TUTORIALS44000
T4TUTORIALS31000
T4TUTORIALS54000

Example 4: 2NF

2nd  Normal  Form

EMLOYEES

Employee IDLast NameFirst Name
T4TUTORIALS-26Akbarali
T4TUTORIALS-26Akbarali
T4TUTORIALS-33AliAhmad
T4TUTORIALS-33AliAhmad
T4TUTORIALS-33AliAhmad
T4TUTORIALS-35tariqRaza
T4TUTORIALS-35tariqRaza
T4TUTORIALS-36jameelAwais
T4TUTORIALS-38buttJunaid
T4TUTORIALS-40shahidRehan
T4TUTORIALS-40shahidRehan

Projects

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

EMPLOYEES-PROJECTS

Employee IDPROJECT NUMBER
T4TUTORIALS-2645-452-T3
T4TUTORIALS-2645-457-T3
T4TUTORIALS-3388-124-T3
T4TUTORIALS-3345-482-T3
T4TUTORIALS-3345-4241-T3
T4TUTORIALS-3588-238-TC
T4TUTORIALS-3535-152-TC
ENE1-3636-272-TC
T4TUTORIALS-3888-238-TC
T4TUTORIALS-4035-554-TC
T4TUTORIALS-4088-241-TC

Example 4: 2NF

ItemColorsPriceTax
Item1Purple8.00.70
Item1Blue8.00.70
Item8Purple8.00.70
Item8Yellow8.00.70
Item3Blue170.008.90
Item3Orange170.008.90
ItemPriceTax
Item18.00.70
Item88.00.70
Item3170.008.90
ItemColor
Item1Purple
Item1Blue
Item8Purple
Item8Yellow
Item3Blue
Item3Orange

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.

Read Tutorials about Normalization in DBMS

  1. First Normal form in DBMS
  2. Second normal form Examples in DBMS
  3. Third normal form
  4. Boyce-Codd Normal Form (3.5 NF)
  5. Fourth Normal Form
  6. Fifth Normal Form (5NF)

Leave a Reply