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 (connections) between the relevant rows of the diagram nodes. The relationships between tables are represented by the connections between primary and foreign keys. A primary key is a table column or a group of columns (rows in diagram nodes) that ensure unique row identification in the table. A foreign key is a table column or a group of columns (rows in diagram nodes) that together with a primary key represent a reference between two database tables which is also called referential constraint.

One of the unique features of JDElite Flowchart Builder is the method of assigning the primary-foreign relationships using simple drag-and-drop. The popup handles appear under the mouse only on the side of rows with designated keys. Selecting a primary key handle simultaneously activates all foreign key handles on all potentially accepting nodes and vice versa, selecting a foreign key handle activates all potential primary keys. Creating a link by drag-and-drop gesture automatically creates the referential constraint corresponding to this relationship. The link connects the two diagram nodes at the locations of the respective keys. As a result all links show unambiguously the primary-to-foreign keys relations. Arbitrary connections are not drawn.

Create diagram nodes, add rows, populate each row, assign constraints and indexes, optionally designate cardinalities, all with the help of popup dialogs. 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 following the described features. 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. 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. A 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 this selections, a graphical link is created automatically in the diagram.

There is a two-way synchronization between a foreign key-to-primary key link and the REFERENCES constraint. As it was pointed above, creating a link will also automatically creates a referential constraint.

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.