A conceptual information model of the business is a great asset, as well as a solid investment due to it being independent of technology. Since IT undergoes rapid changes, the only truly long-living asset is the information model.

IT requires more than just software specifications; it also needs to build software and maintain system integrations. Since CaseTalk uses a fundamental way of handling information, it can mathematically transform the conceptual model to other artifacts. CaseTalk allows visualizing the model in traditional FCO-IM diagrams, but it also supports UML Class diagrams, Relational Diagrams, or generating Database Scripts.

This article briefly shows some of the steps required to perform model to model transformation for Database Script generation, while still maintaining all fact expressions.

Relational Model

When transforming a information model into a relational model, information is simply grouped, reduced, and lexicalized. All of this is executed with the click of a button, while at the same time maintaining all syntactic meaning. This means that business users can still validate the transformed model from database scripts since it maintains the documentation that is directly based upon the facts stated by business users. This transformation in CaseTalk is called GLR (Group Lexicalize Reduce) and it combines three different transformation steps into a single process.

Verbalizations

The business has expressed the following facts to be used as a small information model:

[Emergency Exits]
"There are 2 exits on floor number 1."
"<Emergency Exits> is unique on <Floor>."
"<Floor> must have a <Emergency Exits>."

[Equipment]
"Room 2.1 is equipped with a BB."
"<Equipment> is unique on <Room> and <Facility>."

[Facility]
"We have BB's available."
"We have LCD's available."
"<Facility> is unique on <equipment code>."
"<Equipment> is unique on <Room> and <Facility>."

[Room]
"There is a room 1.2."
"There is a room 2.1."
"There is a room 1.1."
"<Room> is unique on <Floor> and <room number>."
"<Equipment> is unique on <Room> and <Facility>."
"<Room Name> is unique on <Room>."
"<Room> must have a <Room Name>."

[Room Name]
"Room 1.1 is called "Blue Room"."
"<Room Name> is unique on <Room>."
"<Room Name> is unique on <Name>."
"<Room> must have a <Room Name>."

Diagram

The graphical visualization of this information model is presented here. It is used as the basis for explaining the transformation steps in the next few tabs.

rooms

Group

The transformation step called Grouping is about removing roles and grouping the remaining roles inside the Object Type. When looking at the diagram from the previous tab, both role 7 and role 10 are considered groupable. After performing this grouping the model is transformed and can be visualized with the following diagram:

rooms grouped

Note that the expressions are transformed as well. The Fact Type Expression F5 is moved to Floor. The Object Type Expression O4 is swallowed by the newly moved F5. This does not change the original semantic meaning of the expressions.

Lexicalize

Lexicalizing is mandatory to create actual database scripts. Every column must have a database domain definition. Therefore, every role in the model must be reference a Label Type. The following diagram shows the result of lexicalization after grouping. rooms gl
Note that the relationships between Object Types are now removed, and the information model shows these as subset constraints. Subset constraints are visualized as role-to-role references to keep the population consistent with the original models. They work similarly to foreign key definitions among database tables.

Reduce

Note for the observant readers: We played foul by allowing the fact type expression ‘F3’ to be removed before showing the effects of reduction.
ROOMS GLR
Note for the observant readers, we played faul by allowing the fact type expression F3 to be removed before showing the effects of reduction.

Script

Performing Group, Lexicalize, and Reduce greatly transforms the conceptual model while still maintaining the semantic meaning. Take a look at the database script generated by CaseTalk: As you can see, the comments contain all the expression types originally entered in the model. This way, the database designers are still able to understand the tables, columns, and foreign keys in business terms.

-- Table "EQUIPMENT"
--
-- "Room <floor number>.<room number> is equipped with a <equipment code>."
--
CREATE TABLE "EQUIPMENT" (
"FLOOR" "FLOOR_NUMBER" NOT NULL, -- Room/Floor
"ROOM_NUMBER" "ROOM_NUMBER" NOT NULL, -- Room/room number
"FACILITY" "EQUIPMENT_CODE" NOT NULL, -- Facility
PRIMARY KEY ("FLOOR", "ROOM_NUMBER", "FACILITY")
);

-- Table "FLOOR"
--
-- "There are <Exists No> exits on floor number <floor number>."
--
CREATE TABLE "FLOOR" (
"FLOOR_NUMBER" "FLOOR_NUMBER" NOT NULL, -- floor number
"EMERGENCY_EXITS" "EXISTS_NO" NOT NULL, -- Emergency Exits
PRIMARY KEY ("FLOOR_NUMBER")
);

-- Table "ROOM"
--
-- "There is a room <floor number>.<room number>."
-- "Room <floor number>.<room number> is called "<Name>"."
--
CREATE TABLE "ROOM" (
"FLOOR" "FLOOR_NUMBER" NOT NULL, -- Floor
"ROOM_NUMBER" "ROOM_NUMBER" NOT NULL, -- room number
"ROOM_NAME" "NAME" NOT NULL, -- Room Name
UNIQUE ("ROOM_NAME"),
PRIMARY KEY ("FLOOR", "ROOM_NUMBER")
);

ALTER TABLE "ROOM"
ADD FOREIGN KEY ("FLOOR")
REFERENCES "FLOOR" ("FLOOR_NUMBER");

ALTER TABLE "EQUIPMENT"
ADD FOREIGN KEY ("FLOOR", "ROOM_NUMBER")
REFERENCES "ROOM" ("FLOOR", "ROOM_NUMBER");

Other conversions

Besides ER, there are many other implementation-specific modeling environments (i.e. UML, Data Warehouse, and XML Schemas).

All these implementation models can be transformed from FCO-IM conceptual models. The Case-Tool already possesses built-in conversions for ER, as well as having export plugins for Oracle; Ms Sql Server; Interbase/Firebird; MsAccess; and many more.

For conversions toward other tools, external conversion tools are available and work together with the Case-Tool in order to provide a wider range of platforms to export to.

The (former) StarBridge tool allows IT personnel to convert the FCO-IM repository into a Data Warehouse model while still maintaining full expressions that automatically document the new dimensional models. This is a powerful tool that has a nine-step decision path, leading automatically to the desired dimensional model.

Add comment