Chapter 12 Database Management Outlines and Objectives

1
Chapter 12 Database ManagementOutlines and
Objectives

• 12.1 An Introduction to Databases
• 12.2 Relational Databases and SQL
• 12.3 The Data Grid Control Creating and
  Designing Databases

2
An Introduction to Databases

• The smallest unit in a database is called a
  field.
• A record is a collection of fields.
• A table (file) is a collection of records.
• A database is a collection of one or more tables.
• Visual Basic can manage, revise, and analyze a
  database that has been created with database
  management products such as Access, Btrieve,
  dBase, FoxPro, and Paradox.

3
The ADO Data Control

• Two types of data controls
• DAO, stands for Data Access Objects, is the
  intrinsic data control found on the Toolbox
• ADO, stands for ActiveX Data Objects, is a custom
  control that must be invoked from the Project
  Menu
• Provides communication with databases such as
  Access
• Using a data control you can read, modify, delete
  and add records to databases
• Use prefix dat for naming a data control.

4
A Data Control Walkthrough p.571

• Press Alt/File/New Project and double-click on
  Standard EXE.
• Double-click on the data control icon. Set its
  Name property to datCities and its Caption
  property to Cities.
• Stretch it horizontally to see the caption
  Cities
• Select the DatabaseName property and set it to
  the filespec for the file Megacty1.mdb
• Select the RecordSource property and click on the
  down-arrow button. The names of the two tables in
  the database, Cities and Countries, are
  displayed.
• Select Cities.

5
A Data Control Walkthrough (contd)

• Place a text box on the form and name it txtCity.
  
• In the properties window, select the DataSource
  property of txtCity.
• Select datCities.
• Select the DataField property.
• Select the field city.
• Place another txtBox txtPop1995 on the form.
• Select txtPop1995s DataSource property
  (datCities and datafield)

6
A Data Control with two text boxes bound to it
Last
First
Previous
Next
7
Example 1, pp. 576to add or delete records from
Megacty1.mdb

• Private Sub cmdAdd_Click()
• 'Add a new record
• datCities.Recordset.AddNew
• txtCity.SetFocus 'Data must be entered and a
  new record moved to
• End Sub
• Private Sub cmdDelete_Click()
• 'Delete the currently displayed record
• datCities.Recordset.Delete
• 'Move so that user sees deleted record
  disappear
• datCities.Recordset.MoveNext
• If datCities.Recordset.EOF Then
• datCities.Recordset.MovePrevious
• End If
• End Sub

A new record will be added to the end of table
The current record is marked for removal
The next record of the table will be the current
record
8
Using the Data-Bound List Box and Combo Box
Control (Example 2, pp. 578)

• They are custom controls used for data entry and
  ensure that data entered are valid. Therefore,
  they must be added to the toolbox.
• They are automatically fill with a column from a
  table or recordset after you set a few
  properties.
• Two key properties determine the entries of a
  data-bound list or combo box
• The RowSource property of the data list/combo
  control must be set to the name of the ADO data
  control
• The ListField property of the list/combo control
  must be set to a field of the ADO controls table

9
12.2 Relational Database

• When a database contains two or more tables, the
  tables are usually related by using a field (or
  set of fields).
• Such a field is called a primary key.
• A field can be specified as a foreign key in a
  table, if rule of Referential Integrity exists.
• Rule of Referential Integrity means that each
  value in the foreign key must also appear in the
  primary key of the other table.
• When there exist such interdependence between two
  tables, the database is called a relational
  database.

10
SQL (Structured Query Language)

• SQL is a query language which is used with
  relational databases.
• SQL is a very powerful language
• Using SQL you can request specialized information
  from an existing database and/or have the
  information presented in a specified order.

11
Four SQL Requests

• Show the records of a table in a specified order
• Show just the records that meet certain criteria
• Join the tables together, connected by a foreign
  key, and present the records as in Request 1 and
  2.
• Make available just some of the fields of the
  tables.

12
SQL Requests

• To satisfy the requests, and create sub-tables,
  use the reserved words such as SELECT, FROM,
  WHERE, ORDER BY, and INNER JOIN . ON.
• SQL statements are either employed at design time
  or run time.
• During run time, the Refresh method for the data
  control should be executed after the RecordSource
  property is set.

13
Example of using SQL in the code p.594

• Private Sub cmdShow_Click()
• If cmdShow.Caption "Show City, Country,
  Currency" Then
• 'Join the two tables and display cities,
  countries, and currency
• datCities.RecordSource "SELECT city,
  Cities.country, currency FROM " _ "Cities INNER
  JOIN Countries ON Countries.country
  Cities.country " _ "ORDER BY city"
• datCities.Refresh
• cmdShow.Caption "Show City, Country,
  Populations"
• Else
• datCities.RecordSource "Cities"
• datCities.Refresh
• cmdShow.Caption "Show City, Country,
  Currency"
• End If
• End Sub

Example 1, pp. 593
14
Example Run
15
Find Methods (pp. 595)

• If a table has been created and ordered, then a
  statement of the form
• Data1.RecordSet.FindFirst criteria
• starts at the beginning of the recordset,
  searches through the recordset for the record
  that satisfies the criteria, and makes that
  record the current record.
• The related methods FindLast, FindNext, and
  FindPrevoius function as their names suggest.

16
Example 2, pp. 596displays the large cities in a
country specified by the user

• Private Sub cmdFind_Click()
• Dim nom As String, criteria As String
• lstCities.Clear
• If txtCountry.Text ltgt "" Then
• nom txtCountry.Text
• criteria "country " "'" nom "'"
• datCities.Recordset.FindFirst criteria
• Do While datCities.Recordset.NoMatch
  False
• lstCities.AddItem datCities.Recordset.Fiel
  ds("city").Value
• datCities.Recordset.FindNext criteria
• Loop
• If lstCities.ListCount 0 Then
  lstCities.AddItem "None"
• Else
• MsgBox "You must enter a country.", , ""
• txtCountry.SetFocus
• End If
• End Sub

17
Sample Run
18
12.3 The Data Grid Control Creating and
Designing Databases

• The FlexGrid control (Chap 11) is based on DAO
  technology and therefore cannot bound to the ADO
  data control.
• The data grid control must be added to the
  toolbar using the components dialog box.
• A data grids DataSource property is used to bind
  the data grid to an ADO data control. The ADO
  data control is connected to a table of the
  database.

19
Example 1, pp. 604the data grid displays the
cities, countries and currency

• Private Sub cmdShow_Click()
• If cmdShow.Caption "Show City, Country,
  Currency" Then
• 'Join the two tables and display cities,
  countries, and currency
• datCities.RecordSource "SELECT city,
  Cities.country, currency FROM " _
• "Cities INNER JOIN Countries ON
  Countries.country Cities.country " _
• "ORDER BY city"
• datCities.Refresh
• cmdShow.Caption "Show City, Country,
  Populations"
• Else
• datCities.RecordSource "cities"
• datCities.Refresh
• cmdShow.Caption "Show City, Country,
  Currency"
• End If
• End Sub

20
Example Run
21
Creating a Database with Visual Data Manager (pp.
606)

• Invoke Visual Data manager (VisData) from VB by
  pressing Alt/Add-Ins/Visual Data Manager.
• Select New to create a new database.
• Choose the Microsoft Access database and then
  specify the version.
• After naming the database, click save.
• You should enter data using Database window and
  SQL statement box.

22
Steps to create a database

• Steps to create a database with two tables (pp.
  607)
• Create the database and the two tables.
• Specify a primary key for a table.
• Place records into a table.

23
How to use the Table Structure Window (pp. 608)

• Invoked from the Database window with the right
  mouse button by choosing New or Design.
• Type the name of the table,
• ..

24
How to use the Add Index Window

• The Add Index window is invoked from the Table
  Structure window by pressing Add Index
• Type a name, and click on the Primary Field check
  box
• Click OK and Close
• Press Build the Table

25
How to use the Table Window

• Table window is invoked from the Database window
  by double-clicking on the table name. Or, choose
  the table, click on the right mouse button, and
  click open.

26
Principles of Database Design

• Include the necessary data
• Be aware that data should often be stored in
  their smallest parts
• Avoid redundancy
• Strive for table clarity
• Dont let a table get unnecessarily large
• Avoid fields whose values can be calculated from
  existing fields
• Avoid tables with intentionally blank entries