Question

Question 30. Convert the ER diagram below to a relational schema using the conversion algorithm from this unit. Show all of the steps in the algorithm. ER To Relational Conversion Algorithm Step 1: Each entity becomes a relation Step 2: Each many-to-many relationship becomes a relation Step 3: Each one-to-many relationship is represented by a foreign key Step 4: Write out the final relational schema Crazy Party Planning buys Nickname Address DUDE SNACK SnackName SnackDescription knows GuestName GUEST PhoneNumber invites

Answer 1

For the given ER diagram, below are the steps to convert the ER diagram into the relational schema:

1. For the relationship between the DUDE and SNACK:

knows GUEST DUDE Nickname GuestName Address PhoneNumber

Each entity becomes a relation. First convert all the entities and relationship into tables with their primary keys (marked in red) and other attributes as below. So three tables are created i.e. DUDE with primary key NickName, GUEST with primary key GuestName and KNOWS with the primary keys of tables DUDE and GUEST.

DUDE Nickname Address D1 A1 D2 A2 A3 D3 GUEST GuestName PhoneNumber G1 PN1 G2 PN2 G3 PN3 KNOWS Nickname GuestName G1 D1 D1 G2 D2 G3 D2 G1 G2 D3 D3 G3

In the above KNOWS table, the relationship shown is many-to-many i.e. every dude knows many guest and every guest knows many dude, and therefore it is seen that Nickname and GuestName values are repeating but the combination of both the values is unique. Therefore, the combination of both the Nickname and GuestName is the primary key for the table KNOWS. Therefore, all the three tables above have different primary keys and hence cannot be merged into one or two tables. Therefore, the above ER diagram is converted into three tables.

Resultant tables: Table1 Nickname Address D1 A1 D2 A2 D3 A3 Table2 GuestName PhoneNumber G1 PN1 PN2 G2 G3 PN3 Table3 Nickname GuestName D1 G1 G2 D1 D2 G3 D2 G1 D3 G2 D3 G3

2) For the relationship between the DUDE and SNACK:

buys SNACK DUDE Nickname SnackName Address SnackDescription

In the above BUYS table, the relationship shown is one-to-many i.e. one dude buys one or many snacks but one snack can be bought only by one dude, and therefore it is seen that Nickname values are not repeating but the SnackName values are repeating. Therefore, Nickname is the primary key for the table BUYS. Both the BUYS table and DUDE table have the same primary key therefore, these two tables can be merged into one table by making SnackName in the buys table as the foreign key after merging the two tables. Therefore, the above ER diagram is converted into two tables Table4 with primary key as Nickname and foreign key as SnackName and Table5 with the primary key as SnackName as below:

Resultant Tables: Table4 Nickname Address SnackName S1 A1 G1 G3 S2 A2 G3 S3 A3 Table5 SnackName SnackDescription S1 S_D1 S2 S_D2 S3 S D3

3) For the self relationship between the GUEST and GUEST:

GUEST invites GuestName PhoneNumber

In the above GUEST table, the relationship shown is one-to-many i.e. one guest as a host can invite one or more guest and many guest can visit only one host. Therefore, GuestName is the primary key for the table invites with Host as the foreign key that will have the same value as the column GuestName. Both the INVITES table and GUEST table have the same primary key therefore, these two tables can be merged into one table by making GuestName as the primary key and Host as the foreign key. Therefore, the above ER diagram is converted into one table as below:

Resultant Table: TABLE6 GuestNamePhoneNumber Host G2 G1 PN1 G2 PN2 G3 G3 PN3 G1