Business to Business Interactions Bridging Relational Technology Cars R Us Tires R Us and XML eXtensible Markup Language (XML) Jayavel Shanmugasundaram Internet Order Fulfillment Purchasing Cornell University Application Application (Work done at University of Wisconsin & IBM Almaden Research Center) Relational Database Relational Database System System Are XML Database Systems Shift in Application Developers’ the Answer? Conceptual Data Model Cars R Us Tires R Us XML application XML application development development XML eXtensible Markup Language (XML) Code to convert XML Code to convert relational Internet data to relational data data to XML data Order Fulfillment Purchasing Application Application Relations XML Database XML Database Relational data System System manipulation A Solution Why use Relational Database Systems? Cars R Us Tires R Us • Highly reliable, scalable, optimized for performance, advanced functionality eXtensible Markup Language (XML) – Result of 30+ years of Research & Development – XML database systems are not “industrial strength” … and not expected to be in the foreseeable future Internet • Existing data and applications Order Fulfillment Purchasing – XML applications have to inter-operate with existing Application Application relational data and applications XML Translation Layer XML Translation Layer – Not enough incentive to move all existing business applications to XML database systems Relational Database Relational Database System System • Remember object-oriented database systems? 1
Bridging Relational Technology XML Translation Layer and XML (Contributions) XML application XML application development development XML • Store and query XML documents – Harnesses relational database technology for this purpose [VLDB’99] Code to convert XML Code to convert relational • Publish existing relational data as XML XML Translation data to relational data data to XML data Layer documents – Allows relational data to be viewed in XML terms [VLDB’00] Relations Relational data manipulation Relational Data Outline PurchaseOrder • Motivation & High-level Solution Id Customer Day Month Year • Background (Relations, XML) 200I Cars R Us 10 June 1999 300I Bikes R Us null July 1999 • Storing and Querying XML Documents • Publishing Relational Data as XML Documents Item Payment • Conclusion Pid Pid Name Quantity Cost Installment Percentage 200I 1 200I Firestone Tire 50 2000.00 40% 300I Schwinn Tire 100 2500.00 200I 2 60% 300I 300I Trek Tire 20 400.00 1 100% 200I Goodyear Tire 200 8000.00 XML Document SQL Query Self-describing tags <PurchaseOrder id=“200I” customer=“Cars R Us”> <Date> <Day> 10 </Day> Find all the items bought by “Cars R Us” in the year 1999 <Month> June </Month> <Year> 1999 </Year> </Date> <Item name=“Firestone Tire” cost=“2000.00”> Select it.name <Quantity> 50 </Quantity> From PurchaseOrder po, Item it </Item> <Item name=“Goodyear Tire” cost=“8000.00”> Where po.customer = “Cars R Us” and Predicates <Quantity> 200 </Quantity> po.year = 1999 and </Item> po.id = it.pid Join <Payment> 40% </Payment> <Payment> 60% </Payment> </PurchaseOrder> 2
XML Document XML Document Self-describing tags <PurchaseOrder id=“200I” customer=“Cars R Us”> Self-describing tags <PurchaseOrder id=“200I” customer=“Cars R Us”> <Date> <Date> <Day> 10 </Day> <Day> 10 </Day> <Month> June </Month> <Month> June </Month> Nested structure Nested structure <Year> 1999 </Year> <Year> 1999 </Year> </Date> </Date> <Item name=“Firestone Tire” cost=“2000.00”> <Item name=“Firestone Tire” cost=“2000.00”> <Quantity> 50 </Quantity> <Quantity> 50 </Quantity> </Item> Nested sets </Item> <Item name=“Goodyear Tire” cost=“8000.00”> <Item name=“Goodyear Tire” cost=“8000.00”> <Quantity> 200 </Quantity> <Quantity> 200 </Quantity> </Item> </Item> <Payment> 40% </Payment> <Payment> 40% </Payment> <Payment> 60% </Payment> <Payment> 60% </Payment> </PurchaseOrder> </PurchaseOrder> XML Document XML Schema PurchaseOrder <PurchaseOrder id={integer} customer={string}> Self-describing tags <PurchaseOrder id=“200I” customer=“Cars R Us”> Date (Item)* (Payment)* <Date> </PurchaseOrder> <Day> 10 </Day> <Month> June </Month> Date <Date> Nested structure <Year> 1999 </Year> Day? Month Year </Date> </Date> <Item name=“Firestone Tire” cost=“2000.00”> Day <Day> {integer} </Day> <Quantity> 50 </Quantity> Nested sets </Item> Month <Month> {string} </Month> <Item name=“Goodyear Tire” cost=“8000.00”> <Quantity> 200 </Quantity> Year <Year> {integer} </Year> </Item> <Payment> 40% </Payment> Order Item <Item name={string} cost={float}> <Payment> 60% </Payment> Quantity </PurchaseOrder> </Item> … and so on XML Schema (contd.) XML Query PurchaseOrder <PurchaseOrder id={integer} customer={string}> Date? (Item | Payment)* Find all the items bought by “Cars R Us” in 1999 </PurchaseOrder> PurchaseOrder <PurchaseOrder id={integer} customer={string}> (Date | Payment*) (Item (Item Item)* Payment)* </PurchaseOrder> For $po in /PurchaseOrder Where $po/@customer = “Cars R Us” and $po/date/year = 1999 Return $po/Item PurchaseOrder <PurchaseOrder id={integer} customer={string}> Date Item (PurchaseOrder)* Payment </PurchaseOrder> 3
XML Query (contd.) Outline • Motivation & High-level Solution //Item • Background (Relations, XML) • Storing and Querying XML Documents //Item[5] • Publishing Relational Data as XML Documents • Conclusion //Item Before //Payment /(Item/(Item/Payment)*/(Payment | Item))*/Date Outline Storing and Querying XML Documents [Shanmugasundaram et. al., VLDB’99] • Motivation & High-level Solution • Background (Relations, XML) XML XML XML XML Schema Documents Query Result • Storing and Querying XML Documents XML Translation Layer – Relational Schema Design and XML Storage Relational SQL Relational – Query Mapping and Result Construction Tuples Schema Query Result • Publishing Relational Data as XML Documents Translation • Conclusion Information Relational Database System Desired Properties of Generated XML Schema Relational Schema � • All XML documents conforming to XML schema should be “mappable” to tuples in � PurchaseOrder <PurchaseOrder id={integer} customer={string}> (Date | (Payment)*) (Item (Item Item)* Payment)* • All queries over XML documents should be </PurchaseOrder> “mappable” to SQL queries over � • Not Required: Ability to re-generate XML schema from � 4
Simplifying XML Schemas Why is Simplification Possible? • XML schemas can be “simplified” for • Structure in XML schemas can be captured: translation purposes – Partly in relational schema – Partly as data values PurchaseOrder <PurchaseOrder id={integer} customer={string}> (Date | (Payment)*) (Item (Item Item)* Payment)* PurchaseOrder <PurchaseOrder id={integer} customer={string}> </PurchaseOrder> Date? (Item)* (Payment)* </PurchaseOrder> • Order field to capture order among siblings PurchaseOrder <PurchaseOrder id={integer} customer={string}> Date? (Item)* (Payment)* • Sufficient to answer ordered XML queries </PurchaseOrder> – PurchaseOrder/Item[5] • All without undermining storage and query – PurchaseOrder/Item AFTER PurchaseOrder/Payment functionality! • Sufficient to reconstruct XML document Simplification Desiderata Translation Normal Form • Simplify structure, but preserve differences • An XML schema production is either of the that matter in relational model form: – Single occurrence (attribute) P <P attr 1 ={type 1 } … attr m ={type m }> – Zero or one occurrences (nullable attribute) a 1 … a p a p+1 ? … a q ? a q+1 *… a r * – Zero or more occurrences (relation) </P> PurchaseOrder <PurchaseOrder id={integer} customer={string}> where a i ≠ a j (Date | (Payment)*) (Item (Item Item)* Payment)* </PurchaseOrder> • … or of the form: P <P attr 1 ={type 1 } … attr m ={type m }> PurchaseOrder <PurchaseOrder id={integer} customer={string}> {type} Date? (Item)* (Payment)* </PurchaseOrder> </P> Simplified XML Schema Example Simplification Rules PurchaseOrder <PurchaseOrder id={integer} customer={string}> (e 1 | e 2 ) e 1 ? e 2 ? Date (Item)* (Payment)* </PurchaseOrder> (Date | (Payment)*) (Item (Item Item)* Payment)* Date <Date> Day? Month Year </Date> Date? (Item)*? (Item (Item Item)* Payment)* Day <Day> {integer} </Day> Month <Month> {string} </Month> e*? e* Year <Year> {integer} </Year> Date? (Payment)*? (Item (Item Item)* Payment)* Item <Item name={string} cost={float}> Quantity </Item> Date? (Item)* (Item (Item Item)* Payment)* … and so on 5
Recommend
More recommend
