Find out what symbols are used in the Entity-Relationship Diagram (ERD) and what they mean.
The most popular notation in ER diagrams is the Information Engineering (IE) notation, also called crow's foot notation. This is the default ER diagram notation used in Vertabelo.
There are a few standard symbols used in logical and physical ER diagrams, and some useful additional non-standard symbols that you can use in Vertabelo. We'll discuss them in this article.
Standard Symbols Used in Logical ER Diagrams
When creating a logical ER diagram, you typically use the following symbols:
Entity
The entity is denoted by a rectangle. This rectangle is divided into two parts: the entity name at the top and the entity attributes in the lower part. You can list the attributes with or without their types. Here's an example of an entity in Vertabelo:
The M annotation next to the attribute stands for "mandatory" – you use it for attributes that can't be left empty. In other words, M attributes must have a value.
The PI annotation stands for "primary identifier". You use this for the attribute(s) that uniquely identify the whole entity.
You can also specify attributes' data types, as shown below:
Relationships
After creating entities, you may need to specify the relationships between them. These relationships are denoted in diagrams by lines. Each relationship is one line and has cardinality and mandatory attributes.
Relationships can be one-to-one, one-to-many, or many-to-many. The many side is denoted by the crow's foot symbol; one is denoted by a single line.
Each entity in a relationship can be mandatory or optional. If the entity is mandatory, we denote it by a vertical segment. If it's not mandatory, we denote it as an open circle.
Let's consider some examples:
This is a one-to-one relationship. Each principal is a principal in only one school and each school has only one principal, so there are no crow's feet.
Each entity is mandatory – each school needs to have a principal and there is no principal without the school. This is shown by the vertical bars near each entity.
Above, we see a many-to-one relationship. Each school has many students (hence the crow's foot near the students entity) but each student is only in one school.
Each entity is mandatory (hence the vertical line). Each student must be assigned to a school and each school must have some students.
This is a many-to-many relationship. Each school has many teachers and the teachers can teach in a few different schools. In this kind of relationship, there are crow's feet at both ends.
The teacher entity is mandatory – each school must have some teachers. On the other hand, a teacher can be currently unemployed but still in the database. So the school entity isn't mandatory, which we denote by an empty circle.
You can read more about many-to-many relationships here.
Nonstandard Logical ER Diagram Symbols Used in Vertabelo
If you're dealing with complicated databases, the above notations may not be enough. That's why Vertabelo also supports some nonstandard functions (e.g. association and inheritance) you can use when creating your logical ER diagram. Using these nonstandard symbols can make a programmer's life a lot easier.
Association
An association is a special representation of a relationship between two entities – specifically, a relationship with additional attributes. In this way, you can realize a many-to-many relationship with attributes. Or you can implement a ternary relationship, which cannot be achieved through a simple relationship between entities.
Associations are represented in a diagram by a rectangle with rounded corners. Like entities, associations have names (displayed at the top) and attributes (displayed in the lower part of the rounded rectangle). You can (but don't have to) specify the attributes' types. You can also leave comments about the association or edit its appearance.
Here's an example of an association in a logical diagram:
This is an association between students and teachers. The relationship is represented by a rectangle with rounded corners. Inside we have the association name and its attributes. You can choose their data types and specify whether they are mandatory or optional. On the other hand, since it's an association and not the entity, there is no such thing as a primary identifier. As with basic relationships, you can also specify cardinality for the entity-association relationship.
Inheritance
The other nonstandard feature Vertabelo allows you to use in logical ER diagrams is inheritance. Traditional ER diagrams don't have this concept, but it has become a standard modeling technique.
Inheritance is denoted by an arrow that points from the child entity to the parent entity. It's a UML notation. The child entity inherits all parent's attributes, their types, and whether they're mandatory and are primary identifiers. You can also add some other columns if you'd like.
Take a look at the following example of inheritance:
A principal is a specific type of teacher, so the principal entity inherits from the teacher entity; you want the principal to have all the attributes a teacher has. Here, principal is the child entity and teacher is the parent entity; thus, the arrow points to the teacher entity. If you want the principal to have some additional attributes (e.g. an office) just list these attributes in the lower part of the principal entity.
Now that you know the symbols, feel free to create your own logical ER diagram. Not sure if you understand everything mentioned above? Or do you want to know more? There's no better place than the Vertabelo Data Modeler documentation to learn answers to your questions.
Whether you already have a logical ER diagram or you feel like you don't need it, you can create a physical ER diagram in Vertabelo. This kind of diagram is much more specific than the logical ER diagram. As a matter of fact, it's an evolution of the logical diagram. It has some other symbols and other functions, but you may find it quite similar to the logical diagram.
Symbols Used in Physical ER Diagrams
When creating a physical ER diagram, you typically use the following symbols:
Table
The table is similar to the logical diagram's entity. It's denoted by a rectangle divided into two parts: the table name at the top and the table columns lower down. You list the columns with their data types; you can choose them from a list or simply type them. Here's an example of a table in Vertabelo:
N stands for "nullable" – you use it when there can be NULL values in a column. Otherwise, the NULL values won't be accepted. Here, the middle_name column is not mandatory, so this column is nullable.
PK stands for "primary key", which is a unique key used to identify the whole table. Here, the student should be identified by their id – a column with unique, non-repeating values. It may happen that two students in the same school have exactly the same name, so you must be able to distinguish them using another unique identifier – in this case, the id column.
Note that a primary key column can't have NULL values. Vertabelo makes sure you don't make such mistakes by automatically blocking the NULL option when you indicate a column is the primary key.
Tables also have one or more foreign keys when they are in a relationship with other tables. Foreign keys are denoted by the FK notation.
References
Just like the relationships between entities in the logical ER diagram, references between tables are denoted by lines. Each reference is one line and has cardinality and mandatory attributes.
References in the physical ER diagram can be one-to-one or one-to-many. Many is denoted by the crow's foot; one is denoted by a vertical line. There are no many-to-many references in the physical diagrams.
Each table in a reference can be mandatory or optional. If the table is mandatory, we denote it by a vertical segment. If it's not mandatory, we denote it by an open circle.
Here are some examples:
This is a one-to-one reference. Each principal is the principal in only one school and each school has only one principal, so there are no crow's feet.
Each table is mandatory – each school needs to have a principal and there is no principal without the school. This is shown by the vertical bars.
The principal_id column in the school table is created automatically in Vertabelo, along with the foreign key symbol. It's also automatically removed when you remove the reference between the tables.
Above we have a many-to-one reference. Each school has many students (hence the crow's foot near the students table) but each student is only in one school.
In case you decide that you still want to keep the students who graduated or dropped out of school in the database, we've made the school table optional; this is denoted by an open circle. But each school still must have some students. Thus, the student table is mandatory (which is denoted by the vertical line).
Again, the student_id column in the school table is created automatically and it's a foreign key. Also, because the school table isn't mandatory, the foreign key can be NULL – hence the nullable symbol.
Other ER Diagram Notations
These are the basics you need to create an ER diagram in Vertabelo. Of course, there's more to it when it comes to physical ERDs. You can create constraints, default values, unique keys, etc. Those objects are denoted in the diagram, but the information about their definition is kept in the physical data model in Vertabelo. You can explore them using the Table properties panel or Model properties panel.
Now that you know the default notation, you should be aware that Vertabelo also supports other notations: UML and IDEF1X for logical diagrams; UML, Barker's notation, and IDEF1X for physical diagrams.
You'll be pleased to know that Vertabelo also lets you create physical diagrams from logical diagrams automatically. You can even generate the SQL script automatically from the diagram. It simplifies everything!
Thanks for reading this article. If you have questions or comments on ERDs or diagram notations, tell us about them in the comments section!
Source: https://vertabelo.com/blog/symbol-in-erd-diagram/
Posted by: martychinge0193577.blogspot.com
