In this assignment, we will perform. some analytics on real data from one hundred years of elections. The data
, a database for political science. It contains information on political parties, elections and
cabinets for most democracies that are part of the EU (European Union) or the OECD (Organization for Economic
Co-operation and Development)
Download the schema and a sample dataset. Your code for this assignment must work on any database instance
(including ones with empty tables) that satisfies the schema, so make sure you understand the schema well.
Warmup: Getting to know the schema
A similar schema to the one we are using in this assignment has been used previously in 343, but has been changed
for this term, so be sure you are following exactly the specification in this handout. It is critical that you invest
time early on getting to understand the structure and meaning of the tables. Here are a few things to keep in mind.
• The schema can record elections of two kinds. A parliamentary election is an election within a country
to choose a national government. A European parliament election (or EP election) is an election, held
across all European Union countries, to choose national representatives for the European parliament.
• If you are not familiar with any of the political terminology used in the schema, refer to the comments for an
explanation.
• The same schema is being used to represent election data from many countries, with numerous variations in
their style. or their form. of governance, so some of the terminology may be used in an unfamiliar way. Take
it to be as defined in the schema, even if this doesn’t quite match what you think a “cabinet” is in Canada,
for instance.
Two tables embed structures within them that are particularly interesting.
• Each row of the election table records information about a single election. The row includes the ID of the pre-
vious Parliamentary election and the previous EP election (using attributes previous parliament election id
and previous parliament election id). These two attributes essentially create two independent linked lists
within the table. However, it’s more complicated than that, because even a Parliamentary election has a ref-
erence to the previous EP election, and even an EP election has a reference to the previous Parliamentary
election. This diagram may help you understand the structure embedded in the election table.
The orange arrows show the linked list of parliamentary elections going back through time, and the green
arrow shows the linked list of EP elections going back through time. But we also store the references across
election types that are also stored. When you look at the whole structure, you can see that it more than just
two linked lists.
1
http://www.parlgov.org
2
• The election result table records political alliances that form. between di↵erent parties in an election. To
represent that a set of parties formed an alliance in an election, the database singles one party out (we’ll call it
the head of the alliance, and it is arbitrary which party is the head) and has all the others refer to it in their
alliance id attribute. (This is a little surprising, since alliance id sounds like a unique identifier for the
alliance rather than a reference to one of the parties in the alliance.) The other parties in the alliance refer to
the head party by storing in alliance id the id of the election result for the head party. The alliance id
value for the head party of the alliance is NULL. For example, if parties A, B, C, and D formed an alliance
in election e1, then the table could include these rows.
id election id party id alliance id seats votes
id1 e1 A NULL
id2 e1 B id1
id3 e1 C id1
id4 e1 D id1
(Or the database could have singled out a di↵erent one of these parties to be the head.)
SQL Statements
General requirements
In this section, you will write SQL statements to perform. queries. Write your SQL statement(s) for each question
in separate files. You will submit two files for each question i.
• In qi.sql file you will (a) define the table that is required for that query, (b) define the SQL query, and (c)
INSERT INTO the table you defined.
• In qi order.sql, you simply order the table you created in qi.sql based on ordering criteria defined in the
question.
In total, you will submit 14 files: q1.sql, q1 order.sql, q2.sql, q2 order.sql ...,q7.sql,q7 order.sql
For example, suppose we ask you to find the sIDS of all students with a cpga greater than 3 and sort them by
cpga. An example correct solution is the following.
• In q1.sql:
– SET search path TO parlgov;
– drop table if exists q1 cascade;
– create table q1(sID integer, cgpa integer);
– insert into q1 select sID, cgpa from student where cgpa > 3;
• And then in q1 order.sql:
– SET search path TO parlgov;
– select * from q1 order by cgpa;
Notice that we have given you the create table statements for each of the questions in the answerTablesDefi-
nition.zip file.. You are encouraged to use views to make your queries more readable. However, each file should be
entirely self-contained, and may not depend on any other files. Each file will be run separately on a fresh database
instance, and so (for example) any views you create in q1.sql will not be accessible in q5.sql. For development
and marking, you should use statements like drop table if exists q1 cascade; (and similarly for any views or
other DDL you create) in your files.
Each of your files must begin with the line SET search path TO parlgov; Failure to do so will cause
your query to raise an error, leading you to get a 0 for that question.
3
The output from your queries must exactly match the specifications in the question, including attribute names,
order and type, and the order of the tuples.
We will be testing your code in theCS Teaching Labs environmentusing PostgreSQL. It is your responsibility
to make sure your code runs in this environment before the deadline! Code which works on your machine but
not on the CS Teaching Labs will not receive credit.
The queries
IMPORTANT: In all queries other than Q7, we will define the winning party to be the party of the cabinet for
that election. Use the code below to determine the winning parties for a given election.
-- get all of the winning parties based on the cabinet
create view election_winners as
select election.id as election_id, cabinet_party.party_id
from election join cabinet
on election.id = cabinet.election_id
join cabinet_party
on cabinet.id = cabinet_party.cabinet_id
where cabinet_party.pm = true;
Listing 1: View containing election id and the winning party of that election
Write SQL queries for each of the following.
1. Longterm Political Trends
Analyze the change in party positions for parliamentary elections in the 20th and 21st centuries (note that
the year 2000 is part of the 20th century). Per metric (i.e., left-right, state-market, liberty-authority), report
the average position of the winning party (or parties, if an alliance) in all parliamentary elections held in the
century per country. For alliance governments, the party position for that alliance should be considered the
average across each member; i.e., when averaging positions over the century consider alliances as one entity.
Attribute Description Data Type
century will be ’20’ or ’21’ varchar
country name of the country varchar
left right average of the winning parties real
left right dimension in the indicated century
state market average of the winning parties real
state market dimension in the indicated century
liberty authority average of the winning parties real
liberty authority dimension in the indicated century
Order by century ascending, then country ascending
Everyone? all countries for each of two centuries
Duplicates? none
2. Electoral Systems & Alliances
Analyze the number and size of alliance governments that result from di↵erent electoral systems (parliamentary
elections only). Report the number of governments that were formed comprised of 1 party (no alliance), 2-3
parties, 4-5 parties, and 6 or more parties.
4
Attribute Description Data Type
country name of country varchar
electoral system a description of the electoral system used by the country varchar
single party number of governments formed from exactly one party int
two to three number of governments formed from 2-3 parties int
four to five number of governments formed from 4-5 parties int
six or more number of governments formed from 6 or more parties int
Order by country ascending
Everyone? all countries
Duplicates? none
3. Dissolution
Analyze the number of dissolved parliaments across countries (only parliamentary elections). All countries
elect a government every election cycle years. Report the number of elections that occurred “o↵-cycle”
(less than every election cycle years). For each country, report the number of dissolutions (i.e., o↵-cycle
elections) and the number of “on-cycle elections” (which occur election cycle years apart). Consider the
very first election recorded to be on-cycle regardless of whether the subsequent election was o↵-cycle. Also,
report the most recent o↵-cycle and on-cycle elections.
For this question, just consider the year in which an election is held. So if an election was held 01-01-2000
and the next election was held 12-31-2002 then these elections are two years apart and would be on-cycle if
and only if election cycle is two years.
Attribute Description Data Type
country name of the country varchar
num dissolutions number of parliamentary dissolutions int
which is same as number of o↵-cycle elections
most recent dissolution most recent dissolution date
num on cycle number of parliamentary elections occurring on cycle int
most recent on cycle most recent on-cycle election date
Order by country ascending
Everyone? all countries
Duplicates? none
4. Incumbency
Analyze the influence of incumbency (by leader and by party) on election results per country. Report, per
country, the total number of elections, the number of sequential elections won by the same party, and the
number of elected prime ministers who have previously been elected (not necessarily sequentially). Again,
assume that the winning party is the one that formed the cabinet for that election.
Attribute Description Data Type
country name of the country varchar
num elections total num of parliamentary elections int
num repeat party total num elections won by the party who most recently held oce int
num repeat pm total num of elected prime ministers who have already served int
as prime minister (of the same country)
Order by country ascending
Everyone? all countries
Duplicates? none
5. Close Calls
Find the parliamentary elections where the vote di↵erence was strictly less than 10% between the winning
party or alliance (based on the party in cabinet) and the runner up party or alliance (based on number of
votes). In the case of an alliance, compute the number of votes garnered by the winning party (in cabinet),
and sum the votes received by any party in an alliance with the winning party.
Attribute Description Data Type
electionId election identifier int
countryName name of a country varchar
winningParty name of winning party (alliance) varchar
closeRunnerUp party (alliance) with votes within 10% of winner varchar
Order by electionId ascending, closeRunnerUp
Everyone? Only elections with one or more close runner ups
Duplicates? An election can have more than one close runner up
6. Longest winning streak
Find the parties with the longest winning streak in parliamentary elections for each country. Report all if
there are ties.
Attribute Description Data Type
countryId id of a country int
partyName short name of party varchar
number number of consecutive wins int
Order by countryId descending, then partyName descending
Everyone? Every country
Duplicates? There can be multiple parties with the same number of consecutive wins
7. Strong Parties
A strong party is one that has served (as a winning party or part of a winning alliance) before at least one
European Parliamentary election and in every period before every European Parliamentary election. For
example, if the only European Parliamentary elections were on 01-01-2000, 08-01-2004, and 12-31-2008, then
a strong party must have won a parliamentary election strictly before 01-01-2000, sometime on or after 01-
01-2000 and strictly before 08-01-2004, and sometime on or after 08-01-2004 and strictly before 12-31-2008.
Report all strong parties along with the family (if any) of the party.
Attribute Description Data Type
partyID Party id of the strong party int
partyFamily Name of the party’s family if it exists, otherwise, null varchar
Order by partyID
Everyone? Include all strong parties
Duplicates? There should be no duplicates