Object Types, Predicates, and Basic Constraints

Object Types

As discussed in the previous chapter, an ORM object type is either a value type or an entity type. A value corresponds to an entry in a table, and is typically a character string or numeric constant (e.g., "Ireland," 56). A value identifies or refers to itself, so there is no need to specify a reference scheme (identification scheme) for it.

An entity is identified by means of a reference scheme that uses one or more values. A simple reference scheme uses only one value (e.g., the Country with CountryCode ˜US ). A good rule of thumb is "If you can write it down, it's a value." Clearly, you can write down the code ˜US, but you can't write down the actual country that is referred to by the code ˜US. Thus, the country code ˜US is a value, but the actual country (the United States of America) is an entity.

Entities and values in an ORM model correspond to real world objects in the application domain, not tables and columns in a logical database model. Trying to make this association will only lead to confusion.

A simple reference scheme for an entity type may be declared explicitly using a binary association (e.g., Country has CountryCode . This reference scheme may be abbreviated by using a reference mode displayed in parenthesis after the entity type name (e.g., Country(Code) .

A composite reference scheme uses two or more binary associations, e.g., the State that has StateCode ˜WA and is in the Country that has CountryCode ˜US. Composite reference schemes are declared using an external uniqueness constraint (e.g., each StateCode, CountryCode combination refers to at most one State).

Table 4-1 summarizes the ways in which different kinds of object type may be referenced. This section discusses how to declare value types and how to use reference modes to provide simple reference schemes for entity types. It also shows how to delete object types. The next section explains how to declare sentence types, including binary associations used to reference entity types.

Table 4-1: Object types and reference schemes.

Kind of Object

Reference Scheme

Reference Scheme Declaration

Entity

Simple

Reference mode

Binary association

Composite

External uniqueness constraint

Value

An object type may also be declared a subtype of another. For example, Woman might be declared a subtype of Person. Composite reference schemes and subtyping are discussed in Chapter 5.

The Visio tool also allows an object type to be declared external . This means that the object type has been previously defined (either as an entity type along with its associated reference scheme, or as a value type) in another model and is simply being reused, so there is no need to redefine it in the current model. External object types are discussed in Chapter 6.

The Visio tool allows you to add new object types and edit them using the Business Rules window, the Fact Editor, or the ORM Source stencil. Existing object types may also be edited using the Database Properties Window. Figure 4-1 shows four object types displayed in the Drawing window, with details of their properties visible in the Business Rules and Database Properties windows . Let's examine some alternative ways to create these examples. If you have not already done so, open the ORM Source Model template ( File > New > Database > ORM Source Model ).

click to expand
Figure 4-1: Object types may be displayed graphically, with their properties listed.

Adding Object Types with the Business Rules Window

  1. If the Business Rules window is closed, open it ( Database > View > Business Rules ).
  2. Select the Object Types tab of the Business Rules window, to view the Object Types pane .

    The Object Types pane displays a table with four columns, as shown in Figure 4-1. Each row of this table lists the main properties of an object type in the model. You can insert and update entries in this table directly. The Object Types column holds the name of the object type.

    The Physical Data Type column displays its data type in the currently selected DBMS. By default, the physical data type is set to char(10) (i.e. a fixed length string of ten characters ). This column may also be used to display the portable data type, in this case SBCS(2), where "SBCS" means "single byte character set."

    Portable data types use generic names for corresponding physical data types in different back-ends. To toggle the display between physical and portable data types, right-click the table header, and select Show Portable Data Type or Show Physical Data Type from the context menu.

    The Kind column classifies the object type as an Entity type, Value type, or External type. By default, Kind is set to Entity. The RefMode column holds the reference mode. An entry is allowed here only if the object type is an Entity type.

  3. Position the cursor in the Object Types column, and type the name "Country."

    A solid circle with dark blue fill appears to the left of the object type name. This icon denotes an entity type. You can also double-click or press the F2 key before typing the name, but this is not needed. If ever you add a new row that you don't want, delete it by selecting it and pressing the Delete key.

  4. Position the cursor in the Physical Data Types column, double-click the entry to enable in-place editing, and replace "10" by "2," so that the entry reads "char(2)."

    Selecting this column also causes a down-arrow to appear on the right. This can be used to display a drop-down list of data types for selection. However, inplace editing is usually faster. Data types are discussed in detail later in the chapter.

  5. Place the cursor in the RefMode column and type "Code."
  6. To display the object type on a diagram, select the dark blue entity type icon at the left of the row and drag it onto the drawing window.
  7. Double-click the object type on the drawing window to display its Database Properties sheet. In the Categories section, select the Ref mode category.

    The settings for Country should now appear as in Figure 4-1. The Database Properties window allows you to specify all details about object types. The Type of a reference mode may be selected from a drop-down list, the default for Entity types 15 Identification. The choice for this type influences how the reference scheme is verbalized. The four radio buttons under Options allow you to control how table and column names are formed when building the logical model. For this example, just accept the default settings.

  8. Select the Notes category, and enter any relevant details in the Notes field to explain how the term "Country" and its reference scheme are to be understood in the model. Figure 4-2 shows one possibility. The Database Properties window allows you to set many other properties for object types, but we'll ignore these until later.

    click to expand
    Figure 4-2: Notes may be used to provide further documentation.

Now let's look at reference mode type in more detail. Use the Business Rules window to add the three other object types shown in Figure 4-1, and assign them the property values shown. When entering the CountryName object type, change its Kind to Value by selecting that option from the drop-down list. A dashed circle with light blue fill appears to the left of the object type name. This icon denotes a value type. After adding CountryName, Date(ymd), and Mass(kg), display them on the drawing surface by dragging their object type icons onto the drawing window.

If the Verbalizer tab does not appear next to the Business Rules tab, open the Verbalizer ( Database > View > Verbalizer ), and select its tab to view the verbalization of the current selection on the diagram. Select the Mass object type and change the Type of its reference mode to Measurement. The verbalization of the reference scheme should now read "Every Mass value is measured in kg," as shown in Figure 4-3.

click to expand
Figure 4-3: If the reference scheme is unit-based, set its type to Measurement.

This figure also includes an Object Types window in the lower left corner. To view this window, select Database > View > Object Types . This window provides a compact list of all the object types and their reference modes. You can float this window to any position by right-clicking its title bar and choosing Float Window . Although you cannot use it to edit properties, you can use it to drag object types onto the drawing window.

The type of reference mode has four settings: Identification; Measurement; Formatting; and No reference mode. Identification is the normal case, and is the best choice for Country(code). Measurement is the best choice for unit-based reference schemes, such as Mass(kg). Formatting may be used to describe the format in which values are displayed. For example, Date(ymd) indicates that date values used in examples show the year first, then month, then day (e.g., 2002-08-14). The No Reference Mode setting is used for value types, or for entity types with no reference mode (e.g., compositely identified entity types).

The examples in Table 4-2 show how the type of reference mode influences the verbalization. Verbalizations may be included in reports , so choosing the type of reference mode can improve the documentation. However such choices have no formal significance and have no impact on the structure of the resulting database schema.

Table 4-2: The type of reference mode influences the verbalization.

Example

Type of RefMode

Verbalization

Country(Code)

Identification

Every Country is identified by one distinct Code.

CountryName

None

Date(ymd)

Formatting

Every Date value is recorded as ymd .

Mass(kg)

Measurement

Every Mass value is measured in kg.

Adding Object Types with the Fact Editor

Object types and reference modes may also be added to a model using the Fact Editor . The fastest way to do this is to include the reference modes while entering fact types in Freeform mode, as discussed in the previous chapter. For example, open the Fact editor ( Database > View > Fact Editor ), set the input style to Freeform, and enter the fact type, with reference modes placed in parenthesis after the object types. Value types are followed by empty parentheses (). A simple example is shown in Figure 4-4(a).

click to expand
Figure 4-4: Reference modes may be (a) included or (b) excluded, while entering fact types.

Alternatively, you can enter the fact type first without the reference schemes, as shown in Figure 4-4(b). You can then add details about the reference modes by selecting the Object pane of the fact editor, and making appropriate entries, one object type at a time, as shown in Figure 4-5. Although this allows you to enter the type of reference mode, and notes for the object type, you need to specify the data type elsewhere (e.g., using the Business Rules window or Database Properties window).

click to expand
Figure 4-5: Reference schemes may be omitted while entering fact types.

Adding Object Types with the ORM Source Stencil

Object types may also be diagrammed directly by dragging shapes from the ORM source stencil and using the Database Properties sheet to set the properties. Let's see how to do this with an example.

  1. Drag the Object Type shape from the ORM source stencil onto the Drawing window. By default, it has the name "Object."
  2. Double-click the object type to display its Database Properties (see Figure 4-6).

    click to expand
    Figure 4-6: An object type shape may be dragged directly from the ORM source stencil.

  3. Type the object type's name (e.g. "Region"). If you double-clicked before, this name replaces "Object" in the Name field of the Definition pane. If not, you need to move the cursor to this field before typing. As you type the name in the dialog box, the change is immediately displayed in the shape on the drawing window.
  4. Select the Ref Mode category, and enter the reference mode (e.g., Code). You can also set the data type, and add notes as discussed earlier.

Displaying Object Types

Regardless of how object types were created, they are all listed in the Object Types and Business Rules windows. At any time, you may drag existing object types onto the Drawing window from either the Object Types pane of the Business Rules window or the Object Types window.

The object types pane of the Business Rules window can also be used to locate an instance of the object type in the diagram . For example right-click the Country row of the table in Figure 4-1, and select Find Object Type in Drawing from the context menu. The Country shape in the drawing window is now highlighted. If multiple instances of the object type occur on the drawing surface, only one of these will be highlighted.

Deleting an Object Type

An object type may exist in the model without being displayed on a diagram. The same object type may be displayed in many places on the diagram, on the same or different pages. You can delete an object type shape from the drawing window by selecting it, and then pressing the Delete key. This invokes a message box with the prompt "Remove selected item from the underlying model?" If you answer Yes , the object type is removed from the model, so every shape depicting it on the diagram is also removed. If you answer No , the selected shape is removed from the diagram, but the object type still exists in the model (and any shapes depicting it elsewhere on the diagram remain ).

You can also delete an object type from the model by selecting it in the object types pane of the Business Rules window, and pressing the Delete key. In this case, no prompt is issued. If you deleted the object type by mistake, immediately press the Undo icon on the main menu (or press Ctrl+Z) to restore it.

You can also delete an object type from the model by selecting it in the Object Types list window, and pressing the Delete key. This invokes a message box with the prompt "This will delete all instances of this object type from this diagram. Are you sure?" If you answer Yes , the object type is removed from the model and the diagram. If you answer No , the object type remains in both the model and the diagram.

Name Spaces

As shown in Figure 4-7, the Definition pane of an object type's Database Properties window includes a Name space text box. On rare occasions, you may want to allow different object types in the same model to have the same local name. For instance, you might use a Region object type in a Sales model to refer to sales areas (e.g., North). You might also use a Region object type in a Geography model to refer to geographical regions (e.g., Oceania). If you later merge the models, and want to retain the original names for these separate concepts, you can distinguish them by assigning them different name spaces (e.g., Sales and Geography).

click to expand
Figure 4-7: Object types may be declared Independent, and assigned a Namespace.

As Figure 4-7 shows, the Object Types window displays the fully qualified names of object types (e.g., Sales.Region). The object types pane of the Business Rules window displays only their local names (e.g., Region), but you can find the corresponding shape by selecting Find Object Type in Drawing from the context menu. When you build a logical database model from a conceptual model, numbers are used instead of namespace names to provide distinct names (e.g., Region and Region1).

Independent Object Types

In ORM, an object type is said to be an independent object type if its instances can exist without playing other roles. Independent object types always map to a key, total table when the logical model is built (i.e., their identifier is used as the primary key of the table, and the table contains all their instances). One use of independent object types is to create reference tables. For example, if we want the database to include a table with all the region codes, we might declare the Region object type to be independent.

To declare an object type independent, open the Definition pane of its Database Properties window, and select the Check Box named Independent. The tool automatically appends "!" to the name of the object type to indicate its independent status (see Figure 4-7).

Note that if a region is identified by a code (e.g., ˜N ), but we also record a region name (e.g., ˜North ), then Region is not independent, since each instance plays a role other than that in its reference scheme. In this case, a two-column reference table is created, with one column for the code and another column for the name.


Fact Types

In ORM, a relationship type used to identity an entity type is called a reference type (e.g., Country has CountryCode ). Other relationship types are called fact types (e.g., Country competed in Sport ). Relationship types are also called sentence types or associations. In the Visio tool, all sentence types are informally called fact types, or simply facts.

You can add sentence types to an ORM model by entering their text into the Fact Editor. You can also add them graphically, by working with Object Type and Predicate shapes in the drawing window. You can edit existing sentence types using the Fact Editor or Database Properties dialog, and you can delete them with the Delete key. Most people find the Fact Editor to be the fastest and most natural way to add and edit sentence types, so let's consider these methods in turn .

Adding Fact Types with the Fact Editor

The basic operation of the Fact Editor was discussed in earlier sections. To invoke the Fact Editor from the Business Rules window, move the cursor to the bottom row of its Fact Types pane, then either start typing the fact type, or press the New button, or press the F2 key. You can also invoke the Fact Editor by choosing Database > View > Fact Editor from the main menu.

By default, the fact editor's input style is Guided, and the relationship is Binary (it has two roles), as shown in Figure 4-8. You can enter a binary relationship giving both a forward reading (e.g., Employee works for Department ) and an inverse reading (e.g., Department employs Employee ). To enter the forward reading, type the names of the object types in the Object Name fields, and the forward predicate name in the Relationship field. To enter the inverse reading, just type the inverse predicate name in the Inverse relationship field ”the object type names are provided for you in the relevant order.

click to expand
Figure 4-8: Adding a fact type in the Fact Editor using Guided input style.

If you wish to add reference schemes or other details for the object types, select the Object pane of the Fact Editor, enter your choices as discussed in the previous section, and press the Apply key to return to the Fact pane.

If you wish to add more fact types at this stage, press the Apply button to add the current fact type into the model. The editor is now ready to accept another fact type, with the subject of the previous entry named in the first Object Name field. Selecting the arrow at the right of this field displays a drop-down list of all the object types in the model for your selection. If you want to introduce a new object type, simply type its name over the top of the entry.

If you have no more fact types to add at this stage, press the OK button to add the current fact type into the model and exit the Fact Editor. If you pressed the Apply button instead of OK, and have no further fact type to declare, press the Cancel button to exit the Fact Editor.

Once you are familiar with the Fact Editor, you will probably want to change its input style to Freeform . As discussed in the previous chapter, freeform input allows you to enter sentence types faster by using a formal syntax. You can use the radio button to change to Freeform. You can also make Freeform the default by opening the Database Modeling Preferences dialog ( Database > Options > Modeling ) selecting its Fact Editor pane and setting the preferred mode to Freeform.

With freeform input, you can enter fact types in either Capitalized or Bracketed form, as chosen from the Fact Editor's Freeform drop-down list box. With capitalized form, you name each object type using a single word that starts with a capital letter, and you avoid capitalized words in the predicate text. The sentence can now be easily parsed into object terms and predicate. For example: Vice President visited Country .

For languages where this doesn't work, or when the name uses multiple words separated by spaces, use bracketed form . In this case, you must enclose the object type names in square brackets. For example: [vice president] visited [country] . The verbalization shown at the bottom of the Fact Editor always uses bracketed mode. If an inverse reading is supplied, a slash "/" is used to separate forward and inverse readings .

Recall that the arity of a predicate is its number of roles. A unary predicate has one role, a binary predicate has two roles, a ternary predicate has three roles, and so on. With freeform input, the arity of a predicate is determined simply by the number of object terms included in the sentence. With Guided input, you can select the arity from the Guided drop-down list box. This is set to Binary by default, as shown in Figure 4-8.

As an exercise, try adding the following two fact types using either freeform or guided input: ( Patient smokes; Patient was prescribed Drug by Doctor .) If using Guided input, set the arity to Unary for the smokes fact type, and Ternary for the prescription fact type. For ternary and longer fact types, Guided input provides extra fields to cater for more object terms and the rest of the predicate text, as shown in Figure 4-9.

click to expand
Figure 4-9: Entering a ternary fact type using Guided input.

To display a fact type on the diagram, drag it from the Business Rules window to the Drawing Window. For binary predicates with forward and inverse readings, both readings are displayed, separated by a slash. For ternary and longer fact types, only one reading can be displayed on the diagram. By default, a horizontal predicate is read from left to right, and a vertical predicate is read from top to bottom.

You can flip the direction of a predicate by selecting the relevant menu option (e.g., Shape > Rotate or Flip > Flip Horizontal ), tool bar icon, or hot key. Flipped predicates are displayed with "<<" in front of the predicate name to indicate the normal reading direction is reversed .

Ternary and longer predicates are often called n -ary ( n > 2). For n-ary fact types, only one reading can be entered in the fact editor. To add alternative readings for n-ary fact types, select the predicate shape and enter the other readings in the Readings pane of its Database Properties window. For example, Figure 4-10 includes two extra readings for the fact type Patient was prescribed Drug by Doctor.

click to expand
Figure 4-10: Adding alternative readings for n-ary predicates.

The object types are listed in a column, in the order in which they fill the " " placeholders in the first predicate reading to provide the fact type reading. The tool assigns position names to the roles played by the object types, based on their order in the list. In this example, Patient = %1, Drug = %2, and Doctor = %3. Numbers are used because in general the same object type may play more than one role in the same predicate. When entering a second or later reading, you must associate this reading with the relevant order to traverse the roles in the fact type. You do this by using the position names to refer to the roles. In this example for instance, you enter %2 was prescribed for %1 by %3 to declare the alternate fact type reading Drug was prescribed for Patient by Doctor. Although the alternate readings are not displayed on the diagram, they can be viewed in the Verbalizer window, as shown in Figure 4-10.

Adding Fact Types with the ORM Stencil

Fact types may also be diagrammed directly by dragging shapes from the ORM source stencil and using the Database Properties sheet to set the properties. Let's see how to do this with an example.

  1. Drag two Object Type shapes from the ORM source stencil onto the Drawing window, and use the Database Properties window to declare them as Patient(Nr) and Drug(name).
  2. Drag a Predicate shape from the stencil onto the Drawing Window, and click one of its roles twice. The first click selected the predicate. The second click selected the role, displaying a control handle (yellow diamond) at the end of its role connector.
  3. Select the role connector's control handle, and drag it onto the border of the Patient shape to glue it to a connection point. By default, connection points are not displayed, but the tool notifies you when it makes a connection.
  4. Select the other role (two clicks) and glue its connector it to the Drug shape.
  5. Select the predicate shape. In the Readings pane of its Database Properties window add the forward predicate text " is allergic to ." Optionally, you may add an inverse predicate reading, and readings for one or both roles (see Figure 4-11).

    click to expand
    Figure 4-11: Editing the properties of a predicate shape

If you need to define a unary predicate, or an n -ary predicate, use the Definition pane to set the relevant arity, as shown in Figure 4-12. This pane can also be used to declare a namespace for the predicate, or to declare it as external. You may add explanatory comments about the predicate in the Notes pane. Other panes are discussed later.

click to expand
Figure 4-12: The arity of a predicate may be any number from 1 through 9.

Displaying Fact Types

Regardless of how fact types were created, they are all listed in the Fact Types pane of the Business Rules window. At any time, you may drag existing fact types from this pane onto the Drawing window to have them displayed. To select a contiguous series of fact types, hold the Shift key down as you select the first and last fact type in the series. All but the first fact type will appear highlighted. You can then drag the fact types on to the drawing page where you want them displayed. You can finesse the display by moving the predicate text and object types around.

A handy alternative is to open the Object Types pane of the Business Rules window, drag out one or more relevant object types, and use the Show Relationships option. For example, if you drag the Patient object type onto any drawing page, right-click Patient, and select Show Relationships from its right-click menu, all the relationships in which Patient plays will be displayed on that page. This feature is extremely useful in schema browsing and in reverse engineering.

The fact types pane of the Business Rules window can also be used to locate an instance of the fact type in the diagram . Right-click any fact type listed in the pane, and select Find Fact in Drawing from its context menu. The tool highlights the first instance it finds of the fact type shape in the drawing window. In a multi-page model, it may need to change the active page to do this. If multiple instances of the fact type occur on the drawing surface, only one of these will be highlighted.

Editing Fact Types and Deleting Predicates

You can edit an existing fact type using either the Fact Editor or the Database Properties window. You can select the fact type's row in the Business Rules window, and press the Edit button to bring up the Fact Editor. You can also select the fact type in either the Business Rules window or Drawing window and then open the Fact Editor on it (Database > View > Fact Editor). If the fact type is displayed on the drawing window, you can also select it to bring up its Database Properties sheet and edit it there.

A fact type may exist in the model without being displayed on a diagram. The same fact type may be displayed in many places, on the same or different pages. To delete a predicate shape from the drawing window, select the shape, and then press the Delete key. This invokes the prompt "Remove selected item from the underlying model?" If you answer Yes, the predicate is removed from the model, so every shape depicting it on the diagram is also removed. If you answer No, the selected shape is removed from the diagram, but the predicate still exists in the model; any shapes depicting it elsewhere on the diagram remain .

Note that deleting a predicate does not delete the object types that played in that predicate. If you want to delete them also, you need to select them before pressing Delete. If you select multiple elements on the drawing surface and press the Delete key, you are prompted "Remove all selected items from the underlying model?" Answer Yes or No according as to whether you wish to perform this bulk delete.

You can also delete a fact type from the model by selecting it in the fact types pane of the Business Rules window, and pressing the Delete key. In this case, no prompt is issued. If you deleted the object type by mistake, immediately press the Undo icon on the main menu (or press Ctrl+Z) to restore it.


Adding Basic Constraints in the Fact Editor

An internal constraint applies to just one predicate. External constraints apply to two or more predicates. The Fact Editor allows you declare the following internal constraints: internal uniqueness, simple mandatory, internal frequency, and ring constraints. It does not allow you to specify internal, set-comparison constraints (e.g., an exclusion constraint between two roles of the same predicate), external constraints (e.g., an external uniqueness constraint, or a set-comparison constraint between two predicates) or value constraints (e.g., restricting Sexcode values to { ˜M , ˜F }).

In practice, constraints declared in the fact editor are best restricted to simple internal uniqueness and simple mandatory constraints, as discussed in this section, and ring constraints, as discussed in the next chapter. Value constraints are added using the Database Properties window, and all other constraints may be added using the Add Constraints dialog, as discussed in the next chapter.

You can add internal uniqueness and mandatory constraints to a binary or longer fact type when you first enter the fact type in the Fact Editor. Alternatively, you can select an existing fact type on the drawing window, and open it for editing in the Fact Editor (Database > View > Fact Editor). Some examples were considered in the previous chapter. Let's look at a couple more now.

Open the Fact Editor on the fact type Employee works for / employs Department , and select the Constraints tab. For a binary fact type, the constraints pane by default combines uniqueness and mandatory constraints to make it faster to specify them. For instance, in Figure 4-13, choosing "exactly one" means both "at least one" (mandatory) and "at most one" (unique). If you don't want to use this default shortcut, open the Database Modeling Preferences dialog ( Database > Options > Modeling ) and uncheck the option that indicates combined uniqueness and mandatory. The constraint symbols and verbalization automatically appear below to help you see the result of your choice. If there are more than two constraints involved, only two of these are verbalized here. You can always see the full verbalization later by selecting the fact type on the diagram and opening the Verbalizer window.

click to expand
Figure 4-13: Adding uniqueness and mandatory constraints in the Fact Editor.

For a binary fact type, you are asked two constraint questions, one for each role. For each question, select the relevant choice from the drop-down list: Zero or One; Zero or More; Exactly One; One or More.

Including an inverse predicate reading often improves the clarity of the second constraint question. If the question is still unclear, press the Rephrase button to have the question formulated in a different way. Press the Apply button to enter the constraints into the model and leave the Fact Editor open. Press the OK button if you want to enter the constraints into the model and close the Fact Editor.

Caution

Don't declare a role to be mandatory for an object type unless each instance of that type in the database must play that role. It is rare for a role to be mandatory unless it also has a simple uniqueness constraint. It is very rare for a role played by a value type to be declared mandatory. For example, consider the fact type Patient(Nr) has / is of FamilyName() . If FamilyName plays no other role in the schema, its role is implicitly mandatory, so there is no need to declare this. If it does play another role in a fact type (e.g., Person formerly had Family Name ), it may be possible to have an instance of Family Name that does not currently name a patient.

A uniqueness constraint may be declared primary by selecting the relevant role(s) from the drop-down list for Primary Figure 4-14 declares the primary reference scheme for Country explicitly, using a mandatory 1:1 association between Country and Country Code. Primary uniqueness constraints are marked with a "P". As discussed earlier, it's easier to declare the reference scheme implicitly using a reference mode.

click to expand
Figure 4-14: Declaring a primary uniqueness constraint.

If the fact type is ternary or longer, the Constraints pane is displayed differently. Figure 4-15 depicts entry of a uniqueness constraint that spans all three roles of the fact type Patient was prescribed Drug by Doctor discussed in Figure 4-10. To add a uniqueness constraint, select the uniqueness constraint button at the top left, then select the relevant constraint option, and press the Space key . To toggle the constraint off, press the space bar again. Unless the constraint spans all the roles, you may add more than one uniqueness constraint.

click to expand
Figure 4-15: Declaring a uniqueness constraint on a ternary fact type.

It is rare for any role of an n -ary fact type to be mandatory. If you do need to add a mandatory constraint to a role, select the mandatory constraint button in the second-top left position, then select the relevant mandatory constraint option, and press the Space key. To toggle the constraint off, press the space bar again.


Populating Fact Types with Examples

It is a good idea to include sample population of fact instances for all fact types. You can do this when you declare the fact type in the Fact Editor, or later by selecting the fact type on the diagram and opening the Fact Editor for it ( Database > View > Fact Editor ). Click the Examples tab of the Fact Editor, and enter enough sample facts to illustrate the relevant constraints.

For example, Figure 4-16 shows three fact instances for the fact type Patient was born in Country . Here the patients with patient numbers 1001 and 1002 were born in the United States (CountryCode = ˜US ), and patient 1003 was born in Australia (CountryCode = ˜AU ). The population is consistent with the uniqueness constraint pattern shown on the fact type. Each role of the predicate corresponds to a column in the sample fact table. The first role has a simple uniqueness constraint, so the entries in the first column must be unique. This indicates that each patient was born in at most one country. The second role does not have a uniqueness constraint, so entries in the second column may be duplicated (e.g., ˜US ). This illustrates the possibility that more than one patient was born in the same country.

click to expand
Figure 4-16: Adding a sample fact population to a fact type.

For a binary fact type, you should include at least three rows of data to illustrate its uniqueness constraint(s). You can use the Analyze button to request the tool to induce the constraints from your examples, or to check for inconsistencies between your data and your constraint specification. For example, if you incorrectly replace 1003 by 1002 in the third row, the analysis leads to an error message noting that the examples suggest a uniqueness constraint spanning both roles, not just the first. In this case, close the analysis results dialog and correct the examples.

If the examples are significant however, you can press the Apply UC Constraints button to have the tool apply the uniqueness constraint pattern that is consistent with the sample population. Try it out for yourself. This is a very useful feature for validating constraints with domain experts, since they find it easier to think about the rules using concrete examples.

If you select a fact type for which you have provided examples, and then you open the Verbalizer (Database > View > Verbalizer) the examples are also verbalized, as shown in Figure 4-17. As discussed later, fact examples may also be included in printed reports . You cannot delete an example row by clearing each of its cells , since the tool merely replaces the values by nulls, shown as "=???>" in the verbalization. To delete a fact example, right-click its row and select the option Delete Rows from the context menu.

click to expand
Figure 4-17: Fact examples are included in the verbalization of the fact type.


Saving a Model

To save your model, choose File > Save from the File menu, or click the Save (diskette) icon. If the model has not been saved before, this opens the Save As dialog box. Choose the folder where you want to save the model, add a filename for the model, press the Save button in the dialog, then press OK in the properties dialog. The file will be saved with the extension ".vsd" (Visio document). If you previously saved the file, then the Save operation simply replaces the old copy with the latest version of the model without opening any dialog boxes.


Verbalization and Hyphenation

Both the ORM source model and the logical database model solutions provide automatic verbalization of any part of the model that you select, including any examples that you entered. This feature is very useful for communicating the meaning of a model to non-technical domain experts. To illustrate this feature, let's open the sample Employee ORM source model that comes with the product. To open this model, choose File > New > Browse Sample Drawings then select the Database folder and the Employee ORM source sample file, and hit the Open button.

The Employee page of the Employee source model should now appear. The full model is spread over three pages, called Employee, Project and Room. The name of the currently displayed page appears in a tab below the drawing window. By default, only the Database Properties and Business Rules windows appear below the drawing window. To open the verbalizer window, choose Database > View > Verbalizer from the main menu. This should now appear below the drawing window. If you ever have trouble seeing all the windows, choose Window > Tile from the main menu.

Now use the mouse to select the part of the model you want verbalized. To select a single model element, simply click on it. To select an area of the model to be verbalized, hold the left house button down and drag the cursor diagonally over the area. All aspects of the model within that area will be verbalized (including fact examples if you have added them). In Figure 4-18, the Employee has MobileNr and Employee has Room predicates and their constraints are selected. The verbalization appears in the Verbalizer window (shown here in a float position).

click to expand
Figure 4-18: All model elements in the current selection may be verbalized.

The Verbalizer window remains open until you close it. You can float or dock windows freely by right-clicking their title bars and choosing the relevant option from their context menu. For windows docked together, you can choose which of them are displayed at any time by selecting the relevant tab at the bottom of the combined window (e.g., Database Properties, Business Rules, or Verbalizer).

The tool verbalizes constraints mainly by inserting logical quantifiers (e.g., each, some, at most one ) and operators (e.g., If then ) into fact type readings . If you include an adjective in a predicate just before an object place-holder, you can often improve the constraint verbalization by appending a hyphen to the adjective.

For example, Figure 4-19 shows the verbalization of two fact types: Patient has firstGivenName; Patient has second-GivenName . Note the use of the hyphen in the predicates "has first-" and "has second-". This binds the adjectives "first" and "second" to GivenName when constraints on those predicates are verbalized, so that keywords like "some" or "at most one" are inserted before the adjective instead of after it. In this example, if you omitted the hyphens, the constraints would verbalize instead as " Each Patient has first at most one GivenName " and " Each Patient has second at most one GivenName ", which is at best awkward and at worst unintelligible.

click to expand
Figure 4-19: Use of hyphens to bind predicate parts to object terms in the verbalization.

The use of hyphens does not have a direct effect on the logical model that the tool creates from the ORM model. However, the overall effect is potentially large, since hyphenation can help the domain expert to understand the verbalization and correctly validate your model.


Objectifying an Association (Nesting)

The sample model in Figure 4-18 includes the association Employee took Course . To record the grade (if any) that an employee gets for a given course, the association was objectified as Coursework , and the fact type Coursework resulted in Grade was added. The object type Coursework is said to be nested, since it nests an association inside it.

Nesting is specified using the Advanced pane of the Fact Editor. If you enter a new fact type (e.g., Employee plays Sport ) in the Fact Editor, you can add the nesting before closing the editor. If instead you have a fact type on the diagram that you want to objectify, then first select the fact type and then open the Fact Editor ( Database > View > Fact Editor ). Now select the Advanced tab and enter a name for the objectified association in the field labeled " Objectify / Nest fact as :" For example, you might objectify Employee plays Sport as Play . Figure 4-20 shows the nesting declaration for the Coursework association in the sample model.

click to expand
Figure 4-20: Nesting is declared using the Advanced pane of the Fact Editor.

If the association is already on the drawing window, pressing the OK button in the Fact Editor causes the nesting envelope to be displayed around the association. You can also display a nested object type by dragging it out from the Business Rules editor. The name of the objectified association appears outside the nesting envelope, as in Figure 4-18. You can reposition this name by selecting the nested object type, and then dragging its control handle (which appears as a small, yellow diamond). You can also resize the envelope vertically by dragging a shape handle (small green square).

ORM currently requires that each objectified association either has a spanning uniqueness constraint, or is a 1:1 association. This rule is enforced when a model error check is performed (see next section).

When you create a nested object type, the tool automatically creates derived predicates between the nested object type and the object types involved in its defining predicate. This provides a uniform way of navigating from any object type (nested or un-nested) to the rest of the schema, and is mainly designed to facilitate conceptual queries. By default the derived predicates are named "involves" or "is involved in". If you wish, you may rename these predicates by double-clicking the nested object type on the diagram window to display its Database Properties Sheet, then selecting the Nested Roles category and renaming the nested role readings .


Model Error Checks

The tool allows you to create and save your conceptual schema in stages, even if the schema is incomplete or incorrect. However, before the tool can map your conceptual schema to a logical database schema, your conceptual schema must be syntactically valid ”it must conform to the grammatical rules laid down by the ORM metaschema. The tool provides both basic and deep model error checking mechanisms to enforce these rules of grammar.

To request a basic model error check at any time, select Database > Model Error Check from the main menu. This test quickly scans your model to see whether it satisfies many basic rules to which all valid ORM model must conform. Any errors detected are displayed in the Output window. Double-click the error message to highlight the model element causing that error . For example, a model error check on the ORM model in Figure 4-21 generates error message 2006. Double-clicking that message highlights the offending fact type: Patient is allergic to Drug . If the predicate is not yet on the diagram, you are prompted to drag it there for viewing. If the error message is unclear, select the message and press F1 to view the online help topic for that error number. In this example, the error can be removed by adding a uniqueness constraint to the predicate.

click to expand
Figure 4-21: A Model Error Check displays errors in the Output window.

The tool automatically performs a deep model error check whenever you attempt to build a logical model from the conceptual model. This procedure takes longer but is far more thorough, and can detect many kinds of errors that might not be detected by a basic model error check. Again, double-clicking an error message takes you to the ORM model element causing the error, so you can fix it before trying another build. The build will not succeed until you have fixed all the errors.


Derived Fact Types

If a fact type may be derived from one or more other fact types, you may specify this using either the Advanced pane of the Fact Editor or the Derived pane of the Database Properties window. Select the relevant radio button to set the derivation status of a fact type to be None (not derived), Derived or Derived and stored . If the fact type is displayed on the diagram, a single asterisk "*" is appended to the predicate name to indicate it is derived (but not stored).

A double asterisk " ** " indicates the fact type is both derived and stored. This setting requires special care (e.g., a derived and stored fact should not be updateable unless the derivation rule is preserved), so should be chosen only in exceptional cases.

The ORM schema in Figure 4-22 includes the derived fact type Window has Area. Whenever you declare a fact type to be derived, you should also enter a derivation rule to declare how it can be derived from other fact types. Although ORM includes a formal language for specifying derivation rules, the tool does not yet support this language. So any derivation rules entered in an ORM schema are treated just as comments. To apply the derivation, you need to provide the relevant code yourself at either the logical or physical level. For example, you could create a relational view WindowView ( windowNr , height, width, area) with the rule for computing area included in the SQL code for the view definition. Views are discussed in a later chapter.

click to expand
Figure 4-22: Specifying a derived fact type in the Fact Editor.

If you declare a fact type to be derived and stored and request the tool to build the logical database schema, the tool will create one or more base table columns to store facts of this type. However, you still need to write the code to enforce the derivation rule. How you do this (with triggers or computed columns , etc.) depends on the DBMS.

The derivation for the area example involved simple arithmetic computation. In such cases, it's often easier to declare derivation rules in attribute style. If you assign the role names "height," "width," and "area" to the right-hand roles of the predicates, you can attribute these to the left-hand object type using dot notation (e.g.,( Window.area ). This enables you to formally capture the derivation rule in attribute style.

If the derivation involves logical inference of a more general nature, it's often convenient to specify the derivation rule in relational style, as shown in Figure 4-23. Here numbers are appended to distinguish the object type variables Person1, Person2 and Person3. If you assign the names "parent," "child," "grandparent," and "grandchild" to the roles of the parenthood and grandparenthood predicates, you could declare the derivation rule in attribute style as Person.grandparent=Person.parent.parent .

click to expand
Figure 4-23: Derivation may also be declared in the Database Properties window.

Although compact, this has the disadvantage of being navigation-specific (e.g., it doesn't tell us how to derive someone's grandchildren). Moreover, the role names actually refer to sets in this case, and if you use plural names to distinguish such cases, the rule becomes unstable if the uniqueness constraint pattern may change over time.

If that last statement lost you, don't worry. The tool doesn't force you to specify the rules formally. Just write them down in any way that makes sense to those who have to validate or implement the rules.


Data Types

In the first section of this chapter, you saw how to specify a data type for an ORM object type using either the Object Types pane of the Business Rules window, or the Data Type pane of the Database Properties window. It's time now to look at data types in more detail.

Although data types for columns may be specified at the relational level, it is far better to specify data types at the ORM level. Why? First, it saves a lot of work, because data types in ORM correspond to the syntactic domains on which relational attributes are based. Typically each ORM object type plays many roles, each of which maps to one or more columns in a relational database. Setting the data type once for the object type propagates that data type to every attribute mapped from its roles. Second, this avoids type mismatch problems in the generated relational database (e.g. foreign keys are automatically given the same data type as the primary keys they reference).

Third, it makes it much easier to change data types. For example, a database might include hundreds of columns concerning dates (birthdate, hiredate, orderdate etc.). Suppose you need to change the data type for each of these columns from, say char(10) where dates were stored as character strings like ˜2002-07-06, to a datetime data type with built-in support for date arithmetic. At the ORM level, all you need do is change the data type for the object type Date. At the relational level, you need to change all the hundreds of date column data types (unless your DBMS properly supports relational domains or user -defined types, and you have been disciplined in using this support). Of course, after changing the schema for a populated database, you still have the data migration problem but having the schema updated so easily is a major benefit.

If you have decided on the target DBMS, and you want to see the physical data types for that DBMS, you should setup the relevant database driver . To do this, choose Database > Options > Drivers from the main menu to invoke the Database Drivers dialog box and select the relevant DBMS. Figure 4-24 shows the main pane of the dialog screen when choosing Microsoft SQL Server. Ignore the other panes for now. Press the Setup button to open the Setup dialog, and use the ODBC Drivers pane to select the relevant ODBC driver.

click to expand
Figure 4-24: Selecting a DBMS driver.

Microsoft provides ODBC drivers for many DBMSs. For some DBMSs (e.g., DB2), native ODBC drivers may be obtained from their vendors . If your DBMS is not on the list, you may use the ODBC generic driver. Use the Preferred Settings pane of the Setup dialog to make more specific choices (e.g., to set the SQL Server version to 2000). For further details about setup and ODBC, see Chapter 11.

By default, each object type in an ORM model is assigned a data type of char(10). Before mapping an ORM model to a relational model, you should declare the relevant data type for each object type. If the object type is displayed in the drawing window, you can double-click it to bring up its Database Properties sheet, select the Data Type pane, and then press the Edit button to enter the new data type. For instance, if you select the FamilyName object type in Figure 4-25, the Data Type pane of its properties dialog appears with the default data type of char(10) as shown. Here the radio button is set to show the physical data type for the chosen DBMS, in this case SQL Server.

click to expand
Figure 4-25: To edit the data type in the Database Properties window, press the Edit button.

The data type field in the Database Properties sheet is read-only, so you cannot edit it directly. Instead, press the Edit button to invoke the Data Type dialog box specific to that DBMS. The Native type and Length fields display the defaults (char and 10). To store family names as variable length character strings, select the relevant item (e.g., varchar) from the Native type drop-down list (scroll down, or quickly type the initial characters until the item appears), as shown in Figure 4-26(a). Click the mouse to accept the change, then move the cursor to the Length field. To allow for family names of up to 30 characters, change the value from 10 to 30 (see Figure 4-26[b]), then press the OK button to accept the change. The Database Properties window now shows the data type as varchar(30).

click to expand
Figure 4-26: Changing the physical data type to varchar(30).

If you want to edit several data types, it's quicker to do this in the Object Types pane of the Business Rules Window. If needed, open this window (Database > View > Business Rules) and select its Object Types tab to display the Object Types pane. The data type field supports a drop-down list for selecting the native type and allows you to edit the length directly (see Figure 4-27). The whole field is editable, so you can simply type in the desired data type (e.g., varchar(30)) without accessing the drop-down list at all. The field is parsed as you go, with basic Intellisense support (e.g., if you type "varc" this is automatically expanded to "varchar"). If you enter an illegal data type, the Data Type dialog box is invoked for you to complete the entry there.

click to expand
Figure 4-27: Basic data type settings may also be edited in the Business Rules window.

The reference scheme Country(code) . abbreviates the association Country is identified by CountryCode , so when you set the data type for Country you are actually setting the data type for CountryCode. One standard way to reference countries is by their 2-letter ISO codes (e.g., ˜AU for Australia, and ˜US for the United States). This choice requires a fixed length character string of 2 characters in length, so the data type for Country should be set to char(2). Value types often have other constraints that can't be captured by simply declaring a built-in data type. For example, the characters in country codes must be letters , and social security numbers must match the pattern ddd-dd-dddd (where "d" denotes a digit). Currently, the tool does not support the specification of such pattern constraints, but they are easy to implement manually in most DBMSs.

If you know the DBMS in which your model will be implemented, you will probably prefer to work with physical data types. If you haven't made that choice yet, or you intend to work with many kinds of DBMS, then you may prefer to use portable data types . When a portable data type is mapped to a given DBMS, it is replaced by a corresponding physical data type. To display portable data types on the Data Type pane of the Database Properties window, select the "Show portable data type" radio button.

Use the drop-down lists to select the appropriate values. For example, Figure 4-28 shows portable settings that might be used in place of the physical data type varchar(30). The Text category is used for character strings, the Type is set to Variable Length, the Size is set to Single Byte characters (rather than Double Byte), and the Length is set to 30. As discussed earlier, you can toggle the display of data types between physical and portable in the Object Types pane of the Business Rules window by right-clicking the table header and selecting that option from the context menu.

click to expand
Figure 4-28: Portable data types may be used instead of physical data types.

The possible settings for portable data types are summarized in Table 4-3. You can access details about specific settings by using the on-line help. If you choose the Numeric category, one of the options is Auto Counter . This is used if you want the DBMS itself to provide a unique number for each entry in the relevant base table column, automatically incrementing the number for the next entry in the column.

Table 4-3: Portable data types.

Category

Type

Length/Precision (= default)

Scale (= default)

Size

User-defined

typename

Text

Fixed length
Variable length
Large length

Length = 10
Length = 10



1-byte char set
2-byte char set

Numeric

Signed integer
Unsigned integer
Auto counter
Floating point
Decimal
Money



Precision = 10

Precision = 10
Precision = 10





= 2
= 2

Small, Large
Small, Large
Small, Large
Small, Large
Small, Large
Small, Large

Raw data

Fixed length
Variable length
Large length
Picture
OLE object

Length = 10
Length = 10










Temporal

Auto timestamp
Time
Date
Date & Time







Small, Large
Small, Large
Small, Large
Small, Large

Logical

True or False
Yes or No



Small, Large
Small, Large

Other

RowID
ObjectID
Unknown







Support for this feature depends on the DBMS as well as the tool. For example, in Microsoft Access this maps the Counter data type, and in SQL Server this is implemented as smallint with the identity property added. For SQL Server, if you display physical data types in the Database Properties window, you can add the identity property to any numeric type by checking the Identity option in the Data Type dialog. You can see this option in Figure 4-26(a), but it is grayed out because the data type is not numeric. For Oracle databases, the tool can be configured to create an Oracle Sequence object for all auto counter primary keys. The tool will also write the trigger for assigning primary keys to new rows based on the Oracle .NEXTVAL syntax.

If an ORM object type with the identity property is mapped to a logical schema, the identity property is incorrectly displayed as applying to all columns mapped from role of the object type, not just the desired primary key column. However this error is fixed when the actual DDL is generated for the physical schema. If you find this apparent inconsistency confusing, you may prefer to delay setting any identity properties until the logical level.

To define user-defined data type, choose the Database > User Defined Types from the main menu to invoke the User defined Types dialog. Press the Add button to enter a name for the type, then enter the settings for the type in the portable data type property fields. Physical data type settings cannot be displayed in this dialog. The example shown in Figure 4-29 effectively defines phoneNrType as a synonym for char(12).

click to expand
Figure 4-29: Defining a user-defined type.

Once defined, a user-defined type can be used like any other data type, and will appear in drop-down lists of data types. Because they are data types rather than object types, user-defined types do not appear as object types in the Business Rules window or in the Object Types window. In an ORM model, user-defined data types are of little use, because ORM object types themselves provide even stronger support for semantic domains, and allow value constraints to be specified (see next chapter). Once you define a data type for an ORM object type, this propagates to all relational columns mapped from those roles, along with any value constraints defined for the type.

If you don't use ORM, however, user defined types are useful at the logical level. You can define many columns based on the same user-defined type, so if ever you change that user-defined type, the change propagates to all the columns defined on it. Although the tool supports this concept of user-defined types, not all DBMSs do so. If you wish to avail yourself of this feature, ensure that your DBMS supports it, and check that the tool generates appropriate DDL for this feature.




Database Modeling with Microsoft Visio for Enterprise Architects
Database Modeling with MicrosoftВ® Visio for Enterprise Architects (The Morgan Kaufmann Series in Data Management Systems)
ISBN: 1558609199
EAN: 2147483647
Year: 2002
Pages: 150

Flylib.com © 2008-2020.
If you may any questions please contact us: flylib@qtcs.net