The structure of the database (such as table, relationship, view, and trigger) is called database mode. These elements can be created using the SQL statement and arranged in the desired manner, but if the graphics tool is not used, it may be confused.
PowerDesigner provides a graphical representation of a database structure. Simply draw a new table or input information, you can better modify the structure of the database or create a new table. After the design is complete, PowerDesigner generates a SQL script to generate a new database.
The figure below shows the structure of the sample database, you can easily create this illustration using PowerDesigner.
The performance of the database is mainly dependent on the design. Generally, information of different types of objects (such as employees or products) should be stored in separate tables.
You can use a reference to determine the relationship between these tables, that is, with the foreign key in a table to identify a particular row in another table. References can be represented by multi-pair one and one-to-one relationship. Multi-to-many relations require two references and another table.
For more information on database design, see Designing Databases.
To learn more about PowerDesigner, click [Help] in the PowerDesigner main window toolbar to access the following three books: PowerDesigner General Features Guide, PowerDesigner PDM User's Guide, and PowerDesigner Report User's Guide.
To get more PowerDesigner tutorials, open PowerDesigner. Select [WHERE to Start]> [PDM getting start] from the [Help] menu. This action can be accessed by Physical Data Model Getting Started.
PowerDesigner can read the structure of a database from a script file for creating a database. However, the generally easier method is: Connect to the database from PowerDesigner and use reverse engineering characteristics.
The following tutorial will use the sample database as the starting point. This tutorials modified the sample database when elaborating PowerDesigner to improve the design of the sample database.
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 orders will be changed accordingly. This issue can be resolved in the Sales_Order_Items table. In this way, the actual sales price of each customer can be stored separately. The price record in the Product table current list price.
Start PowerDesigner:
From the [Start] menu, select [Program]> [SQL Anywhere 9]> [PowerDesigner 9]> [PowerDesigner]. At this point, the PowerDesigner main window appears: The PowerDesigner main window contains an object browser (left) and output window (bottom). Select [New] from the [File] menu. The [New] dialog will appear. In the [New] dialog box, select [Physical Data Model], then click [OK]. The [New Physical Data Model] dialog will appear. 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 will appear in the browser and the title bar. In this section, reverse projects can be performed by the example database to generate a physical data model (PDM). Performing a reverse engineering for a database: Click the Diagram window (the big pane of the PowerDesigner Center). Select [REVERSE ENGINEERING DATABASE] from the [Database] menu. The [Database Reverse Engineering] dialog box will appear: Make sure the [USING A ODBC DATA SOURCE] is selected, and the data source is [ASA 9.0 Sample]. If [ASA 9.0 Sample] does not appear, click the icon on the right side of the data source field. At this point, the [Connect to the ODBC Data Source] dialog appears. Select [Computer Data Source] and select [ASA 9.0 Sample] from the drop-down list. Enter the user ID DBA and password SQL. Click [Connect] to return to the [Database Reverse Engineering] dialog. In the [Database Reverse Engineering] dialog box, click [OK]. At this point, the [ODBC Reverse Engineering] dialog box appears: 7 check boxes in the lower half of the dialog, for selecting keys, indexing, etc. These are all reverse engineering options. Make sure all options except the [Permissions] check box (default) check box (default). It should also be ensured that all the tables (also default settings) have been selected. Click [OK] to perform database reverse project. The graphical representation of the sample database will appear in the graphical window, the model object will appear in the browser: Select [Display Preferences] from the [Tools] menu to see the modified display. Click Each option on the left to view the display preference parameter page. Click [Help] at the bottom of each page to view all information. View icon. You can use the F6 keys and F7 keys to magnify and reduce the image to facilitate viewing. Each table in the database is represented by a box. The name of the table is displayed at the top of the box. The following is a list of columns. The column name with underscore is part of the primary key. The data type of each column is displayed on the right. After the reverse engineering, some tables may overlap. From the [Symbol] menu, select [Auto-Layout] to rearrange it. The reference between these tables is expressed with arrows. These arrows point to the parent table, that is, the table containing the primary key. There is a equality next to each of the arrows referenced by each identity. Rearrange these tables to view the illustration. To rearrange, drag these tables with your mouse. The reference arrow automatically moves with these tables. You can select multiple objects by pressing the SHIFT key when you click. Here is a possible arrangement: Lesson 2: Add column
Now, you can add Unit_Price columns to the Sales_Order_Items table. This task can be done through the [Table Property] table to access the column list. Add Column: Select the Sales_Order_Items table. Select [Properties] from the [View] menu. The [Table Properties] dialog box will appear. Click the [COLUMNS] tab. A list of columns will appear. Add new columns to store unit prices. Click the [INSERT A ROW] button. At this point, an arrow will appear in the lead and display the column using the default name (column_6). Type Unit_Price in the Name column. Auto this name is automatically replicated as a code. In the [Data Type] column, select [NuMeric] from the drop-down list. The [DATA] field may not be viewed because it is too narrow. You can drag both sides of the column to extend the column. Column properties P, F and M represent [primary key], [foreign key], and [mandatory] (explained below):
[Primary Key] The value of the specified column uniquely identifies the rows in the table. [Outside Key] The specified column depends on the primary key column in another table and is migrated from the primary key column. [Mandatory] The required column must be assigned to the specified column. Select [Force], then click [OK]. Check the impact on the changes to the database graphic. Now, the sales_order_items table contains a new column called Unit_Price. Lesson 3: Checking the work done
PowerDesigner can also be used to quickly detect database design errors in the new model. Check new mode:
Select [Check Model] from the [Tools] menu. At this point, the [Check Model Parameters] dialog box will appear. You can use the default parameters. Click [OK]. At this point, the result of [Check Model] is displayed in [Result List]. Lesson 4: Save Changes and Generate Databases
In PowerDesigner, a model of physical components (including tables and columns) describing the database design is called Physical Data Models, PDMs. PowerDesigner stores these models in the file (extension .pdm). Save the physical data model (PDM):
Select [Save As] from the [File] menu. Type the file name C: /Temp/newdeMo.pdm. Click [Save]. You can use PowerDesigner to generate a SQL script that is used to implement all components of the model. Then, use the SQL script to generate a database. Generate SQL scripts to create a new database:
Select [Generate Database] from the [Database] menu. At this point, 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 view the options for many other properties that control the generated script. Click [OK]. When you create a script, the [Result] dialog box appears. Click [EDIT] to view the script. Check if the script reflects the changes. For example, check the definition of the new Office table shown below. / * ===================================================================================================== * // * Table: Office * // * ================================================== * / create Table Office (Id> = 100), Name Char (15) Not Null, Street Char (30) Not Null, City Char (20) Not Null, State Char (2) Not Null, Zip Char 5) Not Null, Phone Char (10), Fax Char (10), Primary Key (ID)); After completion, turn off the dialog box: In the [Result] dialog box, click [Close]. Now you can create a new database from Interactive SQL. New Database: Start Interactive SQL. From the [Start] menu, select [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 to replace the directory in the statement with the appropriate directory. Create Database 'C: //Temp//newdeMo.db'
Tip To execute the SQL statement in Interactive SQL, type or copy this statement in the [SQL Statement] pane, then press the F5 key. Or, select [Execute] from the [SQL] menu. Close the connection to the sample database. Select [Disconnect] from the [SQL] menu. Connect to a new database. Select [Connection] from the [SQL] menu. Enter the DBA Enter SQL as the user ID as a password Click the [Database] tab, and then enter the full path and file name of the new database file in the [Database Files] box. Click [OK]. Use the READ statement. Remember that this statement requires the file name to use dual quotes.
Execute this SQL statement: Read "c: //temp//newdeMo.sql" can use these basic steps to modify other databases. summary
This tutorial only introduces some of the basic features of PowerDesigner. In fact, it can handle all design or modifications of database mode, including all tables, views, indexes, reference, triggers, and procedures. The domain can use other characteristics to greatly simplify the design tasks of large databases. For example, you can specify a domain. The domain contains specific types of data, such as phone numbers. The domain is associated with a certain data type, but the domain is more specific. For example, you can create an identification number. This column can be associated with the identification number when an identification number is required. At the same time, all properties and inspections associated with this domain are automatically associated. The domain can reduce the definition of repeatability. This not only reduces your workload, but also reduces the possibility of misunderstanding other types of definitions or inspection processes. The column is not simply identified as an integer, but specifies the specific data type included in the column. All instances of this data type share the same definition. For more information, see Using the domain. Business rules business rules express business operations in writing. For example, the Order Shipped Date Must Be Greater Than or equal to the Order Date (order delivery date must be later or equal) is a business rule. Business rules are divided into four categories:
Define the intrinsic properties of the object. [Definition] is typically used to describe entities. The fact is definite or existed. [Fact] is usually used to describe the relationship. The verification is a constraint set to the value. The formula is used to determine the calculation of the value. The use of business rules is very convenient because they are directly related to the tasks that the customer requires the database. By recording business rules and associates them with specific objects, you can ensure that the data required for the database.
