Attributes

"Atomic" attributes

The mapping of attributes "in columns" implies that only atomic attributes should be used: the first normal form is thus obtained. Therefore, basic types (or predefined primitive classes) must have a relational non-decomposable equivalent.

Type mapping

The action of mapping attributes poses a problem commonly known as the "impedance problem" (because of the similarity to problems in electricity).

An object-oriented information structure has to be transformed in a table-oriented structure, in other words, language types have to be converted into RDBMS primitive types.

Generation suggests, by default, a global conversion policy that can be adapted and added to by the user (see "Configuring SQL Designer" for further details). The table below presents the mapping rules for the ORACLE, Sybase and SQL Server RDBMS.

Model	ORACLE Data Types	Sybase and SQL Server Data Types
integer	INTEGER	INTEGER
{short} integer	INTEGER	SMALLINT
{long} integer	INTEGER	INTEGER
{unsigned} integer	INTEGER	INTEGER
boolean	INTEGER	BIT
real	FLOAT	REAL
{long} real	FLOAT	FLOAT (p)
string(n)	VARCHAR2(n)	VARCHAR (n)
char	CHAR (1)	CHAR (1)
set {array} (n) of char	VARCHAR2 (n)	CHAR (n)
{unsigned char}	/	TINYINT

Note: SQL Designer does not check the uniqueness of the names in the SQL sense of the term, not being case sensitive. This type of error, therefore, only appears when the SQL script is executed.

Typing attributes with an SQL type

To type an attribute using an SQL type that does not have a UML equivalent, such as the date type for example, it is possible to use the {sqlType} tagged value by defining the parameter with the desired type.

The UML type of the attribute is then not taken into account for generation, since the parameter values of the {sqlType} tagged value, if defined, take priority.

For example, with SQL Server, to generate a date type column, you should:

· simply annotate the attribute with the {sqlType} tagged value, with "datetime" as its parameter

· or select the "datetime" value from the "SQL type" scrolling list in the SQL tab of the auxiliary window (as shown in Figure 26 below)

Figure 26. Typing an attribute with the "datetime" SQL type

Access restriction

Attributes can be declared public, protected or private.

Class attributes

A class attribute is unique for the set of instances of the class. In the context of databases, this concept should be clarified by stating that a class attribute is unique for the database which memorizes it.

Two transformations are possible:

· By the creation of a table that groups together all the class attributes of all the application's classes

· By the creation of a specific table associated with the class on which one or several class attributes are defined.

The first transformation is simple to implement, and limits the number of tables. All class attributes for all classes are grouped in a table, indicated by the parameter of the {globalTable} tagged value on the class attributes. Each line of this table contains the value of a class attribute.

However, this pattern presents a major disadvantage, in as much as it requires the definition of one column per type of class attribute. For example, if five class attributes are present, three of which are integer type and two string type, the global table will contain two columns:

· an "att_int" integer-type column

· an "att_string" string-type column

The table structure also includes a "ref_attribute" attribute, to contain the name of the class and of the class attribute. It is possible to have several tables which group together class attributes.

The following model (as shown in Figure 27) presents two class attributes which will be grouped in a global table.


Figure 27. The analysis model, modeling two class attributes

Figure 28 shows the physical model after transformation and automatic generation of a global table.

Figure 28. The physical model, implementing a global table for class attributes

Figure 29 presents the "ClassAttributeTable" table for the "Class1" and Class2" classes.

Figure 29. Example of a table containing class attributes

The second transformation consists of the definition of one table per class, including one or several class attributes. This transformation is more in line with the class attribute notion, despite the generation of a specific table. No annotation is necessary, and a finer management of attributes and their associated type is possible. The generated table bears the "classname_attribute" name. This name can be modified, by using the {classAttributesTableName} tagged value.

Figure 30 shows the analysis model.

Figure 30. Analysis model, modeling a class attribute for the generation of a specific table

Figure 31 shows the corresponding physical model.

Figure 31. Physical model, generating a class attribute in a specific table

The {sqlDefault} tagged value

In Oracle or Sybase, the DEFAULT clause is used to specify the value that will be assigned to a column if the insertion of a set of values misses a value for this column. In SQL coupling, this feature is implemented using the {sqlDefault} tagged value on attributes.

Note: This notion is separate from the notion of initial value in the model (taken into account in dynamic code). Specifying this value in an attribute's dialog box has no effect whatsoever on SQL generation. The auxiliary window can be used to enter this tagged value.

Primary key definition

The primary key of a relational table is composed of one or more attributes (the table's columns). It allows the clear identification of the instances (in other words, the sets of values).

Second normal form: the primary key must be composed of the smallest combination of attributes which are used in identification.

The {primaryKey} tagged value

You must define the primary key of the associated table for a persistent basic class using the {primaryKey(rank)} tagged value, with as parameter the rank of the attribute in the primary key. Its declaration consequently forbids null values for columns thus specified, as well as guaranteeing the key's uniqueness. This uniqueness constraint will be mentioned in the following paragraphs. Just like {persistence}, this tagged value is generalized.

Note: Key order can influence access to the database. It is preferable to rank its components from the most discriminating to the least selective. A button in the auxiliary window on a class and a menu on classes can be used to open a window used to graphically define the primary key.

Example

Figure 32. Defining the primary key

In other words, in SQL (without taking into account the key's uniqueness constraint):

CREATE TABLE GRADESAL (

GRADE… NOT NULL,

LEVEL… NOT NULL,

MINSAL…,

MAXSAL…

);

Uniqueness of the primary key

The declaration of a primary key brings about an integrity constraint. Each time sets of values are created, it is necessary to check that the value of the key does not already exist in the database, in order to preserve the uniqueness of the primary key.

As the primary key is also a priority access key to the information, this constraint is implanted via the declaration of a "unique index", the generation of which depends on the target RDBMS. For example, Oracle uses the "PRIMARY KEY" command. The name of this index is the name of the table with the "_PK" suffix.

This gives the following in SQL:

CREATE TABLE GRADESAL (

GRADE… NOT NULL,

LEVEL… NOT NULL,

MINSAL…,

MAXSAL…

);

CREATE UNIQUE INDEX GRADESAL_PK

ON GRADESAL (GRADE, LEVEL);

Generalization and the primary key

The primary key of a child class is obligatorily the same as the parent class' primary key.