You can picture a star join as a structure with many branches, in which each branch is directly related to one table in the middle. In Figure 6-1, Tables D, F, and E form a very simple star join. More commonly, Table F would be at the center of many tables, each of which is joined to Table F.
In a star join, multiple tables are related to one table at the center of the join, in a one-to-many relationship. The one table at the center of the join represents the “many” side of the relationship, while each of the tables around it represent the “one” side of the relationship. Each table on the “one” side holds a set of values with its own unique primary key. A foreign key in the table on the “many” side of the relationship relates that table to the primary key of the table on the “one” side of the relationship.
The “many” table at the center of the star is sometimes called the fact table. The “one” tables related to it are called the dimension tables.
In the sample database used throughout this book, the sales_order table contains three foreign keys, each of which is related to the primary key of another table.
You can create this table using the following commands:
CREATE TABLE "DBA"."sales_order"(
(
"id" integer NOT NULL,
"cust_id" integer NOT NULL
REFERENCES "DBA"."customer" ("id"),
"order_date" datetime NOT NULL,
"fin_code_id" char(2) NULL
REFERENCES "DBA"."fin_code" ("code"),
"region" char(7) NULL,
"sales_rep" integer NOT NULL
REFERENCES "DBA"."employee" ("emp_id"),
PRIMARY KEY ("id"),
);
As shown in the figure, the sales_order table is at the center of the star join. Each of its foreign key columns can contain many instances of the primary key it refers to. For example, if you enter:
SELECT sales_rep FROM sales_order WHERE sales_rep = 299
the results show 20 rows with 299 in the sales_rep column.
However, if you enter:
SELECT emp_id FROM employee WHERE emp_id = 299
the results show only one row with 299 in the emp_id column.
Query optimizations for all joins rely heavily on underlying
primary keys. They do not require foreign keys. However, you can
benefit from using foreign keys. Sybase IQ enforces foreign keys
if you set up your loads to check for primary key-foreign key relationships.
Sybase IQ does not support star-join style join indexes that use multiple join key columns for any given join.
For a true star join (that is, one in which none of the dimensions shares a join key with any other dimension), the IQ query optimizer allows a maximum of 24 dimension tables in a single clause. However, as the time required to process the query increases exponentially with the number of dimensions, performance degrades as you get close to this maximum.
To create a foreign key, see “Creating primary and foreign keys”. For other information on foreign keys, see “Declaring entity and referential integrity”.
