|  | Click Here!

Search the site:
EXPERT SEARCH

| Programming Languages
| Databases
| Security
| Web Services
| Network Services
| Middleware
| Components
| Operating Systems
| User Interfaces
| Groupware & Collaboration
| Content Management
| Productivity Applications
| Hardware
| Fun & Games

EarthWeb sites
Crossnodes
Datamation
Developer.com
DICE
EarthWeb.com
EarthWeb Direct
ERP Hub
Gamelan
GoCertify.com
HTMLGoodies
Intranet Journal
IT Knowledge
IT Library
JavaGoodies
JARS
JavaScripts.com
open source IT
RoadCoders
Y2K Info

#

- Day 2
  - Creating Databases
    - Relational Databases
      - Data Fields
        - Figure 2.1.
      - Data Records
        - Figure 2.2.
      - Data Table Rows and Columns
        - Figure 2.3.
      - Database Relationships
        - Figure 2.4.
        - Figure 2.6.
      - Key Fields
        - Figure 2.7.
    - Visual Basic Database Field Types
      - Building the Visual Basic 5 Field Data Types Project
        - Figure 2.8.
    - Listing 2.1. Code for the cmdBtn_Click event.
      - Figure 2.9.
      - Figure 2.10.
      - Figure 2.11.
      - Figure 2.12.
      - Figure 2.13.
      - Figure 2.14.
    - Summary
    - Quiz
    - Exercises

## Day 2

## Creating Databases

In today's lesson, you 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 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 also learn Visual Basic database field types, including their names, storage sizes, and common uses. Along the way, you 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 (from smallest to largest)

- Data fields (sometimes 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

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).
Figure 2.1. Examples of data fields in a database.

> 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 names--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.

When you begin constructing your database, spend time thinking about the various ways you (and your users) 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

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. Figure 2.2 shows the Company Database with a Customer Record defined. The Customer Record (row) contains three fields (columns).
Figure 2.2. An example of a data record in a database.

####
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).
Figure 2.3. An example of a data table in a database.

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).

#### 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 related in turn 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). 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.
Figure 2.5. customer records relationships

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 need to add a Commission field 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 (along with the exceptions), and you 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.

In the example illustrated in Figure 2.6, you have added an additional data field: Type.
Figure 2.6. Using a qualifier field to establish a one-to-one relationship.

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 approaches to establish relationships between tables. They are usually established through a key field. Key fields are covered in depth in the next section.

#### 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 (field) or more in your table as either a primary key or a foreign key. A data table can have only 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" are 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 to 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.
Figure 2.7. The LastName field is the primary key field of the Customer table.

> 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.

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 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 16, "Database Normalization," and Day 17, "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 14 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 14 database field types recognized by Visual Basic.

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 Data Manager from the Toolbar. You learn about using this tool in Day 7, "Using the Visdata Program."

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.
Table 2.1. Visual Basic data field types.

Data Field Type | Size | VBType | Comments
BINARY | V | (none) | Limited to 255 bytes
BOOLEAN | 1 | Boolean | Stores 0 or -1 only
BYTE | 1 | Integer | Stores 0 to 255 only
COUNTER | 8 | Long | Auto-incrementing Long type
CURRENCY | 8 | Currency | 15 places to left of decimal, 4 to right
DATETIME | 8 | Date/Time | Date stored on the left of decimal point, time stored on the right
DOUBLE | 8 | Double |
GUID | 16 | (none) | Used to store Globally Unique Identifiers
INTEGER | 2 | Integer |
LONG | 8 | Long |
LONGBINARY | V | (none) | Used for OLE objects
MEMO | V | String | Length varies up to 1.2 gigabytes
SINGLE | 4 | Single |
TEXT | V | String | Length limited to 255

> 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 need to know exactly what data field types are needed and how they are to be mapped to various data formats. The various data formats are covered in Day 9, "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. Double-clicking a data-bound LONGBINARY field automatically invokes the local application that is registered to handle the stored OLE object.

- 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 13 into the input box, Visual Basic stores -1 in the data field. To make matters trickier, Visual Basic does 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 is converted into -1.

- The BYTE data field type only accepts input ranging from -0 to 255. Any other values (including negative numbers) result in a runtime error (error number 524) when you attempt to update the data record.

> > WARNING: This behavior is changed from Visual Basic 4.0. In the past, Microsoft Jet would automatically convert the invalid value to a byte value and not report an error. For example, if you enter the value 255 (stored as FF in hexadecimal), Visual Basic stores 255 in the data field. If you enter 260 (stored as 0104 in hexadecimal--it takes two bytes!), Visual Basic stores a decimal 4 in the data field because the right-most byte is set to hexadecimal 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 results 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 Visual Data Manager that ships with Visual Basic allows the TEXT field to have a length of 1 to 255 bytes.

- GUID data field types are used to store a special type of 128-bit number--the Globally Unique Identifier. This value is used to identify ActiveX components, SQL Server remote procedures, Microsoft Jet replication IDs, and other objects that require a unique identifier. For more on Microsoft Jet replication, see Day 20, "Database Replication."

#### Building the Visual Basic 5 Field Data Types Project

The following project illustrates how different Visual Basic data field types store user input. You also see how Visual Basic responds to input that is out of range for the various data field types.

  1. 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.

> > WARNING: Notice that you are creating a set of four buttons with the same name, but different Index property values. This is a control array. Control arrays offer an excellent way to simplify Visual Basic coding. However, they behave a bit differently than non-arrayed controls. It is important that you build the controls exactly as described in this table.

Figure 2.8. The form for the Visual Basic data field types project.

Table 2.2. Controls for the Visual Basic data field types project.

Control | Property | Setting
Project | Name | prjFieldTypes
Form | Name | frmFieldTypes
| Caption | VB5 Basic Data Field Types
CommandButton | Name | cmdBtn
| Caption | &Add
| Height | 300
| Index | 0
| Width | 1200
CommandButton | Name | cmdBtn
| Caption | &Add
| Height | 300
| Index | 0
| Width | 1200
CommandButton | Name | cmdBtn
| Caption | &Update
| Height | 300
| Index | 1
| Width | 1200
CommandButton | Name | cmdBtn
| Caption | &Delete
| Height | 300
| Index | 2
| Width | 1200
CommandButton | Name | cmdBtn
| Caption | E&xit
| Height | 300
| Index | 3
| Width | 1200
DataControl | Name | datFieldTypes
| DatabaseName | `FIELDTYPES.MDB` (include correct path)
| RecordSource | FieldTypes

  2. Now add the code behind the command button array. Double-click the Add button (or any other button in the array) to bring up the code window. Enter the code from Listing 2.1 into the `cmdBtn_Click` event.

### Listing 2.1. Code for the cmdBtn_Click event.

    Private Sub cmdBtn_Click(Index As Integer)
        `
        ` handle button selections
        `
        On Error GoTo LocalError
        `
        Select Case Index
            Case 0 ` add
                datFieldTypes.Recordset.AddNew
            Case 1 ` update
                datFieldTypes.UpdateRecord
                datFieldTypes.Recordset.Bookmark =
    ÂdatFieldTypes.Recordset.LastModified
            Case 2 ` delete
                datFieldTypes.Recordset.Delete
                datFieldTypes.Recordset.MovePrevious
            Case 3 ` exit
                Unload Me
        End Select
        Exit Sub
        `
    LocalError:
        MsgBox Err.Description, vbCritical, Err.Number
        `
    End Sub

There may be several things in this code segment that are new to you. First, different lines of code are executed based on the button that is pushed by the user. This is indicated by the Index parameter that is passed to the `Click` event. Second, some error-handling code has been added to make it easy for you to experiment with the data form. You learn more about error-handling in Day 14, "Error Handling in Visual Basic 5.0." Don't worry if this code segment looks a bit confusing. For now, just go ahead and enter the code that is shown here.

Now is a good time to save the project. Save the form as `FieldTypes.frm` and the project as `FieldTypes.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 BooleanField. Set the Text property to blank. Refer to Figure 2.9 for placement and sizing.
Figure 2.9. Adding the BOOLEAN data type input control.

Now run the program. If this is the first time you've run the program, you should see an empty field. Press the Add button to create a new record and then press the Update button to save that record. You see that the first value in the input box is a 0, the default value for BOOLEAN fields. Enter the number 13 in the text box and click the Update button. This forces 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, is 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 see that both controls have been marked as selected. Now click Edit | Copy to copy the selected controls to the Clipboard. Use Edit | Paste to copy the controls from the Clipboard back onto your 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).
Figure 2.10. Copying controls on a form.

> 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, they reduce the amount of Windows resources your program uses. You can copy controls as many times as you like--even across forms and projects!

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 ByteField, and you have two new controls on your form with minimal typing. Your form should look like the one in Figure 2.11.
Figure 2.11. Adding the BYTE data type to your form.

Save and run the program. This time, after pressing the Add button, enter the value 256 into the Byte input control and press the Update button. You see that when Visual Basic attempts to store the value to the data table, a runtime error is reported. Byte data fields can only accept positive values between 0 and 255. Trying to save any other value in this data field causes the Microsoft Jet data engine to report an error to Visual Basic. Testing the CURRENCY Data Type Copy the label and text box control again using the mouse to select the controls to be copied, and the `Copy` and `Paste` commands from the Edit menu. Change the label Caption property to Currency and the text box DataField property to CurrencyField. 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. Press the Add button; enter the value 1.00001; force Visual Basic to save the value to the data table (press the Update button) and see what happens. Try entering 1.23456. When storing values to the CURRENCY data type field, Visual Basic stores only four places to the right of the decimal. If the number is larger than four decimal places to the right, Visual Basic rounds the value before storing it in the data field. Also, you notice that Visual Basic does not add a dollar sign ($) to the display of CURRENCY type data fields. Testing the DATETIME Data Type The Visual Basic DATETIME data type field is one of the most powerful data types. Visual Basic performs extensive edit checks on values entered in the DATETIME data type field. Using DATETIME 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 DateTime and the text box DataField property to DateTimeField. Your form should look like the one in Figure 2.13.

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.
Figure 2.13. Adding the DATETIME data type to the form.

How does Visual Basic decide what date format to use? The date format used 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 DATETIME 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 U.S. 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 Now let's test a very special database field 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.

> NOTE: Actually, the Counter data type is not a true database field type. Instead, the Counter data type is a Long data field with its Attribute property set to AutoIncrField. You won't find the Counter data type listed in the documentation, but you will see references to auto-incrementing fields and see a "Counter" type as an option when you build data fields with the Visual Data Manager.

Copy another label/text box control set onto the form. Change the label caption property to Counter and the text box DataField property to AutoIncrField. See Figure 2.14 for guidance in positioning and sizing the control.
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 sets the value of COUNTER fields; users do not. Add a new record to the table by pressing the Add button. You see a new value in the COUNTER input control. Visual Basic uses 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 will all receive unique values in the Counter text box.

> WARNING: You should never attempt to edit the value in the COUNTER text box! If Visual Basic determines that the counter value has been changed, it displays a Visual Basic error message, and you cannot save the record. Even if you reset the value in the COUNTER data field back to its original value, Visual Basic refuses to save the record.

Additional Visual Basic Data Types The rest of the Visual Basic data types (INTEGER, SINGLE, DOUBLE, TEXT, MEMO, BINARY, LONGBINARY, and GUID) 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 returns an error if you enter more than the maximum number of characters into a TEXT data field.

- The LONGBINARY data field is used to store graphic image data and allows any alphanumeric data to be entered and saved. The storage of graphic data is covered later in the book (see Day 11, "Displaying Your Data with Graphs").

- 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 data fields allow you to enter values other than 0 or -1 without reporting an error. Notice that Visual Basic alters the data you entered into these data fields without telling you!

- The CURRENCY data field stores only the first four places to the right of the decimal. If you enter values beyond the fourth decimal place, Visual Basic rounds the value to four decimal places and gives you no message.

- The DATETIME data field has some interesting behavior. Visual Basic does 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 DATETIME 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 reflects the changes made through the Control Panel.

- The GUID data field type is used to store special 128-bit numbers called Globally Unique Identifiers.

- The BINARY data field type allows from 0 to 255 bytes of data storage and has only limited uses. If you are using the Visual Data Manager, you see a "Binary" field type--this is actually the LONGBINARY field.

- The MEMO and LONGBINARY data field types are known as "large value" data fields since they can hold up to 1.2 gigabytes of data in a single field. If you are working with large data fields, you need to move data between your program and the data table using the `GetChunk` and `AppendChunk` methods. You learn more about these methods on Day 9.

- The Visual Basic 5 documentation describes several other data field types recognized by Microsoft Jet (Big Integer, Char, Decimal, Float, Numeric, Time, TimeStamp, and VarBinary). However, these other data field types cannot be created using the Visual Data Manager or using Visual Basic code. These additional data field types may be returned by data tables built using other database tools, including Microsoft SQL Server or other back-end databases.

### 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 14 basic data field types recognized by Microsoft Jet and 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

  1. What are the three main building blocks for relational databases?
  2. What is the smallest building block in a relational database?
  3. A data record is a collection of related __________.
  4. What is the main role of a primary key in a data table?
  5. Can a data table have more than one foreign key defined?
  6. List all the possible values that can be stored in a BOOLEAN data field.
  7. What is the highest value that can be stored in a BYTE data field?
  8. What happens when you attempt to edit a COUNTER data field?
  9. How many places to the right of the decimal can be stored in a CURRENCY data field?
  10. 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:

SSN | Last | First | Age | City | St | Comments
123-45-6789 | Smith | Mark | 17 | Austin | TX | Trans. from New York.
456-79-1258 | Smith | Ron | 21 | New York | NY | Born in Wyoming.
987-65-8764 | Johnson | Curt | 68 | Chicago | IL | Plays golf on Wed.

  1. How many records are in the previous data table?
  2. Which field should you select as the primary key?
  3. Identify each data field, its Data Field Type, and its VISUAL BASIC Type.
  4. Modify the Visual Basic Data Field Types example from this lesson by creating a checkbox and placing the results in the existing BOOLEAN textbox.

| Use of this site is subject certain Terms & Conditions. Copyright (c) 1996-1999 EarthWeb, Inc.. All rights reserved. Reproduction in whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Please read our privacy policy for details.