Reduce ERD to relation schemas, Reduce specialization (EER component) to relation schemas, How to convert ER diagram to relation schemas, how to convert specialization to relation schemas, ER model to relational model conversion
Convert the following ERD to relational schema;
Reduction
rules:
Strong
entity
– all attributes of strong entity will be attributes of relation schema.
M-to-M
relationship
– separate table need to be created with the primary keys of all participating
strong entity sets.
1-to-M
relationship
– primary key of one side entity is included as foreign key in many side entity
set.
Specialization – super class is
modeled as strong entity set. Sub-classes are included with the super class’s
primary keys along with their own attributes as in 1-to-M relationship.
ER Component
|
Reduced into Relational Schemas
|
Strong Entity
Driver
Truck
Trip
Shipment
|
Driver(ID,
Name, PhoneNo)
Truck(LicNo,
maxVol, maxWt)
Trip(tripNo)
Shipment(ShipNo,
Vol, Weight)
|
M-to-M Relationship
Binary - Journey
Ternary -Between
|
Journey(ID,
LicNo, tripNo)
Stoppoint stores either
FROM address or TO address. Hence, we have renamed Address attribute as
follows;
SBetween(ShipNo,
From_Address, Pickup_time, To_Address, Dropoff_time)
|
1-to-M Relationship
Carries
|
No separate schema.
But the many side strong entity set is added with one side’s primary key.
Shipment(ShipNo,
Vol, Weight, tripNo)
|
Specialization
StopPoint
(super class entity)
Warehouse
(sub-class entity)
shopNpay
(sub-class entity)
|
StopPoint(Address)
Warehouse(Address,
port)
shopNpay(Address,
openHrs)
|
Final set of relations are as
follows;
Driver(ID, Name, PhoneNo)
Truck(LicNo, maxVol, maxWt)
Trip(tripNo)
Shipment(ShipNo, Vol, Weight,
tripNo) – tripNo
is foreign key referencing Trip.
Journey(ID, LicNo, tripNo) – ID, LicNo, and tripNo
all are foreign keys referencing Driver, Truck, and Trip relations
respectively.
SBetween(ShipNo,
From_Address, Pickup_time, To_Address, Dropoff_time) – From_Address, To_Address are foreign keys
referencing StopPoint’s Address attribute. Pickup_time and Dropoff_time are descriptive attributes
of the ternary relationship SBetween.
StopPoint(Address)
Warehouse(Address, port) – Address
is the foreign key referencing the super class’s primary key.
shopNpay(Address, openHrs) - Address
is the foreign key referencing the super class’s primary key.
***************