An Naive SQL Implementation

In this study, we limit the cardinality of drug combinations to at most three, because larger combinations are rarely occurred and hard to perceive. The basic task flow for computing all type-1 and type-2 MDC cubes is shown in Figure 4-5. There are four steps in this flow: (1) The base table (BT) is undergone a self-join to generate an intermediate table namely IT2-1 to store all reports involving drug combinations of length 2; (2) The base table BT and intermediate table IT2-1 are joined to generate another intermediate table, namely IT3-1, to store reports involving drug combinations of length 3; (3) The base table BT and the intermediate tables IT2-1 and IT3-1 are used to generate all type-1 MDC cubes; and (4) The

type-1 MDC cubes are processed to generate all type-2 MDC cubes. The structure of the intermediate tables and the concept of steps 1 and 2 are depicted in Figure 4-6. In what follows, we describe the details of each step.

Table 4-2. An example base table conforming to AERS data format.

ISR Age Gender Weight Country Drug PT

1 a1 g1 w1 c1 d1 s1

1 a1 g1 w1 c1 d2 s2

1 a1 g1 w1 c1 d3 s1

1 a1 g1 w1 c1 d4 s2

1 a1 g1 w1 c1 d1 s2

1 a1 g1 w1 c1 d2 s1

1 a1 g1 w1 c1 d3 s2

1 a1 g1 w1 c1 d4 s1

2 a2 g1 w2 c2 d3 s1

2 a2 g1 w2 c2 d4 s3

2 a2 g1 w2 c2 d5 s1

2 a2 g1 w2 c2 d3 s3

2 a2 g1 w2 c2 d4 s1

2 a2 g1 w2 c2 d5 s3

3 a1 g2 w3 c3 d2 s1

3 a1 g2 w3 c3 d3 s2

3 a1 g2 w3 c3 d2 s2

3 a1 g2 w3 c3 d3 s1

Figure 4-5. The basic task flow for computing MDC cubes.

Figure 4-6. The structure of the intermediate tables.

Step 1. Create intermediate table IT2-1

In this step, the base table is undergone a self-join operation to generate IT2-1, as described in the following SQL procedure.

SELECT T1.*, T2.Drug as Drug2 INTO IT2-1

FROM BT as T1 JOIN BT as T2

ON d1.ISR = T2.ISR AND T1.Drug < cast T2.Drug GROUP BY T1.*, T2.Drug

Step 2. Create intermediate table IT3-1

We executed the following SQL procedure to join tables BT and IT2-1 to generate IT3-1.

SELECT T1.*, T2.Drug as Drug3 INTO IT3-1

FROM IT2-1 as T1 JOIN BT as T2

ON T1.ISR = T2.ISR AND T2.Drug > T1.Drug2 GROUP BY T1.*, T2.Drug

Step 3. Create type-1 MDC Cubes

In this step, we perform the following SQL procedures to generate the four different groups of type-1 MDC cubes, where ** denotes any subset of attributes in {Age, Weight, Gender, Country}.

// Create MC[**, Drug, PT] MDC cubes SELECT {**}, Drug, PT, count (*) AS count INTO **_ Drug_ PT

FROM BT

GROUP BY **, Drug, PT INSERT INTO **_ Drug_ PT

SELECT {**}, (Drug+ Drug2) AS Drug, PT, count (*) AS count FROM IT2-1

GROUP BY **, Drug, Drug2, PT

INSERT INTO **_ Drug_ PT

SELECT {**}, (Drug+ Drug2 +Drug3) AS Drug, PT, count (*) AS count INTO **_ Drug_ PT

FROM IT3-1

GROUP BY **, Drug, Drug2, Drug3, PT

// Create MC[**, Drug] MDC cubes SELECT {**}, Drug, count (*) AS count INTO **_ Drug

FROM BT

GROUP BY **, Drug

INSERT INTO **_ Drug

SELECT {**}, (Drug+ Drug2) AS Drug, count (*) AS count FROM IT2-1

GROUP BY **, Drug, Drug2

INSERT INTO **_ Drug

SELECT {**}, (Drug+ Drug2 +Drug3) AS Drug, count (*) AS count INTO **_ Drug

FROM IT3-1

GROUP BY **, Drug, Drug2, Drug3

// Create MC[**, PT] MDC cubes

INTO **_PT FROM BT

GROUP BY **, PT

// Create MC[**] MDC cubes SELECT {**}, count (*) AS count INTO **

FROM BT GROUP BY **

Step4. Creating type-2 MDC Cubes

Recall that the type-2 MDC cubes saves the counts a, b, c and d in each cell, which indeed can be computed by aggregating other type-1 MDC cubes. To this end, we define and execute the following SQL procedures to create b, c and d counts. Note that we do not need to count

“a” because it is stored in type-1 MDC cubes with attributes of {**, Drug, PT}.

// Create count a

SELECT **, Drug, count AS a INTO **_ Drug_ PT_type2 FROM **_ Drug_ PT

// Create count b

INSERT INTO **_ Drug_ PT_type2 SELECT (T2.count – T1.count) AS b

FROM **_Drug_PT AS T1, **_Drug AS T2

WHERE T1.** = T2.** AND T1.Drug = T2.Drug

// Create count c

INSERT INTO **_ Drug_ PT_type2 SELECT (T2.count – T1.count) AS c FROM **_Drug_PT AS T1, **_PT AS T2 WHERE T1. ** = T2. ** AND T1.PT = T2.PT

// Create count d

INSERT INTO **_ Drug_ PT_type2 SELECT (T1.count –T2.count- b-c) AS d FROM ** AS T1, **_Drug_PT AS T2 WHERE T1. ** = T2. **

Example 4-1:

In this example, we will use the base table BT in Table 4-3, which is based on Table 4-1, to create the corresponding type-1 and type-2 MDC cubes.

First, the result after Step 1: Creating intermediate table IT2-1 is shown in Table 4-4.

Note that there is no transaction with ISR = 5 because of single PT. The result after Step 2:

Creating intermediate table IT3-1 is shown in Table 4-5, which consists of only one transaction with ISR = 1.

Next, Step 3: Creating type-1 MDC Cubes is executed, generating a “count” field and using the “string” data type to realize the multivalued Drug dimension. For simplicity and space limitation, we only show the results with Gender = g1. All of the four groups of type-1

MDC cubes generated from the BT, IT2-1 and IT3-1, are shown in Tables 4-6, 4-7, 4-8, and 4-9.

Table 4-3. The relational BT derived from Table 4-1.

ISR Gender Drug PT 1 g1 d1 s1

Table 4-4. The resulting IT2-1 generated from Table 4-3.

ISR Gender Drug Drug2 PT 1 g1 d1 d2 s1

Table 4-5. The resulting IT3-1 generated from Table 4-3.

ISR Gender Drug Drug2 Drug3 PT 1 g1 d1 d2 d3 s1

Table 4-6. Part of the corresponding type-1 MDC cube MC[Gender, Drug, PT]

generated from Table 4-3, with Gender = g1.

Gender Drug PT count

Table 4-7. Part of the type-1 MDC cube MC[Gender, Drug] generated from Table 4-3 with Gender = g1.

Table 4-8. Part of the type-1 MDC cube MC[Gender, PT] generated from Table 4-3 with Gender = g1.

Gender PT count

g1 s1 3 g1 s2 1 g1 s3 1

Table 4-9. Part of the type-1 MDC cube MC[Gender] generated from Table 4-3 with Gender = g1.

Gender count

g1 3

Finally, the type-2 MDC cubes are generated by executing Step 4, primarily focusing on computing the count values of b, c and d. For simplicity, let us consider the rule R2. Its corresponding value for count a is immediately available from Table 4-6, and we have a = 1.

i.e., the cell [g1, (d1, d2), s1, count = 1]. Next, by reading the corresponding cell [g1, (d1, d2)]

in Table 4-7 we obtain the value for count b, that is count[g1, (d1, d2)] − a = 1 − 1 = 0.

Similarly, from Table 4-8, we obtain the value for count c, that is, count[g1, s1] − a = 3 − 1 = 2, and from Table 4-9, we obtain the value for count d, that is, count[g1] – a − b – c = 3 – 1 – 0 – 2 = 0. The final resulting count values for measuring rule R3 are a = 1, b = 0, c = 2 and d = 0, which is saved in the type-2 MDC cube MC[Gender, Drug, PT], as shown in Table 4-10.

Table 4-10. Part of the type-2 MDC cube MC[Gender, Drug, PT] generated from Table 4-3 with Gender = g1.

Gender Drug PT a b c d

g1 d1, d2 s1 1 0 2 0