Database Management

1
Chapter 12

• Database Management

2
Outlines and Objectives

• An Introduction to Databases
• Relational Databases and SQL
• Three Additional Data-Bound Controls
• Creating and Designing Databases

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

4
The Data Control

• Provides communication with databases
• Using a data control you can read, modify, delete
  and add records to databases
• Use prefix dat for naming a data control.

5
A Data Control Walkthrough

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

6
A Data Control Walkthrough Continued

• Place a text box on the form and name it txtCity.
  In the properties window, select the DataSource
  property of txtCity and select datCities.
• Select the DataField property and select the
  field city ( the text box now will display data
  from the city field of the Cities table)
• Place another txtBox and bind it to the data
  control by setting its DataSource property to
  datCities and its DataField property to pop1995.

7
A Data Control with two text boxes bound to it
Last
First
Previous
Next
8
Example (to 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
9
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

• 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

14
Example Run
15
Find Methods

• 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(displays 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
Three Additional Data-Bound Controls Creating
and Designing Databases

• Flex Grid control
• Data-bound list boxes
• Data-bound combo boxes
• Note Before you can use the three controls, you
  must add them to the toolbar using the components
  dialog box that is invoked from the Project menu.

19
Using the FlexGrid Control

• A FlexGrid control will display the entire
  contents of the table, including the field names.
• The FlexGrid control is discussed in Section
  11.3.
• You can use an SQL statement to specify the
  fields displayed in a FlexGrid control.

20
Example(the FlexGrid 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

21
Example Run
22
Using the Data-Bound List Box and Combo Box
Control

• They are used for data entry and ensure that data
  entered are valid.
• These controls automatically fill with a column
  from a table or recordset after you set a few
  properties.
• Note that the data-bound controls display data
  from a table or a recordset.
• Two key properties determine the entries of a
  data-bound list or combo box- RowSource and
  ListField.

23
Example of using data-bound list box

• Private Sub cmdSort_Click()
• datCountries.RecordSource "SELECT FROM
  Countries " _
• "ORDER by pop1995
  DESC"
• datCountries.Refresh
• End Sub
• Private Sub dblCountries_dblClick()
• datCountries.Recordset.FindFirst _
• "Country " "'"
  dblCountries.Text "'"
• lblUnit.Caption datCountries.Recordset.Fields(
  "currency").Value
• End Sub
• Private Sub Form_Load()
• datCountries.DatabaseName App.Path
  "\MEGACTY2.MDB"
• End Sub

24
Example Run
25
Creating a Database with Visual Data Manager

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

26
Steps to create a database

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

27
Example of Table Structure window

• Invoked from the Database window with the right
  mouse button by choosing New or Design.

28
Creating a Database

• Add Index window which is invoked from the Table
  Structure window by pressing Add Index

29
Creating a Database

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

30
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