Iv. Physical Data Model PDM (Physical Data Model)

1. Introduction to PDM

The physical data model (Physical Data Model) PDM provides the basic elements required for the initial design of the system and the relationship between related elements; the physical design stage of the database must be detailed on the basis of the background design, including the database stored procedures, operations, triggers, views and index tables;

 

Powerdesigner can be used to create various types of DBMS (Database Management System ), each type of DBMS contains a standard-defined file used to determine the associations between the PD and DBMS and provides an interface. Different DBMS can be created to generate different database scripts.

 

Main functions of the physical data model (PDM:

1. The physical design results of the database can be migrated from one database to another;

2. You can use reverse engineering to re-generate a physical model or conceptual model for the physical structure of an existing database;

3. You can customize standard model reports;

4. can be converted to oom;

5. Complete the detailed physical design of multiple databases (covering DBMS of various commonly used databases) and generate. SQL scripts for database objects;

 

1.1 Basic Concepts in PDM:

1) Table

2) Columns

3) view

4) primary key

5) candidate keys

6) foreign key

7) Stored Procedure

8) triggers

9) Index

10) Integrity check Constraints

 

1.2 create a PDM object:

1) create a table

2) create a column

3) create a domain

4) create a view

5) create a primary key

6) create a candidate key

7) create a foreign key

8) Create an index

9) create integrity check Constraints

10) create a stored procedure

11) create a trigger

 

1.3 manage PDM objects:

1) create a user

2) set the table user

3) set the physical properties of a table

 

1.4 check PDM:

1) Check option settings

(1) package)

(2) Table)

(3) Column)

(4) Index)

(5) Key)

(6) domain)

(7) view)

(8) Reference)

(9) Trigger)

(10) procedure (Stored Procedure)

(11) User)

(12) File)

2) correct errors/warnings

 

1.5 trigger and stored procedure/function:

1) create a trigger

Set trigger types (insert, update, delete)

2) create a stored procedure/Function

 

1.6 generate Database

1) select database → generatedatabase menu item

2) option settings

(1) Database

(2) User

(3) domain

(4) tables and columns

Primary Key and candidate key

Foreign key

Index

Trigger

(5) Stored Procedure

3) format settings

4) Select an object

(1) view packages

(2) Select All/cancel all

(3) Use the objects selected in the graph

 

1.7 create PDM:

1) create a PDM

(1) Select File> New Model Menu item

(2) Select physical data model from the model type list on the left.

(3) enter the model name on the right, select the DBMS type from the DBMS drop-down list, and select physical divisor from the first divisor drop-down list.

2) Forward Engineering: Generate PDM from CDM/LDM

3) Reverse Engineering: generates PDM reversely from existing databases

 

PDM generated by forward and reverse engineering will be separately organized into one piece.Article.

 

1.8 script generated by PDM:

In the end, PDM needs to be imported into our database. PDM can generate corresponding SQL statements for different databases, or directly deploy scripts in the database. This can be selected when generating the database.

Generally, the SQL script is compiled and then executed in the database.

 

 

Ii. Example of PDM 2.1 create a new PDM

2.2 set the display parameters of PDM

Tools --> display preferences

 

2.3 set the model attributes of PDM (usually set when releasing resources or submitting designs)

Model-> model Properties

 

2.4 set PDM Model Options

Tools --> Model Options

 

2.5 create a table

On the PD tool panel, select a table, create a table in the workspace, right-click the release table, and double-click the table to edit it.

2.6 table columns and their settings

2.7 details settings

2.8 Standard Check Constraints

2.9 completed the design of a table

Create reference integrity (Reference refers to the connection between the parent table and the child table, defines the reference integrity constraints between the corresponding columns of the two tables, the foreign key is linked to the primary key, and the reference is established between the two)

 

Based on the difference in the corresponding nature of the reference connection, you can divide the connection into key connections and user-specified Column Connections:

1) Key Connection: connect the primary key or candidate key in the parent table to the foreign key in the child table;

2) user-specified column join: connect one or more columns in the parent table to the corresponding columns in the child table (less useful)

2.10 set the relationship between two tables (set the reference between two tables)

2.11 connect the primary key, candidate key, and foreign key, or establish a connection between user-specified Columns

2.12 definition integrity

 

2.13 domain settings (this helps to identify the type of information, standardize the mountain features of columns in different tables, and associate the data type, check rules, and mandatory information to the domain through the domain)

Model --> Domains

 

 

2.14 rebuild the keys in the table

Tools --> rebuild objects

 

2.15 create an index

Double-click the table to open the edit, select the corresponding column, and click the point attribute (step 2 ).

 

2.16 create a view

Oracle view description

Http://blog.csdn.net/tianlesoftware/article/details/5530618

 

Select a view on the tool panel and create a view. Double-click View to open the configuration.

 

On the SQL query page, define the SQL statements of the view.

1. Union merges two or more select statements without repeated data;

2. Union all merges two or more select statements, and the data can be repeated;

3. Intersect merges two or more select statements to show the intersection of data;

4. Minus merges two or more select statements to display the completion set;

Click Edit on the right of the query column to edit the SQL statement.

Complete instance

 

Iii. generate an SQL script

In general, it is the first to draw the CDM E-R, and then the CDM into PDM. The objects in PDM are the same as those in our database.

There are two ways to deploy PDM:

(1) generate an SQL script

(2) Deploy the script directly to the database, that is, directly create our objects on the database.

 

Database --> Generate database, the following window is displayed:

 

Script generation: generate an SQL script. Do one file on need to generate multiple SQL statements according to the table (optional by default, that is, no)

Direct Generation: Execute the script directly in the database. Only one of the two can be selected. Generally, an SQL script is generated directly.

Automatic archive: generate an SQL statement and generate an APM file.

Check model: powerdesigner defines the maximum length of table names and field names of each database. This length is used by powerdesigner as the check model, however, the length of this definition is not necessarily the same as the actual allowed length of the database. If you want to pass the PD check, you can modify the settings in the PD. (The PD conventions for different databases are actually saved in an XML file with the extension. XDB )! Or you can choose not to check the model when generating the database.