Database Project: ERD, Relational Schema, and Normalization Techniques

This assignment provides a comprehensive overview of database design, focusing on Entity Relationship Diagrams (ERDs) and normalization techniques. The solution begins with an introduction to ERDs, explaining how they illustrate the logical structure of databases and the relationships between entity sets. A scenario is presented, detailing a customer service and shipment process, which is then used to create an ERD. The solution includes a relational schema with tables for Customer, Staff, Log, Shipment, Handling Team, and Recipient, along with their attributes and primary/foreign keys. The core of the assignment involves normalizing the tables to 1NF, 2NF, and 3NF. The solution explains the principles behind each normal form, identifying and resolving dependencies such as transitive dependencies, particularly in the Log table where delivery cost depends on the delivery type. The final normalized tables are presented, ensuring data integrity and efficiency in the database design. The assignment demonstrates a practical application of database design principles, providing a structured approach to creating a well-organized and optimized database.

Introduction to the ERD diagram
An entity relationship diagram (ERD) shows the relationships of entity sets stored in a database.
An entity in this context is a component of data. In other words, ER diagrams illustrate the
logical structure of databases.
Entity
Relation
Attribute
weak entity
PK attribute

ER Diagram
Scenario explanation according to my ER diagram
When customer call or request to the company staff so customer staff create a log than give
information about the delivery option and delivery cost to the customer when customer confirm
the log so stuff take information about the customer (customer name, cutomer_Emial, customer
address, and if customer from a company than company name) than log start shipment according
to the customer delivery choice and create an ID for shipment that customer can track the
shipment. Log also save information about time and location of shipment. Than shipment
delivered by HD team to the recipient and they get confirmation id from recipient as a prove than
they update the log.

Relation schema
Customer (cu_name, cu_ID, cu_address, cus_Email, company_name, Shp_ID)
Staff (S_name, S_ID, S_phone,)
Log (Log_ID, PK_address, Pk_time, Delivery_option,)
Shipment (carr_location, carr_time, weight, Shp_ID,)
Handling Team (HD_id, HD_name)
Recipient (R_ID, R_Phone, R_Name, Confirmation_ID)
Normalization
Customer
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.
Cu_ID Cu_name Cu_add Cu_email Company_name Ship_ID
PK FK
Staff
In Staff 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.
S_ID S_name S_phone
PK

LOG
In log 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
so here in this table transitive dependency exists because delivery cost depend in delivery type if
delivery type is by air so the coast is different or if its by land so the cost it different.
Log_ID, PK_address, Pk_time, Delivery_type, Delivery_cost
fd1 (PK) fd2 (TD)
Shipment
In Shipment 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.
Shp_ID, shp_time shp_location,
fd1 (PK)
Handling Team
In handling 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.
HD_id HD_name, HD
fd1 (PK)
Recipient
In Recipient 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.
Confirmation_ID R_Name R_Phone
fd1 (PK)

1nf
Customer table is already in 1nf because there is not any repeating group within the table with
multiple values.
Customer (cu_name, cu_ID, cu_address, cus_Email, company_name)
Staff table is already in 1nf because there is not any repeating group within the table with
multiple values
Staff (S_name, S_ID, S_phone)
Log table is already in 1nf because there is not any repeating group within the table with multiple
values
Log (Log_ID, PK_address, Pk_time, Delivery_type, Delivery_cost)
Shipment table is already in 1nf because there is not any repeating group within the table with
multiple values
Shipment (Shp_ID, shp_location, shp_time)
Handling team table is already in 1nf because there is not any repeating group within the table
with multiple values
Handling Team (HD_id, HD_name, HD_)
Recipient table is already in 1nf because there is not any repeating group within the table with
multiple values
Recipient (R_Phone, R_Name, Confirmation_ID)

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 (cu_name, cu_ID, cu_address, cus_Email, company_name)
Staff (S_name, S_ID, S_phone)
Log (Log_ID, PK_address, Pk_time, Delivery_type, Delivery_cost)
Shipment (Shp_ID, carr_location, carr_time, weight)
Handling Team (HD_id, HD_name)
Recipient (R_Phone, R_Name, Confirmation_ID)
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 log table. We took
delivery type as a primary key in new table with delivery and put delivery type as a foreign key
in log table.
Customer (cu_name, cu_ID, cu_address, cus_Email, company_name, ship_id)
Staff (S_name, S_ID, S_phone)
Log (Log_ID, PK_address, Pk_time, delivery_type)
delivery ( Delivery_type, Delivery_cost)
Shipment (Shp_ID, carr_location, carr_time)
Handling Team (HD_id, HD_name)
Recipient (R_Phone, R_Name, Confirmation_ID)