Relationships and entity relationship diagrams
A table in a database holds the attributes for one entity only. Data can then be added in the form of records.
Rather than storing information in one table, relational databases store data across several tables.
Using relational databases that link tables using primary and foreign keys helps to avoid issues like insertion, deletion and update anomalies.
In a relational database, it is necessary to define the relationship that exists between different entities.
One-to-many cardinality
A one-to-many relationship exists when one entity can be present in many different instances of another entity. One-to-many relationships are the most common relationships in correctly implemented relational database management systems.
Sports centre example
In a sports centre, one surface can be used on many different sports areas.
If the sports centre has five grass pitches, the same surface (grass) is used in five different areas. However, each sports area can only have one surface.
The full list of sports areas is as follows:
- 2 x grass football pitches
- 1 X 3G football pitch
- 1 X astro hockey pitch
- 1 X grass rugby pitch
- 6 X outdoor tennis courts
- 1 X indoor basketball court
There would need to be another entity to hold information on the different sports areas. Each pitch and hall has a playing surface (e.g. grass, 3G, hardwood) so it may be necessary to include an entity called surface.
This would allow the sports centre to keep information on suppliers for when any of the surfaces require maintenance.
The relationship between the surface and the different sports areas is a one-to-many relationship. One-to-many relationships are illustrated as follows:
It can be beneficial to show the attributes of each entity as part of an entity relationship diagram. There are two ways to do this:
Method 1
Method 2
In these examples you will notice that the words 'is used on' appear on the line that shows the relationship between the entities. This statement is describes the relationship that exists between the entities.
In this case it can be read as 'one surface is used on many sports areas'. It is necessary to include relationship descriptions like this when creating an entity relationship diagram.