Outline

• Semi-Structured Data
• XML, DTD's
• In-Class Exercise
• XML in Databases

Semi-Structured Data

• As our last topic this semester we turn to a different kind of data model called the semi-structured data model.
• In the semi-structured model, unlike the models we have considered so far, the schema is implied by the data, rather than being declared separately.
• The semi-structured data model plays a special role in database systems:
  1. It serves as a model suitable for integration of databases. That is, it can be used for describing data contained in two or more databases that contain similar data with different schema.
     • Recall on Monday, we said in addition to issues where we are trying to scale our database, integrating databases that occur because of things like mergers is another reason why distributed databases may occur.
     • Semi-structured data provides a tool for managing such kinds of distributed databases.
  2. It serves as the underlying model for notations such as XML, JSON, etc that are being used to share information on the web.
• We now introduce the basic ideas behind semi-structured data, look at XML, then look at how XML is handle in Postgres.

Motivation of Semi-Structured Data

• The models we've considered so far - E/R, UML, relational, ODL - each start with a schema, a rigid framework into which data is placed
• Especially, in the case of the relational model, this rigidity means that DBMS designers can come up with efficient implementations of the DDL, DML, and query operations.
• Semi-structured data is motivated on the other hand by its flexibility -- the data is self-describing in that it often carries information about what its schema is, and that schema can vary arbitrarily over time and within a single database.
• An early example of a semi-structured data model was Lotus Notes (1989) (see Lotus Notes Library of Congress Digital Format Preservation) which organized data into databases (.nsf files) of Notes. A given Note may be built out of one of many types of items such as email messages, calendar entries, etc. The header of the item specifies what kind of item it is.
• The flexibility of not having a fixed schema means that is harder to query, but it makes it easier to do things link add a new attribute or relationship.

Semi-structured Data Representation

• A database of semi-structured data is a collection of nodes.
• Each node is either a leaf or interior.
• Leaf nodes have associated data; the type of this data can be any atomic type such as numbers or strings.
• Interior nodes have one or more arcs out. Each arc has a label which says how the node of the arc relates to the node at the tail.
• One node called the root node has no arcs entring it and represents the entire database. Every node must be reachable from this node.
• The image above shows an example semi-structured database for stars and movies. Notice it is neither a tree nor a directed acyclic graph (DAG).
• One can think of it as representing three main objects (the immediate child nodes of the the Root node), one for cf (Carrie Fisher), one for mf (Mark Hamill) and one for the movie sw (Star Wars).
• Notice also the labels are used to either indicate: (a) kind of attribute or to indicate (b) kind of relationship.

Information Integration Via Semi-structured Data

• As we said, the flexibility and self describing nature of semi-structured data have made it useful as an exchange format either in applications like the web or to allow communication between legacy database systems.
• The legacy database system problems arises when an organization has multiple databases that they have acquired over time that store data slightly differently.
• For example, one company might acquire another company and their respective databases are large and not necessarily easy to migrate. They might each have an employee like table but store things like names, etc slightly differently. They might treat consultants differently, etc.
• One might have separate databases for things to prevent insider trading for say the Sarbanes-Oxley Act, yet which still must communicate to some of their information.
• The above diagram illustrates one possible solution to these kind of scenarios. Semi-structured data can be constructed from the data at the source database s using components called wrappers/adapters. Then one has an interface where the user can query the semi-structured data from all sources. This query is then translated to queries to the individual sources, return to the interface as semi-structured data for combination, and then presented to the user.

XML

• XML (Extensible Markup Language) (W3C 1998) is a tag-based notation designed originally as a more flexible format/semantically rich format for marking up documents rather than use HTML.
• An XML document is a text document that contains tag elements.
• A tag element consists of an open tag which has the format <the-name-of-the-tag a-possible-list-attributes >, the tag contents which consist of text or other XML data, and a close tag which has the format </the-name-of-the-tag>
• Such an element which does not have any tag content can be short closed using the syntax: <the-name-of-the-tag />. For example, the break tag in XHTML (an example, XML language), can be short closed as <br />
• Two common modes for XML data are:
  1. Well-formed XML which allows you to invent your own tags on the fly without any pre-defined schema. Each document is allowed to use whatever tags the author of the document chooses. Nesting rules for tags must still be obeyed, however. I.e., a tag sequence <b><i>some text</i></b> is well formed, but <b><i>some text</b></i> is not.
  2. Valid XML which involves a document type definition (DTD) or schema which specifies whichh tags are allowed for a given document and gives the grammar for how they are allowed to be nested. I.e., this specifies an XML language such as XHTML, RSS, SMIL, etc. We now look at this approach.

The Syntax of XML

• A new XML language can be specified in one of several ways (give a DTD - Document Type Definition, using XML Schemas, using Relax-NG, etc.).
• We will look at the simplest way, which is with a DTD.
• Other ways, allow for more precise control than DTDs would what can be in tags and attributes.
• To create a new language with a DTD one specifies:
  1. What tag elements exist in the language
  2. What sub-elements or data a given element is allowed to contain
  3. What attributes an element has and what their values can be.
  4. What abbreviation entities are allowed in the language.
• Tags are case-sensitive in XML, and every tag must have a close tag, although the abbreviation <element /> works as an implicit close tag.

XML Document Structure and Entities

• Let's look at XML document:

  <patient type="out of state">
       <![CDATA[This is data that will not be parsed by the XML parser even if it has tags in it like this: <tag>]]>
  <name><first>John</first><last>Smith</last></name>
  <insurerID>&kaiser;</insurerID>
  </patient> 
  

• Two files are usually associated with such a document:
  1. one specifies what tags will be used with this document
  2. another specifies how those tags should be styled.
• One can abbreviate parts of a document using entities. For example, &kaiser; above might abbreviate some string of numbers.
• Associated with any XML document is a document entity. This can be thought of as the root of the tree associated with the tags of the document. This entity thus can represent the whole document or the parts of it which do not change.
• Entities can also be used to reference binary data such as images.

Document Type Definitions

• A DTD is used to specify:
  1. what tags will be used in a document
  2. what kind of data or subtags these tags can hold
  3. what attributes the tags can have and what values for these attributes are legal
  4. what entities can occur in the document
• DTDs can be internal (occur in the XML document itself) or external (occur in some separate file).
• A DTD consists of a sequence of declarations enclosed in the block of a DOCTYPE markup declaration. Items 1-4 above correspond to the declaration of type <!ELEMENT ...> (1 and 2), <!ATTLIST ...>, and <!ENTITY ...>
• There is also a tag called <!NOTATION ...>which is sometimes used.

<!ELEMENT ..>

• Basic syntax of the tag is:

  <!ELEMENT element_name 
     (list of names of child elements) SYSTEM Location NDATA NotationName >
  

• Some examples:

  <!ELEMENT memo (from, to, date) > 
  ---- SYSTEM and NDATA don't have to used
  
  <!ELEMENT dept_script SYSTEM "dept.php" NDATA "php" >
  <!NOTATION php SYSTEM "/usr/bin/php" >
  
  <!ELEMENT person(parent+, age, spouse?, sibling*) > 
  ---- + is one or more, ? is optional, and * is 0 or more
  
  <!ELEMENT element_name (#PCDATA) > 
  ---- pcdata = parsable character data can also use EMPTY 
  for no sub-tags or character data or ANY if 
  you want to allow everything
  

<!ATTLIST ..>

• The general way to specify a list of attributes is:

  <!ATTLIST element_name 
  attribute_name_1 attribute_type [default_value] ... attribute_name_n attribute_type [default_value]  >
  

• For example,

  <!ATTLIST airplane places CDATA "4">
  <!ATTLIST airplane engine_type CDATA #REQUIRED> ---- must have the field
  <!ATTLIST airplane price CDATA #IMPLIED> ---- no default value is given
  <!ATTLIST airplane manufacturer CDATA #FIXED "cessna"> 
     --- all instances must have the same value
  

• We can define multiple attributes in the same ATTLIST:

  <!ATTLIST img src CDATA #REQUIRED
                   width  CDATA #REQUIRED
                   height CDATA #REQUIRED
                   alt    CDATA #IMPLIED
  >
  

• We can also list possible values rather than leave it as general as just CDATA:

  <!ATTLIST horizontal_rule alignment (left|center|right) #IMPLIED >
  

<!ENTITY ..>

• Entities come in two flavors:
  • General entities that can be referenced anywhere in an XML document
  • Parameter entities which can be referenced only in markup declarations
• The general form of an entity declaration is:

  <!ENTITY [%] entity_name "entity value" >
  

  % is used when it is a parameter entity
• For example,

  <!ENTITY cp "Chris Pollett" >
  <!ENTITY cool_pic SYSTEM "/usr/local/cool_pic.jpg">
  

Example DTD

<?xml version = "1.0" encoding ="utf-8" ?>
<!-- planes_for_sale.dtd  -->
<!ELEMENT planes_for_sale (ad+) >
<!ELEMENT ad (year, make, model, color, price?, seller) >
<!ELEMENT year (#PCDATA)>
<!ELEMENT make (#PCDATA)>
<!ELEMENT model (#PCDATA)>
<!ELEMENT color (#PCDATA)>
<!ELEMENT price (#PCDATA)>
<!ELEMENT seller (#PCDATA)>
<!ATTLIST seller license CDATA #REQUIRED >
<!ENTITY c "cessna" >

In-Class Exercise

• Design a simple XML language for Cars that makes use of ELEMENT, ATTLISTS, and ENTITY's.
• Post your solutions to the Dec 4 In-Class Exercise Thread.

Postgres

• We will next consider DBMS systems in which XML data can be stored natively.
• Several commercial systems support this such as both Oracle and DB2.
• We will look at Postgres (officially Postgresql), an open source DBMS with a long and distinguished history.
• Postgres can be downloaded from the Postgresql Web Site.
• It can also be installed using choco/apt/brew package managers.
• It actually comes installed as a default on a Mac, although the default installed version is a little hard to work with.
• Postgres evolved out of the earlier relational database Ingres developed at Michael Stonebraker's group at Berkeley 1986-1989.

XML in Postgres

• The command for the Postgres shell is psql. For example, assuming there was no password, to connect to the cpollett database running on localhost on the default port (5421) as the default user (in my case cpollett). One could type the command:

  psql -d cpollett
  

• Postgres supports SQL commands as one expects. It also supports creating tables with XML or JSON column types. For example, the following would create a tables that had a single XML column named bar:

  CREATE TABLE TEST (BAR XML);
  

• Postgres uses the command XMLPARSE to parse string data into its internal representation for XML. For example, we could add a row to the table above using the command:

  INSERT INTO TEST VALUES (XMLPARSE (CONTENT '<la><foo>bar</foo><bar>foo</bar></la>'));
  

• To query XML data, we can use XPath. That is, we use a / delimited sequence of nodes that give a path from the root node in an XML document to a particular collection of nodes. These nodes are then returned as the result of the query. For example, if we want to get the subtrees below any path down through an XML document ending in a foo tag in our table TEST we could write the query:

  SELECT XPATH('//foo', bar) FROM TEST;