Entity Relation Model (ER Model) is a method used to represent the design of a database in a visual diagram. It is a conceptual data model that focuses on the types of entities, attributes, and relationships between them. The ER model is an important part of the process of creating a well-designed database application and helps in understanding the data better.
Components
Entity Types:
Entities are objects or concepts that exist in the real-world and are represented in the database. They are represented by rectangles in the ER model.
Strong Entity Type:
These entities can exist independently and are represented by a rectangle with a single border.
Weak Entity Type:
These entities cannot exist independently and are dependent on another entity. They are represented by a rectangle with a double border.
Relationships:
These are connections between different entity types and represent the information that the database is built to preserve.
Identifying vs Non Identifying Relationship:
Identifying relationships connect a weak entity to a strong entity and the weak entity is identified by the key of the strong entity. Non-identifying relationships connect entities that have their own keys or partial keys.
In summary, ER model is a way of representing the design of a database in a visual format, it helps to understand the relationship between entities and their attributes. It is divided into entities and relationships where entities have a strong and weak type and relationships have identifying and non-identifying types.
Total and Partial Participation:
The participation of an entity type in a relationship can be total or partial. Total participation means that all instances of the entity type are part of the relationship, represented by a double line in the ER model. Partial participation means that not all instances of the entity type are part of the relationship, represented by a single line. This property is only associated with many-to-many or one-to-many relationship types.
Attributes:
An attribute is a piece of information that belongs to an entity or a relationship, but not an entity type or relationship type. An entity may have one or more attributes while a relationship is not required to have any attributes.
Cardinalities:
Cardinalities describe the number of occurrences of one entity in relation to another. They are associated with entity types in a relationship and represented by a number or letter near the relationship connector. For example, a one-to-one relationship would be represented as 1:1, a one-to-many relationship would be represented as 1:N, and a many-to-many relationship would be represented as M:N.
Symbols:
Example
In order to create an ER diagram for XplainD, we need to take into consideration the following requirements:
The main entities involved are articles and authors.
Important information to be stored about articles include the ID, title, description, body, related articles, creation time, and editing time.
Information to be stored about authors include the ID, name, address, and LinkedIn profile.
Other important requirements include tracking which author edited which article at what time, and keeping track of the location of the article in the hierarchy.
Step 1:
To begin, we will identify the main entities and create attributes for all relevant information about them. Any information that cannot be represented as an attribute, such as the editors of an article, will be addressed in the next step.
The attributes for the article entity include: ID, title, description, body, related articles, creation time, and editing time.
The attributes for the author entity include: ID, name, address and LinkedIn profile.
Notice that “related pages” and “images” are multivalued attributes for the article entity, meaning they can have multiple values associated with them.
The attribute “address” for the author entity is a composite attribute, meaning it is made up of multiple sub-attributes such as street address, city, and country. Additionally, “age” is a derived attribute, which means it can be calculated by the application using the “date of birth” attribute.
Step 2:
Next, we identify the relationships and their types that will preserve the required information.
Author of an Article: The relationship between the author and article is represented in the ER diagram. An author can write multiple articles, and an article has only one author, so the relationship is one-to-many (one author to many articles).
In the above picture relationship, the double line represents total participation, meaning all articles must have an author. The single line for the Author entity represents partial participation, meaning there may be authors who have not written any articles.
Editors of an Article: In this relationship, the connection between the article and the authors who have edited it, as well as the time of edit, needs to be established. Since an article can be edited by multiple authors, the relationship is many-to-many. This relationship is represented as such in the ER diagram.
Notice that from the above relationship, we cannot determine the last edit time of an article, so we do not need to save this information as a separate attribute. Instead, we add a derived attribute for “Time Edited”.
Location of Article: To avoid the tedious task of saving the hierarchy for each article as an attribute, we store this information in a relationship. The relationship is recursive, meaning it relates an entity to itself. The complete hierarchy of an article can be determined on the application level, so we add a derived attribute “Hierarchy” to the article entity.
Result
