Automatic%20Generation%20of%20SQLX%20View%20Definitions%20from%20ORA-SS%20Views

1
Automatic Generation of SQLX View Definitions
from ORA-SS Views

• Yabing Chen, Tok Wang Ling, Mong Li Lee

2
Outline

• Introduction
• Preliminaries
• Generating SQLX Query Definitions From ORA-SS
  Views
• Conclusion

3
Introduction

• XML
• The dominant standard for exchanging data on the
  Internet
• XML Views
• Exploit the potential of XML
• Secure the source data
• Provide an application-specific view

4
Introduction (cont.)

• Main related work
• In all these works, the original data are in RDB
• SilkRoute 1 2
• Two declarative language RXL and XML-QL to define
  and query the views over relational data
• XPERANTO 3 4
• uses a canonical mapping to create a default XML
  view from relational data
• Oracle 7, IBM DB2 8 SQL Server 6
• provide the ability to export relational data to
  materialized XML views

5
Introduction (cont.)

• Disadvantage of the main related work
• Ignore semantic information in source data
• For example, ignore the difference between object
  class, attribute and relationship in schema
• Cannot check the validity of designed views
• Difficult to use query languages to define views
• Proprietary language or XQuery

6
Introduction (cont.)

• Our approach for XML views
• The original data are in XML and then stored in
  ORDB
• Design valid XML Views 9
• Based on a semantically rich model - ORA-SS
• Use query operators, such as selection, drop,
  swap, join, etc.
• Support more flexible views than related work,
  such as swapping views
• Transform the designed views into SQLX queries on
  ORDB (this paper)

7
Preliminaries

• ORA-SS data model
• Three concepts Object class, attribute and
  relationship type
• An object-relational storage for XML
• Store each object class with all its attributes
  into a nested relation
• Store each relationship type with all its
  attributes into a nested relation
• SQLX syntax
• An emerging part of the ANSI and ISO SQL standard
• XMLELEMENT
• Generate an XML element
• XMLAGG
• Produce a forest of XML elements from a
  collection of individual elements

8
Preliminaries Example
Query List all employees
Select xmlelement(employee_list,
xmlagg( xmlelement(employee
, xmlattributes(e.eno,
e.ename) )
) ) From employee e
An ORA-SS source schema
Result
ltemployee_listgt ltemployee enoe01
enamedavid/gt ltemployee enoe02
enamepaul/gt lt/employee_listgt
Object relations project (jno,
jname) employee (eno, ename)
Relationship relations pe (jno, eno,
progress)
Object-relational storage schema
9
Generate SQLX queries from ORA-SS views

• Main idea
• An object in the view is determined by some
  particular objects in the view through the
  relationship types involved.

• publication is determined by member through
  relationship type mp
• publication has nothing to do with project
• member is called the Determined Object Class
  (DOC) of publication
• mp is called the Determined Relationship Type
  (DRT) of publication

10
Generate SQLX queries from ORA-SS views

• Main idea (cont.)
• Identify relationship types in the views
• Original relationship in the source schema
• Derived relationship by projecting existing
  relation in the ORDB
• Derived relationship by joining existing
  relations in the ORDB

Object relations supplier (sno,
sname) part (pno, pname)
factory (fno, fname) project (jno,
jname) employee (eno, ename, (email))
Relationship relations ps
(pno, sno, price) sf (sno, fno)
spj (sno, pno, jno,
qty) je (jno, eno, progress)
Drop supplier
Source schema
View schema
11
General rules for generating SQLX queries from
ORA-SS views

• Rule Gen 1 for object class along with its
  attributes
• Case 1 DRT is original relationship
• Case 2 DRT is derived relationship by projecting
  existing relation
• Case 3 DRT is derived relationship by joining
  existing relations
• Rule Gen 2 for attributes
• Case 1 original relationship attributes
• Case 2 derived relationship attributes
• Case 3 multi-valued attributes

12
Rule Gen 1 (for object class in the view)

• Case 1 If the DRT is an original relationship
  type from the source schema, then
• Directly use the original relationship type to
  generate the where conditions in the where
  clause.

e.g. je
Select xmlagg( xmlelement(employee,
xmlattributes(e.eno,
e.ename))) From employee e, je Where e.eno
je.jno and je.jno j.jno
employee (eno, ename, (email)) je (jno, eno,
progress)
SQLX expression for employee
13
Rule Gen 1(cont.)

• Case 2 if the DRT is a derived relationship type
  by projecting existing relation in the ORDB, then
• Replace the derived relationship type with its
  corresponding original relationship type in the
  where clause

Select xmlagg( xmlelement(project,
xmlattributes(j.jno, j.jname))) From
project j, pj Where pj.jno j.jno and pj.pno
p.pno
e.g. pj
pj spj p, j
Select xmlagg( xmlelement(project,
xmlattributes(j.jno, j.jname))) From
project j, spj Where spj.jno j.jno and spj.pno
p.pno
spj (sno, pno, jno, qty) project (jno, jname)
14
Rule Gen 1(cont.)

• Case 3 If the DRT is a derived relationship type
  by joining existing relations in the ORDB, then
• Rewrite the where condition with the DRTS
  corresponding original relations in the where
  clause

Select xmlagg( xmlelement(factory,
xmlattributes(f.fno, f.fname) ) From
factory f, pf Where f.fno pf.fno and pf.pno
p.pno
e.g. pf
Replace pf by ps and sf in ORDB
Select xmlagg( xmlelement(factory,
xmlattributes(f.fno, f.fname) ) From
factory f, ps, sf Where f.fnosf.fno and
sf.snops.sno and ps.pnop.pno
factory (fno, fname) ps (pno, sno, price)
sf (sno, fno)
15
Rule Gen 2 (For relationship attributes)

• Case 1 If a single valued attribute A belongs to
  R and R is an original relationship type from
  source schema, then
• generate an xmlelement function for the attribute
  A
• xmlelement(A, R.A)

e.g. progress
Select xmlagg( xmlelement(employee,
xmlattributes(e.eno, e.ename),
xmlelement(progress,
je.progress))) From employee e, je Where e.eno
je.jno and je.jno j.jno
SQLX expression for employee
View schema
16
Rule Gen 2 (cont.)

• Case 2 If an attribute A belongs to R and R is a
  derived relationship type by projecting an
  original relation R in the ORDB, then
• Generate an xmlelement function for the attribute
  A with agg function
• xmlelement(A, agg(R.A))
• Append a group by clause for the agg function
  (e.g. total_qty)

(e.g. total_qty)
Select xmlagg( xmlelement(project,
xmlattributes(j.jno, j.jname),
xmlelement(total_qty,
sum(spj.qty)))) From project j, spj Where spj.jno
j.jno and spj.pno p.pno Group by j.jno,
j.jname
SQLX expression for project
View schema
17
Rule Gen 2 (cont.)

• Case 3 If an attribute A is a multi-valued
  attribute in the ORDB, then
• Generate an sub query to extract the value of A

(e.g. email)
Select xmlagg( xmlelement(employee,
xmlattributes(e.eno, e.ename)
(select xmlagg(xmlelement(email,

email)) from
table(e.email) )) From employee
e, je Where e.eno je.jno and je.jno j.jno
View schema
SQLX expression for employee
18
The SQLX query for the whole view example
Select //generate root
part xmlelement(root, xmlagg(
xmlelement(part, xmlattributes(p.pno,
p.pname), (Select // generate
factory xmlagg(
xmlelement(factory,
xmlattributes(f.fno, f.fname) ) ) From
factory f, ps, sf Where f.fno sf.fno
and sf.sno ps.sno and
ps.pno p.pno ), (Select
// generate project xmlagg(
xmlelement(project,
xmlattributes(j.jno, j.jname),
xmlelement(total_qty, sum(spj.qty)),
(Select xmlagg( // generate employee
xmlelement(employee,
xmlattributes(e.eno,
e.ename),
(select xmlagg(xmlelement(emai
l,
email)) from
table(e.email) )
xmlelement(progress, je.progress)
) ) From
employee e, je Where e.eno je.jno
and je.jno j.jno ) )
) From project j, spj Where
j.jno spj.jno and spj.pno p.pno Group
by j.jno, j.jname ) ) ) From part p
19
Algorithm

• Use a deep-first search algorithm to generate the
  SQLX query for the ORA-SS view
• Generate the SQLX query based on the view schema
  (with derived relationship types, etc)
• Replace each derived relationship type by using
  the corresponding relations in the ORDB
• For swapping and selection relationship types,
• They refers to the same relations in the ORDB
• For selection operator, append the selection
  condition in the SQLX query

20
Conclusion

• Main contribution
• An approach to automatically generate SQLX query
  definitions of ORA-SS views
• Remove the need for users to manually write
  complex SQLX view definitions
• Main difference between this paper and ER03
  paper 10
• The storage of XML data
• In ER03 Text file
• In this paper ORDB
• Semantics of ORA-SS views (relationship in the
  views)
• In ER03 Do not utilize the semantics
• In this paper Utilize the semantics (e.g. DRT
  DOC)

21
References

• 1M. Fernandez, W. Tan, D. Suciu, Efficient
  Evaluation of XML Middleware Queries, ACM
  SIGMOD, pp. 103-114, 2001.
• 2 M. Fernandez, W. Tan, D. Suciu, SilkRoute
  Trading Between Relations and XML, World Wide
  Web Conference, 1999.
• 3 M. Carey, J. Kiernan, J. hanmugasundaram, et.
  al., XPERANTO A Middleware for Publishing
  Object-Relational Data as XML Documents, VLDB,
  pp. 646-648, 2000.
• 4 M. Carey, D. Florescu, Z. Ives, et. al.,
  XPERANTO Publishing Object-Relational Data as
  XML, WebDB Workshop, 2000.
• 6 Microsoft Corp. http//www.microsoft.com/XML.
• 7 Oracle Corp. http//www.oracle.com/XML.
• 8 IBM Corp. http//www.ibm.com/XML.
• 9 Y.B. Chen, T.W. Ling, M.L. Lee, Designing
  Valid XML Views, ER Conference, 2002
• 10 Y.B.Chen, T.W.Ling, M.L.Lee, Automatic
  Generation of XQuery Definitions from ORA-SS
  Views, ER Conference 2003.
• 11 Y.Y.Mo, T.W.Ling, Storing and Maintaining
  Semistructured Data Efficiently in an
  Object-Relational Database, WISE Conference,
  2002.
• 12 SQLX. http//www.sqlx.org