A database is in second normal form if it must satisfy the following 2 conditions:
- All database tables must be in the first normal form. (no repeating groups)
- 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;
- Efficient reduction of redundant data in the database tables.
- Improves the overall organization of data in the database.
- Consistency of data is in the database tables.
- More Database Security
- 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.
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.
Read Tutorials about Normalization in DBMS
- First Normal form in DBMS
- Second normal form Examples in DBMS
- Third normal form
- Boyce-Codd Normal Form (3.5 NF)
- Fourth Normal Form
- Fifth Normal Form (5NF)