CREATING TABLES

The primary tables of the OLS database are MEMBERS, AUTHORS, RESEARCHTEAMS, and PAPERS. I describe creating them (with the SQL CREATE statement) in Book 5, Chapter 4. You may want to do a little more work, however, in the interest of keeping erroneous data out of the database. You do that by adding constraints, which I discuss in the next section.

ADDING CONSTRAINTS TO PREVENT DATA-ENTRY ERRORS

When the MEMBERS table was defined in Book 5, Chapter 4, the MemberID attribute was assigned the INTEGER type, and the other attributes were assigned the CHAR type, with various maximum lengths. These assignments constrain the data entered in those fields to some extent but leave a lot of room for the entry of erroneous data. You can do a better job by applying some constraints to the attributes, as follows:

• At this time, OLS leadership cannot imagine a membership of more than 1,000 members, so MemberID could be capped at that level. Any larger number entered must be an error.
• Only five offices exist: president, vice president, secretary, treasurer, and archivist. Any entry in OfficeHeld other than one of those five must be an error.
• The State field may contain only the two-letter abbreviations for Oregon (OR) and the three adjacent states: Washington (WA), Idaho (ID), and California (CA). Membership is restricted to residents of those four states.
• All zip codes for Oregon, Washington, Idaho, and California start with either 8 or 9.

Applying these constraints to the MEMBERS table eliminates at least some, if not all, data-entry errors. If you had thought ahead, you could have applied these constraints when you created MEMBERS with the CREATE statement. If the table has already been created, but data hasn’t yet been entered in it, you can drop the existing empty table and then re-create it, this time applying the constraints. Here’s an example:

CREATE TABLE MEMBERS (

MemberID Integer PRIMARY KEY,

FirstName Char (15),

LastName Char (20) NOT NULL,

OfficeHeld Char (20),

Email Char (50),

Phone Char (20),

Street Char (25),

City Char (20),

State Char (2),

Zip Char (10),

CONSTRAINT max_size

CHECK (MemberID BETWEEN 1 AND 1000),

CONSTRAINT offices

CHECK (OfficeHeld IN ('President', 'Vice President',

'Secretary', 'Treasurer', 'Archivist')),

CONSTRAINT valid_states

CHECK (State IN ('OR','WA','ID','CA')),

CONSTRAINT valid_zip

CHECK (SUBSTRING (Zip FROM 1 FOR 1) = 8

OR SUBSTRING (Zip FROM 1 FOR 1) = 9)

);

If the table has already been created and data has already been added to it, you can’t add constraints after the fact, because they could conflict with data that’s already in the database.

By examining all the table designs before creating them, you can add appropriate constraints and thereby minimize the problems that inevitably arise when erroneous data enters the database.

ADDING DATA WITH FORMS

Most DBMSs support form-based data entry. This feature enables you to create a screen form that has a field for every column in a database table. Field labels in the form enable you to determine easily what data goes into each field. The data-entry operator enters all the data for a single row in the form. After the DBMS accepts the new row, the system clears the form to accept another row. In this way, you can easily add data to a table one row at a time.

Form-based data entry is easy and less susceptible to data-entry errors than is a list of comma-delimited values. The main problem with form-based data entry is that it’s nonstandard; each DBMS has its own method of creating forms. This diversity, however, isn’t a problem for the data-entry operator. You can make the form look generally the same from one DBMS to another. Although this practice is great for the data-entry operator, the application developer must return to the bottom of the learning curve every time he changes development tools. Another possible problem with form-based data entry is that some implementations may not permit a full range of validity checks on the data that you enter.

The best way to maintain a high level of data integrity in a database is to keep bad data out of the database in the first place. You can prevent the entry of some bad data by applying constraints to the fields on a data-entry form. This approach enables you to make sure that the database accepts only data values of the correct type, which fall within a predefined range. Applying such constraints can’t prevent all possible errors, but it does catch some of them.

If the form-design tool in your DBMS doesn’t enable you to apply all the validity checks that you need to ensure data integrity, you may want to build your own screen, accept data entries in variables, and check the entries by using application program code. After you’re sure that all the values entered for a table row are valid, you can add that row by using the SQL INSERT command.

ENTERING DATA WITH SQL

If you enter the data for a single row in a database table, the INSERT command uses the following syntax:

INSERT INTO table_1 [(column_1, column_2, …, column_n)]

VALUES (value_1, value_2, …, value_n) ;

As indicated by the square brackets ([ ]), the listing of column names is optional. The default column list order is the order of the columns in the table. If you put the VALUES in the same order as the columns in the table, these elements go in the correct columns — whether you explicitly specify those columns or not. If you want to specify the VALUES in some order other than the order of the columns in the table, you must list the column names, putting the columns in an order that corresponds to the order of the VALUES.

To enter a record in the MEMBERS table, for example, use the following syntax:

INSERT INTO MEMBERS (MemberID, FirstName, LastName, OfficeHeld, Email, Phone, Street, City, State, Zip)

VALUES (:vmemid, 'Linda', 'Nguyen', '235 Ion Drive',

'Titania', 'OR', '97110', '(503) 555-1963') ;

The first VALUE, vmemid, is a variable that you increment with your program code after you enter each new row of the table. This approach guarantees that you have no duplication of the MemberID, which is the primary key for this table and, therefore, must be unique.

The rest of the values are data items rather than variables that contain data items. You can hold the data for these columns in variables, too, if you want. The INSERT statement works equally well with variables or with an explicit copy of the data itself as arguments of the VALUES keyword.

ADDING DATA ONLY TO SELECTED COLUMNS

Sometimes, you want to note the existence of an object, even if you don’t have all the facts on it yet. If you have a database table for such objects, you can insert a row for the new object without filling in the data in all the columns. If you want the table to be in First Normal Form (1NF), you must insert enough data to distinguish the new row from all the other rows in the table. (For a discussion of 1NF, see Book 2, Chapter 2.) Inserting the new row’s primary key is sufficient for this purpose. In addition to the primary key, insert any other data that you have about the object. Columns in which you enter no data contain nulls.

The following example shows such a partial row entry:

INSERT INTO MEMBERS (MemberID, FirstName, LastName)

VALUES (:vmemid, 'Linda', 'Nguyen') ;

You insert only the customer’s unique identification number and name into the database table. The other columns in this row contain null values.

ADDING A BLOCK OF ROWS TO A TABLE

Loading a database table one row at a time by using INSERT statements can be tedious, particularly if that’s all you do. Even entering the data in a carefully human-engineered ergonomic screen form gets tiring after a while. Clearly, if you have a reliable way to enter the data automatically, you’ll find occasions in which automatic entry is better than having a person sit at a keyboard and type.

Automatic data entry is feasible, for example, if the data already exists in electronic form because somebody has already entered the data manually. If so, you have no reason to repeat history. The transfer of data from one data file to another is a task that a computer can perform with minimum human involvement. If you know the characteristics of the source data and the desired form of the destination table, a computer can (in principle) perform the data transfer automatically.

COPYING FROM A FOREIGN DATA FILE

Suppose that you’re building a database for a new application. Some data that you need already exists in a computer file. The file may be a flat file or a table in a database created by a DBMS different from the one you use. The data may be in ASCII or EBCDIC code or in some arcane proprietary format. What do you do?

The first thing you do is hope and pray that the data you want is in a widely used format. If the data is in a popular format, you have a good chance of finding a format conversion utility that can translate the data into one or more other popular formats. Your development environment probably can import at least one of these formats. If you’re really lucky, your development environment can handle the data’s current format directly. On personal computers, the Access, xBASE, and Paradox formats are probably the most widely used. If the data that you want is in one of these formats, conversion should be easy. If the format of the data is less common, you may need to go through a two-step conversion. Just about any environment you’re likely to encounter will support the comma-separated value (csv) format. Translating from your source format to csv and then from csv to your destination format should work in most cases.

As a last resort, you can turn to one of the professional data-translation services. These businesses specialize in translating computer data from one format to another. They have the capability of dealing with hundreds of formats — most of which nobody has ever heard of. Give one of these services a tape or disk containing the data in its original format, and you get back the same data translated into whatever format you specify.

TRANSFERRING ALL ROWS BETWEEN TABLES

A less severe problem than dealing with foreign data is taking data that already exists in one table in your database and combining that data with compatible data in another table. This process works great if the structure of the second table is identical to the structure of the first table — that is, if every column in the first table has a corresponding column in the second table, and the data types of the corresponding columns match. If so, you can combine the contents of the two tables by using the UNION relational operator. The result is a virtual table containing data from both source tables. I discuss the relational operators, including UNION, in Book 3, Chapter 4.

TRANSFERRING SELECTED COLUMNS AND ROWS BETWEEN TABLES

Generally, the structure of the data in the source table isn’t identical to the structure of the table into which you want to insert the data. Perhaps only some of the columns match — and these are the columns that you want to transfer. By combining SELECT statements with a UNION, you can specify which columns from the source tables to include in the virtual result table. By including WHERE clauses in the SELECT statements, you can restrict the rows that you place in the result table to those that satisfy specific conditions. I cover WHERE clauses extensively in Book 3, Chapter 2.

Suppose that you have two tables — MEMBERS and PROSPECTS — and you want to list everyone living in the state of Idaho who appears in either table. You can create a virtual result table with the desired information by using the following command:

SELECT FirstName, LastName

FROM MEMBERS

WHERE State = 'ID'

UNION

SELECT FirstName, LastName

FROM PROSPECTS

WHERE State = 'ID' ;

Here’s a closer look:

• The SELECT statements specify that the columns included in the result table are FirstName and LastName.
• The WHERE clauses restrict the rows included to those with the value 'ID' in the State column.
• The State column isn’t included in the results table but is present in both the MEMBERS and PROSPECTS tables.
• The UNION operator combines the results from the SELECT on MEMBERS with the results of the SELECT on PROSPECTS, deletes any duplicate rows, and then displays the result.

Another way to copy data from one table in a database to another is to nest a SELECT statement within an INSERT statement. This method (a subselect) doesn’t create a virtual table but instead duplicates the selected data. You can take all the rows from the MEMBERS table, for example, and insert those rows into the PROSPECTS table. This method works only if the structures of the MEMBERS and PROSPECTS tables are identical, of course. If you want to place only those customers who live in Idaho in the PROSPECTS table, a simple SELECT with one condition in the WHERE clause does the trick, as shown in the following example:

INSERT INTO PROSPECTS

SELECT * FROM MEMBERS

WHERE State = 'ID' ;

Even though this operation creates redundant data (now you’re storing member data in both the PROSPECTS and MEMBERS tables), you may want to do it anyway to improve the performance of retrievals. Be aware of the redundancy, however, and to maintain data consistency, make sure that you don’t insert, update, or delete rows in one table without inserting, updating, or deleting the corresponding rows in the other table. Another potential problem is the possibility that the INSERT might generate duplicate primary keys. If even one preexisting prospect has a primary key called ProspectID that matches the corresponding primary key, MemberID, of a member that you’re trying to insert into the PROSPECTS table, the insert operation will fail.