USE gc200380935;

DROP TABLE IF EXISTS donations;
CREATE TABLE donations (

first_name VARCHAR(20) NOT NULL,
last_name VARCHAR(20) NOT NULL,
recipient VARCHAR(255) NOT NULL );

INSERT INTO donations (first_name, last_name, recipient)
VALUES ('Eric', 'Smith', 'Barrie, Liberal Party');

INSERT INTO donations (first_name, last_name, recipient)
VALUES ('Alicia', 'Jones', 'Simcoe North, Conservative Party');

INSERT INTO donations (first_name, last_name, recipient)
VALUES ('Sue', 'Wilson', 'London South, NDP');

/*View the donations table*/
select * FROM donations;

/*Is this in 1NF (first normal form)?
 No, it needs a unqiue identifier (key) for each row
 Let add a column called donorID*/
 ALTER TABLE donations
ADD COLUMN donorID INT NOT NULL auto_increment primary KEY;
/*Let's make the donorID the first column*/
ALTER TABLE donations
MODIFY COLUMN donorID INT FIRST;

/*Let's move the recipient column to be the 2nd column*/
ALTER TABLE donations
MODIFY COLUMN recipient VARCHAR(255) AFTER donorID;



select * FROM donations;
/*The riding and political parties are currently in the same column, which is not atomic.  As such, we need to
  add 2 columns*/



alter table donations
add column riding VARCHAR(25) AFTER recipient;

alter table donations
add column party VARCHAR(25) AFTER riding;


  select * FROM donations;

/*We need to populate these new fields, let's write some queries to do that*/
select recipient , INSTR(recipient, ',') from donations;

/*OR try substring index*/

/*Now let's update the riding column with this information*/

/*Create a query to give us the political party*/

/*We've tested our query, let's update the table*/

/*We now need to DROP the recipient column*/

/*We are now in first normal form!  Also known as 1NF*/