Section 10.1. An Introduction to Databases

[Page 518]

10.1. An Introduction to Databases

The management of databases is the number one use of computers today. Airlines use databases to handle nearly 1.5 billion passenger reservations per year. The 6,500 hospitals in the United States utilize databases to document the care of over 30 million patients per year. Banks in the United States utilize databases to monitor the use of 350 million credit cards. Although databases vary considerably in size and complexity, most of them adhere to the fundamental principles of design discussed in this chapter. That is, they are composed of a collection of interrelated tables.

A table is a rectangular array of data. Table 10.1 provides information about large cities. Each column of the table is called a field. (The third column gives the 2005 population in millions and the fourth column gives the projected 2015 population in millions.) The names of the fields are city, country, pop1995, and pop2015. Each row, called a record, contains the same type of information as every other row. Also, the pieces of information in each row are related; they all apply to a specific city. Table 10.2, Countries, has three fields and nine records.

Table 10.1. Cities.
city	country	pop2005	pop2015
Bombay	India	18.2	22.6
Calcutta	India	14.3	16.8
Delhi	India	15.1	20.9
Dhaka	Bangladesh	12.4	17.9
Jakarta	Indonesia	13.0	17.5
Lagos	Nigeria	11.0	17.0
Mexico City	Mexico	19.0	20.6
New York	USA	18.5	19.7
Sao Paulo	Brazil	18.2	20.0
Tokyo	Japan	35.2	36.2
Note: The population figures are for "urban agglomerations"that is, contiguous densely populated urban areas.

Table 10.2. Countries.
country	pop2005	monetaryUnit
Bangladesh	141.8	taka
Brazil	186.4	real
China	1316.8	yuan
India	1103.4	rupee
Indonesia	222.8	rupiah
Japan	128.1	yen
Mexico	107.0	peso
Nigeria	131.5	naira
Russia	143.2	ruble
USA	298.2	dollar

Source: United Nations, Dept. for Economic and Social Information and Policy Analysis.

[Page 519]

A database (or relational database) is a collection of one or more (usually related) tables that has been created with database-management software. Microsoft Access is one of the best known database-management products. Some other prominent ones are Oracle, SQL Server, and DB2. VB.NET can manage, revise, and analyze a database that has been created with any of these products. VB.NET also has a powerful data viewer that can be used to browse through all portions of a database.

The database used in each example is found in the Debug subfolder for the program. The databases needed for the exercises are contained in the folder MajorDatabases located in the folder Programs\Ch10. The database files were created with Microsoft Access and have the extension .MDB. For instance, the file MEGACITIES.MDB is a database file containing the two tables presented on the preceding page. MDB files should be copied onto a hard drive and accessed from the hard drive. If the files originally resided on a CD, they might have the attribute "Read-only" set. If so, you should change this attribute for each file. To change the attribute setting, first use Windows Explorer to locate the file on the hard drive. Then right-click on the file, click Properties, and uncheck the "Read-only" box under Attributes.

Database Explorer

Visual Basic Express has a tool called Database Explorer that allows a developer to browse through the contents of a database. The other editions of Visual Basic have a tool called the Server Explorer that can not only browse a database, but also view information on another computer. The following steps refer to Database Explorer, but are applicable to Server Explorer with slight modifications.

1.	Press Alt/V/D to bring up Database Explorer from the View menu. It will appear on the left side of the screen. (Server Explorer can be invoked from the Standard and Professional editions of Visual Basic 2005 with Alt/V/V.)
2.	Right-click on "Data Connections", and select "Add Connection".
3.	In the Add Connection dialog box that appears, the Data Source should be "Microsoft Access Database File (OLE DB)." If not, click on the Change button, select "Microsoft Access Database File" as the data source, and click on OK to return to the Add Connection dialog box.
4.	Click on the "Browse. . ." button to the right of the "Database file name" text box. This will open up a file browser that allows you to locate any file. Select the file MEGACITIES.MDB from the folder Programs\Ch10\MajorDatabases, and press Open.
5.	Clear the contents of the "User name" text box.
6.	Press the Test Connection button at the bottom of the window. The message box stating "Test Connection Succeeded" will appear. Press the OK button on that message box, and then press the OK button on the Add Connection Properties box.
7.	An icon titled something like "ACCESS.D:\PROGRAMS\CH10\MEGACITIES.MDB" should appear in Database Explorer. Click on the + sign to the left of the icon to expand this entry. Four subentries will appear: Tables, Views, and Stored Procedures, and Functions.
	[Page 520]
8.	Expand the Tables entry to reveal the two subentries, the tables Cities and Countries.
9.	Expand the Cities entry to reveal the fields of the Cities table. See Figure 10.1. Figure 10.1. Server Explorer
10.	Right-click on Cities and select "Show Table Data." A grid similar to Figure 10.2 will appear. The grid not only displays all of the data in the Cities table, but also gives the names of the fields. Note: To remove the entry for the database in Database Explorer, right-click on the entry, and select Delete.

Figure 10.2. The Cities table of MEGACITIES.MDB.

The remainder of this section is devoted to creating programs that display information obtained from a database. A DataTable object is the representation of a table (with rows and columns) in ADO.NET. The ActiveX Data Objects .NET (ADO.NET) technology allows programs to access data seamlessly from multiple, varied, and distant servers.

Each time we begin a new program, there are two steps you must take in order to gain access to the Data Table object.

[Page 521]

Add references to System.Data.dll and System.Xml.dll.

To accomplish this step:

a. Click on Project in the Menu bar.

b. Click on Add Reference in the drop-down menu to invoke the "Add Reference" dialog box.

c. Make sure the .NET tab is selected.

d. Click on System.Data, and then hold down the Ctrl key and click on System.Xml This way both items will be selected.

e. Press the OK button.

At the top of the code window (before the line Public Class Form1), type the statement

Imports System.Data

Important Note: We will assume these two steps have been carried out for every program in this chapter.

Accessing a Database with a Data Table

A DataTable object holds the contents of a table as a rectangular array. (A data table is similar to a two-dimensional array; it has rows and columns.) The following six lines of code create a DataTable variable named "dt" and fill it with the contents of the Cities table from the database MEGACITIES.MDB:

   Dim dt As New DataTable()    Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                          "Data Source=MEGACITIES.MDB"    Dim sqlStr As String = "SELECT * FROM Cities"    Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)    dataAdapter.Fill(dt)    dataAdapter.Dispose()

Treat these six lines of code as a boiler plate to be inserted into a program.

Note: You can save yourself time by storing this code fragment in the Toolbox and dragging it into the Code window when needed.

A database resides on a disk, and a data table resides in memory. A data adapter is an intermediary object that serves as a conduit to allow bidirectional data transfers between the two. The first statement in the boiler plate, which declares dt to be a variable of type DataTable, is often placed in the Declaration section of the Code window. The fifth statement of the boiler plate uses the data adapter to fill the data table with data from the database. The sixth statement of the boiler plate releases all resources of the data adapter.

The variable connString is called a connection string. A connection string has two parameters. The Provider parameter specifies the driver that is used to communicate with the database. The Data Source parameter gives the filespec of the database. When the database is in the bin/Debug folder of the current program, the filename can be used instead of the filespec.

The variable sqlStr is called an SQL string. SQL strings tell the data adapter which table to retrieve. SQL strings are discussed extensively in Section 10.2.

[Page 522]

The second and third statements of the boiler plate have the following general form:

Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                         "Data Source=" & DataBaseName Dim sqlStr As String = "SELECT * FROM " & TableName

where the database is assumed to be in the Debug subfolder of the bin subfolder of the program folder.

After the six lines of code are executed, the value of

dt.Rows.Count

is the number of records in the table, and the value of

dt.Columns.Count

is the number of fields in the table. The records are numbered 0 through dt.Rows.Count - 1, and the fields are numbered 0 through dt.Columns.Count - 1. The value of

dt.Columns(j)

is the name of the jth field. The value of

dt.Rows(i)(j)

is the entry in the jth field of the ith record. The value of

dt.Rows(i)(fieldName)

is the entry in the specified field of the ith record. In Table 10.3, dt holds the Cities table of MEGACITIES.MDB.

Table 10.3. Some values from cities table of MEGACITIES.MDB.
Expression	Value
`dt.Rows.Count`	10
`dt.Columns.Count`	4
`dt.Rows(7)(1)`	USA
`dt.Rows(1)("city")`	Calcutta

Note: The values of dt.Columns(j), dt.Row(i)(j), and dt.Row(i)(fieldName) must be converted to strings with CStr before being placed in a text box or assigned to a String variable. However, they need not be converted when placed in a list box.

[Page 523]

Example 1. (This item is displayed on pages 523 - 524 in the print version)

The following program displays one record at a time from the Table 10.1 of the MEGACITIES.MDB database. The user can move forward or backward through the records, or specify the city to be displayed.

Object	Property	Setting
frmCities	Text	Cities Data
btnFind	Text	Find
btnNext	Text	Next
btnPrevious	Text	Previous
lblCity	Text	City:
txtCity	ReadOnly	True
lblCountry	Text	Country:
txtCountry	ReadOnly	True
lblPop2005	Text	Population 2005:
txtPop2005	ReadOnly	True
lblPop2015	Text	Population 2015:
txtPop2015	ReadOnly	True

Dim dt As New DataTable() Dim rowIndex As Integer = 0 Private Sub frmCities_Load(...) Handles MyBase.Load   'Get data from the database, put it into the DataTable object dt,   'and display the initial record's data in text boxes.   Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                           "Data Source=MEGACITIES.MDB"   Dim sqlStr As String = "SELECT * FROM Cities"   Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)   dataAdapter.Fill(dt)   dataAdapter.Dispose()   UpdateTextBoxes() End Sub Private Sub btnFind_Click(...) Handles btnFind.Click   'Search through each row looking for the requested city.   'Update the fields if that city is found.   'Otherwise display a message box.   Dim cityName As String   Dim cityFound As Boolean = False   cityName = InputBox("Enter the name of the city to search for.")   For i As Integer = 0 To (dt.Rows.Count - 1)     If CStr(dt.Rows(i)("city")) = cityName Then       cityFound = True       rowIndex = i       UpdateTextBoxes()     End If 
[Page 524]    Next     If (Not cityFound) Then       MsgBox("Cannot find the requested city", 0, "Not in Table")     End If   End Sub   Private Sub btnNext_Click(...) Handles btnNext.Click     'Show the next record if the current one is not the last.     If (rowIndex < dt.Rows.Count  1) Then       rowIndex += 1 'Increase the rowIndex by 1.       UpdateTextBoxes()     End If   End Sub   Private Sub btnPrevious_Click(...) Handles btnPrevious.Click     'Show the previous record if the current one is not the first.    If (rowIndex > 0) Then     rowIndex = rowIndex - 1     UpdateTextBoxes()   End If End Sub Sub UpdateTextBoxes()   'Display the contents of the row specified by the rowIndex variable.   txtCity.Text = CStr (dt.Rows(rowIndex)("city"))   txtCountry.Text = CStr (dt.Rows(rowIndex)("country"))   txtPop2005.Text = CStr (dt.Rows(rowIndex)("pop2005"))   txtPop2015.Text = CStr (dt.Rows(rowIndex)("pop2015")) End Sub

[Run, and then click the Next button seven times.]

[Page 525]

Example 2. (This item is displayed on pages 525 - 526 in the print version)

The following program displays the contents of the Table 10.1 of MEGACITIES.MDB in a list box, along with the percentage increases for the populations:

Object	Property	Setting
frmCities	Text	Display a Table
		Programatically
btnShow	Text	Show Data
lstDisplay

Private Sub btnShow_Click(...) Handles btnShow.Click  Dim fmtStr As String = "{0,-15}{1,-10}{2,7:N1}{3,7:N1}{4,7:P0}"  Dim percentIncrease As Double  'Place contents of Cities table into a DataTable.  Dim dt As New DataTable()  Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                          "Data Source=MEGACITIES.MDB"  Dim sqlStr As String = "SELECT * FROM Cities"  Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)  dataAdapter.Fill(dt)  dataAdapter.Dispose()  'Fill the list box.  lstDisplay.Items.Add(String.Format(fmtStr, "CITY", "COUNTRY", _                                     "2005", "2015", "INCR."))  For i As Integer = 0 To dt.Rows.Count - 1    percentIncrease = (CDbl(dt.Rows(i)("pop2015")) - _        CDbl(dt.Rows(i)("pop2005"))) / CDbl(dt.Rows(i)("pop2005"))  lstDisplay.Items.Add(String.Format(fmtStr, dt.Rows(i)(0), _      dt.Rows(i)(1), dt.Rows(i)(2), dt.Rows(i)(3), percentIncrease))  Next End Sub

[Page 526]

[Run, and click the button.]

In Example 2, we placed information from a data table into a list box one line at a time. However, a data table can be connected directly to a list box and have information transferred automatically into the list box. In such a situation, the list box is said to be bound to the data table. The statement

lstBox.DataSource = dt

binds the list box to the data table, and the statement

lstBox.DisplayMember = "country"

displays the contents of the specified field in the list box.

Example 3. (This item is displayed on pages 526 - 527 in the print version)

The following program uses a list box to display a column of data named "country" from a data table. When a particular country is selected, the event procedure retrieves the country's monetary unit from the corresponding record and displays it in a text box.

Object	Property	Setting
frmCountries	Text	Currency
lstCountries
lblMonetaryUnit	Text	Monetary unit:
txtMonetaryUnit	ReadOnly	True

Dim dt As New DataTable() Private Sub frmCountries_Load(...) Handles MyBase.Load   Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                           "Data Source=MEGACITIES.MDB"   Dim sqlStr As String = "SELECT * FROM Countries" 
[Page 527]  Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)   dataAdapter.Fill(dt)   dataAdapter.Dispose()   lstCountries.DataSource = dt 'Bind the list box to the data table.   lstCountries.DisplayMember = "country" 'Display the specified field. End Sub Private Sub lstCountries_SelectedIndexChanged(...) _             Handles lstCountries.SelectedIndexChanged   txtMonetaryUnit.Text = _        CStr (dt.Rows(lstCountries.SelectedIndex)("monetaryUnit")) End Sub

[Run, and click on one of the countries.]

Practice Problem 10.1

Determine the output displayed when the button is clicked.

Q1:

Private Sub btnDisplay_Click(...) Handles btnDisplay.Click   Dim dt As New DataTable()   Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _                           "Data Source=MEGACITIES.MDB"   Dim sqlStr As String = "SELECT * FROM Countries"   Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr)   dataAdapter.Fill(dt)   dataAdapter.Dispose()   lstOutput.Items.Add(dt.Columns(2))   lstOutput.Items.Add(dt.Rows(5)(0))   lstOutput.Items.Add(dt.Rows(8)(dt.Columns.Count - 2))   lstOutput.Items.Add(dt.Rows(0)("monetaryUnit"))   lstOutput.Items.Add(dt.Rows(dt.Rows.Count - 1)(2)) End Sub

[Page 528]

Exercises 10.1

Exercises 1 through 16 refer to the database MEGACITIES.MDB. In Exercises 1 through 4, determine the output displayed when the button is clicked.

1.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim sum As Double Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() sum = 0 For i As Integer = 0 To dt.Rows.Count - 1 sum += CDbl(dt.Rows(i)("pop2015")) Next txtOutput.Text = sum & " million" End Sub
2.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() For i As Integer = 0 To dt.Rows.Count - 1 If CDbl(dt.Rows(i)("pop2015")) > 22 Then lstOutput.Items.Add(dt.Rows(i)(0)) End If Next End Sub
3.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() For i As Integer = 0 To dt.Rows.Count - 1 If CStr(dt.Rows(i)("country")).Length = 5 Then lstOutput.Items.Add(dt.Rows(i)(0)) End If Next End Sub
	[Page 529]
4.	Private Sub btnDisplay_Click(...) Handles btnDisplay.Click Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Countries" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() For j As Integer = 0 To dt.Columns.Count - 1 lstOutput.Items.Add(dt.Columns(j)) Next End Sub
5.	Write a program to place in a list box the names of the countries in the Countries table in the order that they appear in the table.
6.	Write a program to place in a list box the names of the countries in the Countries table in the reverse order that they appear in the table.
7.	Write a program to place in a list box the names of the cities in the Cities table whose populations are projected to exceed 20 million in the year 2015.
8.	Write a program to place in a list box the names of the cities in the Cities table whose 2005 populations are between 12 and 16 million.
9.	Write a program to place in a list box the names of the countries in the Countries table, where each name is followed by a hyphen and the name of its monetary unit.
10.	Write a program to find and display the city in the Cities table that will experience the greatest percentage growth from 2005 to 2015. Note: The percentage growth is (pop2015 pop2005) / pop2005.
11.	Write a program to place the countries from the Countries table in a list box in descending order of their 2005 populations. Hint: Place the countries and their 2005 populations in an array of structures, and sort the array in descending order based on the populations.
12.	Write a program to place the cities from the Cities table in a list box in descending order of their percentage population growth from 2005 to 2015.
13.	Write a program to display the name and currency of each city in the table Cities.
14.	Write a program to display in a list box the names of each country in Table 10.1 followed by the cities in that country that are listed in the Cities table.
15.	Write a program to back up the contents of the Cities table in one sequential file and the Countries table in another sequential file. Run the program, and compare the sizes of these two sequential files with the size of the file MEGACITIES.MDB.
16.	Write a program that allows the user to specify a city and then displays the percentage of its country's 2005 population that lives in that city. Hint: Use two DataTables.

[Page 530]

Exercises 17 through 20 refer to the BIBLIO.MDB database from the folder Programs\Ch10\MajorDatabases.

17.	How many tables are in the database? What are their names?
18.	Give the names of the fields in the table Publishers.
19.	How many records are in the table Publishers?
20.	Write a program that requests the name of a publisher (such as Prentice Hall or Microsoft Press) and gives the publisher's address.
21.	The folder Programs\Ch10\MajorDatabases contains the database STATE_ABBR .MDB consisting of one table, States, having two fields, abbreviation and state. Each record consists of a two-letter abbreviation and the name of a state. Some records are (AZ, Arizona) and (MD, Maryland). Write a program that allows the user to enter a two-letter abbreviation and obtain the name of the state. Of course, if the two-letter abbreviation does not correspond to any state, the user should be so informed.

The folder Programs\Ch10\MajorDatabases contains the database BASEBALL.MDB that has the two tables Players and Teams. The fields for the Players table are player, team, atBats, and hits. The fields for the Teams table are team, location, league, stadium, atBats, and hits. The database has been filled with information from the 2004 baseball season for the major leagues. Three sample records from each table are as follows.

Players
Barry Bonds, Giants, 373, 135
Ichiro Suzuki, Mariners, 704, 262
Manny Ramirez, Red Sox, 568, 175

Teams
Giants, San Francisco, National, SBC Park, 5546, 1500
Diamondbacks, Arizona, National, Bank One Ballpark, 5544, 1401
Red Sox, Boston, American, Fenway Park, 5720, 1613

The database BASEBALL.MDB should be used in Exercises 22 through 26.

22.	Write a program to determine the player in the Players table with the most hits. In the case of a tie, the program should list all players having the most hits.
23.	Write a program to determine the player (or players) in the Players table with the highest batting average.
24.	Write a program that displays all the teams in the Teams table in a list box. When the user clicks on one of the teams, the program should display the team's home stadium in a text box.
25.	Write a program that displays all the teams in the Teams table in a list box. When the user clicks on one of the teams, the program should display the names of all the players in the Players table from that team. Hint: Use two DataTables.
26.	Write a program to count the number of players in the Players table who play for a National League team. Hint: Use two DataTables.

[Page 531]

Solution to Practice Problem 10.1

A1:

monetaryUnit Japan 143.2 taka dollar