Getting started with database modeling using PowerDesigner

Getting started with database modeling with PowerDesigner (PD) is a powerful database modeling tool. With PD, you can create business models and UML diagrams. Of course, the most important function is database modeling. I plan to explain how to use PD in the following parts. 1. Two important models: Conceptual Model and Physical Model 2. Create a table and a master

Getting started with database modeling with PowerDesigner (PD) is a powerful database modeling tool. With PD, you can create business models and UML diagrams. Of course, the most important function is database modeling. I plan to explain how to use PD in the following parts. 1. Two important models: Conceptual Model and Physical Model 2. Create a table and a master

Getting started with database modeling using PowerDesigner

PowerDesigner (PD) is a powerful database modeling tool. With PD, you can create business models, virtual hosts, and UML diagrams. Of course, the most important function is database modeling. I plan to explain how to use PD in the following parts.

1. Two important models: Conceptual Model and Physical Model
2. Create a table and a primary foreign key
3. Create views and stored procedures
4. Generate a database

Two important models

First of all, the concept model is an abstract macro-level business model, such as E-R (entity relationship) diagram, website space, in the concept model, the most important object is the entity and relationship.

A logical model can be generated based on the conceptual model. The logical model is an extension of the conceptual model, indicating the logical order between concepts. It is a model at the method level. Specifically, the logical model shows the relationship between the attributes of the entity and the object. On the other hand, the inheritance and reference in the object relationship are displayed in the attributes of the object. The logical model is between the conceptual model and the physical model. It has the characteristics of the physical model and has many-to-many relationships in the conceptual model, the logical model will be implemented by adding a one-to-multiple relationship to the intermediate entity. The logical model makes the entire conceptual model easier to understand and does not rely on specific database implementations. The logical model can be used to generate a physical model for a specific database management system. The logical model is not required in the entire process. You can use the conceptual model to generate a physical model.

Second, the physical model depends on the specific physical implementation. It uses database objects and converts the original "Object-link" to "table-foreign key ", the attribute of an object is converted to a table column, and the Data Type of each column is converted to the data type supported by the corresponding DBMS.

For the physical model of SQL Server 2008, if the primary key needs to use auto-increment, You need to modify the specific column, the Hong Kong virtual host, and set this column as Identity. If the generated data type is unreasonable, you can adjust the data type to make the data type more appropriate. In addition to the tables generated in the physical model, you can also manually add database objects that cannot be expressed in conceptual and logical models, such as views, stored procedures, and business rules.

After adjusting the physical model, you can apply the model to the SQL Server database. There are two ways to implement a physical model in a database: one is to connect to the SQL Server database using PD, and then synchronize the model to the database, and the other is to generate a database script.

Create a table and a primary foreign key

1. Create a 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 displays a toolbar dedicated to the physical model design, for example:

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 ,:

2. Set the 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 system displays the column attribute window, 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. In this window, switch to the Columns tab, click Add column. In the pop-up 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 ,:

3. Set the 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.