Download - Mark Dixon, SoCCE SOFT 131Page 1 17 – Persistent data storage: relational databases and ADO
Mark Dixon, SoCCE SOFT 131 Page 1
17 – Persistent data storage: relational databases and ADO
Mark Dixon, SoCCE SOFT 131 Page 2
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 3
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 4
Record
Field
Flat files: Data Duplication
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
Track
Mark Dixon, SoCCE SOFT 131 Page 5
Relations (tables)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
Down with the Sickness 4
Artist ID
Artist Name Country
1 Black Sabbath UK
2 Aerosmith US
3 Alien Ant Farm US
4 Disturbed US
Track
Artist
Primary Key
Foreign Key
Mark Dixon, SoCCE SOFT 131 Page 6
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 7
Exercise: Prescriptions• Identify duplication and separate:
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
Prescription
Mark Dixon, SoCCE SOFT 131 Page 8
Exercise: Solution
Date PatientID DrugID
6 Jan 04 1 1
11 Jan 04 2 2
18 Jan 04 3 1
5 Feb 04 4 1
8 Feb 04 2 2
10 Feb 04 2 3
Prescription
PatientID Surname Forenames
1 Jones Alison
2 Smith Bob
3 Hope John
4 Johnson Sally
Patient
DrugID Drug Name
1 Co-codamol
2 Tegretol
3 Sorbitol
Drug
Mark Dixon, SoCCE SOFT 131 Page 9
People Database (with Hobbies)ID Surname Forenames Phone email
1 Dixon Mark 01752 232556 [email protected]
2 Smith John 01752 111111 [email protected]
3 Jones Sally 01752 888888 [email protected]
4 Bloggs Fred 01752 123123 [email protected]
5 Anderson Genny 01752 987987 [email protected]
HobbyID Description PersonID
1 Archery 1
2 Herpetology 1
3 Music 1
4 Football 2
5 Rugby 2
6 Hitting people with swords 1
Hobby
Person
Mark Dixon, SoCCE SOFT 131 Page 10
Entity-relationship diagrams• Each table in db
– stores details of entity• shown as rectangular box
•Relationships between tables
–represent relationships between entities
•shown as line between entities (boxes)
Person Hobby
Mark Dixon, SoCCE SOFT 131 Page 11
Relationship Types• One-to-one
• One-to-many
• Many-to-one
• Many-to-many– (can't be implemented in relational database)
A B
A B
A B
A B
Mark Dixon, SoCCE SOFT 131 Page 12
Exercise: Which relationship type?
ID Surname Forenames Phone email
1 Dixon Mark 01752 232556 [email protected]
2 Smith John 01752 111111 [email protected]
3 Jones Sally 01752 888888 [email protected]
4 Bloggs Fred 01752 123123 [email protected]
5 Anderson Genny 01752 987987 [email protected]
HobbyID Description PersonID
1 Archery 1
2 Herpetology 1
3 Music 1
4 Football 2
5 Rugby 2
6 Hitting people with swords 1
Hobby
Person
Person
Hobby
Mark Dixon, SoCCE SOFT 131 Page 13
Database Management Systems• DBMS provides facilities for:
– creating and changing databases• add/remove records• add/remove fields• add/remove data
– For example:• Microsoft Access• dBase• Borland Paradox• MySQL• Microsoft SQL Server• Oracle
home/small business
large scale
Mark Dixon, SoCCE SOFT 131 Page 14
MS Access
Music database
Mark Dixon, SoCCE SOFT 131 Page 15
ActiveX Data Objects (what & why)
• ActiveX Data Objects (ADO)– common database interface
• allow you to write code for any DBMS
VB orVB Script
codeADO
MS Access
MS SQL Server
…
…DB front end
Mark Dixon, SoCCE SOFT 131 Page 16
Enabling ADO• Project menu
– References item• Microsoft ActiveX Data Objects Library (latest 2.7)
Mark Dixon, SoCCE SOFT 131 Page 17
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 18
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 = NothingEnd Sub
btnLoad lstTracks
Mark Dixon, SoCCE SOFT 131 Page 19
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 20
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 21
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 22
Example: Music v2Private Sub lstTracks_Click()Dim rs As ADODB.RecordsetDim 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 = NothingEnd Sub
Mark Dixon, SoCCE SOFT 131 Page 23
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 24
Example: Music v3Private Sub txtTrackTitle_Change()Dim rs As ADODB.RecordsetDim 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 = NothingEnd Sub