The structure of a database (such as tables, relationships, views, and triggers) is called a database
Mode。 You can use SQL statements to create these elements and arrange them in the way you want, but it can be confusing if you don't use graphical tools. PowerDesigner provides a graphical representation of the structure of a database. You can better modify the structure of a database or create a new table by simply drawing a new table or entering information. After the design is complete, powerdesigner can generate a SQL script to generate a new database. The following illustration shows the structure of the sample database, which can be easily created using PowerDesigner.
The performance of a database depends primarily on the design. Generally, information about different types of objects, such as employees or products, should be stored in a separate table. You can use references to determine the relationship between these tables by using a foreign key from one table to identify a specific row in another table. A reference can represent a one-to-many and one-to-many relationship. A many-to-many relationship requires two references and another table. For more information about database design, see Designing a database. To learn more about PowerDesigner, click Help on the PowerDesigner main window toolbar to access the following three books: PowerDesigner General Features Guide, PowerDesigner P DM user ' s Guide and powerdesigner the Guide of the user ' s. For more PowerDesigner tutorials, please open powerdesigner. From the Help menu, choose Where to start] > [PDM Getting Started]. This operation can access the physical Data Model Getting Started book. PowerDesigner can read the structure of the database from the script file used to create the database. However, it is usually easier to connect to the database from PowerDesigner and extract the design directly using the reverse engineering feature. The following tutorial takes the sample database as a starting point. This tutorial modifies the sample database to improve the design of the sample database when explaining PowerDesigner. Currently, the price of each product is always read from the product table. Therefore, if the price is updated, the sales price of the product on all previous orders will be changed accordingly. You can resolve this problem by adding a unit_price column to the Sales_order_items table. This allows you to store the actual sales price for each customer separately. The price in the Product table records the current list price. Start PowerDesigner: From the Start menu, choose [Program] > [SQL Anywhere 9] > [Powerdesi Gner 9] > [PowerDesigner]. At this point, the PowerDesigner main window appears:
The PowerDesigner main window contains the Object Browser (left) and the Output window (bottom). From the [File] menu, select [New]. The [New] dialog box appears. In the [New] dialog box, select [Physical Data Model] and click OK. The [New Physical Data Model] dialog box appears. On the General tab, select [Sybase as Anywhere 9] from the Drop-down list. Use the default settings for the remaining options, and then click OK. At this point, the model name Physicaldatamodel_1 appears in the browser and title bar. In this section, you can reverse-engineer the sample database to generate a physical data model (PDM) for it. Reverse-Engineer The database: Click the Diagram window (large pane of the PowerDesigner Center). From the [Database] menu, choose [Reverse Engineering Database]. The [Database Reverse Engineering] dialog box appears: Make sure that [Using an ODBC data source] is selected and that the data source is [ASA 9.0 Sample]. If [ASA 9.0 Sample] does not appear, click the icon to the right of the data source field. The Connect to ODBC Data Source dialog box appears. Select [Computer data source], and then select [ASA 9.0 Sample] from the Drop-down list. Enter User ID
DBAand password
SQL。 Click Connect to return to the [Database Reverse Engineering] dialog box. In the [Database Reverse Engineering] dialog box, click OK. At this point, the ODBC Reverse Engineering dialog box appears:
The bottom half of the dialog box has 7 check boxes for selecting keys, indexes, and so on. All of these are
Reverse Engineering Options。 Make sure to select all options except the [Permissions] check box (the default). You should also make sure that all tables are selected (also the default settings). Click OK to reverse engineer the database. A graphical representation of the sample database appears in the Diagram window, and the model object appears in the browser: Choose Display Preferences from the Tools menu to see how the changes are displayed. Click each option on the left to view the Display preference Parameters page. Click Help at the bottom of each page to see all the information. View the diagram. You can use the F6 key and the F7 key to enlarge and shrink the diagram for easy viewing. Each table in the database is represented by a box. The name of the table appears at the top of the box. Below it is a list of columns. The underlined column name is part of the table's primary key. The data type for each column appears on the right. After reverse engineering, some tables may overlap. From the [Symbol] menu, select [Auto-layout] to rearrange it. References between these tables are represented by arrows. These arrows point to the parent table, which is the table that contains the primary key. There is an equation next to the arrow for each identity reference. Rearrange the tables to make the diagram easier to see. To rearrange them, drag them with your mouse. The reference arrows are automatically moved along with these tables. You can select multiple objects by pressing the Shift key while you click. Here's a possible way to arrange:
Lesson 2nd: Adding ColumnsNow you can add the Unit_price column to the Sales_order_items table. You can do this by accessing the list of columns from the Table property table. Add Column: Select Sales_order_items table. Choose Properties from the View menu. The Table Properties dialog box appears. Click the [Columns] tab. The list of columns appears. Add a new column to store the unit price. Click the Insert a Row button. At this point, an arrow appears at the beginning of the line and displays a column with the default name (Column_6). In the Name column, type
Unit_price。 The name is copied automatically as code. In the Data Type column, select [Numeric] from the Drop-down list. The [Data] field may be too narrow to view. You can drag the sides of a column to extend the column. Column Properties P, F, and M represent primary key, foreign key, and force (interpreted as follows): The value of the column specified by primary key uniquely identifies the row in the table. The column specified by the foreign key depends on the primary key column in the other table and is migrated from that primary key column. Mandatory requires that the specified column be assigned a value. Select Force, and then click OK. Check the impact of your changes on the database diagram. The Sales_order_items table now contains a new column called Unit_price.
Lesson 3rd: Checking the work donePowerDesigner can also be used to quickly detect database design errors in the new model. Check the new mode: from the [Tools] menu, select [Check model]. At this point, the Check Model Parameters dialog box appears. You can use the default parameters. Click OK. At this point, the result of Check Model is displayed in the results list.
Lesson 4th: Save changes and build a databaseIn PowerDesigner, a model that describes the physical components of a database design, including tables and columns, is called
Physical Data Model
(physical Data Models, PDM)。 PowerDesigner stores these models in a file (with the extension. PDM). Save the physical Data Model (PDM): From the [File] menu, select [Save As]. Type the filename c:/temp/newdemo.pdm. Click [Save]. You can use PowerDesigner to generate an SQL script that implements all the components of the model. The SQL script is then used to generate a database. Generate SQL script to create a new database: from the [Database] menu, choose [Generate Database]. The Database Generation dialog box appears: Type c:/temp/in the Directory field and type Newdemo.sql in the File Name field. Make sure [Script Generation] is selected. Click the Database tab and make sure Create Database is selected. Browse other tabs to see options for controlling many other properties of the generated script. Click OK. When you create the script, the result dialog appears. Click [Edit] to view the script. Check to see if the changes are reflected in the script. For example, check the definition of the new office table shown below.
/* ========================================= */
* Table:office * *
/* ========================================= */
CREATE TABLE Office
(
ID integer NOT NULL
Default AutoIncrement
Check (
ID >= 100),
Name Char NOT NULL,
Street char is not NULL,
City Char is not NULL,
State char (2) is not NULL,
Zip char (5) NOT NULL,
Phone char (10),
Fax char (10),
Primary KEY (ID)
);
When you are done, close the dialog box: In the Result dialog box, click Closed. You can now create a new database from Interactive SQL. New database: Start Interactive SQL. From the Start menu, choose Programs > [SQL Anywhere 9] > [Adaptive Server Anywhere] > [Interactive SQL]. Connect to the sample database using the ASA 9.0 sample ODBC data source. Create an empty database: Execute the following SQL statement and replace the directory in the statement with the appropriate directory.
CREATE DATABASE ' c://temp//newdemo.db '
| tip to execute an SQL statement in Interactive SQL, type or copy the statement in the SQL statements pane, and then press the F5 key. Alternatively, choose Execute from the SQL menu. |
Close the connection to the sample database. From the SQL menu, choose Disconnect. Connect to the new database. From the SQL menu, choose Connect. Input
DBAEnter as user ID
SQLClick the Database tab as a password, and then enter the full path and file name of the new database file in the database file box. Click OK. Use the Read statement. Remember, this statement requires a double quotation mark to cause the file name. Execute the SQL statement:
READ "C://temp//newdemo. SQL "
You can use these basic steps to modify other databases.
SummaryThis tutorial only describes some of the basic features of PowerDesigner. In fact, it can handle all the design or modification of the database schema, including all tables, views, indexes, references, triggers, and procedures.
Domain
You can use other features to greatly simplify the design tasks for large databases. For example, you can specify a domain . A field contains a specific type of data, such as a phone number. A field is associated with a data type, but the domain is more specific. For example, you can create an identification number field. When you want an identification number in a table, you can associate the column with the Identification Number field. Also, automatically associates all the properties and checks associated with the domain. Domains can reduce the definition of repeatability. This not only reduces your workload, but also reduces the likelihood of misusing other type definitions or checking procedures. Instead of simply identifying the column as an integer, you specify the specific data type that the column contains. All instances of the data type share the same definition. For more information, see Working with domains.
Business Rules
Business rules express the way business operates in written form. For example, the order shipped date must is greater than or equal to the order date (Orders delivery date must be later than or equal to the orders day) is a business rule. Business rules are grouped into four categories: define intrinsic properties that represent objects. [definition] is typically used to describe an entity. The fact that there is certainty or existence. [facts] are often used to describe relationships. A checksum is a constraint that is set to a value. The formula is used to derive the calculation of the value. 
