Concepts
Ermodeller
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.
Terminology
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.
Common Elements
Elements
that are common for all model types.
Domain
Domain
represents a data type of column or attribute and
defines all attribute's possible value. For example, domain can
define an enumeration type.
Logical Type
Logical
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 USER_ID.
View
Dynamically
created view of some data in database. It may display some related
data that are stored in different entities in the database.
Cost entry
Cost
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
Conceptual
model elements are intended to be used during the phase of conceptual
design.
Entity type
It represents a type of object that is used in the system and stored
in database.
Entity
The
record of the entity type in the database.
Attribute
Attribute
holds a single data in the entity type.
Identifier
Identifier
is an attribute or collection of attributes that has unique values in
the entity and allows the entity's identification.
Primary Identifier
Primary
identifier is chosen identifier for entity's identification.
Relationship
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
This
chapter describes the elements of the logical model. They usually
corresponds with elements of physical model, but they includes
logical types.
Table
Table is a collection of columns and rows to store data of certain
entities.
Foreign Key
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
table.
Tables
identified by foreign key are called
weak entities.
Primary Key
Primary
key corresponds to primary identifier of conceptual model.
Column
Columns corresponds to attributes of conceptual module.
Physical Model Elements
Physical Type
Physical
type is the type of attribute that is defined by particular DBMS.
Conceptual Model
Conceptual
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.
Logical Model
Logical
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.
Physical Model
Physical
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.
For
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.
Logical Types
Logical
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
and meaningfully.
Logical Types as Self-Descriptive Data
Types
Logical
types enables the general and self-descriptive
identification of column types. For example, all primary key columns
id
may use a type ID.
Logical type is centrally editable, so it ensures to all id
columns have the same type, for example CHAR(5).
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
Physical Types
Mapping
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 ID
will be mapped on NUMBER(5, 0)
for DBMS Oracle and on
INTEGER for MySQL.
Logical Types as Abstraction of Physical Types
Mapping
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
LT_INTEGER
or LT_CHAR
and provide mapping of those types to concrete physical data types
for each DBMS.
Parameters in Logical Types
The problem
of abstracting physical data types is that physical types allow to
define their size – e.g. CHAR(5).
If logical types should be used as an abstraction of physical types
they must be able to handle parameters as well. For example, logical
type LT_INTEGER
should provide parameter length.
Then , the physical type mapping for Oracle will can be to type
NUMBER(length, 0)
instead of mapping to static values.
Mapping Logical Types to Other
Logical Types
Using
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 id
can be now mapped for example to
LT_INTEGER(5),
but we need to define parameter consistently for all columns in
central place. Mapping of type ID
to physical types is inefficient, because that mapping is already
defined by LT_INTEGER.
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.
This
mapping allows users to define type LT_INTEGER(length)
that abstracts all integer types for all DBMS and then create type ID
that is mapped to type LT_INTEGER(5).
The above restriction of level 1 means that user cannot construct
something like LTYPE -> LTYPE2 ->
LTYPE3, but also it prevents from
constructing something like LTYPE ->
LTYPE2 -> LTYPE1. Furthermore, I
would say, that for all use cases is encapsulation at higher level
irrelevant and don't needed.
