PowerDesigner (vi)-Physical data Model (PDM reverse engineering)

Physical Data Model PDM

Physical data MODEL,PDM: After the logical structure of the database has been designed, the physical design needs to be completed, and PDM is designed to achieve this goal.

The physical data model is based on the common DBMS (Database management System) theory, and the real-world model established in CDM/LDM generates the corresponding DBMS's SQL language script. The SQL script is used to generate the real-world information storage structure (tables, constraints, etc.) in the database, and to ensure the integrity and consistency of the data in the database.

1. Basic Concepts

1.1 table, column, view, primary key, candidate key, foreign key

1.2 Stored procedures and triggers

1.3 Default and Rules

When a user does not assign a value to a column, the value that the database server automatically inserts for that column is the default. The rule is to allow or disallow those values to be inserted into certain columns, as specified in the management of the database. The default and rules together help the system maintain the integrity of the data. Default and rules can be associated with columns and user-defined data.

1.4 Integrity CHECK constraints (uniqueness and PRIMARY KEY constraints, referential integrity constraints, check constraints, etc.)

Completeness refers to the correctness and consistency of data in a database. By restricting and constraining database insertions, deletions, and modifications to data values, the database provides two ways to preserve data integrity: One is to define integrity constraints at the time of definition, and two to define rules, constraints, indexes, and triggers.

1.5 indexes (combined index, unique index, clustered index and nonclustered index)

2. Create a PDM

2.1 Building PDM

There are 4 ways to build PDM:

• Building PDM directly using the design environment
• Build PDM from existing data or database SQL script reverse engineering
• Building PDM from CDM using internal model generation method
• Building PDM based on the model's internal generation method of class diagram in object-oriented model

The first method is used here.

(1) Select the file-new model command and select the physical Data model option on the left side of the open window, as shown in 6.1.

Figure 6-1

(2) Enter the model name on the right and select a target DBMS from the DBMS drop-down list box.

(3) Select the Extended Model Definitions tab, and if you are using PowerBuilder to develop an application, save the extended properties of the table and columns to their catalog table when PowerBuilder connect to the database. Select the PowerBuilder check box to generate PDM to get extended properties for tables and columns from the catalog table. As shown in 6-2.

Figure 6-2

(4) Click the "OK" button to open the new PDM design window, as shown in 7-3. The workspace includes the left browser window, the design window on the right, the Output window on the lower side, and the floating tool window, which can be used to design the PDM in the design window using the icon in the tool window.

(5) Create a table using the table icon in the toolbar, create a reference relationship with the reference icon, create a view with the view icon, and create a stored procedure proceduce the icon. Other tools are used in the same way as toolbars in CDM.

(6) Create tables, columns, sequences, references and referential integrity, domains, indexes, views, stored procedures, etc. Effect 6-3 shows (only tables and columns are created).

Figure 6-3

3.PDM Reverse Engineering

The reverse engineering of PowerDesigner includes the reverse generation of the business processing model from the processing language (process Language) and the reverse generation of the object-oriented model from object language (objects Language). Reverse-Generate the physical data model from the database script back to generate the XML model from the XML definition (XML difinition). This article introduces PDM reverse engineering, which is the process of generating PDM from the user database of an existing DBMS or from an existing database SQL script, that is, the process of deriving its physical design from the physical structure of an existing database.

(1) Select the file-reverse engineer-database command, open the new Physical Data Model window, enter the model name and select the DBMS, and click the "OK" button, as shown in 6-4.

Figure 6-4

(2) Open the Database Reverse Engineering Options window and select the Options tab, 6-5.

Figure 6-5

(3) Select the Selection tab, select the reverse engineering method, using the script files (SQL script) or using a data source (the DataSource defined in ODBC).

(4) If the Using script files radio button is selected, click the Add Files icon in the window shown in Figure 6-7 to open the Select SQL File window and select one or more SQL files.

Figure 6-7

(5) If the using a data source radio button is selected, click the Connect to a data source icon in the lower right corner of the database Reverse Engineering Options window to open connect to a data Source Window 6-8, select an ODBC data source in the window or select click Configure to enter the Connection Profilre Definition window to configure the data source and enter the user name and password, click the Connect button, 6-9.

Figure 6-8

Figure 6-9

(6) Select the Target Models tab, click the Add Models icon to select an existing PDM file, and merge the results of the reverse engineering into one PDM. If you do not select a PDM file, a new PDM is regenerated.

(7) Click the OK button in the Database Reverse Engineering Options window to open the window shown in 6-10 to select all or part of the object in the databases that you want to reverse engineer. As shown in effect 6-11.

Figure 6-10

Figure 6-11

Note: If you encounter a com.mysql.jdbc.Driver clas not find error when connecting data, look for the path to the database connection jar that is configured in the environment variable. As shown in 6-12.

Figure 6-12

Summary: PMD reverse engineering can ensure that the existing physical database and PDM synchronization, but also for the System designer analysis, improve the existing database to provide convenience, which is the powerdesigner of the wonderful.

PowerDesigner (vi)-Physical data Model (PDM reverse engineering) (EXT)