Conversely, the recursive association can be applied to any item to see its participation in other items. For example, the uses of grease can be seen by running an implosive query, as shown in Table 1.3.

These examples may seem simplistic, but many database systems have items with numerous subassemblies. Recursions may cascade dozens of levels down in a hierarchy. Consider the design of a database that manages fast food items (see Figure 1.17). All non-food items have a class construct, which hold all data and behaviors. This class construct is an example of an abstract class, with no object of non_food_part created. But non_food_part does include ordering behaviors and the names of suppliers. These data items cascade to lower-level classes, specifically the toy_part and paper_part objects. The toy_part class is not only next in the class hierarchy, it also has data and behaviors. Contrasted with the non_food_part abstract class, toy_part is a concrete class.

Figure 1.17  A recursive many-to-many relationships with the addition of an ISA Hierarchy.

Clearly, we compound the problem of recursive relationships by adding this additional construct—namely, a class hierarchy. Unfortunately, these types of challenges are very common. While it is true that “parts are parts,” the different parts have subtle variations, leading to different data items depending on part type. For example, a food-related part might have a shelf_life column, but that column does not apply to a non-food part.

With an understanding of the nature of recursive relationships, the question becomes one of implementation: What is the best way to represent a recursive relationship in Oracle and navigate the structure?

The following Oracle table definitions describe the tables for the part-component example:

CREATE table part(
part_nbr     number,
part_name    varchar2(10),
part_desc    varchar2(10),
qty_on_hand  number);
CREATE table component (
has_part     number,
is_a_part    number,
qty          number);

Look closely at the component example. Both the has_part and is_a_part fields are foreign keys for the part_nbr field in the part table. Therefore, the component table is all keyed except for the qty field, which tells how many parts belong in an assembly. Look at the following SQL code that is required to display all components in a Big_Meal:

SELECT part_name
FROM part, component
WHERE
has_part = 'HAPPY MEAL'
AND
part.part_nbr = COMPONENT.has_part;

This type of Oracle SQL query requires joining the table against itself. Unfortunately, since all items are of the same type (namely, part), no real substitute exists for this type of data relationship.

STAR Schema Design

The STAR schema design was first introduced by Dr. Ralph Kimball as an alternative database design for data warehouses. The name STAR comes directly from the design form, where a large FACT table resides at the center of the model surrounded by various “points” or reference tables. The basic principle behind the STAR query schema is the introduction of highly redundant data for high performance. A more exhaustive discussion of STAR schema design is included in Chapter 10, Tuning Oracle Data Warehouse And OLAP Applications.

Returning to the customer-order E/R model in Figure 1.9, we see an illustration of a standard Third Normal Form database used to represent the sales of items. Since redundant information is not an issue, salient data (such as the total for an order) is computed from the items comprising the order. In this Third Normal Form database, users need a list of line items to multiply the quantity ordered by the price for all items belonging in order 123. An intermediate table called TEMP holds the result list, shown in the following example:

CREATE table TEMP as
SELECT (QUANTITY.quantity_sold * ITEM.list_price) line_total
FROM QUANTITY, ITEM
WHERE
QUANTITY.order_nbr = 123
AND
QUANTITY.item_nbr = ITEM.item_nbr;
SELECT sum(line_total) from TEMP;

---

NOTE:  The state-city-customer hierarchy in Figure 1.9 is very deliberate. To be truly in the Third Normal Form, no redundant information is possible. As such, a user’s seemingly simple query is complex to the system. For example, the SQL calculating the sum of all orders for the western region looks very complex and involves the five-way table join:

---

CREATE table TEMP as
SELECT (QUANTITY.quantity_sold * ITEM.list_price) line_total
FROM QUANTITY, ITEM, CUSTOMER, CITY, STATE
WHERE
QUANTITY.item_nbr = ITEM.item_nbr    /* join QUANTITY and ITEM */
AND
ITEM.item_nbr = CUSTOMER.cust_nbr    /* join ITEM and CUSTOMER */
AND
CUSTOMER.cust_city = CITY.cust_city /* join CUSTOMER and CITY */
AND
CITY.state_name = STATE.state_name  /* join CITY and STATE */
AND
STATE.region_name = 'WEST';

In reality, sufficient redundancy eliminates the city and state tables. The point is clear: A manager who is analyzing a series of complete order totals requires a huge amount of realtime computation. This process arrives at the basic tradeoff. For true freedom from redundant data, query time demands a price.

In practice, the Third Normal Form database generally evolves into a STAR schema as you create a FACT table to hold the quantity for each item sold (see Figure 1.18).

Figure 1.18  The STAR query E/R model.

At first glance, it’s hard to believe this is the same data as the normalized database. The new FACT table contains one row for each item on each order, resulting in a tremendous amount of redundant key information in the FACT table. It should be obvious that the STAR query schema requires far more disk space than a Third Normal Form database. Also, the STAR schema is most likely a read-only database because of the widespread redundancy introduced into the model. Finally, the widespread redundancy makes updating difficult (if not impossible) for the STAR schema.