CREATE TABLE donor ( donor_id SERIAL PRIMARY KEY, donor_name VARCHAR(500) NOT NULL, donor_email VARCHAR(200) NOT NULL, donor_address VARCHAR(200) NOT NULL, donor_city VARCHAR(100) NOT NULL, donor_state VARCHAR(20) NOT NULL, donor_zip VARCHAR(10) NOT NULL );

CREATE TABLE fund ( fund_id SERIAL PRIMARY KEY, fund_name VARCHAR(50) NOT NULL );

CREATE TABLE gift ( gift_id SERIAL PRIMARY KEY, donor_id INTEGER NOT NULL REFERENCES donor (donor_id), gift_date DATE NOT NULL );

CREATE TABLE gift_fund_allocation ( gf_alloc_id SERIAL PRIMARY KEY, gift_id INTEGER NOT NULL REFERENCES gift (gift_id), fund_id INTEGER NOT NULL REFERENCES fund (fund_id), amount DECIMAL NOT NULL );

-- BEGIN TABLE donor -- Donor data generated with Mockeroo insert into donor (donor_name, donor_email, donor_address, donor_city, donor_state, donor_zip) values ('George Coleman', 'gcoleman0@narod.ru', '80042 Manley Lane', 'Greenfield', 'MA', '48913-8689'); insert into donor (donor_name, donor_email, donor_address, donor_city, donor_state, donor_zip) values ('Todd Burton', 'tburton1@xing.com', '35476 Lawn Pass', 'Huntington', 'IN', '51288'); insert into donor (donor_name, donor_email, donor_address, donor_city, donor_state, donor_zip) values ('Melissa Gibson', 'mgibson2@dailymail.co.uk', '43 Spohn Terrace', 'Modesto', 'CA', '17566-6735'); insert into donor (donor_name, donor_email, donor_address, donor_city, donor_state, donor_zip) values ('Albert Grant', 'agrant3@prnewswire.com', '2572 Scoville Junction', 'East Setauket', 'NY', '82644-0087'); insert into donor (donor_name, donor_email, donor_address, donor_city, donor_state, donor_zip) values ('Howard Willis', 'hwillis4@mapquest.com', '05452 Mallard Place', 'Tinton Falls', 'NJ', '72760-1745');

-- BEGIN TABLE fund INSERT INTO fund (fund_name) VALUES ('General / Operations'); INSERT INTO fund (fund_name) VALUES ('Cat Sheltering'); INSERT INTO fund (fund_name) VALUES ('Dog Sheltering'); INSERT INTO fund (fund_name) VALUES ('Veterinary Assistance'); INSERT INTO fund (fund_name) VALUES ('Lobbying');

-- BEGIN TABLE gift INSERT INTO gift (gift_id, donor_id, gift_date) VALUES (935, 1, '2009-10-06'); INSERT INTO gift (gift_id, donor_id, gift_date) VALUES (936, 1, '2010-01-19'); INSERT INTO gift (gift_id, donor_id, gift_date) VALUES (937, 1, '2005-06-01'); INSERT INTO gift (gift_id, donor_id, gift_date) VALUES (938, 1, '2013-03-17'); INSERT INTO gift (gift_id, donor_id, gift_date) VALUES (939, 1, '2008-01-17');

-- BEGIN TABLE gift_fund_allocation INSERT INTO gift_fund_allocation (gf_alloc_id, gift_id, fund_id, amount) VALUES (2045, 935, 1, 204); INSERT INTO gift_fund_allocation (gf_alloc_id, gift_id, fund_id, amount) VALUES (2046, 936, 1, 845); INSERT INTO gift_fund_allocation (gf_alloc_id, gift_id, fund_id, amount) VALUES (2047, 936, 2, 966); INSERT INTO gift_fund_allocation (gf_alloc_id, gift_id, fund_id, amount) VALUES (2048, 937, 1, 50); INSERT INTO gift_fund_allocation (gf_alloc_id, gift_id, fund_id, amount) VALUES (2049, 937, 2, 45);

Summary In this assignment, you will set up your PostgreSQL database, reverse-engineer a logical schema from some SQL DDL statements, and write queries to retrieve information. This homework will use the data in the SQL file charity.sql. Run this file as an SQL script in to import the data (it will create a database called 'charity'). Submit the final results of your assignment as a single PDF file; this is probably easiest to do by copying SQL queries and results into a Word or LibreOffice document. Part 3 (40 points): SQL Queries Write SQL queries to retrieve the following data. Include the query and the first 5 rows of output in your report. 6. 7. The total amount received in each month of 2007. For donor 35, the gifts they gave in 2007 with the date and total amount of each gift. 8. For donor 35, the funds to which they contributed in 2007 with the total amount contributed to each fund. 9. The names and full addresses of the top 5 donors in 2010, with their total donations, in decreasing order of total donations. 10. The list of Dog Lovers: everyone who has donated to Dog Sheltering. Do not include duplicates. Tip: you can convert the gift date into a year with the SQL function EXTRACT(YEAR FROM gift_date). In this assignment, 'total amount' means the total dollars given. Summary In this assignment, you will set up your PostgreSQL database, reverse-engineer a logical schema from some SQL DDL statements, and write queries to retrieve information. This homework will use the data in the SQL file charity.sql. Run this file as an SQL script in to import the data (it will create a database called 'charity'). Submit the final results of your assignment as a single PDF file; this is probably easiest to do by copying SQL queries and results into a Word or LibreOffice document. Part 3 (40 points): SQL Queries Write SQL queries to retrieve the following data. Include the query and the first 5 rows of output in your report. 6. 7. The total amount received in each month of 2007. For donor 35, the gifts they gave in 2007 with the date and total amount of each gift. 8. For donor 35, the funds to which they contributed in 2007 with the total amount contributed to each fund. 9. The names and full addresses of the top 5 donors in 2010, with their total donations, in decreasing order of total donations. 10. The list of Dog Lovers: everyone who has donated to Dog Sheltering. Do not include duplicates. Tip: you can convert the gift date into a year with the SQL function EXTRACT(YEAR FROM gift_date). In this assignment, 'total amount' means the total dollars given