Transition to the physical model of the database - Databases: design

Go to the physical database model

The construction of the physical model of the database is completely based on the principles of organizing the data implemented in the logical model of the database. That's why usually developers, when designing a database, build a logical and physical model of the database at the same time. However, especially in large projects, creating a parallel logical and physical model is quite problematic due to the large volume of structural elements and the need to often reconcile the resulting models. In this connection, the logical model of the database is first designed, and then the project of the physical model of the database is created on its basis, which is brought to an acceptable level, considering the addition of views, referential integrity rules, procedural program modules, table allocation over the table spaces, database.

To move from a logical database model to a physical one, you do not need to completely rewrite the model. In the tools, the mechanism of transformation of the logical model into the physical one is implemented, with the preservation of all the predetermined rules for structuring and binding entities, as well as the application of certain rules of technical normalization, which provide for the elimination of many-to-many relationships, due to the impossibility of its physical implementation in the database, and categorization, which is also not provided for in databases, is identical to one-to-one communication.

To implement the transformation process, using IBM InfoSphere Data Architect, the item "Transform to Physical Data Model" is provided in the context menu of the database logical model. (Transformation into a physical data model). A similar action can be performed by selecting the menu item Data/Transform/Physical Data Model (Data/Transformation/Physical Data Model).

Selecting the menu item of the model transformation on the first step of the parameter definition dialog box requires the developer to specify the option of creating a physical database model (Figure 4.87):

• Create new model - selecting this option will create a new database model with a description of the tables that relate only to the model being created;

• Update existing model-Selecting this option changes the existing and specified in the "Target model" field. (Destination model) of the database model, replacing existing entities, links, keys, and other elements in the database.

Fig. 4.87. Determining the option of creating a physical database model


At the second stage of determining the transformation parameters, the developer specifies the DBMS (Figure 4.88), the rules of which should be used when describing tables and program modules. To do this, in the Database (Database), you need to select the required DBMS from the list, which is updated regularly from version to version of the tool and contains a fairly large number of database management tools used on the market. In the Version (Version) it is important to specify for which version of the DBMS the physical model of the database will be designed, because often in different versions there are different syntax and a set of standard functional program modules. In this example, in order to maintain the continuity of the manufacturer of the software used, we select DBMS IBM DB2 for Linux, UNIX and Windows and the latest available version of the "V 10.1" version.


Puc. 4.88. Specify the DBMS used for the model

The third step of defining transformation parameters indicates the naming rules for attributes, entities, and the use of data types. Here it is worth making some digression from the process of setting up the transformation (Figure 4.89). It is important to adopt the rules for naming database elements in the same way as programming, where there is necessarily an agreement of names used in software codes. The database name convention takes into account the principles and rules used to correctly use table names, fields (columns), procedures, functions, constraints, defaults, etc. This agreement will be applied not only within the framework of the transformation, but also after the primary formation of the physical data model to bring it to a form convenient for writing the program code for processing data and accessing database objects. For the model to be correctly represented, the developer defines the following naming convention parameters:

the rule for merging words in the table name, because, by default, it is assumed that the DBMS should not handle any names containing special characters and spaces;

- the rule for applying the character case for naming database objects;

- the rule for describing the surrogate key in part of the application of the method of arithmetic progression to calculate its value;

- the need to create indexes for primary keys and unique attributes (alternative keys);

- naming rules for individual instances of database objects.

In the third step, given the name convention, the developer defines

properties for converting names of tables, attributes and other database elements:

• Name Case-This property allows you to specify that all attribute and table names must be represented as small (lower), uppercase (large) characters, or in an existing variant in the database logical model;

• Data Type Defaults - the property specifies the data type with the base dimension parameters that must be set for the attribute if the database type specified in the database logical model is not represented in the DBMS;

• Physical Options/Surrogate key - A property defines the rule for representing a surrogate key in the database in one of two ways: an identifying column (field) or a counter (sequence);

• Physical Options/Index - this property specifies the need to create an index description for primary keys and attributes with a unique value constraint;

• Join table separator - this property defines a combination of characters that should be used for spaces and special characters when transforming entity names into table names.

Fig. 4.89. Model object transformation settings


The result of the operation is the database model (Figure 4.90), where, in accordance with the requirements of the DBMS, the database objects are described: tables, zero (columns), indices, constraints, defaults, etc.

Fig. 4.90. The result of the transformation of the model


When using ERWin Data Modeler to create a physical model from a logical model, it is enough to export the created physical model, specifying the physical model as the destination model. As a result, a file with a physical model will be created or, if a combined model is chosen, a logical-physical model with the transformation of the names of the model objects into the corresponding elements of the physical model.

Also We Can Offer!

Other services that we offer

If you don’t see the necessary subject, paper type, or topic in our list of available services and examples, don’t worry! We have a number of other academic disciplines to suit the needs of anyone who visits this website looking for help.

How to ...

We made your life easier with putting together a big number of articles and guidelines on how to plan and write different types of assignments (Essay, Research Paper, Dissertation etc)