This tutorial shows you how to work with the predefined design rules and add your own design rules as well as transformation scripts
Approximately 25 minutes
Oracle SQL Developer Data Modeler supports logical and physical data modeling for Oracle, and Microsoft SQL Server and IBM DB2. This addition to the Oracle SQL Developer family of tools provides forward and reverse engineering of database structures for all who work with graphical data models. There are various diagramming options, you can create Domains and use a set of Design Rules to help ensure your models comply with a set of standards. Oracle SQL Developer Data Modeler also provides a read-only reporting repository for reviewing the design. Save all designs to the repository and then use Oracle SQL Developer to run the reports provided.
In this tutorial you apply a predefined design rule, review and use a custom design rule, create a rule set and use a transformation script to apply changes throughout your model.
Before starting this tutorial, you should:
1 . |
Install Oracle Database 11g with Sample Schema. |
---|---|
2 . |
Unzip the Oracle SQL Developer Data Modeler software to your system. |
3 . |
Download and unzip the files.zip file into a working directory to use during this tutorial. |
In this section, you run a design rule that is included with SQL Developer Data Modeler. Perform the following steps:
1 . |
Open Oracle SQL Developer Data Modeler from the icon on your desktop. If you performed the previous tutorial, and did not exit Oracle SQL Developer Data Modeler, select File > Close to close the model you previously worked on. Then click Close without Save.
|
---|---|
2 . |
You want to open an existing model. Select File.
|
3 . |
Select Open.
|
4 . |
Select the OE_Sample.dmd file in your files directory.
|
5 . |
Click OK.
|
6 . |
The Relational Model is displayed.
|
7 . |
You want to run one of the Design Rules that is supplied with the product. Select Tools > Design Rules > Design Rules.
|
8 . |
Expand the Logical > Entity rule.
|
9 . |
Notice all the predefined design rules for Entities.
|
10 . |
In this case, you want to run all the rules for the Logical Model. Select Logical and click Apply Selected.
|
11 . |
There are some warnings and errors. In this case, some of the errors indicate that there is an attribute without a datatype. Double-click the Error: MARTIAL_STATUS: Attributes without a datatype.
|
12 . |
To defined the datatype, click the unknown button.
|
13 . |
For Logical Type, click the unknown button.
|
14 . |
For Logical Type, select VARCHAR and enter 12 for Size, then click OK.
|
15 . |
Click OK again.
|
16 . |
Click OK once more.
|
17 . |
Click Apply Selected to run the design rules again
|
18 . |
Notice that the MARITAL_STATUS attribute no longer has an error. In the next section, you will review a custom rule. Click Close.
|
In this section, you review a custom design rule and apply it to show how it can be used.. Perform the following steps:
1 . |
Select Tools > Design Rules > Custom Rules.
|
---|---|
2 . |
There is one custom rule already defined for you. You review what it does.
|
3 . |
A design rule can be based on different objects. In this case, the design rule will be based on a table.
|
4 . |
A custom design rule can use a variety of process engines. SQL Developer Data Modeler provides a few example custom design rules that use the default Mozilla Rhino engine, which runs Javascript. You can use other engines, for example, you can the JRuby engine and specify a jruby script that executes when the design rule is applied.
|
5 . |
A design rule can generate a Warning or Error. Note that if you select Warning, the warning icon
|
6 . |
You can specify which library the script is coming from. In this case, my first library is selected. Note that if you do not specify a library, you need to invoke the method and pass the value into the method.
|
7 . |
Below is the javascript code that will execute when the rule is applied. In this case, it is checking if there is a Comment in the RDBMS specified, if not, then a Problem type error message will be generated. If there is not Comment for the table specified, then an Error type error message will be generated.
|
8 . |
Click Apply to see the errors that are generated.
|
9 . |
Notice that there are a number of error type messages displayed. Double-click the error for the OE.ORDER_ITEMS table.
|
10 . |
Select the Comments attribute.
|
11 . |
Enter a comment in the Comments field and click OK.
|
12 . |
From the Custom Design Rules page, click Apply again.
|
13 . |
Notice that the error has turned to a Problem because there are comments defined but there are no comments in the RDBMS defined. Double-click the Problem for OE.ORDER_ITEMS to add an RDBMS comment.
|
14 . |
Select the Comments in RDBMS attribute.
|
15 . |
Enter a comment in the Comments in RDBMS field and click OK.
|
16 . |
Click Apply one more time.
|
17 . |
Notice that the Problem is no longer in the list. In the next section, you examine how to create a Rule Set. Click Close.
|
In this section, you learn how to create a rule set which applies just the design rules you want to run together. Perform the following steps:
1 . |
Select Tools > Design Rules > Design Rules.
|
---|---|
2 . |
Expand Relational > Table. Notice that the custom design rule you just reviewed is in the list.
|
3 . |
You want to create a rule set that will only apply some of the rules. Click the Rule Sets tab.
|
4 . |
Click the '+' to create a new Rule Set.
|
5 . |
Select the Rule Set and select the Properties icon.
|
6 . |
A list of the rules appears. Scroll down to the Column object rules.
|
7 . |
Select the rule Identify columns without datatypes and click the Add Rule (right arrow) button to move it over to the list of selected rules.
|
8 . |
Scroll down to the Table object rules.
|
9 . |
Select the following rules and click the Add Rule (right arrow) button to select the rules.
|
10 . |
Click OK.
|
11 . |
Click Save to save your rule set.
|
12 . |
Select your Rule Set and click Apply Selected.
|
13 . |
All the design rules in the rule set are evaluated and you see the errors that were found. In the next section, you will examine transformation scripts and add a table template that contains some columns that you will apply to all the existing tables in your relational design. Click Close.
|
In this section, you examine the predefined transformation scripts and add a table template table that contains some columns that you apply to all the existing tables in your relational design. Perform the following steps:
1 . |
You want to examine the transformation scripts that are currently available. Select Tools > Design Rules > Transformations.
|
---|---|
2 . |
The transformation scripts are listed. Because the JRuby engine is not installed, you may receive a warning message. Click OK.
|
3 . |
If you click one of the scripts, you see the javascript that is executed when the transformation is applied. In this case, an abbreviation can be added to a column in a table.
|
4 . |
In the Table template example, a table can be created that is a template. The template can be applied to all the tables in your model. Notice that the table must be called table_template. You will apply this transformation script. Click Close.
|
5 . |
The first task you need to perform is to create a table called table_template. Select the New table icon in the toolbar.
|
6 . |
Click in the white space of the relational design. Enter table_template for Name and click the Columns attribute.
|
7 . |
Click the '+' button to create a new column.
|
8 . |
Select the '+' again to create another column.
|
9 . |
Click OK to create the table and the columns.
|
10 . |
Notice the table_template table with the 2 columns. You want to apply these new columns to your existing tables.
|
11 . |
In the navigator, right-click the Relational Model and select Apply Custom Transformation Scripts.
|
12 . |
Select the Table template script and click the Add (right arrow) button.
|
13 . |
Click Apply.
|
14 . |
The transformation script has been applied. Click OK.
|
15 . |
Click Close.
|
16 . |
Notice that each of your tables now contain the two columns from the table_template table. Select the Pointer in the Toolbar.
|
17 . |
Notice that the columns do not have a data type specified. You can change the table_template table definition and then apply those changes using the same script. Double-click the table_template table.
|
18 . |
Select the Columns attribute.
|
19 . |
For Column_1, change the name to UPDATED_DATE, select the Logical datatype, and select Date for Type. Then select Column_2 from the list.
|
20 . |
For Column_2, change the name to CREATED_BY, select Logical for Datatype, select VARCHAR for Type and enter 25 for Size. Then click OK.
|
21 . |
Notice that the columns now have a datatype specified in the table_template table.
|
22 . |
To apply the transformation script again, right-click the Relational Model and select Apply Custom Transformation Scripts.
|
23 . |
Select the Table template script and click the Add (right arrow) button.
|
24 . |
Click Apply.
|
25 . |
Click OK.
|
26 . |
Click Close.
|
27 . |
Notice that the changes you made to the table_template are now applied to the other tables.
|
In this tutorial, you have learned how to:
![]() |
Copyright © 2011, Oracle and/or its affiliates. All rights reserved |