Relating schema's and SQL - DDL

XML schema's and SQL - DDL are related: They both describe data models and thus integrity constraints. We consider a simple invoice example:

Figure 758. Invoice integrity constraints Slide presentation Create comment in forum

A relational implementation looks:

Figure 759. Relational implementation Slide presentation Create comment in forum

exercise No. 7

An XML schema representing invoices Create comment in forum

Q:

Represent the relational schema being described in Figure 759, “Relational implementation ” by an XML Schema and provide an appropriate example instance. Try to resemble the relational schema as closely as possible. A possible road map:

  1. Collect all relational constraints and write them down informally.

  2. Start from a (just) well-formed XML document which allows for entering data corresponding to the relational model.

  3. Add an XSD schema file providing all constraints from your relational model.

  4. Provide appropriate tests by XML document instances violating different aspects of the given schema (data types, uniqueness,...).

A:

A possible schema implementation reads:

<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:vc="http://www.w3.org/2007/XMLSchema-versioning"
    elementFormDefault="qualified"
    vc:minVersion="1.0" vc:maxVersion="1.1">

    <xs:simpleType name="money">                                          <!-- Defining basic types -->
        <xs:restriction base="xs:decimal">
            <xs:fractionDigits value="2"/>
            <xs:totalDigits value="10"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="invoiceStatus">
        <xs:restriction base="xs:token">
            <xs:enumeration value="open"/>
            <xs:enumeration value="due"/>
            <xs:enumeration value="cleared"/>
        </xs:restriction>
    </xs:simpleType>

    <xs:element name="data">                                              <!-- Element content model definitions -->
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="customer" maxOccurs="unbounded"/>
                <xs:element ref="invoice" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
        </xs:complexType>

        <xs:key name="customerId">
            <xs:annotation>
                <xs:documentation>Customers' primary key</xs:documentation>
            </xs:annotation>
            <xs:selector xpath="customer"/>
            <xs:field xpath="@id"/>
        </xs:key>

        <xs:keyref refer="customerId" name="customerToInvoice">
            <xs:annotation>
                <xs:documentation>Referencing a customer's primary key</xs:documentation>
            </xs:annotation>
            <xs:selector xpath="invoice"/>
            <xs:field xpath="@customer"/>
        </xs:keyref>
    </xs:element>

    <xs:element name="customer">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="name" type="xs:string"/>
                <xs:element name="phoneNumber" type="xs:string" minOccurs="0"/>
            </xs:sequence>
            <xs:attribute name="id" type="xs:int" use="required"/>
        </xs:complexType>
    </xs:element>

    <xs:element name="invoice">
        <xs:complexType>
            <xs:simpleContent>
                <xs:extension base="money">                               <!-- See  xs:simpleType  definition above -->

                    <xs:attribute name="status" type="invoiceStatus"/>    <!-- See  xs:simpleType  definition above -->
                    <xs:attribute name="customer" type="xs:int" use="required"/>

                </xs:extension>
            </xs:simpleContent>
        </xs:complexType>
    </xs:element>

</xs:schema>

An example data set:

<data xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="invoice.xsd">

    <customer id="5">
        <name>Clarke Jefferson</name>
    </customer>

    <invoice customer="5" status="due">33.12</invoice>
</data>

Some constraint violating tests among with corresponding XML parser error messages:

Attribute 'id' must appear on element 'customer'.
<data ... ="../invoice.xsd">
    <customer >
        <name>Clarke Jefferson</name>
    </customer>
</data>
The value 'five' of attribute 'id' on element 'customer' is not valid with respect to its type, 'int'
    <customer id="five">
        <name>Clarke Jefferson</name>
    </customer>
'33,12' is not a valid value for 'decimal'
    <customer id="5">
  ...
    <invoice customer="5" status="due">33,12</invoice>
Duplicate key value [5] declared for identity constraint "customerId" of element "data"
    <customer id="5">
        <name>Clarke Jefferson</name>
    </customer>

    <customer id="5">
        <name>Jane Austen</name>
    </customer>
Key 'customerToInvoice' with value '6' not found for identity constraint of element 'data'
    <customer id="5">
        <name>Clarke Jefferson</name>
    </customer>

    <invoice customer="6" status="due">33.12</invoice>