Reversing XML to Rdbms

exercise No. 35

Q:

Reverse exercise Avoiding intermediate SQL file export to read Rdbms data via JDBC and export corresponding XML data using Jdom. You will need two database tables describing each product among with at least one description. Consider the following schema among with some sample data:

DROP TABLE IF EXISTS Description;
DROP TABLE IF EXISTS Product;

CREATE TABLE Product (
   id INTEGER NOT NULL
  ,name VARCHAR(255) NOT NULL
  ,age SMALLINT
  ,PRIMARY KEY(id)
);

CREATE TABLE Description (
   product INTEGER NOT NULL
  ,orderIndex int NOT NULL   -- preserving the order of descriptions belonging to a given product
  ,text VARCHAR(255) NOT NULL
  ,UNIQUE(product, orderIndex)
  ,FOREIGN KEY(product) REFERENCES Product(id)
);

-- example data corresponding to products.xml --

-- A single product lacking age property --
INSERT INTO Product (id, name) VALUES (1, 'Monkey Picked Tea');

INSERT INTO Description VALUES(1, 0, 'Picked only by specially trained monkeys.');
INSERT INTO Description VALUES(1, 1, 'Rare wild Chinese tea.');

INSERT INTO Product VALUES (2, '4-Person Instant Tent', 15);
INSERT INTO Description VALUES(2, 0, 'Exclusive WeatherTec system.');
INSERT INTO Description VALUES(2, 1, '4-person, 1-compartment tent.');
INSERT INTO Description VALUES(2, 2, 'Pre-attached tent poles.');
  1. Explain the ratio of the UNIQUE(product, orderIndex) constraint in Description.

  2. Write a JDBC application which reads from your RDBMS data and exports a corresponding XML instance:

    <catalog>
      <product id="1">
        <name>Monkey Picked Tea</name>
        <description>Picked only by specially trained monkeys</description>
        <description>Rare wild Chinese tea.</description>
      </product>
      <product id="2">
        <name>4-Person Instant Tent</name>
        <description>Exclusive WeatherTec system.</description>
        <description>4-person, 1-compartment tent.</description>
        <description>Pre-attached tent poles.</description>
        <age>15</age>
      </product>
    </catalog>

    Use PreparedStatement instances throughout your application.

A:

  1. Without UNIQUE(product, orderIndex) the following state would become possible:

    +---------+------------------------------+------------+
    | product | text                         | orderIndex |
    +---------+------------------------------+------------+
    |   ...   |             ...              |    ...     |
    |       2 | Exclusive WeatherTec system. |          1 |
    |       2 | 4-person, 1-compartment tent.|          1 |
    |   ...   |             ...              |    ...     |
    +---------+------------------------------+------------+

    The presence of two distinct descriptions having identical orderIndex does not enforce the required order.

  2. See: