End of SQL XMLNull ValuesNull Value LogicSlide 4Testing for NullOuterjoinsLeft OuterjoinsPowerPoint PresentationOuter JoinsXMLMore Facts About XMLWhat is XML ? From HTML to XMLHTMLSlide 14Slide 15XML ApplicationsWeb ServicesXML SyntaxXML TerminologySlide 20The XML TreeMore XML Syntax: AttributesReplacing Attributes with Elements“Types” (or “Schemas”) for XMLAn Example DTDMore on DTDs: AttributesDTDs as GrammarsMore on DTDs as GrammarsXML for Representing DataXML vs Data ModelsSemi-structured Data ExplainedSemistructured Data ExplainedXML Data v.s. E/R, ODL, RelationalData Sharing with XML: Easy Exporting Relational Data to XMLExport data grouped by companiesThe DTDExport Data by ProductsWhich One Do We Choose ?Storing XML DataEnd of SQLXMLApril 22th, 2002Null Values•If x=Null then 4*(3-x)/7 is still NULL•If x=Null then x=“Joe” is UNKNOWN•Three boolean values:–FALSE = 0–UNKNOWN = 0.5–TRUE = 1Null Value Logic•C1 AND C2 = min(C1, C2)•C1 OR C2 = max(C1, C2)•NOT C1 = 1 – C1SELECT *FROM PersonWHERE (age < 25) AND (height > 6 OR weight > 190)Semantics of SQL: include only tuples that yield TRUENull ValuesUnexpected behavior:SELECT *FROM PersonWHERE age < 25 OR age >= 25Some Persons are not included !Testing for NullCan test for NULL explicitly:–x IS NULL–x IS NOT NULLSELECT *FROM PersonWHERE age < 25 OR age >= 25 OR age IS NULLNow it includes all PersonsOuterjoinsExplicit joins in SQL:Product(name, category) Purchase(prodName, store) SELECT Product.name, Purchase.store FROM Product JOIN Purchase ON Product.name = Purchase.prodNameSame as:SELECT Product.name, Purchase.store FROM Product, Purchase WHERE Product.name = Purchase.prodNameBut Products that never sold will be lost !Left OuterjoinsLeft outer joins in SQL:Product(name, category) Purchase(prodName, store) SELECT Product.name, Purchase.store FROM Product LEFT OUTER JOIN Purchase ON Product.name = Purchase.prodNameName CategoryGizmo gadgetCamera PhotoOneClick PhotoProdName StoreGizmo WizCamera RitzCamera WizName StoreGizmo WizCamera RitzCamera WizOneClick -Product PurchaseOuter Joins•Left outer join:–Include the left tuple even if there’s no match•Right outer join:–Include the right tuple even if there’s no match•Full outer join:–Include the both left and right tuples even if there’s no matchXMLMore Facts About XML•Every database vendor has an XML page:–www.oracle.com/xml–www.microsoft.com/xml–www.ibm.com/xml•Many applications are just fancier Websites•But, most importantly, XML enables data sharing on the Web – hence our interestWhat is XML ?From HTML to XMLHTML describes the presentation: easy for humansHTML<h1> Bibliography </h1><p> <i> Foundations of Databases </i> Abiteboul, Hull, Vianu <br> Addison Wesley, 1995<p> <i> Data on the Web </i> Abiteboul, Buneman, Suciu <br> Morgan Kaufmann, 1999<h1> Bibliography </h1><p> <i> Foundations of Databases </i> Abiteboul, Hull, Vianu <br> Addison Wesley, 1995<p> <i> Data on the Web </i> Abiteboul, Buneman, Suciu <br> Morgan Kaufmann, 1999HTML is hard for applicationsXML<bibliography> <book> <title> Foundations… </title> <author> Abiteboul </author> <author> Hull </author> <author> Vianu </author> <publisher> Addison Wesley </publisher> <year> 1995 </year> </book> …</bibliography><bibliography> <book> <title> Foundations… </title> <author> Abiteboul </author> <author> Hull </author> <author> Vianu </author> <publisher> Addison Wesley </publisher> <year> 1995 </year> </book> …</bibliography>XML describes the content: easy for applicationsXML•eXtensible Markup Language•Roots: comes from SGML–A very nasty language•After the roots: a format for sharing data•Emerging format for data exchange on the Web and between applicationsXML Applications•Sharing data between different components of an application.•Archive data in text files.•EDI: electronic data exchange:–Transactions between banks–Producers and suppliers sharing product data (auctions)–Extranets: building relationships between companies•Scientists sharing data about experiments.Web Services•A new paradigm for creating distributed applications?•Systems communicate via messages, contracts.•Example: order processing system.•MS .NET, J2EE – some of the platforms•XML – a part of the story; the data format.XML Syntax•Very simple:<db> <book> <title>Complete Guide to DB2</title> <author>Chamberlin</author> </book> <book> <title>Transaction Processing</title> <author>Bernstein</author> <author>Newcomer</author> </book> <publisher> <name>Morgan Kaufman</name> <state>CA</state> </publisher></db>XML Terminology•tags: book, title, author, …•start tag: <book>, end tag: </book>•start tags must correspond to end tags, and converselyXML Terminology•an element: everything between tags–example element: <title>Complete Guide to DB2</title>–example element:•elements may be nested•empty element: <red></red> abbreviated <red/>•an XML document has a unique root elementwell formed XML document: if it has matching tags <book> <title> Complete Guide to DB2 </title> <author>Chamberlin</author> </book> <book> <title> Complete Guide to DB2 </title> <author>Chamberlin</author> </book>The XML Treedbbook book publishertitle authortitle authorauthor name state“CompleteGuideto DB2”“Chamberlin” “TransactionProcessing”“Bernstein” “Newcomer”“MorganKaufman”“CA”Tags on nodesData values on leavesMore XML Syntax: Attributes<book price = “55” currency = “USD”> <title> Complete Guide to DB2 </title> <author> Chamberlin </author> <year> 1998 </year></book><book price = “55” currency = “USD”> <title> Complete Guide to DB2 </title> <author> Chamberlin </author> <year> 1998 </year></book>price, currency are called attributesReplacing Attributes with Elements<book> <title> Complete Guide to DB2 </title> <author> Chamberlin </author> <year> 1998 </year> <price> 55 </price> <currency> USD </currency></book><book> <title> Complete Guide to DB2 </title> <author> Chamberlin </author> <year> 1998 </year> <price> 55 </price> <currency> USD </currency></book>attributes
View Full Document
Unlocking...