Draw E R Diagram Using MySQL Database Engine

Introduction

  • A data model is a document of organized business data which is used as framework for application development. A data model provides the structure and format of data. A database model defines how data is stored, organized and modified inside a database environment. Flat model, Hierarchical model, Network model, Relational model, Dimensional model, and Object-Relational model are some common types of database models used in the industry.
  • A data structure diagram (DSD) provides graphical representation of database entities, their relationships and applicable constraints. The graphical presentation includes boxes and arrows to represent entities and the relationship among them. Entity relationship modeling is a widely use modeling method of creating a data model. An Entity Relation Model (E-R Model) provides a detailed logical representation of business data. An Entity-Relationship diagram or ERD in short is a graphical representation of the E-R Model.
  • The ERD is a powerful tool for communication between developers and database users. The primary components that are represented in an ERD are
    • Entity – Can be a person, place, object, event, or concept about which data is maintained.·
    • Relationship – Represents association among entities/entity types.
    • Attribute (s) – Property of an entity that is of interest to the business.
    • Constraints (Cardinality) – A characteristic of a relation showing number of instances of one entity related to number of instances of another entity.
  • Relationships and their characteristics together define the business rules represented in the ERD. Both entities and Relationships can have attributes that are represented in an ERD. The following is a step by step illustration of how to create an ERD using MySQL Database engine as the software tool.

Steps

  1. Identify the entities of the database according to the requirement of the organization or business.
    • There are some general guidelines for naming the entities. The name of the entity should be a singular noun, concise (for ease); if abbreviations are used then it should be specific
    • Spaces should be avoided. If more than one word is used then it should be separated by underscore ( _ ).
  2. Define the characteristics of the entities by attributes
    • The attributes can be singular noun or noun phrase
    • It should be unique for a particular entity type
  3. Build meaningful relationship among instances of one or more entities by creating relationship between them. The relationships mirror business rules of the organization
    • The relationship should be a verb phrase
    • It should clearly explain the action being taken
    • Cardinality - The relation can have one-to-one (1:1), one-to-many (1: m) or many-to-many (m: n) characteristics.
  4. Now open MySQL Workbench. To open MySQL Workbench go to start, click all programs, MySQL and then select MySQL Workbench.
  5. Open the platform to draw the Entity Relation diagram. From the menu bar click on file and then new model. Again use the menu bar to click on model and choose add diagram.
  6. Draw tables on the platform. The tables represent Business entities on the ERD. Select the table icon or press T from the vertical menu bar.
  7. Move the mouse to the platform and click on the location where you want to place the table.
  8. Name the table. Double click on the table to open a window (table editor) at the bottom. This window provides options to enter the table name (entity).
  9. Add attributes to the tables (Attributes of The same table editor provides options to add the columns (attributes) to the table (entity).
  10. Select the proper data type and primary key (Constraint). Select the data type for each attribute from the drop down list as per the requirement. Select an attribute or group of attributes that uniquely identifies that table. That becomes the primary key for the table. Also select which attributes are non-null.
  11. Perform the same operation for all the remaining entities which have been identified as part of the business requirements.
  12. Add relations to the tables (Entities).
    • Select the characteristic /type of relation (1:1, 1: m, m: n) from the vertical menu bar.
    • Then click on the two tables (one after other) to add the selected relation between the tables.
  13. Repeat the same action to add remaining identified relationships (Business rules) among tables (Entities).
  14. Save the entity relation diagram (ERD). From the menu bar click on file, select save model as and give a proper file name to save the ERD.
    • You can also save the ERD as a PDF file. To do this go to file, click on export and then select export as single page PDF. Give the file name and save it.

Tips

  • Carefully determine the type of relation as it is confusing.
  • Pay attention to the identifying and non-identifying relationships.
  • Follow the general guidelines for naming tables and columns. A best practice is to use capital for the first letter of each word.
  • Mark the primary keys and non-null keys.
  • It is important to clearly identify all the entities about which the business wants to maintain data.
  • Choose the data types properly.
  • It is important to define the all the necessary attributes of the entities and relationships.

Warnings

  • Incorrect identification of entities can lead to design complexities and time overhead.
  • Incorrect identification of relationship type can lead to wrong data model which can result in incorrect data retrieval and will incur time overhead to fix.
  • Avoid cycles (Three or more entities associated in a cyclic relationship) in ERD Diagrams. These can lead to incorrect data retrieval and incur time overhead to fix.

Sources and Citations

You may like

Retrieved from "https://kipkis.com/index.php?title=Draw_E_R_Diagram_Using_MySQL_Database_Engine&oldid=316732"