20.4 Database Design

  Previous section   Next section

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.

Listing 20.1 Relational Schema for the Book Sales and Reports System
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,
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.

Figure 20.3. EER Diagram


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.

Listing 20.2 DTD Corresponding to the Relational Schema in Listing 20.1
<!ELEMENT          Sales (Invoice*, Customer*, Item*,
<!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.


Part IV: Applications of XML