
CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Member_Name" TEXT, 
	"Party_ID" TEXT, 
	"In_office" TEXT, 
	PRIMARY KEY ("Member_ID"), 
	FOREIGN KEY("Party_ID") REFERENCES party ("Party_ID")
)


CREATE TABLE party (
	"Party_ID" INTEGER, 
	"Minister" TEXT, 
	"Took_office" TEXT, 
	"Left_office" TEXT, 
	"Region_ID" INTEGER, 
	"Party_name" TEXT, 
	PRIMARY KEY ("Party_ID"), 
	FOREIGN KEY("Region_ID") REFERENCES region ("Region_ID")
)


CREATE TABLE party_events (
	"Event_ID" INTEGER, 
	"Event_Name" TEXT, 
	"Party_ID" INTEGER, 
	"Member_in_charge_ID" INTEGER, 
	PRIMARY KEY ("Event_ID"), 
	FOREIGN KEY("Member_in_charge_ID") REFERENCES member ("Member_ID"), 
	FOREIGN KEY("Party_ID") REFERENCES party ("Party_ID")
)


CREATE TABLE region (
	"Region_ID" INTEGER, 
	"Region_name" TEXT, 
	"Date" TEXT, 
	"Label" TEXT, 
	"Format" TEXT, 
	"Catalogue" TEXT, 
	PRIMARY KEY ("Region_ID")
)

CREATE TABLE member (
	"Member_ID" INTEGER, 
	"Member_Name" TEXT, 
	"Party_ID" TEXT, 
	"In_office" TEXT, 
	PRIMARY KEY ("Member_ID"), 
	FOREIGN KEY("Party_ID") REFERENCES party ("Party_ID")
)

/*
2 rows from member table:
Member_ID	Member_Name	Party_ID	In_office
1	Hon Tony Abbott	3	1994–present
2	Hon Dick Adams	2	1993–2013
*/


CREATE TABLE party (
	"Party_ID" INTEGER, 
	"Minister" TEXT, 
	"Took_office" TEXT, 
	"Left_office" TEXT, 
	"Region_ID" INTEGER, 
	"Party_name" TEXT, 
	PRIMARY KEY ("Party_ID"), 
	FOREIGN KEY("Region_ID") REFERENCES region ("Region_ID")
)

/*
2 rows from party table:
Party_ID	Minister	Took_office	Left_office	Region_ID	Party_name
1	Dr. Kwame Nkrumah (MP)	1957	1958	1	Convention Peoples Party
2	Kojo Botsio (MP)	1958	1959	2	Progress Party
*/


CREATE TABLE party_events (
	"Event_ID" INTEGER, 
	"Event_Name" TEXT, 
	"Party_ID" INTEGER, 
	"Member_in_charge_ID" INTEGER, 
	PRIMARY KEY ("Event_ID"), 
	FOREIGN KEY("Member_in_charge_ID") REFERENCES member ("Member_ID"), 
	FOREIGN KEY("Party_ID") REFERENCES party ("Party_ID")
)

/*
2 rows from party_events table:
Event_ID	Event_Name	Party_ID	Member_in_charge_ID
1	Annaual Meeting	1	4
2	Conference	1	12
*/


CREATE TABLE region (
	"Region_ID" INTEGER, 
	"Region_name" TEXT, 
	"Date" TEXT, 
	"Label" TEXT, 
	"Format" TEXT, 
	"Catalogue" TEXT, 
	PRIMARY KEY ("Region_ID")
)

/*
2 rows from region table:
Region_ID	Region_name	Date	Label	Format	Catalogue
1	United Kingdom	1 July 2002	Parlophone	CD	540 3622
2	United Kingdom	1 July 2002	Parlophone	2× LP	539 9821
*/
Which ministers are not a part of the Progress Party?
SELECT minister FROM party WHERE party_name != 'Progress Party'
[{'Minister': 'Dr. Kwame Nkrumah (MP)'}, {'Minister': 'Ebenezer Ako-Adjei (MP)'}, {'Minister': 'Imoru Egala (MP)'}, {'Minister': 'Ebenezer Ako-Adjei (MP)'}]