Database Normalization
First Normal Form (1NF):
requires that:
● Each column contains atomic values.
● Each table has a primary key.
Entities in the ER diagram:
● Admin
● LoginDetails
● User
● Order
● Restaurant
● Menu
● Payment
● Delivery
● Rider
● Partner
Removing Multi-Valued Attributes:
The Phone attribute is multi valued for some entities.we need to remove this multi-valued
attribute by creating separate tables for phone numbers.
● User table:
UserID Name Address AdminID
● UserPhone table:
UserID Phone
● Primary key: Composite key (UserID, Phone)
● Foreign key: UserID
● Partner table:
PartnerID Name Address AdminID
● PartnerPhone table:
PartnerID Phone
● Primary key: Composite key (PartnerID, Phone)
● Foreign key: PartnerID
● Rider table:
RIderID Name Address AdminID
● RiderPhone table:
RIderID Phone
● Primary key: Composite key (RiderID, Phone)
● Foreign key: RiderID
● Admin table:
AdminID Name Address
● AdminPhone table:
AdminID Phone
● Primary key: Composite key (AdminID, Phone)
● Foreign key: AdminID
● LoginDetails table:
EmailID Password AdminID UserID
Already in 1 normalization form
● Order table:
OrderID OrderAmount OrderDate OrderStatus UserID RestaurantID
Already in 1 normalization form
● Restaurant table:
RestaurantID RestaurantName Address OperatingTime PartnerID
Already in 1 normalization form
● Menu table:
MenuID MenuName Category Price RestaurantID
Already in 1 normalization form
● Payment table:
PaymentID PaymentType PaymentStatus PaymentDate OrderID
● Delivery table:
DeliveryID DeliveryStatus DeliveryTime Address RiderID OrderID
Already in 1 normalization form
Second Normal Form (2NF):
requires that:
● The table is in 1NF.
● There are no partial dependencies.
● Order table:
OrderID OrderAmount OrderDate OrderStatus UserID RestaurantID
➢ OrderID is the primary key and all attributes (OrderAmount, OrderDate,
OrderStatus, UserID, RestaurantID) are fully dependent on OrderID.
● Restaurant table:
RestaurantID RestaurantName Address OperatingTime PartnerID
➢ RestaurantID is the primary key and all attributes (RestaurantName, City, State,
Zipcode, Address, OperatingTime) are fully dependent on RestaurantID.
● Delivery table:
DeliveryID DeliveryStatus DeliveryTime Address RiderID OrderID
➢ Delivery: DeliveryID is the primary key and all attributes (DeliveryStatus,
DeliveryTime, City, State, Zipcode, Address, RiderID, OrderID) are fully
dependent on DeliveryID.
● Payment table:
PaymentID PaymentType PaymentStatus PaymentDate OrderID
➢ Already in 2 normalization form
● Menu table:
MenuID MenuName Category Price RestaurantID
➢ Already in 2 normalization form
● User table:
UserID Name Address AdminID
➢ Already in 2 normalization form
● UserPhone table:
UserID Phone
➢ Already in 2 normalization form
● Partner table:
PartnerID Name Address AdminID
➢ Already in 2 normalization form
● PartnerPhone table:
PartnerID Phone
➢ Already in 2 normalization form
● Rider table:
RIderID Name Address AdminID
➢ Already in 2 normalization form
● RiderPhone table:
RIderID Phone
● Already in 2 normalization form
● Admin table:
AdminID Name Address
➢ Already in 2 normalization form
● AdminPhone table:
AdminID Phone
● Already in 2 normalization form
● LoginDetails table:
EmailID Password AdminID UserID
Already in 2 normalization form
Third Normal Form (3NF):
requires that:
● The table is in 2NF.
● There are no transitive dependencies.
● Order table:
OrderID OrderAmount OrderDate OrderStatus UserID RestaurantID
➢ OrderID is the primary key and all attributes are directly dependent on OrderID
and there are no transitive dependencies.so the order table is in 3 normalization
form.
● Restaurant table:
RestaurantID RestaurantName Address OperatingTime PartnerID
➢ RestaurantID is the primary key, and all attributes are directly dependent on
RestaurantID and there are no transitive dependencies.so the Restaurant table is
in 3 normalization form.
● Delivery table:
DeliveryID DeliveryStatus DeliveryTime Address RiderID OrderID
➢ DeliveryID is the primary key, and all attributes are directly dependent on
DeliveryID and there are no transitive dependencies.so the Delivery table is in 3
normalization form..
● Payment table:
PaymentID PaymentType PaymentStatus PaymentDate OrderID
➢ PaymentID is the primary key and all attributes are directly dependent on
PaymentID and there are no transitive dependencies.so the Payment table is in 3
normalization form.
● Menu table:
MenuID MenuName Category Price RestaurantID
➢ MenuID is the primary key and all attributes are directly dependent on MenuID and
there are no transitive dependencies.so the menu table is in 3 normalization
form.
● User table:
UserID Name Address AdminID
➢ UserID is the primary key and all attributes are directly dependent on UserID and
there are no transitive dependencies.so the User table is in 3 normalization form.
● UserPhone table:
UserID Phone
➢ Composite key (UserID, Phone) is the primary key and all attributes are directly
dependent on Composite key (UserID, Phone) and there are no transitive
dependencies.so the UserPhone table is in 3 normalization form.
● Partner table:
PartnerID Name Address AdminID
➢ Already in 3 normalization form
● PartnerPhone table:
PartnerID Phone
➢ Already in 3 normalization form
● Rider table:
RIderID Name Address AdminID
➢ Already in 3 normalization form
● RiderPhone table:
RIderID Phone
● Already in 3 normalization form
● Admin table:
AdminID Name Address
➢ All attributes are directly dependent on AdminID, and there are no transitive
dependencies.so the admin table is in 3 normalization form.
● AdminPhone table:
AdminID Phone
● Already in 3 normalization form
● LoginDetails table:
EmailID Password AdminID UserID
Already in 3 normalization form
