Oracle database schema

The conceptual model described in Section 3.2 is translated to a logical model, the database schema for Oracle Spatial. This translation can be done automatically from the Enterprise Architect (EA) using model transformation commands; a screenshot is given in Figure 3.11.

Figure 3.11: Screenshot of Enterprise Architect showing the model transformation window for automatic generation of tables from the class model.

Figure 3.12 shows the UML diagram of tables created from EA. For each class in the conceptual level there is a corresponding table in the logical level. All the one-one or one-many associations are resolved by primary key, foreign key relations. The label PK in front of a column name indicates that it is (part of) a primary key, label FK indicates that it is a foreign key, label pfK indicates that it is (part of) a primary key that is also a foreign key, * indicates that the column cannot be empty, and an arrow shows foreign key – primary key relation. The automatically generated tables and relationships need corrections. The translation of types is not always correct, e.g. an int or integer type is not translated to an Oracle data type, as well as date and timestamp data types. The dependencies are not resolved correctly, e.g. the class Sectormal is dependent on the class RealIncident but the translation has not created a foreign key relationship; the same happens for Gasmal, Process, DamagedCar, DamagedBuilding, and DamagePA. The associations with attributes are also problematic; the association InvolvedIn is created as a separate table with no

relationship with DMSUser and Process, while a new table JoinDMSUserToProcess is created from the association between DMSUser and Process. The automatic translation adds a primary key attribute named after the table name and followed by

‘ID’ in case there is no such attribute; e.g. the attribute eventObjectID is added to table EventObject, while we have meant objectID to be the primary key of the table.

In addition to these, several data types in our conceptual model require special treatment, e.g. boolean, enumeration types, the spatial types, as well as the new data types that were created.

Figure 3.12: EA automatically generated tables from the class model: PK indicates a primary key, FK a foreign key, pfK a foreign key that is also a primary key; an arrow indicates tables relationship.

Figure 3.13 shows tables and relationships after making the necessary modifications:

types int and integer are changed to ^NUMBER(), the types date and timestamp are changed to ^DATE and ^TIMESTAMP, respectively. We changed the spatial types to

MDSYS.SDO_GEOMETRY, which is the spatial type of Oracle Spatial for all points, lines, and polygons. The boolean types are changed to ^CHAR(1), with a check constraint added to the field to restrict values to ‘Y’ (true) and ‘N’ (false), e.g. for the attribute decontamination of the table PatientCard the constraint is ^CHECK

(decontamination in (’Y’, ’N’)). The enumeration types are changed to

NUMBER(), and these numeric values will be used as codes that refer to the values of the enumeration list. The relationship tables InvolvedIn and JoinDMSUserToProcess are merged under the name UserInProcess. The relationship table ResponsibleFor is renamed to DeptResponsibe4Proc. The relationships are added for the dependent tables, e.g. Process. The primary key attributes added from the automatic generation were deleted and the identifier (class) attributes were declared as primary keys, e.g.

objectID is the primary key of table EventObject instead of eventObjectID, which is removed from the table. The primary keys were added in the depended tables. Then, the primary-foreign key relationships were added or corrected. We merged the

classes MeasurementTask and Measurement into one table named Measurement to be able to express constraints using attributes of both classes.

Figure 3.13: Oracle spatial data tables for ER dynamic information: PK shows primary key; FK foreign key, pfK a column that is primary as well as a foreign key, an arrow shows foreign key – primary key relation.

The UML diagram of Figure 3.13 is a visual model of the database schema. The corresponding Data Definition Language (DDL) statements will create the database schema in Oracle Spatial. These are the structures where the data can be stored. The

generation of DDL statements can also be done automatically from EA; a screenshot is shown in Figure 3.14.

Figure 3.14: Automatic generation of SQL scripts from the data tables.

The DDL script created by EA needs modifications. The automatic generation cannot handle new data types, neither a solution for enumeration lists. We added most of the constraints directly in the DDL scripts (and not in the UML table model, for not being practical, though possible). We created new data types for the patient card information, e.g. the Exposure data type is created by

CREATE TYPE EXPOSURE AS OBJECT ( level NUMBER(1),

radiologic NUMBER(1), biologic VARCHAR2(25), chemic_infect NUMBER(1), chemic_type NUMBER(1), chemic_name VARCHAR2(25) );

The creation of temporal and spatiotemporal data types is treated in Chapter 4.

All the enum types are implemented as (look-up) tables in Oracle. For example, the attribute process_type takes values from a look-up table Process_Type containing information for all processes. The table Process_Type is created from:

CREATE TABLE Process Type (

code NUMBER(2) CONSTRAINT PK_Process_Type PRIMARY KEY, value_EN VARCHAR2(70),

value_NL VARCHAR2(70) );

The table is filled with information for all the 25 processes. Values of process_type attribute are constrained to codes available in Process_Type

CREATE TABLE Process (

incidentID NUMBER(15) CONSTRAINT FK_Process_Incident REFERENCES RealIncident,

Process_type NUMBER(2) CONSTRAINT REF_Process REFERENCES Process_Type,

start_time TIMESTAMP, end_time TIMESTAMP,

CONSTRAINT PK_Process PRIMARY KEY (incidentID, process type)

);

The automatic generation of DDL scripts creates the primary and foreign key constraints with an ALTER TABLE statement after the creation of all the tables.

After modifying the DDL scripts we put the constraints within the CREATE TABLE statement.

Other constraints are added to control attributes of boolean type (Oracle tables do not support boolean type), a well as (pre-)conditions of the information. For example, explosion state LEL is a measurement from the form of Figure 3.5:

attribute task_exposionLEL contains values true/false deciding if this measurement should be performed, and attribute explosionLEL contains the measurement value in case the task decided that this measurement should be performed. This is checked through these constraints written in the declaration of

Measurement table:

task_explosionLEL CHAR(1) CONSTRAINT CHK_explosion_task CHECK (task_explosionLEL in (’Y’, ’N’)),

...

CONSTRAINT CHK_explosion CHECK

(task_explosionLEL <> ’Y’ OR explosionLEL IS NOT NULL),

The first declares task_exposionLEL attribute as having only values ’Y’ (true) and

’N’ (false); the second assures that if task_exposionLEL is set to true, then explosionLEL is not empty. Appendix A: Oracle scripts creating the database schema with temporal data at attribute level contains the complete scripts that create the database schema.