HW#1 --- last modified September 16 2020 21:28:44.

Solution set.

Due date: Sep 16

Files to be submitted:
  Hw1.zip

Purpose: To gain experience with storing and querying semi-structured data such as XML and JSON in a DBMS. To recall how to write, from our intro DB class, simple transactions on a DBMS.

The main course outcomes covered by this assignment are:

CLO1 -- Be able to build and a database making use of XML columns as well as suitable queries to those columns for a concrete use case.

Description:

For this homework, I'd like you to use the Postgresql DBMS. It can be downloaded from https://www.postgresql.org/, if you don't already have a copy of it. The homework has four parts: an XML experiment part, a mapping XML documents into a traditional database table part, a using XML type and XPath part, and finally, an experiment with SPARQL part. I describe each of these in turn below:

1. For the XML experiment part, I'd like you to first redo the Aug 26 In-Class Exercise, except rather than make a DTD for a shopping cart language, I want you to make an XML Schema for this language. Since XML Schema does not support entities, I leave it up to you how you modify that portion of the DTD. Put this in a file cart.xsd that you include in your Hw1.zip file you are submitting for the homework. Also, include at least three example documents in this new language cart1.xml, cart2.xml, cart3.xml. Prove your documents conform to their XML Schema by including three, less-than-1MB-sized, screenshots of these documents validating in OxygenXML (has a free 30 day trial). Finally, write a XSLT stylesheet cart_to_table.xsl that transforms cart.xsd data to an HTML table (keep it simple, but map each possible cart tag to something).
2. For the mapping XML documents into a traditional database table part, I want you to submit as part of your Hw1.zip file, a file oblivious.sql consisting of a sequence of SQL commands that would execute correctly on a version of PostgresSQL not more than 4 years old (don't use any super-new features). The first command should create the schema-oblivious mapping table, Node, given in the book and class. I then want you to have a sequence of INSERT statements in your file (basically, to give you a flavor of how awkward this mapping can be) that insert the equivalent of the data in cart1.xml into this table. Finally, your file should have a SQL query on the Node table that returns all items in the cart between $5 and $15.
3. For the using XML type and XPath part, I want you to write a short program in either Java (call your program Cart.java in this case), C++ (call your program Cart.cpp in this case), Python (call your program Cart.py in this case), or PHP (call your program Cart.php in this case). Assume my laptop has a version of each language that is at most 4 years old. Your program should have any database connection info pulled out as constants at the top of the program so I can easily tweak them. Your program should make a connection to a Postgresql database, Shopping, then create a table if it doesn't exist called Cart with an XML data type column. It should then look for all files in the current folder that match the pattern cart*.xml and insert these into the Cart table. Finally, it should perform one query on the resulting table that makes use of the PostgresSQL XPath() function (I am being flexible on what you do, but it should demo you understand XPath syntax) and output the result (should be non-empty) to the terminal.
4. Wikidata is an important example of RDF data. For the SparkQL part of the homework, I want you to conduct some experiments using their online SparkQL Query Service. I want you to create four SparkQL queries on this dataset that are not already on the the WikiData website, one looking for Dog images, one for items from the year 1970, one related to medicine, and one related to Alice in Wonderland. Put your queries and the results of running them in the file sparkql.txt which you submit in the Hw1.zip file.

That concludes the description of the homework. Below is the point breakdown: