-- 2. Split the campaign data into three tables.

----------------------------------------------------------------------------

-- Part 2 - split the campaign data into three tables.

----------------------------------------------------------------------------

-- Please read all of the following:

-- The campaign contribution data is contained in a single big table,

-- but this approach leads to much redundancy. For example, Hillary

-- Clinton's name appears thousands of times in the data. Similarly,

-- if a contributor makes multiple contributions, there are multiple

-- rows of the table with the contributor's details, such as address,

-- occupation, etc.

-- The following relation schemas show an alternative way to organize

-- the data that avoids this kind of redundancy. For example, in the

-- candidate table we give the candidate names and ids, with only one

-- row in the table for each candidate.

-- The connection between the attributes of the three new tables and

-- the attributes of the campaign table are shown in comments in the

-- table create statements below. For example, attribute name of

-- table contributor comes from field contbr_nm of the campaign table.

create table candidate (

cand_id varchar(12) primary key, -- cand_id

name varchar(40) -- cand_nm

);

create table contributor (

contbr_id integer primary key,

name varchar(40), -- contbr_nm

city varchar(40), -- contbr_city

state varchar(40), -- contbr_st

zip varchar(20), -- contbr_zip

employer varchar(60), -- contbr_employer

occupation varchar(40) -- contbr_occupation

);

create table contribution (

contb_id integer primary key,

cand_id varchar(12), -- cand_id

contbr_id varchar(12), -- contbr_id

amount numeric(6,2), -- contb_receipt_amt

date varchar(20), -- contb_receipt_dt

election_type varchar(20), -- election_tp

tran_id varchar(20), -- tran_id

foreign key (cand_id) references candidate,

foreign key (contbr_id) references contributor

);

-- 9. Write an insert statement to fill the contribution table.

-- The contribution table, after it is filled, should contain one row

-- for each row of the campaign table.

-- Hint: You do not need to suppy a value for the contb_id field; SQLite will

-- supply one for you automatically.

-- Hint: The select statement inside your insert statement will need to

-- mention the contributor table, since that is where the contbr_id is defined.

insert into contribution (cand_id, amount, date, election_type, tran_id)

select cand_id, contb_receipt_amt, contb_receipt_dt, election_tp, tran_id

from campaign;