Lesson 2: Creating a table

Lesson 2 will help you become familiar with the creation and maintenance of a Paradox table. We are using the Training-By-Design (TBD) sample database as the basis for this tutorial. TBD is a fictitious company that offers computer training courses. As an extension of the business, TBD has decided to offer training videos for sale to its customers. A table containing video information has already been created to support this extension of their business. However, TBD needs a new table to keep track of all video orders. You'll begin by creating the structure for the table.

In this lesson, you will

  explore the Visual Database Designer
  
  use the Visual Database Designer to create your table structure
  
  define table properties
  
You'll need about 30 minutes to complete this lesson.

To see what your finished table will look like, click here.

Notes

  Before you begin creating your table, it's a good idea to establish a location on your computer to contain your new database, and set that location as your working directory. The Paradox folder called TUTORIAL stores all the files you will need for this tutorial. 
  
  For more information about installing the tutorial sample files, see "Installing the Paradox Classic MAST sample and tutorial files."
  
Setting your current working directory

In Lesson 1, you set the Working Directory to the folder where you saved the Tutorial files. We've included these steps again for you to ensure that your working directory is set to the TUTORIAL folder.

To set the TUTORIAL folder as your current working directory

1.	Click File menu, Working Directory.

2.	In the Set Working Directory dialog box, scroll to locate the TUTORIAL folder.

3.	Click the TUTORIAL folder.

The directory path for the TUTORIAL folder should appear in the Directory box.

4.	Click OK.

You are now ready to create your table.

Using the Visual Database Designer

The Visual Database Designer is a completely new application designed for working with databases. Using the Visual Database Design, you can create Paradox tables, establish referential integrity between tables, view the fields of tables, and create, print, and save a layout of your database design.

For this lesson, you will use the Visual Database Designer to create a table. In Lesson 3, you will use the Visual Database Designer to link tables and establish referential integrity relationships. 

Creating a table

There are three aspects to every database table: the table structure; special properties attached to the table, such as validity checks, secondary indexes, lookup tables, passwords, and referential integrity; and the data entered and stored in the table. 

To create the table structure

1.	Click Tools menu, Visual Database Designer.

2.	Click Insert menu, New Table.

3.	In the Workspace, click to place the table.

4.	In the Table Name dialog box, type ORDERS.

Paradox will automatically save this new table as ORDERS.DB. 

5.	Click OK.

The Create Paradox Table dialog box opens.

Note

  For this tutorial you use the Visual Database Designer to access the Create Table dialog box. You can also access the Create Table dialog box through the File menu. 
  
Creating fields to store your information

The Create Paradox Table dialog box contains everything you need to establish the table structure and define any special properties for your table. The dialog box consists of five tabbed pages: Field Roster, Secondary Index, Table Lookup, Passwords, and Referential Integrity.

The Field Roster page displays when you first open the dialog box. This is where you identify the field names, data types, and sizes that make up the structure of your table. The procedure below guides you through the creation of your first field. When you are finished defining the first field, named OrderNo, repeat your steps to add four more fields to the table. The first field contains a primary key.

To create the first field in your table structure

1.	Click the first row in the Field Name column.

2.	Type OrderNo.

3.	Click the Type column to display the list of record types.

4.	From the list box, choose Long Integer.

The letter 'I' appears in the Type field. Although you can't see the value, Paradox automatically assigns a size in the Size column.

5.	Move the mouse pointer to the key column between the field number column and the Field Name column.

A key cursor appears.

6.	Click the field to set a primary key.

7.	Click in the Field Name column under OrderNo to create a new field.

To create additional fields

1.	Follow steps 1 to 4 in the previous procedure and type the following values in the corresponding fields:

  Field Name-ItemNo
  
  Type-From the list box list, choose Long Integer
  
2.	Click in the Field Name column under the last record to begin a new line. Type the following values in the corresponding fields:

  Field Name-CustomerID
  
  Type-From the list box list, choose Long Integer
  
3.	Click in the Field Name column under the last field to begin a new line. Type the following values in the corresponding fields:

  Field Name-Qty
  
  Type-From the list box list, choose Short
  
Since you already typed in a name for your table, when you click Create, Paradox will automatically name and save your table.

4.	Click Create.

Your new table appears in the Visual Database Designer window.

Defining table properties

Each field in your table can be further defined by setting a table property. Table properties are rules applied to the table in general or to specific fields that control and maintain the integrity of the data entered in your database. Table properties can be set while you are creating the structure of your table, or can be defined later, after your information has been entered. When you see the need to apply certain restrictions or limitations on your data after the table has been created, you must perform a Table Restructure to apply a property.

The categories of table properties include: Validity Checks (setting minimum, maximum and default values on information entered, and assigning picture formats and required fields), Secondary Index, Lookup Table (explained in Lesson 4), Referential Integrity, and Password Protection.

To restructure a table

  In the Visual Database Designer, right-click the ORDERS.DB table and choose Restructure.
  
The Restructure dialog box opens.

Setting Table Properties

TBD only has so many Videos in stock since it is a new product line. Therefore, customers can only order a maximum number of videos for each order. You must set a maximum number for the Qty (Quantity) field. Paradox also allows you to set a number of other properties for the table including the language driver, a required field, a picture, and a default value.

To set the language driver

1.	Click the Table Language list box on the bottom right corner of the Restructure window.

2.	From the drop-down list, select Paradox 'ascii'.

To set minimum and maximum values

1.	Click the Qty field name.

2.	Press ENTER twice to move your cursor into the Min column.

3.	Type 0.

4.	Press ENTER to move your cursor into the Max column.

5.	Type 25.

You have now set minimum and maximum limits for the values that are entered in the Qty field. A user will not be permitted to enter an amount lower than 0 or higher than 25.

To set a Required Field

1.	Click the OrderNo field name.

2.	Press ENTER to move your cursor to the Req'd column.

3.	Enable the check box.

The Required Field column identifies a field that must be present for every record entered in the table.

To open the Picture Assistant

1.	Click the OrderNo field name.

2.	Press ENTER and move the cursor to the Picture column.

3.	From the list box, choose Picture Assistant.

To set a new picture

1.	In the Type list box, choose Long Integer (it should be the default).

2.	Click the Add button.

3.	In the Pictures box, type Order#.

4.	Click the Code Syntax button.

A list of codes is provided along the bottom of the dialog box to help you select the code combination you wish to use. 

5.	In the Picture Code box, type ####.

The code you entered means "four numeric digits." 

6.In the Sample Value box, type in 1234 

7.	Click OK to close the Picture Assistant dialog box.

To set a default value

Default values are particularly useful where the same value is repeated often within a table, for example, in a customer table where the customer city is the same for the majority of records entered. For records with a different city, the user simply types over the default value displayed. Once you set a Default value, the value automatically appears in the field when you enter a new record. If you want to enter a value other than the default, type the information over the default value. For this table, however, you do not need to set any default values.

To Save Table Properties

1.	On the Restructure dialog box, click the Save button.

A Restructure Warning dialog box opens. Paradox asks whether the new validity checks should be enforced on existing data for each field for which you defined a validity check. Since you don't have any data in the ORDERS.DB table, you want to enforce the validity checks.

2.	Enable the Yes button and click OK.

You will need to repeat step 2 for each new validity check.

Establishing a Secondary Index

When a key is identified on a table, Paradox establishes an index based on this key to sort the records in your table, speed up the retrieval of your records during a search, and prevent the entry of a second record with the same key value. Once this key, also known as a primary key, has been defined on a table, additional indexes can be established as secondary indexes. Paradox creates a listing based on these indexes, which helps speed up queries by sorting data in a different order from the key field. Secondary indexes are often the common field used to link tables. You will set a Secondary index for two of the tables in the TUTORIAL folder.

Make sure the Restructure dialog box is closed.

To establish a secondary index

1.	Click Tools menu, Utilities, Restructure.

2.	From the Select File box, choose ORDERS.DB and click Open.

3.	Click the Secondary Index tab.

4.	Click the Add button, and type Item Number in the Index Names box.

5.	Click the ItemNo field from the Define Index Fields list.

6.	Click the Right Arrow to move the field into the Selected Index Fields column.

The Sort column sets the default sort order as A-Z (ascending). You can also set a descending sort order by clicking the Sort column and choosing (Z-A) from the list box list. For this lesson you want to use ascending sort order (A-Z).

7.	Under Index Options, enable the Maintained and Case sensitive check boxes. 

8.	Click Save.

Paradox informs you that the Paradox Native index will be renamed after its corresponding field. You will only see this dialog box if you set a secondary index with the Paradox Native options (non-composite, non-unique, case-sensitive, and ascending).

9.	Click OK.

To add another secondary index

1.	Click Tools menu, Utilities, Restructure.

2.	From the Select File box, choose ORDERS.DB and click Open.

3.	Click the Secondary Index tab.

4.	Click the Add button.

5.	Type CustomerID in the Index Names list box and press ENTER.

Since you named the index the same as one of the fields in the table, Paradox assumes that you want the index to have the settings as a Paradox Native index (non-composite, non-unique, case-sensitive, and ascending). For this lesson, you want to create a Paradox Native index, but you could edit the index name if you wanted different index options.

6.	Click Save.

Notes

  You will only see the Paradox Native warning dialog box if you set a secondary index with the Paradox Native options (non-composite, non-unique, case-sensitive, and ascending). If you create an index with any other combination of options, you can define a custom name.
  
  The three Paradox index options are the following:
  
  Case sensitive - determines whether capitalization is considered when sorting records.
  
  Maintained - instructs Paradox to update the index each time new data is added to the table. This setting is essential to speed up queries and to support linked tables.
  
  Unique - (you must first enable Maintained before you can enable Unique) requires that the value entered exists only once. If Paradox detects a duplicate value, a warning message appears and the index is not created.
  
Assigning password security

When you want to control which users can read or modify your data, you can assign Master and Auxiliary Passwords to your tables or to individual fields to protect the data from unauthorized access.

A Master Password controls all access to an entire table. You must specify a Master Password before creating Auxiliary Passwords. To maintain security you can change your Master Password regularly. As well, you can delete Master Passwords if they are no longer required. An Auxiliary Password uses table rights and field rights to provide different levels of access privileges for different users in a group. 

To assign a Master Password

1.	Click Tools menu, Utilities, Restructure.

2.	From the Select File box, choose CUSTOMER.DB and click Open.

3.	Click the Passwords tab.

4.	Click the Define button and type a password into the Password text box.

For this tutorial, you can make up any password you wish.

5.	Press TAB to move your cursor to the Verify Password text box and type the password again.

Passwords are case-sensitive and can be up to 15 characters long including spaces. The two passwords must be identical.

6.	Click OK.

To assign an Auxiliary Password.

1.	Under Auxiliary Passwords, click Add.

2.	Type Password1 to name the Auxiliary Password.

This activates the options for table rights.

3.	Under Table Rights, choose All. Paradox provides the following user rights:

  All - gives full rights to all functions including the ability to change and delete passwords.
  
  Insert & delete - new data can be entered and existing data can be edited and deleted but the table cannot be deleted or restructured.
  
  Data entry - new data can be inserted and existing data can be edited but records cannot be deleted and the table cannot be restructured.
  
  Update - editing can only take place on non-key fields.
  
  Read Only - the user is able to view the data only.
  
To assign a password at the field level

1.	Choose a field from the list provided in the Field Rights box.

2.	Click in the Access column, and choose one of the following options from the list box:

  None - the user has no rights to this field and Paradox does not display the values in this field.
  
  Read Only - the user can only view the data.
  
  Full - there are no restrictions.
  
Now that you know how to work with Passwords, you are going to remove them from the table. It is important to know how to set passwords when you create databases using Paradox. For this tutorial, however, we do not need them. 

To remove a password

1.	Under Auxiliary Passwords, select Password1 and click Remove.

2.	Under Master Password, click Delete.

A warning dialog box will ask you if you are sure you want to delete the Master Password.

3.	Click Yes.

4.	Click Save.

Summary of Lesson 2

In this lesson you learned how to

  use the Visual Database Designer
  
  create your table structure
  
  define table properties
  
In Lesson 3 you'll learn how to link your tables using the Visual Database Designer, create Referential Integrity, and create a data model using the Data Model Designer.

Finished table

The table is empty because you haven't entered any data yet.








