Introduction to ERD Diagram

The assignment content discusses the creation of an ERD (Entity Relationship Diagram) to illustrate the logical structure of a database. It describes the entities, relations, and attributes involved in the scenario where customers request delivery options and costs from a company. The ERD diagram shows the relationships between these entities, including customer information, staff details, log entries, shipment tracking, handling team data, and recipient confirmation. The assignment also discusses normalization techniques to remove repeating groups, partial dependencies, and transitive dependencies, with 1NF, 2NF, and 3NF normalizations being applied to the tables.

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)