3380 Fall 2020 Assignment 3
Parts 1 is to be handed in with handin
handin 3380 a3p1 myA3p1Folder
Parts 2 and 3 are to be handed in with Crowdmark.
Part 1 – Programming (5 marks)
Given 2 tables that are stored in text files as CSVs. There are two CSV files provided to you in this folder.
Do joins in code. You may choose any programming language, but it must run in the rodents lab. Include a makefile that has a make run task, which should both build (if necessary) and run your program. Include everything to make sure that make run will work with no modification to your program, or addition of other files.
Include a readme text file that explains how and where to run your program, and any other special notes. Ideally this is just a note to say make run.
Write a left join, and an inner join, and print out the results of the left join and inner join operations. The provided text files have the key to merge on in the first column (personID). Though we may test with different text files, you can assume:
• The first column has the key to merge on
• The top row is a header
Do not import any libraries that do merges/joins to do this. You must do this manually. You may import ArrayList, or similar data structures to hold your data, but nothing more.
Example output:
— Inner join —
1,Mikey,1,Playing the Organ
1,Mikey,1,Sailing a ship
1,Mikey,1,Go through a secret fireplace
2,Brand,2,Playing the Organ
2,Brand,2,Sailing a ship
2,Brand,2,Go through a secret fireplace
2,Brand,2,Dodge Rocks
3,Chunk,3,Playing the Organ
3,Chunk,3,Go through a secret fireplace
3,Chunk,3,Go down a slide
4,Mouth,4,Playing the Organ
4,Mouth,4,Go down a slide
5,Stef,5,Sailing a ship
6,Andy,6,Sailing a ship
— Left join —
1,Mikey,1,Playing the Organ
1,Mikey,1,Sailing a ship
1,Mikey,1,Go through a secret fireplace
2,Brand,2,Playing the Organ
2,Brand,2,Sailing a ship
2,Brand,2,Go through a secret fireplace
2,Brand,2,Dodge Rocks
3,Chunk,3,Playing the Organ
3,Chunk,3,Go through a secret fireplace
3,Chunk,3,Go down a slide
4,Mouth,4,Playing the Organ
4,Mouth,4,Go down a slide
5,Stef,5,Sailing a ship
6,Andy,6,Sailing a ship
7,Perkins,Null,Null
Bonus (2 marks)
Also do a full outer join of the two tables.
Be sure to mention your bonus attempt in your readme.
Bonus (2 marks)
Do a natural join between the datasets.
You will have to create more datasets to prove your code is working. Make a note in your readme about what you did, how your tests display that it works.
This must work with multiple joining columns.
Part 2 – SQL injection (3 marks)
Given the “A3P2” class, run the class, and, using malicious inputs, inject SQL into the program to extract data, and change data.
Download the files in A3P2.zip. You can run the code on aviary/rodents. You can run it with java -cp .:hsqldb.jar A3P2, which will put you onto a command-line interface with the database.
Using injection, do the following tasks:
• Delete records you should not be able to delete given the interface
• Update records you should not be able to update given the interface
• View records not intended from the given command (view other tables than intended [even though you could get it in a different way])
You will likely need to inject into a few different available commands. Type h for help on what commands are available.
You must complete these tasks without modifying the code, just by changing the inputs – either in the command-line interface, or in the CSV file.
Document your malicious command, and results, with screenshots, and a small (1-3 sentence) writeup of what you accomplished.
Part 3 (5 marks)
Santa wants a “Made in the North Pole ©” solution for contact tracing. A database of contacts has been curated. Once infected, the elves are immediately contagious, and remain contagious for a week (to the second, surprisingly). The time is presented in UNIX Epoch time – seconds since Jan 1, 1970.
Find:
• Which elf had the most contacts?
• Which elf had the least contacts?
• The most popular elf got infected at 1500000000. Which elves did that elf meet directly while contagious?
• All the elves that met the contagious elf met became contagious the moment met. Show 3 levels of affected elves from the most popular elf (found above, you can hard-code the elf into your query).
• Do the same as 4, but for the least connected elf.
To do 4 and 5, use this article, which describes how to compare nodes in a path
Hints:
• There is 604800 seconds in a week
Hand in via Crowdmark:
Hand in two images:
• A screenshot of your query by itself so it is clearly visible and readable.
• A screenshot of the query with the results as a table, or as a node graph.
Alternative Part 3 – Simulation (5 marks + 4 bonus)
Given the above contract tracing database, or one of your own, create a graph simulation with a graph database.
Simulate at least 4 weeks of pandemic. For any contact with a contagious elf, there is a 5% chance of transmission. Same parameters as above, unless you’d like to make your simulation more realistic. You will have to connect a programming language (Java, Python, etc) to run multiple queries through your database, and provide odds for the transmission.
Make a 2-5 minute narrated video explaining your process, how you simulated marking nodes.
Provide a YouTube (or similar) link to as one of your screenshots. Consider minifiying the link if it is ridiculously long.