Transcript slides - Plymouth

17 – 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
– create an entity-relationship diagram from a
database
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: 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: 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
People Database (with Hobbies)
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon, SoCCE
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
SOFT 131
PersonID
1
1
1
2
2
1
Page 9
Entity-relationship diagrams
• Each table in db
– stores details of entity
• shown as rectangular box
Person
Hobby
•Relationships between tables
–represent relationships between entities
•shown as line between entities (boxes)
Mark Dixon, SoCCE
SOFT 131
Page 10
Relationship Types
• One-to-one
A
B
• One-to-many
A
B
• Many-to-one
A
B
• Many-to-many
– (can't be implemented in relational database)
A
Mark Dixon, SoCCE
B
SOFT 131
Page 11
Exercise: Which relationship type?
Person
ID
1
2
3
4
5
Surname
Dixon
Smith
Jones
Bloggs
Anderson
Forenames
Mark
John
Sally
Fred
Genny
Phone
01752 232556
01752 111111
01752 888888
01752 123123
01752 987987
email
[email protected]
[email protected]
[email protected]
[email protected]
[email protected]
Hobby
HobbyID
1
2
3
4
5
6
Mark Dixon, SoCCE
Description
Archery
Herpetology
Music
Football
Rugby
Hitting people with swords
PersonID
1
1
1
2
2
1
SOFT 131
Person
Hobby
Page 12
Database Management Systems
• DBMS provides facilities for:
– creating and changing databases
• add/remove records
• add/remove fields
• add/remove data
– For example:
•
•
•
•
•
•
Mark Dixon, SoCCE
Microsoft Access
dBase
Borland Paradox
MySQL
Microsoft SQL Server
Oracle
SOFT 131
home/small business
large scale
Page 13
MS Access
Music database
Mark Dixon, SoCCE
SOFT 131
Page 14
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 15
Enabling ADO
• Project menu
– References item
• Microsoft ActiveX Data Objects Library (latest 2.7)
Mark Dixon, SoCCE
SOFT 131
Page 16
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 17
Example: 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 18
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 19
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 20
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 21
Example: 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 22
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 23
Example: 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 24