XML file辅导、XQuery query讲解、辅导SQL设计、SQL编程调试
- 首页 >> Database Homework 4
1.Consider the following relational data:
Products:
pid Name Price Description
---------------------------------------------------------------------
p123 gizmo 22.99 great
p231 gizmoPlus 99.99 more features
p312 gadget 59.99 good value
========================================
Stores:
sid Name Phones
-------------------------------------------------
s323 Wiz 555-1234
s521 Econo-Wiz 555-6543
============================
Sells:
sid pid Markup
----------------------------------------------
s323 p231 10%
s323 p123 25%
s323 p123 15%
===========================
(a) 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 products
the products element contains a sequence of product sub-elements, one
for each product in the database
each product element contains one name, one price, and one description
sub-element, and a sequence of store sub-elements, one for each store that sells
that product
each store element contains one name, one phone, and one markup subelement.
(b) Assume the relational database above is accessible through an XML interface
that exports it as:
p123
gizmo
22.99
great
...
...
...
...
...
Write an XQuery expression that, when given an input with this structure,
constructs an XML document with the structure described in part (1a).
(c) Assuming that you have XML documents with the structure given in part (1a),
write an XQuery expression that returns the names and prices of all products that
are sold at least at one store with a markup of 25%.
(d) Write the same query in SQL over the original relational database schema.
2. Consider XML data given by the following DTD:
(Elements that are not defined are PCDATA.) For each of the questions below
write an XPath or an XQuery query.
(In case you need any XQuery built-in functions, you can reference:
http://www.xqueryfunctions.com/ )
(a) Return all titles in the XML document (b) Find the addresses of all theaters that have some tickets under $35 on
11/9/2008 and the titles of their show on that night.
(c) Retrieve all concert titles whose type is chamber orchestra where the average
ticket price is at least $50.
(d) Write a query that constructs a new XML document with the following
structure:
3. This homework problem uses the following XML file, DTD file and XSL file:
The XML File
Note that one item is repeated, and that the use of spaces is not entirely uniform.
Leslie Lamport
Latex: A Document Preparation System
1986
Addison-Wesley
David Marr
Visual information processing
1980
290
199
218
Phil. Trans. Roy. Soc. B
R. K. Clifton
Breakdown of echo suppression in the precedence<br>effect
1987
82
1834
1835
J. Acoust. Soc. Am.
David Marr
Vision
1982
NY
Freeman
David Marr
Visual information processing
1980
290
199
218
Phil. Trans. Roy. Soc. B
The DTD Code
The XSL Code
result-ns="http://www.w3.org/TR/REC-html">
Bibliography
Answer the following questions.
1) Modify the XSL (and maybe the XML) file so that books are displayed in a
style like that of the following:
Lamport, Leslie. Latex: A Document Preparation System (AddisonWesley
1986).and journal articles are displayed in a style like that of the following:
Marr, David. Visual information processing, Phil. Trans. Roy. Soc. B, 290,
pp.199-218, 1980.
2) Add two books and two journals to the XML file, where two of the new items
have some information missing.
3) Define a new type of bibliography item for PhD theses in XSL, add two such
items to the XML file, and add the appropriate declarations to the DTD code.
1.Consider the following relational data:
Products:
pid Name Price Description
---------------------------------------------------------------------
p123 gizmo 22.99 great
p231 gizmoPlus 99.99 more features
p312 gadget 59.99 good value
========================================
Stores:
sid Name Phones
-------------------------------------------------
s323 Wiz 555-1234
s521 Econo-Wiz 555-6543
============================
Sells:
sid pid Markup
----------------------------------------------
s323 p231 10%
s323 p123 25%
s323 p123 15%
===========================
(a) 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 products
the products element contains a sequence of product sub-elements, one
for each product in the database
each product element contains one name, one price, and one description
sub-element, and a sequence of store sub-elements, one for each store that sells
that product
each store element contains one name, one phone, and one markup subelement.
(b) Assume the relational database above is accessible through an XML interface
that exports it as:
...
...
...
Write an XQuery expression that, when given an input with this structure,
constructs an XML document with the structure described in part (1a).
(c) Assuming that you have XML documents with the structure given in part (1a),
write an XQuery expression that returns the names and prices of all products that
are sold at least at one store with a markup of 25%.
(d) Write the same query in SQL over the original relational database schema.
2. Consider XML data given by the following DTD:
(Elements that are not defined are PCDATA.) For each of the questions below
write an XPath or an XQuery query.
(In case you need any XQuery built-in functions, you can reference:
http://www.xqueryfunctions.com/ )
(a) Return all titles in the XML document (b) Find the addresses of all theaters that have some tickets under $35 on
11/9/2008 and the titles of their show on that night.
(c) Retrieve all concert titles whose type is chamber orchestra where the average
ticket price is at least $50.
(d) Write a query that constructs a new XML document with the following
structure:
3. This homework problem uses the following XML file, DTD file and XSL file:
The XML File
Note that one item is repeated, and that the use of spaces is not entirely uniform.
NY
The DTD Code
The XSL Code
Bibliography
pages
Answer the following questions.
1) Modify the XSL (and maybe the XML) file so that books are displayed in a
style like that of the following:
Lamport, Leslie. Latex: A Document Preparation System (AddisonWesley
1986).and journal articles are displayed in a style like that of the following:
Marr, David. Visual information processing, Phil. Trans. Roy. Soc. B, 290,
pp.199-218, 1980.
2) Add two books and two journals to the XML file, where two of the new items
have some information missing.
3) Define a new type of bibliography item for PhD theses in XSL, add two such
items to the XML file, and add the appropriate declarations to the DTD code.