16 Persistent data storage relational databases and ADO

16 – Persistent data storage: relational databases and ADO Mark Dixon, So. CCE 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, So. CCE 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, So. CCE SOFT 131 Page 3

Flat files: Data Duplication Field Track Record Mark Dixon, So. CCE 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

Track 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 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, So. CCE 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, So. CCE SOFT 131 Page 6

Exercise 1: Prescriptions • Identify duplication and separate: Prescription Mark Dixon, So. CCE 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. ID Surname Forenames Prescription 1 Jones Alison 2 Smith Bob 3 Hope John 4 Johnson Sally Date Patient. ID Drug. ID 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 Drug. ID Drug Name 10 Feb 04 2 3 1 Co-codamol 2 Tegretol 3 Sorbitol Mark Dixon, So. CCE Drug 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 • d. Base – Large scale • Microsoft SQL Server • Oracle Mark Dixon, So. CCE SOFT 131 Page 9

MS Access Mark Dixon, So. CCE SOFT 131 Page 10

Active. X Data Objects (what & why) • Active. X 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, So. CCE MS SQL Server … … SOFT 131 Page 11

Enabling ADO • Project menu – References item • Microsoft Active. X Data Objects Library (latest 2. 5) Mark Dixon, So. CCE SOFT 131 Page 12

ADO Record. Set 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 Move. First: moves focus to first record Move. Previous: moves focus to previous record Move. Next: moves focus to next record Move. Last: moves focus to last record Close: closes recordset Mark Dixon, So. CCE SOFT 131 Page 13

Example 1: Music Private Sub btn. Load_Click() Dim rs As ADODB. Recordset Set rs = New ADODB. Recordset rs. Open "Track", cs rs. Move. First Do Until rs. EOF lst. Tracks. Add. Item rs. Fields("Track. Title") rs. Move. Next Loop rs. Close Set rs = Nothing End Sub btn. Load Mark Dixon, So. CCE lst. Tracks 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 btn. Load_Click() Dim rs As ADODB. Recordset Set rs = New ADODB. Recordset rs. Open "Track", cs … End Sub Mark Dixon, So. CCE 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, So. CCE 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, So. CCE SOFT 131 Page 17

Example 2: Music v 2 Private Sub lst. Tracks_Click() Dim rs As ADODB. Recordset Dim str. Criteria As String Set rs = New ADODB. Recordset rs. Open "Track", cs, ad. Open. Dynamic str. Criteria = "Track. Title = '" & _ lst. Tracks. List(lst. Tracks. List. Inde rs. Find str. Criteria txt. Track. Title. Text = rs. Fields("Track. Title" rs. Close Set rs = Nothing End Sub Mark Dixon, So. CCE SOFT 131 Page 18

Changing Data • Recordset methods – Add. New: inserts a new record and makes it current – Update: sends changes back to DB – Delete: deletes currently selected record Mark Dixon, So. CCE SOFT 131 Page 19

Example 3: Music v 3 Private Sub txt. Track. Title_Change() Dim rs As ADODB. Recordset Dim str. Criteria As String Set rs = New ADODB. Recordset rs. Open "Track", cs, ad. Open. Dynamic, ad. Lock. Pessimistic str. Criteria = "Track. Title = '" & _ lst. Tracks. List(lst. Tracks. List. Index) & "'" rs. Find str. Criteria rs. Fields("Track. Title"). Value = txt. Track. Title. Text rs. Update rs. Close Set rs = Nothing End Sub Mark Dixon, So. CCE SOFT 131 Page 20