Use the Power*Architect playpen to create a data model diagram that includes tables, columns, indices,

use the playpen and the database tree.

When you create a data model in Power*Architect, the model is saved in its own project. The project

contains the data model diagram in the playpen and the database tree. You can have multiple projects (and

therefore multiple data models) open in Power*Architect at once. Each project opens in a separate window.

To create a new table:

1.

Click in the side toolbar. The cursor changes to a +.

Note: To cancel creating a new table, press ESC.

Alternate methods:

• Right-click in the playpen, then click New Table.

• Place the cursor over the playpen, then press T.

2. Click in the playpen where you want to create the table. The Table Properties dialog box appears.

3. Enter the following information:

Table Name Enter a table name.

Primary Key Name You cannot enter a primary key name until you

have added columns to the table and defined the

primary key. The primary key name is used when

you forward engineer the data model. For more

information, see the section called “Creating New

Columns” .

Note: Primary key names are not used when

forward engineering to a MySQL database

(MySQL does not support custom primary key

names).

Creating a Data Model

13

Remarks Enter a description of the table. When you forward

engineer the data model, the remarks will be

included as comments in the database.

4. Click OK.

To modify a table:

•

Click a table in the playpen, then click in the side toolbar.

The Table Properties dialog box appears.

Alternate methods:

• Right-click a table in the playpen, then click Table Properties.

• Click a table in the playpen, then press Enter.

• Modify the table properties as required. For a description of the properties, see the section called

“Creating New Tables” .

• Click OK.

When you create a column, you can choose where the new column is inserted in the table.

To add a column to a table:

1. Click a table in the playpen. The location you click determines where the column will be inserted in

the table.

• If you click the table name or if the table does not contain any columns, the new column is added

to the end of the column list.

• If you click an existing column, the new column is added above the selected column.

Creating a Data Model

14

• If you click a column in the primary key, the new column is added within the primary key.

2.

Click in the side toolbar. The Column Properties dialog box appears.

Alternate methods:

• Right-click a table, then click New Column.

• Click a table, then press C.

3. You can enter the following information:

Name Enter the column name.

Type Select the type of data the column holds.

Precision Set the data precision.

Scale Set the scale.

In Primary Key Select the check box if the column is in the primary

key.

Allows Nulls Select the check box if the column handles null

information.

Auto Increment Select the check box if auto increment is allowed.

Sequence Name When Power*Architect creates a table in a

database platform that uses sequences (such as

Oracle or PostgreSQL), Power*Architect creates

a sequence for each auto-increment column in the

table. Enter the name to use for the sequence.

Note: This option is only available if you have

selected the Auto Increment option for the column.

Creating a Data Model

15

Remarks Enter comments about the column. When you

forward engineer the data model, the remarks will

be included as comments in the database.

Default Value Enter a default value for the column.

Note: Power*Architect does not validate the

default value, so ensure you use a valid format.

The following examples show valid formats for

different data types:

• 'word' for a String

• {d '2007-12-10'} for a Date

• {t '5:38:00'} for a Time

• {ts '2007-12-10 5:38:00'} for a Timestamp

4. Click OK.

To modify a column:

1.

Click a column, then click in the side toolbar.

Alternate methods:

• Right-click a column, then click Column Properties.

• Click a column, then press ENTER.

The Column Properties dialog box appears.

Creating a Data Model

16

If you added this column to your data model using reverse engineering, the source database and table

from which the column originated are shown at the top of the Column Properties dialog box.

2. Modify the column properties as required. For a description of the properties, see the section called

“Creating New Columns” .

3. Click OK.

You can move a column from one table to another or rearrange columns within a table.

• To move a column, click the column and drag it to a new location.

• To move multiple columns, use CTRL+click to select the columns, then drag them to a new location.

Note: You can also add or remove columns from the primary key. For more information, see the section

called “Working with Primary Keys” .

After adding one or more columns to a table, you can define the column(s) used for the primary key.

To add a primary key:

1. Select one or more columns.

2. Drag the column(s) to the primary key area in the table.

To remove a primary key:

1. Select the column(s) in the primary key area.

2. Drag the column(s) from the primary key area to the table's column list.

Note: You can change the primary key name for the table. For more information, see the section called

“Modifying Tables” .

Creating a Data Model

17

You can create relationships between tables. For example, a typical one-to-many relationship might

describe how invoices and invoice line items relate to each other. The relationship might indicate that the

invoice_line table is a child of the invoice table, and every row in the invoice_line table relates to exactly

one row in the invoice table.

You can create identifying and non-identifying relationships:

• In an identifying relationship, the child table cannot be uniquely identified without the parent.

• In a non-identifying relationship, the child can be identified independently of the parent.

You could choose to create the invoice and invoice line relationship from the previous example as either

an identifying or non-identifying relationship.

• If you create an identifying relationship, an invoice line cannot be uniquely identified without also

knowing the invoice number it belongs to. For example, assume that invoice line numbers always start

at 0 or 1 within each invoice. The same line numbers will appear in different invoices - each invoice

will have a line 0, line 1, line 2, etc.

• If you create a non-identifying relationship, an invoice can be uniquely identified without knowing

the invoice number it belongs to. For example, assume each invoice line has its own unique identifier

(invoice_line_id). In this example, invoice_line_id is referred to as a "surrogate key," because it's just

a made-up number which has no special meaning in terms of the invoice line.

For this relationship, you would also want to create a unique index on the combination of

(invoice_number, line_number) to guarantee there are no two line items with the same line number on

the same invoice. In the identifying relationship example, the primary key enforces this rule.

To create a new relationship:

1. Do one of the following:

•

To define an identifying relationship, click in the side toolbar, or press R. The cursor changes

to a +.

Creating a Data Model

18

•

To define a non-identifying relationship, click in the side toolbar, or press SHIFT+R. The cursor

changes to a +.

Note: To cancel creating a relationship, press ESC or click a blank area in the playpen.

2. Click the parent table, then click the child table. A relationship is created between the two tables and

is shown as a line.

The mapping between the tables is based on the parent table's primary key. For each column in the

primary key of the parent table:

• If the child table contains a column with the same name and this is the first relationship between the

two tables, the relationship is mapped to the existing column in the child table.

• If the child table does not contain a column with the same name, or the child table contains a column

that has the same name but the column has a different data type, or a relationship already exists

between the tables, a new column is created in the child table. The relationship is mapped to the

new column.

3. To view the columns that are mapped by the relationship, click the relationship link. The mapped

columns are shown in red.

You can now define the relationship properties, view the individual column mappings or change the

mapping of the child table to the parent table. For more information, see the section called “Modifying

a Relationship” .

Note: You can automatically straighten the relationship lines between tables. For more information, see

the section called “ Straightening Diagram Lines in the Playpen ” .

To modify a relationship:

1.

Click a relationship link in the playpen, then click in the side toolbar. The Relationship Properties

dialog box appears.

Creating a Data Model

19

Alternate method:

• Right-click the relationship link, then click Relationship Properties.

2. You can enter the following information on the Relationship tab:

Relationship Name Enter a name for the relationship. When you

forward engineer the data model, the relationships

are created as foreign key constraints in the

target database. These constraints are named based

on the relationship name. You can also view a

relationship's name in the playpen when you hover

over the relationship line.

Relationship Type Select the type of relationship (identifying or nonidentifying).

Cardinality Select the end cardinality for the primary and

foreign keys.

Deferrability Select the deferrability options.

• Not Deferrable - Foreign key constraints are

checked immediately at the time an INSERT,

UPDATE, or DELETE statement is issued.

• Deferrable, Initially Deferred - If the database

transaction doesn't specify whether to defer

Creating a Data Model

20

constraint checks, the foreign key constraints

will be deferred, meaning that they are not

checked until the INSERT, UPDATE, or

DELETE transaction is committed.

• Deferrable, Initially Immediate - If the database

transaction doesn't specify whether to defer

constraint checks, foreign key constraints are

checked immediately at the time an INSERT,

UPDATE, or DELETE statement is issued.

Important: Before selecting an option, read

the following description to ensure you fully

understand the effect of each option.

When manipulating data in a database (using

INSERT, UPDATE, and DELETE statements),

the foreign key constraints created by

Power*Architect are used to ensure data integrity

between the two tables. The deferrability options

control when these constraints are enforced.

Within the context of a transaction, deferred

constraints are not checked until the transaction

is committed, while immediate constraints are

checked at the time the INSERT, UPDATE, or

DELETE statement is issued (in the middle of

the transaction). This means that if you are using

immediate constraints, you must be careful about

the order in which data is changed. With deferred

constraint checking, you can make changes in any

order as long as all constraints have been satisfied

by the time you commit.

For databases that support deferred and immediate

constraint checking, each transaction can specify

whether to defer constraint checks or carry

them out immediately. If a transaction does not

specify this option, each deferrable foreign key

constraint is evaluated according to its "initially

immediate" or "initially deferred" option. On the

other hand, constraints marked as "not deferrable"

will always be checked immediately regardless of

the transaction's setting.

Important Notes:

• For data manipulation done outside the context

of a database transaction, there is no difference

between immediate constraint checking and

deferred constraint checking.

• Not all database platforms support this

option. Some only support deferred constraint

checking, while others only support immediate.

Creating a Data Model

21

When Power*Architect forward engineers to

these types of systems, the DDL script includes

comments warning about this lack of support.

3. On the Mappings tab, you can change the mapping to the child table. Click and drag the relationship

link to the column in the child table that is mapped to the parent table.

Note: If a column in the child table is shown in red, this means the column is a foreign key in another

parent table. This alerts you that the column is already "in use", since you wouldn't normally use the

same column as a foreign key in multiple tables.

4. Click OK.

You can create multiple indices for a table.

To create an index:

1.

Select a table in the playpen, then click in the side toolbar. The Index Properties dialog box appears.

Creating a Data Model

22

Alternate methods:

• Right-click a table in the playpen, then click New Index.

• Right-click a table in the database tree, then click New Index.

2. You can enter the following information:

Index Name Enter a name for the index.

Unique Select the check box if the index will act as

a constraint which guarantees the values in this

index's columns are unique across all rows in the

table. This is similar to the primary key constraint,

with two exceptions: A unique index may contain

nullable columns, and a table can have any number

of unique indices.

Primary Key Select the check box to set this index as the table's

primary key. The primary key is a special type

Creating a Data Model

23

of index which enforces uniqueness: The values

in the primary key's columns are unique across

all rows in the table. A table can only have one

primary key, and none of the columns in the

primary key may be nullable. It is considered good

practice to have a primary key on every table in the

data model.

Clustered Select the check box to create a clustered index.

Many databases support the notion of a clustered

index. The exact meaning varies by platform, but

marking an index as clustered often affects the

physical ordering of the rows within the table

(which may increase or decrease performance

based on the types of SQL queries being run). Most

database platforms allow only one clustered index

per table.

Index Type Select the index type. The list includes all known

index types for all database types configured in

your user preferences. If you are building a crossplatform

data model, it's best to leave this setting

at "platform default." However, if you are tuning

your data model for a specific target database,

you may choose the desired index type for your

platform.

List of columns Select the In Index check box beside each column

you want to include in the index. For each column,

select the sort order (Ascending, Descending, or

Unspecified).

Use the arrows at the bottom of the dialog box

to set the order of the columns within the index.

Columns higher in the list will come first in the

index's column list.

Notes:

• If the table contains columns in the primary key,

a separate index will always be created for the

primary key column(s), even if you don't select

any columns.

• On some database platforms, the column order

in the index and the column order in the SQL

WHERE clause must match in order for the

query optimizer to use the index.

• On most database platforms, a WHERE clause

that references a subset of a multi-column index

can usually be used when those columns in the

WHERE clause are the leading columns in the

index.

Creating a Data Model

24

Example: Table A has columns B, C, D, E, F.

Table A has an index on (F, E, D).

SELECT * FROM a WHERE f='x'; - index can

be used on most platforms

SELECT * FROM a WHERE e='x'; - index can

not be used on most platforms

SELECT * FROM a WHERE f='x' AND e='x'

AND d='x'; - index can be used

SELECT * FROM a WHERE d='x' AND e='x'

AND f='x' ; - index can be used on some

platforms, but index order and WHERE clause

order are different so some platforms will not

use the index

3. Click OK.

To modify an index:

1. Right-click a table in the playpen, then click Index Properties. If there are multiple indices for the table,

select the index you want to modify.

Alternate method:

• Right-click the index in the database tree, then click Index Properties.

The Index Properties dialog box appears.

2. Modify the index properties as required. For a description of the properties, see the section called

“Creating an Index” .

3. Click OK.

Right-click the index in the database tree, then click Delete Selected.

Power*Architect keeps track of your actions and allows you to undo them at a later time. The 100 most

recent actions you have performed are remembered and can be undone in sequence.

If you undo an action accidentally, you can choose to redo the action. However, be careful: If you make

a new change after undoing one or more actions, your redo history is lost.

Creating a Data Model

25

To undo an action, click in the top toolbar. You can also select Edit » Undo or press CTRL+Z.

To redo an action, click in the top toolbar. You can also select Edit » Redo or press CTRL+Y.

To select multiple objects (tables, columns, or relationships) in the playpen, do any of the following:

• Press CTRL or SHIFT and click the objects.

• Click a blank area in the playpen, then drag to form a grey box around the objects.

• Press CTRL+A to select all the objects in the playpen.

To cancel the selection, click a blank area in the playpen.

To delete a diagram object (table, column, or relationship) in the playpen, select one or more objects in

the playpen, then click in the side toolbar.

Alternate methods:

• Right-click an object, then click Delete Selected.

• Select one or more objects, then press DELETE.

You can change the layout of your data model diagram by rearranging the tables in the playpen. You can

also change where relationship links visually connect to a table in the diagram. (To change the columns

mapped by a relationship link, you must modify the relationship. For more information, see the section

called “Modifying a Relationship” .)

Notes:

• You can rearrange columns within a table or move columns from one table to another. For more

information, see the section called “Moving Columns” .

• You can automatically arrange the tables in the playpen. For more information, see the section called

“ Automatically Arranging Tables in the Playpen ” .

• You can automatically straighten the relationship lines between tables. For more information, see the

section called “ Straightening Diagram Lines in the Playpen ” .

To move a table, select one or more tables, then drag the table(s) to a new location in the playpen.

To move the placement of a relationship link, select a relationship link, then drag either end of the link

to a new location on the parent or child table.

You can automatically arrange tables in the playpen. Automatic layout works best when you have a large

or medium-sized collection of tables, and may not work as well with a small number of tables.

Creating a Data Model

26

To automatically arrange tables, select several tables in the playpen, then click in the top toolbar.

Note: If you don't select any tables or select only one table, all of the tables will be arranged.

You can automatically create straight lines for the relationship links in your data model diagram. All

relationship links will be changed to horizontal or vertical straight lines, as long as the tables connected

by the link are aligned horizontally or vertically. If the tables are not aligned, the relationship link will

not be changed.

To straighten the relationship lines, right-click a blank area in the playpen, then click Straighten Lines.

You can use the zoom options on the side toolbar to control the magnification level in the playpen. The

four zoom buttons, in order from top to bottom, are:

•

Zoom in

•

Zoom out

•

Reset the zoom to the default level

•

Zoom to fit

To use the zoom options on specific objects in the playpen, select the objects before clicking a zoom

button. If you don't select any objects in the playpen, the zoom options affect the entire diagram.

You can search for objects in the playpen. You can then quickly rename the items or select them in the

playpen.

1. Select Edit » Find/Replace, or press CTRL+F. The Find dialog box appears.

Creating a Data Model

27

2. Enter your search criteria, then click Search. The Search Results dialog box appears with your results.

3. To rename an object, select the object and click Rename Selected. You can also select multiple objects

if you want to rename all the objects using the same name.

4. To select an object in the playpen, select the object and click Show in Playpen.

To print the data model diagram currently in the playpen, select File » Print.

To export the data model diagram currently in the playpen:

1. Select File » Export Playpen to PDF. The Save dialog box appears.

2. Select the location and filename for the PDF, then click Save.

3. To hide the Creating PDF dialog box, click Run in Background.