DATABASES
7.4 ENTITY RELATIONSHIP DIAGRAM (ERD)

WHAT IS AN ENTITY RELATIONSHIP DIAGRAM

An entity relationship diagram is a visual representation that shows how each section/table in your database will join together.

​Whilst it is very tempting to jump straight in and start to build your database, taking a step back to focus on the planning stage will frequently save you time and often save you from having to re-start the building of your database. Remember with a database if you make an error with the structure you can often not go back and make the changes, you have to recreate the table or database again from scratch.

CARDINALITY

Cardinality represents the relationship between two different tables. Looking at the example of the E-sports database. This section will go through the process of developing the ERD (Entity Relationship Diagram) for the E-sports scenario.

Table 1

Table 2

Table 3

Firstly looking at the relationship between Table 1 and Table 3; a player can play many games or no games at all, so the playerID can appear in 'Table 3' none or many times. This same player can only appear in the 'Table 1' table once, so the playerID can only appear in 'Table 1' once, it must be unique. Remember if the player is not in 'Table 1' then the same player cannot possibly appear in 'Table 3', therefore the player cannot be in 'Table 1'. This means the join between the player and session table will be a one to zero or many join.

The same scenario applies to the link between the gameName from 'Table 3' and the gameID in 'Table 2'. Each game can only appear once in table 2 but might be played many time so can appear in Table 3 many times or they may never be played therefore may never appear in Table 3 hence a one to zero or many relationship.

Using Lucidchart to create an ERD which shows the tables, links and 'crows-foot' notation of the join type(cardinality) can be seen below.

Taken from the 3 Normalised tables, this ERD now shows where the joins are and the type of join needed. The last important step to take before starting to create the database is to consider data types to use and naming conventions.

NAMING CONVENTIONS

Firstly looking at naming conventions, simply meaning the rules you will apply to how you name your fields and tables. There are some rules and some guidelines.

A common mistake is to use mathematical operators ( + - * / ) in the table name, whilst when you create the database this will work, when it comes to writing SQL you will start to get errors. For example in 'Table 1' in the diagram above the table name is 'E-sports_Competition_Player_Details', this name will not work with SQL because it have a minus sign after the E. The other problem with this name is that it is long, this is not against any rules but might become frustrating when writing SQL statements, along with a higher change for typing errors. Therefore consider shorter meaningful names for tables and fields, for example in this project we will make the following changes:
'E-sports_Competition_Player_Details' change to: Player_Details
'E-sports_Competition_Game_Details' change to: Game_Details
'E-sports_Competition_Session_Details' change to: Session_Details

Another common mistake is to us spaces, again this might work when you set up your database but will give you problems when it comes to SQL statements. Using the underscore _ method is an acceptable alternative.

Looking at consistency in the naming of fields, in this example all fields have the first word all lowercase and the second word in title case (first letter of the second word uppercase) for example ' firstName'. 

Lastly in the example given table names used have an underscore _ used to separate words, whereas field names have the second letter start with uppercase to separate words, this is intentional in design to help differentiate between field names and table names.

DATA TYPES

Before completing the last ERD and creating the database the last thing to consider are the data types of each field. It is important to map this out because if two related fields have different datatypes the the relationship will not work. It is great to have a map of your datatypes rather than trying to remember each datatype when you are setting up your database.

For example the playerID in the Player_Details table is always one letter followed by 4 numbers, this could be set up as a 'varchar' data type, this means that the related field, the playerID in the Session_Details table will also need to be set as 'varchar' otherwise the relationship will not work.

Common database datatypes are:
text : 
short text :
varchar : 
integer :
boolean : 

ENTITY RELATIONSHIP DIAGRAM

With normalisation done and naming conventions structured the next stage is to create an ERD that shows the 'key' type, a 'FK' (Foreign Key) or 'PK' (Primary Key), the 'field name', 'data type' and 'table name', we can now construct the final ERD diagram.

ERD - Entity Relationship Diagram (produced on LucidChart)

As can be seen the the table needed to be split into two tables to be put into 2nd Normal Form and we can now see that there is no duplicate data but some data is missing, such as the second entry of Jill. 

NEXT SECTION : SQLITE

Then next section looks at building your database using SQLite and an introduction to SQL. A free easy to use database package that you can easily download. For more information go to the next section.

1. What is an Entity Relationship Diagram (ERD)?
2. What is the purpose of creating an ERD?
3. What are the three main components of an ERD?
4. How does an ERD represent the relationships between entities?
5. What are cardinalities in an ERD and what are the different types?
6. How do you determine the primary key of an entity in an ERD?
7. What is an attribute in an ERD and how is it represented?
8. What is a weak entity in an ERD and how is it represented?
9. What is an inheritance relationship in an ERD and how is it represented?
10. How can an ERD be used to improve database design and development?

DOWNLOAD PAGE AS PDF
File Size:	277 kb
File Type:	pdf

Download File

---

KEY TERMS

CARDINALITY : Cardinality represents the relationship between two different tables
JOIN TYPE: How fields from different tables are joined together using either; one-to-one, one-to-many or many-to-many.

NEXT: SQLITE