BudgetContractidEmployIDToCEOFromCSE 444, Spring 2002CSE 444, Spring 2002Assignment #4: due Friday, May 24Objectives: To be able to write XML, DTD, XPath and XQuery documents or codes. To be able to understand constraints and triggers. Number of points: 100 points Formats: [15 points]Organize all your answers as an XML document. You don’t need to give the DTDfile. And the XML document doesn’t need to be complex. For example, you caninclude the whole answer for question 1(a) as an element with the tag of<answer></answer>, and use another element to indicate the question number. Justorganize it in a reasonable way, and make sure that your XML document is well-formed. That counts for 15 points.Note: To include code in your document, You may need to refer tohttp://www.w3schools.com/xml/xml_cdata.asp for CDATA in XML.Questions: 1. [60 points] Consider the following relational data:Products:pid Name Price Description323 gizmo 22.99 great233 gizmo plus 99.99 more features312 gadget 59.99 good valueStores:sid Name Phones282 Wiz 555-1234s521 Econo-Wiz 555-6543Sells:pid Markup sid323 10% s521233 25% s282233 15% s521a. [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 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 subelement, and a sequence of store subelements, one for each store that sells thatproduct. Also, it has an attribute pid of type ID. - each store element contains one name, one phone, and one markup element,plus an attribute sid of type ID.b. [10 points] Write the XML document obtained by exporting the databaseabove.c. [20 points] Assuming that you have an XML document named “products.xml”with the structure given in a), for each question below, write an XQuery query.State whether we can simply write an XPath expression to retrieve the sameinformation. If so, write the XPath expression. If not, give the reason. (i) Which products are sold at least in one store? List their names. Makesure that each product name is listed only once. (ii) What are the product-store pairs whose markup is no lower than 15%?For each pair, list the product name and the store name. (iii) Which stores sell some products with a price higher than 50? Listtheir IDs. Make sure that each store ID is listed only once. (iv)Which products (except “gizmo”) are sold in some store that also sellsthe product “gizmo”? List their names. You can list the same name onceor several times. Note: Your query can return the required information in any format that isreadable. For example, in i), the answer can include only names, or names withtags. d. [10 points] (i) Assuming that you have XML documents with the structure given in a),write an XQuery query that returns the names and prices of all productsthat are sold in all stores with a markup of 25%. (ii) If it is applied to the XML document in b), what’s the result of yourquery? Make sure that the format of the results is in accordance to yourquery.(iii) Now, write the same query in SQL over the original relationaldatabase schema.Note: The result should not be duplicated.e. [10 points] Assume the same database is represented in an XML documentwhose structure follows the relational tables as shown below. Write an XQueryquery that, when given an input with this structure, constructs an XML documentwith the structure described in a).<db> <products><row> <pid>323 </pid><name> gizmo </name><price> 22.99 </price><description> great </description> </row><row> ... </row><row> ... </row></products><stores><row> ... </row> ... </stores><sells><row> ... </row> ... </sells> </db>2. [25 points] The following schema captures the E/R diagram shown below. (Youmay want to refer to the midterm answer key on our course web site.)Employees (EmployID, Lot)Hourly_Emps (EmployID, Hours_worked, Hours_wages)Contract_Emps (EmployID, ContractID)Company (DeptID, Name, Budget, CEOContractID, From, To)Work-in (EmployID, DeptID)EmployeesLot EmployIDContract_EmpsisaHourly_EmpsisaHours_worked ContractidHours_wagesCompanyDeptID BudgetNameWork-inFromCEOToWrite SQL statements to create tables according to the schemas. While creatingthe tables, consider the following requirements:a. [5 points] Choose realistic domains for the attributes.b. [5 points] Specify key (including non-primary key) and foreign keyconstraints according to the E/R diagram.c. [9 points] Enforce integrity constraints in this way:(i) If a company tuple is deleted, all the tuples in table Work-in that point tothe company are deleted. If the compID field of the tuple is updated,update all the corresponding tuples in table Work-in in turn. It’s the sameto employees. (ii) If a tuple in the Employees table is deleted or the key field is updated, thecorresponding tuple in Contract-Emps or Hourly-Emps is deleted orupdated. (iii) If the record of a CEO is deleted from table Contract-Emps,the CEO field for the corresponding company is set to NULL. If the key ofthe tuple is updated, the corresponding company is updated accordingly.d. [6 points] Now define the following triggers for your tables:(i) If after updates, the attribute of From is no earlier than To, adjust theattribute of To to be one year later than “From”.(ii) If after updates, an hourly employee has worked for more than 2000 hours,increase his hour wage by
View Full Document
Unlocking...