This weeks tutorial is dedicated to one important document in the database design life cycle. This is Entity relationship Diagrams, l’ll refer to it as ERD through out the tutorial.
What is an ERD? It is a simple graphical representation of the Entities and relationship that exist among them. It also captures all the attributes of the entities of the system and the type of relationship that exist among entities. Relationship is the association that exist between entities. There are some terms we have to cover under relationship.
Degree: number of entities participating in the relationship. If there are two entities related to each other, then the degree is 2.
Cardinality: how many instances of one entity relates to the other entity. This is very important. You have to know the cardinality of relationship to properly design tables for the system at the physical level. I’ll say Entity Diagrams come at the Logical level of Database design. There are different types of cardinality, that is Unary(1:M), One – to One (1:1), One-to-Many(1:M),Many-to-One(M:1) and Many-to-Many(M:M). Let’s take a look at some of these cardinality types.
Unary(1:M)
In this type of relationship, the entity is mapped unto itself. That is one instance of the entity can be related to many instances of the same entity. Let’s take for example employee entity.
We know there are some employees who are just employees and there are some employees that are managers. if we want to represent the relationship for this type of relationship. we have to map employee entity unto itself. this is called a unary relationship.
1:1
This means, each instance of one entity relates to one instance of the other entity in the relationship. For example, let’s say we have an entity President and County. The entity President is related to the entity Country and if the business rule says that, a president rules only one country and a Country is ruled by only one president, then this is a 1:1 relationship. This assumption is based solely on the business rule. If the business rule says, a Country can be ruled by more than one president or one president can rule more than one country, then this relationship is no more a 1:1.
1:M
In a One-to-Many relationship, one entity is related to more than one instance of the other entity. Let’s take for example, a customer of a bank and the account as entities. If the business rule for the bank says that one customer can have many accounts, but each account is owned by a single customer, then this is a 1:M relationship.
M:1
In Many-to-One relationship, many instances of one entity is related to a single instance of another entity. For example, Lets look at polygamy system of marriage. Many women can marry one man but a woman can marry only a single man. This is not a fair example but l hope it makes sense lol.
M:M
In a many-to-many relationship, many instances of one entity is related to many instance of the other entity. Lets take the bank example again. Assume we identify a bank as an entity and a person as an entity. One bank can have many customers, one person can have account at many banks.
These relationships are implemented in the physical design using conjunctive tables for M:M and by Foreign keys for 1:1, 1:M and M:1. When we reach the implementation part of the tutorial, we’ll see how this is performed.
Another concept we should be familiar with is Week vs Strong Entities. A strong entity is an entity that can exist on it own and have a primary key but a week entity can not exist on it own. A weak entity has a primary key consisting of the primary key of the entity it depends on and the unique identifier for the weak entity. Lets take for example, the entity tax payer and dependent. The dependent entity can not exist on it own, it depends on the tax payer entity. so the tax payer is the strong entity and the dependent is the weak entity. The tax payer can have its primary key and the dependent will have to be given a composite primary key consisting of the tax payer primary key and the unique identifier for the dependent, example ssn.
In some situation, the relation between entities lead to attributes. This attributes are called relationship attributes. For example, we have an entity student and an entity course. When the student and course entity are related, we can get an attribute like mark or grade which means the mark or grade that student get in that course. These type of attributes are shown on ERD as well.
Another thing to mention is Single value vs multi value attributes. A single value attribute means it takes at most one value while the multi value attribute takes more than one value.
In general Entities are represented with the name of the entity and a rectangle surrounding it. The attributes of the Entity are represented with the attribute name in a circle and a line drawn to connect the attribute to the entity.
The multi value attributes are differentiated with two circles surround it.
If one entity is related to the order, the relationship is show with a diamond and a line drawn from the diamond to each of the entities. The relationship that exist between the entities can be written inside the diamond that connects them. If there is a relationship attribute, it is connected to the diamond between the two relationships.
To represent a many type of relationship on an entity involved in relation, a small arrow is put at the point of contact of the line from the diamond connecting the two entities.
Weak entities are represented with double rectangles and the diamond that exist between it and another entity is also doubled. The line between the weak entity to the diamond is also doubled.
Another thing we have to consider in ERD is the participation of entities in relationship. There are two types of participation; partial participation and total participation. A partial participation is when the entity does not get involved in every instance of the relationship. A total participation means the entity is always involved in every instance of the relation. Take for example, weak entities. Because they rely on the strong entity, the relationship side of the weak is total participation but the relationship from the strong entity side is partial participation. To represent total participation for the relationship, a double line is used to connect the diamond representing the relation to the entity that is involved in the total participation. Partial Participation is just the normal single line connection to the diamond.
Another thing that comes to mind is composite vs simple attributes. simple attributes are attributes that can not be divided into further components but a composite attribute can be divided into components. Let’s take for example the entity person. we identified name as an attribute but we know name can be divided into first name, middle name and last name. so name is a composite attribute. Composite attributes are represented just as normal attributes but with the components broken apart and connected to it. The components will be represented as normal attributes. A line will then connect the components to the composite attribute itself. I’ll embed an image which will explain all this concepts at the end of the tutorial.
Primary keys of an entity are represented by underlying it.
Below is a graphical representation of of almost all of the discussion.
I’ll be writing on Normalisation and Relation Schema in the next tutorial. Keep in touch and lets do this together.
