is intended to be used in all three levels of designing database
model, such as conceptual design,
logical design and
finally design physical models
with specific features depended on particular RDBMS. This means that
Ermodeller enables modelling of database in three steps that matches
to the development cycle of a database. Conceptual model is the most
suitable for your system analysis, while logical model can be used
for a particular database design and physical models are intended to
be used in the stage when particular RDBMS is selected and you
prepare database to deploy on to server.
The following text uses the terms of database
models, so at the beginning, the terms overview is provided to ensure
unified terminology for the following document.
that are common for all model types.
represents a data type of column or attribute and
defines all attribute's possible value. For example, domain can
define an enumeration type.
Type is data type that is not bounded to a particular RDBMS. It may
have the similar function as a Domain, but it does not provide a
mechanism of constraining data to certain values. For example,
logical type can be
created view of some data in database. It may display some related
data that are stored in different entities in the database.
entry is an attribute/column contained in all entities to hold some
kind of meta-data. For example, each table may contain tracks the
last modification date of each row.
Conceptual Model Elements
model elements are intended to be used during the phase of conceptual
It represents a type of object that is used in the system and stored
record of the entity type in the database.
holds a single data in the entity type.
is an attribute or collection of attributes that has unique values in
the entity and allows the entity's identification.
identifier is chosen identifier for entity's identification.
Relatiotionship represents the relationship of two entities. It is
defined by its cardinality (1 to 1, 1 to N and M to N). Entities
participating with the relationship may be defined as mandatory or
optional participation and the relationship can be defined as
identifying or non-identifying.
Logical Model Elements
chapter describes the elements of the logical model. They usually
corresponds with elements of physical model, but they includes
Table is a collection of columns and rows to store data of certain
Foreign key is a relationship between two tables and is represented
by columns in a detail table that refers to columns of the primary
key of the master table. When the foreign key is identifying, all
columns of the foreign key are also primary key columns of the detail
identified by foreign key are called
key corresponds to primary identifier of conceptual model.
Columns corresponds to attributes of conceptual module.
Physical Model Elements
type is the type of attribute that is defined by particular DBMS.
model (schema) provides database model in the most general way. It
doesn't care about how the relationships are stored in database, so
binding tables or foreign keys are not irrelevant for this model. It
only maintains entities, their relationships and attributes.
Attributes' types are defined via logical types, to ensure
portability across different RDBMS.
model is some kind of transition between conceptual and physical
model. This model reflects physical model structure with binding
tables and foreign keys, but it does not care about RDBMS specific
types and other specific features. Tables' columns are mapped to
logical types as attributes are in conceptual model.
model defines schema used for particular RDBMS with its database
types and other specific features, such as types, triggers,
procedures and so on. Physical model can be used to generate SQL/DDL
scripts for the particular RDBMS.
particular elements of all models that corresponds to themselves is
ensured that changes in properties in one layer are transferred to
other layer. For adding and removing is possible only top down
access, that means that e.g. for entity is added to conceptual are
generated appropriate objects in all dependent layers, but on the
other hand, the table added to concrete physical model is not handle
to above layer in any way. This considerations allow to create
specific tables on specific DBMS and do not impact any other models.
types are one of the most important and valuable features of
Ermodeller. The concept of logical types is going much further than
in other similar CASE tools. First of all, logical types provides
self-descriptive types to define types of more columns consistently
Logical Types as Self-Descriptive Data
types enables the general and self-descriptive
identification of column types. For example, all primary key columns
may use a type
Logical type is centrally editable, so it ensures to all
columns have the same type, for example
This approach speeds up your development process by removing
additional costs connected with specifying type's parameter and it
makes types of your columns more consistent.
Mapping Logical Types to
logical types to physical types is important for specifying the
concrete physical type for in given RDBMS. This mapping should
respect different physical types in different RDBMS. In other words,
logical type must be able to be mapped to one physical type for each
DBMS at the same time. For example, logical type
will be mapped on
for DBMS Oracle and on
INTEGER for MySQL.
Logical Types as Abstraction of Physical Types
logical types to physical types associates an idea of abstracting
common physical types of all RDBMS into
corresponding logical types. For example, we can introduce types like
and provide mapping of those types to concrete physical data types
for each DBMS.
Parameters in Logical Types
of abstracting physical data types is that physical types allow to
define their size – e.g.
If logical types should be used as an abstraction of physical types
they must be able to handle parameters as well. For example, logical
should provide parameter
Then , the physical type mapping for Oracle will can be to type
instead of mapping to static values.
Mapping Logical Types to Other
logical types as an abstraction of physical types
as stated in paragraph may lead into overriding the original use
case of logical types as stated in paragraph , because column
can be now mapped for example to
but we need to define parameter consistently for all columns in
central place. Mapping of type
to physical types is inefficient, because that mapping is already
So logical types should support to be mapped on other logical types,
that define physical types mapping. Note that logical types mapping
is allowed only to level 1 in order to prevent indefinite loops of
mapping allows users to define type
that abstracts all integer types for all DBMS and then create type
that is mapped to type
The above restriction of level 1 means that user cannot construct
LTYPE -> LTYPE2 ->
LTYPE3, but also it prevents from
constructing something like
LTYPE2 -> LTYPE1. Furthermore, I
would say, that for all use cases is encapsulation at higher level
irrelevant and don't needed.