PowerDesigner's PDM (physical concept model)

I. Overview of PDM

PDM (physical data model), generally understood, is to display and design the database graphically in PowerDesigner.

The basic concepts involved in PDM include:

• Table
• Column
• View
• Primary key;
• Candidate Key;
• Foreign key;
• stored procedures;
• Trigger
• Index
• Integrity check constraints;

These are the concepts of the database, not suitable for powerdesigner inside the statement.

Second, create PDM

There are 4 ways to create PDM:

• Create PDM directly using the design environment;
• Create PDM from an existing database or reverse engineer with SQL script;
• The internal model generation method of CDM is used to establish PDM.
• Based on the model's internal generation method, the PDM is built from the class diagram in Oom.

The use of CDM to generate PDM is most consistent with the design approach, but in many enterprise development process, are in the design environment directly set up PDM, because most enterprises do not have the money to pay so much salary you design so many pictures.

　　1. Establish PDM directly in the design environment

The steps to establish PDM directly in the design environment are as follows:

　　1. Select the File->new model in the PowerDesigner main window and select the physical Data model option on the left side of the open creation

　　

　　2, the meaning of the right option

• Model name: Names of models;
• DBMS: Database type, you can also click the following folder button, select "Browse Folder", select the XML file as the target (CDM to PDM);
• Share the DBMS definition: shared database definitions;
• Copy the DBMS definition in model: Copy from database definition for CDM to PDM;
• First Diagram: Create a PDM, by default there will be a Diagram, here is the choice of this Diagram type;

　　3. Meaning of Extended Model definitions tab

When developed through PowerBuilder, the generated PDM can get the extended properties of the table and column from the catalog table if PowerBuilder is selected.

　　

　　4. Click "OK" button to open the new PDM design window.

　　

The workspace includes the Browse window on the left, the design window on the right, the Output window on the lower side, and the floating tool window, which you can use to design PDM in the design window using the icons in the tool window.

5, the role of various icons

　　

Before the PDM is established, the display parameters of PDM can be defined to meet the display requirements of PDM.

　　2, define the display parameters of PDM

　　1. Tools->display Preferences open the Settings window and set the display parameters for the entire model in the General node.

　　

Each option has the following meanings:

• Window color: Windows colors;
• Unit: Unit of length measurement. inch (feet), Millineter (mm) and pixel (pixels);
• Grid: Grid lines;
• Diagram:
• Show Page delimiter: Displays the paging line;
• Constrain Labels: Whether you need to limit the distance between the tab and the Connection object;
• Content node: A parameter that sets the display style for each type of object in the graphics window PDM.
• Format node: Sets the display format for each type of object, such as size, monetization color, fill color, shadow, font, and so on;
• Modify: The button can be set in more detail;

　　3. Defining the model properties of PDM

The methods for modifying model properties in the Model Properties window are as follows:

　　1. Select the Model->model propertise command or right-click on the background of the graphics window and select the Properties command from the popup shortcut menu.

　　

The meanings of each option are as follows:

• Name: Names;
• Code: Codes;
• Comment: Comments;
• File name: file name;
• Author: Author;
• Version: Versions;
• DBMS: Database management System
• Database: DB name
• Default diagram: The graph that is displayed when the model is open;

　　2. Click the Create icon behind the database to enter the database Propertise window. You can configure the CREATE Database option in the window

• General: Common Properties;
• Name: Names of the databases;
• Code: The data of the database;
• DBMS: the type of database;
• Rules: rule;
• Script: Inserting scripts into the beginning and end of the database SQL script;
• Physical options: A physical option that the database can take advantage of;

Third, create the table

To create a table, just click on the table icon on the toolbar.

The meanings of other property pages are:

Column, Trigger (trigger), keys (key), Procedure (stored procedure), rules (Rules), Indexes (index), and check (constraint);

　　

Iv. Creating columns

　　1. Create columns

1, the basic properties of the column

The basic properties of the column are as follows:

• Name: column name;
• Code: column codes;
• Data type: datatype;
• Domain: field as the data type;
• Comment: Comments;
• M: Non-empty;
• P: Primary key;
• D: Displayed in the graphics window;

　　

　　

Select a column, click the propertise icon (or double-click the column), and open the Column Properties window, where you can define additional properties for the column.

　　

The General tab properties are as follows:

• Displayed: Whether to display in graphical symbols;
• Foreign key: External key;
• Computed: Computed columns;
• Mandatory: Non-empty;
• Identiry: self-increment;

The Detail tab can define the following properties:

• Column fill Parameters:null values (a percentage of the number of nullable columns allowed), distince values (percent of allowable columns of different values), and average length (the average length of the column values);
• Test Data Parameters:profile (the value of the tests). Click the list button to the right, you can further define the value of the test method, can be a character, numeric or date/time type;
• Computed expression: Define computed column expressions;

　　2. Create a computed column

A computed column is the result of an expression that computes the value of another column.

(1), in the General Data Constraint Definition window of the column, click the Edit with SQL Editor button to the right of the computed expression list box to pop up the computed column expression Definition window:

　　

　　

When the database chooses MySQL, there is no such box (because MySQL does not have the concept of computed columns).

　　3. Constraints

Standard Checks tab, where you define constraints.

• Values:minimum (minimum), Maximum (maximum), and default.
• Characteristics:format (display format), Unit (unit), uppercase (capital letter), lowercase (lowercase letter), and cannot modify (not allowed to modify).
• List of values: Defines a list of values.

　　

　　4. Naming constraints

Additional checks tab specifically for defining naming constraints

　　

　　5. Create a sequence

Double-click the column to enter column properties

　　

　　

• Start with: Start number
• Min Value: minimum;
• Max value: maximum;
• Incremented by: increment value;

V. Creating references and referential integrity

A reference is a connection between a parent table and a child table, which defines referential integrity constraints between the corresponding columns in two tables.

　　1. Reference Model Settings

Model options determine the characteristics of the reference, and you can open the Model Options window by Tools->model options.

　　

Suggest that the two auto what properties are also tick off it, manually, it seems a little ugly to understand the meaning of these two options.

Relationship of model item to connection

　　

Option meaning:

• Unique code: Indicates that the reference code in the model is unique;
• Auto-reuse columns: Indicates that the half-table primary key has the same Code column or the child table column is not the foreign key of the other table, then the column is treated as the foreign key of the child table;
• Auto-migrate columns: Indicates that when a reference is generated, the parent table's primary key is migrated to the child table as a foreign key;
• Domain: Indicates that if the Auto-migrate columns and domain check boxes are selected, the primary key's domain is migrated to the foreign key when the reference is established;
• Check: Indicates that if the Auto-migrate columns and check check boxes are selected, the check parameters of the primary key are migrated to the external key when the reference is established;
• Rules: Indicates that if the Auto-migrate columns and Rules check boxes are selected, the business rules for the primary key will be migrated to the foreign key when the reference is established;
• Primary key: Indicates the reference connection primary key column to the external key column;
• User-defined: Indicates that the reference does not generate a connection;

The meaning of auto two check boxes:

　　

　　2. Create references and define related properties

Specific ways to create references:

1. Click the reference icon in the Palette toolbar of the PDM model;

　　

2. Double-click the newly established connection to open the Reference Properties window, where you can modify the individual attributes of the reference:

　　

• Name: reference name;
• Code: reference codes;
• Comment: Comments;
• Parent table: A reference to the parented tables;
• Child table: referenced sub-tables;
• Generate: Whether to generate references in the database;
• The Joins tab allows you to define the connection between the table and the table;

3.Join (connection) can be used to connect primary keys, candidate keys and external keys, or to establish a connection between user-specified columns;

　　

4. The connection can be created automatically by the model options in the previous section, or manually, or, if specified manually, by using the Reuse columns icon, the Migrate columns icon, and the Cancel Migration icon for column reuse or migration.

• Reuse Columns: Reuse columns that exist in the child table and are the same as the parent table;
• Migrate Columns: Indicates that the primary key of the parent table is migrated to the child table as a foreign key;
• Cancel migration: Indicates that all columns migrated from the parent table to the child table are deleted;

The 5.Integrity tab is used to define referential integrity. Primarily used to set the effect on child tables of data that modifies or deletes a reference column in a parent table.

　　

• Constraint name: Constraint name, which is used when generating SQL scripts.
• Implementation: the way of realization;

Option meaning:
Declarative: Declarative, the referential integrity constraint is defined as a special reference.
Trigger: The validity of data is maintained by a trigger in the corresponding DBMS;

• Cardinality: Cardinality. Represents the minimum and maximum number of instances in a child table that may be owned by each instance in the parent table.
• Update/delete constraint: Indicates how the child table column values are modified after the parent table column value is modified:

None: Modifies or deletes the parent table without affecting the child table;
Restrict: You cannot modify or delete a value from a parent table if there is one or more corresponding values in the child table;
Cascade: A colleague who modifies or deletes a value in a parent table modifies or deletes the corresponding value in the child table;
Set NULL: The values in the parent table are modified or deleted while the corresponding value in the child table is set to null;
Set default: The colleague who modifies or deletes the values in the parent table sets the corresponding values in the child table to the defaults;

• Mandatory Parent: Whether to force the foreign key column values in the child table must have corresponding column values in the parent table;
• Check on commit: whether to validate referential integrity at commit time;
• Change parent allowed: whether the value of the reference column in the parent table is allowed to be modified;

6. In addition, the text information displayed on the reference graph symbol can be modified to meet the needs of different systems.

In this PDM model window, select the Tools->display Preferences command, open the Display Preferences window, and click the Object view->reference node.

　　

• Name: Names of references;
• Constraint Name: Names of referential integrity constraints;
• Join: Two tables The connection name of the same column;
• Rederential Integrity: referential integrity;
• Cardinality: base;
• Implementation: How to implement the referential integrity;

The following are examples of setting referential integrity:

　　

VI. Create a domain

In PDM, the use of domains helps to identify types of information and makes it easy to standardize the data characteristics of columns in different tables. A field defines a set of valid values for a column that can associate information such as Data Type, Check, Rule, mandatory, and so on to the domain.

　　1. Create a domain

1. Open the PDM model, select the Model->domains command, and click on a blank line to add a row. Enter names, codes, and data types in the name, code, and DataType fields, respectively;

　　
2. Click on a blank line to add a row;
3. Double-click the arrow at the front of the line to enter the property settings:

　　

The main meanings of the General tab are as follows:

• Name: Names;
• Code: Codes;
• Comment: Comments;
• Data type: datatype;
• Length: long;
• Profile: test data definition file;

The other tabs are similar to the previous.

　　2. Using abstract data types

When you select a data type for a domain, you can specify an abstract data type. Abstract data types are user-defined data types. ADT data types vary by database system, and the ADT data types that are allowed in the PowerDesigner system are the following table:

Type	Describe	Example
Array	Set of fixed-length elements	Varray Oracle8
List	Collection of non-fixed-length objects	Table Oracle8
Java	Java class	Java Adaptive Server Anywhere
Object	An object that contains a list of properties	OBJECT Oracle8
Structured	Structure containing the list of attributes	NAMED ROW TYPEDB2
Clr	. NET Common Language runtime	Microsoft SQL Server 2005

　　　　

　　1. Common Abstract data types

Open the PDM model and select the Model->abstract Data types command.

　　

Double-click Line entry

　　

　　2. Abstract data type of type Object

If you select the Object option in the Type drop-down list box, you can create an abstract data type object.

　　

Select the Attributes tab and enter it separately in the name, code, and data type fields.

　　

Vii. creating keys in a table

A key is a collection of one or more columns in a table that can uniquely identify a record. PDM supports two types of keys: Primary key, candidate key.

　　1. Primary key

Defining the primary key is simple, select the P check box as the primary key column.

　　

If you use reverse engineering, reverse from an existing database to a PDM model, you may not be able to generate a primary key or if you do not have the option to rebuild the primary key when you want the project database, you need to rebuild the primary key.

Methods for rebuilding the primary key:

1. Select tools->rebuild objects->rebuild Primary keys command.
2. Open primary Key rebuild window;
3. Select the check box before you want to rebuild the primary key table, OK;

　　

　　2. Candidate Keys

The candidate key (Alternate key) refers to one or more columns, and the column values for each record in the table are unique. Each candidate key generates a unique index or UNIQUE constraint in the database.

1. Open the Keys tab of the table, click in the name or code bar, and the system automatically adds a new key. Set the name and code of the key;

2. Double-click the arrow at the beginning of the new key row and select the Columns tab in the Key Properties window, which lists all the columns the key contains;

3, click the Add Columns icon, in the window list all the columns contained in the table, select one or several required columns;

　　

　　3, the constraint name of the key

When using PDM to generate database scripts, the keys are also included, and for the purpose of identifying and modifying them, you need to define a uniform constraint name for them. If not specified, the system automatically establishes a default.

Select the table that has the primary key defined, double-click the mouse to open the table properties, select the Keys tab, display all primary and candidate keys, select one, click Properties, and enter the name of the key in the Constraint Name text box.

　　

Viii. Creating an Index

　　1. Create an index

The specific methods for indexing are as follows:

1, in the Table Properties window, select the Indexes tab, blank line click, the system will automatically give the index name and code, you can modify as needed.
2. Click the Properties icon to open the entry for the Index properties,general tab meaning:

• Name: index names;
• Code: Index codes;
• Comment: Index comment;
• Table: tables containing indexes;
• Type: index, normal index, full-text index, XML index
• Unique: Single index;
• Cluster: Clustered index;

3, select the Columns tab, you can select the columns included in the index;

　　

　　2. Rebuilding the Index

The exact method for rebuilding the index is as follows:

Tools->rebuild objects->rebuild Indexes command, open index Rebuild (rebuild index);

　　

Each option has the following meanings:

• Primary key: Rebuilding primary key index; Right input primary key index name:%TABLE%_PK
• Other keys: Rebuilding candidate key index, right input as default candidate key index name:%table%_ak
• Foreign Key indexes: Rebuild external key index, right input is%REFR%_FK
• Foreign key threshold: Minimum number of records required to establish a foreign key index
• Delete and Rebuild: Remove an existing index before rebuilding the index
• Add missing indexed: Add only missing indexes

In the Selection tab, select the table for which you want to rebuild the index;

　　

Ix. Creating a View

There are two ways of creating a view:

1, select the table, tools->create the View command, to create a table containing all the fields of the selected tables;
2. Build an empty view and select the table or define the SQL statement directly.

The second way to do this is as follows:

Using the view icon of the toolbar, create a view and double-click into the View Properties window.

　　

The basic properties of its General tab have the following meanings:

• Name: The names of the views;
• Code: View codes;
• Comment: The purpose of the view;
• Usage: The purpose of the view;
• Query only: for queries only;
• Updateable: Used for query and modification;
• With CHECK option: To receive restrictions on the defined constraints in the table when inserting data into the view;
• Dimensional type: dimension types;
• Generate: Whether to generate views in the database;
• User-defined: Whether to access the Query editor when the user customizes the view;

Select the SQL Query tab to define the corresponding SQL statement for the view.

It is easy to write SQL statements by clicking Edit with SQL Editor to enter the SQL compiler.

　　

PowerDesigner's PDM (physical concept model)