The conversion from ER model to Relational model involves the formation of the following Relational
Tables including all entity sets, relationship sets and the involved attributes :-
Case 1 : NULL values not allowed
-
A ( a1 , a2 )
-
B1 ( b1 , b2 )
-
B2 ( b1 , b3 )
-
C ( c1 , c2 )
-
R1 ( a1, b1 )
-
R2 ( a1, a1 )
-
R3 ( c1, b1 )
As there are no total participations invloved in this Model ..we cannot combine any entity set with relationship set
We have to maintain seperate tables for each Relation ….
Since b3 is a multivalued attribute of B ...We will make a seperate relations B1 and B2 where B2 maintains
the multivalued attribute b3 along with b1 , which together acts as primary key in B2
The R1 , R2 , R3 tables in Realtional model act as Referencing Tables refering attributes of Parent Tables
R1 has the following foreign keys : a1 refering primary key a1 in A and b1 refering primary key b1 in B
R2 has the following foreign keys : a1 refering primary key a1 in A and a1 refering primary key a1 in A (Self referential)
R3 has the following foreign keys : c1 refering primary key a1 in A and b1 refering primary key b1 in B
B2 has the following foreign key : b1 refering primary key b1 in B
Case 2 : NULL values allowed
In this case the entity set C and relationship set R3 could be merged …
-
A ( a1 , a2 )
-
B1 ( b1 , b2 )
-
B2 ( b1 , b3 )
-
R1 ( a1, b1 )
-
R2 ( a1, a1 )
-
R3 ( c1, c2 ,b1 )
R1 has the following foreign keys : a1 refering primary key a1 in A and b1 refering primary key b1 in B
R2 has the following foreign keys : a1 refering primary key a1 in A and a1 refering primary key a1 in A (Self referential)
R3 has the following foreign keys : b1 refering primary key b1 in B
B2 has the following foreign key : b1 refering primary key b1 in B
Hence the minimum no of foreign keys required in the Relational model is 6 which is the
minimum among the two cases.
