Business Intelligence Tutorial

Lesson 11. Defining keys on target tables

In this lesson, you will define primary and foreign keys on target tables to use later in a join. To do this lesson, you must have defined the dimension tables and fact table in Defining the rest of the star schema (optional).

In each target table, you will select a column that can be used to uniquely identify rows in that table. This will be the table's primary key. The column that you select as a primary key must have the following qualities:

• It must always have a value. The column for a primary key cannot contain null values.
• It must have unique values. Each value in the column must be different for each row in the table.
• Its values must be stable. A value should never change to another value.

For example, the CITY_ID column in the LOOKUP_MARKET table (created in Lesson 8, Defining data transformation and movement) is a good candidate for designation as a primary key. Because each city needs an identifier, no two cities can have the same identifier, and identifiers are unlikely to change.

Defining a primary key for a table is highly recommended because uniquely identifying each row speeds up row access.

You use foreign keys to define relationships between tables. In a star schema, a foreign key defines the relationship between the fact table and its associated dimension tables. The primary key of the dimension table has a corresponding foreign key in the fact table. The foreign key requires that all the values of a given column in the fact table also exist in the dimension table. For example, the CITY_ID column of the FACT_TABLE might have a foreign key defined on the CITY_ID column of the LOOKUP_MARKET dimension table. This means that a row cannot exist in the FACT_TABLE unless the CITY_ID exists in the LOOKUP_MARKET table.

In this lesson, you will define primary keys on the four target tables that you created in Lesson 8, Defining data transformation and movement: LOOKUP_MARKET, LOOKUP_TIME, LOOKUP_PRODUCT, and LOOKUP_SCENARIO. You will define corresponding foreign keys in the FACT_TABLE target table.

Defining a primary key

In this exercise, you will define a primary key for the LOOKUP_MARKET, LOOKUP_TIME, LOOKUP_PRODUCT, and LOOKUP_SCENARIO target tables.

To define the primary keys:

1. Go to the DB2 Control Center window.
2. Find the LOOKUP_MARKET table in the list of tables for the TUTWHS database. Right-click the table and click Alter.

   The Alter Table notebook opens.

3. Click the Primary Key tab. The Primary Key page opens.
4. In Available columns, select CITY_ID as the primary key.
5. Click > to move CITY_ID to the Primary key columns list.
6. Leave the Constraint name field empty, so that DB2 Universal Database will generate a constraint name for you. A primary key can be considered a constraint, because all values in the selected column must be unique.
   
   
7. Click OK to save your definitions.

Follow the same steps to define primary keys for the other target tables. Define:

• TIME_ID as the primary key for the LOOKUP_TIME table.
• PRODUCT_KEY as the primary key for the LOOKUP_PRODUCT table.
• SCENARIO_ID as the primary key for the LOOKUP_SCENARIO table.

Defining a foreign key

You need to define foreign keys for the relationships between the FACT_TABLE and the other target tables.

In this exercise, you will define a foreign key in the FACT_TABLE (dependent table) based on the primary key of the LOOKUP_MARKET table (parent table).

To define the foreign keys:

1. Find the FACT_TABLE table in the list of tables for the TUTWHS database. Right-click the table and click Alter.

   The Alter Table notebook opens.

2. Click the Foreign Keys tab. The Foreign Key page opens.
3. Click Add. The Add Foreign Key window opens.
4. In the Table schema field, type the user ID under which you created the warehouse database in Lesson 2, Creating a warehouse database.
5. In the Table name field, specify LOOKUP_MARKET, which is the parent table. The Primary key field displays the primary key for LOOKUP_MARKET.
6. In the Available column list, select CITY_ID as the column that you want defined as the foreign key.
7. Click > to move CITY_ID to the Foreign key list.

   Accept the defaults for the On delete and On update fields. For more information about these fields, see the online help.

8. Leave the Constraint name field empty, so that DB2 Universal Database will generate a constraint name for you. A foreign key can be considered a constraint, because for each value in the foreign key column of the dependent table, there is a row in the parent table with a matching value in its parent's primary key column.
   
   
9. Click OK to save your definitions.

Follow the same steps to define foreign keys for FACT_TABLE to the other target tables. Define:

• TIME_ID as a foreign key with the LOOKUP_TIME table as the parent.
• PRODUCT_KEY as a foreign key with the LOOKUP_PRODUCT table as the parent.
• SCENARIO_ID as a foreign key with the LOOKUP_SCENARIO table as the parent.

What you just did

In this lesson, you defined primary keys for the LOOKUP_MARKET, LOOKUP_TIME, LOOKUP_PRODUCT, and LOOKUP_SCENARIO target tables. You defined foreign keys for the FACT_TABLE that correspond to the primary keys. In Lesson 16, Creating a star schema from within the Data Warehouse Center, you will use these keys to join the tables in the star schema.