Getting started

Paradox is a full-featured relational database management system that you can use to manage data. It can be used either as a standalone system on a single computer or as a multi-user system on a network.

A database is a management system for information. Databases are widely used every day, from telephone books to online library catalogs. Whether you develop databases for a large company or run a small business from your home, you can use Paradox to organize information. If you are new to databases, you can use Paradox Experts to quickly create tables, forms, reports, charts, and queries. If you are a more advanced user, you can code your own databases using the ObjectPAL scripting language. Querying several tables at a time can be accomplished by querying by example; or, users familiar with query programming can use SQL (Structured Query Language).

Before using Paradox, you should become familiar with the concepts of fields, records, keys, and indexes. Once you understand these, you can create your own database. Learning how to maintain data integrity, using properties like referential integrity and validity checks, will allow you to fine-tune your database and relate all the tables, forms, or reports you have created.

In this section, you'll learn about

  planning a database
  
  understanding relational databases
  
  understanding Paradox objects
  
  paradox experts
  
  using toolbars
  
  using the status bar
  
  printing, saving, closing and exiting
  
  using the Project Viewer
  
  understanding the ObjectPAL scripting language
  
Planning a database

When you plan a database, you must identify all the information you want to retrieve. Then define and organize the data you want to store and maintain. When your plan is complete, you can start to build your database.

A good plan is the key to creating an efficient, highly flexible database and to maintaining data integrity. By following a few basic steps, you'll save time and eliminate the problems that could arise from a poorly planned database.

1	Identify the information

Make a list of all of the information you want to retrieve from the database. Do you want to generate monthly reports, mailing labels, or invoices? What information do you want these documents to display? Identify all the information that each list, document, or report will contain. Be as specific as possible. Consider making a mock-up or sample of any document you want produced. Consider also where your information will come from and how you are going to enter it into the database. Identifying the information you want to retrieve helps you decide what data you need to store.

2	Define the fields

Separate all the information listed into individual pieces of data. Each piece of data will become a field in a table. For each field, you will need to identify a name, data type, and size. The name will become the name of a field, so use something meaningful and specific. For the data type, select an appropriate option, such as alphanumeric, number, money, or date. For size, you need to determine the number of characters you require to store that piece of data in the field. For more information, see "Creating defining and modifying table fields."

3	Group fields into tables

Look for repeating patterns of fields. Group related fields into sets, one set for each subject. For example, a set of fields pertaining to authors may include the author's first name, last name, date of birth, and a unique author number. Each set of like fields will become a table. For more information about tables, see "Creating and modifying tables."

4	Choose key fields

Identifying key fields early in the planning process helps you create individual tables faster. These may also be the fields you will use when you want to retrieve information from the database. Choose unique identifiers as your key fields. For more information about keys, see "Guidelines for defining key fields."

5	Link the tables

Decide which fields are going to link tables. If you have planned the tables carefully, the only fields in the database that will be duplicated from one table to another are the fields you use for linking purposes. If we use the example of a book collection database, the book table would use the International Standard Book Number (ISBN) as the unique key. The author table may also contain a unique key, such as author number, but must contain a linking field to tie the information back to the book table. By including only one additional field, the unique author number in the book table, you can easily establish a link. For more information about linking tables, see "Maintaining data integrity."

6	Establish relationships

Once you have an idea of how your data will be grouped, you need to establish how your tables will work together to share information, ensuring referential integrity as you build your database. One of your tables takes on the role as the parent table. The other tables are child (detail) tables. For more information about referential integrity, see "Defining referential integrity."

Understanding relational databases

A database is an organized collection of information based on specific data. For example, you could create a simple database of your compact disc collection by organizing your data into specific categories such as artist, album titles, length, and price.

In a relational database like Paradox, data is organized into tables. Tables contain categories of data, repeated for each item in the table. Using the above example, if you structure the CD collection as a table, you may put artists in one column, album titles in another, length in another, and price in another. For each entry in your collection (each item in the table), you enter the same categories of data (artists, album titles, length, and price).

For example, if you want to organize information about specific CDs, such as the songs on each CD, the length of each song, and the guest artists for specific songs. You could store this information in a table, too. Just as you have two collections of data, you'd have two tables.

Using a relational database, you can extract specific information from each table and assemble it in a meaningful way. For example, suppose you want to see a list that only includes each CD album title, artist, and the songs on each CD. Using Paradox, you can link the CD collection table and the specific album information table by identifying a common field ("Album Title"). Then you are free to combine the kinds of information you want to see from both tables. You can also create a query that will result in listing the album title, artist, and song titles. You can then save the query so that you can generate an up-to-date list any time. The tables are kept separate; however, in a relational database, there exists a relationship between the separate tables. In a relational database like Paradox, the term database refers to all of your tables and all of their relationships.

Using common fields to relate tables prevents data duplication and makes data easier to maintain because if you change data in a single field, the data can be updated simultaneously in many tables.

Relational systems may involve four types of table-to-table relationships: one-to-one, one-to-many, many-to-one, many-to-many.

Other concepts involved in creating a database include

  keys and links
  
  referential integrity
  
  data normalization
  
  validity checks
  
Keys and links

Keys

When you create a database table, you need to determine and define the table's primary or composite key. A primary key is a unique identifier that determines the sort order for the table and helps prevent the duplication of records within your database. For example, in a table containing customer information, you could define the Customer number field as the primary key to sort the records in sequence by number. The primary key must be the first field in the table.

A composite key is similar to a primary key except that it is composed of a group of initial table fields rather than a single field. Paradox sorts the table by the key, starting with the first field in the key and then sorting according to subsequent fields. Composite keys allow duplicate values within an individual key field as long as values are not duplicated across all fields. For example, you can define customer name and phone number fields as the composite key. You could enter two customers who have the same name, but by also keying in the phone number field, you prevent users from entering the same customer twice.

When you establish a key, Paradox creates one or more files that contain an index of the field's values and their locations. This is why the primary key is also referred to as the primary index. You cannot see the index, however, the index is referred to when Paradox refers to the index file when locating and displaying the records in a table.

Paradox allows you to establish both primary and secondary indexes in any table.

For more information about keys, see "Guidelines for defining key fields."

Links

In order to create an efficient database that is easy to maintain, you need to create tables that can be linked together. The most common way of linking tables is through common fields. For example, in order to be able to link a customer information table with a customer orders table, you may decide to include a Customer number field in both tables.

In order for Paradox to identify fields in two tables as identical, the following conditions must apply:

  The fields must have the same name, including capitalization.
  
  The fields must have the same field size.
  
  The fields must have the same field type.
  
  One of the fields must be a primary key in one of the tables.
  
When you meet these conditions, Paradox recognizes these fields as containing the same data. Then, whenever you update the information in one table, the information in all other tables containing that field is updated, ensuring that data integrity is maintained across your database.

If you are new to databases, you can read "Case Study: the MAST company", which is an example of how one company created its database.

The relationship between key fields and links

You link tables by defining a relationship between the fields of two tables. In other words, both tables must have an identical field. In one (or both) of these tables, that field must be the key field. With this relationship intact, the two tables can be linked by Paradox and you can manipulate data from both of them. This is called querying tables.

Both Paradox and dBASE let you create indexes to specify the order in which records are accessed. However, the way indexes work is different for Paradox and dBASE tables.

For more detailed information, see "Defining and removing keys."

Referential integrity

When you create your tables, you will want to establish referential integrity among your tables. Referential integrity ensures that the links between like data in separate tables cannot be broken. When you are entering data in a table, the validity of the value is verified before it is accepted. For example, if you establish referential integrity between your Customer and Orders tables on their Customer number fields, and then enter a value in the Customer number field of the Orders table, the Customer table is searched and the value is accepted or rejected depending on whether it is an existing customer number.

In the following diagram, you are prohibited from entering a value in the orders.db CustomerNo field that doesn't match an existing value in the customer.db CustomerNo field.

If you are new to databases, you will find it useful to read "Case Study: the MAST company", which illustrates how one company created its database.

For more information about data integrity, see "Maintaining data integrity."

Data normalization

Data normalization is the arrangement of data into separate tables in which each table contains the fewest number of fields necessary to establish unique categories. Rather than using one large, complex table that contains numerous fields, normalized tables distribute information over many tables using fewer fields.

When a database is completely normalized, which is the ultimate goal for any database, the only fields that are duplicated across the database are those that link tables. Normalized tables provide more flexibility in terms of analysis and are the key to creating an efficient database.

Validity checks

Validity checks are rules you impose on different fields in your tables that require the data entered to meet certain criteria before it is accepted as valid. Validity checks help minimize data entry errors. Paradox allows you to impose many types of validity checks on a table's fields. For example, you can specify maximum or minimum values for number fields, specify a default value for a field, or require that users enter a value in a given field before moving on to the next record. Implementing a few validity checks in your database can increase productivity by decreasing the time it takes someone to enter information and greatly reduce data entry errors.

For more information about data integrity, see "Maintaining data integrity."

Understanding Paradox objects

In Paradox, the database components that store, display, retrieve, and present data are called objects. The main objects you work with in Paradox are tables, forms, queries, and reports. You may also work with ObjectPAL scripts and libraries, data models, or SQL files. In addition, Paradox offers a wide variety of design objects that you can use to customize your forms and reports. Objects and design objects have attributes or characteristics called properties.

Paradox uses the following objects to store, display, and present information:

  files
  
  tables
  
  forms
  
  reports
  
  queries
  
  data models
  
  ObjectPAL scripts
  
  libraries
  
  SQL files
  
Design objects are objects you create with toolbar tools and place in forms and reports in a design window. Design objects include the following:

  text objects
  
  boxes, lines, and ellipses
  
  crosstabs
  
  charts
  
  buttons
  
  graphics
  
  OLE objects
  
  document pages
  
Each object has a different extension. For a list, see "File extensions for Paradox objects".

Tables

Creating a table involves storing attributes about entities. The only condition is that the information must be organized into a basic structure, which consists of a series of columns that represent the table fields and rows, that represent the individual records. Think of a field as a column heading, such as Name, Address, State/Province, or Employee ID. Once you have created a table with all the appropriate fields, and defined the field types, you can start creating records by adding data to rows.

Some elements, such as field names, types, and sizes, are common in creating all types of tables. However, to ensure data integrity and establish the sharing and verification of data between tables, some fields need special properties assigned to them. Maintaining data integrity is essential to a good database. You can use the following Paradox features to help ensure the proper functioning of your database: validity checks, table lookups, passwords, and referential integrity.

Paradox also provides a unique tool for creating, restructuring, and linking tables, called the Visual Database Designer. You can use it to create, restructure, and link tables. It is ideal for creating a visual representation of your database. For more information about the Visual database designer, see "Working with the Visual Database Designer."

For more detailed information about Tables, see "Creating and modifying tables."

Forms

Sometimes it's more convenient to work with the data from your tables one record at a time, rather than with an entire table full of data. Forms let you see as much or as little of your data as you want in the format you prefer. The following figure shows a form created by Paradox that displays only one record at a time.

When you view data in a form, you see the same data as in the table, but it is arranged differently. If you edit data in the form, it is updated in the table.

You can use Paradox to create custom form layouts. You can design forms that display several records from a table, or even records from several tables at the same time. To specify settings for your forms, see "Laying out forms and reports."

For more detailed information about forms, see "Creating forms."

Reports

Many people want to see their data in printed reports. Paradox reports are flexible and powerful. You can sort and group records, calculate fields and totals, and arrange your data in various formats.

Reports, like forms, take advantage of Paradox design features and tools. Using these tools, you can customize the way your reports look. You can link tables together and combine data from several tables into one report.

For more information about reports, see "Using reports."

Data models

A data model is the graphical representation of the relationships between tables in a database. Data models let you choose which tables you want to work with, what data from each table you want to display, and how these tables are linked.

Data models exist in two ways:

  As part of a form or report. When you create a form or report, you need to specify which tables to use and how the tables are linked (on which fields). When you create a data model for a form or report, you use the Data model dialog box.
  
  As a separate file. You can create a data model separately without creating a form or report. This type of data model is known as a reference data model; you can create data models separately for use at a later time. These data model reference files have a filename extension of .dm, and are created using the Data Model Designer.
  
The Data model dialog box and the Data model designer allow you to do exactly the same things. The only difference is that when you are creating a data model in conjunction with a form or report, Paradox opens the Data model dialog box; when you are creating a data model separately, Paradox opens the Data model designer.

A data model can be based on a single table or on multiple tables. To use information on a form, report, or query from more than one table, you must create a multi-table data model. You then define relationships between the tables to link them. Before you can link tables to create a data model, you must structure the tables correctly using keys and indexes. For information about indexes, see "Defining and deleting secondary indexes."

You can create a data model independent of any form, report, or query, and use the Data model dialog box to design a document or run a query. 

For more detailed information about data models, see "Creating data models."

Queries

A Paradox query is a question you ask about the data in your tables. You can use queries to

  find or select data from a table
  
  combine data from more than one table
  
  perform calculations on the data in a table
  
Paradox gives you a simple yet powerful way to ask questions about a table's data called Query By Example (QBE). In the Paradox query window, you choose which tables you want to ask questions about and then enter an example of the data you want. By doing this, you are running a query and generating an Answer table based on your example.

Paradox also allows you to run live queries that generate a live, editable view of the data you described in the query. When you edit the live query view, you actually change the data in the table you queried. Live query views give you a simple way to limit your view of data to just what you need to work with.

For more information about queries, see "Querying databases."

Paradox Experts

Paradox Experts provide easy-to-follow steps that help you quickly perform common Paradox tasks.

 The Chart Expert helps you create a chart of your data.

 The Crosstab Expert helps you create a crosstab from your data.

 The Documentation Expert produces a report on the structure and coding of your documents and objects.

 The Find Duplicate Expert helps you maintain your database by finding records with duplicate fields values in a single table. This only works with a Paradox table (*.db).

 The Form Expert helps you create a form that displays data from one or two tables in a variety of predefined layouts and styles.

 The HTML Import Expert helps you import data from HTML documents.

 The Launcher Expert creates a small tabbed form you can use to open or launch selected forms  reports  queries  scripts  and executable files with the click of a button.

 The Mailing Label Expert helps you create mailing labels in a variety of mailing label formats.

 The Merge Expert helps you merge data from a table into a form letter using a variety of word processors.

 The Query Expert helps you find  correlate  and view information in your database. This only works with a Paradox table (*.db).

 The Report Expert helps you display and print data from one or two tables in a variety of predefined layouts and styles.

 The Search and Replace Expert helps you change the underlying source code of several Paradox files simultaneously.

 The Table Expert helps you create a new table from a list of table templates.

 The Text Import Expert helps you import fixed length or delimited text into Paradox tables.

 The Utilities Expert helps you edit and update a group of documents simultaneously.

To run Paradox Experts

1	Click Tools  Experts.

2	Select the Expert you want to run.

3	Click Run expert.

Using toolbars

Paradox toolbar buttons change depending on the toolbar type and the of window types that are open. Toolbar buttons provide quick equivalents to menu commands or keystrokes.

Toolbars can be moved away from their standard position at the top of the screen. You can also display or hide any toolbar. 

Paradox provides you with the following toolbars:

Standard toolbar

This is the default toolbar which usually appears immediately below the menus. This toolbar displays buttons and tools which are shortcuts to menu commands for the current active window such as cut, paste, copy, and print.

Property toolbar

The Property toolbar contains buttons which are useful for navigating around your database. This toolbar contains buttons to let you quickly filter a table, open database objects such as forms and reports, locate table records, switch to Design mode in a form or report, and switch between editing and viewing a table.

Text Formatting toolbar

The Text Formatting toolbar displays buttons and tools which are shortcuts to commonly used menu commands which deal with text. Therefore, its buttons do not change when the active window changes. This means that some buttons on this toolbar will not have any effect if the currently selected object does not support that action. The Text Formatting toolbar does not appear by default.

Align toolbar

The Align toolbar, which only appears when the Form Design or Report Design windows are open, provides shortcuts for aligning multiple objects that you have selected. The Align toolbar does not appear by default.

Design Object toolbar

The Design Object toolbar displays buttons for various tools used when designing a form or report, and only appears when the Form Design or Report Design windows are open. Using the Design Object toolbar you can create and add such objects as buttons, fields, frames, charts, or graphics to forms and reports.

ActiveX Control toolbar

The ActiveX Control toolbar contains OLE Controls you have added to the toolbar. The native Windows controls included with Paradox also appear on this toolbar. These include the List Box, Combo Box, Spin Box, Progress Bar and Track Bar controls. The ActiveX Control toolbar does not appear by default.

To move a toolbar

1	Select a toolbar.

2	Click (click within the toolbar but not on a button) and drag the toolbar to its new location.

To dock a toolbar

1	Select a toolbar.

2	Click (click within the toolbar but not on a button) and drag the toolbar to the edge of the desktop until a dashed outline appears and the toolbar changes shape. Release the mouse button when the outline touches the edge of the window.

To let a toolbar float

1	Select a toolbar.

2	Click the appropriate toolbar (click within the toolbar but not on a button) and drag the toolbar to the middle of the desktop.

When you release the mouse button the toolbar appears in its own window which you can drag to any area of the desktop.

To display a toolbar

1	Click View  Toolbars.

2	In the Toolbar preferences dialog box, enable the check box beside each toolbar that you want to display.

Using the status bar

The status bar displays information about the current file, the selected object, or the action you are performing.You can display and hide the status bar. Its default location is the bottom of the Application window, but you can change its position. You can also resize and reset the status bar.



To display the status bar

  Click View  Status bar modes  Hide status bar.
  
Note

  A check mark appears next to the Hide status bar menu item when the status bar is hidden, therefore, if a check mark does not display, the Status bar is already being displayed.
  
To hide the status bar

  Click View  Status bar modes  Hide status bar.
  
Note

  A check mark appears next to the Hide status bar menu item when the status bar is hidden.
  
To position the status bar

1	Click View  Status bar modes  Position.

2	Choose one of the following options:

  Top - displays the status bar at the top of the desktop
  
  Bottom - displays the status bar at the bottom of the desktop
  
To size the status bar

1	Click View  Status bar modes.

2	Choose one of the following options:

  One line - displays the status bar in one line
  
  Two line - displays the status bar in two lines
  
To reset the status bar

  Click View  Status bar modes  Reset status bar.
  
This resets the status bar to the bottom of the Application Window and sizes it to two lines. 

Printing, saving, closing, and exiting

You can print tables, forms, reports, or scripts created in Paradox files.

While working with Paradox, you can save the tables, forms, reports, queries, and other objects. Files are saved differently, depending on the file type. For example, you must manually save form and report designs, but Paradox automatically saves data as you enter it. Paradox will prompt you to save a modified file upon closing it or quitting the application.

To print a table, form, report, or script

  Click File  Print.
  
To save your work

  Click File  Save.
  
To close a window

  Click File  Close.
  
To close all windows

  Click Window  Close all
  
To exit Paradox

  Click File  Exit.
  
Tip

  You can also exit Paradox by pressing ALT + F4.
  
Using the Project Viewer

Because databases can be very large and contain many different objects such as tables, forms, reports, and queries, Paradox offers several features to help you organize database projects. The Project Viewer is a graphical interface that lets you view all the objects of one or more type contained in a given directory by clicking on the type name(s). Contained within the Project Viewer are the directories, both working and private, that store your work.

The Project Viewer is a central feature of the Paradox desktop. It provides a quick way for you to organize database projects.

For more information about how to customize Paradox to better suit your needs, see "Customizing Paradox."

To learn about the elements in the Project Viewer, click any of the elements in the picture below.



   Project Viewer.
  
To have the Project Viewer open each time you run Paradox

1	Click Tools  Settings  Preferences.

2	Click the General tab.

3	Enable the Open Project Viewer on startup check box.

To display a menu of commands for Project Viewer items

  Right-click an object name on the right panel of the Project Viewer.
  
Notes

  If you are viewing all files, you will see that some files do not have menus. This is because some files are automatically created with a Paradox object and are modified only when you modify the object. (For example, .px files, .tv files, and .mb files are associated with Paradox tables.)
  
  You can change your working directory from the Project Viewer. For information about working and private directories, see "Specifying working and private directories."
  
To add a reference to the Project Viewer

1	Right-click the object you want to reference, and click Create shortcut.

2	Right-click the shortcut, and click Cut.

3	In the Project Viewer, display the folder where you want to add the reference.

4	Right-click the right panel of the Project Viewer,and click Paste.

To remove a reference from the Project Viewer

  In the right panel of the Project Viewer, right-click the object you want to remove, and click Delete.
  
Notes

  You can follow these steps to add non-Paradox items to the Project Viewer. If the item's filename extension is associated with a program, you can double-click the non-Paradox object to open it. Refer to your Windows documentation for information about associating filename extensions with programs.
  
  Removing a reference from the Project Viewer does not delete an object; it only deletes the reference to that object.
  
Understanding the ObjectPAL scripting language

ObjectPAL (Object Paradox Application Language) is an object-based, event-driven, visual programming language. You can use ObjectPAL to customize applications with new buttons, menus, dialog boxes, prompts, warnings, and online Help. ObjectPAL can be used to extend the regular Paradox functions, or to create non-database applications. You can also create scripts, libraries, and SQL files in ObjectPAL.

ObjectPAL and Paradox are tightly integrated; the more you know about Paradox, the more you can take advantage of  it in your ObjectPAL programming. If you think of ObjectPAL as a tool that extends the power of Paradox, you can discover ways of using the language to perform tasks that would be awkward, difficult, time-consuming, or impossible to perform without it.

ObjectPAL supports variables, control structures, such as IF, FOR, WHILE, LOOP, and many of the same data types as other programming languages. You can use methods and procedures supplied by ObjectPAL, or you can create your own custom methods and custom procedures.

For more information about ObjectPAL see the "ObjectPAL online Help file." Or for step-by-step instructions about how to use ObjectPAL, see the "ObjectPAL Tutorial".

Scripts

Scripts are pieces of ObjectPAL code that you can create to perform operations automatically. ObjectPAL code is usually attached to objects in forms, but you can also create standalone scripts that perform operations you specify independently of a form. For example, you can write a script to open a particular table and perform a calculation on one or more of its fields. Paradox runs this type of script directly from the desktop, not from triggering an event on an object in a form. For more information about writing scripts, see the "ObjectPAL online Help file."

Libraries

A library is an object you can use to store commonly used ObjectPAL code. It lets you easily share code among forms, scripts, and other libraries. For more information about libraries, see the "ObjectPAL online Help file."

SQL Files

An SQL file is an object that contains code you write in SQL (Structured Query Language). For more information about using SQL with Paradox and about using Paradox to work with remote data, see "Connecting to SQL databases."

You can use the SQL Editor to write SQL code to perform operations on remote data using Corel SQL Links. You can also write query scripts using SQL that you can run on local Paradox or dBASE data.




