Due:  Friday March 1st at 11:59PM. You can optionally work in pairs on this assignment. (Late submissions policy: see syllabus for details.)

General Instructions

Ready for some SQL fun with a dataset from the real world?   Let’s do this!   This  is the first part of your SQL assignment (worth 40% of A2’s grade).  The second part will be published after the midterm test and will involve more advanced SQL operations.  Note:  You are encouraged to work on your  Google  Cloud  Platform  PostgreSQL instance for this assignment (use your free academic credit!).

Download the starter zip folder from the A2 directory on Canvas. You’ll find the following:

.  The database schema script (a2 dimeDB.sql).  Execute the lines in this SQL script in your postgres instance, after connecting to it.  This script will create the schema and populate its tables from a public Google storage bucket.  Note that your submission will be tested later on a different  dataset from the one that will be loaded into your database here, so make sure your queries can work on any valid database instance and don’t contain hard-coded identifiers, for example.

.  Expected answers for some of the queries (in expected answers/), based on the provided test dataset.  Note that these answers were produced on a GCP PostgreSQL instance.  There may be minor differences in the ordering of strings, if you run your queries on a different platform or operating system with different text encoding.

.  Sample answer file (q sample.sql); you must follow this style when writing your answers!

Once you have submitted your files, be sure to check that you have submitted the correct version; new or missing files will not be accepted after the due date (unless you and your partner -if applicable- have a late-token remaining and you resubmit within 24 hours of the deadline).

DIME Schema Description

Congratulations, you’ve been hired by a research think tank in D.C. to study how campaign financing and political donations impact politicians in America. You will be using a real-world data set collected and published by scholars at Stanford University, known as: The Database on Ideology, Money in Politics, and Elections (DIME) Adam [2016], Bonica [2016].  This database was developed as part of a large-scale effort to  “construct a comprehensive ideological mapping of political elites, interest groups, and donors.”

This database contains 5 tables, described briefly below. For detailed descriptions of the attributes in each table, you will read specific sections from the official DIME documentation (referenced below).  You can find the official documentation files in our A2 Canvas folder.

. Recipient:  A tuple in this relation represents a political candidate and contains information on the election cycle, fundraising statistics, election outcomes, and characteristics like name and gender, if applicable – among other features. Note that we refer to candidates as recipients because they receive donations from contributors. Each candidate is identified by a unique recipient ID referred to as “bonica.rid” .

Read the detailed description of this table’s attributes in section 5.1 of the document dime codebook v3 1.pdf , pages 6-9.

. Contributor: A tuple in this relation represents a donor, also known as a contributor. Donors can be individuals or organizations.  Each donor is identified by a unique contributor ID referred to as  “bonica.cid” .   This table also contains a column for each election year covered by the data, storing the amount of donations given by a contributor during that year.  For example, the column “amount.2014” stores the amount of campaign donations made by a contributor during the 2014 election cycle, and so on.

2. (6 points) Many, many donors. Who is the candidate that had the highest number of distinct donors for an election, among all candidates?  Hint:  The attribute num.givers in table Recipient can help you find this information.  If there are ties, report them all.  Your query should return that candidate’s recipient ID (i.e., bonica.rid), their name, the election cycle, and the number of distinct donors (i.e., num.givers), renamed as ‘num donors’. When you rename an attribute, make sure you use the exact alias mentioned here, to avoid losing points.

3. (8 points) Just take my money, 2022! Who were the top 10 contributors of political donations in the year 2022? Report their contributor IDs (stored in bonica.cid),their names (stored in most.recent.contributor.name), their type (stored in contributor.type), and the total amount of money they donated in the year 2022.

You can assume that there will be no ties in the data; i.e., no two contributors will have the exact same total amount of donations in the year 2022.

Hint: You can utilize the LIMIT clause in your SQL query to return a certain number of rows, if needed.

(10 points) Take lots of money! What if we want to consider all the years available in the data, not just the year 2022? Find the top 10 contributors in terms of the total amount of money they donated over the years 1980 to 2022 (inclusive). Again, report their contributor ID, contributor name, type, and the sum of money they donated over all years.

You can assume that there will be no ties in the data; i.e., no two contributors will have the exact same total amount of donations in the data.

Hint: You can utilize the LIMIT clause in your SQL query to return a certain number of rows, if needed.

5. (10 points) Would you cosponsor me? Each bill in the Congressional Voting table (known in our schema as table ‘Vote’), has an indicator for whether the legislator in a given tuple was a co-sponsor of that bill (1 = yes, 0 = no). Use this information to identify the bill(s) with the highest number of co-sponsors in the database. If there are ties, report them all.