Transcript Slide 1

Association Relationship
Transformation of XML Schemas to
Object–Relational Databases
Bandung, 11 September 2002
Nathalia Devina Widjaya, David Taniar
Wenny Rahayu, Eric Pardede
Structure of Presentation

Background

Objectives

Association Relationship

Related Work

Proposed Transformation Methodology

Conclusion and Future Work
Background

The popularity of XML is growing and is gradually accepted as the
standard for describing and interchanging data between various
systems and databases on the Internet

XML now offers XML Schema definition language as a substitution
of DTD. It provides richer facilities for defining and constraining the
content of XML documents

With the wide acceptance of the OO conceptual models, more
systems are modeled and expressed with OO notation. This
situation suggests the necessity to integrate the OO conceptual
models and XML.
Objectives

The goal of this work is to present a coherent way to transform the
XML schema into ORDB using Oracle 9i features models
XML Schema

Translator
ORDB (Oracle 9i)
This transformation is important so that all the data that are created
using XML schema can be transformed to the database using
Oracle format and features, without eliminating the object relational
features
Objectives (2)

The emphasis of this paper is on the transformation of association
relationship to help people conveniently generate Oracle database

The work presented in this paper is actually part of a larger
research project on Transformation from XML Schema to Object-
Relational Databases.
(i) transformation of aggregation and inheritance relationship from
XML Schema to ORDB
(ii) transformation of association relationship from XML Schema to
ORDB
Association Relationship

ORDB was introduced to improve RDB performance and has
become popular because it improves the limitations of RDB such as
lack of support for new types and composite data values

Since ORDB has OO features, it encapsulates the static (including
association relationship) as well as dynamic aspects of objects

The association relationship represents a “connection” between
object instances. Basically, it is a reference from one object to
another that provides access paths among objects in a system.

The objects are connected through an association link. The link can
have a specified cardinality: one-to-one, one-to-many, many-tomany
Related Work

Most existing work has focused on methods to map RDB to an XML
database for database interoperability. Some works still used DTD
and some have used XML schema.

The previous works include mapping referential integrity constraints
from RDB to XML, semantic data modeling using XML schemas,
hybrid database schema to map a subset of XML documents onto
database structures, generic-transforming rules from the OOCM to
XML schema, etc.

The transformation of generalization and aggregation relationship
from the XML Schema to ORDB has been done earlier as a first part
of the project
Proposed Transformation
Methodology

There are 3 parts of association relationships that will be
transformed, one-to-one association, one-to-many association and
many-to-many association

The method that we use to implement the association relationship in
Oracle is using object references (REF). Instead of connecting two
tables through the values of the associated primary key and foreign
key, this method allows one to directly connecting two tables through
referencing attribute
One-to-One Relationship
Lecturer

1
1
HAS
has
1
1
Office
The example that is used in this section is the relationship between
lecturer and office

For one-to-one association relationship, it is important to determine
the participation of the objects to decide location of the foreign keys
in relational system
One-to-One Relationship (2)

The Transformation steps:
1.
From the XML Schema, element with partial participation will
be recognized by statement minOccurs =”0” maxOccurs =”1” .
First, create an object for the one with partial participation
(office) with its attributes
…
<complexType>
<sequence>
<element ref=”office” minOccurs=”0” maxOccurs= “1” />
<element ref=”lecture” minOccurs=”0” maxOccurs=“unbounded”/>
</sequence>
…
One-to-One Relationship (3)
…
</element>
<element name = “office”>
<attribute name = “officeid” type = “string” use = “required”/>
</element>…
CREATE OR REPLACE TYPE Office_T AS OBJECT
(office_id
VARCHAR2(10));
One-to-One Relationship (4)
2.
Create another object for the one with total participation,
lecture, with the attributes and a REF that refers to office type
<element name = “lecture”>
…
<attribute name = “lectureid” type = “string” use = “required” />
<attribute name = “lectureName” type = “string” use = “optional” />…
CREATE OR REPLACE TYPE Lecture_T AS OBJECT
(lecture_id
VARCHAR2 (10),
lecture_name
VARCHAR2 (50),
lecture_office
REF office_T)
One-to-One Relationship (5)
3.
Lastly, we create one table for ‘Lecture’ and another one for
‘Office’. Each table has its id as well as the constraints
CREATE TABLE Office OF Office_T
(office_id NOT NULL,
PRIMARY KEY (office_id));
CREATE TABLE Lecture OF Lecture_T
(lecture_id NOT NULL,
PRIMARY KEY (lecture_id));
One-to-Many Relationship
Customer

1
1
HAS
has
*
*
Order
The example that is used in this section is the relationship between
customer and order
One-to-Many Relationship (2)

The Transformation steps:
1.
An element with a complex type in XML Schema, which does
not have reference inside it, is transformed by creating an
object in ORDB with all the attributes
<xs:element name = “customer” type = “customerType” / >
…
<xs:complexType name = “customerType”>
…
</xs:complexType>
One-to-Many Relationship (3)
<xs:complexType name = “customerType”>
<xs:attribute name = “customerName” type=“xs:string”/>
<xs:attribute name = “shippingAddress” type=”xs:string”/>
<xs:attribute name = “shippingCity” type=”xs:string”/>
<xs:attribute name = “shippingState” type=”xs:string”/>
<xs:attribute name = “shippingPostalCode”
type=”xs:integer”/>
</xs:complexType>…
CREATE OR REPLACE TYPE Customer_T AS OBJECT
(customer_id
VARCHAR2 (10),
customer_name
VARCHAR2 (50),
shipping_address
VARCHAR2 (100),
shipping_city
VARCHAR2 (20),
shipping_state
VARCHAR2 (20),
shipping_postalcode
NUMBER)
One-to-Many Relationship (4)
2.
For another element in the complex type that has reference to
another element, create another object and write all the
attributes. The minOccurs = “1” and maxOccurs =
“unbounded” in XML will be shown by using REF in Oracle
…
<xs:sequence>
<xs:element name = “order” type= “ orderType”
minOccurs= “1” max Occurs = “unbounded” / >
…
<xs:complexType name = “orderType”>
…
One-to-Many Relationship (5)
…
<xs:complexType name = “orderType”>
<xs:attribute name =”orderID” type=”xs:integer”/>
<xs:attribute name = “orderDate” type= “xs:date”/>
</xs:complexType>
CREATE OR REPLACE TYPE Order_T AS OBJECT
(order_id
VARCHAR2(10),
order_date
DATE,
order_customer
REF Customer_T)
One-to-Many Relationship (6)
3.
Lastly, create a table for each type. Each table has its id as
well as the constraints
CREATE TABLE Customer OF Customer_T
(customer_id
NOT NULL,
PRIMARY KEY (customer_id));
CREATE TABLE Order OF Order_T
(order_id
NOTNULL,
PRIMARY KEY (order_id));
Many-to-Many Relationship
Student

*
1
ENROLLS_IN
has
*
*
Course
The example that is used is the relationship between student and
course
Many-to-Many Relationship (2)

The Transformation steps:
1.
All elements name that has minOccurs = “1” max Occurs =
“unbounded” need to be created as object in ORDB and write
all its attributes
…
<xs:complexType>
<xs:sequence>
<xs:element name = “students” minOccurs =”1” maxOccurs =“unbounded”>
<xs:complexType>
…
<xs:element name = “course” minOccurs =”1” maxOccurs = “unbounded”>
<xs:complexType>
…
Many-to-Many Relationship (3)
<xs:element name = “student”>…
<xs:attribute name =”refCourseID” type= “xs:string” />…
<xs:attribute name = “studentid” type = “xs:string” />…
…
<xs:element name = “course”>…
<xs:attribute name =”refstudentID” type= “xs:string” />…
<xs:attribute name = “courseid” type = “xs:string” />…
…
CREATE OR REPLACE TYPE Student_T AS Object
(student_id
VARCHAR2 (10),
student_name
VARCHAR2(30))
CREATE OR REPLACE TYPE Course_T AS Object
(course_id
VARCHAR2(10),
course_name
VARCHAR2(30))
Many-to-Many Relationship (4)
2.
In the XML Schema, each element will be linked to another
element by using the attribute name that refers to another
element ID. For those elements create table for each of them
in ORDB and add the constraints for the attributes
…
<xs:attribute name =”refCourseID” type= “xs:string” />…
…
<xs:attribute name =”refstudentID” type= “xs:string” />…
CREATE TABLE Student OF Student_T
(student_id
NOT NULL,
PRIMARY KEY(student_id));
CREATE TABLE Course OF Course_T
(course_id
NOT NULL,
PRIMARY KEY (order_id));
Many-to-Many Relationship (5)
3.
Create another table to keep the relationship between the two
connected tables in ORDB. This table will have reference to
the participating objects
CREATE TABLE Enrolls_in
(student
REF Student_T,
course
REF Course_T);
Conclusion and Future Work

In this paper, we have investigated the transformation from XML
schema to the ORDB by using Oracle 9i, emphasis the
transformation of association relationship

This transformation is important because people tends to eliminate
the object-oriented conceptual features when they transform XML
schema to the database.

Our future work is being planned to investigate more transformation
from XML schema to ORDB for other XML Schema features that has
not been discussed in this paper. Further research should be done
to create a query from XML schema to get the data from the Oracle
9i databases