by Burleson Consulting
The Data Warehouse Development Life Cycle
The Role Of Functional Decomposition
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
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
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.
This is an excerpt from "High Performance
||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.
can buy it directly from the publisher and save 30%, and get instant
access to the code depot of Oracle tuning scripts.
Expert Remote DBA
BC is America's oldest and largest Remote DBA Oracle support
provider. Get real Remote DBA experts, call
BC Remote DBA today.