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

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

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.

After 2NF

Products Category Table

Brand Table

Product & Brand ID Table

Example 2: 2NF

Before 2NF

After 2NF

Example 3 : 2NF

After 2NF

Student Course Table

Course Fee Table

Example 4: 2NF

2nd  Normal  Form

EMLOYEES

Projects

EMPLOYEES-PROJECTS

Example 4: 2NF

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)