Section 10.2. Relational Databases and SQL
10.2. Relational Databases and SQLPrimary and Foreign KeysA well-designed table should have a field (or set of fields) that can be used to uniquely identify each record. Such a field (or set of fields) is called a primary key. For instance, in the Countries table of Section 10.1 (Table 10.2), the country field is a primary key. In the Cities table (Table 10.1), because we are only considering very large cities (of over 1 million population), the city field is a primary key. Databases of student enrollments in a college usually use a field of Social Security numbers as the primary key. Names would not be a good choice because there could easily be two students having the same name. When a database is created, a field can be specified as a primary key. If so, Visual Basic will insist that every record have an entry in the primary-key field and that the same entry does not appear in two different records. If the user tries to add a record with no data in the primary key, the error message "Index or primary key cannot contain a Null Value." will be generated. If the user tries to add a record with the same primary key data as another record, the error message "The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again." will be displayed. When a database contains two or more tables, the tables are usually related. For instance, the two tables Cities and Countries are related by their country field. Let's refer to these two fields as Cities.country and that Countries.country. Notice that every entry in Cities.country appears uniquely in Countries.country and that Countries.country is a primary key. We say that Cities.country is a foreign key of Countries.country. Foreign keys are usually specified when a table is first created. If so, Visual Basic will insist on the Rule of Referential Integrity, namely, that each value in the foreign key must also appear in the primary key of the other table. In the database MEGACITIES.MDB, Cities.city and Countries.country have been specified as primary keys for their respective tables, and Cities.country has been specified as a foreign key of Countries.country. If the user tries to add a city to the Cities table whose country does not appear in the Countries table, then the error message "You cannot add or change a record because a related record is required in table 'Countries'." will be displayed. The message will also be generated if the user tries to delete a country from the Countries.country field that appears in the Cities.country field. Due to the interdependence of the two tables in MEGACITIES.MDB, this database is called a relational database. A foreign key allows Visual Basic to link (or join) together two tables from a relational database in a meaningful way. For instance, when the two tables Cities and Countries from MEGACITIES.MDB are joined based on the foreign key Cities.country, the result is Table 10.4. The record for each city is expanded to show its country's population and its monetary unit. This joined table is very handy if, say, we want to navigate through a table with buttons as in Example 1 of Section 10.1, but display a city's name and monetary unit. We only have to create the original two tables; Visual Basic creates the joined table as needed. The request for a joined table is made in a language called SQL.
SQLStructured Query Language (SQL) was developed in the early 1970s at IBM for use with relational databases. The language was standardized in 1986 by ANSI (American National Standards Institute). Visual Basic uses a version of SQL that is compliant with ANSI-92 SQL. There are some minor variations that are of no concern in this book. SQL is a very powerful language. One use of SQL is to request specialized information from an existing database or to have the information presented in a specified order. Four SQL Requests (Queries)We will focus on four basic types of requests that can be made with SQL. Request I: Show the records of a table in a specified order. Some variations of ordering with Cities are
Request II: Show just the records that meet certain criteria. Some examples of criteria with Cities are
Request III: Join the tables together, connected by a foreign key, and present the records as in Requests I and II. Some examples with MEGACITIES.MDB are
Request IV: Make available just some of the fields of either the basic tables or the joined table. Some examples with MEGACITIES.MDB are
Normally, we set the SQL statement of a data adapter to an entire table. Also, the records of the table are normally presented in the order they are physically stored in the table. We make the requests just discussed by specifying the SQL statement as one of the following types:
"ASC" and "DESC" specify ASCending and DESCending orders, respectively. A criteria clause is a string containing a condition of the type used with If blocks. In addition to the standard operators <, >,and =, criteria strings frequently contain the operator Like. Essentially, Like uses the wildcard characters "_" and "%" to compare a string to a pattern. An underscore character stands for a single character in the same position as the underscore character. For instance, the pattern "B_d" is matched by "Bid", "Bud", and "Bad". A percent sign stands for any number of characters in the same position as the percent sign. For instance, the pattern "C%r" is matched by "Computer", "Chair", and "Car". See Comments 3 through 5 for further information about Like. In the sentence SELECT fields FROM clause fields is either * (to indicate all fields) or a sequence of the fields to be available (separated by commas), and clause is either a single table or a join of two tables. A join of two tables is indicated by a clause of the form tblA INNER JOIN tblB ON foreign key of tblA=primary key of tblB Appending WHERE criteria to the end of the sentence restricts the records to those satisfying criteria. Appending ORDER BY field(s) ASC (or DESC) presents the records ordered by the specified field or fields. In general, the SQL statements we consider will look like SELECT www FROM xxx WHERE yyy ORDER BY zzzwhere SELECT www FROM xxx is always present and accompanied by one or both of WHERE yyy and ORDER BY zzz. In addition, the xxx portion might contain an INNER JOIN phrase. The settings for the examples mentioned earlier are as follows: I (a) Show the records from Cities in alphabetical order based on the name of the city with SELECT * FROM Cities ORDER BY city ASCI (b) Show the records from Cities in alphabetical order based first on the name of the country and, within each country group, the name of the city with SELECT * FROM Cities ORDER BY country, city ASC I (c) Show the records from Cities in descending order based on the projected 2015 population, using SELECT * FROM Cities ORDER BY pop2015 DESC II (a) Show the records for the Cities in India with SELECT * FROM Cities WHERE country = 'India' II (b) Show the records from Cities whose 2015 population is projected to be at least 20 million, as in SELECT * FROM Cities WHERE pop2015 >= 20 II (c) Show the records from Cities whose name begins with the letter "D", with SELECT * FROM Cities WHERE city Like 'D%' III (a) Show the records from the joined table in descending order of the populations of their countries, using SELECT * FROM Cities INNER JOIN Countries ON Cities.country = Countries.country ORDER BY Countries.pop2005 DESC III (b) Show the records from the joined table whose monetary unit has "u" as its second letter with SELECT * FROM Cities INNER JOIN Countries ON Cities.country = Countries.country WHERE monetaryUnit Like '_u%' IV (a) Make available just the city and country fields of the table Cities, using SELECT city, country FROM Cities IV (b) Make available just the city and monetaryUnit fields of the joined table, as in SELECT city, monetaryUnit FROM Cities INNER JOIN Countries ON Cities.country = Countries.countryNote: In several of the statements the single quote, rather than the normal double quote, was used to surround strings. This is standard practice with SQL statements. We can think of an SQL statement as creating in essence a new "virtual" table from existing tables. For instance, we might regard the statement SELECT city, pop2015 FROM Cities WHERE pop2015>=20 as creating the "virtual" table
This table is a subtable of the original table Citiesthat is, it consists of what is left after certain columns and rows are omitted. As another example, the statement SELECT Cities.city, Cities.Country, Countries.monetaryUnit FROM Cities INNER JOIN Countries ON Cities.country = Countries.country WHERE Countries.country>'K' creates in essence the "virtual" table
which is a subtable of a join of the two tables Cities and Countries. These "virtual" tables don't exist physically. However, for all practical purposes, Visual Basic acts as if they did. In standard relational database books, a "virtual" table is called a view. The programs in Section 10.1 all contain a statement of the form Dim sqlStr As String = "SELECT * FROM tableName"that creates an entire existing table as a view. Replacing this string with a different SQL statement will result in a different "virtual" table for the data table when it is filled by the data adapter. The DataGridView ControlIn Section 10.1, information from databases is displayed in text boxes and list boxes. Another control, called the DataGridView, displays the values for an entire view in a table format identical to the table displayed by Database Explorer. The standard prefix for the name of a DataGridView control is dgv. After a data table has been filled, the statement dgvDisplay.DataSource = dtdisplays the contents of the data table dt in the data grid view. Example 1. | |||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||||
 The following program allows the user to alter the order and kinds of information displayed from a database. When the first button is pressed, the cities are presented in ascending order based on their 2005 populations. When the second button is pressed, the cities are presented in alphabetical order along with their monetary units.
Private Sub frmCities_Load(...) Handles MyBase.Load UpdateGrid("Select * From Cities") End Sub Private Sub btnOrderbyPop_Click(...) Handles btnOrderbyPop.Click 'Display the data from the data table in the data grid. UpdateGrid("Select * From Cities Order By pop2005 ASC") End Sub Private Sub btnShowMonUnit_Click(...) Handles btnShowMonUnit.Click UpdateGrid("SELECT city, Cities.country, " & _ "Cities.pop2005, monetaryUnit " & _ "FROM Cities INNER JOIN Countries " & _ "ON Cities.country=Countries.country " & _ "ORDER BY city ASC") End Sub Sub UpdateGrid(ByVal sqlStr As String) Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" [Run, and click on the Show Monetary Unit button.] |
The program in Example 3 of Section 10.1 searched a table for a specific record by looping through all the records. Whereas this technique is fine for small tables, it is not efficient for searches of large tables. A better way to find data that match a criteria is to include the criteria in the SQL string.
Example 2. (This item is displayed on pages 537 - 538 in the print version)
| The following program displays the large cities in a country specified by the user. Notice the single quotes surrounding the criteria in the SQL string.
Private Sub btnFindCities_Click(...) Handles btnFindCities.Click UpdateGrid("SELECT city FROM Cities WHERE country = '" & _ txtCountry.Text & "' ORDER BY city ASC") End Sub Sub UpdateGrid(ByVal sqlStr As String) 'Declare and populate the data table. Dim dt As New DataTable() Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() 'Display the names of the cities in the specified country. If dt.Rows.Count = 0 Then MsgBox("No cities from that country in the database") Else dgvDisplay.DataSource = dt End If End Sub [Run, type "India" into the text box, and press the button.] |
Changing the Contents of a Database
The programs presented so far only read data from a database. Data grid views can be used to add, modify, and delete records from a database. After a DataAdapter has been created, the statement
Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataAdapter)will automatically enable any modifications to be stored. If changes is an Integer variable, then the statement
changes = dataAdapter.Update(dt)will store all of the insertions, updates, and deletions made in the data table to the database and assign the number of records changed to the variable changes.
Example 3. (This item is displayed on pages 539 - 540 in the print version)
| The following program demonstrates loading data from a database to a data grid via a data adapter, allowing the user to make edits within the data grid, and saving the changes back to the database. To update a record, highlight the field, and make the change. To insert a new record, click on the bottom row that begins with an asterisk and has blank fields. (The values in the fields are initially filled with "(null)" and should be changed.) To delete a row, click on the box to the left of the row so that the entire row is highlighted, then click the Delete key. If you click the "Load Table From Database" button before clicking the "Save Changes To Database" button, any changes are lost. Run the program, and test it by clicking on the Load button, making some changes, and then clicking on the Save button.
'Class-level variables Dim connStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=MEGACITIES.MDB" Dim sqlStr As String = "SELECT * FROM Cities" Dim dt As New DataTable() Private Sub btnLoad_Click(...) Handles btnLoad.Click 'Displays the table's data in the data grid 'Clear the current contents of the table. dt.Clear() 'Fill the data table with data from the database. Dim dataAdapter As New OleDb.OleDbDataAdapter(sqlStr, connStr) dataAdapter.Fill(dt) dataAdapter.Dispose() 'Display the table in the data grid. dgvDisplay.DataSource = dt End Sub |
Calculated Columns with SQL
Suppose we want to display in a data grid the cities in the database MEGACITIES.MDB database along with their expected population growth from 2005 to 2015. We could use Access to add a fifth field to the Cities table and fill it with the population growth. That is, for each record the entry in the new field would be the difference between its pop2015 and pop2005 fields. However, this would violate a fundamental principle of database designavoid fields whose values can be calculated from existing fields. SQL provides a way to display a population growth column without creating a new field for the Cities table.
After the data table dt has been filled using the string
sqlStr = "SELECT city, (pop2015-pop2005) FROM Cities"then the statement
dgvDisplay.DataSource = dtcauses the data grid view to display two columns, as shown in Figure 10.3, where the second column shows the population growth for each city. This column is called a calculated column.
Figure 10.3.
The display in Figure 10.3 has two flawsthe second column does not have a meaningful name, and the numbers in the second column are not rounded to one decimal place. The first flaw can be corrected by adding the clause AS popGrowth after (pop2015pop2005). This will give the column the heading popGrowth. The second flaw can be corrected by replacing (pop2015pop2005) with Round(pop2015pop2005, 1). Therefore, using the string
sqlStr = "SELECT city, Round(pop2015-pop2005, 1) AS popGrowth " & _ "FROM Cities"
to fill the table produces the output shown in Figure 10.4.
Figure 10.4.
Note: The column title following the AS keyword can contain spaces. If so, the title should be surrounded by brackets. For instance, if the clause is AS [Population Growth], then the column title will be Population Growth.
Calculated values can be used in an ORDER clause to sort the displayed data. For instance, if the SQL string above is changed to
sqlStr = "SELECT city, Round(pop2015-pop2005, 1) AS popGrowth " & _ "FROM Cities ORDER by (pop2015-pop2005) DESC"
then the cities will be sorted by their population growth.
Comments
Each record of the Countries table is related to one or more records of the Cities table, but each record of the Cities table is related to only one record of the Countries table. Therefore, we say that there is a one-to-many relationship from the Countries table to the Cities table.
SQL statements are insensitive to case. For instance, the following choices for criteria have the same effect: City = 'Tokyo', city = 'tokyo', CITY = 'tokyo', CiTy = 'TOKYO'.
When the Like operator is used, the "pattern" must appear on the right of the operator. For instance, the SQL statement
SELECT * FROM Cities WHERE city Like 'S%'cannot be replaced by
SELECT * FROM Cities WHERE 'S%' Like cityThe operator Like permits a refinement of the wildcard character "_". Whereas "_" is a placeholder for any letter, an expression such as "[letter1-letter2]" is a placeholder for any letter from letter1 to letter2. For instance, the pattern "[A-F]ad" is matched by Bad and Dad, but not Sad.
The Like operator can be used in If blocks in much the same way as the operators > ,=, and < In this situation, the operator is case sensitive. For instance, the condition ("bad" Like "[A-F]ad") is False. However, when Like is used in SQL statements, it is case insensitive. That is, ('bad' Like '[A-F]ad') is True. Furthermore, when Like is used in an If block, the asterisk is used instead of the percent sign to denote any number of characters, and the question mark stands for any one character.
The requirement that no record may have a null primary key and that entries for primary keys be unique is called the Rule of Entity Integrity.
Practice Problems 10.2
For each of the following grids, give an SQL statement that can be used to create the grid from the MEGACITIES.MDB database:
| 1. | |
| 2. | |
Exercises 10.2
Exercises 1 and 4 refer to the database MEGACITIES.MDB, where the primary keys of Cities and Countries are city and country, respectively, and Cities.country is a foreign key to Countries.country. Determine whether the stated action could ever cause a problem. Explain.
| 1. | Add a new record to the Cities table. |
| 2. | Delete a record from the Countries table. |
| 3. | Delete a record from the Cities table. |
| 4. | Add a new record to the Countries table. |
The following tables are "virtual" tables derived from the MEGACITIES.MDB database. In Exercises 5 through 8, identify the "virtual" table associated with the SQL statement.
| (A) | ||
|---|---|---|
country | pop2005 | monetary Unit |
Russia | 143.2 | ruble |
Indonesia | 222.8 | rupiah |
India | 1103.4 | rupee |
Brazil | 186.4 | real |
| (B) | ||
|---|---|---|
country | pop2005 | monetary Unit |
China | 1316.8 | yuan |
(C) | ||
|---|---|---|
country | pop2005 | monetary Unit |
China | 1316.8 | yuan |
India | 1103.4 | rupee |
| (D) | ||
|---|---|---|
country | pop2005 | monetary Unit |
China | 1316.8 | yuan |
Brazil | 186.4 | real |
Bangladesh | 141.8 | taka |
| 5. | SELECT * FROM Countries WHERE pop2005>1200 ORDER BY pop2005 ASC |
| 6. | SELECT * FROM Countries WHERE country<"E" ORDER BY pop2005 DESC |
| 7. | SELECT * FROM Countries WHERE monetaryUnit Like 'r%' ORDER BY country DESC |
| 8. | SELECT * FROM Countries WHERE pop2005>700 ORDER BY country ASC |
The following tables are "virtual" tables derived from the MEGACITIES.MDB database. In Exercises 9 through 12, identify the "virtual" table associated with the SQL statement.
| (A) | |
|---|---|
city | monetaryUnit |
Sao Paulo | real |
| (B) | |
|---|---|
city | monetaryUnit |
Tokyo | Yen |
Bombay | rupee |
| (C) | |
|---|---|
city | monetaryUnit |
Bombay | rupee |
Delhi | rupee |
Calcutta | rupee |
| (D) | |
|---|---|
city | monetaryUnit |
Tokyo | Yen |
| 9. |
SELECT city, monetaryUnit FROM Cities INNER JOIN Countries ON Cities.country = Countries.country WHERE city='Tokyo' |
| 10. |
SELECT city, monetaryUnit FROM Cities INNER JOIN Countries ON Cities.country = Countries.country WHERE pop2015>22 ORDER BY pop2015 DESC |
| 11. |
SELECT city, monetaryUnit FROM Cities INNER JOIN Countries ON Cities.country = Countries.country WHERE Cities.country = 'India' ORDER BY Cities.pop2005 DESC |
| 12. |
SELECT city, monetaryUnit FROM Cities INNER JOIN Countries ON Cities.country = Countries.country WHERE city Like 'S%' ORDER BY Countries.pop1995 ASC |
For each grid in Exercises 13 through 20, give an SQL statement that can be used to create the grid from the MEGACITIES.MDB database. Then test your answer with a DataGridView filled from a table whose DataAdapter uses the SQL statement. Note: Several of the exercises here have more than one correct answer.
| 13. |
|
| 14. |
|
| 15. |
|
| 16. |
|
| 17. |
|
| 18. |
|
| 19. |
|
| 20. |
|
Exercises 21 through 23 use the database EXCHRATE.MDB from the folder Programs\Ch10\MajorDatabases. It gives the exchange rates (in terms of American dollars) for 45 currencies of major countries on January 14, 2005. Figure 10.5 shows the first eight records in the database in a DataGrid control. The dollarRate column gives the number of units of the currency that can be purchased for one American dollar. For instance, one American dollar purchases 1.20809 Canadian dollars.
Figure 10.5. Exchange rates
| 21. | Write a program that displays the names of the countries in a data-bound list box. When the user clicks on one of the names, the monetary unit and the exchange rate should be displayed. | ||||||||||||||||||||||||
| 22. | Write a program that displays the names of the countries in a data-bound list box in ascending order determined by the number of units that can be purchased by one American dollar. When the user clicks on one of the names, the monetary unit and exchange rate should be displayed. | ||||||||||||||||||||||||
| 23. | Write a program containing two data-bound list boxes as shown in Figure 10.6. When the user selects two countries and an amount of money, and clicks on the button, the program should convert the amount from one currency to the other. Hint: Use two DataTables. Figure 10.6. A possible output for Exercise 23 | ||||||||||||||||||||||||
| 24. | The database MOVIES.MDB has the following two tables, named Lines and Actors. The left table contains famous lines from films that were spoken by the leading male actor. Use this database in a program that displays a grid of two columns, where the first column contains a famous line and the second column contains the name of the actor who spoke the line.
| ||||||||||||||||||||||||
| 25. | Suppose the database USSTATES.MDB contains the table States consisting of the three fields name, population, and area with one record for each of the 50 U.S. states. Give an SQL string that can be used to display a data grid view having two columns with the first column containing the names of the states and the second column containing their population densities. The states should appear in decreasing order of their population densities. | ||||||||||||||||||||||||
| 26. | Suppose the database EMPLOYEES.MDB contains the table Payroll consisting of the four fields name, phoneNumber, hoursWorked, and hourlyPayRate. Assume that each entry phoneNumber field has the form xxx-xxx-xxxx, and the hoursWorked field is filled with the number of hours the person worked during a week. Give an SQL string that can be used to display a data grid view having two columns, with the first column containing the names of the people whose area code is 301 and the second column containing their wages for the week. The names should appear in increasing order of their earnings for the week. Note: In SQL, the left 3 characters of strVar are computed as Left(strVar, 3). |
Exercises 27 through 35 use the database BASEBALL.MDB discussed in the exercises in Section 10.1. In Exercises 27 through 33, write a program to display the stated information in a DataGridView control.
| 27. | The names of the players who play for a team based in New York. |
| 28. | The name(s) of the player(s) in the American League having the most hits. |
| 29. | The name(s) of the National League player(s) with the highest batting average for that league. |
| 30. | The names of all the players and their batting averages. The records should be sorted in descending order by batting average. |
| 31. | The names of all the teams, their home stadiums, and the team batting averages. Records should be sorted in ascending order by team batting average. |
| 32. | The names of all the players, their leagues, and their team's batting averages. |
| 33. | The names of players and their batting averages. The records should be sorted in descending order by batting average and should only show the players whose batting average exceeded their team's batting average. |
| 34. | Write a program that requests a batting average and a league (American or National) and then displays the names of all the players in the league whose batting average is above the given batting average. The program should not permit the given batting average to be greater than 1 or less than 0. |
Exercises 35 and 36 refer to the database PHONEBOOK.MDB, which holds all the information for the residence listings of a telephone book for a city. Assume the database consists of one table, Entries, with the six fields: lastName, firstName, middleInitial, streetNumber, street, and phoneNumber.
| 35. | Write a program that will display the contents of the phone book in the standard form shown in Figure 10.7(a).
| ||||||||||||||||||||
| 36. | Write a program that will display a "criss-cross" directory that gives phone numbers with the entries organized by street as in Figure 10.7(b). | ||||||||||||||||||||
Solutions to Practice Problems 10.2
For each of the following grids, give an SQL statement that can be used to create the grid from the MEGACITIES.MDB database:
| 1. |
SELECT * FROM Countries WHERE country Like 'I%' ORDER BY pop2005 ASC |
| 2. |
SELECT country FROM Countries WHERE country='Mexico' OR country='Brazil' ORDER BY pop2005 ASC |
EAN: 2147483647
Pages: 164