Oracle Tips  by Burleson Consulting

The Data Warehouse Development Life Cycle

The Role Of Functional Decomposition
PLACE_ORDER (
CHECK_CREDIT(SELECT on CUSTOMER)
ADD_ORDER(INSERT on ORDER)
CHECK_INVENTORY_LEVEL(SELECT on ITEM)
DECREMENT_INVENTORY(UPDATE on ITEM)
ADD_LINE_ITEM(INSERT on LINE_ITEM)
)

Figure 3.4 A Level 1 data flow diagram.

While this level of decomposing is fine for traditional systems analysis, it is better to continue to decompose the place_order behavior in relational data warehouses. In a relational data warehouse, place_order would be divided into its sub-processes, namely check_credit, add_order, check_inventory_level, decrement_inventory, and add_line_item (see Figure 3.5).

Figure 3.5 A decomposed data flow diagram.

Each of the sub-processes within the place_order behavior will have its own process logic specifications, and each sub-process can be encapsulated into an Oracle stored procedure or trigger. The behavior place_order is now decomposed into its sub-behaviors, as shown by the mini-spec in Listing 3.2.

Listing 3.2 Mini-spec for a Level 2 DFD.

IF TOTAL_AMT > 1000
Check CREDIT_RATING in CUSTOMER
IF CREDIT_RATING = 'BAD' then reject order
ELSE Store ORDER RECORD
end if

MINI-SPEC for CHECK_INVENTORY

FOR (each item on the order)

-- compare QTY_ORDERED in ORDER with QTY_ON_HAND in ITEM
IF QTY_ON_HAND < QTY_ORDERED
Remove item from order
Prepare backorder slip

NEXT ITEM

MINI-SPEC for ADD_LINE_ITEM

FOR (each item on the order which is in stock)

-- Add the item to the order
Subtract QTY_ORDERED from QTY_ON_HAND.
Move QTY_ORDERED to QTY in LINE_ITEM.
Store LINE_ITEM record.

NEXT ITEM

If you like Oracle tuning, you may enjoy the book Oracle Tuning: The Definitive Reference , with over 900 pages of BC's favorite tuning tips & scripts.  You can buy it directly from the publisher and save 30%, and get instant access to the code depot of Oracle tuning scripts.