Teach Yourself Database Programming
with Visual C++ 6 in 21 days
Today you will learn how to create and manage database applications by using the Visual C++ Professional and Enterprise Editions. With the Visual C++ Professional or Enterprise Editions, you can retrieve data and modify the contents of databases. With the Enterprise Edition, you can also create and modify the structure of the database (the database's metadata).
Today you will
Building a good database is not rocket science. It is, however, computer science. If you can learn C++, you already possess enough computer science savvy to learn the science of building good databases. A good database has a flexible design, speedy performance, and efficient capacity and is adaptable to meet today's, and future, requirements.
Your first design decision is to choose which database model to use for your application. There are several database models to choose from, including flat file, object, and relational.
| CAUTION |
Neglecting to make a definite choice of a database model for your application means that you are choosing to invent your own. Inventing a database model is harder than it looks. If you try to invent your own database model, you will severely handicap your database. |
One particular database model offers a unique combination of power, flexibility, and universal acceptance. That model is the relational model.
The relational model enables you to build databases that can be implemented on all popular computing platforms, including mainframes, servers, PCs, and even some handheld machines. Most popular programming languages can communicate with databases that adhere to the relational model.
The relational model also provides enough abstraction to enable you to add elements and features to the database without having to recompile and redistribute the code for the applications that use it. This level of abstraction also enables you and your customers to retrieve combinations of data from the database that you hadn't anticipated needing at the time that the database was created.
E.F. Codd, a computer scientist from IBM, first formulated relational theory in 1970. The first commercially available relational database system (RDBMS) was Oracle. Many relational database systems have become commercially available since then. It is interesting to note that the relational database systems commercially available do not fully implement Codd's relational theory.
The relational model is based on relational calculus, a complex mathematics, but some fundamental principles of the model make it easy to use. You can get up and running right away with the basics of the relational model and learn the advanced portions of it later as the need arises.
The Visual C++ Professional and Enterprise Editions provide built-in support for the relational database model. You can view and modify the contents of relational databases inside the Visual C++ Developer Studio.
First, you need to make sure that you have installed the necessary Visual C++ database components. These components consist of ODBC drivers and OLE DB providers. To install these (or to verify that they're already installed), run the Visual Studio 6 setup program. Your first screen will look like Figure 2.1.
Figure 2.1 : The Visual Studio 6 Setup dialog. Click the Add/Remove button.
Click the Add/Remove button. You will be presented with the Maintenance setup dialog, shown in Figure 2.2.
Figure 2.2 : The Visual Studio 6 Maintenance setup dialog.
Select Data Access from the list and click the Change Option button.
Select OLEDB Components from the list shown in Figure 2.3 and click the Change Option button.
Figure 2.3 : The Visual Studio 6 Data Access dialog.
Check the boxes for the OLE DB providers, shown in Figure 2.4, that you will need. If you are unsure which providers you will need, you can install all of them except those that you're certain you will not use. You need to install the Microsoft OLEDB Jet Provider for the examples in this book, so make sure it's checked. Click the OK button to return to the Data Access dialog shown in Figure 2.5.
Figure 2.4 : The Visual Studio 6 OLEDB Components dialog.
Figure 2.5 : The Visual Studio 6 Data Access dialog.
Select Microsoft ODBC Drivers from the list and click the Change Option button. Your screen will look like Figure 2.6.
Check the boxes for the ODBC drivers you need. If you're unsure which drivers you need, install all of them except those that you're certain you will not use. For the examples in this book, you need to install the Microsoft Access ODBC Driver; make sure it's checked. Click the OK button to return to the Data Access dialog.
Figure 2.6 : The Visual Studio 6 Microsoft ODBC Drivers dialog.
Click the OK button on the Data Access dialog to return to the Maintenance setup dialog. The bottom right of the dialog shows the number of components you are adding. If that number is zero, the database components you need for this book are already installed. You can click the Cancel button and exit the setup program.
If the number of components to add isn't zero, click the Continue button. The setup program will install the OLE DB providers and ODBC drivers you need for this book.
After the OLE DB providers and ODBC drivers are installed, you need to install the author's examples from the CD-ROM included with this book. The examples include a Microsoft Access database file, VCDb.mdb, which is used in examples throughout this book. Follow the installation instructions for the CD in order to install the author's examples.
You need to set up the sample database VCDb.mdb as an ODBC data source on your machine. This is done with the 32-bit ODBC applet in the Control Panel.
Run the 32-bit ODBC applet, which will display a dialog that enables you to create, edit, and delete ODBC data sources (see Figure 2.7). An ODBC data source is also called a Data Source Name (DSN).
Figure 2.7 : The System DSN tab in the 32-bit ODBC Administrator applet.
The first three tabs along the top of the dialog enable you to create DSNs. A User DSN is accessible only on this local machine and only by the current user. A System DSN is accessible on this local machine by any user. A File DSN is a file-based data source that is accessible on a local or remote machine by any user.
Now you will create a System DSN for the sample database. Select the System DSN tab and then click the Add button (see Figures 2.8 and 2.9).
Figure 2.9 : The DSN setup for the Microsoft Access database.
Type in a name for the data source (such as OrdersDb). You can also type in a description for the data source. (This description is not used programmatically.)
Click the Select button to specify the path and name of the database file. Make sure to specify the VCDb.mdb file on your hard disk instead of the one on the CD-ROM. The one on the CD is read-only and won't work for the examples in the book.
Click the OK button to create the ODBC data source. To open the VCDb.mdb database inside Visual Studio, you need to create a database project. Run Visual C++ and select the File…New menu. Click the Projects tab (see Figure 2.10) and select Database Project. Specify a directory and a project name and click OK.
Figure 2.10: The New Projects dialog.
On the Machine Data Source page, you will be prompted to select the data source for this database project, as shown in Figure 2.11.
Figure 2.11: Selecting the ODBC data source for a data-base project.
Select the ODBC data source you created earlier and click OK. Now click the Data View tab at the bottom left of the Developer Studio main window.
The Data View in Developer Studio shows the components contained in the database. In the Data View, you can double-click the elements (or click the plus sign [+]) to expand the tree view to display the components of the database. As you can see in Figure 2.12, a relational database can consist of many components and has considerable structure (or metadata).
Figure 2.12: The Visual C++ Data View.
The structure of a relational database might seem like too much overhead, but this structure provides amazing benefits.
As an illustration, you can liken the raw data and complex structure of a relational database to those of a human body. A human body is more than raw chemical elements. A human body contains complex structures such as proteins and enzymes. The abundance of structures is what enables the chemical elements to provide us much more value than they would if they were less highly structured.
A relational database is more than a data file containing raw bits and bytes. A relational database contains a complex structure. As in the human body, the abundance of structure in a relational database is what enables the data to provide so much more value than it would if it were less highly structured.
The structure is where the value lies. The raw elements (and the raw data) provide the building blocks, but the structure is what makes it all valuable.
This is one reason why I advise developers not to attempt building their own database models. The structure of a homemade database model can never be as good as the structure of a relational database. A database that uses a homemade model is far less valuable than it would be if it used the relational model.
The Microsoft Access database VCDb.mdb contains four tables and two views. Expand the Data View so that it shows the tables in the database. Double-click the Products table in the Data View to see the data inside the table, shown in Figure 2.13.
Figure 2.13: Opening a database table inside Visual C++.
The data in relational databases is arranged in tables. (In relational database parlance, tables are called relations. I will refer to them as tables throughout this book.) These tables contain rows of like information. The tables can be compared to arrays of structures in C++. The columns in the database table are like the data members of the structure. The rows in the table are like the elements of the array.
It's important to note that database tables are not like arrays of unions in C++. As you might know, a union is a user-defined data type that can hold data of different types at different times. In an array of unions, the number of data members, their types, and their lengths can vary from one element of the array to the next. A database table isn't like that. The number of columns, their type, and their length do not vary from row to row.
In relational database parlance, the process of creating tables of like information is called normalization.
Good table design is fundamental to a good database. If you don't properly design your tables, your database will not be as functional as it could be. I will explain the rules of thumb for good table design on Day 8, "Utilizing the Capabilities of Database Servers."
The columns in a database table are called fields. (In relational database parlance, fields are called attributes.)
A field is the smallest element in a database. Each field in a table has a data type and a length. In the Data View, click the plus sign by the Products table name to see the fields in the table, as in Figure 2.14.
Figure 2.14: Fields in a database table.
Each field should contain one data element. For instance, rather than have a single field to hold both the first and last name of a customer, a table should have one field for the customer's first name and another field for the last name. This enables easier searching and editing of the database.
Think carefully about the granularity of the data in the fields. Make each field as granular or precise as possible. The usefulness of the database depends on the integrity of its smallest element-the field.
As I described on Day 1, "Choosing the Right Database Technology," relational data-bases have their own type systems. This means that the data types in C++ aren't the same as the data types in relational databases.
In the Data View, click the plus sign by a table name to display the fields in the table. Right-click a field name and select Properties from the Context menu (see Figure 2.15). The properties of each field are name, data type, length, and precision. Look at the data types for several fields and note that they're similar to but not the same as C++ data types.
Figure 2.15: The Column Properties window.
The ODBC API and the OLE DB API (explained on Day 15, "The ODBC API and the MFC ODBC Classes," and Day 16, "The Ultimate Database API: OLE DB") provide a translation facility between C++ data types and database data types.
Many C++ data types readily translate to and from database data types. However, some types do not. When designing your database applications, you need to carefully read the appropriate documentation.
An interesting data value that fields can hold is NULL. In relational databases, NULL does not mean zero. Zero is a value, whereas NULL is undefined.
The use of NULL values is best illustrated with an example. Let's say you have a table that lists men and the color of their hair. The hair color field would contain values such as Brown, Black, Red, and so on. What about men who are bald? Well, that would be an ideal place to use NULL. A bald man's hair color is undefined because he doesn't have hair.
The rows in a database table are called records. (In relational database parlance, records are called tuples.)
Records can be likened in C++ to elements of an array of structures. When interfacing a C++ program to a relational database, you use records to store instances of C++ classes. The topic of mapping C++ objects to relational databases is discussed in more detail on Day 14, "Legacy Database APIs." For now, the basic idea is that the class definition corresponds to a table, the data members in the class correspond to the fields, and each instance of the class that is created at runtime can be persisted as a record in the table.
The important thing about records in a database is that each record must be unique. It makes no sense to have several records containing the same data. For example, if you had a table that listed your customers, you would not want duplicates. Each customer would be listed once, and only once, in the customer table. If there were duplicate customer records and you needed to change a customer's address, you would not know which record to change.
The requirement that each record be unique can be fulfilled with good database design. Database design is discussed further on Day 8.
Creating unique records raises the necessity of discussing keys. A key is a field, or combination of fields, with which you can uniquely identify a record.
It's easier to identify unique records by using a single field instead of a combination of several fields. That's why you frequently see things such as account numbers, customer numbers, part numbers, and so on.
As you can see in the sample database (and in Figure 2.14), there is a part number field in the Products table. This part number field uniquely identifies each product and is the key field in the Products table.
A good real-life example of a key is a Social Security number. If you know a person's Social Security number, you can identify that person. A Social Security number is a key attribute.
When you design a database, try to think carefully about what field or fields would constitute the key for each table.
A benefit of having a primary key, which uniquely identifies each record, is that keys can be used to relate records to each other.
In the sample database, you have one table that lists your customers and another table that lists their orders (see Figure 2.16). The Orders table consists of an order number, the date of purchase, the customer, the product purchased, and the price and payment information.
In the Orders table, you don't need to store all the customer data with every order. That would be redundant. You simply store the customer number with each order. In C++ parlance, think of it as storing a pointer to the customer with each order. That way you can easily tell which customer bought what when, and the database will have no duplicate data.
Figure 2.16: Fields in the sample database.
The customer number is the primary key in your Customers table because it uniquely identifies the customer. The customer number in the Orders table is a foreign key (see Figure 2.17). A foreign key is a primary key from another table. Again, in C++ you would think of a foreign key as a pointer to an object.
Figure 2.17: The Orders table with primary and foreign keys.
The same customer number could appear in the Orders table several times. That's okay. Foreign keys can repeat within a table, but primary keys cannot. The customer number must appear only once in the Customers table but can appear in several records in the Orders table. There is a one-to-many relationship between the customer numbers in the Customers table and the customer numbers in the Orders table.
The process of using primary keys and foreign keys is how the relationships between the data are defined in a relational database. I will go into greater detail on this subject in the next few days.
The most widely used and accepted database model is the relational model. The relational model provides great openness and flexibility. Applications in addition to a database's original application can access the data. The database is sufficiently abstracted from the application so that the database and the application can be independently updated.
A relational database consists of tables, which are arranged in columns and rows. Each column is called a field. Each row is called a record and is unique, based on some key field or fields. The records in the tables in a relational database are related to each other, based on key fields that are called primary and foreign keys.
| Does Visual Studio provide support for other database models or technologies in the same way that it supports relational databases? | |
| No. The database integration that you find inside Visual Studio is based on ODBC. ODBC is designed to work with relational databases only. Therefore, Visual Studio 6 provides direct support of and integration with only relational database technology. | |
| What's the difference between Microsoft Access and Jet? | |
| Jet is the name of the database engine portion of Microsoft Access. You can think of Microsoft Access as a user interface (UI) to the Jet database engine. The Jet database engine is also used in the Access ODBC driver. When you write a C++ application that stores data in an Access MDB file, your application makes calls to the Access ODBC driver, which calls the Jet engine, which talks to the MDB file. | |
| Can't I build a relational database by using a record manager such as Btrieve or a desktop database such as FoxPro? | |
| You can build a set of tables that use primary and foreign keys to relate records to each other, using Btrieve or FoxPro. However, with Btrieve and FoxPro, each table is stored in a separate file. Also, Btrieve and FoxPro make no effort to help you enforce relational rules inside your database as a relational database server (and, to a certain extent, Microsoft Access) does. With Btrieve and FoxPro, you will likely end up with a database that is partly relational and partly your own model, which will be a handicap in the future when you try to add new features or capabilities to your database. | |
| Can't I use a spreadsheet such as Microsoft Excel as a database? | |
| Some spreadsheet applications do provide support for database-type functionality. However, this functionality merely consists of storing rows and columns of data in a manner akin to a single table in a relational database. Spreadsheets provide no relational capabilities. Some spreadsheets, such as Microsoft Excel, do enable users to obtain data from relational databases and analyze that data inside the spreadsheet. The data must be formatted as a single table of data, however. | |
| With all the overhead of a relational database, isn't a relational database going to be slow when compared to a lean and mean database that I create myself in C++ or compared to a record manager? | |
| A desktop database such as Microsoft Access will probably perform much faster than any database you can write yourself. A relational database server, with its capability to take full advantage of multiprocessor servers and modern disk subsystems, will outperform record managers in handling large quantities of data. |
The Workshop quiz questions test your understanding of today's material. (The answers appear in Appendix F, "Answers.") The exercises encourage you to apply the information you learned today to real-life situations.
© Copyright, Sams Publishing. All rights reserved.
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}
){kind=link}