XML and Databases

1. XML and Databases By Roger S. Huff CS 8630 Database Systems Final Project 7/19/2004

2. Topic • XML. Read Chapter 29. Implement an application that queries an XML database and an SQL database. Compare both of them.

3. Semi-Structured Data • Foundation for Extensive Markup Language (XML) • Schema definition included with the data. • Useful for: • When WEB sources are treated like a database and it is not possible to constrain the sources with a schema. • Flexible format for data exchange between databases. • XML is becoming a popular standard.

4. More on XML • SGML • XML is Derived form Standard Generalized Markup Language (SGML) • Standard for over a decade • Defining structured document types • Allows separation of document (two types) • Structure of the document • Document text • Document Type Definition (DTD) • Powerful document management system • Not widely adopted because of complexity • XML • Restricted version of SGML • Similar functions, but simpler • Extensibility, structure, and validation • Will not replace SGML or HTML, but compliment them (Web data exchange) • Replacing Electronic Data Interchange (EDI)

5. Advantages • Simplicity • Simple standard < 50 pages • Text-based language • Human legible • Reasonably clear • Open Standard and Platform/Vendor-Independent (…almost) • Mostly Platform/Vendor Independent (Oracle?) • Restricted form of SGML, an ISO standard • Based on Unicode character sets so supports the worlds alphabets (ISO 10646) • Extensibility • Allows users to define their own tags

6. Advantages (cont’d) • Reuse • Tags built once can be reused by many applications • Separation of content and presentation • Document content • Presentation of data • Referred as “Write once, publish anywhere” • Load balancing • Improved by data delivered to desktop for local computation allowing server resources to be allocated to other requests.

7. Advantages (cont’d) • Multiple Source Integration Support • Data combined easily • Applicable Data Description • Self describing • User-Defined Tags. • More Advanced Search Engines • Search engines will parse description-tags for information instead of using meta-tags • New Opportunities

8. Disadvantages • Security • Large files become cumbersome because of the tags.

9. What Makes Up an XML Document? Declaration Section <?xml version=”1.0” encoding=UTF-8” standalone=”yes”?> <?xml:stylesheet type = “text/sxl” href = “staff_list.xsl:?> <!DOCTYPE STAFFLIST SYSTEM “staff_list.dtd”> <STAFFLIST> <STAFF branchNo = “B005”> <STAFFNO>SL21</STAFFNO> <NAME> <FNAMW>John</FNAME><LNAMW>White</LNAME> </NAME> <POSITION>Manager</POSITION> <DOB>1-Oct-45</DOB> <SALARY>30000</SALARY> </STAFF> <STAFF> branchNo = “B003”> <STAFFNO>SG37</STAFFNO> <NAME> <FNAME>Ann</FNAME><LNAME>Beech</LNAME> </NAME> <POSITION>Assistant</POSITION> <SALARY>12000</SALARY> </STAFF> </STAFFLIST> ROOT TAG Start Tag Content Attribute End Tag

10. Document Type Definition (DTD) • Defines the valid syntax of an XML Document • Element type declarations • Attribute-list declarations • Entity declarations • Notation declarations • Processing Instructions • Comments • Parameter entity references DTD Example <!ELEMENT STAFFLIST (STAFF)*> <!ELEMENT STAFF (NAME, POSITION, DOB?,SALARY)> <!ELEMENT NAME (FNAME, LNAME)> <!ELEMENT FNAME (#PCDATA)> <!ELEMENT LNAME (#PCDATA)> <!ELEMENT POSITION (#PCDATA) <!ELEMENT DOB (#PCDATA) > <!ELEMENT SALARY (#PCDATA)> <!ATTLIST STAFF branchNo CDATA #IMPLIED>

11. XML APIs • Document Object Model (DOM) • Loads entire XML document where each tag is parsed as a node. Software can then traverse the tree and search parent and child nodes. • Can be inefficient with large files • Simple API for XML (SAX) • Event based • Parses based on call backs. • linear

12. Diabetes Control Application • Purpose: • Simple database application to log important information for a person with diabetes. Possibly, once matured could be a PDA application to monitor carbohydrates intake and blood sugar levels. • Implemented using Microsoft Visual Basic .NET • Used the Oracle Data Provider from Microsoft • Used the DataSet object (DOM Model)

13. Diabetes Control Application CRUD Table

14. Diabetes Control Application • 4 Tables • Meals • Contains the meals planned for the future and a history of meals eaten in the past. • Blood_Sugar_Readings • Contains a log of the blood sugar readings in the past. • Daily_Guide • Contains the limits for blood sugar, carb intake, and the next doctors appointment. • Food • Contains a list of food, their category, serving size, and carbohydrates per serving. • Implemented using Oracle and XML in the same application. • Execute the Diabetes_Control.exe

15. Diabetes Control Application • To log in: • Enter the Database name and click ‘OK’ • Enter the username and click ‘OK’ (owner of the tables) • Enter the password and click ‘OK’ • To demonstrate the Oracle interface: • Click load from Oracle button. • In the data grid update information • Add records • Delete records • Modify records • Click Update Oracle button Code Private Sub Form1_Load(ByVal sender As System.Object, B …. DB_name = InputBox("Enter the database to connect to: ") username = InputBox("Please enter your username: ") password = InputBox("Enter your password: ") conn.ConnectionString = "Data Source=" + DB_name + ";" + _ "User ID=" + username + ";" + _ "Password=" + password + ";"

16. Diabetes Control Application • Description of Diabetes Control Form • Main Form • Load From Oracle • Update Oracle • Load From XML • Update XML

17. Diabetes Control Application DLL Reference System.Data.Oracle.dll Imports System.Data.OracleClient Public Class Form1 Inherits System.Windows.Forms.Form Dim ds As DataSet = New DataSet Dim strConnect As String Dim conn As OracleConnection = New OracleConnection Dim dailyguide_da As OracleDataAdapter Dim meals_da As OracleDataAdapter Public username As String Public password As String Public DB_name As String Data Adapter, DataSet, and Connection

18. Diabetes Control Application Binding Data to Grid Load From Oracle Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button4.Click Dim ocb As OracleCommandBuilder Dim dailyguide_strCmd As String Dim meals_strCmd As String ds.Clear() ' setting up connection string dailyguide_strCmd = "SELECT id,DAILY_GLUCOSE_LEVEL, DAILY_INTAKE_CARBS, NEXT_DOCTORS_APPT FROM Daily_Guide" meals_strCmd = "Select id,MEAL_DATE, MEAL_TYPE, CARB_SIZE, CARB_BUDGET, FOOD_GROUP1, FOOD_GROUP1_AMT, FOOD_GROUP2, FOOD_GROUP2_AMT, FOOD_GROUP3, FOOD_GROUP3_AMT, FOOD_GROUP4, FOOD_GROUP4_AMT, OTHER_ITEMS, OTHER_ITEMS_AMT from meals" If conn.State = ConnectionState.Closed Then conn.Open() End If Load From Oracle dailyguide_da = New OracleDataAdapter(dailyguide_strCmd, conn) dailyguide_da.TableMappings.Add("Table", "Daily_Guide") dailyguide_da.Fill(ds, "Daily_Guide") grdDailyGuide.SetDataBinding(ds, "Daily_Guide") ocb = New OracleCommandBuilder(dailyguide_da) dailyguide_da.UpdateCommand = ocb.GetUpdateCommand dailyguide_da.InsertCommand = ocb.GetInsertCommand dailyguide_da.UpdateCommand = ocb.GetDeleteCommand meals_da = New OracleDataAdapter(meals_strCmd, conn) meals_da.TableMappings.Add("Table", "Meals") meals_da.Fill(ds, "Meals") grdMeals.SetDataBinding(ds, "Meals") ocb = New OracleCommandBuilder(meals_da) meals_da.UpdateCommand = ocb.GetUpdateCommand meals_da.InsertCommand = ocb.GetInsertCommand meals_da.UpdateCommand = ocb.GetDeleteCommand End Sub Creating Update, Insert, and Delete Commands

19. Diabetes Control Application Load from XMLPrivate Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button5.Click ds.Clear() ds.ReadXml(".\meals_and_dailyguide.xml") grdDailyGuide.SetDataBinding(ds, "Daily_Guide") grdMeals.SetDataBinding(ds, "Meals") End Sub Update XML Private Sub Button2_Click_1(ByVal sender ds.WriteXml(".\meals_and_dailyguide.xml") End Sub Update Oracle Private Sub Button1_Click(ByVal sender As dailyguide_da.Update(ds) meals_da.Update(ds) End Sub

20. Diabetes Control App. (cont’d) • Click View/Update Blood Sugar Readings • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints. • Click load from Oracle button • Add records • Delete records • Modify records • Click Update Oracle button • Close Window

21. Diabetes Control Application • Description of View/ Update Blood Sugar Readings Form • Sub Form • Load From Oracle • Update Oracle • Load From XML • Update XML • Same Implementation of Code as for Diabetes Control

22. Diabetes Control App. (cont’d) • Click View/Update Food Items button • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints. • Click load from Oracle button • Add records • Delete records • Modify records • Click Update Oracle button • Close Window • Close Main Window

23. Diabetes Control Application • Description of View/ Update Food Items Form • Sub Form • Load From Oracle • Update Oracle • Load From XML • Update XML • Same Implementation of Code as for Diabetes Control

24. Diabetes Control Application • Execute the Diabetes_Control.exe and log in: • Enter the Database name and click ‘OK’ • Enter the username and click ‘OK’ (owner of the tables) • Enter the password and click ‘OK’ • From the Main Window • Click load from Oracle button. • Verify that the data includes changes made previously. • Click View/Update Blood Sugar Readings

25. Diabetes Control App. (cont’d) • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints. • Click load from Oracle button • Verify that the data includes changes made previously • Close Window • Click View/Update Food Items button • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints.

26. Diabetes Control App. (cont’d) • Click load from Oracle button • Verify that the data includes changes made previously. • Close Window • Close Main Window

27. Diabetes Control Application • To log in: • Enter the Database name and click ‘OK’ • Enter the username and click ‘OK’ (owner of the tables) • Enter the password and click ‘OK’ • To demonstrate the XML interface: • Click load from XML button. • In the data grid update information • Add records • Delete records • Modify records • Click Update XML button • Click View/Update Blood Sugar Readings

28. Diabetes Control App. (cont’d) • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints. • Click load from XML button • Add records • Delete records • Modify records • Click Update XML button • Close Window • Click View/Update Food Items button • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints.

29. Diabetes Control App. (cont’d) • Click load from XML button • Add records • Delete records • Modify records • Click Update XML button • Close Window • Close Main Window

30. Diabetes Control Application • Execute the Diabetes_Control.exe and log in: • Enter the Database name and click ‘OK’ • Enter the username and click ‘OK’ (owner of the tables) • Enter the password and click ‘OK’ • From the Main Window • Click load from XML button. • Verify that the data includes changes made previously. • Click View/Update Blood Sugar Readings

31. Diabetes Control App. (cont’d) • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints. • Click load from XML button • Verify that the data includes changes made previously • Close Window • Click View/Update Food Items button • Repeat log in • NOTE: Normally this information would be passed from one form to the other; however I was unable to create a global support object due to time constraints.

32. Diabetes Control App. (cont’d) • Click load from XML button • Verify that the data includes changes made previously. • Close Window • Close Main Window

33. XML and Oracle Comparison • Oracle was more difficult to implement than XML. • Oracle required • Database connections • Data adapters • A specialized dll (System.Data.Oracleclient.dll) • A DataSet Object • XML required • DataSet Object • Filename. (i.e. Food.xml)

34. XML and Oracle Comparison • Both the Oracle and XML implementations required setup • Oracle required • Creating tables • Interacting with SQL • XML required • Creating Document Tags • Creating Files.

35. XML and Oracle Comparison • Oracle has better data integrity • Oracle • Logging in • Harder to delete data without application • XML required • Files are located in a directory and can be easily deleted. Loosing all data. • Files can be opened and modified by notepad or some other editor.

36. Lessons Learned • Had there been more time I would have liked to implement a more robust application. • Budgeted Carbohydrates field as derived. • Dependencies on the food table for foods included in the meals table • Triggers to indicate that the budget carbohydrates is about to be exceeded.

37. Conclusion • In conclusion, the Visual Studio .NET environment is a very flexible and comfortable tool to develop code with. • DataSets and Data Providers • (i.e Oracle Data Provider from Microsoft) • Make accessing and updating databases easier. • Load application for Oracle • Very nice utility for modifying, accessing, updating databases.

38. Load for Oracle

39. XML and Databases • Sources • [CONNOLLY-BEGG] Connolly, Thomas & Begg, Carolyn (2002). Database Systems, A Practical Approach to Design, Implementation, and Management; Third Edition. Harlow, England: Addison-Wesley. • [YOUNG] Young, Michael J. (2000). Microsoft: Step-by-Step XML. Redmond: Microsoft Press. Events vs. Trees 06 July 2004. WWW http://sax.sourceforge.net/?selected=event • [UTLEY] Utley, Craig. (2001). A Programmer’s Introduction to Visual Basic.NET. Indianapolis: SAMS. • World Wide Web (from http://www.oasis-open.org/cover/xml.html#applications) • World Wide Web (from http://www.w3.org/AudioVideo/). • World Wide Web (from http://www.dotnetspider.com/Technology/Tutorials/DataSetOperations.aspx ) • World Wide Web (from http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm ) • World Wide Web (from http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/frlrfSystemDataOracleClient.asp ) • World Wide Web (fromhttp://msdn.microsoft.com/msdnmag/issues/02/02/cutting/ ) • World Wide Web (from http://www.able-consulting.com/dotnet/adonet/Data_Providers.htm#OracleNETDataProvider )