
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
*/
Show all party names and their region names.
SELECT T1.party_name ,  T2.region_name FROM party AS T1 JOIN region AS T2 ON T1.region_id  =  T2.region_id
[{'Party_name': 'Convention Peoples Party', 'Region_name': 'United Kingdom'}, {'Party_name': 'Progress Party', 'Region_name': 'United Kingdom'}, {'Party_name': '3', 'Region_name': 'Japan'}, {'Party_name': 'Convention Union Party', 'Region_name': 'United States'}, {'Party_name': 'Sinefine Party', 'Region_name': 'United States'}]