Login | Register
My pages Projects Community openCollabNet

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.