Database vs. DBMS

1. Database vs. DBMS • Database • A collection of organized, related tables (data stored in rows and columns) • Database Management System • Also known as DBMS • Software used to create and maintain a database

2. Entity-Relationship Diagram • Also known as ERD • Graphical representation showing the relationships of entities in a database • Entities • things of interest to business (products, employees, suppliers, customers, purchases, sales,…) • Relationships • real-world associations between entities • products purchased by customers • employees who designed a particular part • suppliers who provided a particular raw material

3. ERD Cardinality Student Course Teacher Related to one Related to one or zero (optional) Related to zero or more Related to one or more Each course belongs to a specific teacher (manager) A teacher may teach one or more courses A student can sign up for one or more courses A course may have no students (not offered)

4. Normalized Databases • Normalization • A process of organizing database tables to increase stability through reduced (controlled) data redundancy. • Higher normal forms further restrict how attributes are grouped within records • 3NF (Third Normal Form) is the generally accepted, minimum standard for designing a database • Every nonkey field is directly related to only to the entire key field and nothing else • {Emp # (key), Emp Name, Dept Code, Dept Name} is not in 3NF since Dept Name can be derived entirely from dept code (both non-key fields) • {Prod # (key), Prod Name, Qty, Cost, ValueinInventory} is not in 3NF since ValueInInventory is a calculated value (from Qty & Cost) • {RawMat # (key), Prod #, AmtReqrd} is not in 3NF since AmtReqrd is derived from both RawMat# and Prod #

5. Database Terminology Northwind DB tables Second record or row of Categories table describes condiments Relationship based on shared data in two tables Key fields Records 3, 6 8 are condiments

6. Databases Accessible by VB • VB Native format • Microsoft Jet database engine • Microsoft Access • Jet engine can also access • Excel, dBASE, FoxPro, Lotus, Paradox, text files • VB Professional with Open DataBase Connectivity (ODBC) drivers • Can also access SQL Server, Oracle, and DB2

7. Data Control • Provides access to data stored in databases using any one of three types of Recordset objects: • table • snapshot • synaset • Enables data navigation viewed through bound controls. • Without a Data control, data-bound controls on a form can't automatically access data. • Validate event occurs just before new record becomes current. data control

8. Data Control Properties • Caption • Text that appears inside the control on the form • Connect • Identifies DBMS source of database (Access) • DatabaseName • Assigns the database filename with path (use dialog) • ReadOnly • Defaults to False & must be set to true if user is not allowed to change contents • RecordSource • Assigns the table or query (view) of the database to be used by the VB program • BOF and EOF • Flags set to true when current record pointer positioned before first record or after last record

9. Data-Bound Controls • Same controls we’ve used thus far with additional properties set • Labels (user cannot change), Textboxes (user can change), Checkboxes (T/F or Y/N), etc. • DataSource • point to database (specify name of data control) • DataField • field name in database table that relates to this It is possible to navigate data in a database simply by designing a form with the data control and binding data-aware controls to the data control -- no coding is needed!

10. Programming the Data Control • Recordset Object • Defined by setting RecordSource property of data control • Accessing Recordset properties in code • datName.Recordset.Property • Since this represents a value, it can only be part of a statement (e.g., assign it to variable or assign something to it) • Applying methods to Recordset object • datName.Recordset.Method • Since this represents a process, it is written like a statement

11. Recordset Properties • Checking the database pointer • datName.Recordset. BOF • Flag that is true when record pointer is before first database record (select previous on first record) • datName.Recordset. EOF • Flag that is true when record pointer is after last database record (select next on last record) • Counting the number of DB records • datName.Recordset.RecordCount • Setting or tracking the current DB record • datName.Recordset.BookMark • Determining if desired record was found • datName.Recordset.NoMatch

12. Recordset Move & Refresh Methods • Navigating DB via Code • Move to next database record • datName.Recordset.MoveNext • Move to last database record • datName.Recordset.MoveLast • Move to previous database record • datName.Recordset.MovePrevious • Move to first database record • datName.Recordset.MoveFirst • Refresh method • Explicitly opens the database • datName.Refresh

13. Adding a First button cmdFirst_Click Move to db record 1 RecNum = 1 End Private Sub cmdFirst_Click() datEx.Recordset.MoveFirst lblRecNum.Caption = 1 End Sub

14. Adding a Last button cmdLast_Click Move to last db record RecNum = RecordCount End Private Sub cmdLast_Click() datEx.Recordset.MoveLast lblRecNum.Caption = _ datEx.Recordset.RecordCount End Sub

15. Adding a Next button cmdNext_Click Move to next db record False True EOF? Display already at end - go to start RecNum=RecNum+1 cmdFirst_Click End Private Sub cmdNext_Click() datEx.Recordset.MoveNext If datEx.Recordset.EOF Then Call MsgBox(“msg”,…) Call cmdFirst_Click Else lblRecNum.Caption = _ CInt(lblRecNum.Caption)+1 EndIf End Sub

16. Adding a Previous button cmdPrevious_Click Move to previous db record False True BOF? Display already at start - go to end RecNum=RecNum-1 cmdLast_Click End Private Sub cmdPrevious_Click() datEx.Recordset.MovePrevious If datEx.Recordset.BOF Then Call MsgBox(“msg”,…) Call cmdLast_Click Else lblRecNum.Caption = _ CInt(lblRecNum.Caption)-1 EndIf End Sub

17. Recordset Find Methods • Search for a specific type of record and then move to it in the specified direction. • Specifying search criteria <DBFieldName> <relational operator> <expression> • Find methods define starting point & search direction • Start at first record and search forwards • datName.Recordset.FindFirst (search criteria) • Start at last record and search backwards • datName.Recordset.FindLast (search criteria) • Start at current record and search forwards • datName.Recordset.FindNext (search criteria) • Start at current record and search backwards • datName.Recordset.FindPrevious (search criteria)

18. Searching Example 1) Save the current record CurRecord = datName.Recordset.BookMark 3) Assign search criteria SearchStr = InputBox(“prompt”, “title”, “default”) Target = “TableName.FieldName = `” & SearchStr & “`” 3) Use search criteria Call datName.Recordset.FindFirst(Target) If datName.RecordSet.NoMatch Then Call MsgBox(“ERROR”) Else ‘ whatever processing needs to be done with current record End If

19. Example: Find similar name Private Sub cmdFindName_Click() Dim CurRecord As Variant Dim SearchString As String ' Save location of current record CurRecord = datEmployee.Recordset.Bookmark SearchString = "EmpName Like '" & _ txtSearchName.Text & "*'" Call datEmployee.Recordset.FindFirst(SearchString) If datEmployee.Recordset.NoMatch Then Call MsgBox("No such employee", _ vbOkOnly + vbInformation) ' Move pointer to previously saved location datEmployee.Recordset.Bookmark = CurRecord Else ' fill display controls with found record’s data Call ShowDBData End If End Sub

20. Methods to change DB content Delete current database record datName.Recordset.Delete be sure to add step to go to a valid record AddNew database record datName.Recordset.AddNew clears data bound controls so user can enter new data DB automatically updated by moving to another record or explicitly saving record Edit database record datName.Recordset.Edit moves current DB record to copy buffer to allow changes to occur Update database with addition datName.Recordset.Update explicitly saves newly entered data Reset db to previous values datName.Recordset.UpdateControls resets data bound controls to previous values

21. Example: Add a new record Private Sub cmdAddRec_Click() If cmdAddRec.Caption = "&Add" Then ' 1) Clear input controls Call DisableNavigation Call datEmployee.Recordset.MoveLast txtID.Text = Format(datEmployee.Recordset!empid + 1) Call datEmployee.Recordset.AddNew txtEmpName.Text = "" txtBirthdate.Text = "" txtPayRate.Text = "" ' Change caption to Save and disable other buttons cmdAddRec.Caption = "&Save" Call txtEmpName.SetFocus Else ' the caption must be &Save ' 2) Write user input to DB Call WriteDBData datEmployee.Recordset!empid = txtID.Text Call datEmployee.Recordset.Update ' Change caption to Add and enable other buttons cmdAddRec.Caption = "&Add" Call EnableNavigation End If End Sub

22. Example: Delete a record Private Sub cmdDeleteRec_Click() Dim CurRec As Variant CurRec = datEmployee.Recordset.Bookmark' The record to delete datEmployee.ReadOnly = False Call datEmployee.Refresh Call datEmployee.Recordset.MoveLast datEmployee.Recordset.Bookmark = CurRec Call datEmployee.Recordset.Delete datEmployee.ReadOnly = True Call datEmployee.Refresh ' Move to the last record Call cmdLast_Click If datEmployee.Recordset.EOF Then ' Last record was deleted Call cmdFirst_Click If datEmployee.Recordset.BOF Then ' No records left Call MsgBox("There are no records left-the database" & _ " is empty", vbOkOnly + vbInformation) End If End If End Sub

23. Example: Change a record Private Sub cmdModifyRec_Click() ' The current record will be the one to be modified If cmdModifyRec.Caption = "&Modify" Then ' 1) Allow edit of record Call DisableNavigation Call datEmployee.Recordset.Edit ' Change caption to Save and disable other buttons Call txtEmpName.SetFocus cmdModifyRec.Caption = "&Save" cmdDeleteRec.Enabled = False cmdAddRec.Enabled = False Else ' the caption must be &Save ' 2) Copy updates to DB Call WriteDBData Call datEmployee.Recordset.Update ' Change caption to Modify and enable other buttons cmdModifyRec.Caption = "&Modify" cmdDeleteRec.Enabled = True cmdAddRec.Enabled = True Call EnableNavigation End If End Sub

24. Examples: The primary key must be non-blank All data entered must be of correct type and length The database table has valid records (not an empty table) Pseudocode/Algorithm If a required field is empty and both BOF and EOF are False Then Show error msg Reset error condition so program can run Don’t save changes Endif Preventing Errors • Add code to the Validate Event of data control • Add error handlers in all procedures that access DB

25. Checking for Empty Recordsets If BOF and EOF Then Display error message End If • By checking for both BOF & EOF, you are checking for a recordset with no members

26. Queries: Joining DB tables 1) Define query to get all unique field names from both tables Query = “Select * From TableName1, TableName2 Where ” & _ “ TableName1.FieldX = TableName2.FieldY” Query = “Select TableName1.FieldA, TableName1.FieldB, “ & _ “TableName2.FieldZ Where TableName1.FieldX = “ & _ “TableName2.FieldY” 2) Assign Query to RecordSource property & Open the DB datName.RecordSource = Query Call datName.Refresh 3) Start at the first record Call datName.Recordset.MoveFirst 4) Assign value in current DB field to control on form lblName.Caption = datName.Recordset(“FieldName”) lblName.Caption = datName.Recordset!FieldName

27. Filling list with DB field choices 1) Get filename from user (common dialog) 2) Assign user’s file selection to DatabaseName property of data control 3) Define query to get all unique field names Query = “Select Distinct FieldName From TableName” datName.RecordSource = Query 4) Use Refresh method to open the database Call datName.Refresh 5) Clear the list & add any general choices (“Show All”) 6) For each item in the database, add the item to the list and go to next item until done Call cboName.AddItem(datName.Recordset(”FieldName")) Call cboName.AddItem(datName.Recordset!FieldName)

28. Returning select DB records 1) Define query using user’s selection a) General “Show All” choice: Query = “Select * From TableName” b) Otherwise: Query = “Select * From TableName Where “ & _ “FieldName = `“ & cboName.Text & “`” 2) Assign Query to data control datName.RecordSource = Query 3) Use Refresh method to open the database Call datName.Refresh 4) If flexgrid is used on the form, it will automatically contain all fields of the desired records based on the search criteria