Welcome to this segment on ‘Building a Relational Model’. In the last segment, we understood various database keys and how they can be used. In this segment, you will learn how to map a relational model from an ER model and how to implement the relations between entities in a relational model.
a relation between two entities in an E-R Model is implemented using foreign keys.
Let us say, a bus entity and a customer entity have a relation ‘ticket’ in the E-R model. The relation is many-to-many. This ticket relation will be a new table which contains foreign keys to both the tables. Sometimes, a relation is just implemented by foreign keys and a new table is not created. For example, a department has employees. The relation is one-to-many and the department primary key becomes a foreign key in the employee’s table. A new table is created for a relation when the relation is many-to-many as in the case of bus and customers.
- If the relation between two entities is one-to-one:
- If both entities have mandatory participation, the foreign key column can be put in any table. But it must be marked as Non-Null because the participation is mandatory, so every row in the table must have a foreign key value.
- If both entity participation is optional, the foreign key column can be put in any table. But it must not be marked as Non-Null because the participation is optional, so not every row in the table is related to some other row in the second table or not every row must have a value for the foreign key column.
- If one entity A has mandatory participation and one entity B has optional participation, the foreign key is kept in the entity A with mandatory participation. This ensures that since the participation is mandatory, every row in table A will have a value for the foreign key. The column will be marked as Non-Null. Suppose, the foreign key was kept in table B. Since the participation is optional, some fields in the foreign key column may remain null.
Also, the foreign key column is made Unique in the one-to-one relation since one row from one table can correspond to only one row in another table. This means that each value in the foreign key column is also unique for each row.
- If the relation between two entities is one-to-many:
Suppose, Entity A lies on the ‘one’ side and entity B lies on the ‘many’ side in a one-to-many relation. Why it is recommended to put the foreign key in the entity on the ‘many’ side?
Consider two tables, team and project. One team can handle many projects but one project can be handled by only one team. This means that the relation between team and project is one-to-many.
Consider the team table:
Team IDTeam NameTeam Category101Team ATechnical102Team BSales105Team CMarketing
Consider the project table:
Project IDProject Name150Project A200Project B250Project C300Project D350Project E
We know the foreign key must be put in the table on the ‘many’ side. This means that the foreign key column must go in the project table. But let us say, we put it in the team table.
Consider that Team A manages to project A and project C. Team B manages project B. Team C manages project D and project E.
Team IDTeam NameTeam CategoryProject ID (Foreign Key)101Team ATechnical150, 250102Team BSales200105Team CMarketing300, 350
This causes Project ID in the team table to have multiple values. Since Team A manages two projects, there are two values in the foreign key column. To retrieve one value, when multiple values are present in a field, becomes difficult since the values are not stored as a list of values but as a single value. So, (150, 250) becomes a single value.
This is not very efficient when querying a database. We can solve this issue by separating the values into new rows.
Team IDTeam NameTeam CategoryProject ID101Team ATechnical150101Team ATechnical250102Team BSales200105Team CMarketing300105Team CMarketing350
This way there are no multiple values in any of the fields. But the information gets repeated. If Team B manages 5 projects, there will be 5 such rows for Team B. This is the reason why the foreign key column is not kept in the table on ‘one’ side of the relation.
Now, let us see what happens when we put the foreign key in the project table which is on the ‘many’ side of the relation:
Project IDProject NameTeam ID150Project A101200Project B102250Project C101300Project D105350Project E105
This way every project stores the information about which team is handling that project.
Now, let us also consider cardinality in this example. The foreign key is in the project table as it is on the ‘many’ side of the relation. If the project entity has mandatory participation, the foreign key column must be marked as Non-Null. If the project entity has optional participation, the foreign key column must not be marked as Non-Null. The value in the foreign key column may be repeated in many rows as seen in the project table where 101 and 105 values are repeated.
- If the relation between two entities is many-to-many:
If the relation is many-to-many, keeping the foreign key in any entity will cause multiple values in the fields of the foreign key column as seen in the team table case. A new entity that contains the primary key of both entities is created. Consider the same example of team and project tables. If the relation between team and project table is many-to-many, we create a new table ‘project management’.
Let us say, Team A now manages project A and project D. Team B manages project A, project B and project E. Team C manages project B, project C, project D and project E. Team D manages project C, project D and project E. Team E manages project E.
Consider the project management table.
Team IDProject ID101150101300102150102200102350105200105250105300105350
This table shows which team manages which project.
The relation between the team table and project management table is one-to-many. You can see that one team can have many records in the project management table.
The relation between the project table and project management table is one-to-many. You can see that one project has many records in the project management table.