About the Database Design Transformer

Related Topics

The Database Design Transformer creates and maintains database designs (or server models) based upon entity models recorded in your repository. It creates tables to record instances of each entity, columns to store the attributes, and constraints to implement the relationships between entities. It also creates constraints to enforce any unique identifiers that you have defined, and indexes to support your foreign keys.

Like the entity model, the generated database design is stored in the repository. This model can subsequently be used to generate the SQL statements required to create the database itself.

The Transformer gives you complete control over the scope of the design process. It allows you to choose the type of entity-to-table mappings used, the detail of those mappings, and the types of element that the process creates or modifies. It enables you to create consistent server models quickly in the manner best suited to your requirements. You can develop the model as a whole, or concentrate on small areas of it. Similarly, you can create full working designs, with full detail in a single step, or you can progressively refine the model, adding detail as you go.


Entity modeling - database design principles

Related Topics

When you record an entity model in the repository, you are specifying the types of object that your business needs to manipulate and about which it needs to store information. The purpose of the database design (or server model) is to define a physical database schema that you can use to store these objects.

Entities and attributes

Entity relationships

Unique identifiers

Entity sub-types

Entities and attributes

Each entity in the entity model is described by its attributes. In the illustration below, the entity model contains an employee entity. The attributes of this entity define the type of information that your business needs to store about each individual employee. Typically, the details of any given employee will be stored as a separate row in the database. Therefore, when you define your database, you will define one or more database tables containing suitable columns to store your employee data.

Entity relationships

The entities in your model have relationships. For example, if your entity model contains an employee entity, each employee may be responsible for one or more bookings and each booking may be approved by one and only one employee. The database design must be able to store and enforce these relationships. You do this by defining foreign keys between the tables used to store the entities.

Unique identifiers (UIDs)

An entity may contain one or more unique identifiers. These are attributes, relationships, or combinations of both, that uniquely define each instance of an entity. For example, if your entity model contains an employee entity, you might specify that each employee has an employee number, and that this is a unique identifier. This means that each employee must have a unique number.

The database design must also ensure that this is the case. To do this, you define primary and unique keys on the tables.

Entity sub-types

Entities may contain other entities. For example, if your entity model contains a title entity it may contain both movies and games.

	Movie and game are both referred to as sub-types of title. That is, each movie is a title, but there are some additional details that you need to know about a movie such as duration. Similarly, title is referred to as a super-type of movie and of game. A sub-type inherits the attributes and relationships of its super-type. For example, the title entity defines the details that are common to all titles, while the purpose of the movie entity is to define those details that are specific to movies.  When you store the details of a movie in your database, you will record those details that are common with other types of title (such as product code) and those details that are specific to a movie (such as movie category).
There are a variety of approaches when implementing entity sub-types as tables in the database design:

•	Single table (also known as super-type implementation)

•	Separate table (also known as explicit sub-type implementation)

•	Implicit sub-type implementation

•	Arc implementation

For more information, see Deciding how to implement entity sub-types using the Database Design Transformer.

Single table implementation

Using this approach, the sub-types are implemented in the super-type table. This is the default approach. How

Separate table implementation

In this approach, separate tables are created for each sub-type. The sub-type tables hold both the sub-type specific details and the attributes inherited from the super-type. How

Implicit sub-type implementation

In this approach, separate tables are created for each sub-type and for the super-type. The sub-type tables hold both the sub-type specific details and the attributes inherited from the super-type. The super-type table holds only the super-type specific details, not those of its sub-types. How

Arc implementation

In this approach, separate tables are created to hold the sub-type specific details and the super-type details are stored in the super-type table. How

Each instance of the sub-type entity is stored partly in the sub-type table and partly in the super-type table. The Database Design Transformer generates foreign key columns on the super-type table to join the super-type to each of its sub-types. An arc is used to implement mutually exclusive relationships between a super-type entity and its sub-types.


What the Database Design Transformer does

Related Topics

The purpose of the Database Design Transformer is to create a first-cut database design that allows you to store the details of each instance of the entities specified in your entity model. You can display the generated design and refine it further using a server model diagram in the Design Editor.

During the transformation process, Database Design Transformer:

Creates/updates tables:

to implement entities

to implement many-to-many relationships

Creates/updates columns:

to implement attribute to column mappings

to discriminate between multiple entities mapping to the same table

to implement foreign keys

to implement surrogate primary keys

Creates/updates primary and unique keys:

to implement UIDs

to provide primary keys where none are present

Creates/updates foreign keys:

to implement relationships

to support mapping styles and choices

Assigns foreign keys to arcs:

to support entity relationship arcs

to support mapping styles

Creates indexes:

to support the foreign keys

Creates/assigns allowable values to columns:

to discriminator columns

to columns that implement attributes

The Database Design Transformer also creates and maintains additional elements to support the design. These include: key components for the generated keys, sequences for surrogate primary keys, index entries, and Table to Entity mapping elements to record the association between a table and the entity that it implements.


Database Design Transformer creates tables to implement entities

Related Topics

An entity to table mapping indicates that the table is used to store instances of the entity. The Database Design Transformer creates or updates suitable table definitions and also creates and maintains definitions of other database elements, such as columns and keys.

There are a variety of approaches to mapping entities to tables. The Database Design Transformer allows you to choose the one best suited to your requirements. The methods supported are:

•	Single table (also known as super-type implementation)

•	Separate table (also known as explicit sub-type implementation)

•	Implicit sub-type implementation

•	Arc implementation

Data warehouse considerations

If your entity model contains entity definition classified as ‘facts’ or ‘dimensions’ for a data warehouse, the Database Design Transformer creates corresponding fact table and dimension table definitions in the repository, i.e. the Data warehouse Type property on the tables are set to ‘Fact’ or ‘Dimension’ respectively.

In addition, for each fact table, appropriate primary key constraints, foreign key constraints and BITMAP index definitions are created.

Note:	The Data Warehouse? property of the application system must be set to ‘Yes ‘.


Database Design Transformer creates tables to implement M:M relationships

Related Topics

To implement a many-to-many (M:M) relationship, the Database Design Transformer creates an intersection table and associated foreign keys. The intersection table holds instances of the relationship.

The Database Design Transformer creates foreign keys from the intersection table to those tables that implement the two entities in the relationship.


Database Design Transformer creates columns to implement attributes

Related Topics

The attributes of the entity represent the items of information that you need to store about each instance. For example, you need to store the description of each title. When the database design is complete, the table will have columns to store the attribute data.

The Database Design Transformer regards the mappings of attributes to columns as a more detailed level of entity to table mapping. So, you could develop the tables first, and then develop the columns and keys.

An entity may be mapped to more than one table. In the following example, the sub-type entities of Title are mapped to separate tables.

Therefore an attribute of an entity may be mapped to several columns, each in different tables. In this example, the super-type entity is not mapped to a table, but its sub-types are. The sub-types inherit the super-type's attributes, and therefore each one requires a column to store each attribute that it inherits.

Similarly, relationships may be implemented by several foreign keys and unique identifiers by several unique keys.


Database Design Transformer creates discriminator columns

Related Topics

The Database Design Transformer also generates discriminator columns if you have several entities mapped to the same table. For example, where you have used single table implementation, each sub-type entity is mapped to the table for its super-type.

The Database Design Transformer creates a discriminator to distinguish between instances of the different sub-types. It also creates allowable values for the discriminator column, one for each sub-type.


Database Design Transformer creates columns to implement foreign keys

Related Topics

The Database Design Transformer creates and maintains the columns that implement foreign keys. It bases these foreign key columns on those for the referenced primary or unique key.


Database Design Transformer creates columns to implement surrogate primary keys

Related Topics

The Database Design Transformer creates columns for surrogate primary keys. These surrogate keys are created where there is no existing primary key and no unique key that is suitable for use as the primary key.

The Database Design Transformer can create surrogate primary keys for all tables. This can be useful if you require that all primary keys have similar properties.


Database Design Transformer creates unique keys to implement unique identifiers

Related Topics

A unique identifier on an entity is a combination of attributes and/or relationships that serves to uniquely identify each instance of the entity. The Database Design Transformer uses the UID to create unique keys on the tables that implement the entity. These keys can then be used to uniquely identify a particular row in the table that stores the entity.

Note that a UID may be implemented by more than one unique key.


Database Design Transformer creates primary keys for all tables

Related Topics

Where a table does not have a primary key, the Database Design Transformer attempts to find one that is suitable for use as the primary key of the table. If it succeeds, the key definition is changed to be the primary key.

If it does not succeed, the Database Design Transformer creates a surrogate primary key. This is a key consisting of a single column. This column takes its value from a sequence also created by the Database Design Transformer.


Database Design Transformer creates foreign keys to implement relationships

Related Topics

Just as the relationships on an entity relationship diagram show how two entities are associated, a foreign key shows how two tables are related. Therefore, to implement a given relationship between two entities, the Database Design Transformer has to create one or more foreign keys between the tables that store instances of the entities.

Depending on the mappings used, a relationship may be implemented by more than one foreign key.

Each foreign key is attached to the table that implements the relationship, and references a unique or primary key in the related (or referenced) table.

The foreign key is implemented by one or more columns. Each of these references the equivalent column in the referenced unique or primary key. Therefore, to create the foreign key components, the Database Design Transformer needs to know the key components of the primary key or keys in the referenced table.

Note:	Many-to-many relationships are implemented by creating intersection tables. Foreign keys are created between the intersection table and the tables that implement the entities involved in the relationship.


Database Design Transformer creates foreign keys to support sub-type mapping styles

Related Topics

In addition to creating foreign keys to implement entity relationships, the Database Design Transformer may also create foreign keys to support complex sub-type mappings that you have chosen.


Database Design Transformer creates foreign key arcs to implement entity relationship arcs

Related Topics

Just as relationships are implemented by foreign keys, the Database Design Transformer implements entity relationship arcs as foreign key arcs.


Database Design Transformer creates foreign key arcs to support sub-type mapping styles

Related Topics

The Database Design Transformer may need to create foreign key arcs to ensure that only a single instance of a given super-type entity is referenced by a particular instance of a relationship.


Database Design Transformer creates indexes to support foreign keys

Related Topics

Wherever a foreign key is used in a SQL statement to join two tables, you can often improve the performance of the query by adding an index. Therefore, to ensure that your database design is efficient, the Database Design Transformer can create index definitions based on your foreign keys.

The indexes created in this way enhance the performance of any multi-table queries.


Database Design Transformer creates allowable values for discriminator columns

Related Topics

The Database Design Transformer creates allowable values for a discriminator column based on the entities between which it discriminates.

Discriminator columns are generated to distinguish between sub-type entities that are implemented in the same table as their super-type entity. The discriminator column is suffixed with _TYPE.

The Database Design Transformer generates an allowable value for each sub-type entity, based on the entity short name.

By default an allowable value is not generated for the super-type entity. However you can use the ‘Allow instantiable super-types’ option on the Other Settings tab of the Settings dialog to generate an allowable value for the super-type.

Allowable values can be validated in the database using check constraints. A check constraint is generated implicitly by the Server Generator to validate allowable values defined at the column level.


Database Design Transformer creates allowable values for attribute columns

Related Topics

The Database Design Transformer creates allowable values for a column based on the allowable values for the attribute that it implements.

Allowable values can be validated in the database using check constraints. A check constraint is generated implicitly by the Server Generator to validate allowable values defined at the column level.


Database Design Transformer dependencies

Related Topics

The Database Design Transformer gives you great control over the scope of the design processing. For example, you can choose to create table definitions for a group of entities, but leave the column definitions until later. When you are doing this, you need to be aware of the dependencies involved in the process.

The key dependencies that you need to remember when choosing your run set and your options are as follows. The Database Design Transformer cannot:

•	create a foreign key unless the referenced table has a primary key

•	create foreign key components unless the equivalent primary key also has components

•	create a foreign key unless the referenced entity is implemented

•	create an intersection table unless both entities involved in the many-to-many relationship are implemented

This picture illustrates one such dependency.

The Database Design Transformer cannot create a foreign key to implement the relationship because the second entity has not yet been mapped to a table. Similarly, if the Employees table exists but does not yet have columns, the Database Design Transformer can create the foreign key but cannot create and assign the foreign key columns.


Database Design Transformer limitations

Related Topics

The Database Design Transformer

•	runs only within the current application system. It has read access to elements that are referenced from the current application system. Elements not referenced from the current application system are not visible to, or used by, the Transformer.

•	uses the name of a unique identifier to map it to a unique key. If you subsequently change the unique identifiers on an entity, the Database Design Transformer will be unable to accurately identify and modify the associated key.

•	cannot remove redundant columns, keys or indexes.

•	does not delete elements, e.g., if you create an entity with three attributes and then run the Transformer, the result is a table with three columns. If you then delete one attribute and re-run the Transformer with the entity still mapped to the same table, the result will still be a table with three columns as the third column will still exist.


Default values of the Database Design Transformer

Related Topics

When run in Default mode, the default settings of the Database Design Transformer are as follows:

•	when an entity is included in the run set, all of its components are also included

•	all entity super-types are included in the run set: sub-types are mapped using the single table mapping approach

•	all types of elements are created (create run options)

•	all types of elements are not modified (modify run options)

Note:	If you change the create or modify run options, the new options become the default settings for any future runs of the Database Design Transformer.

The following default values are used:

Database	Default
Commit frequency for changes	After each phase
Keys
On delete cascade rule	Restricted
On update cascade rule	Restricted
Implementation level for constraints	Server
Create surrogate keys	Unchecked
Domain for new surrogate primary keys	Null
Maximum identifier length	30
Other Settings
Prefix foreign keys	Checked
Prefix surrogate keys	Checked
Prefix columns	Unchecked
Table prefix	Null


Output Window
Output Window - Database Design Transformer

Related Topics

The Output Window displays information during the transformation process. The Output Window includes a Cancel button. If you chose the ‘After Each Phase commit frequency’ option, pressing the Cancel button performs a rollback to the last commit point.

Note:	If you press Cancel, you must exit from and then re-invoke the Database Design Transformer before running it again.

When the transformation process is complete, the Output Window includes:

•	a Close button for minimizing the Output Window. The Output Window cannot be closed; it can only be minimized.

•	a Copy button for copying the output information to the clipboard.


Transforming entity models step-by-step, using Database Design Transformer
Transforming entity models step-by-step

Related Topics

If you have created entities and relationships as part of your analysis, you can use the Database Design Transformer to create a normalized database design from the entity relationship model. The Database Design Transformer creates tables from entities, columns from attributes, foreign keys from relationships. It also creates indexes to support foreign keys.

To create a database design from an entity model:

Next Step

Modify the database design generated by the Transformer. How

Display the tables generated by the Transformer. How


Invoking the Database Design Transformer

Related Topics	Where am I?

Before running the Database Design Transformer, your entity relationship model should be complete.

The Database Design Transformer can be invoked from the Oracle Designer Front Panel, the Repository Object Navigator, or the Entity Relationship Diagrammer. If you invoke the Transformer from the Oracle Designer Front Panel or the Repository Object Navigator, all super-type entities in the current application system will be included in the default run set. If you invoke the Transformer from the Entity Relationship Diagrammer with some entities selected, those entities are included in the default run set; if no entities are selected, all super-type entities in the current diagram are included in the default run set.

To invoke the Database Design Transformer:

1.	Do one of the following:

•	Click the Database Design Transformer icon in the Oracle Designer Front Panel.

•	Select Utilities→ Database Design Transformer from the Entity Relationship Diagrammer.

•	Select Utilities→Designer→Database Design Transformer from the Repository Object Navigator.

1. 	Select the application system you require.


Choosing the mode of the Database Design Transformer

Related Topics	Where am I?

The Database Design Transformer can be run in two modes: default or custom.

•	Default mode enables you to quickly run the Transformer with default settings. Simply choose whether you want to run the Transformer on selected entities or all super-type entities and then click the Run button.

•	Custom mode enables you to customize the Transformer before running it, e.g., you can change the mappings of elements, the run options or specify some of the settings.

To choose the mode of the Transformer:

1.	Invoke the Database Design Transformer. How

2.	Select the mode you require from the Mode tab in the Database Design Transformer dialog.

If you do not wish to display the Mode tab the next time you invoke the Database Design Transformer, uncheck Show This Tab At Startup.


Selecting the default run mode of the Database Design Transformer

Related Topics	Where am I?

The default mode provides a quick and easy way of running the Database Design Transformer. You can choose to run the Database Design Transformer in default mode to create database design elements based on one of the following:

•	the entities selected when you invoked the Transformer

•	all the super-type entities.

The scope of 'all entities' differs according to where you invoked the Database Design Transformer from:

•	if you invoked the Transformer from the Oracle Designer Front Panel, all super-type entities in the current application system will be included

•	if you invoked the Transformer from a diagram in the Entity Relationship Diagrammer, all super-type entities in the diagram will be included.

To select the default run mode:

1.	Invoke the Database Design Transformer. How

2.	Click either Selected entities or All entities in the default section of the Mode tab.


Choosing the Database Design Transformer run set

Related Topics	Where am I?

The run set determines which elements are processed by the Database Design Transformer. When you include an entity in the run set, you by default include all the components for that entity. In order that the Database Design Transformer can generate a complete design, you should ensure that the entities at both ends of a relationship are included in the run set.

Note:	After choosing the run set, you must specify the run options. How

To select the run set:

1.	Invoke the Database Design Transformer and choose the custom mode. How

2.	Select the Table Mappings tab in the Database Design Transformer dialog.

3.	Check the In Set check box for each entity to be included in the run set. Tip

If an entity in the run set has no mapping, a new table definition will be created. If you are running the Transformer for the first time, no mappings will exist. You therefore need only select the run set on the Table Mappings tab, and ensure that the run options are set to generate the types of element you require.

If an entity in the run set is already mapped to a table, the existing table will be updated. You can modify an existing design generated by the Transformer using the Other Mappings tab in the Database Design Transformer. How


To quickly add all super-type entities to the run set, click the Select All button.

To quickly remove all entities from the run set, click the Select None button.


To quickly add all items to the run set, click the Select All button.

To quickly remove all items from the run set, click the Select None button.


Deciding how to implement entity sub-types using the Database Design Transformer
Deciding how to implement entity sub-types

Related Topics	Where am I?

When using the Database Design Transformer, entity sub-types can be implemented in four different ways:

How to implement a single table approach for mapping sub-types

How to implement a separate table approach for mapping sub-types

How to implement an arc approach for mapping sub-types

How to implement an implicit sub-type approach

Choosing the best approach

It is not possible to define a single solution for entity sub-type implementation. You must consider several factors before deciding which of the four approaches to use. Your decision may be influenced by the distribution of attributes and relationships within the super-type and sub-types, or the expected volumes of each sub-type. You may favor approaches that minimise redundant storage, or ones which result in a simpler database design. Or, there may be other requirements that guide the decision to a particular approach.

The ‘single table’ and ‘separate table’ approaches are the most widely used. The other two approaches are used less frequently, possibly because of the more complex database designs that they produce.

When to use the ‘single table’ approach

This is the default mapping for the Database Design Transformer and can be used successfully for most entity models. It is particularly suitable when considering the following factors:

Factor	Considerations
Common attributes	If most attributes exist at the super-type rather than the subtype level, the data model suggests a single table implementation.
Common relationships	If most relationships are at the super-type level, the model suggests that the business sees the super-type as a single entity.
Similar validation	If most validation checks apply to all subtypes, this suggests a single table implementation.
Pattern of retrieval	If information from the subtypes often appears in the same report, use a single implementation.
Advantages:

•	Simple database design

Disadvantages:

•	Some redundancy, especially if some sub-type entities have a large number of attributes. The extent of the redundancy varies but will be worse if the volume of large sub-type entities is low and the volume of small sub-type entities is high.

•	Mandatory attributes (and relationships) in a sub-type are implemented as ‘optional columns’ in order to store rows for the other sub-types. To enforce optionality, you may need to add constraints to your database design that depend on the value of the discriminator column.

When to use the ‘separate table’ approach

This approach is best suited to entity models where most of the relationships join to sub-types rather than to the super-type or,  where most of the attributes are defined within sub-types rather than in the super-type. It is particularly suitable when considering the following factors:

Factor	Considerations
Attributes	If most attributes exist at the sub-type rather than the super-type level, the data model suggests a separate table implementation.
Relationships	If most relationships are at the sub-type level, the model suggests that the business sees separate entities.
Pattern of retrieval	If the data from different sub-types appears in distinctly different reports, use a separate table implementation.
Pattern of update	If the sub-types are updated under quite different conditions or in different types of transactions, this indicates a separate table implementation.
Advantages:

•	Mandatory attributes and relationships in sub-types remain mandatory in the database design, unlike the single table approach.

Disadvantages:

•	If an entity with a relationship to the super-type is transformed into a table, it must have one foreign key to each sub-type and these foreign keys must be in an arc.

•	Because foreign keys to sub-types are in an arc, they must be optional and therefore primary keys cannot be based on these foreign key columns.

When to use the ‘arc approach’

If access to the super-type must be straightforward and mandatory attributes from subtypes must be implemented as mandatory columns, use the arc implementation.

Advantages:

•	Mandatory attributes in a sub-type are implemented as ‘mandatory columns’.

Disadvantages:

•	More complex database design.

When to use the ‘implicit sub-type’ approach (instantiable super-type)

This is the same as the ‘separate table’ approach except that there is also a table for the super-type. For example:

Super-type (PERSON)	--> mapped to a table

Sub-types (EMPLOYEE, CONTRACTOR)	--> each mapped to a table

It is particularly suitable if you have instances of a super-type that you cannot classify as being one of its subtypes.

This approach may be useful if your entity model is not yet complete, e.g. not all sub-types have been identified. An alternative in this situation would be to introduce a new sub-type with no attributes and use the ‘separate table’ approach instead, for example:

Super-type (PERSON)	--> not mapped to a table

Sub-types (EMPLOYEE, CONTRACTOR, OTHER PERSON*)	--> each mapped to a table

* The sub-type entity 'OTHER PERSON' in this example has no attributes.

Advantages:

•	Same as separate table approach.

Disadvantages:

•	More complex database design.


Specifying Database Design Transformer run options

Related Topics	Where am I?

You can control the scope of the Database Design Transformer run set in two ways. You can specify which types of elements the Transformer can create and which types of elements and their properties the Transformer can modify.

Note:	Elements must be included in the run set in order for them to be created or modified. How

To specify the types of elements to be created:

1.	Invoke the Database Design Transformer and choose the custom mode. How

2.	Select the Run Options tab in the Database Design Transformer dialog.

3.	Check the Create check boxes for those types of elements that you want the Transformer to create.

To specify the types of element to be modified:

1.	Select the Run Options tab in the Database Design Transformer dialog.

2.	Check the Modify check boxes for those types of elements that you want the Transformer to modify.

1. 	Check the check boxes for those element properties you want the Transformer to modify.

Note:	If you have chosen to alter the default column sequence using the Column Component Priority options, the ‘Modify Column’ and ‘Modify Column Sequencing’ options must both be set.


Displaying the Database Design Transformer run set

Related Topics	Where am I?

Before you run the Database Design Transformer you may wish to display a list of all those elements that will be processed by the Transformer.  A summary of the number of elements in the run set is displayed on the Mode tab.

To show the run set:

•	Click on the Show Run Set button in the Database Design Transformer dialog


Changing Database Design Transformer settings

Related Topics	Where am I?

The Settings dialog enables you to specify many of the settings used by the Database Design Transformer. Settings apply to all elements within the current run set.

To change the settings:

1.	Invoke the Database Design Transformer. How

2.	Click the Settings button.

3.	Set the settings as required. For example:

•	change the database and database user definition in which newly generated tables and indexes are included (Database tab)

•	determine the default storage definitions for tables and indexes (Database tab)

•	choose how often the Transformer commits changes to the repository (Database tab)

•	specify the cascade rules for new foreign keys (Keys tab)

•	set the implementation level for constraints (Keys tab)

•	determine whether surrogate keys are created for all new tables (Keys tab)

•	specify the maximum unique identifier length (Keys tab)

•	determine which elements you want prefixes generated for (Other Settings tab)

•	set the priority for ordering column components (Other Settings tab)

•	allow instantiable super-types (Other Settings tab)


Running the Database Design Transformer

Related Topics	Where am I?

To run the Database Design Transformer:

1.	Invoke the Database Design Transformer. How

2.	Click the Run button in the Database Design Transformer dialog

During the transformation process, messages are displayed in the Output Window.  About

Next Step

Modify the database design generated by the Transformer. How

Display the tables generated by the Transformer. How


Displaying tables generated by the Database Design Transformer

Related Topics

You can view the table definitions generated by the Database Design Transformer using the Design Editor. A server model diagram can be used to display and edit tables graphically on a diagram. Alternatively you can use the  Navigator to browse and edit the table definitions.

Next Step

Further refine the database design using the Design Editor. How

Generate the DDL commands required to implement the design on a database. How


Saving changes made by the Database Design Transformer

Related Topics

Before you run the Database Design Transformer, you can choose how changes are saved (or committed) to the repository.

Note:	If there are uncommitted changes when you try to exit the Database Design Transformer dialog, you are prompted to save the changes.

To commit changes at any time:

•	Click the Commit button in the Database Design Transformer dialog

To commit changes after each logical phase of the run:

1.	Click the Settings button on the Database Design Transformer dialog.

2.	On the Database tab, choose After each phase from the commit frequency.

To commit changes when the entire process has finished:

1.	Click the Settings button on the Database Design Transformer dialog.

2.	On the Database tab, choose At end of run from the commit frequency.

To validate your design without committing changes:

1.	Click the Settings button on the Database Design Transformer dialog.

2.	On the Database tab, choose Don't commit from the commit frequency.

If you are satisfied with the results, you can save them by selecting the Commit button.


Modifying the design generated by the Database Design Transformer

Related Topics

The Database Design Transformer can be run iteratively.  Therefore, if your entity model changes you can modify a previously generated database design, rather than transforming the entire entity model again.

When re-generating your database design, consider the following:

•	If you change the entity model while the Transformer is running, you must exit and re-invoke the Transformer before running it again to take account of the changes.

•	Once created, some elements such as surrogate primary keys and discriminator columns cannot be modified.

•	The Database Design Transformer cannot remove redundant columns, keys or indexes.

Changing the entity to table mappings

Changing the attribute to column mappings

Changing the unique identifier to unique/primary key mappings

Changing the relationship to foreign key mappings

Changing the foreign key to index mappings


Changing Database Design Transformer entity to table mappings

Related Topics

You can modify the Database Design Transformer run set so that an entity is mapped to an existing table, or so that a new table is created.

When you include an entity in the run set, the Database Design Transformer automatically includes all of its components. You can modify any of these mappings, or exclude them from the run set using the Other Mappings tab.

To map an entity to an existing table definition:

1.	Select the Table Mappings tab in the Database Design Transformer dialog.

2.	In the Table field next to the entity that you want to map, select the name of the required table.

3.	Check In Set for the entity to include it in the run set. Tip

4.	Specify the run options, ensuring that you allow tables to be modified.  How

To map an entity to a new table definition:

1.	Select the Table Mappings tab in the Database Design Transformer dialog.

2.	If the entity is already mapped to an existing table, cancel this mapping by selecting the blank field from the Table dropdown list. The Map Type field should now display "No Mapping".

A table may have many dependencies. Therefore when you change a mapping, you must consider what other elements may be affected and use the Transformer to modify these as well.

Note:	Removing a mapping does not remove the table definition but does remove the ‘Table to Entity’ element created to store details about the mapping. A new ‘Table to Entity’ element is created for the new mapping.

3.	Check In Set for the entity to include it in the run set. Tip

4.	Specify the run options, ensuring that you allow tables to be created. How


Changing Database Design Transformer attribute to column mappings

Related Topics

You can modify existing Database Design Transformer mappings between attributes and the columns that implement them. To do this, the entity must already be mapped to a table. You can map an attribute to a new column, to an existing column or exclude an attribute from the run set. For example, if you create additional attributes on an entity after having generated the table to implement it, you can map the new attributes to the column definitions.

Note:	You cannot modify attribute to column mappings if the entity is mapped to a new table (No Mapping).

To map an attribute to an existing column definition:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Columns mapping option.

3.	From the list of tables, select the name of the table in which the attribute is implemented.

4.	In the spreadtable, select the name of the required column adjacent to the attribute that you want to map.

5.	Check In Set to include the attribute in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run options, ensuring that you allow columns to be modified. How

To map an attribute to a new column definition:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Columns mapping option.

3.	From the list of tables, select the name of the table in which the attribute is implemented.

4.	If the attribute is already mapped to an existing column, cancel this mapping by removing the column name from the Columns field adjacent to the attribute in the spreadtable.

A column may have many dependencies, so, when you change a mapping, you must consider what other elements may be affected, and use the Transformer to modify these as well.

Note:	Removing a mapping does not remove the column definition.

5.	Check In Set to include the attribute in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run options, ensuring that you allow columns to be created. How


Changing Database Design Transformer unique identifier to unique or primary key mappings
Changing the unique identifier to unique/primary key mappings

Related Topics

You can modify Database Design Transformer mappings between entity unique identifiers (UIDs) and the unique or primary key on the table that implements the entity. You can map a UID to an existing key definition or create a new key definition for the UID.

Note:	You cannot modify unique identifier mappings if the entity is mapped to a new table (No Mapping).

To map a unique identifier to an existing unique or primary key:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Primary Keys mapping option.

3.	From the list of tables, select the name of the table in which the unique identifier is implemented.

4.	In the spreadtable, select the name of the required key adjacent to the unique identifier that you want to map.

5.	Check In Set to include the unique identifier in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run options, ensuring that you allow keys to be modified.  How

To map a unique identifier to a new unique or primary key:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Primary Keys mapping option.

3.	From the list of tables, select the name of the table in which the unique identifier is implemented.

4.	If the UID is already mapped to an existing key, cancel this mapping by removing the key name from the Keys field adjacent to the unique identifier in the spreadtable.

A key may have many dependencies, so, when you change a mapping, you must consider what other elements may be affected, and use the Transformer to modify these as well.

Note:	Removing a mapping does not remove the key definition.

5.	Check In Set to include the unique identifier in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run options, ensuring that you allow keys to be created. How


Changing Database Design Transformer relationship to foreign key mappings

Related Topics

You can modify Database Design Transformer mappings between entity relationships and the foreign keys that implement them. You can map a relationship to an existing foreign key or create a new foreign key for the relationship. Where the Database Design Transformer creates or maintains the foreign key, it also maintains the columns that populate the key.

Note:	You cannot modify relationship mappings if the entity is mapped to a new table (No Mapping).

To map a relationship to an existing foreign key:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Foreign Keys mapping option.

3.	From the list of tables, select the name of the table in which the relationship is implemented.

4.	In the spreadtable, select the name of the required foreign key adjacent to the relationship that you want to map.

5.	Check In Set to include the relationship in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run options, ensuring that you allow foreign keys to be modified. How

To map a relationship to a new foreign key:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Foreign Keys mapping option.

3.	From the list of tables, select the name of the table in which the relationship is implemented.

4.	If the relationship is already mapped to an existing foreign key, cancel this mapping by removing the key name from the Foreign Keys field adjacent to the relationship in the spreadtable.

A foreign key may have many dependencies, so, when you change a mapping, you must consider what other elements may be affected, and use the Transformer to modify these as well.

Note:	Removing a mapping does not remove the foreign key definition.

5.	Check In Set to include the relationship in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run option, ensuring that you allow foreign keys to be created.  How


Changing Database Design Transformer foreign key to index mappings

Related Topics

To improve the performance of queries, you may wish to create indexes on the foreign keys used in any join conditions. The Database Design Transformer allows you, therefore, to create and maintain index definitions based on your foreign key definitions. You can modify existing mappings between foreign keys and the indexes created for them. You can map a foreign key to an existing index definition, or to a new index definition.

Note:	You cannot modify foreign key to index mappings if the entity is mapped to a new table (No Mapping).

To map a foreign key to an existing index:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Indexes mapping option.

3.	From the list of tables, select the name of the table containing the foreign key for which the index is required.

4.	In the spreadtable, select the name of the required index adjacent to the relationship that you want to map.

5.	Check In Set to include the foreign key in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run options, ensuring that you allow indexes to be modified.  How

To map a foreign key to a new index:

1.	Select the Other Mappings tab in the Database Design Transformer dialog.

2.	Select the Indexes mapping option.

3.	From the list of tables, select the name of the table containing the foreign key for which the index is required.

4.	If the foreign key is already mapped to an existing index, cancel this mapping by removing the index name from the Indexes field adjacent to the foreign key in the spreadtable.

A foreign key may have many dependencies, so, when you change a mapping, you must consider what other elements may be affected, and use the Transformer to modify these as well.

Note:	Removing a mapping does not remove the index definition.

5.	Check In Set to include the foreign key in the run set. Tip

Note:	The entity must also be included in the run set (using the In Set option on the Table Mappings tab).

6.	Specify the run options, ensuring that you allow indexes to be created.  How


Implementing a single table approach to mapping sub-types (Database Design Transformer)
Implementing a single table approach to mapping sub-types

Related Topics	Where am I?

You can implement a super-type entity and each of its sub-type entities as a single table. About

You can map the entities to an existing table or have the Database Design Transformer create a new table definition. This topic describes one way of implementing a single table mapping. It assumes that none of the entities are currently mapped. You can create columns at the same time as the table or at a later stage.

To implement a super-type and its sub-types in a single table:

1.	Include the super-type entities that you want to map to new tables in the run set.

Note:	By default, including an entity in the run set also includes the mappings for its components. If you wish, you can use the Database Design Transformer dialog to alter these mappings and/or exclude them from the run set.

2.	Make sure that their sub-type entities have Included mappings.

When a single table (or super-type implementation) is used, the Transformer creates a discriminator column to distinguish between instances of different sub-types. It then generates an allowable value for each sub-type entity only. If you also want to create an allowable value for a super-type entity, choose the Settings button and then check Allow instantiable super-types on the Other Settings tab in the Database Design Transformer Settings dialog.

3.	Specify the run options, making sure that the create tables option is checked. How

4.	Click Run, to generate the table definitions.

The Transformer creates table definitions from the entities and creates Table to Entity mappings. The sub-type entities are given a mapping of Included.


Implementing a separate table approach to mapping sub-types (Database Design Transformer)
Implementing a separate table approach to mapping sub-types

Related Topics	Where am I?

You can implement some or all sub-type entities as separate tables.  The super-type entity may not be mapped to a table at all. Each instance of the sub-type is completely stored in the sub-type table. About

You can map entities to existing tables or have the Database Design Transformer create new table definitions. This topic describes one way of implementing separate table mappings. It assumes that none of the entities are currently mapped. You can create columns at the same time as the table or at a later stage.

To implement sub-types in separate tables:

1.	Include the sub-type entities that are to be mapped to tables in the run set.

Note:	If all of the sub-type entities are mapped in this way, the super-type is usually not mapped to a table, i.e. it is not included in the run set. If you do map the super-type to a table, it is usually in order to store instances of other sub-types. About

2.	Specify the run options, making sure that the create tables option is checked. How

3.	Click Run, to generate the table definitions.

The Transformer creates table definitions from the entities and Table to Entity mappings.


Implementing an implicit sub-type approach (Database Design Transformer)
Implementing an implicit sub-type approach

Related Topics	Where am I?

You can implement a super-type entity as a table that holds only super-type specific details and the sub-type entities as separate tables. These sub-type tables hold both the sub-type specific details and those details that they inherit from the super-type. Each instance of the sub-type is completely stored in the sub-type table. About

You can map entities to existing tables or have the Database Design Transformer create new table definitions. This topic describes one way of implementing such mappings. It assumes that none of the entities are currently mapped. You can create columns at the same time as the table or at a later stage.

To implement an implicit sub-type approach:

1.	Include the super-type entities and the sub-type entities that are to be mapped to tables in the run set.

2.	Make sure that the create tables option is selected on the Run Options tab.

3.	Click Run, to generate the table definitions.

The Transformer creates table definitions from the entities and Table to Entity mappings.


Implementing an arc approach to mapping sub-types (Database Design Transformer)
Implementing an arc approach to mapping sub-types

Related Topics	Where am I?

You can implement a super-type entity as a super-type table and each sub-type entity as corresponding sub-type tables. The sub-type tables implement the details specific to the sub-entities, while those inherited from its super-type are stored in the super-type table.  About

You can map entities to existing tables or have the Database Design Transformer create new table definitions. This topic describes one way of implementing such mappings. It assumes that none of the entities are currently mapped. You can create columns at the same time as the table or at a later stage.

To implement an arc approach to mapping sub-types:

1.	Include both the super-type entities and the sub-type entities that you want to map in the run set.

2.	Choose the Run Options, making sure that generation of tables is permitted, but that column and key generation is not permitted.

3.	Click Run, to generate the table definitions.

The Transformer creates table definitions from the entities and the initial Table to Entity mappings.

4.	For each relevant sub-type entity, set the Arc flag.

5.	Specify the run options, selecting the generation and modification of tables, columns and keys. How

6.	Using the same run set, click Run to generate the table definitions.

The Transformer creates primary keys for all the tables and generates the required foreign keys to implement relationships between the super-type table and its sub-types.


Transforming entity models step-by-step


Transforming entity models step-by-step


Transforming entity models step-by-step


Transforming entity models step-by-step


Transforming entity models step-by-step


Transforming entity models step-by-step


Transforming entity models step-by-step


Transforming entity models step-by-step


Transforming the entity model step-by-step
Transforming entity models step-by-step


Repository elements generated by the Database Design Transformer

Related Topics

This section describes the properties of repository elements generated from the entity relationship model. You can examine these properties and change their values using a Server Model Diagram or the Navigator in Design Editor.

Generated tables

Tables implementing entities

Table entity usage elements

Table implementations

Generated columns

Columns implementing attributes

Allowable values for columns implementing attributes

Discriminator columns

Allowable values for discriminator columns

Generated primary keys

Primary or unique keys implementing UIDs

Primary or unique key components implementing UID entries

Surrogate primary keys

Surrogate primary key components

Surrogate primary key columns

Primary keys for arced sub-type tables

Generated foreign keys

Foreign key columns

Foreign key components

Foreign keys for many-to-one or one-to-one relationships

Foreign keys for arced sub-type tables

Generated intersection tables

Intersection tables for many-to-many relationships

Primary keys for intersection tables

Foreign keys for intersection tables

Generated sequences

Surrogate primary key sequences

Sequence implementations

Generated indexes

Index storage for unique keys and primary keys

Index storage for indexes

Indexes for foreign keys

Index entries for foreign key components


Properties of tables generated by Database Design Transformer to implement entities

Related Topics

If an entity is included in the Database Design Transformer run set, the relevant table definition is created or updated when you run the Transformer.

In Design Editor, table definitions can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following sections describe only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all table properties, refer to the appropriate repository object definition topic  About

Name

Alias

Col Prefix

Display Title

Volumes

Start Rows

End Rows

Data warehouse

Data warehouse Type

Documentation

Notes

User/Help Text

Name

Modification Category:Name
The Plural name of the primary entity for this table. This is prefixed with the value (if any) of the Table prefix option, set on the Other Option tabs in the Database Design Transformer Settings dialog.

Note:	The primary entity is the entity that is mapped to a particular table and is the entity super-type of all the other entities that are mapped to that table.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the ‘Maximum identifier length’ option (set on the Keys tab in the Settings dialog). The maximum length is 30.

If the resulting name is not unique among all database objects (not just tables, but also views, indexes and constraints) in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Alias

Modification Category:Name
A unique alias for the table name. The Database Design Transformer uses the short name of the entity that is the super-type of all other entities that are mapped to this table. The name is capitalized, any punctuation characters are converted to underscores and it is truncated to the length specified by the ‘Maximum identifier length’ option (set on the Keys tab in the Settings dialog). The maximum length is 30.

If the resulting alias is not unique among all database objects in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Col. Prefix

Modification Category:Name
Set to the alias of the table if you have chosen to prefix columns (set on the Other Settings tab in the Database Design Transformer Settings dialog box).

Display Title

Modification Category:Name
The name of the table, with underscores converted to spaces, and with the table prefix (if any) removed. The first letter of each word is capitalized.

Start Rows

Modification Category:Volumetrics
Set to the sum of the Initial Volume properties for the occupant entities. In the absence of Initial Volume figures, set to 0.

End Rows

Modification Category:Volumetrics
Set to the sum of the Maximum Volume properties for the occupant entities. Default is 0.

Data warehouse Type

Modification Category:Never modified after initial creation
Applicable in data warehouse applications only. If the Data warehouse Type property of an entity is set to ‘Fact’ or ‘Dimension’ and the Data warehouse? property of the application system is set to ‘Yes ‘ this property is set on the corresponding table.

Notes

Modification Category:Text
Set to the Notes text of all the source entities of this table.

User/Help Text

Modification Category:Text
Set to the Description text of all the source entities of this table.


Properties of table entity usages generated by Database Design Transformer

Related Topics

Database Design Transformer creates a table entity usage for each entity to table mapping. If an entity to table mapping is changed, the existing table entity usage is deleted and a new one is created to store details about the new mapping.

Table entity usages can be viewed only from Repository Object Navigator, choose the Table node, Usages, and then Mapped to Entities.

Note:	The following sections describe only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all table entity usages properties, refer to the appropriate repository object definition topic  About

Entity

Table

Mapping Type

Discriminator

Discriminator Column

Discriminator Value

Documentation

Comment

Entity

Modification Category:Never modified after initial creation
The entity used in this mapping.

Table

Modification Category:Never modified after initial creation
The table used in this mapping.

Mapping Type

Modification Category:Datatype
If the entity has a super-type and that super-type is mapped to a table, the mapping type is ‘Override’, otherwise it is null.

Discriminator Column

Modification Category:Always modify
Set to the column used to discriminate between instances of this entity and those of its sub-types. If this entity uses the same discriminator column as its super-type, this value is null.

Discriminator Value

Modification Category:Always generated
If the table has more than one occupant entity, this is set to the short name of the entity in this mapping. If the table has no other occupant entities, this is set to Null.

Comment

If not explicitly specified, a comment is constructed for each mapping in the form: "Created from entity <entity.name> by <user> on <date>".


Properties of table implementations generated by Database Design Transformer

Related Topics

Database Design Transformer creates a table implementation for each table to record implementation specific information relating to table definition.

In Design Editor, table implementations can be viewed from the DB Admin tab of the Navigator.  Choose the appropriate Database and User definition, then Schema Objects.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all table implementation properties, refer to the appropriate repository object definition topic.  About

Database User

Table

Storage

Tablespace

Storage Definition

Percent Free

Database User

Modification Category:Never modified after initial creation
The database user definition in which the table implementation is generated.

Table

Modification Category:Never modified after initial creation
The table for which the table implementation is generated.

Tablespace

Modification Category:Never modified after initial creation
Set to the ‘Tablespace’, set on the Database tab in the Database Design Transformer Settings dialog.

Storage Definition

Modification Category:Never modified after initial creation
Set to the ‘Storage Definition’, set on the Database tab in the Database Design Transformer Settings dialog.

Percent Free

Modification Category:Volumetrics
Set to 10.


Properties of sequence implementations generated by Database Design Transformer

Related Topics

Database Design Transformer creates a sequence implementation for each sequence to record implementation specific information for the sequence definition.

In Design Editor, sequence implementations can be viewed from the DB Admin tab of the Navigator.  Choose the appropriate Database and User definition, then Schema Objects.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all sequence implementation properties, refer to the appropriate repository object definition topic.  About

Database User

Sequence

Database User

Modification Category:Never modified after initial creation
The database user definition in which the sequence implementation is generated.

Sequence

Modification Category:Never modified after initial creation
The sequence for which the sequence implementation is generated.


Properties for unique or primary keys generated by Database Design Transformer to implement UIDs
Properties of unique or primary keys generated by Database Design Transformer to implement UIDs

Related Topics

Database Design Transformer creates or updates a unique or primary key definition for each unique identifier of entities that are mapped to a given table. You can specify the mapping details for unique identifiers and primary/unique keys on the Other Mappings tab in the Database Design Transformer dialog.

If a table definition does not have an existing primary key, the Database Design Transformer tries to find a suitable unique identifier that can be used to generate the primary key. If it finds one, it uses it to generate a primary key rather than a unique key. Most of the properties are set as they are for unique keys. The exceptions are Constraint Name, Constraint Type and Update?.

A unique identifier is suitable for use in generating the primary key if the:

•	table has a primary entity

•	unique identifier is on the primary entity (or on a super-type of it)

•	unique identifier does not contain relationships to entities that are implemented by more than one table

The primary entity is the entity that is mapped to a particular table and is the entity super-type of all the other entities that are mapped to that table.

In Design Editor, unique/primary key definitions can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all unique/primary key properties, refer to the appropriate repository object definition topic.  About

Table

Constraint Name

Complete?

Enable?

Mandatory?

Update?

Validation

Validate In

Source

Unique Identifier

Documentation

Description

Notes

Table

Modification Category:Never modified after initial creation
Set to the table for which the key is being defined.

Constraint Type

Modification Category:Never modified after initial creation
Set to Unique or Primary, as appropriate.

Constraint Name

Modification Category:Name
Rule for primary key:	Set to <Table.Alias>_PK

Rule for unique key:	Set to <Table.Alias>_<UID.Name>_UK.

This is prefixed with the value (if any) of the ‘Table prefix’, set on the Other Settings tab in the Database Design Transformer Settings dialog.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the ‘Maximum identifier length’ (set on the Keys tab in the Settings dialog). If no unique length value is specified, the name is truncated to 30 characters.

If the resulting name is not unique among all database objects (not just tables, but also views, indexes and constraints) in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Enable?

Modification Category:Never modified after initial creation
Set to Yes.

Mandatory?

Modification Category:Always modify
(Only applicable to unique key constraint) Set to Yes if:

•	the table has a primary entity and

•	the unique identifier is defined against the primary entity or one of its super-types, and

•	all members of the unique identifier are mandatory

The primary entity is the entity that is mapped to a particular table and is the entity super-type of all the other entities that are mapped to that table.

Update?

Modification Category:Always generated
Set to No for primary keys and Yes for unique keys.

Validate In

Modification Category:Datatype
Set to the ‘Implementation level for constraints’, as specified on the Keys tab in the Database Design Transformer settings dialog.

Unique Identifier

Modification Category:Never modified after initial creation
The unique identifier that the constraint implements.

Description

Modification Category:Text
Set to the Description text from the unique identifier that is implemented by this key.

Notes

Modification Category:Text
Set to the Notes text from the unique identifier that is implemented by this key.


Properties for key components generated by Database Design Transformer to implement UID entries
Properties of key components generated by Database Design Transformer to implement UID entries

Related Topics

When the Database Design Transformer implements the mapping between a unique identifier and a primary or unique key definition, it may also create or update the components of the key. Each component element represents the association between the key and a column that is in that key. The columns to be included in the key are those that implement the attributes and relationships that make up the unique identifier.

In Design Editor, unique/primary key components can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definition node, Primary/Unique Key and then Columns.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all key component properties, refer to the appropriate repository object definition topic.  About

Constraint

Constraint Type

Column

Join Columns

Sequence in Key

Constraint

Modification Category:Never modified after initial creation
The constraint to which this component belongs.

Constraint Type

Modification Category:Always generated
Set to Primary or Unique, depending on the type of key constraint to which it belongs.

Column

Modification Category:Never modified after initial creation
The column used to implement the key.

Sequence in Key

Modification Category:Sequencing
The sequence of the key components depends on the sequence of the corresponding unique identifier entries that form the unique identifier.


Properties for surrogate primary keys generated by Database Design Transformer
Properties of surrogate primary keys generated by Database Design Transformer

Related Topics

If a table definition does not have an existing primary key, the Database Design Transformer tries to find a suitable unique key that can be used as the primary key. If it cannot find one, it creates a surrogate primary key.

Note:	Surrogate primary keys are never modified after their initial creation.

In Design Editor, surrogate primary key definitions can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all primary key properties, refer to the appropriate repository object definition topic.  About

Table

Constraint Name

Complete?

Enable?

Update?

Validation

Validate In

Source

Unique Identifier

Table

Modification Category:Never modified after initial creation
Set to the table to which this key applies.

Constraint Name

Modification Category:Datatype
Database Design Transformer derives the name from <Table.Alias>_PK.

The name is capitalized, any punctuation characters are converted to underscores, and the result is truncated to the length specified by the ‘Maximum identifier length’ set on the Keys tab in the Settings dialog. The maximum length is 30.

If the resulting name is not unique among all database objects (not just tables, but also views, indexes and constraints) in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Enable?

Modification Category:Never modified after initial creation
Set to Yes.

Update?

Modification Category:Always generated
Set to No for primary keys.

Validate In

Modification Category:Datatype
Set to the ‘Implementation level for constraints’, specified on the Keys tab in the Database Design Transformer Settings dialog.

Unique Identifier

Modification Category:Never modified after initial creation
Set to Null.


Properties of surrogate primary key components generated by Database Design Transformer

Related Topics

Surrogate keys generate by the Database Design Transformer consist of a single column; therefore there is only one key component for a given surrogate key.

Note:	Surrogate primary key components are never modified after their initial creation.

In Design Editor, unique/primary key components can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definition node, Primary Key and then Columns.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all primary key component properties, refer to the appropriate repository object definition topic.  About

Constraint

Constraint Type

Column

Join Columns

Sequence in Key

Constraint

Modification Category:Never modified after initial creation
The surrogate primary key to which this key component belongs.

Constraint Type

Modification Category:Never modified after initial creation
Set to Primary.

Column

Modification Category:Never modified after initial creation
The column generated to implement this key.

Sequence in Key

Modification Category:Sequencing
Set to 1.


Properties of surrogate primary key columns generated by the Database Design Transformer

Related Topics

A surrogate key generated by the Database Design Transformer contain only one column. Surrogate primary key columns are never modified after their initial creation.

In Design Editor, surrogate primary key columns can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definition node, then Columns.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all column properties, refer to the appropriate repository object definition topic.  About

Table

Name

Sequence in Table

Complete?

Domain

Scalar

Datatype

Average Length

Maximum Length

Decimal Places

Definition

Optional?

Sequence

Volumes

Initial Volume

Final Volume

Default Display

Display?

Display Type

Display Sequence

Descriptor Sequence

Prompt

Default Ordering

Sort Order

Source

Source Attribute

Help

User/Help Text

Table

Modification Category:Never modified after initial creation
The table to which the surrogate key applies.

Column Name

Modification Category:Never modified after initial creation
Set to <Table.Alias>_ID.

The you have chosen not to prefix column names, i.e. Columns is unchecked on the Other Settings tab in the Database Design Transformer Settings dialog,  the surrogate key column name is set to 'ID'.

The name is capitalized, any punctuation characters are converted to underscores, and the result is truncated to the length specified by the ‘Maximum identifier length’ set on the Keys tab in the Settings dialog. The maximum length is 30.

Sequence in Table

Modification Category:Never modified after initial creation
Set to 1.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Domain

Modification Category:Never modified after initial creation
The domain specified by the ‘Domain for new Primary Keys’ option set on the Keys tab in the Database Design Transformer Settings dialog.

Datatype

Modification Category:Never modified after initial creation
If you have specified the ‘Domain for new Primary Keys’ option on the Keys tab in the Settings dialog, this property is set to the value of the Datatype property of that domain. Otherwise, it is set to Number.

Average Length

Modification Category: Never modified after initial creation
If you have specified the ‘Domain for new Primary Keys’ option on the Keys tab in the Settings dialog, this property is set to the value of the Average Length of that domain. Otherwise, it is set to 10.

Maximum Length

Modification Category:Never modified after initial creation
If you have specified the ‘Domain for new Primary Keys’ option on the Keys tab in the Settings dialog, this property is set to the value of the Maximum Col Length of that domain. Otherwise, it is set to 10.

Decimal Places

Modification Category:Never modified after initial creation
If you have specified the ‘Domain for new Primary Keys’ option on the Keys tab in the Settings dialog, this property is set to the value of the Decimal Places property of that domain. Otherwise, it is set to 0.

Optional?

Modification Category:Never modified after initial creation
Set to No, i.e. the column is mandatory.

Sequence

Modification Category:Never modified after initial creation
The sequence generated for this surrogate key.

Initial Volume

Modification Category:Never modified after initial creation
Set to 100 if the column is mandatory, otherwise set to half the Percent used - Initial property of the attribute.

Final Volume

Modification Category:Never modified after initial creation
Set to 100, because the column is mandatory.

Display?

Modification Category:Never modified after initial creation
Set to No.

Display Type

Modification Category:Never modified after initial creation
Set to Null.

Display Sequence

Modification Category:Display
If Display? is set to Yes, the Display sequence is set to the same value as Sequence in Table. If Display? is No, the display sequence number is set to null.

Descriptor Sequence

Modification Category:Never modified after initial creation
Set to Null.

Prompt

Modification Category: Never modified after initial creation
The Database Design Transformer takes the name of the column and removes the prefix, if any. It converts underscores to spaces, and capitalizes the first letter of each word.

Sort Order

Modification Category:Never modified after initial creation
Set to the Sorting Order of the attribute.

Source Attribute

Modification Category:Never modified after initial creation
The attribute from which the surrogate key is generated.

User/Help Text

Modification Category:Never modified after initial creation
The Database Design Transformer creates help text from the Notes text stored against the attributes.


Properties of surrogate primary key sequences generated by the Database Design Transformer

Related Topics

When the Database Design Transformer creates a surrogate primary key, it also creates a sequence to provide values for the key column. Such sequences are never modified after their initial creation.

In Design Editor, sequences can be viewed from the Server Model tab of the Navigator.  Choose the Sequence Definition node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all sequence properties, refer to the appropriate repository object definition topic.  About

Name

Code Control?

Name

Modification Category:Never modified after initial creation
To set the Name, the Database Design Transformer uses <Table.Alias>_SEQ.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the Maximum identifier length set on the Keys tab in the Settings dialog. The maximum length is 30.

If the resulting name is not unique among all database objects in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Code Control?

Modification Category:Never modified after initial creation
Set to ORACLE sequence.


Properties of index storage for unique and primary keys generated by the Database Design Transformer
Properties of index storages for unique and primary keys generated by the Database Design Transformer

Related Topics

When the Database Design Transformer generates unique/primary keys, it can also generate index storage information for the index used to implement a unique key or primary key, in the context of a specific implementation of a table for a database user.

In Design Editor, index storages can be viewed from the DB Admin tab of the Navigator. Choose the appropriate Database and User definition, then Schema Objects. Choose the Table implementation and then User Object Index Storage.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all index storage properties, refer to the appropriate repository object definition topic.  About

Table

Unique Key

Primary Key

Storage

Storage Definition

Percent Free

Tablespace

Table

Modification Category:Never modified after initial creation
The table for which the index storage is generated.

Unique Key

Modification Category:Never modified after initial creation
The unique key for which the index storage is generated. Set to Null if this index storage is for a primary key constraint.

Primary Key

Modification Category:Never modified after initial creation
The primary key for which the index storage is generated. Set to Null if this index storage is for a unique key constraint.

Storage Definition

Modification Category:Never modified after initial creation
Set to the value of the index ‘Storage Definition’, set on the Database tab in the Database Design Transformer Settings dialog.

Percent Free

Modification Category:Volumetrics
Set to 10.

Tablespace

Modification Category:Never modified after initial creation
Set to the value of the index ‘Tablespace’, set on the Database tab in the Database Design Transformer Settings dialog.


Properties of index storage for indexes generated by the Database Design Transformer
Properties of index storages for indexes generated by the Database Design Transformer

Related Topics

When the Database Design Transformer generates indexes, it can also generate index storage information in the context of a specific implementation of a table for a database user.

In Design Editor, index storages can be viewed from the DB Admin tab of the Navigator. Choose the appropriate Database and User definition, then Schema Objects. Choose the Table implementation and then User Object Index Storage.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all index storage properties, refer to the appropriate repository object definition topic.  About

Table

Index

Storage

Storage Definition

Percent Free

Tablespace

Table

Modification Category:Never modified after initial creation
The table for which the index storage is generated.

Index

The index for which the index storage is being generated.

Storage Definition

Modification Category:Never modified after initial creation
Set to the value of the index ‘Storage Definition’, set on the Database tab in the Database Design Transformer Settings dialog.

Percent Free

Modification Category:Volumetrics
Set to 10.

Tablespace

Modification Category:Never modified after initial creation
Set to the value of the index ‘Storage Definition’, set on the Database tab in the Database Design Transformer Settings dialog.


Properties of foreign keys generated by the Database Design Transformer to implement entity relationships
Properties of foreign keys generated by Database Design Transformer to implement entity relationships

Related Topics

Database Design Transformer generates foreign keys to implement M:1 or 1:1 relationships between entities. For the 1:1 relationship, the Database Design Transformer decides where the foreign key is to be implemented as follows:

1.	If there is already a foreign key implementing the relationship, that key is used.

2.	If one end of the relationship is optional, and the other is mandatory, the foreign key is created in the tables that implement that mandatory end.

3.	If the Average volume property of one of the relationship ends is greater than that of the other, the foreign key is created in the tables that implement the entity at the relationship end with the greater average volume.

4.	The foreign key is created in the tables that  implement the entity (and relationship end) that precedes the other in alphabetical sequence.

For M:M relationships, a foreign key is implemented in the table mapped to the entity at the ‘many’ end.

In Design Editor, foreign key definitions can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all foreign key properties, refer to the appropriate repository object definition topic.  About

Table

Join Table

Constraint Name

Complete?

Enable?

Mandatory?

Transferable?

Validation

Validate In

Cascade Rules

Delete Rule

Update Rule

Arc

Arc Number

Arc Mandatory?

Joining To

Unique Key Joined To

Documentation

Description

Notes

Table

Modification Category:Never modified after initial creation
The table containing this foreign key.

Join Table

Modification Category:Never modified after initial creation
The join table referenced by the foreign key.

Constraint Name

Modification Category:Name
Set to: <table.Alias>_<rtable.Alias>_FK

Or, if this is not unique, set to: <table.Alias>_<rtable.Alias>_<relend.Name>_FK

Where:

•	table.Alias is the alias of the table containing the foreign key columns

•	rtable.Alias is the alias of the table that is referenced by the foreign key

•	relend.Name is the name of the relationship end that the foreign key is implementing

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the ‘Maximum identifier length’ (set on the Keys tab in the Database Design Transformer Settings dialog). If no unique length value is specified, the name is truncated to 30 characters.

If the resulting name is not unique among all database objects in all application systems in which it is visible, it is made unique by adding a numeric suffix.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Enable?

Modification Category:Never modified after initial creation
Set to Yes.

Mandatory?

Modification Category:Always modify
Set to Yes if:

•	the table has a primary entity and

•	the relationship end is mandatory, and

•	the relationship end is not in an arc, and

•	the relationship end is defined against the primary entity or one of its super-types. Otherwise this is set to No.

The primary entity is the entity that is mapped to a particular table and is the entity super-type of all the other entities that are mapped to that table.

Transferable?

Modification Category:Always modify
Set to the same value as the Transfer property of the relationship end that it implements.

Validate In

Modification Category:Never modified after initial creation
Set to the value of ‘Implementation level for constraints’ (set on the Keys tab in the Database Design Transformer Settings dialog).

Delete Rule

Modification Category:Always modify
Generally set to the value of the ‘On delete’ cascade rule, set on the Keys tab in the Database Design Transformer Settings dialog.

The exceptions to this are:

1.	If the constraint is enforced in the server (or both the client and the server), the value must be Restricted or Cascade.

2.	If the constraint is mandatory, the value must be Restricted, Cascade or Default.

If the ‘On delete’ cascade option is not set to a suitable value, this property is set to Restricted.

Update Rule

Modification Category:Always modify
Generally set to the value of the ‘On update’ cascade rule, set on the Keys tab in the Database Design Transformer Settings dialog.

The exceptions to this are:

1.	If the constraint is enforced in the server (or both the client and the server), the value must be Restricted.

2.	If the constraint is mandatory, the value must be Restricted, Cascade or Default.

If the ‘On update’ cascade option is not set to a suitable value, this property is set to Restricted.

Arc Number

Modification Category:Always modify
Two foreign keys in the same table are given the same arc number if:

•	they implement the same relationship, or

•	they implement different relationships in the same arc and both relationships have the same From entity

Note:	If there is only one foreign key in an arc, the Arc Number is null.

Arc Mandatory?

Modification Category:Always modify
Set to  null if Arc Number is Null, otherwise Yes if:

•	all the foreign keys in the arc are generated from the same relationship end, and

•	the table has a primary entity, and

•	the relationship end is mandatory, and

•	the relationship end is not in an arc, and

•	the relationship end is defined against the primary entity or one of its super-types

This property must be the same for all foreign keys on the same table with the same Arc Number.

Note:	The primary entity is the entity that is mapped to a particular table and is the entity super-type of all the other entities that are mapped to that table.

Unique Key Joined To

Modification Category:Always Generated
If the foreign key has components, this is set to the primary or unique key (in the referenced table) that contains exactly those columns referenced by this key.

If the foreign key does not have components, this is set to the primary key of the referenced table, if any.

Notes

Modification Category:Text
Set to the Notes text from the relationship end that is implemented by this key.

Description

Modification Category:Text
Set to the Description text from the relationship end that is implemented by this key.


Properties for columns generated by Database Design Transformer to implement attributes
Properties of columns generated by Database Design Transformer to implement attributes

Related Topics

When an attribute is mapped to a column, the Database Design Transformer creates or maintains the column definition based on the details of the attribute. In the final database, the column will be used to store the attribute value for each instance of the entity to which the attribute belongs.

In Design Editor, column definitions can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all column properties, refer to the appropriate repository object definition topic.  About

Table

Column Name

Sequence in Table

Complete?

Domain

(Allowable Values)

Scalar

Datatype

Average Length

Maximum Length

Decimal Places

Definition

Optional?

Uppercase?

Default Value

Volumes

Final Volume

Initial Volume

Display

Display?

Display Type

Display Length

Display Sequence

Descriptor Sequence

Prompt

Default Ordering

Order by Sequence

Sort Order

Derivation

AutoGen Type

Source

Source Attribute

Documentation

Comment

Description

Notes

Help

User Help Text

Table

Modification Category:Never modified after initial creation
The table to which this column belongs.

Column Name

Modification Category:Name
Set to:  <Attribute.Name>

Or, to:  <Entity.Short Name>_<Attribute.Name>

If the table has a column prefix (i.e. the Col. Prefix property is defined on the table) and you have set the prefix Columns option on the Other Settings tab in the Database Design Transformer Settings dialog, the column name is prefixed with the table column prefix.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the ‘Maximum identifier length’, (set on the Keys tab in the Settings dialog). The maximum length is 30.

Note that an underscore is not added to the column prefix.

Sequence in Table

Modification Category:Sequencing
The Database Design Transformer orders the columns within a table according to the rules you have specified on the Other Settings tab in the Database Design Transformer Settings dialog.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Domain

Modification Category:Datatype
Set to the Domain reference of the attribute. If the attribute is not in a domain, this is set to null.

Allowable Values

Modification Category:Datatype
Set to the allowable values for the attribute.

Note:	The Database Design Transformer does not copy the domain's allowable values; these will still be available because the column is in the same domain as the attribute.

Datatype

Modification Category:Datatype
The datatype is set using the following priorities:

1.	The datatype of the domain for this attribute.

2.	The format of the domain for this attribute.

3.	The format of the attribute. If the format of the attribute is Char the datatype is set to VarChar2.

Average Length

Modification Category:Datatype
The average length is set using the following priorities:

1.	The datatype average length value of the domain for this attribute.

2.	The format average length value of the domain for this attribute.

3.	The average length of the attribute.

Maximum Length

Modification Category:Datatype
Set to:

1.	The datatype maximum length value of the domain for this attribute.

2.	The format maximum length value of the domain for this attribute.

3.	The maximum length of the attribute.

Decimal Places

Modification Category:Datatype
The decimal places value is set using the following priorities:

1.	The datatype decimal places value of the domain for this attribute.

2.	The format decimal places value of the domain for this attribute.

3.	The decimal places of the attribute.

Optional?

Modification Category:Datatype
Set to No if:

•	the table has a primary entity and

•	the attribute is mandatory, and

•	the attribute is defined against the primary entity or one of its super-types.

The primary entity is the entity that is mapped to a particular table and is the entity super-type of all the other entities that are mapped to that table.

Note:	When UIDs are transformed into primary keys, the ‘Optional’ properties of corresponding primary key columns are also set to ‘No’.

Uppercase?

Modification Category:Never modified after initial creation
Set to No.

Default Value

Modification Category:Datatype
Set to the Default Value of the attribute.

Initial Volume

Modification Category:Volumetrics
If the column is mandatory this is set to 100; otherwise it is set to the Percent Used - Initial of the attribute.

Final Volume

Modification Category:Volumetrics
If the column is mandatory this is set to 100; otherwise it is set to the Percent Used - Average of the attribute.

Display?

Modification Category:Display
Set to Yes if the Display Type is not null; otherwise, this is set to No.

Display Type

Modification Category:Display
The display type is set using the following priorities:

1.	The format of the domain for this attribute.

2.	The format of the attribute.

Display Length

Modification Category:Display
For non-numeric columns, this is set to the lesser of Maximum Length or 70.  For numeric columns, set to Maximum Length:

•	plus 1 (for a sign), if the column can hold negative values,

•	plus 1 (for a decimal point), if the column's Decimal Places is greater than 0

•	minus the column's Decimal Places (to accommodate trailing 0s), if Decimal Places is less than 0

•	plus 6 (for a decimal point and a trailing exponent like E-125), if the column's Decimal Places is null.

Display Sequence

Modification Category:Display
If Display? is set to Yes, the Display sequence is set to the same value as Sequence Number. If Display? is No, the Sequence Number is set to null.

Descriptor Sequence

Modification Category:Never modified after initial creation
Set to Null.

Prompt

Modification Category:Display
The Database Design Transformer takes the name of the column and removes the prefix, if any. It converts underscores to spaces, and capitalizes the first letter of each word.

Order by Sequence

Modification Category:Sequencing
Set to the Sequence in Entity property of the attribute.

Sort Order

Modification Category:Display
Set to the Sort Order of the attribute.

AutoGen Type

Modification Category:Never modified after initial creation
Generally set to Null. However, if the format of the attribute is Number, and it is in a unique identifier with one attribute and one or more relationships, it is set to Sequence in Parent.

Source Attribute

Modification Category:Never modified after initial creation
The attribute that this column implements.

Comment

Modification Category:Never modified after initial creation
Set to the Notes of the attribute.

Notes

Modification Category:Text
Set to the Notes text of the attribute.

Description

Modification Category:Text
Set to the Description text of the attribute.

User/Help Text

Modification Category:Display
Set to the Notes of the attribute.


Properties of foreign key columns generated by Database Design Transformer

Related Topics

Foreign key columns are based on the primary key columns for the table that is referenced by the foreign key. When creating or updating foreign key columns, the Database Design Transformer sets the column properties as follows.

In Design Editor, column definitions can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all column properties, refer to the appropriate repository object definition topic.  About

Table

Column Name

Sequence in Table

Complete?

Domain

Scalar

Datatype

Average Length

Maximum Length

Decimal Places

Definition

Optional?

Uppercase?

Default Value

Volumes

Final Volume

Initial Volume

Display

Display?

Display Type

Display Length

Display Height

Display Sequence

Descriptor Sequence

Prompt

Hint

Default Ordering

Sort Order

Documentation

Comment

Description

Notes

Table

Modification Category:Never modified after initial creation
The table for this foreign key.

Column Name

Modification Category:Name
Set to <referenced table's Alias>_<referenced column's name>

If the referenced table does not have an alias, its full name is used.  If the referenced column has a column prefix, this is removed.

If the table has a column prefix (i.e. the Col. Prefix property is defined on the table) and you have set the prefix Columns option on the Other Settings tab in the Database Design Transformer Settings dialog, the column name is prefixed with the table column prefix.

The name is then capitalized, its punctuation converted to underscores, and truncated to 30 (or preference Unique length) characters. If the resulting name is not unique with the table, a numeric suffix is added to make it unique.

Note that an underscore is not added to the column prefix.

Sequence in Table

Modification Category:Sequencing
The Database Design Transformer orders the columns within a table according to the rules you have specified on the Other Settings tab in the Database Design Transformer Settings dialog.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Domain

Modification Category:Datatype
Set to the Domain of the referenced column.

Datatype

Modification Category:Datatype
Set to the Datatype of the referenced column.

Average Length

Modification Category:Datatype
Set to the Average Length of the referenced column.

Maximum Length

Modification Category:Datatype
Set to the Maximum Length of the referenced column.

Decimal Places

Modification Category:Datatype
Set to the Decimal Places of the referenced column.

Optional?

Modification Category:Always modify
Set to No if the foreign key is mandatory, otherwise, this is set to Yes.

Uppercase?

Modification Category:Datatype
Set to the same value as the Uppercase? property of the referenced column.

Default Value

Modification Category:Never modified after initial creation
Set to Null (except attribute column).

Final Volume

Modification Category:Volumetrics
If the column is mandatory (NULL_INDICATOR=NOT NULL), this is set to 100. Otherwise, it is set to 50.

Initial Volume

Modification Category:Volumetrics
If column is mandatory (NULL_INDICATOR=NOT NULL) this is set to 100. Otherwise, this is set to 50.

Display?

Modification Category:Display
Set to the Display property of the referenced column.

Display Type

Modification Category:Display
Set to the Display Type of the referenced column.

Display Length

Modification Category:Display
Set to the Display Length of the referenced column.

Display Height

Modification Category:Display
Set to the Display Height of the referenced column.

Display Sequence

Modification Category:Display
If the Display flag is set to Yes, the Display Sequence is set to the same value as the Sequence number. If the Display flag is No, the Sequence in Table is set to Null.

Descriptor Sequence

Modification Category:Never modified after initial creation
Set to Null.

Prompt

Modification Category:Display
Set to the Prompt of the referenced column.

Hint

Modification Category:Display
Set to the User Help Text of the referenced column.

Sort Order

Modification Category:Display
Set to the Sort Order of the referenced column.

Comment

Modification Category:Never modified after initial creation
Set to the Comment of the referenced column.

Notes

Modification Category:Text
Set to the Notes text of the referenced column.

Description

Modification Category:Text
Set to the Description text of the referenced column.


Properties of foreign key components generated by Database Design Transformer

Related Topics

A foreign key component element represents the association between a foreign key and a column that makes up the key. The Database Design Transformer creates and updates these components when processing the foreign keys.

In Design Editor, foreign key components can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node, then Foreign Keys.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all foreign key component properties, refer to the appropriate repository object definition topic.  About

Constraint

Constraint Type

Column

Join Column

Join Column

Sequence in Key

Text

Comment

Constraint Type

Modification Category:Never modified after initial creation
Set to Foreign.

Constraint

Modification Category:Never modified after initial creation
The foreign key that owns this component.

Column

Modification Category:Never modified after initial creation
A column implementing this key.

Join Column

Modification Category:Never modified after initial creation
The join column in the referenced table.

Sequence in Key

Modification Category:Sequencing
Set to the sequence number of the equivalent primary key component in the referenced primary key.

Comment

Modification Category:Never modified after initial creation
Set to Null.


Properties of discriminator columns generated by Database Design Transformer

Related Topics

Database Design Transformer can create a discriminator column, if several sub-types are mapped to a single super-type table. This is to distinguish between the different types stored in the table.

Note:	A discriminator column is never modified after its initial creation. However, allowable values for the column can be modified.

The Database Design Transformer only ever generates a single discriminator column for a table, but you can add additional discriminators by hand. Note however, that when updating allowable values, the Database Design Transformer will modify only those for the top-level discriminator (typically the one generated by the Database Design Transformer).

In Design Editor, column definitions can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all column properties, refer to the appropriate repository object definition topic.  About

Table

Column Name

Sequence in Table

Complete?

(Allowable Values)

Scalar

Datatype

Average Length

Maximum Length

Definition

Optional?

Uppercase?

Default Value

Volumes

Final Volume

Initial Volume

Display

Display?

Display Type

Display Length

Display Sequence

Descriptor Sequence

Prompt

Table

Modification Category:Never modified after initial creation
The table containing this column.

Column Name

Modification Category:Name
If the table has a column prefix (i.e. the Col. Prefix property is defined on the table) and you have set the Columns prefix option on the Other Settings tab in the Database Design Transformer Settings dialog, the column name is set to <Col. Prefix>TYPE. If there is no column prefix, the Database Design Transformer uses <Primary_Entity.Short Name>_TYPE.

The name is capitalized, any punctuation characters are converted to underscores, and the result is truncated to 30 (or the Maximum identifier length option) characters. If the resulting name is not unique with the table, a numeric suffix is added to make it unique.

Note that an underscore is not added to the column prefix.

Sequence in Table

Modification Category:Never modified after initial creation
The Database Design Transformer orders the columns within a table according to the order options specified on the Other Settings tab in the Settings dialog.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

(Allowable Values)

Modification Category:Never modified after initial creation
The allowable values for this column are based upon the entities between which it discriminates.

Datatype

Modification Category:Never modified after initial creation
Set to VarChar2.

Average Length

Modification Category:Never modified after initial creation
Set to 10.

Maximum Length

Modification Category:Never modified after initial creation
Set to 10.

Optional?

Modification Category:Never modified after initial creation
Set to No.

Uppercase?

Modification Category:Never modified after initial creation
Set to No.

Default Value

Modification Category:Never modified after initial creation
Set to Null (except attribute column).

Final Volume

Modification Category:Never modified after initial creation
Set to 100.

Initial Volume

Modification Category:Never modified after initial creation
Set to 100.

Display?

Modification Category:Never modified after initial creation
Set to Yes.

Display Type

Modification Category:Never modified after initial creation
Set to Text.

Display Length

Modification Category:Never modified after initial creation
Set to 10.

Display Sequence

Modification Category:Display
If the Display flag is set to Yes, the Display Sequence is set to the same value as Sequence in Table. If the Display flag is No, the Sequence in Table is set to Null.

Descriptor Sequence

Modification Category:Never modified after initial creation
Set to Null.

Prompt

Modification Category:Display
The Database Design Transformer takes the name of the column and removes the prefix, if any. It converts underscores to spaces, and capitalizes the first letter of each word.


Properties of discriminator column values generated by Database Design Transformer

Related Topics

When the Database Design Transformer creates or updates discriminator columns it can also process the allowable values for the columns.

In Design Editor, column values can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node, Columns and then Allowable Values.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all properties, refer to the appropriate repository object definition topic.  About

Column

Value

Meaning

Abbreviation

Abbreviation

Modification Category:Never modified after initial creation
Set to the Short Name of the entity.

Column

Modification Category:Never modified after initial creation
The discriminator column for this entity and the others that are mapped to the same table.

Value

Modification Category:Never modified after initial creation
The discriminator value of the entity. If no discriminator value exists for the entity, Value is set to the entity Short Name.

Meaning

Modification Category:Never modified after initial creation
Set to the Name of the entity.


Properties of intersection tables generated by Database Design Transformer

Related Topics

To implement each many-to-many relationship, the Database Design Transformer creates/updates an intersection table and foreign keys from that table to those that implement the entities in the relationship.

In Design Editor, tables can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all table properties, refer to the appropriate repository object definition topic.  About

Name

Alias

Col. Prefix

Display Title

Volumes

Start Rows

End Rows

Text

Notes

Name

Modification Category:Name
Set to: <entity1.Name>_<entity2.Plural Name>

Where entity1 and entity2 refer to the entities in the relationship that this table implements. This name is prefixed with the value (if any) of the Table prefix option, set on the Other Settings tab in the Database Design Transformer Settings dialog.

The name is capitalized, any punctuation characters are converted to underscores, and the result is truncated to the length specified by the Maximum unique length option set on the Keys tab in the Settings dialog. The maximum length is 30.

If the resulting name is not unique among all database objects (not just tables, but also views, indexes and constraints) in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Alias

Modification Category:Name
Set to the Short Name of the two source entities, punctuation converted, and unique within the application system.

The result is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the Maximum identifier length option, set on the Keys tab in the Database Design Transformer Settings dialog. The maximum length is 30.

If the resulting alias is not unique among all database objects in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Col. Prefix

Modification Category:Name
If you have set the Columns prefix option on the Other Settings tab in the Database Design Transformer Settings dialog, this is set to the alias of the table.

Display Title

Modification Category:Name
The name of the table capitalized, with underscores converted to spaces, and with the table prefix (if any) removed.

Start Rows

Modification Category:Volumetrics
For each entity in the relationship implemented by this table, the Database Design Transformer calculates the Initial volume of the entities, multiplied by the Average volume property of the relationship end. If both calculated values are not null, the Database Design Transformer sets the Start Rows to their mean.

End Rows

Modification Category:Volumetrics
For each entity in the relationship implemented by this table, the Database Design Transformer calculates the Maximum volume of the entities, multiplied by the Average volume property of the relationship end. If both calculated values are not null, the Database Design Transformer sets the End Rows to their mean.

Notes

Modification Category:Text
Set to the Notes from both relationship ends.


Properties of foreign keys on intersection tables generated by Database Design Transformer

Related Topics

When the Database Design Transformer implements a many-to-many relationship between entities, it creates an intersection table and foreign keys from that table to those tables that implement the entities.

In Design Editor, foreign keys can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all foreign key properties, refer to the appropriate repository object definition topic.  About

Table

Join Table

Constraint Name

Constraint Type

Complete?

Enable?

Mandatory?

Transferable?

Validation

Validate In

Cascade Rules

Delete Rule

Update Rule

Arc

Arc Mandatory?

Arc Number

Joining To

Unique Key Joined to

Table

Modification Category:Never modified after initial creation
The table containing this foreign key.

Join Table

Modification Category:Never modified after initial creation
The table referenced by the foreign key.

Constraint Name

Modification Category:Name
Set to: <table.Alias>_<rtable.Alias>_FK

Or, if this is not unique, set to: <table.Alias>_<rtable.Alias>_<relend.Name>_FK

Where:

•	table.Alias is the alias of the table containing the foreign key columns

•	rtable.Alias is the alias of the table that is referenced by the foreign key

•	relend.Name is the name of the relationship end that the foreign key is implementing

This is prefixed with the value (if any) of the Table prefix option, set on the Other Settings tab in the Database Design Transformer Settings dialog.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the Maximum unique length option. The maximum length is 30.

If the resulting name is not unique among all database objects in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Constraint Type

Modification Category:Never modified after initial creation
Set to Foreign.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Enable?

Modification Category:Never modified after initial creation
Set to Yes.

Mandatory?

Modification Category:Never modified after initial creation
Set to Yes.

Transferable?

Modification Category:Never modified after initial creation
Set to No.

Validate In

Modification Category:Never modified after initial creation
Set to the ‘Implementation level for constraints’, set on the Keys tab in the Database Design Transformer Settings dialog.

Delete Rule

Modification Category:Never modified after initial creation
Set to Restricted if the Mandatory? property is Yes, otherwise, this is set to Nullifies.

Update Rule

Modification Category:Never modified after initial creation
Set to Restricted.

Arc Number

Modification Category:Always modified
Two foreign keys in the same table are given the same arc number if:

•	they implement the same relationship, or

•	they implement different relationships in the same arc and both relationships have the same From entity

Note:	If there is only one foreign key in an arc, the Arc Number is Null.

Arc Mandatory?

Modification Category:Never modified after initial creation
Set to No.

Unique  Key Joined to

Modification Category:Always Generated
If the foreign key has components, this is set to the primary or unique key (in the referenced table) that contains exactly those columns referenced by this key.

If the foreign key does not have components, this is set to the primary key of the referenced table, if any.


Properties of primary keys on intersection tables generated by Database Design Transformer

Related Topics

For an intersection table, the Database Design Transformer creates a primary key containing all of the columns in all of the foreign keys that link the intersection table to those tables that implement the entities involved in the many-to-many relationship. Because there is no recorded mapping between a many-to-many relationship and an intersection table, the Database Design Transformer determines whether a given table is an intersection table for a given relationship by the following rules:

•	the table has no source entity and

•	it has foreign keys to the tables that implement the ends of the relationship

In Design Editor, primary keys can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all primary key properties, refer to the appropriate repository object definition topic.  About

Table

Constraint Name

Constraint Type

Complete?

Enable?

Update?

Validation

Validate In

Table

Modification Category:Never modified after initial creation
The intersection table to which this key applies.

Constraint Name

Modification Category:Never modified after initial creation
Set to: <table.Alias>_SEQ.

This is prefixed with the value (if any) of the Table prefix option, set on the Other Settings tab in the Database Design Transformer Settings dialog.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by ‘Maximum unique length’, set on the Keys tab of the Settings dialog. The maximum length is 30.

If the resulting name is not unique among all database objects in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Constraint Type

Modification Category:Never modified after initial creation
Set to Primary.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Enable?

Modification Category:Never modified after initial creation
Set to Yes.

Update?

Modification Category:Never modified after initial creation
Set to No.

Validate In

Modification Category:Never modified after initial creation
Set to the value of the Implementation level for constraints option, set on the Keys tab in the Database Design Transformer Settings dialog.


Properties of allowable column values generated by Database Design Transformer

Related Topics

When a column implements an attribute, the Database Design Transformer can also create allowable values for the column based upon those for the attribute.

In Design Editor, allowable values can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node, Columns and then Allowable Values.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all properties, refer to the appropriate repository object definition topic.  About

Column

Value

High Value

Abbreviation

Meaning

Display Sequence

Column

Modification Category:Never modified after initial creation
The column to which this value applies.

Value

Modification Category:Never modified after initial creation
Set to the Low Value of the Allowable value.

High Value

Modification Category:Never modified after initial creation
Set to the High Value of the Allowable value.

Abbreviation

Modification Category:Datatype
Set to the Abbreviation of the allowable values.

Meaning

Modification Category:Never modified after initial creation
Set to the Meaning of the Allowable value.

Display Sequence

Modification Category:Never modified after initial creation
Set to the Display Sequence of the Allowable value.


Properties of foreign keys on arced sub-type tables generated by Database Design Transformer

Related Topics

If Database Design Transformer uses an arc mapping style for a particular sub-type entity, the sub-type table is referenced by a foreign key generated on the super-type table.

In Design Editor, foreign keys can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all foreign key properties, refer to the appropriate repository object definition topic.  About

Table

Join Table

Constraint Name

Constraint Type

Complete?

Enable?

Mandatory?

Transferable?

Validation

Validate In

Cascade Rules

Delete Rule

Update Rule

Arc

Arc Mandatory?

Arc Number

Joining To

Unique Key Joined to

Table

Modification Category:Never modified after initial creation
The table referenced by the key.

Join Table

Modification Category:Never modified after initial creation
The table for the foreign key.

Constraint Name

Modification Category:Name
Set to: <table.Alias>_<rtable.Alias>_FK

Or, if it not unique, set to: <table.Alias>_<rtable.Alias>_<relend.Name>_FK

Where:

•	table.Alias is the alias of the table containing the foreign key columns

•	rtable.Alias is the alias of the table that is referenced by the foreign key

•	relend.Name is the name of the relationship end that the foreign key is implementing

This is prefixed with the value (if any) of the Table prefix option, set on the Other Settings tab in the Database Design Transformer Settings dialog.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the Maximum identifier length option, set on the Keys tab in the Settings dialog. The maximum length is 30.

Finally, if the resulting name is not unique among all database objects in all the application systems in which it is visible, it is made unique by adding a numeric suffix.

Constraint Type

Modification Category:Never modified after initial creation
Set to Foreign.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Enable?

Modification Category:Never modified after initial creation
Set to Yes.

Mandatory?

Modification Category:Never modified after initial creation
Set to Yes.

Transferable?

Modification Category:Never modified after initial creation
Set to No.

Validate In

Modification Category:Datatype
Set to the ‘Implementation level for constraints’, set on the Keys tab in the Database Design Transformer Settings dialog.

Delete Rule

Modification Category:Never modified after initial creation
Set to Cascade.

Update Rule

Modification Category:Never modified after initial creation
Set to Restricted.

Arc Mandatory?

Modification Category:Never modified after initial creation
Set to No.

Arc Number

Modification Category:Never modified after initial creation
Set to Null.

Unique Key Joined to

Modification Category:Always Generated
If the foreign key has components, this is set to the primary or unique key (in the referenced table) that contains exactly those columns referenced by this key.

If the foreign key does not have components, this is set to the primary key of the referenced table, if any.


Properties of primary keys on arced sub-type tables generated by Database Design Transformer

Related Topics

In Design Editor, primary keys can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all primary key properties, refer to the appropriate repository object definition topic.  About

Table

Constraint Name

Constraint Type

Complete?

Enable?

Update?

Validation

Validate In

Table

Modification Category:Never modified after initial creation
The table to which this key applies.

Constraint Name

Modification Category:Never modified after initial creation
Set to: <table.Alias>_SEQ.

This is prefixed with the value (if any) of the Table prefix option, set on the Other Settings tab in the Database Design Transformer Settings dialog.

The name is capitalized, any punctuation characters are converted to underscores, and it is truncated to the length specified by the Maximum identifier length option (set on the Keys tab in the Settings dialog). The maximum length is 30.

If the resulting name is not unique among all database objects in all application systems where it is visible, it is made unique by adding a numeric suffix.

Constraint Type

Modification Category:Never modified after initial creation
Set to Primary.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.

Enable?

Modification Category:Never modified after initial creation
Set to Yes.

Update?

Modification Category:Never modified after initial creation
Set to No if primary, set to Yes if unique.

Validate In

Modification Category:Never modified after initial creation
Set to the value of the Implementation level for constraints option specified on the Keys tab in the Database Design Transformer Settings dialog.


Properties of indexes generated by Database Design Transformer

Related Topics

Database Design Transformer can generate indexes and index entries for foreign keys.

In Design Editor, indexes can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all index properties, refer to the appropriate repository object definition topic.  About

Table

Name

Complete?

Table

Modification Category:Never modified after initial creation
Set to the From table of the foreign key.

Name

Modification Category:Never modified after initial creation
The Database Design Transformer uses the name of the foreign key for which the index is generated. The name is capitalized, any punctuation characters are converted to underscores and the result is truncated to the length specified by the Maximum identifier length option (set on the Keys tab in the Database Design Transformer Settings dialog). The maximum length is 30.

If the resulting name is not unique among all database objects (not just tables, but also views, indexes and constraints) in all application systems where it is visible, it is made unique by adding a numeric suffix.

Complete?

Modification Category:Never modified after initial creation
Set to Yes.


Properties of index entries generated by Database Design Transformer

Related Topics

Database Design Transformer can generate indexes and index entries for foreign keys.

In Design Editor, index entries can be viewed from the Server Model tab of the Navigator.  Choose the Relational Table Definitions node, Indexes, then Columns.

Note:	The following section describes only those element properties that are set or modified by the Database Design Transformer. For a comprehensive list of all properties, refer to the appropriate repository object definition topic.  About

Index Name

Column

Usage Sequence

Index Name

Modification Category:Never modified after initial creation
The name of the index that this index entry belongs to.

Column

Modification Category:Never modified after initial creation
The equivalent foreign key column.

Usage Sequence

Modification Category:Sequencing
Set to the sequence number of the equivalent foreign key component.


Database Design Transformer dialog box

Related Topics

Allows you to choose the entity to table mappings and options used by the Database Design Transformer.

Mode

Table Mappings

Other Mappings

Run Options

The Table Mappings, Other Mappings and Run Options tabs are not available when Default Mode is chosen on the Mode tab.

The dialog has the following buttons.

Run

Runs the Database Design Transformer.

Commit

Saves elements created or modified during generation, to the repository.

Show Run Set

Displays a list of all elements in the run set.

Settings

Displays the Database Design Transformer Settings dialog.

Cancel/Close

Cancels the operation. If there are any unsaved changes, you are asked if you want to save them.

Help

Displays help for the dialog.


 Database Design Transformer dialog box - Mode tab

Related Topics

Allows you to change the mode in which you run the Database Design Transformer.

Run the Transformer in Default Mode

Default mode enables you to quickly run the Transformer with default settings. You can choose whether you want to run the Transformer on selected entities or all super-type entities.

Note:	Sub-types are not included in the run set in default mode as they are mapped using the single table mapping approach.

Customize the Database Design Transformer

Custom mode enables you to customize the Transformer before running it, e.g., to change the mappings of elements, to change the run options and specify some of the settings.

Show this tab at startup

Determines whether this tab is displayed the next time you invoke the Database Design Transformer.

Summary of run set

Details the number of entities in the run set and the number of tables that are already mapped to entities.


Database Design Transformer dialog box - Table Mappings tab

Related Topics

Allows you to specify the mappings between entities and relational tables in the run set. You can implement an entity in a new table or in an existing table. For each entity included in the run set, the Database Design Transformer creates a Table Entity Usage element to record details about the mappings. If you change an existing mapping, the existing Table Entity usage element is deleted and a new one created for the new mapping.

When you include an entity in the run set, all of its components (attributes, keys and indexes) are also included. If an entity is not currently mapped to a table, a new table definition will be created. If an entity is already mapped to a table, the Database Design Transformer updates the existing table. You can use the Table Mappings tab to modify existing mappings, e.g., to create a new table definition for an entity, or to map an entity to a different table.

Select All

Click this button to select all the entities listed on this tab.

Select None

Click this button to deselect all the entities listed on this tab.

In Set

Indicates whether the mapping is included in the run set.

Including an entity in the run set automatically includes all of its components. If the entity in the run set is mapped to an existing table, you can exclude components from the run set using the Other Mappings tab.

Entity

Display only
Entities in the current application system.  Sub-type entities are listed below their super-type with their names preceded by a dash.

Referenced entities in other application systems also appear in this list.

If you invoke the Transformer from the Oracle Designer Front Panel or the Repository Object Navigator, all super-type entities in the current application system are included in the run set. If you invoke the Transformer from the Entity Relationship Diagrammer with some entities selected, those entities are included in the run set. If no entities are selected, all super-type entities in the current application system are included in the default run set.

Map Type

Display only
Indicates whether or not an entity is mapped to a table, and the mapping style used (see below):

No mapping

The entity is not currently mapped to a table. A new table definition will be created if the entity is included in the run set.

A Table Entity Usage element is created to store details about the mapping. If the entity was previously mapped to a table and the mapping is removed, the existing Table Entity usage element is deleted and a new one is created to record the new mapping.

Mapped

The entity is mapped to a table. If the entity is a sub-type entity, this indicates that the sub-type is mapped to a different table than its super-type.

Included

Only shown on sub-type entities. This indicates a single table mapping in which the sub-type is mapped to the same table as its super-type.

Arc

Represents an arc mapping. This option indicates that the sub-type is mapped to a different table than its super-type. The super-type entity is mapped to a super-type that has a foreign key arc to the sub-type.

Arc

Indicates that the Database Design Transformer is to create an arc mapping for this sub-type entity. This can be set only for sub-type entities that are already mapped.

In an arc mapping, the entity is mapped to a separate table from its super-type. However, the attributes it inherits from its super-type are still stored in the super-type table. The sub-type table is linked to the super-type table by a foreign key. Each instance of the sub-type is partly stored in the sub-type table and partly in the super-type table.

Table

The name of the table to which the entity is mapped.

If this field is blank and the entity is included in the run set, a new table definition is created.

If the entity is mapped to an existing table definition and the entity is included in the run set, the existing table is updated.

Note:	Entities can be mapped to tables in the current application system only. Entities cannot be mapped to referenced  tables.


Database Design Transformer dialog box - Other Mappings tab

Related Topics

Allows you to modify mappings between:

•	attributes on entities and columns on relational tables

•	unique identifiers on entities and primary and unique keys on tables

•	relationships and foreign keys

•	foreign keys and indexes

You can create new columns, keys, relationships and indexes for the elements you are mapping, or map them to existing columns, keys, relationships and indexes. You can also remove columns, keys, relationships and indexes from the run set.

Note:	This tab does not apply to entities that are mapped to a new table.

Items to map option buttons

Determines whether the mapping information you are defining is for columns, primary keys, foreign keys or indexes.

Table

Lists table definitions in this application system. You must select a relational table in this list before specifying its mappings.

Unmapped <items>

Lists the items in this table that are not mapped to items in the entity.

This list excludes columns that are used to implement foreign keys and surrogate keys or that act as discriminators.

Entities implemented by table

The names of the entities that are mapped to the currently selected table.

Select All

Click this button to select all the items in the list below.

Select None

Click this button to deselect all the items in the list below.

In Set

Indicates whether the mapping is included in the run set.

<Items in entity>

An item on the entity mapped to the table, e.g., an attribute.

<Items in table>

An item on the table used to implement the item on the entity, e.g., the column used to implement an attribute.

Setting this field blank and including the mapping in the run set creates a new table item for the entity item. Alternatively, you can map an entity item to an existing table item.

Note:	When you include an item in the run set, the In Set check box must be checked for each item and also for the related table (on the Table Mappings tab).


Database Design Transformer dialog box - Run Options tab

Related Topics

Allows you to specify the types of element that will be created and/or modified based on the current run set.

•	To generate a complete relational database design (tables, columns, keys and indexes) during the same run of the Transformer, check all the Create options. This is the default setting and is recommended when running the Transformer for the first time, to ensure that all elements can be generated successfully.

•	To generate the design in stages using separate runs of the Transformer, check the appropriate Create options.  For example, you can generate the table definitions first, then the columns, the keys and indexes. When building the design iteratively, it is important to consider the dependencies in the generation process.

•	To modify the properties of elements created in previous runs of the Transformer, check the appropriate Modify options. For example, if you create new attributes on an entity after generating the database design, you can generate only the columns required to implement those attributes.

When modifying existing elements, you must specify the types of property to be modified in the Modify group and the types of element to be changed.

Create options

Specifies whether you want to create tables, columns, keys or indexes.

Modify options

Specifies whether you want to modify tables, columns, keys or indexes and which properties can be changed for each element type.

Note:	When modifying key columns, the Modify option should be selected for both columns and keys. Surrogate keys are never modified after their initial creation.

Name

Allows the Database Design Transformer to change the name properties of those elements you allow the Transformer to modify.

Text

Allows the Database Design Transformer to change the text properties of those elements you allow the Transformer to modify.

Sequencing

Allows the Database Design Transformer to change the sequence properties of those elements you allow the Transformer to modify.

Note:	If you have chosen to alter the default column sequence using the Column Component Priority options, the ‘Modify Column’ and ‘Modify Column Sequencing’ options must both be set.

Volumetrics

Allows the Database Design Transformer to change the volume properties of those elements you allow the Transformer to modify.

Datatype

Allows the Database Design Transformer to change the datatype properties of those elements you allow the Transformer to modify.

Display

Allows the Database Design Transformer to change display properties of those elements you allow the Transformer to modify.


Database Design Transformer - Settings dialog box

Related Topics

Allows you to specify run preferences for the Database Design Transformer.

Database

Keys

Other Settings


Database Design Transformer Settings dialog box - Database tab

Related Topics

Database definition for new tables/indexes

Specifies the repository-held database and user definitions in which newly generated tables and indexes are included. Database and user definitions for this application system can be created using the Repository Object Navigator or the Design Editor.

Default storage definitions

Allows you to specify repository-held tablespace and storage definition details for generated tables and indexes. Tablespace and storage definitions for this application system can be created using the Repository Object Navigator or the Design Editor.

Commit frequency for changes

Allows you to choose the frequency with which the Database Design Transformer commits the design to the repository during generation:

•	After each phase - changes are committed after each separate phase of the design process (this is the default)

•	At end of run - the design is committed only when all phases of the design process have been successfully completed

•	Don't commit - the design is validated and reported, but not committed.

Note:	To use the ‘At end of run’ or ‘Don't commit’ options, you must have a sufficiently large rollback segment to hold all of the changes made during the design process.


Database Design Transformer Settings dialog box - Keys tab

Related Topics

Cascade rules for new foreign keys

Specifies the ‘On delete’ and ‘On update’ cascade rules for newly generated foreign key definitions. The default is ‘Restricted’.

Implementation level for constraints

Specifies where newly generated constraints are to be validated. Implementation levels available are:

•	Server - the constraint is validated in the server only (default)

•	Client - the constraint is validated in the client only

•	Both - the constraint is validated in the server and on the client

Surrogate keys

Create surrogate keys for all new tables

Creates surrogate keys for all tables generated from the entities in the run set. The default is unchecked.

This option is useful if you want the primary keys of all generated tables to have similar properties, for example the same datatypes. You can specify a domain for generated surrogate keys using the Domain for new surrogate primary keys option.

Note:	If checked, surrogate keys are generated for all entities in the run set, even if those entities have unique identifiers. The Database Design Transformer will implement any unique identifiers as unique keys and create surrogate keys for the tables.

If unchecked, surrogate keys are generated only when no unique identifier exists for an entity in the run set.

Domain for new surrogate primary keys

Specifies the name of the domain used for any newly generated surrogate key columns. Domains can be defined using the Entity Relationship Diagrammer, the Design Editor or the Repository Object Navigator.

Maximum identifier length

The maximum length in which the Database Design Transformer will make all generated unique names and property values unique. For example, where the value is set to 15, the Database Design Transformer ensures that the first 15 characters of any generated name are unique.

The range must be between 10 and 30. The default is 30.


Database Design Transformer Settings dialog box - Other Settings tab

Related Topics

Elements that you want prefixes generated for

Foreign key columns

Generates a prefix for foreign key columns based on the alias of the referenced table, in the format:

<referenced table alias>_<referenced primary key column>

The default is checked. If unchecked, no prefix is generated and only the referenced primary key column name is used.

Note:	If the Columns prefix option is set when you run the Database Design Transformer, foreign key columns are prefixed with the column prefix (see below for details).

Surrogate key columns

Generates a prefix for surrogate key columns based on the alias of the referenced table, in the format:

<table alias>_ID

The default is checked. If unchecked, no prefix is generated and the surrogate key column name is set to 'ID'.

Columns

Specifies whether the Database Design Transformer sets a new column prefix for newly generated table definitions (i.e. the Col. Prefix property is set to the same value as the Table Alias). If checked, newly generated column definitions are given names that are prefixed with the Table Alias.

Note:	If you are regenerating or updating existing table and column definitions ensure that the appropriate ‘Modify‘ options are set on the Run Options dialog.  To set a new Col. Prefix, the Modify Tables and Modify Table Name options must be checked. Likewise, to rename columns using this prefix the Modify Columns and Modify Column Name options must be checked.

Table prefix

The prefix to be used when constructing the name of any new table definition.

Allow instantiable super-types

Creates an allowable value for a super-type entity where the super-type and its sub-types are to be implemented using a single table. (This type of implementation is also referred to as super-type implementation.)

When a single table is used, the Database Design Transformer creates a discriminator column to distinguish between instances of different sub-types. It then generates an allowable value for each sub-type entity only.

Selecting this option generates an allowable value in the discriminator column for the super-type entity, in addition to those generated for the sub-types.

Column component priority

Specifies how different categories of columns are ordered within a table. For example, you may wish to sequence primary key columns first, then mandatory columns and then optional columns.

Note:	To enable this feature, ensure that the ‘Modify Column’ and ‘Modify Column Sequencing’ options are both set on the Database Design Transformer - Run Options tab.  How

Use the up and down arrows to change the default priority.

	Ascending
• Group columns by their  source entity	The column sequence matches the attribute sequence of the source entity. If more than one entity is mapped to the same table, columns are still sequenced according to their source entity and the source entities nearest the root are sequenced first.
• Primary key columns	Primary-key columns are sequenced first.
• Discriminator columns	Discriminator columns are sequenced first.
• Mandatory columns	Mandatory columns are sequenced first.
• Foreign key columns  before attribute columns	Foreign key columns are sequenced first.
Set the Ascending checkbox to specify that the items appear at the beginning of the sequence. If the checkbox is not set (i.e. Descending), items are placed last in the sequence.


Database Design Transformer - Elements in the Run Set dialog box

Related Topics

A list of all repository elements in the current run set.  The run set includes all the entities you have selected, together with their attributes, unique identifiers, relationships, etc.

If any of the selected entities are mapped to tables, the mapped tables and any associated table elements are also include in the runset.

For more information, see Choosing the Database Design Transformer run set.