Replicate Relational and XML Databases for Internet Computing

1
Replicate Relational and XML Databases for
Internet Computing
By Joseph Fong Department of Computer
Science City University of Hong
Kong Reference Joseph Fong and H K Wong,
XTOPO An XML-based topology for information
highway on the Internet, in Press, Journal of
Database Management, Volume 15, Number 3, pp.
18-44, 2004.
2
Outline
Introduction
Architecture Overview
Methodology
Case Study
Conclusion
3
Introduction

• Internet applications today are facing with the
  problem
• replicating,
• transforming,
• exporting, or
• saving their data from one format to another
• The process could be laborious, tedious and error
  tendencies.
• The demand on database is increased in
  e-commerce. Not only relational database (RDB) is
  needed for traditional data processing, but also
  its equivalent XML documents (database) are
  needed for B2B applications.
• Therefore, performance for online conversion from
  relational data to XML document is an issue.

4
Introduction

• The Internet holds within it the potential for
  integrating all information into a global
  network, promising access to information any time
  and anywhere.
• However, this potential has yet to be realised.
• At present, XML has emerged to address this
  problem and is gradually accepted as the standard
  for data interchange in the Internet world.
• This paper aims to create a replicate XML
  database for a companys RDB to improve database
  performance, and to automate XML database
  recovery in case of system failures.

5
Introduction

• For any successful update to the RDB,
  corresponding update will be applied to its
  replicate XML database.
• The result is an incrementally maintained XML
  database for efficient and effective computing
  for e-commerce.
• In summary, Internet computing performance can be
  improved because a replicate XML database and its
  counterpart RDB can be parallel processing for
  both internal data and external data transmission
  on the Internet.
• Furthermore, an XML database can be recovered by
  its counterpart RDB once it is down.

6
Outline
Introduction
Architecture Overview
Methodology
Case Study
Conclusion
7
Architecture Overview

• To make relational tables compatible with the XML
  document, we suggest a scheme to translate RDB
  into an XML document according to their topology
  mapping.
• The scheme is capable to preserve the original
  RDB constraints.
• The benefit is that we can make XML documents
  compatible with RDB and vice versa.

8
Architecture Overview
9
Architecture Overview

• After converting production RDB into an XML
  database, we update these two databases
  asynchronously by translating and processing RDB
  transactions into XML database transactions.
• Once translated, the update transactions are
  processed asynchronously in the order of SQL and
  JDOM.
• Figure 2 is an architecture of update transaction
  translation from SQL to JDOM and their
  asynchronous processing of SQL followed by JDOM
  for each update transaction
• As a pre-process, we extract a view of RDB for
  data transmission on the web. The view is
  converted / replicated into an XML document that
  is stored in a replicate XML database.

10
Outline
Introduction
Architecture Overview
Methodology
Case Study
Conclusion
11
Methodology

• Step 1 Mapping relations ? XML documents
• Algorithm
• begin
• map relational schema into a Classification
  Table (CT)
• recover data dependency of relations from the CT
  into an EER model
• for each relation of RDB
• case recover data dependency into elements
• (1) functional dependency
• (2) multi-valued dependency
• (3) join dependency
• (4) mn cardinality
• case end
• next // for loop
• end

12
Methodology

• Case (1)
• Transform recovered
• functional dependency into a
• single sub-element
• topological XML document

13
Methodology

• Case (2)
• Transform recovered multi-
• valued dependency into a
• multiple sub-elements
• topological XML document

14
Methodology

• Case (3)
• Transform recovered join
• dependency into a group
• topological XML document

15
Methodology

• Case (4)
• Transform recovered mn
• cardinality into a referral
• topology XML document

16
Methodology

• Step 2 Integrating XML documents using JDOM /
  DOM
• Algorithm
• begin
• create JDOM tree for each XML document
• for each JDOM instance
• search for the same JDOM instance in
• another JDOM
• If found delete a duplicate JDOM instance
• chain the JDOM instances
• next // for loop
• map the integrated JDOM into an XML
  document
• end

17
Methodology

• Step 3 Mapping XML documents ? Relations
• Algorithm
• begin
• Load XML document into DOM
• While not at end of XML document do
• begin
• Get Root instance
• While not at end of Root instance do
• begin
• case recover XML topology into relations
• (1) Single sub-element
• (2) Multiple sub-element
• (3) Group sub-element
• (4) Referring element
• end
• end
• end

18
Methodology

• Case (1)
• Transform topological single sub-element into
  parent and child relations

19
Methodology

• Case (2)
• Transform topological multiple sub-elements into
  parent and multiple children relations

20
Methodology

• Case (3)
• Transform topological group elements into parent
  and group children relations

21
Methodology

• Case (4)
• Transform topological referral elements into
  relationship relations

22
Methodology

• Step 4 Asynchronous Update transactions
  translation and processing of SQL and JDOM / DOM
• INSERT Attribute values are specified for a
  sub-element instance to be inserted in an element
  Ek. We denote by v1, v2,..vn the values for
  attributes corresponding to fields in Rk and with
  V1, V2,..Vn of the values of the foreign keys in
  Rk and N1Nn for non-key values

23
Methodology
24
Methodology
The syntax of insert algorithm is
25
Methodology

• UPDATE Suppose we must replace the value(A) of
  an attribute A in the relation R by an element Ek
  with the value V in the translated XML document
  (database) X.
• Basically, we consider two cases. In the first
  case, attribute A is not a foreign key. It
  corresponds to a data item in the corresponding
  relation R and we need a JDOM command to perform
  the replacement in the XML database.
• In the second case, attribute A is a foreign key.
  Replacing a value in this case involves changing
  the element sub-element relationship rather than
  the attribute value in the translated XML
  document (database) X.

26
Methodology
27
Methodology
The syntax of update algorithm is
28
Methodology

• DELETE A simple delete-only statement in the RDB
  corresponds to the XMLDB delete statement for a
  given XML schema. The delete-sub-element-Ek-only
  statement has the following properties
• Remove sub-element Ek from all elements in which
  it participates as a sub-element
• Do not remove sub-element Ek for each element
  where Ek participates as an element.

29
Methodology
30
Methodology
The syntax of delete algorithm is
31
Outline
Introduction
Architecture Overview
Methodology
Case Study
Conclusion
32
Case Study

• In a Bank Loan application, a loan with an
  identity number belongs to a customer who has a
  customer identity number.
• Customers have mortgage loans secured by loan
  securities. Each loan interests can be accured by
  multiple interest types. Each interest type can
  be assigned to different loans.
• Customers open accounts at different branches
  with a maturity date. Each loan is charged with
  interest of a rate of an interest type. They are
  all described in an extended entity relationship
  model in Figure 3.

33
Case Study
34
Case Study

• case (1) map relational ? Group topological
• We join R(Customer, Security), R(Security,
  Loan, Maturity_Date), R(Loan, Customer) into
  relation R1(Customer, Security, Loan,
  Maturity_Date). Then we transform the relation R1
  into a group of elements in an XML document tree.
• case (2) map relational ? multiple sub-element
  topological
• We join R(Customer, Credit_Card), R(Customer,
  Debit_Acct) into relation R2(Customer,
  Credit_Card, Debit_Acct). Then we transform the
  relation R2 into a group of sub-elements of
  multiple occurrences in an XML document tree.

35
Case Study

• case (3) map relational ? single sub-element
• We join R(Type, Enter_Date, Description) and
  R(Type, Effective_Date, Rate,) into relation
  R3(Type, Effective_Date, Enter_Date, Rate,
  Description). Then we transform the relational R3
  in an XML document tree.
• case (4) map relational ? referral topological
• We use the relation R(Loan_ID, Type) and
  transform it into an ID and IDREF in XML document
  tree.

36
Case Study

• Finally, we integrate the above translated XML
  document trees into an XML document tree in
  Figure 4

37
Case Study
Main Menu
38
Case Study
BEFORE
Source Relations
View
39
Case Study
BEFORE
DOM Integration
DOM Generation
40
Case Study
BEFORE
Data loading RDB ? XMLDB
XML Database (Tamino)
41
AFTER Data loading XMLDB ? RDB
42
Case Study
BEFORE
Insert Record (Tomi)
Insert Record (Enna)
43
Case Study
BEFORE
Delete Record (Enna)
Update Record (Tomi)
44
Case Study
AFTER
Source Relations
View
45
Case Study
AFTER
DOM Generation
DOM Integration
46
Case Study
AFTER
XML Database (Tamino)
47
Outline
Introduction
Architecture Overview
Methodology
Case Study
Conclusion
48
Conclusion

• This paper presents a methodology of replicating
  relational database into an XML database.
• The significance is to improve the performance of
  Internet computing by allowing parallel
  processing for data exchange on the Internet as
  well as data processing relational data.
• Also the reliability of XML database can be
  improved by recovery from its counterpart
  relational database if necessary.