Date: Tue, 14 Jan 1997 20:08:02 GMT Server: NCSA/1.5 Content-type: text/html Last-modified: Wed, 15 Feb 1995 00:41:02 GMT Content-length: 11668

Paradox QBE Tutorial

1. Introduction

Query By Example (QBE) is a query language, which is used by Borland's Paradox database management system, and explained in Section 4.2 in Korth and Silberschatz's "Database System Concepts" (the course textbook). The instructions included in this tutorial are based on Paradox 4.5 for MS-DOS and Paradox 1.0 for Windows. There are some differences between the MS-DOS and the Windows versions, but they will be clearly pointed out.

2. Start Up

Paradox runs on the IBM PC-Compatibles in the open lab (2205 TMCB). There are two versions of Paradox available in the labs.

MS-DOS:
Paradox can be found under the C:\PARADOX directory. After entering the Paradox directory, you can run Paradox by typing paradox at the prompt. (Hint: the DOS version displays important function keys and actions they perform at the bottom row of the screen. These function keys can be of great help when you're not sure what to do next.) Working with the MS-DOS version is faster and is probably best for simple tasks.
Windows:
Paradox can be found under the C:\PDOXWIN directory. There is a Paradox group window with icons to click on. Click on the icon entitled Pad The user interface, menu design, and online help are available. Also, QBE queries can be stored on disk for later retrieval. However, it runs at typical "windoze" speed, slower than the DOS version.

3. Creating Tables

In this section, we will show how to create relations (tables) and add tuples to relations in Paradox. The following SQL command creates the table (relation scheme) students:
CREATE TABLE students (
	name 		Char (10) 	PRIMARY KEY, 
	SID 		NUMBER 		PRIMARY KEY, 
	address 	CHAR (20));
A similar concept is followed in Paradox's method for creating tables:
  1. Identify the attributes and their data types, and the key attributes (if any).
  2. Give the table relation scheme a name.

3.a Creating Relation Schemes in Paradox

Windows
1. Select File/New/Table from the menu bar, i.e., File followed by New and then by Table.
2. The table type is Paradox for Windows (the default)
3. Enter the following:
4. Click on the Save As... button and enter the name of the file. All tables are automatically given the ".db" file extension if an extension is not specified. It is recommended to use the default extension.

MS-DOS
1. Select from the menu bar Create
2. Enter the name of the table to be created.
3. Similar to step 3 in the Windows version, enter the field name and the field type. Data types are the same in DOS as in Windows. However, the data type, size, and key attribute area all entered in the "Field Type" column. Hence, if an attribute is (part of) a primary key of an alphanumeric type with a size of 10 characters, the "Field Type" is "A10*".
4. Press F2 (DoIt!) to save the table.

Note: Paradox requires the key fields to come first.

3.b Adding Tuples to a Table

Windows
1. Select File/Open/Table from the menu bar.
2. Pick the table you want to insert data into.
3. Select Table/Edit/Data from the menu bar or press F9.
4. Enter the data in the appropriate columns.
5. When finished entering data, select Table/End/Edit or press F9. The table with the new values is automatically saved when an edit session is ended.

MS-DOS

If the table to be edited is not present on the desktop:
1. Select Modify/Edit form the menu bar.
2. Enter the name of the table to edit.
3. Insert the data in the appropriate columns.
4. Press F2 (DoIt!) to save the table.

If the table is already visible on the desktop, then there is no need to re- open the table. Just click on the table and press F9 to allow editing of the table and hit F2 to save modifications made to the table.

4. Building Queries

Building queries in QBE on Paradox is similar to the ones given in the textbook with some exceptions which will be discussed in this section.

4.a Bringing Up Skeleton Tables

Before a QBE query can be built, the skeleton tables for each relation involved in the query must be selected:

Windows

1. Select File/New/Query from the menu bar
2. A window will appear with the names of available tables. Select the table involved in the query.
3. If there are other tables involved in the query, select Query/Add Table... from the menu bar to select more tables involved in the query.
4. Repeat step #3 for each table you wish to query on.
MS-DOS
1. Select Ask from the menu bar and enter the names of the tables involved in the query.
2. Repeat this for all tables involved in the query.

4.b Performing Projections

Windows and MS-DOS
1. Select the table containing the attributes on which you want to project. If the table is already on the desktop, click anywhere on the table; otherwise, open it.
2. Using the arrow keys on the keyboard or the mouse, select an attribute field on which you want to project.
3. Hit the F6 key or click on the little white box in the field and a check will appear in the field. To remove the check, hit the F6 key in that field again.
4. Repeat steps 1-3 for any other fields on which you want to project.

4.c Performing Selections

Windows and MS-DOS
1. Select the table containing the attributes on which you want to select. If the table is already on the desktop, click anywhere on the table; otherwise, open it.
2. Select the attribute fields using the arrow keys or the mouse.
3. Enter the comparison operator followed by the comparing value (either a constant or another variable).

Valid relational comparison operators include >, <, >=, <=, NOT, and the optional =.

4.d Performing Joins

Windows and MS-DOS

1. Select a table to be joined. If the table is already on the desktop, click anywhere on the table; otherwise, open it.
2. Select an attribute field to be joined.
3. Enter a variable into the field. Variables must be preceded by an underscore, "_". In Windows, the variable will appear in red and the underscore is not shown.
4. Repeat steps 1 - 3 for the other tables whose attributes you want to join.

4.e Performing Set Queries

QBE uses the SET command with NO or EVERY in the attribute field to perform such queries as Find the employee who makes more money than all others, or Find the customer who has ordered none of the books. For this type of query we will need to first create a set of tuples for comparison and compare the set to another table; we then perform other selections and projections after grouping as explained in the following example.

Suppose we wish to find all employees who work for all of departments.

First we need to create a set of all the departments by Dno in the Department location table. To do this, we follow the steps below:
  1. Select the Dep_loc table.
  2. Type SET into the field under the table name.
  3. Select the field Dno to be grouped as a set of tuples.
  4. Enter the variable _dno into the field.

    Now we need to compare this set of Dno's with the departments in the employee table to find all of the employees who work in all (or none of the) departments. To do this, we continue as follows:

  5. Select the Employee table.
  6. Select the Field Dno in the Employee table to compare with the set.
  7. Enter EVERY into the field followed by the variable _dno.

    We now have the following query specification (where P. represents the check mark). The first table establishes the set of Dno's, and the second table prints the names of employees associated with EVERY Dno in the set.



=== Dep_loc ===== Dep_Name ===== Dno ===
 SET          |              | _dno    |
========================================


=== Employee ===== Name ===== Emp_No ===== Dno ======
               | P.       |            | EVERY _dno |
=====================================================


The set comparison operators are ONLY, NO, EVERY, and EXACTLY.

ONLY: The values in the group contain only members of the defined set. For example: Which customers have ordered only one book in the Travel Series?

NO: No values in the group are in the defined set. For example: Which patients on Ward 12 haven't exhibited any symptom seen in the patients on Ward 6?

EVERY: The values in the group include all members of the defined set. For example: Which Students have taken all the courses required for a major in chemistry?

EXACTLY: The values in the group exactly match the members of the defined set (combines ONLY and EVERY). For example: Which customers have ordered all the books in the Travel Series and no others?

4.f Performing Multiple Sub-queries on the Same Table.

Each Table skeleton has multiple horizontal lines where you can perform multiple sub-queries on that table. After entering the first query in a table, use the arrow to move to the next line to perform a second sub-query. This can be repeated for more queries on that same table. Suppose, for example, that we want to query all Employees who are not supervised by a compute scientist. This would involve two queries on the Employee relation: One to select all computer scientists that are supervisors and another to compare each employee with that set of computer scientists that are supervisors. The following query demonstrates how the Employee table can be used twice in a query.

=== Emp === Emp_No === Sup_No === Degree ==============
         | _eno     |          | NOT Computer Science |
         | P.       | _eno     |                      |
=======================================================

4.g Executing and Saving Queries

Windows
1. If the query is not on the desktop, i.e., needs to be read in from a file, click on File/Open/Query in the menu bar and select the QBE file to be opened.
2. To Execute the query on the desktop, select Query/Run from the menu bar.
MS-DOS
1. Queries cannot be loaded and saved and, hence, queries must be built on the desktop.
2. Press F2 (DoIt!) to execute the chosen query.