To create a physical database schema from your model, you can either create a DDL (data definition language) script, or connect directly to a database server via an ODBC driver. The fundamental issues with both methods are the same, although there are additional considerations when using an ODBC connection. This chapter will first explain how to create a DDL script using basic driver settings, and then describe driver setup options, and finally explain how to use ODBC connections.
DDL for different database platforms generally looks quite similar. All platforms provide syntax for creating tables and constraints, and some allow for stored and procedures and triggers. However, the differences in DDL syntax by platform are pronounced enough that DDL written for one database product will generally not run unmodified on another platform. Thus, VEA must be able to translate models into platform specific DDL.
VEA database drivers translate data models into platform specific DDL the same way that printer drivers translate between word processors and various printer control languages.
Physical schemas, whether created via ODBC connections or DDL scripts, can only be generated from a database model diagram. It doesn't matter whether you create the diagram directly, or build a project from source models. For the following example you need a simple database model diagram that looks like Figure 11-1. To save space, the graphic shows only the database model diagram and not a full screen shot.
Figure 11-1: Database model diagram for testing physical schema generation.
If you build the database model diagram directly or use an ER source diagram, refer to Table 11-1 for a list of the entities, attributes and keys that you should include in your model. In this table, the primary key of each entity is double underlined, unique attributes are single underlined , and mandatory non-key attributes are shown in boldface. Also, make sure to add a relationship from the Patient entity (child) to the Country entity (parent), and make the Country_code attribute optional in the Patient entity.
Country code, CountryName
Patient Nr, FamilyName,
Country_code (automatically migrated as a result of the foreign key from Patient (child) to Country (parent))
An ORM source model with the correct facts and constraints will automatically generate a database model diagram like the one in Figure 11-1. If you choose to create an ORM model, the first part of section 16.1 in this book lists the facts and constraints that you would need to include in the ORM source model. Regardless of how you created the database model diagram, save it to a file called Patient_ch11.vsd.
To generate a DDL script:
Figure 11-2: Selecting the default driver.
In the VEA beta used to for this book, the drivers for INFORMIX Online/SE Sever and Sybase Adaptive Server appear in the Database Drivers window, but are non functional. These drivers will neither generate nor reverse engineer a physical schema. Check with Microsoft to see if these drivers have been changed in the production release.
Figure 11-3: Generate Wizard page one.
By default, your DDL script will be generated to a file with the following path and file name: .DDL. If you wish to change the path or name of the DDL file, you can use the File name text box and the Browse button shown in Figure 11-3. If you were to select the Generate new database box, VEA would try to establish an ODBC connection with a database server. For this example, accept the default choices and click on the Next button.
Figure 11-4: Generate Wizard, page two.
The list box will default to the driver you chose in step one. The Setup button will be explained later in the chapter. At the bottom of the page, supply a name for the database that you will generate. Do not select the Extract Server Information box. Click the Next button.
Because you can choose a Visio database driver from page two of the Generate Wizard (Figure 11-4), you are not technically not required to set the correct default driver (step one of this example) prior to generating DDL. However, setting the default driver ahead of time makes it much easier to assign the proper physical data types to objects in your model. The default database driver setting is persistent, so you only have to set it once.
Figure 11-5: Generate Wizard page three.
Figure 11-6: Generate Wizard page four.
Click the Finish button to start the actual DDL generation.
The physical validation page of the Generate Wizard is the most likely place for errors to occur during DDL generation. Platform specific rules, such as object name length, reserved words, and characters , etc, are not enforced during the modeling phase prior to DDL generation. It is thus possible for a model with no conceptual or logical errors to fail physical validation. Figure 11-7 shows a Generate Wizard page for a model that has failed physical validation.
The errors that occur during physical validation are not listed on the Generate Wizard page. Instead the errors are listed in the Output window (Figure 11-8). Warnings, which are recommendations but not fatal errors, are listed in a text box on the Generation Wizard page.
Figure 11-8: Output window with physical validation errors.
In most cases when physical validation fails there are no warnings, only errors. Thus, it is common to see the message "There are no warnings" in the text box when validation fails. The absence of warnings does not mean the absence of errors. When physical validation fails, always check the Output window.
Figure 11-7: Generate Wizard, failed physical validation.
Figure 11-9: MS SQL Server Create Database window.
The analogous screen for Oracle (Figure 11-10) is more complex because of the additional physical parameters that apply to Oracle databases. You will not see the Oracle window if you are following along with this example. Figure 11-10 is included here for information only.
Figure 11-10: Oracle Server physical parameters.
If you fill out the screen, the result of your input will be generated into your DDL file using the proper syntax. If you leave the screen blank, your DDL will not contain these parameters. Leave the screen blank and click the Close button
Figure 11-11: View DDL prompt.
Click the Yes button to invoke the VEA code editor and automatically load the DDL file, as shown in Figure 11-12.
Figure 11-12: Generated DDL script.
The preceding example showed how to create a DDL script, but ignored database driver options. The options fall into two categories, driver specific options, and options that apply to all drivers.
Discussion of the Setup button on the Database Drivers window was deferred in step one of the DDL script generation example (Figure 11-2). Re-Invoke the Database Drivers window by choosing Database > Options > Drivers from the main menu. Your screen should once again look like Figure 11-2. Click the Setup button to invoke the VEA driver setup dialog box. The first tab is labeled ODBC Drivers , and the second is labeled Preferred Settings . The ODBC Drivers pane is discussed in section 11.5. For now, click the Preferred Settings tab to make your screen look like Figure 11-13.
Figure 11-13: VEA driver setup, Preferred Settings pane.
The choices available on the Preferred Settings pane are specific to the driver being configured, in this case MS SQL Server. The top portion of the pane is devoted to resolving data type mapping ambiguity. Portable data types are mapped to a single physical data type for each supported database product. MS SQL Server has three physical data types that deal with integers: tinyint, smallint, and int. Each portable data type must map to just one physical type, which can be specified on this pane.
In the list box on the bottom of the Preferred Settings pane, choose the version of MS SQL Server for generating DDL or making an ODBC connection. In versions subsequent to 6.0, primary and foreign keys are generated with SQL statements, which is why the radio buttons at the bottom of the pane are disabled. In version 6.0, primary and foreign keys can optionally be defined by stored procedures instead of SQL statements.
If you have set MS SQL server as your default driver, you will not see the Oracle Server Setup dialog box (Figure 11-14), but it is shown here for informational purposes.
Figure 11-14: Oracle Setup, Preferred Settings pane.
The radio buttons along the top of the pane specify which version of Oracle to use when generating DDL or making an ODBC connection. The text box in the middle of the pane is for specifying the default array size for the Oracle VARRAY collection type, an object relational data type.
The "Create uppercase " checkbox forces all column names and table names to full upper case. Many Oracle developers prefer uppercase object names because of case sensitivity issues that arise otherwise .
The final checkbox causes VEA to create an Oracle sequence for any single column primary key that is an Auto Counter portable data type. In addition to the sequence object, VEA will write an INSERT trigger for the column to automatically populate the primary key with the next value in the sequence.
Oracle does not use an "identity" property nor does it use a special physical data type to create an auto incrementing key. Instead, Oracle relies on a data type to create an auto incrementing key. Instead, Oracle relies on a separate database object called a "sequence" to provide auto incremented numbers . A table INSERT trigger calls the next value in the sequence and inserts it into the appropriate column. If you select the "Create sequence " checkbox in Figure 11-14, VEA will not only create the sequence, but write the required trigger as well.
Use this feature carefully . The sequence that is created will always be called
_SEQ, and the trigger will be called _TRIG. If your table name is 30 characters long (the Oracle maximum), the last four letters of the table name will be replaced with the characters "_SEQ." Thus, if your database has more than one table where the first 26 characters of the table name are the same and you declare the keys of both tables to be the portable data type Auto Counter, you will generate a name collision with the two sequence objects. A similar problem can arise with the trigger.
Once you have filled out the Preferred Settings for your driver, click the OK button to be returned to the Database Drivers window (see Figure 11-2).
The options discussed in this section apply to all database drivers. Click on the Default Mapping tab to open the next configuration pane, as shown in Figure 11-15.
Figure 11-15: Setting Default Mapping for portable data types.
In this pane you can make minor customizations to your database driver.
VEA will work perfectly for you even if you never touch the Default Mapping pane. Some modelers find this pane useful, but many are only vaguely aware of its existence. Most people find the out-of-the box settings for Default Mapping to be fine.
The Category list box in Figure 11-15 contains the major kinds of portable data types which are Text, Numeric, Raw Data, Temporal, Logical , and Other . The Type text box contains further subdivisions of the selected major category. As Figure 11-15 shows, the portable data type of the major category Text can be Fixed Length , Variable Length , or Large Length .
The Size text box allows you to specify the default character set for portable data types that belong to the Text category. For portable that data types that belong to the Numeric category, use the Size box to indicate magnitude (e.g., "small" or "large"). In the Length text box, you set the default data length that should be used for the chosen Category / Type combination.
In Figure 11-15, the default length for a Text, Fixed Length portable data type will be 10 characters. The Scale text box is disabled, because scale only applies to certain numeric portable data types. These adjustments only affect the default length of a portable data type.
The final list box in Figure 11-15 is labeled Default category type for column creation . This list box tells VEA what portable data type to assign to ORM objects and logical columns for which data types have not been specifically declared.
Click on the Driver Misc tab to invoke the next configuration pane, shown in Figure 11-16. This pane is confusing, because it appears that all the options it are applicable to all drivers, when in fact one of the options is driver specific.
Figure 11-16: Setting Miscellaneous preferences.
The first checkbox on the Driver Misc pane controls whether VEA will put comments about each database object into the generated DDL script file. These comments can be derived from user entered notes only, or they can include notes that VEA automatically generates during database project builds. The comments inserted into the script will not execute, because they will be marked as comments /* like this * /. If you want "bare bones" DDL, deselect this box.
Choose among the three radio buttons in the middle of the pane to control VEA's generation of SQL COMMENT ON statements, which actually store comments in the server data dictionary. Generating SQL statements to store comments in the database server dictionary is not the same as simply generating comments into the DDL script. Not all database engines support the COMMENT ON feature. In the generated script, VEA will truncate any comments that exceed the server's maximum comment length.
The SQL COMMENT ON feature is actually driver specific, and probably belongs in a different window. However, if you enable SQL COMMENT ON statements, and then generate DDL for a database that does not support this feature, no harm will be done. VEA will recognize that the target database does not support COMMENT ON statements and suppress their generation, regardless of the settings you have chosen.
As an alternative to generating a script, VEA can establish a connection with your database server and generate the physical schema directly. VEA makes use of ODBC drivers to establish the connection. The ODBC drivers are different from the VEA database drivers that are discussed in the beginning of this chapter. VEA database drivers are an integral part of the VEA product. ODBC drivers are supplied by various vendors (including Microsoft) and are not actually part of the VEA product, although some of the more common ODBC drivers are distributed with VEA. Figure 11-17 shows how VEA connects to a database server.
Figure 11-17: VEA connects to the database.
The VEA database driver translates the Database Model Diagram into platform specific DDL. The VEA database driver attaches to an ODBC DSN (data source name ) that is associated with a particular ODBC driver and points to a particular database instance. Through the DSN, the VEA database driver issues DDL to the ODBC driver, which in turn passes that DDL to the database for execution.
You must define a DSN that uses the appropriate ODBC driver and points to the proper physical database instance. DSNs are defined through the ODBC Data Source Administrator , which is an administrative tool of the Windows operating system.
The VEA Generate Wizard gives you two choices when generating a physical schema to a physical database. You can have VEA create a new database, or you can have VEA connect to an existing database.
Figure 11-18: Database generation, page one.
Figure 11-19: Database generation, page two.
Figure 11-20: Create New Data Source, page one.
Figure 11-21: Create New Data Source, page two.
Figure 11-22: Driver specific ODBC Wizard, page one.
Figure 11-23: Driver Specific ODBC Wizard, page two.
Figure 11-24: Driver Specific ODBC Wizard, page three.
Figure 11-25: Driver Specific ODBC Wizard, page four.
The " Connect to SQL Server " checkbox is selected by default. If the check-box is selected and the Server that you specified on the first page of the wizard (Figure 11-22) is unreachable, you will receive an ODBC connection failure message when you click on the Next button. If you wish to configure a DSN for a temporarily unavailable server, deselect the checkbox.
Figure 11-26: DSN test screen.
Figure 11-27: Database generation, page two after creating a new DSN.
If you already have an existing DSN that points to the correct instance of SQL Server, you do not have to perform steps 4 “12 of this example. Instead, type the DSN name into the Data Source Name box (Figure 11-27), and type the desired name of your new database into the Database name. Once you generate the schema, the database that you typed into the text box will become the default database for the DSN that you used.
Figure 11-28: Connection prompt.
The next steps are almost exactly like the final steps in generating a DDL script, and will refer to those screen images. The difference is that model objects (tables, keys, etc) will be generated directly into the database server.
Figure 11-29: Output Pane after database generation.
It is always wise to generate a DDL script when using and ODBC connection to generate a schema directly to a database server. Select the checkbox on the first page of the generate wizard (see Figure 11-18).
If the database generation fails, you will see a server error message in the Output window. After the first error, no other commands will be executed on the server. Right click in the Output window and select Copy All Messages from the context menu. Paste the messages into a text editor. Compare the error messages to the generated DDL script to determine the cause of the database generation failure. If you don't have the DDL script, determining and correcting the cause of the failure can be very difficult.
Instead of creating a new database from within VEA, can generate your schema into an existing database. To generate into an existing databse:
The database you use does not have to be empty, but make sure that none of the existing database objects (i.e., tables, constraints, views, triggers, etc) share a name with the objects in the model you intend to generate. For instance, if you are going to generate the Patient_Ch_11.vsd model into an existing database, that database must not contain a table named Patient .
Figure 11-30: Generating to an Existing Database, page two.
Figure 11-31: Generating into an existing database, page three.
Highlight the correct DSN and click the Next button.
As shown in Figure 11-17, models ard translated by VEA database drivers, which connect to databases via a DSN. Each DSN is associated with an ODBC driver. For database generation to function properly, the ODBC driver of a DSN must be designed for the same target database engine as the selected VEA driver.
Ideally, the list of available DSNs in Figure 11-31 should be limited based upon the ODBC driver associated with each DSN. For instance, if you are using the VEA MS SQL Server database driver, then the list in Figure 11-31 should only include DSNs that use the SQL server ODBC driver. Similarly, if they are using the VEA Oracle Server driver, the list of DSNs should only include those DSNs that use the Oracle ODBC drive .
By default all DSNs are included in the list of available data sources, regardless of the associated ODBC driver. To change this default behavior:
Figure 11-32: VEA Driver setup ODBC Drivers pane.
The VEA MS SQL Server driver is now associated with the SQL Server ODBC driver. From now on, the list of available DSNs will be limited to only those DSNs using the SQL Server ODBC driver. You can repeat this procedure for each VEA driver that you use, though it is not necessary for the proper functioning of the tool.
Overview of Database Modeling and the Database Modeling Tool
The Conceptual Modeling Solution (ORM)
The Logical Modeling Solution (ER and Relational)
Managing Database Projects