Design physical models in powerdesigner

The following methods are used to create a physical model in PD:

1. Directly create a physical model.
2. Design the conceptual model, and then generate the physical model from the conceptual model.
3. Design the logical model, and then generate the physical model from the logical model.
4. Use reverse engineering to connect to an existing database and generate a physical model from the database.

The physical model can intuitively reflect the structure of the current database. Database objects such as tables, views, and stored procedures in databases can be designed in physical models. Due to the consistency between the physical model and the database, the following describes the correspondence between the database object and the physical model object:

Table

When creating a physical model, you must specify the DBMS corresponding to the physical model. Here we use SQL Server 2008. After creating a new physical model, the system will display a toolbar dedicated to the physical model design:

To add a table to the physical model, click the "table" button and click "Model Design" to add a table. The system name is table_n by default, here, N will increase as the number of tables added increases. The added table does not contain any columns ,:

Click the mouse pointer button on the toolbar, switch the mouse back to the pointer mode, and double-click a table. The system opens the table Properties window. On the General tab, you can set attributes such as name and code of the table. For example, to create a new classroom table (classroom), you can modify the name and code. Name is the name displayed in the model, and code is the actual table name when the database table is generated. In addition, the content in name is also used as the table remarks in SQL Server.

Click columns to switch to the columns tab. You can add columns in the table in the following list. The name is the name displayed on the model, and the code is the actual table name generated. The three check boxes "p" follow indicate the primary key, and "F" indicate the foreign key, and "M" indicates that the value cannot be blank. Two columns are designed for the classroom table ,:

 

Primary Key

When designing a table, each table usually has a primary key, which is divided into a single column primary key and a composite primary key. You can set a primary key for a table in the following ways:

1. On the columns tab, select the P column check box of the primary key column. This is the easiest way.

2. select a column and click the "attribute" button in the toolbar. The column attribute window is displayed, in which you can set various attributes of the column, of course, it also includes whether the column is a primary key. Another important check box is "identity ". If you select the "Identity" check box, this column is an auto-incrementing column.

3. switch to the Keys tab, add a row named pk_classroom, and click the "properties" button in the toolbar to open the key Properties window, switch to the columns tab, and click Add column, in the pop-up Column Selection window, select the columns that should be included in the primary key and click OK to create the primary key.

In addition, when a primary key is created, the system creates an index on the primary key. indexes are classified into clustered indexes and non-clustered indexes, on the General tab of the "Key properties" window, you can set whether the index created on the primary key is a clustered index or a non-clustered index ,:

Foreign key

If the physical model is generated by the conceptual or logical model, the foreign key is generated through relationship, or the reference in the toolbar can be used to implement the foreign key relationship between the two tables. If a course only takes classes in a fixed classroom and multiple courses are arranged in a single classroom at different times, the relationship between the classroom and the course is one-to-many, in the curriculum, you need to add the roomid column to form a foreign key column. The specific operation is to click the "Reference" button in the toolbar, and then in the design panel, press the left mouse button on the curriculum, drag the mouse to the instructor table. If no roomid Column exists in the course list, the system automatically creates the roomid column and creates a foreign key reference for the column. If the roomid column already exists, only foreign key references are added, and no new columns are added.

 

Switch to the mouse pointer mode, double-click the arrow, and the system will pop up the referenced Property Window, in the Properties window, you can set the referenced name, code, associated columns, constraint names, update policies, and deletion policies.

Unique Constraint

The unique constraint is basically the same as creating a unique index, because when creating a unique constraint, the system creates a unique index and implements the constraint through the unique index. However, the unique constraint intuitively expresses the uniqueness of the corresponding column so that the purpose of the corresponding index is clearer. Therefore, we generally recommend that you create a unique constraint instead of only creating a unique index.

Create a unique constraint operation in PD. In the classroom table, roomid is the primary key and must be unique. If roomname is required to be unique, perform the following operations:

On the Model Design panel of Pd, double-click the "classroom" table, open the Properties window, and switch to the "" keys "tab. a row of Data pk_classroom is displayed, which is the primary key constraint. Add a row of data and name it uq_roomname. You cannot select the "P" column on the right. Then, click the "properties" button on the toolbar. The uq_roomname attribute window is displayed. Switch to the column tab, click the Add column button, select to add the roomname column to it, and click OK to add the unique constraint.

In this way, the system automatically creates a unique constraint.

Check Constraints

Check is divided into column constraints and table constraints. Column constraints are only constraints on one column in the table. You can set them in column attributes, table constraints are constraints on multiple columns and must be set in the table attributes (in fact, column constraints can also be set in Table constraints ).

1. Standard Check Constraints

For some common check constraints, you can directly complete them through the settings interface. Take the class table as an example. Each school has its own naming rules. If classname must start with 2, you need to define the check constraint on the classname column so that it meets the naming rules. The specific operation is to double-click the class table in PD, open the class attribute window, switch to the column tab, select the classname column, and click the "attribute" button in the toolbar to bring up the classname attribute window, switch to the standardchecks Tab

On this tab, you can define the standard check constraints for attributes. The meanings of parameters in the window are as follows:

Parameters	Description
Minimum	Acceptable minimum number of attributes
Maximum	Maximum number of acceptable attributes
Default	Default value provided by the system when the attribute is not assigned a value
Unit	Units, such as kilometers, tons, and Yuan
Format	Attribute data display format
Lowercase	All attribute values are lowercase letters.
Uppercase	All values assigned to an attribute are uppercase letters.
Cannot modify	This attribute cannot be modified once it is assigned a value.
List of Values	Attribute Value assignment list. No value exists except the value in the list.
Label	Label of attribute List Value

2. directly write check constraints for SQL statements

In the classname attribute window that appears, click the "more" button in the lower-left corner. More tabs are displayed. Switch to the "additional checks" tab to set the constraint name and the specific constraint content,:

Table-Level Check constraints are similar to column-Level Check constraints. Click the "more" button in the lower-left corner of the table Properties window, switch to the check tab, and set the name of the check constraint and the content of the SQL statement.

3. Use rule to create constraints

Similarly, the class name must start with 2 and use rule to create check constraints. Create a rule, double-click the class table, open the table Properties window, switch to the Rules tab, and click "Create a object". The system will open a business rule Properties window, modify the rule name and the rule type to constraint ,:

Switch to the expression tab, set the rule content to "classname like '2% '", and click OK to complete the rule setting. Switch to the check tab of the table attribute. "% rules %" in the default constraint content is used to indicate the content set in the rule. If we have other check constraints, you do not want to set it in rule, but in the check tab. You only need to delete % rules % to add the check constraint content, and you can also retain % rules %, then, add an and between % rules %. For example, if classid must be less than 10000, we can set the check content as follows:

The generated script is as follows:

Create Table class (
Classid int not null,
Classname varchar (20) not null,
Constraint pk_class primary key nonclustered (classid ),
Constraint ckt_class check (classid< 10000 ),
Constraint classnamerule check (classname like '2% ')
)
Go

As you can see, the check constraint generated by rule and the constraint set on the check tab create one constraint, which does not affect each other.

Default Constraint

The default constraint is that the system gives the default value without entering a value. The most common field is the createtime field. The default value is getdate (), which records the creation time when a user creates a row of data. For example, if you want to record the course selection time in the Course Selection table, you can set the default value of applytime to the getdate () function.

To set the default value constraints, double-click the course selection table to open the table Properties window, select the applytime field, and click the Properties button on the toolbar to open the column Properties window and switch to the standard checks tab, select getdate () from the default drop-down list box.

View

In SQL Server, a view defines an SQL query. One query can query one table or multiple tables. In PD, a view is similar to a query defined in SQL Server. For example, to create a view of all the course selection results for several students, select the View button in the toolbar and click the mouse on the design panel to add a blank view, switch to the mouse pointer mode. Double-click the view to open the attribute window of the view. On the General tab, you can set the view name and other attributes.

Usage indicates whether the view is read-only or updatable. The check option is specified, you cannot verify any updates directly to the basic table of the View Based on The View. If we only create a general view, select the query-only option.

Dimen1_type specifies whether the view represents a dimension or fact. This parameter is mainly used for Multi-Dimensional Data Modeling in a data warehouse. Generally, this parameter is not required. The following two check boxes do not need to be modified. Type uses the default view option.

Switch to the SQL query tab. You can set the view-defined query content in the text box. We recommend that you verify the correctness of the view-defined SQL statement in SSMs, then copy and paste the SQL statement into the text box. We recommend that you do not use * when defining a view. Instead, you should use the names of all required columns so that each column can be seen in the columns of the view attribute. Design SQL query.

Of course, you can also use the built-in SQL editor in PD to write SQL statements. Click the "Edit with SQL Editor" button in the lower right corner to pop up the SQL editor and write SQL statements.

Stored Procedures and functions

Stored Procedures and user-defined functions are both set in the same component. In the toolbar, click the procedure button, and then click the design Panel once to add a procedure. For example, to create a stored procedure to obtain the course selected by the student based on the student's student ID, perform the following operations:

Double-click the added procedure in Pointer mode to open the procedure Properties window. On the General tab, you can set the name of the stored procedure.

Switch to the definition tab, which defines the definition of the stored procedure. In the drop-down list box, select the <default procedure> option. If you want to define a function, select the <default function> option. The system creates an SQL statement template based on the selected type.

In the following SQL statement, you can retain create procedure [% qualifier %] % proc %, delete other items, and write SQL statements based on the stored procedure you want to create.

create procedure [%QUALIFIER%]%PROC%@StudentID intasbeginselect CourseNamefrom vwStudentCoursewhere StudentID=@StudentIDend

Click OK. The system associates the tables and views used with the stored procedure based on the prepared SQL statement ,:

The process of creating a function is similar to that of using create function instead of create procedure.

So far, the most common database objects: tables (Table constraints), views, stored procedures, functions, and so on have been introduced in the creation of PD. Next we will introduce the settings of PD.