Question

Discussion Topics:
Assume that you have a Saudi league player database. In this database, you have a table containing players’ attributes such as (Name, age, position, etc.) and you decided to add information about players’ agents. Would you represent the agent information as attributes in the player table or would you create an entity set for players’ agents? Justify your answer.   

Note: Provide answers in your own words.

Answer 1

1). ANSWER :

GIVENTHAT :

- Lets assume this is the table for the player which is given to us
Player( name , age , position , gender , address, experience ) ------------------> GIVEN

TODO:
- Now, we need to add the players agent info.


Process:
- Check if the agent is directly an attribute of the player or NOT.
- No, agent is a seperate entity linked by some relationship.
- It means that player and agent are two seperate entities having some relationship between them.
- The relationship here is IS-A relationship.
- It means that X is an agent of Y (example). X is agent, Y is player.

As we can see that there are two independent entities having some relationship between them, thus, there will be TWO table or a new Entity Set for AGENT.


Why did we choose seperate entity for AGENT and NOT ONE?
- Consider a scenario where all the information is stored in single table.
- All the players will be tightly coupled with the agents.
- It means that if a particular agent leaves, we also have to remove the corresponding player entry if it is in a same table.
- The above case is called DELETE ANAMOLY in case of DB Design.

- Now, consider 2 different tables linked via a foreign key in AGENTS table.
- If we want to remove any reference of agent, corresponding to the player, we can easily do so by removing from the entry from the AGENTS table without having to change anything in the PLAYERS table.
- This way we have resolved the DELETE ANAMOLY by creating a seperate entity/table for AGENTS corresponding to the player.