JDB SQL Reference

Chapter 2. Introduction to Databases

A database is a collection of information organized into different areas. Generally, a database covers information about a specific subject. For example, a company might have one database for personnel and another database for customer orders.

What sets a database system apart from other computer applications is that a structure exists which organizes the information. This structure allows each piece of information to be tagged. In some database systems, this structure is called the data model or database schema. Since there is a structure, the information stored in a database can be easily accessed for display to a screen or for printing in a report.

Structure of a Relational Database

To describe the structure of JDB, you need to have a basic understanding of relational databases as a whole.

Tables

JDB, like other relational database management systems, organizes its information into tables, which consist of a row and column arrangement of data values. Generally, a database table contains a subset of related information about the main subject.

For example, if inventory is the main subject for your database, you might have tables for products, orders, and suppliers.

The two-tier sample application provided with Panther uses a JDB database called videobiz. This database was designed for a video rental store, so it needs information about customers, video titles, and video rentals. Table 2-1 lists the database tables in the videobiz database.

**Table 2-1 Videobiz database tables**
Table Name	Description
`customers`	Address, phone number and membership information for each customer
`titles`	Title, director, length, rating and price category for each video.
`title_dscr`	Description of each video
`tapes`	Status of each copy of a video
`pricecats`	Listing of the various price categories
`actors`	Actors appearing in the videos
`roles`	Roles associated with each video
`rentals`	Video title, customer, and date information for each video rental
`codes`	Listing of the various codes used in the database
`users`	Login names for users of the database
`flag`	Yes/No flag used in the VideoBiz application

Columns

Each database table is divided into columns and rows. The columns are the various subcategories of the table, each containing a piece of the table information. The columns in the titles table, described in Table 2-2, have information about a video title, such as the director, the type of video, and the running time.

**Table 2-2 Titles table**
Column Name	Description	Status
`title_id`	Identification code for the video	Primary key
`name`	Name of the video
`genre_code`	Code describing the video type
`dir_last_name`	Director's last name
`dir_first_name`	Director's first name
`film_minutes`	Length of the video
`rating_code`	Rating code
`release_date`	Year the original film/video was released
`pricecat`	Price category used when this video is rented	Foreign key

Entering Data

When you start to enter data into your database tables, you will not always have a value for every column. In those cases, the value of the column is said to be NULL. However, you must enter a value for your primary key columns and any column specified as NOT NULL. Those columns cannot have null values.

Null values are used when the column value is unknown or unavailable. A null value is not synonymous with an entry of zero or with a blank.

Rows

When you insert information into a database table, you can enter a value for each column. Each entry is called a row. In some database systems, the equivalent of a column is called a field and the equivalent of a row is called a record.

Figure 2-1 The columns and rows associated with the titles table.

Each row of data in a JDB database table can be up to 1K. For more information, refer to page 6-12.

Primary Keys

Every table in a well-designed relational database should have a primary key. A primary key is the column, or set of columns, that uniquely identifies a row. Because the rows of a relational database are unordered, the primary key definition lets you select a particular row of information.

JDB does not enforce unique entries for the primary key columns. So, while you will not receive an error, you should define the primary key columns when you create your database tables because:

The primary key definitions are assigned to table view properties when you import database tables to the repository.
The transaction manager gives an error message if you attempt to insert a duplicate primary key or update the primary key column to a duplicate value.
If you transfer your database schema to another database engine, the primary key columns will, no doubt, need to be defined for that engine.

JDB stores the primary key information. To define primary key information, choose which column or columns are the primary keys when you create your database tables. In the titles table, the primary key is the title_id column, therefore, a unique value should be applied for each row in that column.

In some database tables, a combination of two columns must make up the unique value. For example, the tapes table contains a title_id column, but a store can have several copies of a video title. Therefore, a combination of the title_id and copy_num columns must be used as the primary key of the tapes table. Every tape in the table is guaranteed to have a unique combination of data values in these two columns.

No two rows of a table with a primary key are duplicated. Table 2-3 lists the columns associated with the tapes table.

**Table 2-3 Tapes table**
Column Name	Description	Status
`title_id`	Title	Primary key, Foreign key
`copy_num`	Copy number for this tape.	Primary key
`status`	Code detailing whether the tape is available.
`times_rented`	Number of times this copy has been rented.

Foreign Keys

If a column in a database table matches the primary key in another table, the column is referred to as a foreign key. Any value entered into a foreign key column must match a value previously entered into the primary key column in the other table. For example, the title_id column is a foreign key in the tapes table. Therefore, any value entered into the title_id column of the tapes table should already exist as a value in the titles table.

Naming Database Tables and Columns

In JDB, names for tables and columns are one-word descriptions consisting of letters, numbers and underscores. Names can be up to 31 characters in length. Tables names must be unique within the database. Column names must be unique within the table.

When you create columns in a table, you tell the database whether the data in the column are character strings, dates, or numbers. For numbers, you specify what type can be entered–integer or float, for example. For character strings, specify the maximum length. The maximum allowable length for a character string column is 255.

Notes: Do not use JDB keywords as a name of a table or column; refer to page C-1 for a list of keywords.

Designing Your Database

The design process for building a database follows these general steps:

Choose the main subject.
Decide the main focus of the database, for example, customer orders, inventory, or personnel. Generally, the database name indicates its main purpose. The two-tier sample application included with Panther is for a video rental store and is called videobiz.
Build your database model.
Identify the database tables and their columns. Determine the main subsets of information; these correspond to the database tables. Then, decide which pieces of information are to be stored in each table; these pieces become the columns in each table.
Determine where common data values exist and eliminate duplicate data entry.
Although a database table can contain all the information that logically relates to a subset of your database, this is not always the case. The database table should be designed to avoid duplicate data entry wherever possible. For example, in the sample application, information about video tapes is divided into two separate tables: titles and tapes. The titles table contains the information about the video title–its name, director, length, etc. The tapes table contains the information about each copy of the video–the copy number, status code, and the number of times this copy has been rented. By having two tables, you do not have to reenter the general video information, like the director and the length, for each copy of the video. You simply enter the title_id.
If you split the information into two or more tables, determine which column will be found in all of the tables. In the sample application, the title_id column is found in several tables. This common data value in different tables provides the relationship needed to join tables together in order to combine the information.
In addition, you can also combine data from two tables into a single database table. For example, in the sample application, instead of having separate database tables, one for credit card codes and another for genre codes, the codes are combined into a single table of codes (called codes) used by the application. Entries into the columns code_type, code, and dscr describe and identify each specific code.
Define unique entries.
For each table, determine which column or columns comprise the primary key, uniquely identifying each row. Since the titles table has more than one row with an entry of Henry V in the name column, the following statement affects both rows:
```
DELETE FROM titles WHERE name = 'Henry V';
```
For the titles table, the title_id column is used to uniquely identify each row.
To uniquely identify each copy of a video tape, the tapes table uses the title_id and copy_num columns to make the primary key. The following statement affects all rows with a value of 1345 in the title_id column:
```
DELETE FROM tapes WHERE title_id = 1345;
```
To change data about one copy of the tape, you need to list both the title_id and the copy_num columns in the WHERE clause.
```
DELETE FROM tapes WHERE title_id = 1345 AND copy_num = 4;
```
Chart the tables and their relationships.
Since information is stored in tables and the tables do not have any inherent relationship, it is possible to update a column in one table and not update the column in another table even if both columns correspond to the same value. To preserve the integrity of the database, it helps to chart the relationships between the tables. Then, if you update the information in one table, the chart illustrates if, and which, tables also need to be updated.
Figure 2-2 is a diagram that describes the videobiz database tables and their relationships to one another. Each table is represented in a box. The table name and primary key columns are listed in the top of each box. The lines between the boxes illustrate the foreign key definitions.

Figure 2-2 Diagram of the videobiz database.