Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Individual Assignment III

ISTM 4212 Section 10

Spring 2023

Write one Jupyter notebook with your solutions to all the following problems. Document each step of your process in a reproducible manner, including downloads and other file changes. When you are finished, your instructors and anyone else should be able to run your notebook from start to finish without error.

Add text notes on your process as appropriate, documenting any assumptions and explaining key decisions you make along the way. Use markdown cells for this text, formatting your notes so they are easy to read.

Be sure to answer each question directly and precisely, using the data to justify your answers, and showing all of your work along the way.

This is an individual project.

As always, you are welcome to seek and give assistance to others who might become stuck along the way. Please acknowledge any assistance you receive. At the same time, each group must perform and submit its own work, in accordance with the GWU Code of Academic Integrity.

This assignment is due on Wed, April 5th, at 4 pm. Submit your Jupyter notebook file to Blackboard with name Assignment_III__

No need to provide a publicly available data files in your final answer (e.g. those obtained using wget)

Organization and code comments (5 points)

Problem 1 – Creating your Database Objects (25 points)

Using the schema diagram provided to you with this assignment, create the physical tables for all entities and their relationships. Make sure the tables contain all primary, foreign, and unique keys as well as any default, and check constraints (if any). The tables must also Indicate the null constraint for all attributes

5 tables: 5 points each – Table creation 2 points, keys 1 points, constraints 1 point and 1 point for adding comments on the tables and attributes.

Problem 2 – Constructing your Database – Data Loading   (15 points)

Once you have created the tables and their constraints, construct (i.e., populate) the tables in bulk using the data files for year 2023-24.

Problem 3 – checking your data   (5 points)

For all tables, find the total number of rows loaded. Check your answer (using select queries) against the original text files (using Linux or csvkit commands)

Problem 4 – Change Management   (20 points)

Your sponsor asked you to manipulate the original candidate’s name in candidate master table. You’ll need to

(1) Write a query that uses the built-in functions ‘position’ and ‘substr’ (See table 9-6 from the link https://www.postgresql.org/docs/9.1/functions-string.html)  to split the candidate’s name composite attribute (CAND_NAME) in “candidate master” table into two parts: one to hold the last name and the other to hold first name(s). For example: “Obaidi, Ali A” name would split into “Obaidi” as last name and “Ali A” as first names.

(2) Write Alter tables statements for table “candidate master” to add two new attributes: a) first_names and b) last name. Both attributes have maximum of 30 characters.

(3)  Write an update SQL statement to construct (i.e. populate) the two new attributes using the query function you created in (1)

(4) Write an alter table statement to drop the cand_name attribute from the candidate_master table

Problem 5 – Basic Data analysis   (30 points)

(1) Write a query that list the candidate’s name, their party affiliations, candidate status and their principal campaign committee. Only include candidates for election year 2024 and have a mailing address state of “DC.”

(2) Write a query that list the committee’s name, type, and party for the top 10 committees in terms of the number of candidates they are associated with.

(3)  Write a query that list the committee’s name, type, and party for the top 10 committees in terms of their total contributions from individuals.

(4) Write a query that list the top 10 employers in terms of their individual contribution. Indicate the employer’s name, total contribution, and the total number of distinct committees they contributed to