XML AND DATABASE DESIGN

1

• XML AND DATABASE DESIGN

Jason Moon
CS 8630, Summer 2004
2
XML Basics

• What is XML?
• eXtensible Markup Language
• Created to overcome the limitations found in HTML
• A cross-platform technology
• Developed by the World Wide Web Consortium (W3C)

3
XML Basics cont.

• Advantages of XML
• XML adopts a very strict syntax so browsers do
  not need to include any tolerance code
• This leads to faster and smaller browsers
• XML does not use predefined tags
• This allows you to create your own custom markup
  tags and define how they are used

4
Other XML Technologies

• APIs
• DOM
• SAX
• Style Sheets
• XSL
• XSLT
• Query Languages
• XQuery
• XPath
• Linking Language
• XLink
• Schemas
• DTD
• XMLSchema

5
Project Summary

• The purpose of the project was to create a simple
  database system for Pleasant Valley South Baptist
  Church
• The church wanted a way to keep track of all
  members, Sunday School records, and jobs within
  the church
• I started this project my senior year in
  college.
• The project is still a work in progress
• More knowledge is needed to finish the final
  product

6
E-R Diagram
7
Relational Schema
8
XML Data Example

• lt?xml version"1.0" encoding"UTF-8"?gt
• lt?xmlstylesheet href"persons5.xsl"
  type"text/xsl"?gt
• ltpersons
• xmlnsxsi"http//www.w3.org/2001/XMLSchema-instan
  ce" xsinoNamespaceSchemaLocation"persons.xsd"gt
• ltpersongt
• ltpidgt0001lt/pidgt
• ltlnamegtMoonlt/lnamegt
• ltfnamegtJasonlt/fnamegt
• ltmnamegtDaniellt/mnamegt
• ltphonegt555-5555lt/phonegt
• ltemailgtaol.comlt/emailgt
• ltaddressgt224 South Roadlt/addressgt
• ltcitygtRomelt/citygt
• ltstategtGeorgialt/stategt
• ltzipgt30165lt/zipgt
• ltclassIDgt45lt/classIDgt
• lt/persongt

9
XSL Example

• lt?xml version"1.0"?gt
• ltxslstylesheet version"1.0" xmlnsxsl"http//ww
  w.w3.org/1999/XSL/Transform"gt
• ltxsltemplate match"/"gt
• lthtmlgt ltheadgt lttitlegtMembers Outputlt/titlegt
  lt/headgt
• ltbodygt ltxslapply-templates /gt lt/bodygt lt/htmlgt
• lt/xsltemplategt
• ltxsltemplate match"persons"gt
• lttable width"100" boarder"2"gt
• lttr bgcolor"silver"gt
• lttdgtPidlt/tdgt
• lttdgtLast Namelt/tdgt
• lttdgtFirst Namelt/tdgt
• lttdgtMiddle Namelt/tdgt
• lttdgtPhonelt/tdgt
• lttdgtEmaillt/tdgt
• lttdgtAddresslt/tdgt

10
XSL Example cont

• ltxslfor-each select"person"gt
• ltxslsort select"lname" order"ascending"
  data-type"text"/gt
• lttrgt
• lttdgtltxslvalue-of select"pid"/gtlt/tdgt
• lttdgtltxslvalue-of select"lname"/gtlt/tdgt
• lttdgtltxslvalue-of select"fname"/gtlt/tdgt
• lttdgtltxslvalue-of select"mname"/gtlt/tdgt
• lttdgtltxslvalue-of select"phone"/gtlt/tdgt
• lttdgtltxslvalue-of select"email"/gtlt/tdgt
• lttdgtltxslvalue-of select"address"/gtlt/tdgt
• lttdgtltxslvalue-of select"city"/gtlt/tdgt
• lttdgtltxslvalue-of select"state"/gtlt/tdgt
• lttdgtltxslvalue-of select"zip"/gtlt/tdgt
• lttdgtltxslvalue-of select"classID"/gtlt/tdgt
• lt/trgt
• lt/xslfor-eachgt
• lt/tablegt

11
Output Using XSL
12
Form Example
13
Example of SQL Query

• SQLgt SELECT PID, LNAME, FNAME, C.CLASSNAME FROM
  PERSONS P, CLASSES C WHERE P.CLASSID C.CLASSID
• PID LNAME FNAME
  CLASSNAME
• ---------- ------------- --------------------
  --------------------
• 1 Moon Jason
  College
• 2 Smith John
  College
• 3 Morris Janet
  Singles
• 4 Franks John
  High School
• 5 Stevens Shannon
  Middle School

14
Search JavaScript Code

• function testResults(form)
• var TestVar form.inputbox.value
• var str "Search Results"
• var test
• var count 0
• var message "No Results Found"
• var list"ltcaptiongt"str" Listlt/captiongt"
• // write make model column headers
• list"lttable width'390px' border'1px'
  cellspacing'0px'gt"
• list"lttrgtltthgtFirst Namelt/thgtltthgtLast
  Namelt/thgt"

15
Code cont.

• // write rows
• for(var i0 ilt lnames.length i)
• test getFname(i)
• if(test TestVar)
• // toggle row background colour
• list(((i1)2)0)? "lttr
  style'backgroundsilver'gt""lttr
  style'backgroundwhite'gt"
• // write make model cells
• list"lttdgt"getFname(i)"lt/tdgtlttdgt"get
  Lname(i)"lt/tdgt"
• count count 1
• list"lt/tablegt"
• // output the table
• sho.innerHTMLlist
• if(count 0)

16
Problems Encountered

• Adding/Removing Nodes
• Two commands
• appendChild
• removeChild
• Working with XML data online
• PHP
• ASP
• More advanced querying
• XQuery

17
Conclusion

• Any Questions???