Database Normalization Solution

This assignment provides a step-by-step solution demonstrating database normalization from 1NF to 3NF. It analyzes several tables (Customer Service Professional, Customer, Package, Shipment, Report) identifying functional and transitive dependencies. The solution explains how to remove redundancies and anomalies by applying normalization rules. It details the process of transforming tables to eliminate partial and transitive dependencies, resulting in a more efficient and robust database design. The solution clearly shows the changes made to each table at each normalization level (1NF, 2NF, and 3NF), making it a valuable resource for students learning about database design 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 package 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)