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.

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:

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.
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.

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");