Reduce ERD to relation schemas, Reduce specialization (EER component) to relation schemas, How to convert ER diagram to relation schemas, how to convert multiple relationship between two entity sets to relation schemas, ER model to relational model conversion
The ER diagram given below is for
sports database. Use the given ER diagram to determine the 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.
ER Component
|
Reduced into Relational Schemas
|
Strong Entity
Students
Teams
Games
|
Students(SID,
Name, Address)
Teams(TID,
Name, Ranking)
Games(GID,
Score, Date)
|
M-to-M Relationship
Binary Membership
|
Membership(SID,
TID)
|
1-to-M Relationship
isCaptain
Host
Guest
|
No separate
schema. But the many side’s strong entity set is added with one side’s
primary key. Hence teams becomes as follows. SID in teams schema is given a
special name.
Teams(TID,
Name, Ranking, Captain)
The relationship is
to represent the host team for a game. Hence, we add tid (one
side pk) in games (as foreign key) with a special
name host or host_team as follows;
Games(GID,
Score, Date, Host_Team)
The relationship is
for mentioning the guest team for a game. Hence, we add tid (one
side pk) in games (as foreign key) with a special
name guest or guest_team as follows;
Games(GID,
Score, Date, Host_Team, Guest_Team)
|
Final set of
relations as follows;
Students (SID, Name, Address)
Teams (TID, Name, Ranking, Captain) –
here, Captain
is foreign key refers SID of Students
relation.
Membership (SID, TID) –
here, SID
and TID are foreign keys refer SID of
Students and TID of Teams relations
respectively.
Games (GID, Score, Date, Host_Team, Guest_Team) – here, Host_Team is a foreign
key refers TID of Teams and Guest_Team is another
foreign key refers TID of Teams
relations. Both refers same attribute but possibly with two different values
through two different relationships.
*************