DUE: Tuesday 1/28 11:59pm

HOW/WHAT TO SUBMIT: All files should be submitted through WebSubmit. Only one of your team members needs to submit on behalf of the team. On the WebSubmit interface, make sure you select compsci290 and the appropriate lab number. You can submit multiple times, but please have the same team member resubmit all required files each time. To earn class participation credit, submit a text file team.txt listing members of your team who are present at the lab. To earn extra credit for the lab challenge (Part 2), you must get your solution checked off in class, but you don't need to submit anything.

1. A Few More Tips¶

(Here, we assume that you haven't created indexes by yourself with CREATE INDEX.)

Remember the following query in Lecture #3 for computing the total duration of a person's service in the Congress? Run it and note how slow it is:

SELECT id, first_name, last_name, birthday,
       (SELECT SUM(end_date - start_date)
        FROM person_roles r
        WHERE r.person_id = p.id) AS duration
FROM persons p;

Now, issue the following SQL command:

CREATE INDEX ON person_roles(person_id);

Next, re-run the same SQL query above. (In psql, you can just press [UP] a couple of times to recall the previous commands.) See how fast it now runs? Appreciate what an index can do for you! Can you explain (intuitively) how the new index helps with this query? (Free feel free to create additional indexes to help with query performance for the reminder of this lab.)

Another trick: for a complex query, you can use the WITH clause to define a sequence of temporary views that each build onto other temporary views. Here is the syntax:

WITH tmp_view_1 AS
  (... query that defines tmp_view_1 ...),
tmp_view_2 AS
  (... query that defines tmp_view_2 ...),
tmp_view_3 AS
  (... query that defines tmp_view_2 ...)
-- your actual query then starts below:
SELECT *
FROM tmp_view_3
WHERE ...;

2. [Challenge] Fact-Checking using a Relational Database¶

(1) A Nation Gone Liberal? Our nation has become more liberal. The total length of terms served by Republicans has gone up by 135% from the 19th century (between 1800 and 1900) to the 20th century (between 1900 and 2000). But over the same period, the total length of terms served by Democrats has increased by a whopping 236%!

(2) Democrats: a Party of No's? In 2013, Democratic members of the Congress casted more Nay/No votes than the Republicans.

(3) Women on Obama Care Females in the House are strong Obama Care supporters. Less than 1 in 4 females voted to repeal Obama Care (vote_id = 'h154-113.2013'). In contrast, more than 60% of the males voted to repeal it.

(4) Chiesa the Republican Jeffrey Chiesa, a Republican senator from New Jersey, voted fewer than 60 times with his party majority in 2013. That's abysmal in comparison with Mitch McConnell, the Republican senate minority leader, who voted 262 times with the party majority.

Lab #3: More Relational Data Processing¶

1. A Few More Tips¶

2. [Challenge] Fact-Checking using a Relational Database¶