HW#5 --- last modified April 29 2020 20:35:25.

Solution set.

Due date: May 11

Files to be submitted:
  Hw5.zip

Purpose: To gain experience with information integration, data mining, and OLAP.

Related Course Outcomes:

The main course outcomes covered by this assignment are:

CLO7 -- Be able to create or deploy a query mediator for a system with at least two data sources.

CLO8 -- Be able to determine how the A Priori algorithm would operate on a toy dataset

CLO9 -- Design and deploy analytical databases for OLAP.

Specification:

This homework will consist of two parts, a written part and a coding/experiment part. Both parts will be submitted in the Hw5.zip file. The written part should be in a file, named Hw5.pdf, within this zip-file. For the written part of the homework you should write solutions for the book questions listed below. In what you turn in, make sure to write the names and student ids for each group member. For each problem, first copy and paste the question, then write your solution to it beneath it.

• Exercise 20.5.2a (do with three sites 1,2,3 and where 1 and 2 want to commit 3 want to abort), 20.6.2 (change ii to 5% and iii to 30%), 22.1.1 (modify so B_4 is {juice, pepsi, beer}), 22.2.3

For the coding part of the homework, I want you to write a simulator for the following real world set-up: You have a mobile food delivery app which can be used by stores that want to offer food deliveries. For each delivery person, this app stores data locally in a sqlite database. The app developer has their own site where they keep an OLAP database using Mysql of what deliveries people are doing, for which stores, when during the day, and to what geographic regions. One could imagine the app developer has a web app that store owners can use to access info about their store. The mobile app phones home periodical with the changes since the last time it spoke with the app developers site. This is essentially a mediated transaction the mobile database and the OLAP database.

Although you could actually code a mobile app for this homework in Java using Android, that would be way too much work. Instead, I want you to write two command line programs to simulate our set-up: DeliveryApp.java and Olap.java. For these programs, the top of your program's class file should have any constants the grader needs to tweak to make a JDBC connection to a Mysql DB or sqlite Db. You should have a README.txt file saying what changes the grader needs to make to get your code to work on the grader's machine. You should also have a file olap.sql, the grader can use to create your Mysql database. The grader will compile your programs from the command line with the lines:

javac DeliveryApp.java
javac Olap.java

DeliveryApp should support three different functionalities, each of which can be tested from the command line, using lines like:

java DeliveryApp deliver id location store pickup_time time_to_deliver amount
java DeliveryApp show id date format
java DeliveryApp send id

Here id should be some 9 digit device id such as 123456789. The command should store in a sqlite file id.sqlite (where id has been replaced with a number) in a table DELIVERY, the information location, store, pickup_time, time_to_deliver, amount. Here location consists of two int's separated by a hyphen, the first being a latitude, the second a longitude. For example, 37-121. The store should be a varchar(9) name of a store. The pickup_time should be in the format: YYYY-mm-dd:HH:ii:ss. For example, 2020-05-02:21:07:35. Notice hours are represented using a 24 hour clock, and leading zeros are used if the value is below 10. The time_to_deliver should be in the format: HH:ii:ss. Finally, amount can be stored in a FLOAT and represents the amount of money charged for meal that was delivered. An example using this command might be:

java DeliveryApp deliver 987654321 45-100 BobsPizza 2020-05-02:21:07:35 00:19:12 27.50

The second command simulates a delivery person using the DeliveryApp to look at deliveries that have already done. In java DeliveryApp show id date format, id is as in the delivery command, date should be in the format YYYY-mm-dd, and format should be one of time_to_deliver or location. For example,

java DeliveryApp show 123456789 2020-04-27 time_to_deliver

This should for each store list out the amount of time spent on delivering for each store for that day together with the total amount received for those deliveries. For example,

BigBurger 03:45 $110.72
BobsPizza 02:15 $80.60
ThaiOneOn 01:52 $201.59
VampireSteaks 00:45 $61.00

If location had been chosen then the amount of time spent on and the amount earned on delivery should be grouped by location. For example, the output might look like:

37-121 05:25 $653.86
37-122 03:15  $271.19

The id.sqlite files should have a table that is used to keep track of the last time that data was sent to the app developer's servers. The command java DeliveryApp send id should use this last time info and read the data in the DELIVERY table since that time, and write it into a Mysql database named olap. It should then update the last time data sent info in the id.sqlite database.

Your Olap program should be run from the command line with a command like:

java olap region store time_period

It should then output the average delivery time for that region and time period. Further it should also output the rollup (your query should have a WITH ROLLUP as part of its GROUP UP) of these value. Here region is of the form lat1-long1:lat2-long2 representing a rectangle and time period can be YYYY or YYYY-mm, or YYYY-mm-dd. For example,

java olap 35-115:45-125 BobsPizza 2020-05

Might use a where clause for the region, but do a group by with roll up on the remaining parameters to output:

store year month avg
BobsPizza 2020   5   19.30
BobsPizza 2020  NULL 16.10
BobsPizza NULL  NULL 18.31
NULL NULL  NULL 12.50

This completes the description of what your programs need to do for this homework, below is the grading scheme.

Point Breakdown