The first step was to design a database to use XML features as much as possible. We implemented a system to model book sales and reports. Listing 20.1 contains the core relational schema we used.
Relation Customer (Customer_no, Customer_name, Sex, Postal_code, Telephone, Email) Relation Customer_address (Customer_no, Address_type, Address, City, Sate, Country, Is_default) Relation Invoice (Invoice_no, Customer_no, Quantity, Invoice_amount, Invoice_date, Shipment_type, Shipment_date) Relation Invoice_item (Invoice_no, Item_no, quantity, Unit_price, Invoice_price, Discount) Relation Item (Item_no, Item_name, Catalog_type, Author, Publisher, Item_price) Relation Category (Catalog_type, Catelog_description) Relation Shipment (Shipment_type, Shipment_description) Relation Monthly_sales (Year, Month, Quantity, Total) Relation Customer_sales (Year, Month, Customer_no, Quantity, Total) Relation Item_sales (Year, Month, Item_no, Quantity, Total)
Figure 20.3 shows the EER diagram of the relational schema.
The next step was to translate the relational schema into an XML schema. We adopted the methodology used by K. Williams et al. (Williams et al. 2001). Listing 20.2 contains the final DTD produced from this process.
<!ELEMENT Sales (Invoice*, Customer*, Item*, Monthly_sales*)> <!ATTLIST Sales Status (New|Updated|History) #required> <!ELEMENT Invoice (Invoice_item*)> <!ATTLIST Invoice Quantity CDATA #REQUIRED Invoice_amount CDATA #REQUIRED Invoice_date CDATA #REQUIRED Shipment_type (Post|DHL|UPS|FedEx|Ship) #IMPLIED Shipment_date CDATA #IMPLIED Customer_idref IDREF #REQUIRED> <!ELEMENT Customer (Customer_address*)> <!ATTLIST Customer Customer_id ID #REQUIRED Customer_name CDATA #REQUIRED Sex CDATA #IMPLIED Postal_code CDATA #IMPLIED Telephone CDATA #IMPLIED Email CDATA #IMPLIED> <!ELEMENT Customer_address EMPTY> <!ATTLIST Customer_address Address_type (Home|Office) #REQUIRED Address NMTOKENS #REQUIRED City CDATA #IMPLIED State CDATA #IMPLIED Country CDATA #IMPLIED Is_default (Y|N) "Y"> <!ELEMENT Invoice_Item EMPTY> <!ATTLIST Invoice_Item Quantity CDATA #REQUIRED Unit_price CDATA #REQUIRED Invoice_price CDATA #REQUIRED Discount CDATA #REQUIRED Item_idref IDREF #REQUIRED> <!ELEMENT Item EMPTY> <!ATTLIST Item Item_id ID #REQUIRED Item_name CDATA #REQUIRED Category_type (Art|Comp|Fict|Food|Sci|Sport|Trav) #REQUIRED Author CDATA #IMPLIED Publisher CDATA #IMPLIED Item_price CDATA #REQUIRED> <!ELEMENT Monthly_sales (Item_sales*, Customer_sales*)> <!ATTLIST Monthly_sales Year CDATA #REQUIRED Month CDATA #REQUIRED Quantity CDATA #REQUIRED Total CDATA #REQUIRED> <!ELEMENT Item_sales EMPTY> <!ATTLIST Item_sales Quantity CDATA #REQUIRED Total CDATA #REQUIRED Item_idref IDREF #REQUIRED> <!ELEMENT Customer_sales EMPTY> <!ATTLIST Customer_sales Quantity CDATA #REQUIRED Total CDATA #REQUIRED Customer_idref IDREF #REQUIRED>
The root element, <Sales>, is related to the meaning of the document. The corresponding content elements that follow are <Invoice>, <Customer>, <Item>, and <Monthly_sales>. Every element has its attributes, which can be seen from the DTD in Listing 20.2.
Top |