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 following example, the SalesOrders 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 GROUPO.SalesOrders ( ID NUMERIC (4) NOT NULL IQ UNIQUE (648), CustomerID INTEGER NOT NULL IQ UNIQUE (109), OrderDate date NOT NULL IQ UNIQUE (376), FinancialCode CHAR (2) NULL IQ UNIQUE (1), Region CHAR (7) NULL IQ UNIQUE (5), SalesRepresentative INTEGER NOT NULL IQ UNIQUE (11) ); COMMENT ON TABLE GROUPO.SalesOrders is 'sales orders that customers have submitted to the sporting goods company'; ALTER TABLE GROUPO.SalesOrders ADD FOREIGN KEY FK_CustomerID_ID (CustomerID) REFERENCES GROUPO.Customers (ID) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE GROUPO.SalesOrders ADD FOREIGN KEY FK_FinancialCode_Code (FinancialCode) REFERENCES GROUPO.FinancialCodes (Code) ON DELETE RESTRICT ON UPDATE RESTRICT; ALTER TABLE GROUPO.SalesOrders ADD FOREIGN KEY FK_SalesRepresentative_EmployeeID (SalesRepresentative) REFERENCES GROUPO.Employees (EmployeeID) ON DELETE RESTRICT ON UPDATE RESTRICT;
As shown in the figure, the Salesorders 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 SalesRepresentative FROM SalesOrders WHERE SalesRepresentative = 299
the results show 114 rows with 299 in the SalesRepresentative column.
However, if you enter:
SELECT EmployeeID FROM Employees WHERE EmployeeID = 299
the results show only one row with 299 in the EmployeeID 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”.
