Search code examples
databaserelational-databaseentity-relationshiperd

Translating ER diagram to Relational model

I have a question about the ER diagram below.

ER Diagram Question: If there are 2 relationships born and live from actor to place entity.

Do they both have the same foreign key which is place_no? So does that mean I just put the foreign key in Actors relational model and that's it or do I have to make a table for Born?

Because an actor is born in a place and lives in a place... so how would it be possible to differentiate between 1 place_no FK?

Solution

  • You have to specify the arity of the relationships. You can map the conceptual 1:n relationships directly in relational model using just foreign keys. For n:m relationships you should use junction tables.

    Since it seems that the relationships here are both n:1 since a person can be born in just one place and (based on your comments) we can also assume that a person can only live in one place at the time we can map these relationships as foreign keys.

    As an example in SQL (assuming we have two relations actor and place, the code is in CREATE code of actor):

    [...]
born int REFERENCES place(place_no),
lives int REFERENCES place(place_no),
[...]