Chapter 2 Relational Databases Data Fields: Columns Data Records: Rows Data Table: Rows and Columns Database Relationships One-to-One Relationships One-to-Many Relationships Indexes—Key Fields Visual Basic Database Field Types Building the Visual Basic 4.0 Field Data Types Project Testing the BOOLEAN Data Type Testing the BYTE Data Type Testing the CURRENCY Data Type Testing the DATE Data Type Testing the COUNTER Data Type Additional Visual Basic Data Types Summary Quiz Exercises Day 2 Creating Databases In today's chapter, you will learn a working definition of a relational database, as well as the basic elements of a database, including data table, data record, and data field. You will also learn the importance of establishing and maintaining data relationships. These are some of the key elements to developing quality databases for your applications. You will also learn Visual Basic database field types, including their names, storage sizes, and common uses. Along the way, you will create a programming project that explores the limits, possibilities, and common uses of Visual Basic database field types. Relational Databases Before looking at the individual components of relational databases, let's first establish a simple definition. For the purposes of this book, a relational database is defined as "a collection of data that indicates relation among data elements;" or, to put it even more directly, a relational database is "a collection of related data." In order to build a collection of related data, you need three key building blocks. These building blocks are as follows (from smallest to largest): Data Fields (somtimes called Data Columns) Data Records (also known as Data Rows) Data Tables Let's look at each of these elements in more depth. Data Fields: Columns The first building block in a relational database is the data field. The data field contains the smallest element of data that you can store in a database, and each field contains only one data element. For example, if you want to store the name of a customer, you must create a data field somewhere in the database and also give that field a name, such as CustomerName. If you want to store the current account balance of a customer, you must create another field, possibly calling it AccountBalance. All the fields you create are stored in a single database (see Figure 2.1). NOTE:In formal database theory, a data field is often referred to as a data column. Throughout this book, the phrases data field and data column are used interchangeably. Although it is possible to store more than one data element in a single field (such as first and last name), it is not good database practice to do so. In fact, storing more than one data element in a field can lead to problems when you or other users try to retrieve or update data. This concept seems simple in theory, but it's not so easy in practice. The CustomerName field discussed earlier is a good example. Assume that you have a database that contains a list of your customers by name, and you need to sort the list by last name. How would this be done? Can you assume that each CustomerName data field contains a last name? Do some contain only a first name? Possibly some contain both first and last name—but in what order (last name, first name or first name, last name)?. When you look at this situation, you discover that you're actually storing two data elements in the CustomerName field (First name and last name). For this reason, many databases contain not just the CustomerName data field, but data fields for LastName and FirstName. Figure 2.1 Examples of data fields in a database. When you begin constructing your database, spend time thinking about the various ways you (and your users) will need to retrieve useful data. The quality and usefulness of your database rests on the integrity of its smallest element—the data field. Data Records: Rows Data records are a collection of related data fields. To use the example started earlier, a Customer Record could contain the fields LastName, FirstName, and AccountBalance. All three fields describe a single customer in the database. NOTE:Formal database theory refers to a data record as a data row. Both data record and data row are used interchangeably throughout this book. A single data record contains only one copy of each defined data field. For example, a single data record cannot contain more than one LastName data field. Data Table: Rows and Columns By combining data fields and data records, you create the most common element of relational databases—the data table. This element contains multiple data records, and each data record contains multiple data fields (see Figure 2.3). Just as each data record contains related data fields (LastName, FirstName, and AccountBalance), each data table contains related records. Data tables have meaningful names (Customer Table or Invoice Table, for example) in the same way that data fields have meaningful names (LastName, FirstName, AccountBalance, and so on). These names help you and other users to remember the contents of the elements (table elements and field elements). Figure 2.3 An example of a data table in a database. Database Relationships Just as a data record can contain several related data fields, a database can contain several related tables. Using relationships is a very efficient way to store complex data. For example, a table storing customer names could be related to another table storing the names of items the customer has bought, which could be in turn related to a table storing the names of all the items you have to sell. By establishing meaningful relationships between data tables, you can create flexible data structures that are easy to maintain. You establish relationships between data tables by using pointer or qualifier fields in your data table. You use qualifier fields to point to records in other tables that have additional information. Qualifier fields usually describe what's known as one-to-one relationships. A good example of a one-to-one relationship is the relationship between a single customer record and a single record in the shipping address table (see Figure 2.4). Figure 2.4 An example of a one-to-one relationship between tables. You use pointer fields to point to one or more records in other tables that have related information. Pointer fields usually describe what are known as one-to-many relationships. A good example of a one-to-many relationship is the relationship between a single customer master record and several outstanding customer orders (see Figure 2.5). Figure 2.5 An example of a one-to-many relationship between tables. One-to-One Relationships One-to-one relationships are used to link records in a master table (such as the Customer Table) to a single related record in another table. For example, assume you have two types of customers in your Company Database—retail and wholesale. Retail customers get paid commissions on sales, so you will need a Commission field added to the Customers table. Wholesale customers, however, purchase their products at a discount, so you also need to add a Discount field to the Customers table. Now your database users have to remember that, for Retail customers, the Discount field must be left empty, and for Wholesale customers, the Commission field must be left empty. You must remember these rules when adding, editing, and deleting data from the database, and you must remember these rules when creating reports. This might seem to be a manageable task now, but try adding dozens more data fields to store (along with the exceptions), and you'll have quite a mess on your hands! Instead of establishing all data fields for all customers, what you need is a way to define only the fields you need for each type of customer. You can do this by setting up multiple tables in a single database and then setting up relationships between the tables. Figure 2.6 Using a qualifier field to establish a one-to-one relationship. In the example illustrated in Figure 2.6, you have added an additional data field—Type. This data field qualifies, or describes, the type of customer stored in this data record. You can use this type of information to tell you where to look for additional information about the customer. For example, if the Type field is set to Retail, you know you can look for the customer in the Retail Table to find additional information. If the Type field is set to Wholesale, you can find additional information in the Wholesale Table. By creating the RecordType field, you can establish a one-to-one relationship between records in the Customer Table and the Retail and Wholesale Tables. One-to-Many Relationships One-to-many relationships are used to link records in a master table (such as the Customer Table) to multiple records in another table. For example, you can keep track of several orders for each customer in your database. If you were not creating a relational database, you would probably add a data field to your customer table called Order. This would contain the last order placed by this customer. But what if you needed to keep track of more than one outstanding order? Would you add two, four, or six more order fields? You can see the problem. Instead, you can add an additional table (the Orders Table) that can contain as many outstanding orders for a single customer as you need. After you create the Orders Table, you can establish a relationship between the Customer Table and the Orders Table using the LastName field (refer back to Figure 2.4). The LastName field is used as a pointer into the Orders Table to locate all the orders for this customer. You can use many different ways to establish relationships between tables. This is usually done through a key field. Key fields are covered in depth in the next section. Indexes—Key Fields Usually, at least one data field in each data table acts as a key field for the table. Key fields in relational databases are used to define and maintain database integrity and to establish relationships between data tables. You create keys in your data table by designating one (or more) fields in your table as either a primary key or a foreign key. A data table can only have one primary key, but it can have several foreign keys. The primary key is used to control the order in which the data is displayed. The foreign key is used to relate fields to fields in other (foreign) tables in the database. NOTE:Key fields are sometimes referred to as index fields or indexes. Both key fields and index fields will be used interchangeably throughout the book. It is important to note that in most PC databases (xBase, Paradox, Btreive, and so forth), indexes are used only to speed processing of large files and play only a minor role in maintaining table relationships. The Visual Basic database model (.mdb files) and other true relational database models use key fields to establish database integrity rules as well as speed database search and retrieval. As mentioned earlier, a data table can have only one primary key. The primary key is used to define a unique record in the data table. In the Customer table, the LastName field is the primary key field for the data table. This means that no two records in that table can have exactly the same value in the LastName fields (see Figure 2.7). Any attempt to add more than one record with an identical primary key would result in a database error. TIP:The main role of the primary key is to maintain the internal integrity of a data table. For this reason, no two records in a data table can have the same primary key value. Many companies with large customer bases use social security numbers or area codes and telephone numbers, because they know they are likely to have more than one customer with the same name. In these cases, the SSN or phone number would be the primary key field. Figure 2.7 The LastName field is the primary key field of the Customer Table. A data table can have more than one foreign key. It can also have no foreign key at all. In the Orders Table, the LastName field would be defined as a foreign key field. This means that it is a nonunique field in this data table that points to a key field in an external (foreign) table. Any attempt to add to the Orders table a record that contains a value in the LastName field, which does not also exist in a LastName field in the Customer Table, would result in a database error. For example, if the Customer table contains three records ("Smith", "Amundsen", and "Jones"), and you try to add a record to the Orders Table by filling the LastName field of the Orders Table with "Paxton," you would get a database error. By creating foreign key fields in a table, you build data integrity into your database. This is called referential integrity. TIP:The main role of a foreign key is to define and maintain relationships between data tables in a database. For this reason, foreign key fields are not unique in the data table in which they exist. NOTE:Database integrity and foreign keys are covered in depth on Day 17, "Database Normalization," and Day 18, "Multiuser Considerations." Now that you've worked through the basics of database elements in general, let's look at specific characteristics of Visual Basic data fields. Visual Basic Database Field Types Visual Basic stores values in the data table in data fields. Visual Basic recognizes 13 different data field types that you can use to store values. Each data field type has unique qualities that make it especially suitable for storing different types of data. Some are used to store images, the results of checkbox fields, currency amounts, calendar dates, and various sizes of numeric values. Table 2.1 lists the 12 database field types recognized by Visual Basic. Table 2.1. Visual Basic data field types. Data Field Type Size VBType Comments BINARY V (none)No equivalent VBType BOOLEAN 1 Boolean Stores 0 or -1 only BYTE 1 Integer Stores 0 to 255 only COUNTER 8 Long Auto-incrementing CURRENCY 8 Currency Scaled integer DATETIME 8 Date/Time Stored as a VBDouble DOUBLE 8 Double INTEGER 2 Integer LONG 8 Long LONGBINARY V (none) No equivalent VBType MEMO V String Length varies up to 1.2 gigabytes SINGLE 4 Single TEXT V String Length varies up to 255 The first column contains the Visual Basic data field type name. This is the name you use when you create data tables using the Visual Basic Data Manager from the Toolbar. You'll learn about using this tool in Day 3, "Using the Data Manager." The second column shows the number of bytes of storage taken by the various data field types. If the size column is set to "V," the length is variable and is determined by you at design time or by the program at runtime. The third column in the table shows the equivalent Visual Basic data type for the associated database field type. This column tells you what Visual Basic data type you can use to update the database field. NOTE:It is important to understand the difference between the Visual Basic data field types and the Visual Basic data types. The data field types are those recognized as valid data types within data tables. The data types are those types recognized by Visual Basic when defining variables within a program. For example, you can store the value 3 in a BYTE field in a data table, but you store that same value in an Integer field in a Visual Basic program variable. Even though it is true that Visual Basic allows programmers to create database applications that can read and write data in several different data formats, all database formats do not recognize all data field types. For example, xBase data fields do not recognize a CURRENCY data field type. Before developing cross data-engine applications, you'll need to know exactly what data field types will be needed and how they are to be mapped to various data formats. The various data formats are covered in Day 10, "Visual Basic and the Microsoft JET Engine." A number of things in Table 2.1 deserve additional comment. LONGBINARY data fields are for storing images and OLE objects. Visual Basic has no corresponding internal data type that maps directly to the LONGBINARY data field types. This information is usually stored as character data in Visual Basic. For example, a bitmap image would be stored in a LONGBINARY data table field, but it would be stored as a string variable in a Visual Basic program. The BOOLEAN data field type is commonly used to store the results of a bound checkbox input control. It stores only a -1 (True) or 0 (False). For example, if you enter a 13 into the input box, Visual Basic will store -1 in the data field. To make matters trickier, Visual Basic will not report an error when a number other than 0 or -1 is entered. You should be careful when using the BOOLEAN data type because any number other than 0 entered into a BOOLEAN data field will be converted into -1. The BYTE data field type will accept input ranging from -32,768 to 32767 without reporting an error. However, if the input is outside the range of 0 to 255, Visual Basic will store only the right-most byte of the number. For example, if you enter the value 255 (stored as FF in hexadecimal), Visual Basic will store 255 in the data field. If you enter 260 (stored as 0104 in hexadecimal—it takes two bytes!), Visual Basic will store a decimal 4 in the data field because the right-most byte is set to hexidecimal 04. The COUNTER data field type is a special case. This is an auto-incrementing, read-only data field. Any attempt to write a value to this data field will result in a Visual Basic error. Visual Basic keeps track of the integer value to place in this field; it cannot be altered through the input controls or through explicit programming directives. The COUNTER field is often used as a unique primary key field in sequential processing operations. MEMO and TEXT data field types both accept any character data as valid input. MEMO data fields are built with a default length of 0 (zero). The physical length of a MEMO field is controlled by the total number of characters of data stored in the field. The length of a TEXT field must be declared when the data field is created. The Data Manager that is shipped with Visual Basic allows the TEXT field to have a length of 1 to 255 bytes. Building the Visual Basic 4.0 Field Data Types Project The following project will illustrate how different Visual Basic data field types store user input. You will also see how Visual Basic responds to input that is out of range for the various data field types. Begin by creating a new Visual Basic project (select File | New Project). Using Table 2.2 and Figure 2.8 as guides, populate the Visual Basic form. Table 2.2. Controls for the Visual Basic Data Field Types project. Control Property Setting FormCaptionNameVisual Basic Data Field TypesfrmFieldTypes CommandButtonCaptionName&AddcmbAdd CommandButtonCaptionName&DeletecmdDelete CommandButtonCaptionNamee&xitcmbExit DdataControlDatabaseNameRecordSourceNameDATATYPE.MDB (include correct path)FieldTypesdatFieldTypes Figure 2.8 The form for the Visual Basic Data Field Types project. Now add the code behind the Add button. Double-click on the add button to bring up the code window. The following line is all you need to add a new record to the data table. Sub cmdAdd_Click () datFieldTypes.Recordset.AddNew End Sub Next, add the code line behind the Delete button. Sub cmdDelete_Click () datFieldTypes.Recordset.Delete End Sub Finally, add the single Visual Basic keyword End behind the Exit button. Sub cmdExit_Click () End End Sub Now is a good time to save the project. Save the form as DATATYPE.FRM and the project as DATATYPE.VBP. Run the project just to make sure that you have entered all the code correctly up to this point. If you get error messages from Visual Basic, refer back to Table 2.2 and the preceding code lines to correct the problem. Testing the BOOLEAN Data Type Now you can add a text box input control and a label to this form. Set the caption of the label to BOOLEAN:. Set the DataSource property of the text box to datFieldTypes and the DataField property to BOOLEAN. Set the Text property to be blank. Refer to Figure 2.9 for placement and sizing. Figure 2.9 Adding the BOOLEAN data type input control. Now run the program. You'll see that the first value in the input box is a 0, the default value for new records. Enter the number 13 in the text box and click on the left-most arrow button on the data control. This will force the data control to save the input field to the data table and update the display. What happened to the 13? It was converted to -1. Any value other than 0, when entered into a BOOLEAN data type field, will be converted to -1. Testing the BYTE Data Type Now let's add a label and input control for the BYTE data type field. Instead of picking additional controls from the Toolbox Window and typing in property settings, Visual Basic allows you to copy existing controls. Copying controls saves time, reduces typing errors, and helps to keep the size and shape of the controls on your form consistent. To copy controls, use the mouse pointer, with the left mouse button depressed, to create a dotted-line box around both the label control and the text box control already on your form (in this case, the label Boolean and its text box). When you release the left mouse button, you'll see that both controls have been marked as selected. Now click on Edit | Copy to copy the selected controls to the Clipboard. Now just use Edit| Paste to copy the controls from the Clipboard back onto you form. At this point, Visual Basic asks you whether you want to create a Control Array. Say yes, both times. You then see the two controls appear at the top left of the form. Use your mouse to position them on the form (see Figure 2.10). TIP:The Textbox and Label controls on this form are part of a control array. Because using control arrays reduces the total number of distinct controls on your forms, using control arrays reduces the amount of windows resources your program uses. You can copy controls as many times as you like—even across forms and projects! Figure 2.10 Copying controls on a form. You just created duplicates of the BOOLEAN input control. All you need to do now is change the label caption to BYTE: and the text box DataField property to BYTE, and you have two new controls on your form with minimal typing. Your form should now look like the one in Figure 2.11. Figure 2.11 Adding the BYTE data type to your form. Now save and run the program. This time, enter the value 256 into the Byte input control and hit the left-most arrow button. You'll see that when Visual Basic stored the numeric value into the BYTE data type field, it converted the 256 back to 0. BYTE data type fields can only store from 0 to 255. Try entering a negative number in the Byte text box and see what value gets stored to the data table. Testing the CURRENCY Data Type Copy the label and text box control again using the mouse to select the controls for copy, and the Copy and Paste commands from the Edit menu. Change the label caption property to CURRENCY: and the text box DataField property to CURRENCY. Refer to Figure 2.12 for spacing and sizing of the controls. Figure 2.12 Adding the CURRENCY data type to the form. Save and run the program and test the CURRENCY data type text box. Enter the value 1.12345, force Visual Basic to save the value to the data table (press the left-most arrow button), and see what happens. Try entering 1.00001. When storing values to the CURRENCY data type field, Visual Basic will only store four places to the right of the decimal. If the number is larger than four decimal places to the right, Visual Basic will round the value before storing it in the data field. Also, you'll notice that Visual Basic does not add a dollar sign ($) to the display of CURRENCY type data fields. Testing the DATE Data Type The Visual Basic DATE data type field is one of the most powerful data types. Visual Basic will perform extensive edit checks on values entered in the DATE data type field. Using DATE data type fields can save a lot of coding when you need to make sure valid dates are entered by users. Create a new set of label and text box controls by copying the label and text box controls again. Change the label caption property to DATE: and the text box DataField property to DATE. Your form should look like the one in Figure 2.13. Figure 2.13 Adding the DATE data type to the form. Save and run the program. Try entering 12/32/95. As you can see, Visual Basic gives you an error message whenever you enter an invalid date. Now enter 1/1/0 into the Date text box. Notice that Visual Basic formats the date for you. How does Visual Basic decide what date format to use? The date format used by Visual Basic comes from the settings in the Windows 95 Control Panel Regional Settings applet. While you have this program running, experiment by calling up the Windows 95 Regional Settings applet. (From the task bar, select Start | Settings | Control Panel, and then select Regional Settings.) Change the date format settings, and return to your Visual Basic program to see the results. TIP:The Visual Basic DATE data type should always be used to store date values. If you install your program in Europe, where the common date display format is DD-MM-YY instead of the common US format of MM-DD-YY, your program will work without a problem. If you store dates as strings in the format MM/DD/YY or as numeric values in the format YYMMDD, your program will not be able to compute or display dates correctly across international boundaries. Testing the COUNTER Data Type You can test one more Visual Basic data type—the COUNTER data type. This data type is automatically set by Visual Basic each time you add a new record to the data table. The COUNTER data type makes an excellent unique primary key field because Visual Basic is able to create and store more than a billion unique values in the COUNTER field without duplication. Copy another label/text box control set onto the form. Change the label caption property to Counter and the text box DataField property to COUNTER. See Figure 2.14 for positioning and sizing. Figure 2.14 Adding the COUNTER data type to the form. Now save and run the program one more time. Notice that the COUNTER data type already has a value in it, even though you have not entered data into the field. Visual Basic will set the value of COUNTER fields; users do not. Add a new record to the table by pressing the Add button. You'll see a new value in the COUNTER input control. Visual Basic will use the next available number in sequence. Visual Basic is also able to ensure unique numbers in a multiuser setting. If you have three people running the same program adding records to this table, they would all receive unique values in the Counter text box. CAUTION:You should never attempt to edit the value in the COUNTER Textbox! If Visual Basic determines that the counter value has been changed, it displays a Visual Basic error message, and you will be unable to save the record. Even if you reset the value in the COUNTER data field back to its original value, Visual Basic will refuse to save the record. Additional Visual Basic Data Types The rest of the Visual Basic data types (INTEGER, SINGLE, DOUBLE, TEXT, MEMO, BINARY, and LONGBINARY) are rather unspectacular when placed on a form. The following are some notes on the various Visual Basic data types that you should keep in mind when you are designing your data tables. Visual Basic will return an error if you enter more than the maximum number of characters into a TEXT data field. You will get the same error message if you pass the Visual Basic 32K input limit on the MEMO data field. The BINARY and LONGBINARY data fields are meant to store graphic image data and will allow any alphanumeric data to be entered and saved. The storage of graphic data is covered later in the book (see Day 12). Check the Visual Basic online help under Visual Basic Data Types for additional information on the high and low ranges for DOUBLE, INTEGER, and SINGLE data fields. BOOLEAN and BYTE data fields will allow you to enter values beyond their expected range without reporting an error. Notice that Visual Basic will alter the data you entered into these data fields without telling you! The CURRENCY data field only stores the first four places to the right of the decimal. If you enter values beyond the fourth decimal place, Visual Basic will simply truncate the value to four decimal places and give you no warning. The DATE data field has some interesting behavior. Visual Basic will not let you store an invalid date or time in the data field; you receive a Type mismatch error instead. Also, the display format for the dates and times is determined by the settings you choose in the Windows Control Panel (through the International icon). In fact, when valid data is stored in a DATE data field, you can change the display format (say from 12-hour time display to 24-hour time display), and the next time you view that data record, it will reflect the changes made through the Control Panel. Summary Today you learned the following about relational databases: A relational database is "a collection of related data." The three key building blocks of relational databases are data fields, data records, and data tables. The two types of database relationships are one-to-one, which uses qualifier fields, and one-to-many, which uses pointer fields. There are two types of key (or index) fields: primary key and foreign key. You also learned the 13 data field types recognized by Visual Basic. You constructed a data entry form that allows you to test the way Visual Basic behaves when attempting to store data entered into the various data field types. Quiz What are the three main building blocks for relational databases? What is the smallest building block in a relational database? A data record is a collection of related __________. What is the main role of a primary key in a data table? Can a data table have more than one foreign key defined? List all the possible values that can be stored in a BOOLEAN data field. What is the highest value that can be stored in a BYTE data field? What happens when you attempt to edit a COUNTER data field? How many places to the right of the decimal can be stored in a CURRENCY data field? What Windows Control Panel Applet determines the display format of DATE data fields? Exercises Answer questions 1, 2, and 3 based on the data in this table: SSNLastFirstAgeCity