SQL Series....10

Building and Maintaining a Simple Database Structure

Computer history changes so fast that sometimes the rapid turnover of technological “generations” can be confusing. High-level (so-called third-

generation) languages such as FORTRAN, COBOL, BASIC, Pascal, and C were the first languages used with large databases. Later, languages specifically designed for use with databases, such as dBASE, Paradox, and R:BASE (third-and-a-half-generation languages?) came into use. The latest step in this progres-sion is the emergence of development environments such as Access, Delphi, and C++Builder (fourth-generation languages, or 4GLs), which build applica-tions with little or no procedural programming. You can use these graphical object-oriented tools (also known as rapid application development, or RAD, tools) to assemble application components into production applications.

Because SQL is not a complete language, it doesn’t fit tidily into one of the gen-erational categories I just mentioned. It makes use of commands in the manner of a third-generation language but is essentially nonprocedural, like a fourth-generation language. The bottom line is that how you classify SQL doesn’t really matter. You can use it in conjunction with all the major third- and fourth-generation development tools. You can write the SQL code yourself, or you can move objects around on-screen and have the development environment generate equivalent code for you. The commands that go out to the remote database are pure SQL in either case.

In this article, I take you through the process of building, altering, and drop-ping a simple table by using a RAD tool, and then discuss how to build, alter, and drop the same table using SQL.

Building a Simple Database Using a RAD Tool

People use databases because they want to keep track of important information. Sometimes, the information that they want to track is simple, and sometimes it’s not. A good database management system provides what you need in either case. Some DBMSs give you SQL. Others, such as RAD tools, give you an object-oriented graphical environment. Some DBMSs support both approaches. In the following sections, I show you how to build a simple single-table database by using a graphical database design tool so that you can see what the process involves. I use Microsoft Access, but the procedure is similar for other Windows-based development environments.

Deciding what to track

The first step toward creating a database is to decide what you want to track. For example, imagine that you have just won $101 million in the Powerball lot-tery. (It’s OK to imagine something like this. In real life, it’s about as likely as finding your car squashed by a meteorite.) People you haven’t heard from in years, and friends you’d forgotten you had, are coming out of the woodwork. Some have surefire, can’t-miss business opportunities in which they want you to invest. Others represent worthy causes that could benefit from your sup-port. As a good steward of your new wealth, you realize that some business opportunities aren’t as good as others, and some causes aren’t as worthy as others. You decide to put all the options into a database so you can keep track of them and make fair and equitable judgments.

You decide to track the following items:

First name Last name Address City

State or province Postal code

Phone

How known (your relationship to the person) Proposal

Business or charity

You decide to put all the listed items into a single database table; you don’t need something elaborate. You fire up your Access 2003 development envi-ronment and stare at the screen shown in Figure.

Creating the table with Design View

The screen shown in Figure contains much more information than what previous-generation DBMS products displayed. In the old days (the 1980s), the typical DBMS presented you with a blank screen punctuated by a single-character prompt. Database management has come a long way since then, and determining what you should do first is much easier now. On the right side of the window, a number of options are displayed:

The Open pane lists databases that have been used recently.

The New pane enables you to launch a new blank database or select from a library of database templates.

The Search facility gives you access to several Microsoft resources.

The Spotlight section lists several things that you might want to do, such as enter a bug report or join a newsgroup.

Follow these steps to create a single database table in Access:

1. Open Access and then select Blank Database from the New pane. The File New Database dialog box appears.

2. Name the database you’re creating and save it in a folder.

The My Documents folder is the default choice, but you can save the database to any folder you want. For this example, choose the name POWER because you’re tracking data related to your Powerball winnings. The POWER Database window opens.

3. Select Create Table in Design View.

The second choice, Create Table Using Wizard, isn’t very flexible. The table-creating wizard builds tables from a list of predefined columns. The third choice, Create Table by Entering Data, makes many default assumptions about your data and is not the best choice for serious application development.

After double-clicking the Create Table in Design View option, the table creation window appears, as shown in Figure

4. Fill in the Field Name, Data Type, and Description information for each attribute for your table.

After you make an entry in the Field Name column, a drop-down menu appears in the Data Type column. Select the appropriate data types you want to use from the drop-down menu.

The bottom left of Figure shows the default values for some of the field properties. You may want to make entries for all the fields you can identify.

Access uses the term field rather than column. The original file-processing systems weren’t relational and used the file, field, and record terminol-ogy common for flat-file systems.

You may want to retain these values, or change them as appropriate. For example, the default value for the FirstName field is 50 characters, which is probably more characters than you need. You can save storage space by changing the value to something more reasonable, such as 15 characters. Figure shows the table creation window after all field entries are made.

5. Now that you’ve defined your table, save it by choosing File➪Save.

The Save As dialog box, shown in Figure, appears. Enter the name of the table you want to save. I named my table PowerDesign. The table is about your Powerball winnings, and it was created in Design view.

When you try to save your new table, another dialog box appears (see Figure), which tells you that you haven’t defined a primary key and asks if you want to define one now. I discuss primary keys in the section “Identifying a primary key,” later in this article. For now, just click the No button to save your table.

After you save your table, you may find that you need to tweak your original design, as I describe in the next section, “Altering the table structure.” So many people have offered you enticing business deals that a few of these folks have the same first and last names as other people in the group. To keep them straight, you decide to add a unique proposal number to each record in the database table. This way, you can tell one David Lee from another.

Altering the table structure

Often, the database tables you create need some tweaking. If you’re working for someone else, your client may come to you after you create the database and tell you she wants to keep track of another data item — perhaps several more.

If you’re building a database for your own use, deficiencies in your structure inevitably become apparent after you create the structure. For example, say you start getting proposals from outside the United States and need to add a Country column. Or you decide that you want to include e-mail addresses. In any case, you must go back in and restructure what you created. All RAD tools have a restructure capability. To demonstrate a typical one, I show you how to use Access to modify a table. Other tools have comparable capabilities and work in a similar fashion.

You may need to add unique proposal numbers so that you can distinguish between proposals from different people who have the same name. While you’re at it, you may as well add a second Address field for people with com-plex addresses, and a Country field for proposals from other countries.

To insert a new row and accommodate the changes, do the following:

1. In the table creation window, put the cursor in the top row, as shown in Figure, and choose Insert➪Rows.

A blank row appears at the cursor position and pushes down all the existing rows.

2. Enter the column headings you wish to add to your table.

I used ProposalNumber as the Field Name, AutoNumber as the Data Type, and a unique identifier for each row of the PowerDesign table as the Description. The AutoNumber data type is a numeric type that is automatically incremented for each succeeding row in a table. In a simi-lar way, I added an Address2 field below the Address field and a Country field below the PostalCode field. Figure shows the result.

Identifying a primary key

A table’s primary key is a field that uniquely identifies each row. ProposalNumber is a good candidate for PowerDesign’s primary key because it uniquely identifies each row in the table. It’s the only field that you can be sure isn’t duplicated somewhere in the table. To designate it as the table’s primary key, place the cursor in the ProposalNumber row of the table cre-ation window, and then click the Primary Key icon in the center of the Table Design toolbar (it’s the icon with the key on it). The key icon is now in the left-most column of the table creation window, as shown in Figure. This indicates that ProposalNumber is the primary key of the PowerDesign table.

Creating an index

Because the number of investment and charitable proposals you receive could easily grow into the thousands, you need a quick way to access records of interest. You can accomplish this task in a variety of ways. Say, for exam-ple, that you want to look at all the proposals from your brothers. Assuming none of your brothers have changed their last names for theatrical or profes-sional reasons, you can isolate these offers by basing your retrieval on the contents of the LastName field, as shown in the following example:

SELECT * FROM PowerDesign

WHERE LastName = ‘Marx’ ;

That strategy doesn’t work for the proposals made by your brothers-in-law, so you need to look at a different field, as shown in the following example:

SELECT * FROM PowerDesign

WHERE HowKnown = ‘brother-in-law’ ;

SQL scans the table a row at a time, looking for entries that satisfy the WHERE clause. If PowerDesign is large (tens of thousands of records), these queries may not work quickly. You can speed things up by applying indexes to the PowerDesign table. (An index is a table of pointers. Each row in the index points to a corresponding row in the data table.)

You can define an index for all the different ways that you may want to access your data. If you add, change, or delete rows in the data table, you don’t need to re-sort the table — you need only to update the indexes. You can update an index much faster than you can sort a table. After you establish an index with the desired ordering, you can use that index to access rows in the data table almost instantaneously.

Because ProposalNumber is unique as well as short, using that field is the quickest way to access an individual record. For that reason, the primary key of any table should always be indexed; Access does this automatically. To use this field, however, you must know the ProposalNumber of the record you want. You may want to create additional indexes based on other fields, such as LastName, PostalCode, or HowKnown. For a table that you index on LastName, after a search finds the first row containing a LastName of Marx, the search has found them all. The index keys for all the Marx rows are stored one right after another. You can retrieve Chico, Groucho, Harpo, Zeppo, and Karl almost as fast as Chico alone.

Indexes add overhead to your system, which slows down operations. You must balance this slowdown against the speed you gain by accessing records through an index. It usually pays off to index fields that you frequently use to access records. Creating indexes for fields that you never use as retrieval keys costs you something, but you gain nothing. Creating indexes for fields

that don’t differentiate one record from another also makes no sense. The BusinessOrCharity field, for example, merely divides the table records into two categories; it doesn’t make a good index.

The effectiveness of an index varies from one implementation to another. If you migrate a database from one platform to another, the indexes that gave the best performance on the first system may not perform the best on the new platform. In fact, the performance may be worse than if you hadn’t indexed the database at all. You must optimize your indexes for each DBMS and hardware configuration. Try various indexing schemes to see which one gives you the best overall performance, and consider both retrieval speed and update speed.

To create indexes for the PowerDesign table, click the Indexes icon located to the right of the Primary Key icon in the Table Design toolbar. The Indexes dialog box appears and already has entries for PostalCode and ProposalNumber. Figure shows the Indexes dialog box.

Access automatically creates an index for PostalCode because that field is often used for retrievals. It automatically indexes the primary key as well.

You can see that PostalCode isn’t a primary key and isn’t necessarily unique; the opposite is true for ProposalNumber. Create additional indexes for LastName and HowKnown, because they’re likely to be used for retrievals. Figure 4-11 shows how these new indexes are specified.

After you create all your indexes, you can save the new table structure by choosing File➪Save or by clicking the diskette icon on the Table Definition toolbar.

If you use a RAD tool other than Microsoft Access, the specifics that I describe in this section don’t apply to you. You would execute a roughly equivalent procedure, however, to create a database table and its indexes with a different RAD tool.

Deleting a table

In the course of creating a table such as PowerDesign with the exact structure you want, you may create a few intermediate versions along the way. Having these variant tables on your system may confuse people later, so delete them now while they’re still fresh in your mind. To do so, select the table that you want to delete and click the X icon in the menu bar of the database window, as shown in Figure.

Access asks you whether you really want to delete the selected table. Say you do, and it’s permanently deleted.

If Access deletes a table, it deletes all subsidiary tables as well, including any indexes the table may have.