slides

Download Report

Transcript slides 

16 – Persistent data storage:
relational databases and ADO
Mark Dixon, SoCCE
SOFT 131
Page 1
Session Aims & Objectives
• Aims
– To introduce the fundamental ideas involved in
persistent data storage and relational databases
• Objectives,
by end of this week’s sessions, you should be able to:
– create a relational database
– use a relational database to store an
application's data between executions
Mark Dixon, SoCCE
SOFT 131
Page 2
Persistent Data Storage
• So far
– all programs lose data when closed
• Not realistic
– typically data stored to persistent storage device
(e.g. hard disk, key drive, floppy disk, CD-RW)
• Use either
– flat files
– database (relational, or object oriented)
Mark Dixon, SoCCE
SOFT 131
Page 3
Flat files: Data Duplication
Field
Track
Record
Mark Dixon, SoCCE
Track Title
Artist Name
Country
Paranoid
Black Sabbath
UK
Falling in Love
Aerosmith
US
Pink
Aerosmith
US
Love in an Elevator
Aerosmith
US
Smooth Criminal
Alien Ant Farm
US
Meaning of Life
Disturbed
US
The Game
Disturbed
US
Voices
Disturbed
US
Down with the Sickness
Disturbed
US
SOFT 131
Page 4
Relations (tables)
Track
Track Title
Artist
ID
Paranoid
1
Falling in Love
2
Pink
2
Love in an Elevator
2
Smooth Criminal
3
Meaning of Life
4
The Game
4
Voices
4
Artist
Artist Artist Name
ID
Country
1
Black Sabbath
UK
2
Aerosmith
US
3
Alien Ant Farm US
4
Disturbed
US
Primary Key
Down with the Sickness 4
Foreign Key
Mark Dixon, SoCCE
SOFT 131
Page 5
Normalisation
• Part of database design
• Process of breaking data down
• Codd
– 7 stages of normalisation
• Mathematical
• Difficult to apply stages
Mark Dixon, SoCCE
SOFT 131
Page 6
Exercise 1: Prescriptions
• Identify duplication and separate:
Prescription
Mark Dixon, SoCCE
Date
Surname
Forenames
Drug Name
6 Jan 04
Jones
Alison
Co-codamol
11 Jan 04
Smith
Bob
Tegretol
18 Jan 04
Hope
John
Co-codamol
5 Feb 04
Johnson
Sally
Co-codamol
8 Feb 04
Smith
Bob
Tegretol
10 Feb 04
Smith
Bob
Sorbitol
SOFT 131
Page 7
Exercise 1: Solution
Patient
PatientID Surname Forenames
Prescription
1
Jones
Alison
2
Smith
Bob
3
Hope
John
4
Johnson
Sally
Date
PatientID DrugID
6 Jan 04
1
1
11 Jan 04
2
2
18 Jan 04
3
1
5 Feb 04
4
1
Drug
8 Feb 04
2
2
DrugID Drug Name
10 Feb 04
2
3
1
Co-codamol
2
Tegretol
3
Sorbitol
Mark Dixon, SoCCE
SOFT 131
Page 8
Database Management Systems
• DBMS provides facilities for:
– creating and changing databases
• add/remove records
• add/remove fields
• add/remove data
• e.g.
– home/small business
• Microsoft Access
• dBase
– Large scale
• Microsoft SQL Server
• Oracle
Mark Dixon, SoCCE
SOFT 131
Page 9
MS Access
Mark Dixon, SoCCE
SOFT 131
Page 10
ActiveX Data Objects (what & why)
• ActiveX Data Objects (ADO)
– common database interface
• allow you to write code for any DBMS
MS Access
VB or
VB Script
code
ADO
DB front end
Mark Dixon, SoCCE
MS SQL Server
…
…
SOFT 131
Page 11
Enabling ADO
• Project menu
– References item
• Microsoft ActiveX Data Objects Library (latest 2.5)
Mark Dixon, SoCCE
SOFT 131
Page 12
ADO RecordSet Object
• Used to interact with tables
• Properties
– BOF: true if at start of recordset (before first record)
– EOF: true if at end of recordset (after last record)
– Fields: used to get and set data values
• Methods
–
–
–
–
–
–
Open: used to open recordset
MoveFirst: moves focus to first record
MovePrevious: moves focus to previous record
MoveNext: moves focus to next record
MoveLast: moves focus to last record
Close: closes recordset
Mark Dixon, SoCCE
SOFT 131
Page 13
Example 1: Music
Private Sub btnLoad_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Track", cs
rs.MoveFirst
Do Until rs.EOF
lstTracks.AddItem rs.Fields("TrackTitle").Value
rs.MoveNext
Loop
rs.Close
Set rs = Nothing
End Sub
btnLoad
Mark Dixon, SoCCE
lstTracks
SOFT 131
Page 14
Connection Strings
• Connection string – identify data source
Const cs = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=D:\Music.mdb;" & _
"Persist Security Info=False"
Private Sub btnLoad_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset
rs.Open "Track", cs
…
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 15
UDL files
• Generate connection strings
– Right click on desktop
– Select New, Text Document
– Rename to *.UDL (Yes to warning message)
– Double click
– Select provider
– Click Next
– Select or enter DB name
– Click Test Connection button
– Click OK
– Open with Notepad, cut & paste text
Mark Dixon, SoCCE
SOFT 131
Page 16
Searching for Data
• Recordset methods
– Find: searches for the next record to match
given criteria string:
• e.g. "Name = 'Smith' "
( " are for VB string)
( ' are for database string)
Mark Dixon, SoCCE
SOFT 131
Page 17
Example 2: Music v2
Private Sub lstTracks_Click()
Dim rs
As ADODB.Recordset
Dim strCriteria As String
Set rs = New ADODB.Recordset
rs.Open "Track", cs, adOpenDynamic
strCriteria = "TrackTitle = '" & _
lstTracks.List(lstTracks.ListIndex) & "'"
rs.Find strCriteria
txtTrackTitle.Text = rs.Fields("TrackTitle").Value
rs.Close
Set rs = Nothing
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 18
Changing Data
• Recordset methods
– AddNew: inserts a new record and makes it
current
– Update: sends changes back to DB
– Delete: deletes currently selected record
Mark Dixon, SoCCE
SOFT 131
Page 19
Example 3: Music v3
Private Sub txtTrackTitle_Change()
Dim rs
As ADODB.Recordset
Dim strCriteria As String
Set rs = New ADODB.Recordset
rs.Open "Track", cs, adOpenDynamic, adLockPessimistic
strCriteria = "TrackTitle = '" & _
lstTracks.List(lstTracks.ListIndex) & "'"
rs.Find strCriteria
rs.Fields("TrackTitle").Value = txtTrackTitle.Text
rs.Update
rs.Close
Set rs = Nothing
End Sub
Mark Dixon, SoCCE
SOFT 131
Page 20