Tuesday, 16 January 2018

Reduce the given ERD with many relationships to relationschema

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.

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

 

















SQL exercises for beginners one

SQL Exercises for Beginners / Simple SQL Exercises with Answers / Solved SQL exercises / SQL solved exercises with simple conditions / So...