Powerdesigner Database Design

Original article address: the primary database design tool launched by Sybase. Powerdesign is committed to the use of entiry-relation-based data models, respectively from the conceptual data model (Conceptual Data Model) and physical data model (Physical Data Model) two levels of the database design. Conceptual Data Models describe object definitions and object relationship definitions independent of database management systems (DBMS. The physical data model is specific to the target database management system based on the conceptual data model.

Erwin: This is the fist product of CA. One of its siblings is bpwin, a milestone product of the case tool. The Erwin interface is very concise and beautiful. It also uses the ER model. If you are developing small and medium databases, we strongly recommend Erwin. Its digoal gives people a clear feeling. In an object, different attribute types are displayed with customizable icons, so the relationship between the object and the object is clear at a glance. Erwin is not suitable for the design of very large databases because it lacks more levels of organization for digoal.

How can I export the table structure script at one time?
Database ---> Generate database ---> genarate script.

Name can be queried and written in Chinese and English.ProgramThe Code is the final generated table name.

How can I export a model from powerdesigner as a document?
Use report. Select a template and generate a document in the RTF or HTM format.

How to import all existing database tables to powerdesign?
Use reverse engineering file in PD ---> reverse engineering ==> and go on

Concept data model (CDM)
CDM represents the entire logical structure of the database, and is irrelevant to any software or data storage structure. A conceptual model often includes data objects that are still not implemented in a physical database. It provides a formal representation of the running plan or business activity data. The physical implementation details are not considered, but the relationship between entities.

Physical Data Model (PDM)
PDM describes the physical implementation of databases. The main purpose is to generate a specific DBMS script for the real-world model established in CDM, generate a storage structure for storing information in the database, and ensure the integrity and consistency of data in the database.

Object-Oriented model (OOM)
An OOM contains a series of packages, classes, interfaces, and their relationships. These objects form a class structure of all (or part) the logic of a software system design view. An oom is essentially a static conceptual model of a software system.

Business Program Model (BPM)
BPM describes different internal tasks and internal processes of the business, and how the customer interacts with these tasks and processes. BPM is a conceptual model of business logic and rules from the perspective of business partners. It uses a chart to describe the interaction between programs, processes, information and cooperation protocols.

Forward Engineering
You can directly generate a database from PDM or a database script that can run in your database management system environment. You can generate a database script. If you select ODBC, you can directly connect to the database to directly generate database tables and other database objects.

Reverse Engineering
Generate existing databases into the new PDM. The data source may be from a script file or an open database connection data source.

Not every design requires power designer. For example, a small system or a small number of tables does not need to use power designer.

Powerdesigner is just a tool for implementation.
Do not think that power can help you establish all the relationships and things. Many database theories are only needed. When designing databases, you must master those paradigms and things.
The best tool for designing a good database is not necessary, but the basic theory is necessary.

Powerdesigner is not limited to data modeling.
You can also use powerdweigner to design Web Services.

Not every design requires PD
It is good to use powerdesigner to deal with relatively large projects. For short and fast projects, if you need to complete a program within one week, there is no need to use powerdesigner, directly maintain the database. When the number of tables exceeds 10 (about 10 tables in a small system), we recommend that you use powerdesigner.
My opinion: If you want to make a more standard database, you can also use it for small projects. After all, it is useful to generate reports and reverse engineering.

Fragmentation

During CDM design in PD, all required fields can be defined. Then, the design entity is obtained directly. PD provides such a unified management tool. In the PD menu bar-model-data item.

To make the self-designed CDM look like a bit, you can drag a title from the toolbar. The displayed information is the property value of the current CDM.

In order to make the entity and other symbrs look conspicuous and comfortable. You can adjust the appearance according to your preferences. On the current design page, right-click and choose "display perferences. You can also increase the shadow effect. Press Ctrl + W to select the symbol. Or right-click the menu.

To make the layout neat. Select the symbol to be adjusted, and set it in the menu-symbol-align. Shortcut Keys: Ctrl + up, CTRL + down, CTRL + left, and CTRL + right are aligned between top, bottom, and left.

When designing object attributes, pay attention to the following details: M: Indicates forced non-null; P: whether it is a primary key; D: whether it is displayed in the model. Gerenate: whether to generate a table.

By default, CDM entities will display the identifier column. If you do not want it to be displayed, right-click objectview-entity in-display perferences.

The link naming method is: Object Name 1 Object Name 2.

The Role in the Link indicates the meaning of a direction on the contact line. Use a verb to describe. Role only serves as a description.

Dependency: indicates whether the existence of an object in a link depends on another object. A dependent entity is an entity partially determined by another entity. In the dependency relationship, an entity is associated with another entity through an identifier. when there is no other entity as a reference, it cannot be uniquely identified, there is a dependency between the two entities.
A master-slave table is a typical dependency.

Associative entity: a human entity generated to solve many-to-many connections. It can define attributes for intermediate entities. Right-click multiple-to-multiple contact lines and select "change to entity" in the pop-up menu to convert the contact to the intermediate entity connecting the two entities.
Being good at using automatically generated intermediate entities can simplify the design work and improve the correctness of the database design.
The intermediate entity generally does not need to add new fields.

Note: Foreign keys are automatically created by relational relationship, and do not need to be manually created. Otherwise, redundant keys will be generated. Therefore, when designing, pay attention to the fields of the object and the relationship between objects, especially the many-to-many and dependency relationships.

Note the following before converting CDM to PDM:

The digoal name cannot be changed.
In the tree chart, if you select the symbol marked in red, it indicates to overwrite the modification. If you do not select the symbol, it indicates to protect the modification.

To ensure data integrity and consistency, the database puts forward constraints. Table constraints, column constraints, and integrity constraints. Generally, database design and program development are not absolutely isolated. Therefore, the first two are gradually improved in the actual development process. You need to pay attention to the integrity constraints.
In PD, the first two are set to the right field, and the latter is the right relationship.

Integrity constraints

Restrict ). Modification or deletion operations are not allowed. If the primary key of the primary table is modified or deleted, if the Sub-table contains a sub-record, an error message is displayed. This is the default integrity settings.
Set null ). If the foreign key column can be blank, If you modify or delete the primary key of the primary table, set the foreign key column referenced in the subtable to null ).
Set default ). If the default value is specified, if you modify or delete the primary key of the primary table, set the foreign key referenced in the subtable to the default value ).
Cascade ). When you change the primary key of the primary table to a new value, you can modify the foreign key value of the sub-Table. Alternatively, When you delete the primary key record of the primary table, you must delete the foreign key record of the sub-table.

Note that when you understand the preceding constraints, the master table is used to capture the operation. Sub-Table operations are relative to the master table. The operation methods are update and delete.

When referencing data from a basic data table, you can create a view. Select the table to be viewed, and choose "tools-create View" from the menu.

PD supports updating the table structure of existing tables. However, exercise caution when checking the generated SQL script.

PD can also generate random test data.

A trigger is an event-driven mechanism provided by DBMS. Insert, update, and delete occur in the table. Execute SQL statements or stored procedures.

You can write the stored procedure in the PD for ease of management.

Reverse engineering can be implemented through database scripts or ODBC data sources.

Certificate -----------------------------------------------------------------------------------------------------------------------------------

Frog frog recommendation: Summary of database modeling experience using PD

1. Select sqlserver2000 when creating PDM
2. After creating a PDM, create a DBO user. After creating a new table, set the user to DBO.
3. When creating a table, the name and code can be separated, and the name can be written into Chinese for reading. The code can be written into English and used as the table name. Finally, write the comment, which generates comments. Note writing can be longer, but do not press enter to wrap the line. If you want to generate an sqlserver database, you can click the table design view. It is difficult to view the column comments in several lines. The same is true when adding columns to a table, namely name, code, and comment.
4. Open the database-edit current DBMS and write the value of script/objects/table/create as follows:

/* Tablename: % tname % */
Create Table [ % Qualifier % ] % Table % (
% Tabldefn %
)
[ % Options % ]

Script / Objects / Table / Tablecomment is as follows:
Execute Sp_addextendedproperty n ' Ms_description ' , N ' % Comment % ' , N ' User ' , N ' % Owner % ' , N ' Table ' , N ' % Table % ' , Null , Null

Script / Objects / Column / Add as follows:
% 20 : Column % [ % Compute %? As (% compute %): % 20: datatype % [% identity %? % Identity %: [% null % ] [ % Notnull % ] ] [ Default % default % ]
[ [Constraint % constname % ] Check ( % Constraint % )] /* % Colnname % */

Script / Objects / Column / Columncomment is as follows:
/* % Column %: % comment % */
Execute Sp_addextendedproperty n ' Ms_description ' , N ' % Comment % ' , N ' User ' , N ' % Owner % ' , N ' Table ' , N ' % Table % ' , N ' Column ' , N ' % Column % '

5. generate the followingCode. Click the database-genrate database menu to hook the comment check boxes in the table and column combo boxes on the table & view tab. Click OK to generate the database script.
6. When designing a database, it is best to put the relevant tables together and enclose them with a rectangle. The rectangle is filled with different background colors for easy viewing. You can select all the elements in a rectangle box, click the group symbol in the symbol menu to combine the table and the rectangle box, and click the Ungroup symbol menu to remove the combination.
7. If you want to generate a Database Report, right-click the physical model, select the new-Report menu, and select the language and template. Because the template provided by PD is too cumbersome, you generally select an empty template, design the report elements to be displayed, such as table list and column list, and some table and column descriptions.
8. To view the code generated for each table, you can view it on the preview tab of the table properties.
9. If you right-click a graph and select protect symbol, the graph will be protected and cannot be dragged or modified. What should I do? Click the symbol menu and click the unprotect symbol menu.