Spatial Data Warehouses - Data Warehouse Systems: Design and Implementation - page 442

Rule 2S: Afact F is mapped to a table T F that includes as attributes all
measures of the fact. Further, a surrogate key may be added to the table.
Spatial measures must be mapped to attributes having a spatial type. In
addition, if the fact has an associated topological constraint, a trigger may
be added to ensure that the constraint is satisfied for all fact members.
Note that additional attributes will be added to this table when mapping
relationships using Rule 3S below.
A relationship is mapped using Rule 3 given in Sect. 5.3 . This rule is
extended below for coping with spatial features:
Rule 3S: A relationship between either a fact F and a dimension level L or
between dimension levels L P and L C (standing for the parent and child
levels, respectively) can be mapped in three different ways, depending on
its cardinalities:
Rule 3a: If the relationship is one-to-one, the table corresponding to the
fact ( T F ) or to the child level ( T C ) is extended with all the attributes of
the dimension level or the parent level, respectively. Spatial attributes
must be mapped to attributes having a spatial type.
Rule 3b: If the relationship is one-to-many, the table corresponding to
the fact ( T F ) or to the child level ( T C ) is extended with the surrogate
key of the table corresponding to the dimension level ( T L ) or the parent
level ( T P ), respectively, that is, there is a foreign key in the fact or child
table pointing to the other table.
Rule 3c: If the relationship is many-to-many, a new table T B (standing
for bridge table) is created that contains as attributes the surrogate keys
of the tables corresponding to the fact ( T F ) and the dimension level ( T L )
or the parent ( T P ) and child levels ( T C ), respectively. If the relationship
has a distributing attribute, an additional attribute is added to the table
to store this information.
Further, if the relationship has an associated topological constraint, a
trigger may be added to ensure that the constraint is satisfied by all
instances of the relationship.
For example, applying the above rules to the spatial fact Maintenance
given in Fig. 11.6 will result in a table that contains the surrogate keys of
the four dimensions Segment , RoadCoating , County ,and Time ,aswellasthe
corresponding referential integrity constraints. Further, the table contains
attributes for the measures Length and CommonArea , where the latter is a
spatial attribute. The table can be created as follows:
CREATE TABLE Maintenance (
SegmentKey INTEGER NOT NULL,
RoadCoatingKey INTEGER NOT NULL,
CountyKey INTEGER NOT NULL,
TimeKey INTEGER NOT NULL,
Length INTEGER NOT NULL,