XML and Databases

1
XML and Databases

• CS 8630 Database Systems Final Project

By Roger S. Huff
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 lt 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 (contd)

• 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 (contd)

• 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

• lt?xml version1.0 encodingUTF-8
  standaloneyes?gt
• lt?xmlstylesheet type text/sxl href
  staff_list.xsl?gt
• lt!DOCTYPE STAFFLIST SYSTEM staff_list.dtdgt
• ltSTAFFLISTgt
• ltSTAFF branchNo B005gt
• ltSTAFFNOgtSL21lt/STAFFNOgt
• ltNAMEgt
• ltFNAMWgtJohnlt/FNAMEgtltLNAMWgtWhitelt/LNAMEgt
• lt/NAMEgt
• ltPOSITIONgtManagerlt/POSITIONgt
• ltDOBgt1-Oct-45lt/DOBgt
• ltSALARYgt30000lt/SALARYgt
• lt/STAFFgt
• ltSTAFFgt branchNo B003gt
• ltSTAFFNOgtSG37lt/STAFFNOgt
• ltNAMEgt
• ltFNAMEgtAnnlt/FNAMEgtltLNAMEgtBeechlt/LNAMEgt
• lt/NAMEgt
• ltPOSITIONgtAssistantlt/POSITIONgt

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 lt!ELEMENT STAFFLIST
(STAFF)gt lt!ELEMENT STAFF (NAME, POSITION,
DOB?,SALARY)gt lt!ELEMENT NAME (FNAME,
LNAME)gt lt!ELEMENT FNAME (PCDATA)gt lt!ELEMENT
LNAME (PCDATA)gt lt!ELEMENT POSITION
(PCDATA) lt!ELEMENT DOB (PCDATA) gt lt!ELEMENT
SALARY (PCDATA)gt lt!ATTLIST STAFF branchNo CDATA
IMPLIEDgt
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
Tables Tables Tables Tables
Forms Meals Blood_Sugar_Readings Daily_Guide Food
Diabetes Control CRUD CRUD
View/Update Blood Sugar Readings CRUD
View/Update Food Items CRUD
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.SetDataBindin
g(ds, "Daily_Guide") ocb New
OracleCommandBuilder(dailyguide_da)
dailyguide_da.UpdateCommand ocb.GetUpdateCommand
dailyguide_da.InsertCommand
ocb.GetInsertCommand dailyguide_da.UpdateC
ommand 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 XML Private Sub Button5_Click(ByVal
sender As System.Object, ByVal e As
System.EventArgs) Handles Button5.Click
ds.Clear() ds.ReadXml(".\meals_and_dailygu
ide.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. (contd)

• 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. (contd)

• 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. (contd)

• 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. (contd)

• 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. (contd)

• 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. (contd)

• 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. (contd)

• 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. (contd)

• 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/?selectedevent
• UTLEY
• Utley, Craig. (2001). A Programmers
  Introduction to Visual Basic.NET. Indianapolis
  SAMS.
• World Wide Web (from http//www.oasis-open.org/cov
  er/xml.htmlapplications)
• World Wide Web (from http//www.w3.org/AudioVideo
  /).
• World Wide Web (from http//www.dotnetspider.com/T
  echnology/Tutorials/DataSetOperations.aspx )
• World Wide Web (from http//www.able-consulting.co
  m/dotnet/adonet/Data_Providers.htm )