1Bridging Relational Technologyand XMLJayavel ShanmugasundaramCornell University(Work done at University of Wisconsin &IBM Almaden Research Center)Business to Business InteractionsTires R UsCars R UsOrder FulfillmentApplicationPurchasingApplicationInterneteXtensible Markup Language (XML)Relational DatabaseSystemRelational DatabaseSystemShift in Application Developers’ Conceptual Data ModelXMLRelationsCode to convert XMLdata to relational dataRelational datamanipulationCode to convert relationaldata to XML dataXML applicationdevelopmentXML applicationdevelopmentAre XML Database Systemsthe Answer?Tires R UsCars R UsXML DatabaseSystemOrder FulfillmentApplicationXML DatabaseSystemPurchasingApplicationInterneteXtensible Markup Language (XML)Why use Relational Database Systems?• Highly reliable, scalable, optimized for performance, advanced functionality– Result of 30+ years of Research & Development– XML database systems are not “industrial strength” … and not expected to be in the foreseeable future• Existing data and applications– XML applications have to inter-operate with existing relational data and applications– Not enough incentive to move all existing business applications to XML database systems• Remember object-oriented database systems?A SolutionTires R UsCars R UsRelational DatabaseSystemOrder FulfillmentApplicationPurchasingApplicationInterneteXtensible Markup Language (XML)XML Translation LayerXML Translation LayerRelational DatabaseSystem2XML Translation Layer(Contributions)• Store and query XML documents– Harnesses relational database technology for this purpose [VLDB’99]• Publish existing relational data as XML documents– Allows relational data to be viewed in XML terms [VLDB’00]Bridging Relational Technologyand XMLXMLRelationsCode to convert XMLdata to relational dataRelational datamanipulationCode to convert relationaldata to XML dataXML applicationdevelopmentXML applicationdevelopmentXML TranslationLayerOutline• Motivation & High-level Solution• Background (Relations, XML)• Storing and Querying XML Documents• Publishing Relational Data as XML Documents• ConclusionRelational DataPurchaseOrderId Customer200IYearMonthCars R Us 10 June 1999300IDayBikes R Us null July 1999ItemName200ICostFirestone Tire 50 2000.00200IQuantityGoodyear Tire 200 8000.00Pid300ITrek Tire 20300I Schwinn Tire 100PaymentInstallment40%PercentagePid300I100%200I60%121200I2500.00400.00SQL QueryFind all the items bought by “Cars R Us” in the year 1999Select it.nameFrom PurchaseOrder po, Item itWhere po.customer = “Cars R Us” andpo.year = 1999 andpo.id = it.pidPredicatesJoinSelf-describing tagsXML Document<PurchaseOrder id=“200I” customer=“Cars R Us”><Date><Day> 10 </Day><Month> June </Month><Year> 1999 </Year></Date><Item name=“Firestone Tire” cost=“2000.00”><Quantity> 50 </Quantity></Item><Item name=“Goodyear Tire” cost=“8000.00”><Quantity> 200 </Quantity></Item><Payment> 40% </Payment><Payment> 60% </Payment></PurchaseOrder>3XML Document<PurchaseOrder id=“200I” customer=“Cars R Us”><Date><Day> 10 </Day><Month> June </Month><Year> 1999 </Year></Date><Item name=“Firestone Tire” cost=“2000.00”><Quantity> 50 </Quantity></Item><Item name=“Goodyear Tire” cost=“8000.00”><Quantity> 200 </Quantity></Item><Payment> 40% </Payment><Payment> 60% </Payment></PurchaseOrder>Nested structureSelf-describing tagsXML Document<PurchaseOrder id=“200I” customer=“Cars R Us”><Date><Day> 10 </Day><Month> June </Month><Year> 1999 </Year></Date><Item name=“Firestone Tire” cost=“2000.00”><Quantity> 50 </Quantity></Item><Item name=“Goodyear Tire” cost=“8000.00”><Quantity> 200 </Quantity></Item><Payment> 40% </Payment><Payment> 60% </Payment></PurchaseOrder>Nested structureSelf-describing tagsNested setsXML Document<PurchaseOrder id=“200I” customer=“Cars R Us”><Date><Day> 10 </Day><Month> June </Month><Year> 1999 </Year></Date><Item name=“Firestone Tire” cost=“2000.00”><Quantity> 50 </Quantity></Item><Item name=“Goodyear Tire” cost=“8000.00”><Quantity> 200 </Quantity></Item><Payment> 40% </Payment><Payment> 60% </Payment></PurchaseOrder>Nested structureSelf-describing tagsNested setsOrderXML Schema<PurchaseOrder id={integer} customer={string}>Date (Item)* (Payment)*</PurchaseOrder>PurchaseOrderDate <Date>Day? Month Year</Date>Day<Day> {integer} </Day>Month<Month> {string} </Month>Year<Year> {integer} </Year>Item<Item name={string} cost={float}>Quantity</Item>… and so onXML Schema (contd.)<PurchaseOrder id={integer} customer={string}>Date? (Item | Payment)*</PurchaseOrder>PurchaseOrder<PurchaseOrder id={integer} customer={string}>(Date | Payment*) (Item (Item Item)* Payment)*</PurchaseOrder>PurchaseOrder<PurchaseOrder id={integer} customer={string}>Date Item (PurchaseOrder)* Payment</PurchaseOrder>PurchaseOrderXML QueryFind all the items bought by “Cars R Us” in 1999For $po in /PurchaseOrderWhere $po/@customer = “Cars R Us” and $po/date/year = 1999Return $po/Item4XML Query (contd.)//Item/(Item/(Item/Payment)*/(Payment | Item))*/Date//Item[5]//Item Before //PaymentOutline• Motivation & High-level Solution• Background (Relations, XML)• Storing and Querying XML Documents• Publishing Relational Data as XML Documents• ConclusionStoring and Querying XML Documents[Shanmugasundaram et. al., VLDB’99]Relational Database SystemXML Translation LayerXMLSchemaRelationalSchemaTranslationInformationXMLDocumentsTuplesXMLQuerySQLQueryRelationalResultXMLResultOutline• Motivation & High-level Solution• Background (Relations, XML)• Storing and Querying XML Documents– Relational Schema Design and XML Storage– Query Mapping and Result Construction• Publishing Relational Data as XML Documents• ConclusionXML Schema<PurchaseOrder id={integer} customer={string}>(Date | (Payment)*) (Item (Item Item)* Payment)*</PurchaseOrder>PurchaseOrderDesired Properties of Generated Relational Schema • All XML documents conforming to XML schema should be “mappable” to tuples in • All queries over XML documents should be “mappable” to SQL queries over • Not Required: Ability to re-generate XML schema from 5Simplifying XML Schemas• XML schemas can be “simplified” for translation purposes• All without undermining storage and query functionality!<PurchaseOrder id={integer} customer={string}>Date? (Item)* (Payment)*</PurchaseOrder>PurchaseOrder<PurchaseOrder