
CREATE TABLE "Benefits_Overpayments" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)


CREATE TABLE "Business_Rates" (
	business_rates_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (business_rates_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)


CREATE TABLE "CMI_Cross_References" (
	cmi_cross_ref_id INTEGER NOT NULL, 
	master_customer_id INTEGER NOT NULL, 
	source_system_code CHAR(15) NOT NULL, 
	PRIMARY KEY (cmi_cross_ref_id), 
	FOREIGN KEY(master_customer_id) REFERENCES "Customer_Master_Index" (master_customer_id)
)


CREATE TABLE "Council_Tax" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)


CREATE TABLE "Customer_Master_Index" (
	master_customer_id INTEGER NOT NULL, 
	cmi_details VARCHAR(255), 
	PRIMARY KEY (master_customer_id)
)


CREATE TABLE "Electoral_Register" (
	electoral_register_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (electoral_register_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)


CREATE TABLE "Parking_Fines" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)


CREATE TABLE "Rent_Arrears" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)

CREATE TABLE "Benefits_Overpayments" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)

/*
2 rows from Benefits_Overpayments table:
council_tax_id	cmi_cross_ref_id
3	65
6	41
*/


CREATE TABLE "Business_Rates" (
	business_rates_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (business_rates_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)

/*
2 rows from Business_Rates table:
business_rates_id	cmi_cross_ref_id
2	99
5	49
*/


CREATE TABLE "CMI_Cross_References" (
	cmi_cross_ref_id INTEGER NOT NULL, 
	master_customer_id INTEGER NOT NULL, 
	source_system_code CHAR(15) NOT NULL, 
	PRIMARY KEY (cmi_cross_ref_id), 
	FOREIGN KEY(master_customer_id) REFERENCES "Customer_Master_Index" (master_customer_id)
)

/*
2 rows from CMI_Cross_References table:
cmi_cross_ref_id	master_customer_id	source_system_code
2	4	Rent
4	5	Parking
*/


CREATE TABLE "Council_Tax" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)

/*
2 rows from Council_Tax table:
council_tax_id	cmi_cross_ref_id
1	101
2	103
*/


CREATE TABLE "Customer_Master_Index" (
	master_customer_id INTEGER NOT NULL, 
	cmi_details VARCHAR(255), 
	PRIMARY KEY (master_customer_id)
)

/*
2 rows from Customer_Master_Index table:
master_customer_id	cmi_details
1	Schmitt-Lang
2	Volkman, Mills and Ferry
*/


CREATE TABLE "Electoral_Register" (
	electoral_register_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (electoral_register_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)

/*
2 rows from Electoral_Register table:
electoral_register_id	cmi_cross_ref_id
2	83
3	65
*/


CREATE TABLE "Parking_Fines" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)

/*
2 rows from Parking_Fines table:
council_tax_id	cmi_cross_ref_id
9	4
10	81
*/


CREATE TABLE "Rent_Arrears" (
	council_tax_id INTEGER NOT NULL, 
	cmi_cross_ref_id INTEGER NOT NULL, 
	PRIMARY KEY (council_tax_id), 
	FOREIGN KEY(cmi_cross_ref_id) REFERENCES "CMI_Cross_References" (cmi_cross_ref_id)
)

/*
2 rows from Rent_Arrears table:
council_tax_id	cmi_cross_ref_id
1	100
2	8
*/
How many different source system code for the cmi cross references are there?
SELECT count(DISTINCT source_system_code) FROM CMI_cross_references
[{'count(DISTINCT source_system_code)': 6}]