
CREATE TABLE "Addresses" (
	address_id INTEGER, 
	line_1_number_building VARCHAR(80), 
	town_city VARCHAR(50), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (address_id)
)


CREATE TABLE "Forms" (
	form_id INTEGER, 
	form_type_code VARCHAR(15) NOT NULL, 
	service_id INTEGER, 
	form_number VARCHAR(50), 
	form_name VARCHAR(80), 
	form_description VARCHAR(255), 
	PRIMARY KEY (form_id), 
	FOREIGN KEY(service_id) REFERENCES "Services" (service_id)
)


CREATE TABLE "Individuals" (
	individual_id INTEGER, 
	individual_first_name VARCHAR(80), 
	individual_middle_name VARCHAR(80), 
	inidividual_phone VARCHAR(80), 
	individual_email VARCHAR(80), 
	individual_address VARCHAR(255), 
	individual_last_name VARCHAR(80), 
	PRIMARY KEY (individual_id)
)


CREATE TABLE "Organization_Contact_Individuals" (
	individual_id INTEGER NOT NULL, 
	organization_id INTEGER NOT NULL, 
	date_contact_from DATETIME NOT NULL, 
	date_contact_to DATETIME, 
	PRIMARY KEY (individual_id, organization_id), 
	FOREIGN KEY(individual_id) REFERENCES "Individuals" (individual_id), 
	FOREIGN KEY(organization_id) REFERENCES "Organizations" (organization_id)
)


CREATE TABLE "Organizations" (
	organization_id INTEGER, 
	date_formed DATETIME, 
	organization_name VARCHAR(255), 
	uk_vat_number VARCHAR(20), 
	PRIMARY KEY (organization_id)
)


CREATE TABLE "Parties" (
	party_id INTEGER, 
	payment_method_code VARCHAR(15) NOT NULL, 
	party_phone VARCHAR(80), 
	party_email VARCHAR(80), 
	PRIMARY KEY (party_id)
)


CREATE TABLE "Party_Addresses" (
	party_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_address_from DATETIME NOT NULL, 
	address_type_code VARCHAR(15) NOT NULL, 
	date_address_to DATETIME, 
	PRIMARY KEY (party_id, address_id), 
	FOREIGN KEY(party_id) REFERENCES "Parties" (party_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Party_Forms" (
	party_id INTEGER NOT NULL, 
	form_id INTEGER NOT NULL, 
	date_completion_started DATETIME NOT NULL, 
	form_status_code VARCHAR(15) NOT NULL, 
	date_fully_completed DATETIME, 
	PRIMARY KEY (party_id, form_id), 
	FOREIGN KEY(form_id) REFERENCES "Forms" (form_id), 
	FOREIGN KEY(party_id) REFERENCES "Parties" (party_id)
)


CREATE TABLE "Party_Services" (
	booking_id INTEGER NOT NULL, 
	customer_id INTEGER NOT NULL, 
	service_id INTEGER NOT NULL, 
	service_datetime DATETIME NOT NULL, 
	booking_made_date DATETIME, 
	FOREIGN KEY(customer_id) REFERENCES "Parties" (party_id), 
	FOREIGN KEY(service_id) REFERENCES "Services" (service_id)
)


CREATE TABLE "Services" (
	service_id INTEGER, 
	service_type_code VARCHAR(15) NOT NULL, 
	service_name VARCHAR(80), 
	service_descriptio VARCHAR(255), 
	PRIMARY KEY (service_id)
)

CREATE TABLE "Addresses" (
	address_id INTEGER, 
	line_1_number_building VARCHAR(80), 
	town_city VARCHAR(50), 
	zip_postcode VARCHAR(20), 
	state_province_county VARCHAR(50), 
	country VARCHAR(50), 
	PRIMARY KEY (address_id)
)

/*
2 rows from Addresses table:
address_id	line_1_number_building	town_city	zip_postcode	state_province_county	country
1	25518 Ortiz Centers	West Stacy	193	NorthCarolina	USA
2	033 Stracke Parkways	Lake Meaghan	227	Colorado	USA
*/


CREATE TABLE "Forms" (
	form_id INTEGER, 
	form_type_code VARCHAR(15) NOT NULL, 
	service_id INTEGER, 
	form_number VARCHAR(50), 
	form_name VARCHAR(80), 
	form_description VARCHAR(255), 
	PRIMARY KEY (form_id), 
	FOREIGN KEY(service_id) REFERENCES "Services" (service_id)
)

/*
2 rows from Forms table:
form_id	form_type_code	service_id	form_number	form_name	form_description
1	Basic	13	8069	SSN Application	Form for SSN Application
2	Complex	9	2675	Marriage Certificate	Marriage Certificate
*/


CREATE TABLE "Individuals" (
	individual_id INTEGER, 
	individual_first_name VARCHAR(80), 
	individual_middle_name VARCHAR(80), 
	inidividual_phone VARCHAR(80), 
	individual_email VARCHAR(80), 
	individual_address VARCHAR(255), 
	individual_last_name VARCHAR(80), 
	PRIMARY KEY (individual_id)
)

/*
2 rows from Individuals table:
individual_id	individual_first_name	individual_middle_name	inidividual_phone	individual_email	individual_address	individual_last_name
1	Oscar	Hosea	1-925-696-5232	amie.okuneva@example.org	6956 Lia Plaza	Maggio
2	Geovanny	Antonia	075.012.6775x409	jamey.effertz@example.net	69578 Baylee Prairie	Kerluke
*/


CREATE TABLE "Organization_Contact_Individuals" (
	individual_id INTEGER NOT NULL, 
	organization_id INTEGER NOT NULL, 
	date_contact_from DATETIME NOT NULL, 
	date_contact_to DATETIME, 
	PRIMARY KEY (individual_id, organization_id), 
	FOREIGN KEY(individual_id) REFERENCES "Individuals" (individual_id), 
	FOREIGN KEY(organization_id) REFERENCES "Organizations" (organization_id)
)

/*
2 rows from Organization_Contact_Individuals table:
individual_id	organization_id	date_contact_from	date_contact_to
13	1	2016-08-16 22:09:11	2018-03-25 10:27:18
11	1	2017-03-02 00:00:16	2018-03-06 05:39:43
*/


CREATE TABLE "Organizations" (
	organization_id INTEGER, 
	date_formed DATETIME, 
	organization_name VARCHAR(255), 
	uk_vat_number VARCHAR(20), 
	PRIMARY KEY (organization_id)
)

/*
2 rows from Organizations table:
organization_id	date_formed	organization_name	uk_vat_number
1	2016-08-24 23:52:48	Labour Party	2157
2	2016-10-01 12:42:01	Plaid Cymru	7459
*/


CREATE TABLE "Parties" (
	party_id INTEGER, 
	payment_method_code VARCHAR(15) NOT NULL, 
	party_phone VARCHAR(80), 
	party_email VARCHAR(80), 
	PRIMARY KEY (party_id)
)

/*
2 rows from Parties table:
party_id	payment_method_code	party_phone	party_email
1	Cheque	05374656172	enrico09@example.com
2	Credit Card	1-525-947-7867x51521	brakus.aliya@example.com
*/


CREATE TABLE "Party_Addresses" (
	party_id INTEGER NOT NULL, 
	address_id INTEGER NOT NULL, 
	date_address_from DATETIME NOT NULL, 
	address_type_code VARCHAR(15) NOT NULL, 
	date_address_to DATETIME, 
	PRIMARY KEY (party_id, address_id), 
	FOREIGN KEY(party_id) REFERENCES "Parties" (party_id), 
	FOREIGN KEY(address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Party_Addresses table:
party_id	address_id	date_address_from	address_type_code	date_address_to
10	8	2016-04-08 22:40:02	Residence	2018-02-28 23:14:41
15	5	2016-05-20 23:22:06	Billing	2018-01-29 16:48:01
*/


CREATE TABLE "Party_Forms" (
	party_id INTEGER NOT NULL, 
	form_id INTEGER NOT NULL, 
	date_completion_started DATETIME NOT NULL, 
	form_status_code VARCHAR(15) NOT NULL, 
	date_fully_completed DATETIME, 
	PRIMARY KEY (party_id, form_id), 
	FOREIGN KEY(form_id) REFERENCES "Forms" (form_id), 
	FOREIGN KEY(party_id) REFERENCES "Parties" (party_id)
)

/*
2 rows from Party_Forms table:
party_id	form_id	date_completion_started	form_status_code	date_fully_completed
2	4	2017-12-17 11:29:47	Completed	2018-02-11 16:46:10
15	4	2017-12-16 15:25:48	Partially Completed	2018-02-18 16:09:04
*/


CREATE TABLE "Party_Services" (
	booking_id INTEGER NOT NULL, 
	customer_id INTEGER NOT NULL, 
	service_id INTEGER NOT NULL, 
	service_datetime DATETIME NOT NULL, 
	booking_made_date DATETIME, 
	FOREIGN KEY(customer_id) REFERENCES "Parties" (party_id), 
	FOREIGN KEY(service_id) REFERENCES "Services" (service_id)
)

/*
2 rows from Party_Services table:
booking_id	customer_id	service_id	service_datetime	booking_made_date
1	10	12	2018-03-10 22:43:12	2018-03-23 23:56:51
2	14	11	2018-03-05 15:06:23	2018-03-25 11:08:29
*/


CREATE TABLE "Services" (
	service_id INTEGER, 
	service_type_code VARCHAR(15) NOT NULL, 
	service_name VARCHAR(80), 
	service_descriptio VARCHAR(255), 
	PRIMARY KEY (service_id)
)

/*
2 rows from Services table:
service_id	service_type_code	service_name	service_descriptio
1	Education	Education	Education
2	Welfare	Health	Welfare
*/
Find the emails of parties with the most popular party form.
SELECT t1.party_email FROM parties AS t1 JOIN party_forms AS t2 ON t1.party_id  =  t2.party_id WHERE t2.form_id  =  (SELECT form_id FROM party_forms GROUP BY form_id ORDER BY count(*) DESC LIMIT 1)
[{'party_email': 'alanna.boehm@example.net'}, {'party_email': 'lucinda83@example.org'}, {'party_email': 'erick.mills@example.com'}]