数据库代写

Schema

In this assignment, we will perform some analytics on real data from one hundred years of elections. The data comes from ParlGov1, 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.

(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 the CS Teaching Labs environment using 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.

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.

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

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.

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.

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.

5

6. Longest winning streak
Find the parties with the longest winning streak in parliamentary elections for each country. Report all if

there are ties.

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.

6