Configuring the software: 

	I installed postgreSQL and the related packages more than once because 
	I was missing some of the relevant add-ins. 

	Somehow the multiple installs, I think, resulted in having a broken path to the binary file making it impossible to use psql.
	I found the correct path and reset the path manually. It was hard to figure this out because a wrong path to the binay file was in the list 
	when I typed 'path' in the command prompt but when I looked for that file, it didnt exist. I found the correct one and added it in.

	I'm blaming the multiple installs on some conf file being referenced that had the wrong passwords preventing me from logging in to the 
	database. Following the error message, I went in and deleted the file. 

	I was able to log in but when I did I got the following error message:

		WARNING: Console code page (437) differs from Windows code page (1252)
         		8-bit characters might not work correctly. See psql reference
         		page "Notes for Windows users" for details.

	I quit psql and changed the active code page to 1252. 

	After that it worked. 

Setting up the database and creating tables:
 
	I created a database in PostgreSQL called DGdiet, and started putting tables in it. 
	Here is an example of some of the SQL that I used, (basically copied from Eric's website), to put in a few of the tables:


		CREATE SEQUENCE brandedfood START WITH 0 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE CACHE 1

		CREATE TABLE tbrandedfood ( tbfid INTEGER DEFAULT nextval('brandedfood') CONSTRAINT tbfidpk PRIMARY KEY, fdcid TEXT,brandowner TEXT, gtinupc TEXT,servingsize NUMERIC,servingsizeunit TEXT );

		ALTER TABLE tbrandedfood ADD CONSTRAINT fdcidq UNIQUE (fdcid);

		\COPY tbrandedfood (fdcid, brandowner, gtinupc, servingsize, servingsizeunit) FROM 'C:\Users\aaron\OneDrive\Desktop\dgdietfiles\tBrandedFoodMay2.csv' WITH (FORMAT CSV, DELIMITER ',', HEADER TRUE);



		CREATE SEQUENCE DGinventory START WITH 0 INCREMENT BY 1 MINVALUE 0 NO MAXVALUE CACHE 1;

		CREATE TABLE tDGinventory ( dgid INTEGER DEFAULT NEXTVAL( 'DGinventory' ) CONSTRAINT DGinventorypk PRIMARY KEY,gtinupc TEXT, dgitemid TEXT, dgname TEXT, dgprice NUMERIC);

		ALTER TABLE tDGinventory ADD COLUMN fdcid TEXT;

		ALTER TABLE tDGinventory ADD CONSTRAINT fdcidfk FOREIGN KEY (fdcid) REFERENCES tbrandedfood (fdcid); 


	I ended up with four tables total. Here is a list with breif descriptions. 

		 Schema |     Name      |   Type   |  Owner
		--------+---------------+----------+----------
 		 public | tbrandedfood  | table    | postgres         all the foods in the USDA database            
		 public | tdginventory  | table    | postgres         the foods that Paula looked up at Dollar General
 		 public | tfoodnutrient | table    | postgres         a table with millions of rows, one for each nutrient in every food in tbrandedfood 
		 public | tnutrient     | table    | postgres         a lookup table with information about each nutrient


Querying the database: 

	The ultimate goal here was to combine data from all the tables to create and export a new table that had nutritional, serving size and pricing information on each food available at Dollar General
	so that I could optimize cost and nutrition using linear programming. I didn't actually ever make exactly the table that I wanted due to my limited fluency in SQL and a problem with one of the tables, tfoodnutrient, 
	which appears to be missing some information. 

	I did some queries to play around and made a few new tables. Here is an examples of the SQL:

		SELECT tdginventory.dgname, tnutrient.name, tfoodnutrient.amount 
		FROM tdginventory CROSS JOIN tfoodnutrient JOIN tnutrient 
		ON tdginventory.fdcid=tfoodnutrient.fdcid 
		AND tfoodnutrient.nutrientid=tnutrient.nutrientid;

			This was supposed to make a table that listed every nutrient and its corresponding amount for each food at the dollar store. It did - sort of - there were a few nutrients missing from each food, 
			and they weren't the same ones. I don't know whether I messed up the join somehow or if the tfoodnutrient table is messed up somehow. 
	
	The table that I want to build, (something like it is in the file QuerySpec-1-May11), should have a field for food item, fields for amounts of each nutrient, and a field for the cost per serving. 
	I didn't know how to get something like that in a single query, so I decided to create a table and join one field at a time, slowly but surely adding in fields until I had the table I wanted. 

	So I created some new tables. Here is the SQL:

		CREATE TABLE tdgchart AS 
		SELECT tbrandedfood.fdcid, tdginventory.dgitemid, tdginventory.dgname, tbrandedfood.servingsize, tdginventory.totalg, tbrandedfood.servingsizeunit, tdginventory.dgprice 
		FROM tbrandedfood INNER JOIN tdginventory ON tdginventory.fdcid=tbrandedfood.fdcid;

		CREATE TABLE tprotein AS SELECT tdginventory.fdcid, tfoodnutrient.amount FROM tfoodnutrient RIGHT OUTER JOIN tdginventory ON
		tfoodnutrient.nutrientid='1003' AND tdginventory.fdcid=tfoodnutrient.fdcid;

		ALTER TABLE tprotein RENAME COLUMN amount TO gramsprotein;

			Here's where I ran into another problem. The table tdgchart came out exactly how I wanted it to, but tprotein had <null> for 98 of the rows. I don't see how that is possible unless the biggest 
			table in my database, tfoodnutrient, really is missing info on the nutrients in some or all of the foods. 
	
	I made a few analogous tables for other nutrients and the same problem showed up. 

	I did the following query and it turns out that for the nutrients that I made tables for, the same 97 rows came back <null>: 

		SELECT tcarb.fdcid, tprotein.gramsprotein, tcarb.gramscarb, tfat.gramsfat, tsodium.mgsodium 
		FROM tcarb, tprotein, tfat, tsodium WHERE tprotein.fdcid=tcarb.fdcid AND tprotein.fdcid=tfat.fdcid AND tprotein.fdcid=tsodium.fdcid;

	I turned the above query into a table called tmacros, and created yet another table, tdgmacronutrients, by joining it to my tdgchart table getting rid of the rows that didn't have any nutrient information. 
	It would be good enough to export, modify in Excel and solve a diet problem. 


		SELECT* FROM tdgchart INNER JOIN tmacros ON tdgchart.fdcid=tmacros.fdcid AND tmacros IS NOT NULL;

		ALTER TABLE tmacros RENAME COLUMN fdcid TO fuck;	

		CREATE TABLE tdgmacronutrients AS SELECT* FROM tdgchart INNER JOIN tmacros 
		ON tdgchart.fdcid=tmacros.fuck AND tmacros IS NOT NULL;

		ALTER TABLE tdgmacronutrients DROP COLUMN fuck;

	Here is a sample of what my table looks like:

		DGdiet=# TABLE tdgmacronutrients LIMIT 10;
		 fdcid  | dgitemid |                            dgname                             | servingsize |  totalg  | servingsizeunit | dgprice | gramsprotein | gramscarb | gramsfat | mgsodium
		--------+----------+---------------------------------------------------------------+-------------+----------+-----------------+---------+--------------+-----------+----------+----------
		 428488 | 791801   | Clover Valley Dry Roasted Salted Peanuts                      |          28 |    453.6 | g               |     2.5 |        28.57 |     14.29 |    53.57 |      321
		 414792 | 1887401  | Dole Mandarin Oranges In 100% Juice 4 Cups                    |         119 |    453.6 | g               |     2.5 |         0.84 |     15.13 |        0 |        4
		 414749 | 12396302 | Dole Canned Pineapple Chunks In 100% Juice                    |         122 |      567 | g               |     1.5 |            0 |     14.75 |        0 |        0
		 413111 | 829201   | Armour Potted Meat                                            |          62 |    85.05 | g               |     0.5 |         12.9 |         0 |    14.52 |     1000
		 410766 | 10319401 | Knorr Tomato Based Pasta Soup                                 |          25 |   99.225 | g               |    0.65 |            8 |        68 |        4 |     3320
		 410139 | 821501   | Nissin Top Ramen Chicken 5pk-3oz                              |          42 |   425.25 | g               |       1 |         11.9 |      61.9 |    16.67 |     2167
		 410135 | 821901   | Nissin Bowl Noodles Hot & Spicy With Shrimp Ramen Noodle Soup |          46 |  92.7045 | g               |       1 |          8.7 |     56.52 |    26.09 |     1652
		 409513 | 830102   | Armour Beef Stew                                              |         255 |      567 | g               |    2.25 |         3.14 |      8.24 |     4.71 |      506
		 409438 | 830702   | Armour Vienna Sausage Chicken                                 |          60 |   130.41 | g               |    0.55 |        11.67 |      1.67 |    16.67 |      883
		 409410 | 15297002 | Armour Sausages Vienna 6pk                                    |          60 | 134.6625 | g               |    2.95 |        11.67 |      1.67 |    16.67 |     1050
		(10 rows)

	I would have to get a column with cost per serving, modify any rows where the serving size unit is not grams so that it is given in grams, and then I would have a table that I could solve the diet problem on.
	I exported the table and called it 'tdgforlp.csv.' I could do the calculations needed to get in ready, such as making a cost per serving field and estimating mass for the volume based servings click and drag style in Excel. 
	Then I could solve the diet problem. 
	Solving this problem, however, wouldn't actually answer my original question, "What is the cheapest healthy diet that you can buy at Dollar General?" It would answer a different, less elegant question, 
	"What is a cheap diet that has the right balance of protein, carbs, and fat, and won't poison you with sodium that you can purchace at Dollar General?
	We can't know what the cheapest healthy diet is because that darned table is missing information on about 97 foods, thanks to that USDA table being messed up.
	I was out of time as well. I wasn't going to be able to modify the table putting in the calculated values and constraints in Excel in the 24 hours I had left. 
	I thought it was necessary to solve a diet problem using linear programming and show it to the class so I decided on a toy example using some foods that I had in my pantry.

Solving the LP problem:

	I grabbed 6 foods out of my pantry and refigerator: eggs, matzos, wakame, peanut butter, milk, and a can of navy beans. I checked the cost of each from receipts and price tags,
	and I took the macronutrients, fat, carb, protein, and sodium from their nutrition labels. This all went into an Excel workbook. I added columns for the max and min constraints on each nutrient, a decision variable row, 
	and calculated values for the total amounts of each nutrient and the number of calories. I opened the solver add in and pasted in the relevant information ran minimization using the symplex method
	and immediately got a solution:

	Approximately 6 mazos, 2 cans of beans, and 6 Tbs of peanut butter per day should provide a reasonable diet with about 2000 calories costing $2.50 daily.