ࡱ > G I F 7 bjbjUU &4 7| 7| l $ \ 0 @ @ @ @ @ 0 0 0 0 0 0 0 $ 2 54 Z 80 80 @ @ M0 & @ @ 0 0 U + p `. @ 4 A - n. c0 0 0 *- 6 4 4 `. CSE 444, Winter 2003
Assignment #3: due Wednesday, March 5th
Objectives:
To be able to write XML, DTD, XPath and XQuery documents or code.
Number of points:
100 points
Questions:
Consider the following relational database Commodity:
Products:
pidNamePriceDescription323gizmo22.99great233gizmo plus99.99more features312gadget59.99good valueStores:
sidNamePhones282Wiz555-1234s521Econo-Wiz555-6543Sells:
pidMarkupsid32310%s52123325%s28223315%s521[10 points] We want to export this data into an XML file. Write a DTD describing the following structure for the XML file: - there is one root element called stores - the stores element contains a sequence of storesub elements, one for each store in the database - each store element contains one name, and one phone subelement, and a sequence of product subelements, one for each product that the store sells. Also, it has an attribute sid of type ID. - each product element contains one name, one price, one description, and one markup element, plus an attribute pid of type ID.
[10 points] Write the XML document obtained by exporting the database Commodity into the DTD.
[20 points] Assuming that you have an XML document named stores.xml with the structure given in Question 1, for each question below, write an XQuery query. State whether we can simply write an XPath expression to retrieve the same information. If so, write the XPath expression. If not, give the reason.
Which stores sell some products with a price higher than 50? List their IDs. Make sure that each store ID is listed only once.
Which stores (except Wiz) sell the same products as store Wiz? List their names. You can list the same name once or several times.
Note: Your query can return the required information in any format that is readable. For example, in (i), the answer can include only names, or names with tags.
[25 points] Consider the following XQuery:
For $p IN distinct(stores.xml//product)
RETURN
{$p/name/text()}
{$p/price/text()}
{$p/description/text()}
{
FOR $s IN distinct( stores.xml//store[product/pid = $p/pid])
RETURN
$s/name/text()
$s/phone/text()
{
FOR $m IN stores.xml//store[sid = $s.sid]/product[pid = $p.pid]/markup
RETURN
$m/text()
}
}
(i) [15 points] What is the result of the XQuery when it is applied to the XML document in Question 2? Give the XML result file and the DTD that describes its structure.
(ii) [10 points] If we export the database Commodity into the result DTD in 4(i), do we get the same XML file as the result file we get in 4(i)? Why or why not? For the purpose of this question, we ignore the order of elements.
[15 points] Assume the database Commodity is represented in an XML document database.xml whose structure follows the relational tables as shown below. Write an XQuery query that, when given an input with this structure, constructs an XML document with the structure described in Question 1.
323
gizmo
22.99
great
...
...
s282
Wiz
555-1234
...
...
323
10%
s521
...
...
[20 points]
Assuming that you have an XML document with the structure given in Question 1, write an XQuery query that returns the names and prices of all products that are sold in all stores with a markup no lower than 15%. To make it easier, we suppose markup>=15% can be used to judge whether the markup is not lower than 15%.
Now, write the same query in SQL over the original relational database Commodity.
Note: The result should not contain duplicates.
< > ? A M N
$ % * + 0 1 2 5 6 @ A F G T U V Y Z ` a f g q r s y z { ~ ǿ OJ QJ ^J nHtH OJ QJ ^J o( 5OJ QJ \^J o( CJ aJ nHo(tHPJ CJ OJ PJ QJ ^J 5OJ PJ QJ \^J 5H*\5\G @ A N $$If a$ $h^ha$ $a$ h^h % + 1 2 6 A G [\ R R R R [ R R R $$If a$ $$If T \ r A 0 6 2 3 4 ab G U V Z a g r s Rt R $$If T \ r A 0 6 2 3 4 ab $$If a$ s { ZL Zd $$If T F V V 0 6 2 3 4 ab $$If a$ hx [$ \$ ^h
. 4 < B ` g # $ ? B M Q S CJ OJ QJ o( 5OJ QJ \^J nHtH OJ QJ ^J nHtH OJ QJ ^J o( 5\ 5OJ QJ \^J o( PJ CJ aJ L g Z hx [$ \$ ^h $$If T F V V 0 6 2 3 4 ab $$If a$ p8 g g g p8 g g g p8 g g $$If a$ $$If T F r r 0 6 2 3 4 ab O P g b ` Q B $
&