Database Normalization: Applying 1NF, 2NF, and 3NF Principles

This assignment solution focuses on database normalization, specifically addressing the concepts of 1NF, 2NF, and 3NF. The solution analyzes several tables related to a customer service scenario, including Customer Service Professional, Customer, Package, Shipment, and Report tables. It begins by assessing the tables' compliance with 1NF, ensuring no repeating groups exist. Then, it progresses to 2NF, evaluating full functional dependencies. Finally, the solution identifies and addresses transitive dependencies, particularly within the Package table, to achieve 3NF. The solution proposes a revised schema, introducing a Pickup table to eliminate redundancy and improve data integrity. The document provides a comprehensive overview of the normalization process, explaining each step and the rationale behind the transformations, making it a valuable resource for understanding and applying database normalization principles.

Normalization
Customer service professional
Login_id Password
PK ↑
In Customer service professional table there is not any repeating group within the table with
multiple values and there is not partial dependency due to not having two primary keys in
table. The normalization of 2nf relations to 3nf involves the removal of transitive
dependencies. If a transitive dependency exists but in this table transitive dependency is not
exists.
Customer
Cust_id Cust_name e-mail Address Mobileno lid
PK ↑ FK↑
In Customer table there is not any repeating group within the table with multiple values and
there is no partial dependency due to not having two primary keys in the table. The
normalization of 2nf relations to 3nf involves the removal of transitive dependencies. If a
transitive dependency exists but in this table transitive dependency is not exists.
Package
Tracking_id Weight Delivery
option
Source
address
Destination
address
Date
of
pickup
Time
of
pickup
Location
of
pickup
lid
PK ↑ FK↑
In Package table there is not any repeating group within the table with multiple values and
there is no partial dependency due to not having two primary keys in the table. The
normalization of 2nf relations to 3nf involves the removal of transitive dependencies. If a
transitive dependency exists but in this table transitive dependency is not exists.
Shipment
Shipment_id Current
location
Source
address
Destination
address
Status Condition Cust_id lid
PK ↑ FK↑ FK↑
In Shipment table there is not any repeating group within the table with multiple values and
there is no partial dependency due to not having two primary keys in the table. The
normalization of 2nf relations to 3nf involves the removal of transitive dependencies. If a
transitive dependency exists but in this table transitive dependency is not exists.
Report
Report_id Report listing Report duration lid
PK ↑ FK↑
In Report table there is not any repeating group within the table with multiple values and
there is no partial dependency due to not having two primary keys in the table. The
normalization of 2nf relations to 3nf involves the removal of transitive dependencies. If a
transitive dependency exists but in this table transitive dependency is not exists.

1nf
Customer Service Professional table is already in 1nf because there is not any repeating group
within the table with multiple values.
Customer Service Professional (login_id, password)
Customer table is already in 1nf because there is not any repeating group within the table
with multiple values.
Customer (cust_id, cust_name, email, address, mobileno, lid)
Package table is already in 1nf because there is not any repeating group within the table with
multiple values.
Package (tracking_id, weight, delivery option, source address, destination address, date of
pickup, time of pickup, location of pickup, lid)
Shipment table is already in 1nf because there is not any repeating group within the table with
multiple values.
Shipment (shipment_id, current location, source address, destination address, status,
condition, cust_id, lid)
Report table is already in 1nf because there is not any repeating group within the table with
multiple values.
Report (Report_id, report listing, report duration, lid)
2nf
2nf is based on full functional dependency, so I find out the tables dependency and primary
keys in 1nf. In 2nf I can write down the tables according to the partial dependence by writing
down the primary key and one or two attribute of the table which it can shows the other
attributes.
Customer Service Professional (login_id, password)
Customer (cust_id, cust_name, email, address, mobileno, lid)
Package (tracking_id, weight, delivery option, source address, destination address, date of
pickup, time of pickup, location of pickup, lid)
Shipment (shipment_id, current location, source address, destination address, status,
condition, cust_id, lid)
Report (Report_id, report listing, report duration, lid)

3nf
The normalization of 2nf relations to 3nf involves the removal of transitive dependencies. If a
transitive dependency exists. So in this table transitive dependence exists in package table.
We took pickup type as a primary key in new table with pickup and put pickup type as a
foreign key in log table.
Customer Service Professional (login_id, password)
Customer (cust_id, cust_name, email, address, mobileno, lid)
Package (tracking_id, weight, delivery option, source address, destination address,
pickup_type , lid)
Pickup (pickup_type, date of pickup, time of pickup, location of pickup)
Shipment (shipment_id, current location, source address, destination address, status,
condition, cust_id, lid)
Report (Report_id, report listing, report duration, lid)