The airline example revisited

exercise No. 8

Airline meta information by XML schema Create comment in forum

Q:

Transform the relational schema from Airlines, airports and flights into an XML schema and supply some test data. In particular consider the following constraints:

The following XML example instance may guide you towards an airline.xsd schema:

<top xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
    xsi:noNamespaceSchemaLocation="airline.xsd">
    <airlines>
        <airline airlineCode="DLH" id="1">
            <name>Lufthansa</name>
        </airline>
        <airline airlineCode="AFR" id="2">
            <name>Air France</name>
        </airline>
    </airlines>
    <destinations>
        <destination id="1" airportCode="EDDF">
            <fullName>Frankfurt International Airport – Frankfurt am Main</fullName>
        </destination>

        <destination  id="3" airportCode="EBCI">
            <fullName>Brussels South Charleroi Airport – Charleroi</fullName>
        </destination>
    </destinations>

    <flights>
        <flight id="1" airline="2" origin="1" destination="3">
            <flightNumber>LH 4234</flightNumber>
        </flight>
    </flights>
</top>

Hints:

  • Identify all relational schema constraints from solution of Airlines, airports and flights and model them accordingly.

  • The above example does not contain any constraint violations. In order to test your schema for completeness tinkering with primary key, unique and referencing attribute values may be helpful.

A:

<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema"
    xmlns:vc="http://www.w3.org/2007/XMLSchema-versioning" elementFormDefault="qualified"
    vc:minVersion="1.1">

    <xs:simpleType name="ICAOAirportCode">
        <xs:restriction base="xs:string">
            <xs:length value="4" />
            <xs:pattern value="[A-Z09]+"></xs:pattern>
        </xs:restriction>
    </xs:simpleType>

    <xs:simpleType name="ICAOAirlineCode">
        <xs:restriction base="xs:string">
            <xs:length value="3"/>
            <xs:pattern value="[A-Z]+"></xs:pattern>
        </xs:restriction>
    </xs:simpleType>

    <xs:element name="top">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="airlines"/>
                <xs:element ref="destinations"/>
                <xs:element ref="flights"/>
            </xs:sequence>
        </xs:complexType>

        <xs:keyref name="_FK_Flight_airline" refer="_PK_Airline_id">
            <xs:selector xpath="flights/flight"/>
            <xs:field xpath="@airline"/>
        </xs:keyref>

        <xs:keyref name="_FK_Flight_origin" refer="_PK_Destination_id">
            <xs:selector xpath="flights/flight"/>
            <xs:field xpath="@origin"/>
        </xs:keyref>

        <xs:keyref name="_FK_Flight_destination" refer="_PK_Destination_id">
            <xs:selector xpath="flights/flight"/>
            <xs:field xpath="@destination"/>
        </xs:keyref>

    </xs:element>

    <xs:element name="airlines">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="airline" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
        </xs:complexType>

        <xs:key name="_PK_Airline_id">
            <xs:selector xpath="airline"/>
            <xs:field xpath="@id"/>
        </xs:key>

        <xs:key name="_UN_Airline_name">
            <xs:selector xpath="airline"/>
            <xs:field xpath="name"/>
        </xs:key>

        <xs:key name="_UN_Airline_airlineCode">
            <xs:selector xpath="airline"/>
            <xs:field xpath="@airlineCode"/>
        </xs:key>
    </xs:element>

    <xs:element name="airline">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="name" type="xs:string"/>
            </xs:sequence>
            <xs:attribute name="id" type="xs:int" use="required"/>
            <xs:attribute name="airlineCode" type="ICAOAirlineCode" use="required"/>
        </xs:complexType>
    </xs:element>

    <xs:element name="destinations">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="destination" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
        </xs:complexType>

        <xs:key name="_PK_Destination_id">
            <xs:selector xpath="destination"/>
            <xs:field xpath="@id"/>
        </xs:key>

        <xs:key name="_UN_Destination_airportCode">
            <xs:selector xpath="destination"/>
            <xs:field xpath="@airportCode"/>
        </xs:key>
    </xs:element>

    <xs:element name="destination">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="fullName"/>
            </xs:sequence>
            <xs:attribute name="id" type="xs:int"/>
            <xs:attribute name="airportCode" type="ICAOAirportCode"/>
        </xs:complexType>
    </xs:element>

    <xs:element name="flights">
        <xs:complexType>
            <xs:sequence>
                <xs:element ref="flight" minOccurs="0" maxOccurs="unbounded"/>
            </xs:sequence>
        </xs:complexType>

        <xs:key name="_PK_Flight_id">
            <xs:selector xpath="flight"/>
            <xs:field xpath="@id"/>
        </xs:key>

        <xs:key name="_UN_Flight_flightNumber">
            <xs:selector xpath="flight"/>
            <xs:field xpath="flightNumber"/>
        </xs:key>

    </xs:element>

    <xs:element name="flight">
        <xs:complexType>
            <xs:sequence>
                <xs:element name="flightNumber" type="xs:string"/>
            </xs:sequence>
            <xs:attribute name="id" type="xs:int" use="required"/>
            <xs:attribute name="airline" type="xs:int" use="required"/>
            <xs:attribute name="origin" type="xs:int"/>
            <xs:attribute name="destination" type="xs:int"/>
            <xs:assert test="not(@origin = @destination)">
                <xs:annotation>
                    <xs:documentation>CHECK constraint _CK_Flight_origin_destination</xs:documentation>
                </xs:annotation>
            </xs:assert>
        </xs:complexType>
    </xs:element>

</xs:schema>