Database ERDs

Database ERDs (entity-relationship diagrams) represent the physical structure of databases. Using JDElite Flowchart Builder you can create, edit, or delete tables, columns, keys, indexes, relationships, and constraints. To visualize a database, you can create one or more diagrams illustrating some or all of the tables, columns, keys, and relationships in it. ER diagrams contain two object types: entities and relationships. In physical databases the entities correspond to database tables. They are represented as diagram nodes displaying detailed table designs. Each row in a diagram node corresponds to a column in the database table. The relationships between entities are represented as diagram links between the relevant rows of the diagram nodes.

The ERD flowchart is saved as a file in JSON format. This file can be exported to a specific database script. Conversely, a database script can be imported to a JSON file as an internal diagram structure that can be visualized in the editor. Create diagram nodes, add rows, populate each row, assign constraints and indexes, optionally designate cardinalities, all with the help of popup dialogs. The structures of relational databases are determined by the relations between primary and foreign keys. A primary key is a column or a group of columns (composite primary key) that ensure unique row identification in a database table. A foreign key is a column or a group of columns that together with a primary key represent a reference between two database tables. The foreign key in one table has a reference to a primary key in another table. There could be multiple foreign keys from different tables referencing the same primary key.

Within database modeling, cardinality is the numerical relationship between columns in one database table and columns in another table. There are three basic types of cardinality relationships: one-to-one, one-to-many (many-to-one) and many-to-many. Here the cardinality is represented graphically as crow's foot symbols at the ends of the link connecting table nodes rows that contain the respective keys. The picture above demonstrate a one-to-one relationship, as well as the way to assign the cardinalities.

When one instance of the first entity may be related to more than one instances of the other entity, it is one-to-many relationship. An example is when we keep an authors catalog and we need to show the relation between an author and her/his books.

In a different scenario, in a library, we need to establish the relations between the plurality of authors and the plurality of books. This is a case of many-to-many relation. However, simply relating an author to a book turns out to be not sufficient. Most of the authors have more than one book, and some book titles are repeatedly used by different authors. In this case the pair author-book is an entity by itself. In order to identify it uniquely we need to introduce the AUTHORS_BOOKS table. This table has two foreign keys: author_name and book_title. They will represent a composite primary key, meaning that together they identify uniquely any author-book entity. We substitute the many-to-many relation with the additional table and two one-to-many relations respectively.

Database constraints are used to specify rules for data in a table. They can be column-level or table-level and can refer to a single column or multiple columns. Constraints are used to limit the type of data that can be contained in a table. This ensures the accuracy and reliability of the data in the table. If there is any violation between a constraint and the action on data, the action is aborted.Double-click the table node row that corresponds to a database column to bring the popup editor.

To assign a REFERENCES constraint, double-click a table node row that represents a foreign key. Using the dropdowns, select an existing table and the column name from that table representing the primary key of that table. When you confirm these selections, a graphical link is created automatically in the diagram.

Database indexes are internal data structures that help improve the speed of data retrieval from a database table. To create an index, double-click the table node title bar. Set an index to be UNIQUE by selecting the appropriate checkbox. Unique indexes are indexes that help maintain data integrity by ensuring that no rows in a table have identical key values.

Logical ERDs

Logical ERDs (entity-relationship diagrams) are the conceptual or logical models of business objects as components of systems. They are a convenient tool for business analysts or database designers, and are useful at preliminary design stages for physical databases, presumably following a whiteboard design. A conceptual or logical model identifies the business objects as building blocks. It defines the entities and their attributes, as well as the relationships between them.

A logical ERD consists of three basic node types: entity, relationship and attribute, their derived types, and the connecting links. Attribute nodes are entity properties and in physical databases they correspond to table columns.
As in database modeling, the cardinality specifies the number of occurrences of one entity associated with the number of occurrences of another entity. The association is assigned by using a relationship node, within which a label could be added to describe the semantics of the relation.

The logical models are closely related to the database models. The logical entities are the basis for physical database entities. Because of that, it is important to go through the process of logical modeling in-depth. JDElite's comprehensive editing features assure flexibility for quick logical diagram redesign, as well as clarity of presentation. This makes it possible to explore many different scenarios for even very large models in an amazingly short time.