Lesson 3: Linking your tables

Lesson 3 will show you how to use the Visual Database Designer to link tables together and to create referential integrity relationships. You will also be introduced to the Data Model Designer.

In this lesson, you will

  create a visual model of your database based on three tables
  
  set referential integrity between your tables
  
  use the Data Model Designer to create two data models
  
You'll need about 15 minutes to complete this lesson.

To see what the finished visual representation of your database will look like, click here

Note

  Make sure your working directory is set to the folder where you saved the Tutorial sample files. If necessary, see "Setting your current working directory" in Lesson 2 for more information.
  
Working in the Visual Database Designer

Once you complete the tables that you need to store your information, the next step in database design is to link those tables. Linking the tables using the Visual Database Designer involves creating referential integrity relationships. Referential Integrity ensures that data shared between two tables (child/parent) remains valid. Changes made to a parent table are reflected in all occurrences of that data in the child table.

Now that you have been introduced to the Visual Database Designer, you will use it to link tables together. The first step to linking tables together is adding the tables to the workspace.

To Launch the Visual Database Designer

  Click Tools, Visual Database Designer.
  
To add tables to the workspace

1.	Right-click the workspace and choose Add Table.

2.	In the Add Table dialog box, select CUSTOMER.DB and click the Add button.

3.	In the Add Table dialog box, select VIDEO.DB and click the Add button.

4.	In the Add Table dialog box, select ORDERS.DB and click the Add button.

5.	Click Close.

Tables can be expanded and collapsed to display three levels of information: table name, fields, and indexes. When you first open the Visual Database Designer, the tables will display the default, which is fully expanded to the third level of information.

To collapse and expand tables

1.	Select a table in the Designer window.

2.	Click the up arrow once.

The table collapses to the field and table name level.

3.	Click the up arrow a second time to collapse the table to table name only.

4.	Click the down arrow on the same table to expand the information to include the field names.

5.	Click the down arrow again, to fully expand the table information to include the name of any indexes assigned to this table.

You can also expand all tables by clicking the Expand All button or collapse all tables by clicking the Collapse All button in the property bar.

Three tables are currently displayed fully expanded in the Design Window. If you cannot see all the tables, use the scroll bar at the bottom of the screen workspace and click and drag to reposition all three folders so you see them in the workspace.

Note

  Each field name in the table window is identified by one of the following icons:
  
  blue key - indicates the field is set as a key field

  one bar - identifies a field

  one bar with purple top - indicates a table lookup

  two bars with an arrow - indicate that an index has been established on the field

  three bars - identify a table

Linking tables

Now you are ready to link your tables together. In previous lessons, we introduced the Training-By Design (TBD) database. You created a new table within this application to support the new video component to the TBD company. Now, to support customer orders of new training videos, the tables containing information on customers, order items, and videos must be linked together.

You begin by opening the Visual Database Designer on your desktop and selecting the tables you wish to use. Next, the relationships must be defined between each table to correctly link the tables together. The CUSTOMER.DB is the master or parent table. Without this table, the other tables of information would have no meaning. Product orders, order items, and individual videos are dependent on customers for their existence. These tables therefore become detail or child tables. The relationship that you define is called referential integrity. You can use the Visual Database Designer to link the tables and add referential integrity in one step.

To link your tables and establish referential integrity

1.	Click the ItemNo field in ORDERS.DB and drag it to the ItemNo field in VIDEO.DB .

When you release the mouse button, the Link dialog box appears.

2.	In the Link dialog box, type in ItemNo to name the link.

The Primary key for VIDEO.DB appears in the Parent's Key column. You want to establish referential integrity with the ItemNo field in the Child table, which shows in the Child Fields column.

3.	Click OK.

An arrow appears between the two fields visually showing the link.

4.	Click the CustomerID field in ORDERS.DB and drag the mouse over to the CustomerID field in CUSTOMER.DB.

5.	In the Link dialog box, type in CustomerID and click OK.

An arrow appears between the two fields visually showing the link.

To show the referential integrity link name

  In the Design Window, right-click the workspace and choose Show Referential Integrity link name.
  
To save the visual representation

  Click File Menu, Save.
  
When you close the Visual Database Designer, your visual representation is saved. Next time you open the Visual Database Designer, the visual layout will be the same as when you closed it.

Verifying referential integrity

Referential integrity is one of the components of good relational database design. It ensures that the links between data in separate tables cannot be broken. When established between two fields, referential integrity verifies the validity of field values before accepting them. 

For example, referential integrity is established between a customer and an orders table on the common customer number fields. If a value is entered in the customer number field of the orders table, Paradox searches the records of the customer table and accepts or rejects the value entered based on whether it is an existing customer number.

You've already established referential integrity in the previous procedure using the Visual Database Designer. When you add links in the Visual Database Designer, these relationships are automatically added to the structure of the tables. You will open the Structure Information dialog box to verify that the links are part of the table.

To verify referential integrity

1.	If the Visual Database Designer is not closed, click File Menu, Close.

2.	Click Tools menu, Utilities, Info Structure.

3.	In the Select File dialog box, select ORDERS.DB and click Open.

4.	In the Structure Information dialog box, click the Referential Integrity tab.

Notice the link names that you added in the Visual Database Designer appear under Link Names.

5.	Click Done.

Creating a Data Model

Now that you have linked your tables and have created Referential Integrity relationships, you will create two data models using the Data Model Designer. The Data Model Designer and the Visual Database Designer are similar in their design, but are used for two different functions. The Visual Database Designer is used to create visual representations of linked tables and establish referential integrity relationships that are saved in the table structure. Creating a data model in the Data Model Designer is very similar to linking tables in the Visual Database Designer, but the end result is different.

The Data Model Designer is used to create temporary links for the purposes of a data model-these links are not saved in the table structure. You can save the data model and use it to create forms and reports. You will first create a data model to use in Lesson 4 (Working with forms) and then create a data model to use in Lesson 5 (Working with reports).

To create a data model

1.	File menu, New, Data Model.

2.	In the Add Object dialog box, select ORDERS.DB from the file list and click the Add button.

3.	In the Add Object dialog box, select VIDEO.DB from the file list and click the Add button.

4.	Click Close.

You can click and drag the tables to position them in the Data Model Designer workspace.

5.	In ORDERS.DB, click the ItemNo field and drag it to the ItemNo field in VIDEO.DB.

An arrow appears between the two fields.

6.	Click File menu, Save As.

7.	In the File Name box, type TBDform.

8.	Click Save.

Paradox automatically assigns a *.DM file extension.

9.	Click File menu, close.

To create another data model

1.	Click File menu, New, Data Model.

2.	In the Add object dialog box, do the following:

  CUSTOMER.DB and click the Add button
  
  ORDERS.DB and click the Add button
  
  VIDEO.DB and click the Add button
  
3.	Click Close.

You can click and drag the tables to position them in the Data Model Designer workspace.

4.	In ORDERS.DB, click the ItemNo field and drag it to the ItemNo field in VIDEO.DB

An arrow appears between the two fields.

5.	In ORDERS.DB, click the CustomerID field and drag it the CustomerID field in CUSTOMER.DB.

An arrow appears between the two fields.

6.	Click File menu, Save As.

7.	In the File Name box, type TBDreport.

8.	Click Save.

Paradox automatically assigns a *.DM file extension.

9.	Click File menu, close.

Summary of Lesson 3

In this lesson you learned how to

  use the Visual Database Designer
  
  set referential integrity
  
  create data models in the Data Model Designer
  
In Lesson 4 you'll learn how to create and work with forms and lookup tables to add and edit data in your tables.

Finished visual model of referential integrity relationships








