Fall 2020 — Extra Credit

(Due Friday 20-November, 11:00am EST) — no late submission, no extension
Purpose:

For this extra credit assignment you will work individually, no partner.

Consider the following information and answer the following questions. All homeworks are due before class on the due date. Please remember that the UVA Honor Code is in effect.

Thanks to Jake Moses for creating this extra credit assignment.


Situation

You are a software developer engineer at a technology company and are responsible for working with the database in order to extract information regarding robot operations. In this particular scenario, you are classifying incidents that have occurred with the robot so that the proper engineering team can evaluate the robot and fix what happened. In this particular situation, all of the events that have occurred on the robot are stored in this particular database, as well as all of the labels that have ever been applied to these events are stored in the database. This allows us to keep track of all the labels that have ever been applied to a given event, however, only the most recent label in the database still applies to the event.

Tables

Events( event_id, expedition_id, robot_id, handler_id, location, event_timestamp )
Labels( label_id, label_name, label_type )
Event_Labels( event_id, label_id, created_at ) 

Background information

Sample data

Table: Events

event_id expedition_id robot_id handler_id location event_timestamp
1 abcdef yellow jake New york 2020-08-01 14:01:00

Table: Labels

label_id label_name label_type
adawe-123fw3 Navigation team
adf42s-31245s Mobility team
fdw2s-843ew0 Wheel Issue Root cause
jlkl21-9023f1 GPS Root cause

Table: Event_Labels

event_id label_id created_at
1 adawe-123fw3 2020-08-03 12:13:00
1 adf42s-31245s 2020-08-03 11:58:23
1 jlkl21-9023f1 2020-08-03 12:01:00

The most recent labels on event 1 are:

  • Team: Navigation
  • Root Cause: GPS

[Total: 40 points.] toward assignment grades (before conversion)

  1. (10 points) Write a SQL command where you can find all root_cause labels that have ever been applied to a given event. You can assume that the event you are checking is event '1'
  2. (10 points) Write a SQL command where you can find the most recent root_cause label on each event
  3. (10 points) Write a SQL command where you can find all events that currently have a 'Mobility' team label. You cannot assume that there are no root_cause labels named 'Mobility'
  4. (10 points) Write a SQL command where you can find all events that currently do not have either a team or root_cause label

Submission

Save your report as a PDF file — No word document. No handwritting. No hand-drawing.

Name your file as yourComputingID-EC-sql (for instance, up3f-EC-sql). Submit your assignment to Collab.

Making your submission available to instructor and course staff is your responsibility; if we cannot access your file then you will not get credit. Be sure to test access to your file before the due date.