The Data Warehouse Development Life Cycle
Oracle Data Warehouse Design
Massive De-normalization: STAR
Schema Design
CREATE TABLE TEMP AS
SELECT (QUANTITY.quantity_sold * ITEM.list_price) line_total
FROM QUANTITY, ITEM
SEE CODE DEPOT FOR FULL SCRIPT
SELECT sum(line_total) FROM TEMP;
Also, note that the STATE-CITY
table hierarchyas shown in Figure 4.3 is very
deliberate. In order to be truly in third normal form, we do not
allow any redundant information (except, of course, foreign keys).
Given that this example has been fully normalized into five tables,
a query that would appear very simple to the end user would have
relatively complex SQL. For example, the SQL to calculate the sum of
all orders in the Western region might look very complex, involving
a five-way table join as follows:
CREATE TABLE TEMP AS
SELECT (QUANTITY.quantity_sold * ITEM.list_price) line_total
FROM QUANTITY, ITEM, CUSTOMER, CITY, STATE
SEE CODE DEPOT FOR FULL SCRIPT
STATE.region_name = 'WEST';
In the real world, of course, we
would introduce enough redundancy to eliminate the CITY, and
STATE tables. But, the point is clear:
A manager who wants to analyze a series of complete order totals
would need to do a huge amount of realtime computation. Here, we
arrive at the basic tradeoff: If we want true freedom from redundant
data, we must pay the price at query time.
