4 - Adding Keys and Relations

Top  Previous  Next

 

Now that all the tables are defined, we can add keys then specify the table relationships. You already have defined the keys for the two tables you created in the GSLesson application in the Getting Started lesson. In this chapter, we’ll define keys for the remaining tables.

 

Starting Point:

The LCLESSON.DCT should be open.

 

Examining the Keys for the Orders Table

The columns in the Orders table that relate to other tables in the database are the OrderNumber and CustNumber columns.

 

 

The OrderNumber column relates to the Detail table.

There should be no duplicate or null order numbers in the Orders table; this is a primary key.

There may be multiple Detail rows for a single matching Order Number. Therefore, this is a One to Many relationship—the Orders table is the "Parent" of the Detail table.

 

The CustNumber column relates to the Customer table.

There will be duplicate values in the CustNumber column that relate to rows in the Customers table. The key we define in the Orders table is a foreign key. The Customers table key does not allow duplicates and nulls, and was defined as the primary key for that table.

Multiple Order rows can exist for each Customer, making this a Many to One relationship—the Orders table is the "Child" of the Customers table.

 

The Orders table was imported in the Getting Started lesson, and there are two keys already defined that satisfy the above requirements.

 

Defining Keys for the Detail Table

The columns in the Detail table that relate to other tables in the database are the ProdNumber and OrderNumber columns.

 

 

The OrderNumber column relates to the Orders table.

There will be duplicate values in the OrderNumber column that relate to rows in the Orders table. The key we define in the Detail table is another foreign key. The Orders table key does not allow duplicates and nulls, and was defined as a primary key.

There may be more than one Detail row for a single matching Order Number. Therefore, this is a Many to One relationship, with the Detail table the "Child" of the Orders table.

 

The ProdNumber column relates to the Products table.

There will be duplicate values in the ProdNumber column for the rows in the Detail table. There may be more than one Detail row containing a single Product Number. Therefore, this is another Many to One relationship, with the Detail table the "Child" of the Product table.

 

Define the First Foreign Key

Define ProdNumberKey so that there may be duplicate ProdNumber values in this table.

1.

Highlight the Detail table in the DCT Explorer list.

2.

In the Keys view, press the Add button.

 

 

3.

Type ProdNumberKey in the Label entry.

4.

Select the Columns tab.

5.

Press the Add button.

6.

Select ProdNumber then press the Select button.

7.

Press the Cancel button in the Select a Column dialog, and then press OK to close the Key Properties dialog.

A blank Key Properties dialog appears, ready for you to specify another key.

 

Define the Second Foreign Key

1.

Type OrderNumberKey in the Label entry.

2.

Select the Columns tab.

3.

Press the Add button.

4.

Select OrderNumber then press the Select button.

5.

Press the Cancel button in the Select a Column dialog.

6.

Press the OK button to close the Key Properties dialog.

7.

Press the Cancel button to close the blank Key Properties dialog.

8.

Choose File Save, or press the Save button on the tool bar.

 

Defining Keys for the Products Table

Only one column in the Products table relates to another table in the database: the ProdNumber column.

 

The ProdNumber column relates to the Detail table.

 

 

There should be no duplicate or null order numbers in the Products table; this is a primary key.

For each ProdNumber in the row there can be many Detail rows. This is a One to Many relationship with the Products table a "Parent" to the Detail table.

 

Create the Primary Key

Name the Key

1.

Highlight the Products table in the DCT Explorer list.

2.

In the Keys view, press the Add  button.

3.

Type ProdNumberKey in the Label entry.

4.

In the Attributes group, check the Require Unique Value box, then check the Primary Key box.

5.

Check the Auto Number box.

6.

Select the Columns tab.

7.

Press the Add button.

8.

In the Select a Column window, select ProdNumber then press the Select button.

9.

Press the Cancel button to close the Select a Column window.

10.

Press the OK button to close the Key Properties dialog.

A blank Key Properties dialog appears, ready for you to specify another key.

 

Define an Alphabetical Key

Users will probably want to see the list of Products in alphabetical order, so we’ll add a key for that.

1.

Type ProdDescKey in the Label entry.

2.

Select the Columns tab.

3.

Press the Add button.

4.

Select ProdDesc then press the Select button.

5.

Press the Cancel button to close the Select a Column window.

6.

Press the OK button to close the Key Properties dialog.

7.

Press the Cancel button to close the blank Key Properties dialog.

8.

Choose File  Save, or press the Save button on the tool bar.

 

Defining a Key for the Phones Table

The column in the Phones table that relate to the Customer table in the database is the CustNumber column.

 

 

The CustNumber column relates to the Customer table.

There will be duplicate values in the CustNumber column that relate to rows in the Customer table. The key we define in the Phones table is another foreign key. The Customer table key does not allow duplicates and nulls, and was defined as a primary key.

There may be more than one Phones row for a single matching Customer Number. Therefore, this is a Many to One relationship, with the Phones table the "Child" of the Customer table.

 

Define the Foreign Key

Define CustNumberKey so that there may be duplicate CustNumber values in this table.

1.

Highlight the Phones table in the DCT Explorer list.

2.

In the Keys view, press the Add  button.

3.

Type CustNumberKey in the Label entry.

4.

Select the Columns tab.

5.

Press the Add button.

6.

Select CustNumber then press the Select button.

7.

Press the Cancel button to close the Select a Column window.

8.

Press the OK button to close the Key Properties dialog.

9.

Press the Cancel button to close the blank Key Properties dialog.

10.

Choose File Save, or press the Save button on the tool bar.

 

Defining Table Relationships

The relationships for the Customer, Orders, and States tables were defined in the Getting Started lessons. Refer back to that section in the Getting Started if you would like to review those definitions.

 

Defining Relationships for the Phones Table

Now that all the keys are defined, we can add the relations. Once you have defined relationships, you can add Validity Checks for the columns that should only contain values that exist in another table. These are the last steps to completing the data dictionary.

CustNumberKey relates the Phones table to the Customer table in a Many to One relationship.

 

Define the relationship

1.

Highlight the Phones table in the DCT Explorer if not already highlighted.

2.

In the lower right pane, press the Add button located just above the Relations list. The Relationship Properties dialog appears:

 

 

3.

Choose MANY:1 from the Type dropdown list.

Notice that, when you chose MANY:1, the prompts for the Primary Key and Foreign Key columns switched places. This happens because we are now defining this relationship from the "Child" table’s viewpoint; the opposite side of the relationship to what we did previously. A Primary Key is always in the Parent table, while a Foreign Key is always in the Child table.

4.

Choose Pho:CustNumberKey from the Foreign Key dropdown list.

5.

Choose Customer from the Related Table dropdown list.

This establishes the Customer table as the "Parent" in this relationship.

6.

Choose CUS:KeyCustNumber from the Primary Key dropdown list.

7.

Press the Map by Name button.

 

Set up the Referential Integrity constraints

1.

Choose Cascade from the On Update dropdown list.

Although we are defining this relationship from the "Child" table’s viewpoint, the Referential Integrity constraints are still set on the "Parent" table actions.

2.

Choose Cascade from the On Delete dropdown list.

3.

Press the OK button.

4.

Press the Cancel button to close the new Relationship Properties window.

5.

Choose File  Save, or press the Save button on the tool bar.

 

 

Defining Relationships for the Detail Table

Each time you define a relationship in the Dictionary Editor, you define it for both tables at the same time.

The relationships for the Detail table:

ProdNumberKey relates the Detail table to the Products table in a Many to One relationship.

OrderNumberKey relates the Orders table to the Detail table in a One to Many relationship. From the Detail table, you can also look at it as "Many detail records relate to a single Order record".

 

Define the Detail-Product relationship

1.

Highlight the Detail table in the DCT Explorer list.

2.

In the lower right pane, press the Add button located just above the Relations list. The Relationship Properties dialog appears:

3.

Choose MANY:1 from the Type dropdown list.

4.

Choose DTL:ProdNumberKey from the Foreign Key dropdown list.

5.

Choose Products from the Related Table dropdown list.

6.

Choose PRD:ProdNumberKey from the Primary Key dropdown list.

7.

Press the Map by Name button.

 

Set up the Referential Integrity constraints

1.

Choose Restrict from the On Update dropdown list.

We won’t allow any changes to the product numbers.

2.

Choose Restrict from the On Delete dropdown list.

3.

Press the OK button.

4.

Press the Cancel button to close the new Relationship Properties window.

5.

Choose File  Save, or press the Save button on the tool bar.

 

Define the Detail-Order relationship

1.

Highlight the Detail table in the Tables list.

2.

In the lower right pane, press the Add button located just above the Relations list. The Relationship Properties dialog appears:

3.

Choose MANY:1 from the Type dropdown list.

4.

Choose DTL:OrderNumberKey from the Foreign Key dropdown list.

5.

Choose Orders from the Related Table dropdown list.

6.

Choose ORD:KeyOrderNumber from the Primary Key dropdown list.

7.

Press the Map by Name button.

 

Set up the Referential Integrity constraints

1.

Choose Restrict from the On Update dropdown list.

2.

We won’t allow any changes to the product numbers.

3.

Choose Restrict from the On Delete dropdown list.

4.

Press the OK button.

5.

Press the Cancel button to close the new Relationship Properties window.

6.

Choose File  Save, or press the Save button on the tool bar.

 

Defining Relationship-Dependent Validity Checks

Now that all the table relationships are defined, we can set the Validity Checks for two columns that we expect to use on update forms.

<

When entering a new Orders row, we can specify that the CustNumber must match an existing row in the Customer table.