1. Normalize this table to 3NF step by step.

This question is smilar to the one of pre board. Try it yourself first. Then only see the solution

Orde Customer Customer Produ Product Quan Suppli Supplier Supplier Order
rID Name Phone ctID Name tity erID Name Phone Date
987654321 ABC 12345678 2023-
101 Dipson 201 Laptop 1 S1
0 Corp 90 08-01
912345678 Smartpho 09876543 2023-
102 Pooja 202 2 S2 XYZ Inc
9 ne 21 08-02
987654321 ABC 12345678 2023-
101 Dipson 203 Mouse 3 S1
0 Corp 90 08-01
998877665 Smartpho 09876543 2023-
103 Swastika 202 1 S2 XYZ Inc
5 ne 21 08-03

Step 1: Identify Functional Dependencies

Functional dependencies (FDs) describe the relationship between columns, where one column
(or a set of columns) uniquely determines another column.

Functional Dependencies in the Table:

1. OrderID → CustomerName, CustomerPhone, OrderDate

o Each OrderID is associated with a unique customer and a specific order date.
2. ProductID → ProductName, SupplierID
o Each ProductID uniquely determines the ProductName and SupplierID,
indicating that each product has a unique name and supplier.
3. SupplierID → SupplierName, SupplierPhone
o Each SupplierID uniquely identifies a supplier’s name and phone number.
4. OrderID, ProductID → Quantity
o The combination of OrderID and ProductID determines the quantity of each
product in the order.

Step 2: First Normal Form (1NF)

To achieve 1NF, we need to ensure that:

• Each column contains atomic (indivisible) values.

• There are no repeating groups.
In this case, the table already satisfies 1NF because:

• All columns contain atomic values (single, indivisible values).

• There are no repeating groups.

No changes are needed for 1NF.

Step 3: Second Normal Form (2NF)

To achieve 2NF, we need to:

1. Ensure the table is already in 1NF.

2. Remove any partial dependencies, where a non-key attribute is dependent on part of a
composite primary key.

Analyzing Partial Dependencies

In our table, the composite primary key could be (OrderID, ProductID), as it uniquely
identifies each row.

Let's examine the functional dependencies to identify partial dependencies:

1. OrderID → CustomerName, CustomerPhone, OrderDate: This is a partial dependency

because CustomerName, CustomerPhone, and OrderDate only depend on OrderID, not
on the full composite key (OrderID, ProductID).
2. ProductID → ProductName, SupplierID: This is another partial dependency because
ProductName and SupplierID depend on ProductID alone, not on the full composite
key (OrderID, ProductID).
3. SupplierID → SupplierName, SupplierPhone: This is not a partial dependency
because SupplierID is not part of the primary key.
4. OrderID, ProductID → Quantity: This is a full dependency because Quantity
depends on both OrderID and ProductID.

Resolving Partial Dependencies

To resolve these partial dependencies, we will split the table into separate tables:

1. Orders Table (for OrderID and customer-related information):

o Columns: OrderID, CustomerName, CustomerPhone, OrderDate
2. Products Table (for ProductID and product-related information):
o Columns: ProductID, ProductName, SupplierID
3. Suppliers Table (for SupplierID and supplier information):
o Columns: SupplierID, SupplierName, SupplierPhone
4. OrderDetails Table (for the actual order items and quantity):
 o Columns: OrderID, ProductID, Quantity

New Tables after 2NF

Orders Table

OrderID CustomerName CustomerPhone OrderDate

101 Dipson 9876543210 2023-08-01
102 Pooja 9123456789 2023-08-02
103 Swastika 9988776655 2023-08-03

Products Table

ProductID ProductName SupplierID

201 Laptop S1
202 Smartphone S2
203 Mouse S1

Suppliers Table

SupplierID SupplierName SupplierPhone

S1 ABC Corp 1234567890
S2 XYZ Inc 0987654321

OrderDetails Table

OrderID ProductID Quantity

101 201 1
102 202 2
101 203 3
103 202 1

At this point, the tables are in 2NF because there are no partial dependencies in any of the tables.

Step 4: Third Normal Form (3NF)

To achieve 3NF, we need to:

1. Ensure the table is already in 2NF.

 2. Remove any transitive dependencies, where a non-key attribute depends on another non-
key attribute.

Analyzing Transitive Dependencies

Let’s review each table to see if there are any transitive dependencies:

1. Orders Table:
o OrderID → CustomerName, CustomerPhone, OrderDate
o There are no transitive dependencies here because CustomerName,
CustomerPhone, and OrderDate directly depend on the primary key OrderID.
2. Products Table:
o ProductID → ProductName, SupplierID
oThere are no transitive dependencies here because ProductName and SupplierID
directly depend on ProductID.
3. Suppliers Table:
o SupplierID → SupplierName, SupplierPhone
There are no transitive dependencies here because SupplierName and
o
SupplierPhone directly depend on SupplierID.
4. OrderDetails Table:
o The composite primary key is (OrderID, ProductID), and Quantity depends
directly on both.
o There are no transitive dependencies here.

Since there are no transitive dependencies, all four tables are already in 3NF.

Final Tables in 3NF

After normalization to 3NF, we have the following four tables:

1. Orders Table

OrderID CustomerName CustomerPhone OrderDate

101 Dipson 9876543210 2023-08-01
102 Pooja 9123456789 2023-08-02
103 Swastika 9988776655 2023-08-03

2. Products Table

ProductID ProductName SupplierID

201 Laptop S1
202 Smartphone S2
203 Mouse S1
 3. Suppliers Table

SupplierID SupplierName SupplierPhone

S1 ABC Corp 1234567890
S2 XYZ Inc 0987654321

4. OrderDetails Table

OrderID ProductID Quantity

101 201 1
102 202 2
101 203 3
103 202 1

Summary

• 1NF: The table had atomic values and no repeating groups, so it was already in 1NF.
• 2NF: We removed partial dependencies by splitting the table into Orders, Products,
Suppliers, and OrderDetails.
• 3NF: We confirmed there were no transitive dependencies, so no further changes were
needed.

Example 2: Employee Project Assignment

Original Table

EmpID EmpName EmpDept ProjectID ProjectName ProjectManager ManagerPhone

1 Giriraj IT 101 Website Manish 9876543210
2 Shiva HR 102 Training Chadani 9123456789
3 Giriraj IT 102 Training Chadani 9123456789
4 Rina Finance 103 Audit Reshma 9988776655
1 Giriraj IT 103 Audit Reshma 9988776655

Step 1: Identify Functional Dependencies

1. EmpID → EmpName, EmpDept

o Each employee has a unique name and department.
2. ProjectID → ProjectName, ProjectManager, ManagerPhone
o Each project has a unique name, manager, and manager’s phone number.
3. ProjectManager → ManagerPhone
 Each manager has a unique phone number.
o
4. EmpID, ProjectID → (composite key)
o The combination of EmpID and ProjectID determines each row uniquely.

Step 2: First Normal Form (1NF)

The table is already in 1NF because:

• All columns have atomic values.

• There are no repeating groups.

No changes are needed for 1NF.

Step 3: Second Normal Form (2NF)

To achieve 2NF, we need to remove partial dependencies. Our composite primary key is
(EmpID, ProjectID).

1. EmpID → EmpName, EmpDept: This is a partial dependency, as EmpName and

EmpDept depend on EmpID alone.
2. ProjectID → ProjectName, ProjectManager, ManagerPhone: This is another partial
dependency, as these attributes depend only on ProjectID.

To resolve these partial dependencies, we’ll create the following tables:

1. Employees Table: Contains information about each employee.

o Columns: EmpID, EmpName, EmpDept
2. Projects Table: Contains information about each project.
o Columns: ProjectID, ProjectName, ProjectManager, ManagerPhone
3. Assignments Table: Links employees to projects with a quantity.
o Columns: EmpID, ProjectID

Tables after 2NF

Employees Table

EmpID EmpName EmpDept

1 Giriraj IT
2 Shiva HR
3 Giriraj IT
4 Rina Finance

Projects Table
ProjectID ProjectName ProjectManager ManagerPhone
101 Website Manish 9876543210
102 Training Chadani 9123456789
103 Audit Reshma 9988776655

Assignments Table

EmpID ProjectID
1 101
2 102
3 102
4 103
1 103

Step 4: Third Normal Form (3NF)

To achieve 3NF, we need to eliminate transitive dependencies.

• ProjectManager → ManagerPhone is a transitive dependency because ManagerPhone

depends on ProjectManager rather than directly on the primary key ProjectID.

To resolve this, we will create an additional Managers Table.

1. Managers Table: Contains unique information about each manager.

o Columns: ProjectManager, ManagerPhone

Final Tables in 3NF

Employees Table

EmpID EmpName EmpDept

1 Giriraj IT
2 Shiva HR
3 Giriraj IT
4 Rina Finance

Projects Table

ProjectID ProjectName ProjectManager

101 Website Manish
102 Training Chadani
103 Audit Reshma
Managers Table

ProjectManager ManagerPhone
Manish 9876543210
Chadani 9123456789
Reshma 9988776655

Assignments Table

EmpID ProjectID
1 101
2 102
3 102
4 103
1 103

Exercise: Student-Course Enrollment System (Do it yourself)

StudentID StudentName StudentPhone CourseID CourseName Professor ProfPhone

1 Ashik 9123456789 101 Math Sushil 123456789
2 Shreya 9345678901 102 DBMS Binod 234567890
1 Ashik 9123456789 102 DBMS Binod 234567890
3 Kirtan 9456789012 101 Math Sushil 123456789