Advanced Database Management System - Tutorials and Notes: Convert ER diagram to relation schemas specialization included

Search Engine

Please visit, subscribe and share 10 Minutes Lectures in Computer Science

Sunday, 14 January 2018

Convert ER diagram to relation schemas specialization included

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.

***************





How to reduce ERD to conceptual schema?

Convert ER diagram to set of schemas

Solved problem in ER diagram in DBMS

RDBMS solved exercise

Solved exercise in entity relationship model of DBMS 








Featured Content

Multiple choice questions in Natural Language Processing Home

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents