Transcript Database

Chapter 10 – Database
Management
10.1 An Introduction to Databases
10.2 Editing and Designing Databases
1
10.1 An Introduction to
Databases
•
•
•
•
•
Accessing a Database Table
Binding to Additional Tables
Querying a Table with LINQ
Primary and Foreign Keys
The Join of Two Tables
2
Sample Table – Cities Table
3
Sample Table – Countries Table
4
Database Terminology
• A table is a rectangular array of data.
• Each column of the table, called a field,
contains the same type of information.
• Each row, called a record, contains all
the information about one entry in the
table.
5
Database Management
Software (DBMS)
• Used to create databases
• Databases contain one or more related tables
• Examples of DBMS are Access, Oracle, and
SQL Server.
• The databases used in this chapter are found
in the folder Programs\Ch10\Databases. They
were created with Access and have the
extension accdb.
6
Megacities.accdb
• Contains the two tables Cities and
Countries shown earlier.
• This database will be used extensively in
the examples for this chapter.
• Several steps are required to bind to a
table of the database. (See the next
sixteen slides.)
7
Binding to the Cities Table
Add a BindingSource control to the form. (The
control is in the Data and All Windows Forms
group of the Toolbox. It appears in the form’s
component tray with the name BindingSource1.)
8
DataSource Property of
BindingSource1
click here
9
Choose Data Source Type
select
click on Next button
10
Choose Database Model
select
click on Next button
11
Choose Data Connection
click on New Connection button
12
Add Connection Dialog Box
click on Change button
13
Change Data Source Box
select
click on OK button
14
Add Connection Dialog Box
click on Browse button
15
Select Database File
double-click on Megacities.accdb
16
Add Connection Dialog Box
click on OK button
17
Choose Data Connection
click on Next button
18
click on Yes button
19
Save to File
check this box
click on Next button
20
Choose Database Objects
check on
Tables box
click on Finish
button
21
Changes in Properties
Window and Form
22
After Clicking on DataMember
Down-Arrow
click on Cities
23
VB Generated Items
new icon
new
code
24
Binding Complete
• We are now bound to the Cities table via
the MegacitiesDataSet and the
CitiesTableAdapter.
• The next four slides show how to bind an
additional table.
25
Connect an Additional Table
Add another BindingSource control to the form.
26
Set DataSource Property
click on MegacitiesDataSet
27
Set DataMember Property
click on Countries
28
VB Generated Items
new icon
additional code shows in Load event procedure
Me.CountriesTableAdapter.Fill(Me.MegacitiesDataSet.Countries)
29
Example 1: Form
txtTotalPop
30
Example 1: Code
Private Sub btnDisplay_Click(...) Handles _
btnDisplay.Click
Dim query1 = From city In
MegacitiesDataSet.Cities
Where city.country = "India"
Order By city.pop2010 Descending
Select city.name
lstOutput.DataSource = query1.ToList
lstOutput.SelectedItem = Nothing
31
Example 1: Code (continued)
Dim query2 = From city In _
MegacitiesDataSet.Cities
Where city.country = "India"
Select city.pop2010
txtTotalPop.Text = CStr(query2.Sum)
End Sub
32
Example 1: Output
33
Example 2: Form
txtName
dgvOutput
34
Example 2: Code
Dim query = From country In _
MegacitiesDataSet.Countries
Where country.name = txtName.Text
Select country.name, country.pop2010,
country.monetaryUnit
If query.Count = 1 Then
dgvOutput.DataSource = query.ToList
dgvOutput.CurrentCell = Nothing
Else
MessageBox.Show("Country not found")
End If
35
Example 2: Output
36
Example 3: Form
dgvOutput
37
Example 3: Code
Dim query = From city In _
MegacitiesDataSet.Cities
Let popIncrease = city.pop2015 - city.pop2010
Let formattedIncr =
FormatNumber(popIncrease, 1)
Where popIncrease > 1
Order By popIncrease Descending
Select city.name, formattedIncr
38
Example 3: Code (continued)
dgvOutput.DataSource = query.ToList
dgvOutput.CurrentCell = Nothing
dgvOutput.Columns("name").HeaderText = "City"
dgvOutput.Columns("formattedIncr").HeaderText =
"Population Increase"
39
Example 3: Output
40
Primary Keys
• A primary key is used to uniquely
identify each record.
• Databases of student enrollments in a
college usually use a field of student ID
numbers as the primary key.
• Why wouldn't names be a good choice
as a primary key?
41
Primary Key Fields
• Specified when database is created.
• Every record must have an entry in the
primary-key field.
• Two records cannot have the same entry in
the primary-key field.
• This pair of requirements is called the Rule
of Entity Integrity.
42
Two or More Tables
• 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 and
name fields.
• Notice that every entry in Cities.country
appears uniquely in Countries.name and
Countries.name is a primary key.
• We say that Cities.country is a foreign key
of Countries.name.
43
Foreign Keys
• Foreign keys can be specified when a
table is first created. Visual Basic will
insist on the Rule of Referential
Integrity.
• This Rule says that each value in the
foreign key must also appear in the
primary key of the other table.
44
Join
• A foreign key allows Visual Basic to link (or
join) two tables from a relational database
• When the two tables Cities and Countries from
Megacities.accdb are joined based on the
foreign key Cities.country, the result is the
table in the next slide.
• The record for each city is expanded to show
its country’s population and its monetary unit.
45
A Join of Two Tables
46
Beginning of Query to Join the
Two Tables from Megacities
Dim query = From city In _
MegacitiesDataSet.Cities
Join country In MegacitiesDataSet.Countries
On city.country Equals country.name
47
Example 6: Form
48
Example 6: Code for Load Event
Me.CountriesTableAdapter.Fill(Me.MegacitiesDataSet.Countries)
Me.CitiesTableAdapter.Fill(Me.MegacitiesDataSet.Cities)
Dim query = From country In _
MegacitiesDataSet.Countries
Order By country.monetaryUnit Ascending
Select country.monetaryUnit
Distinct
lstCurrencies.DataSource = query.ToList
49
Example 6: Later Code
Dim query = From city In _
MegacitiesDataSet.Cities
Join country In MegacitiesDataSet.Countries
On city.country Equals country.name
Where country.monetaryUnit =
lstCurrencies.Text
Order By city.name Ascending
Select city.name
For Each city As String In query
lstCities.Items.Add(city)
Next
50
Example 6: Sample Output
51
10.2 Editing and Designing
Databases
• A Program to Edit the Cities Table
• Designing the Form for the Table-Editing
Program
• Writing the Table-Editing Program
• Principles of Database Design
52
Editing Program: Form
navigation
toolbar
53
Navigation Toolbar
54
Editing Program: Output
55
Building the Form
• Start a new program and bind the Cities
table.
• Add a BindingNavigator control (found in
Data group of Toolbox) to the form.
Note: BindingNavigator1 appears in
component tray and a navigation toolbar
appears at the top of the form.
56
Navigation Bar Added
button to be
changed
57
Change Last Button
click here
58
Change Last Button
(continued)
click on
Button
59
Change Last Button
(continued)
new button
• New button has name ToolStripButton 1
• Use the Properties window for the button to
change the Name, Text, and Image settings to
btnUpdate, Update, and Disk.bmp.
60
Change Last Button
(continued)
Disk.bmp used for
picture on button
61
Bind to Cities Table
click here
62
Bind Text Boxes to Cities
Table Fields
drag these four
items onto form
63
Text Boxes and Labels
Generated by VB
64
Complete Design of Form
65
Index Numbers
• Each record of the table Cities has an index
number ranging from 0 to 9.
• The value of BindingSource1.Position is the
index number of the record currently
displayed in the form.
66
Index Numbers (continued)
• The statement
BindingSource1.Position = n
displays the record having index number n.
• The value of
BindingSource1.Find("name", cityName)
is the index number of the specified city.
67
Principles of Database Design
• Data should often be stored in their smallest
parts.
• Avoid redundancy.
• Avoid tables with intentionally blank entries.
• Strive for table cohesion.
• Avoid fields whose values can be calculated
from existing fields.
68