|
(a)8 [lb] Getting Started with Relational
Databases [lb] Rule Numero Uno: Create a Primary Key
for Each Table! [lb] Using Database Tools to Explore and
Manipulate Tables [lb] The Fields Editor [lb] Calculated Fields [lb] TDBGrid at Runtime [lb] Working with TDBGrid Columns [lb] Lookup Fields [lb] Multirecord Objects This chapter covers a set of visual tools you can use to simplify database development. The major areas of concentration are as follows: [lb] Relational databases [lb] The Fields Editor [lb] TField descendant objects [lb] Calculated fields [lb] The TDBGrid component [lb] Lookup fields [lb] The Database Explorer [lb] The Database Desktop [lb] Query by Example (QBE) [lb] MultiRecord objects[md]TDBCtrlGrid Using One of the most frequently mentioned tools in this chapter is the Fields Editor. By using the Fields Editor, you can create objects that you can use to influence the manner and types of data that appear in visual controls such as TDBEdit and TDBGrid. For instance, you can use the objects made in the Fields Editor to format data so that it appears as currency or as a floating-point number with a defined precision. These same changes can be accomplished through the Data Dictionary in the Database Explorer, or through the Database Desktop. These latter tools, however, have a global impact on the field's potential values, whereas the changes made in the Object Inspector affect only the current application. The Columns property of the TDBGrid control can be used to change the appearance of a grid so that its columns are arranged in a new order or are hidden. You can also use the Columns property to change the color of columns in a grid, or to insert drop-down combo boxes into a grid. The lessons you learn in this chapter demonstrate techniques used
by most programmers when they present database tables to their users. Much of
the material involves manipulating visual tools, but the basic subject matter
is fairly technical and assumes an understanding of the (c)Getting Started with Relational Databases Many different kinds of databases are possible, but in today's world, only two kinds have any significant market share for the PC: [lb] Flat-file databases [lb] Relational databases ***Begin Note*** Note Emerging in recent years has been a new system called object-oriented databases. These databases represent an interesting form of technology, but I will omit discussion of them here because they have a small user base at this time. Oracle has also made significant improvements to its database
model, many of which are supported by ***End Note*** A flat-file database consists of a single file. The classic example would be an address book that contains a single table with six fields in it: Name, Address, City, State, Zip, and Phone. If that is your entire database, what you have is a flat-file database. In a flat-file database, the words table and database are synonymous. In general, relational databases consist of a series of tables related to each other by one or more fields in each table. The Address program shown in Chapter 9, "Basic Database and ClientDataSet," is an example of a flat-file database. In Chapter 10, "Relational Databases," you will see a second program, called kdAdd, which is a relational database. Consider these three key differences between relational and flat-file databases: 1. A flat-file database, like the address book example outlined previously, consists of one single table. That's the whole database. There is nothing more to say about it. Each table stands alone, isolated in its own solipsistic world. 2. Relational databases always contain multiple tables. For
instance, the Customer and Orders tables are both part of the DBDEMOS
database that ships with 3. Tables in relational databases are tied together on special fields. These fields are called primary and foreign keys. These keys usually have indices, and they usually, but by no means always, consist of a simple integer value. For instance, the CustNo field relates the Customer and Orders tables to one another. The CustNo field is a primary key in the Customer table and a foreign key in the Orders table. Both fields also have indices. ***Begin Note*** Indices are about searching and sorting. Keys, on the other hand, are about relating tables, and particularly about something called referential integrity. In practice, these concepts get mixed together in some pretty ugly ways, but the underlying theory relies on the kinds of distinctions I am drawing in this note. For instance, keys are usually indexed, and so people often talk about keys and indices as if they were the same. However, they are distinct concepts. One way to start to draw the distinction is to understand that keys are part of the theory of relational databases, whereas indices are part of the implementation of relational databases. You'll learn more on this subject as the chapter evolves. ***End Note*** Clearly, relational databases are radically different from flat-file databases. Relational databases typically consist of multiple tables, at least some of which are related together by one or more fields. Flat-file databases, on the other hand, consist of only one single table, which is not related to any other table. (d)Advantages of the Relational
Database Model What advantages do relational databases have over flat-file databases? Well, this system has many strengths; the following are a few of the highlights: [lb] Relational databases enforce referential integrity. These constraints help you enter data in a logical, systematic, and error-free manner. [lb] Relational databases save disk space. For instance, the Customer table holds information about customers, including their address, phone, and contact information. The Orders table holds information about orders, including their date, cost, and payment method. If you were forced to keep all this information in a single table, each order would also have to list the customer information, which would mean that some customers' addresses would be repeated dozens of times in the database. In a big database, that kind of duplication can easily burn up megabytes of disk space. Using a relational database is better because each customer's address would be entered only once. You could also have two flat-file databases, one holding the customer information and the other holding the orders information. The problem with this second scenario is that flat-file databases provide no means of relating the two tables so that you can easily see which orders belong to which customer. [lb] Relational databases enable you to create one-to-many relationships. For instance, you can have one name that is related to multiple addresses. You cannot capture that kind of relationship in a simple way in a flat-file database. In the KDAdd program, you will see that you can easily relate multiple addresses, phone numbers, and so on, with each name. The flexible structure of relational databases enables programmers to adopt to these kinds of real-world situations. For many entries in a database, you will want to keep track of two addresses: one for a person's home, and the other for his or her work. If someone you know has a summer home or an apartment in the city, you need to add yet more addresses to the listing. You cannot add these addresses conveniently in flat-file databases. Relational databases handle this kind of problem with ease. In the preceding paragraph, I emphasized that this kind of feature saves space; in this paragraph, I'm emphasizing that it allows for a more logical, flexible, and easy-to-use arrangement of your data. A relational database offers these possibilities: 1. You can view the Customer table alone, or you can view the Orders table alone. 2. You can place the two tables in a one-to-many relationship so that you can see them side by side but see only the orders relating to the currently highlighted customer. 3. You can perform a join between the two tables so that you can see them as one combined table, much like the combined table you would be forced to use if you wanted to "join" the Customer and Orders table in a single flat-file database. However, you can decide which fields from both tables will be part of the join, leaving out any you don't want to view. The joined table is also temporary and does not permanently take up unnecessary disk space. In short, relational databases can use joins to provide some of the benefits of flat-file databases, whereas flat-file databases cannot emulate the virtues of relational databases. As you can see, the three concepts that stand out when talking about relational databases are referential integrity, flexibility, and conservation of disk space. In this case, the word flexibility covers a wide range of broad features that can only be fully appreciated over time. The one disadvantage that relational databases have when compared
to flat-file databases is that they are more complicated to use. This
disadvantage is not just a minor sticking point. Neophytes are often
completely baffled by relational databases. They don't have a clue as to what
to do with them. Even if you have a relative degree of expertise, you can
still become overwhelmed by a relational database that consists of three dozen
tables related to one another in some hundred different ways. (And yes,
complexity on that scale is not uncommon in corporate (d)Simple Set Logic: The Basis of
Relational Databases The basis for relational databases is a simple form of mathematics. In its simplest case, each table represents a set that can be related to other tables through fundamental mathematics. Because computers are so good at math, and particularly at integer math, they find relational databases easy to manipulate. One common feature of relational databases is that most records will have a unique number associated with them, and these numbers will be used as the keys that relate one table to another. These keys enable you to group tables together using simple mathematical relationships. In particular, you can group them using simple integer-based set arithmetic. I should add, however, that there is no reason why you can't use a nonnumeric field as a key. In fact, using such a field this way is common practice in many corporate databases. Evidence of this practice can be seen in the strange combination of letters and numbers you find in the serial numbers or account numbers on products and services you might use in your own home. However, you will find an admirable purity in the way tables relate when they are keyed on integer fields. In the Customer table from DBDEMOS, each record has a unique CustNo field. This CustNo field serves as a key. Furthermore, the Orders table has a unique OrderNo field associated with it. This field is also a key field. The Orders table also has a CustNo field that will relate it to the Customer table. The terminology of relational databases expresses these ideas by saying that the Customer table has a primary key called CustNo, and the Orders table has a primary key called OrderNo and a foreign key called CustNo, as shown here: Table Name Primary Key Foreign Key (Secondary Index) Customer CustNo Orders OrderNo CustNo Given this scenario, you can say: Show me the set of all orders such that their CustNo field is equal to X or within the range of X [nd] Y. Computers love these kinds of simple mathematical relationships. They are their bread and butter. In essence, you are just asking for the intersection of two sets: Show me the intersection of this record from the Customer table with all the records from the Orders table. This intersection will consist of one record from the Customer table with a particular CustNo plus all the records from the Orders table that have the same CustNo in their foreign key. These CustNo, OrderNo, AuthorNo, BookNo, and similar fields might also be used in flat-file databases as indices, but they play a unique role in relational databases because they are the keys used to relate different tables. They make it possible to reduce the relationship between tables to nothing more than a simple series of mathematical formulas. These formulas are based on keys rather than on indices. It is merely a coincidence that in most tables the keys also happen to be indexed. (d)Viewing Indices and Keys in DBD or
the Database Explorer In the next few sections, I define primary and secondary keys,
and describe how to use them. Prefacing this discussion with a brief
description of how to view keys using some of the tools that ship with You can view the indices and keys on a table in two ways. The best way is to use the Database Explorer. Open the Explorer and view the DBDEMOS database, as shown in Figure 8.1. *** Begin Note *** Note: The Database Explorer is also known as the SQL Explorer. I use the two terms interchangeably. ***End Note*** ***Insert Figure 8.1 09fig01 PC X Figure 8.1 Viewing the DBDEMOS database in the Database Explorer. Click the Orders table and open the Referential Constraints branch, as shown in Figure 8.2. Notice that the two constraints on this table: one called RefCustInOrders and the second called RefOrders. The RefCustInOrders field defines CustNo as a foreign key that relates to the CustNo field in the Customer table. ***Insert Figure 8.2 09fig02 PC X Figure 8.2 The primary and foreign fields of the Orders table. A second way to view this key is to use the Database Desktop. Set the Working Directory from the File menu to the DBDEMOS alias. Open the Orders table in the Database Desktop, and select Table, Info structure. Drop down Table Properties and select Referential Integrity, as shown in Figure 8.3. ***Insert Figure 8.3 09fig03 PC X Figure 8.3 Selecting Referential Integrity in the Database Desktop. Double-click RefCustInOrders to bring up the Referential Integrity dialog, as shown in Figure 8.4. ***Insert Figure 8.4 09fig04 PC X Figure 8.4 The CustNo field in the Orders table relates to the CustNo field in the Customer table. The fields on the left side of this dialog belong to the Orders table. On the right is a list of all the tables in the database. In the center, you can see that the CustNo field has been selected from the Orders table, and the CustNo field has been selected from the Customer table. The primary key of the Customer table is related to the foreign key of the Orders table. Now go back to the Database Explorer and open the Indices branch of the Orders table, as shown in Figure 8.5. ***Insert Figure 8.5 09fig05 PC X Figure 8.5 The primary and CustNo indices on the Orders table. Note that you can see the names of the indices, here labeled as <primary> and as CustNo. The fields found in the indices are also displayed. For instance, you can see that the primary index consists of the OrderNo field, and the secondary index consists of the CustNo field. I am showing these figures to you so that you will begin to see the distinction between keys and indices. The two concepts are distinct. For further proof, open the IBLOCAL database in the Database Explorer. Use SYSDBA as the username and masterkey as the password. Now open the Employee Project table, as shown in Figure 8.6. Note the separate listings for the index, primary key, and foreign key. ***Insert Figure 8.6 09fig06 PC X Figure 8.6 The Employee_Project table has three indices, one primary key, and two foreign keys. You can also see the indices for a table inside the Database Desktop. To get started, open the Orders table and select Table, Info Structure. The fields with the stars beside them are part of the primary index. Drop down the Table Properties combo box to view the secondary indices. Double-click the indices you see to view the details of their design. If you want to change the structure of a table, choose Table, Restructure rather than Table, Info Structure. Most of the time, I find the Database Desktop is the right tool
to use when I want to create or modify a table, and the Database Explorer is
the right tool to use when I want to view the structure of a table. I find
the Database Explorer loads faster than the Database Desktop. However, I
often find myself jumping back and forth between the two tools to get the
best features of each. Third-party CASE tools are generally superior to
either of the products discussed in this section. However, no CASE tools ship
with Throughout the ensuing discussion, you might have occasion to use the Database Explorer to examine the structure of the Customer, Orders, Items, and Parts tables. I use these tables when defining what relational databases are all about. (c)Rule Numero Uno: Create a Primary Key for Each Table! The preceding two sections introduced you to some of the key concepts in relational databases. Based on this information, you should begin to see the importance of creating a unique key in the first field of most tables you create. This field is called a primary key. In both Paradox and InterBase, creating a primary key without also simultaneously creating an index is impossible. If you want to have a list of addresses in a table, don't just list the Address, City, State and Zip. Be sure to also include a CustNo, AddressNo, or Code field. This field will usually be both an index and the first field of the database. It is the primary key for your table and must be, by definition, unique. That is, each record should have a unique Code field associated with it. This field need not be an integer value, but an integer is a logical choice for this kind of field. The primary key does the following: [lb] It serves as the means of differentiating one record from another. [lb] It is also used in referential integrity. [lb] Because it is usually indexed, it can also help with fast searches and sorts. As I said earlier, the distinction between indices and keys becomes blurred at times. However, they are distinct concepts, and you should struggle to discover the differences. Just to make sure this information is clear, I'll list the right and wrong way to create a table: Right Method CustNo: Integer LastName, FirstName, Address, City, State, Zip: string Wrong Method LastName, FirstName, Address, City, State, Zip: string The first example is "correct" because it has a primary index called CustNo. It is declared as a unique Integer value. The second example is "wrong" because it omits a primary index. I put the words correct and wrong in quotation marks because this discipline really doesn't have any hard-and-fast rules. On some occasions, you might not want to create a table that has a primary index. However, 99 percent of the time, that's exactly what you want to do. At the height of warm May spring day, there is such a thing as a rose bush that has no buds or flowers. However, the whole point of rose bushes in May is that they flower. I doubt we would feel quite the same way about roses if they did not have beautiful blooms. In the same way, relational databases without primary indices wouldn't garner quite so much attention as they do now. Even if you don't yet understand how relational databases work, for now I suggest that you automatically add a simple numerical value in a primary index to all your tables. Do so even if you are not using the field at this time. Believe me, as you come to understand relational databases, you will see why I recommend adding this value in most, though not all, cases. At this point, however, you will probably be better off creating the extra field and letting it go to waste, even if you don't understand why you are doing it. After you get a better feeling for relational databases, you will understand intuitively when the field is needed, and when you are encountering one of those rare occasions when it is going to be useless. When people first work with relational databases, they can get a little hung up about the overhead involved in creating all these extra key fields. The point to remember is that these fields allow the database to be treated as nothing more than sets related together in various combinations. Computers fly through integer math and in general can easily relate tables together on keys. Adding these extra index fields to your tables makes your data become computer friendly. Computers love these keyed fields; your computer will show its thanks by running faster if you add them to your tables. Computers don't feel weighed down by the extra field any more than a car feels weighed down by a steering wheel, people feel weighed down by their hands, or a rose bush feels weighed down by a rose. Relational databases want you to add an extra field as a primary index to your tables. Remember, people like beautiful paintings, eloquent words, lovely members of the opposite sex. Computers like logic. They like numbers; they like nice, clean, easily defined relationships. They like primary keys in the first field of a table. (d)One-to-Many
Relationships: The Data
and the Index One good way to start to understand relational databases is by working with the Customer, Orders, Items, and Parts tables from the DBDEMOS database. All four of these tables are related in one-to-many relationships, each-to-each. That is, the Customer table is related to the Orders table, the Orders table to the Items table, and the Items table to the Parts table. (The relationship also works in the opposite direction, but it may be simpler at first to think of it as going in only one direction.) Master Detail Connector (Primary Key and Foreign Key) Customer Orders CustNo Orders Items OrderNo Items Parts PartNo Read the preceding table as a series of rows, starting left and moving to the right, as if they were sentences. The preceding list shows that the Customer and Orders tables are related in a one-to-many relationship, with Customer being the master table and Orders being the detail table. The connector between them is the CustNo field. That is, they both have a CustNo field. The CustNo field is the primary key of the Customer table and the foreign key of the Orders table. The OrderNo field is the primary key of the Orders table and a foreign key of the Items table. The PartNo field is the primary key of the Parts table and a foreign key of the Items table. You can see all these relationships in action by running the Relate program from the CD that accompanies this book. The relationship between these tables can be reversed. For instance, the Parts table could become the master table and the Items table the detail table, and so on, back down the line. The reason you can reverse the relationship becomes clear when you think in purely mathematical terms. The Customer table has a series of CustNo fields. Say the CustNo for the first record is 1000. To get the orders associated with that customer, you ask this question: "What are all the rows from the Orders table that have a CustNo of 1000?" That is: Select * from Orders where CustNo = 1000 Clearly, you could reverse this question. If you select a particular row from the Orders table, you could find which item from the Customer table it is related to by asking for the set of all Customer records with a CustNo of 1000. Because the CustNo field for the Customer table is a unique index, you will get only one record back. However, the way you relate the tables is still the same: Select * from Customer where CustNo = 1000 (d)Working with Primary Keys and Primary Indices The Parts, Orders, Items, and Customer tables have various keys. As it happens, these keys are also indices. An index enables you to sort tables on a particular field. A key helps you define the relationship between two tables or otherwise group related bits of information by a set of predefined and automatically enforced rules. Unfortunately, sadly, and confusingly, you can still relate tables even without the presence of any keys or indices. For instance, if no CustNo primary and foreign keys appeared in the Customer and Orders tables, Paradox would still let you use SQL to relate the tables in a one-to-many relationship. However, in this scenario, performance would be slow because you have no index, and you have no constraints on the data you could enter in the two tables because no primary and foreign keys define referential integrity. You are back to the rose bush without a rose phenomena. The tables are still part of a relational database, but they lack the features that make a relational database appealing. You need both the keys and the indices to make a relational database appealing. I'll draw a distinction between only two differe |