1.Consider the following relational data: Products: pid Name Price Description -
ID: 3920300 • Letter: 1
Question
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 sub-element.
(b) Assume the relational database above is accessible through an XML interface that exports it as:
<db>
<products>
<row> <pid>p123</pid>
<name>gizmo</name>
<price>22.99</price>
<description>great</description>
</row>
...
</products>
<stores>
<row> ... </row>
...
</stores>
<sells>
<row> ... </row>
...
</sells>
</db>
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.
Explanation / Answer
-- Part 1a
SELECT ( SELECT pid,Name,Price,[Description] from Products
FOR
XML PATH,
ROOT('products'),
TYPE
),
( SELECT * from Stores
FOR
XML PATH,
ROOT('stores'),
TYPE
),
( SELECT * from Sells
FOR
XML PATH,
ROOT('sells'),
TYPE
)
FOR XML PATH(''),
ROOT('db')
GO
--------------------------------------
--Part 1b
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
EXEC master.dbo.sp_configure 'xp_cmdshell', 1
RECONFIGURE
-- Please change your dir location -- Please check permission too
EXEC xp_cmdshell 'bcp "SELECT ( SELECT pid,Name,Price,[Description] from Products FOR XML PATH, ROOT(''products''), TYPE ),( SELECT * from Stores FOR XML PATH, ROOT(''stores''), TYPE ), ( SELECT * from Sells FOR XML PATH, ROOT(''sells''), TYPE ) FOR XML PATH(''), ROOT(''db'') " queryout "C:cptest.xml" -T -w -r -t,'
-- Part 1c
SELECT ( Select * from Products
where pid IN(
Select PID from Sells
INNER JOIN Stores ON Sells.[sid]=Stores.[sid]
where Markup='25%')
FOR
XML PATH,
ROOT('products'),
TYPE
)
FOR XML PATH(''),
ROOT('db')
GO
--Part 1d
Select * from Products
where pid IN(
Select PID from Sells
INNER JOIN Stores ON Sells.[sid]=Stores.[sid]
where Markup='25%')
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.