n7PdJ9Nc

1USE gc200380935;
2
3DROP TABLE IF EXISTS donations;
4CREATE TABLE donations (
5
6first_name VARCHAR(20) NOT NULL,
7last_name VARCHAR(20) NOT NULL,
8recipient VARCHAR(255) NOT NULL );
9
10INSERT INTO donations (first_name, last_name, recipient)
11VALUES ('Eric', 'Smith', 'Barrie, Liberal Party');
12
13INSERT INTO donations (first_name, last_name, recipient)
14VALUES ('Alicia', 'Jones', 'Simcoe North, Conservative Party');
15
16INSERT INTO donations (first_name, last_name, recipient)
17VALUES ('Sue', 'Wilson', 'London South, NDP');
18
19/*View the donations table*/
20select * FROM donations;
21
22/*Is this in 1NF (first normal form)? 
23 No, it needs a unqiue identifier (key) for each row
24 Let add a column called donorID*/
25 ALTER TABLE donations
26ADD COLUMN donorID INT NOT NULL auto_increment primary KEY;
27/*Let's make the donorID the first column*/
28ALTER TABLE donations 
29MODIFY COLUMN donorID INT FIRST;
30
31/*Let's move the recipient column to be the 2nd column*/
32ALTER TABLE donations 
33MODIFY COLUMN recipient VARCHAR(255) AFTER donorID;
34
35
36
37select * FROM donations;
38/*The riding and political parties are currently in the same column, which is not atomic.  As such, we need to
39  add 2 columns*/
40  
41  
42  
43alter table donations
44add column riding VARCHAR(25) AFTER recipient;
45
46alter table donations
47add column party VARCHAR(25) AFTER riding;  
48  
49  
50  select * FROM donations;
51  
52/*We need to populate these new fields, let's write some queries to do that*/
53select recipient , INSTR(recipient, ',') from donations;
54
55select LEFT(recipient, instr(recipient,',')-1) from donations;
56
57/*OR try substring index*/
58select substr(recipient,1,instr(recipient,',')-1) from donations;
59
60/*Now let's update the riding column with this information*/
61UPDATE donations 
62SET riding = left(recipient, instr(recipient,',')-1);
63
64select * from donations;
65
66/*Create a query to give us the political party*/
67select substr(recipient, instr(recipient,',')+2) from donations;
68
69/*We've tested our query, let's update the table*/
70update donations 
71set party = substr(recipient, instr(recipient,',')+2);
72
73
74
75
76
77
78/*We now need to DROP the recipient column*/
79alter table donations
80drop column recipient;
81
82/*We are now in first normal form!  Also known as 1NF*/
83select* from donations;