
CREATE TABLE "Accounts" (
	"Account_ID" INTEGER NOT NULL, 
	"Statement_ID" INTEGER NOT NULL, 
	"Account_Details" VARCHAR(255), 
	PRIMARY KEY ("Account_ID"), 
	FOREIGN KEY("Statement_ID") REFERENCES "Statements" ("Statement_ID")
)


CREATE TABLE "Documents" (
	"Document_ID" INTEGER NOT NULL, 
	"Document_Type_Code" CHAR(15) NOT NULL, 
	"Project_ID" INTEGER NOT NULL, 
	"Document_Date" DATETIME, 
	"Document_Name" VARCHAR(255), 
	"Document_Description" VARCHAR(255), 
	"Other_Details" VARCHAR(255), 
	PRIMARY KEY ("Document_ID"), 
	FOREIGN KEY("Document_Type_Code") REFERENCES "Ref_Document_Types" ("Document_Type_Code"), 
	FOREIGN KEY("Project_ID") REFERENCES "Projects" ("Project_ID")
)


CREATE TABLE "Documents_with_Expenses" (
	"Document_ID" INTEGER NOT NULL, 
	"Budget_Type_Code" CHAR(15) NOT NULL, 
	"Document_Details" VARCHAR(255), 
	PRIMARY KEY ("Document_ID"), 
	FOREIGN KEY("Budget_Type_Code") REFERENCES "Ref_Budget_Codes" ("Budget_Type_Code"), 
	FOREIGN KEY("Document_ID") REFERENCES "Documents" ("Document_ID")
)


CREATE TABLE "Projects" (
	"Project_ID" INTEGER NOT NULL, 
	"Project_Details" VARCHAR(255), 
	PRIMARY KEY ("Project_ID")
)


CREATE TABLE "Ref_Budget_Codes" (
	"Budget_Type_Code" CHAR(15) NOT NULL, 
	"Budget_Type_Description" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Budget_Type_Code")
)


CREATE TABLE "Ref_Document_Types" (
	"Document_Type_Code" CHAR(15) NOT NULL, 
	"Document_Type_Name" VARCHAR(255) NOT NULL, 
	"Document_Type_Description" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Document_Type_Code")
)


CREATE TABLE "Statements" (
	"Statement_ID" INTEGER NOT NULL, 
	"Statement_Details" VARCHAR(255), 
	PRIMARY KEY ("Statement_ID"), 
	FOREIGN KEY("Statement_ID") REFERENCES "Documents" ("Document_ID")
)

CREATE TABLE "Accounts" (
	"Account_ID" INTEGER NOT NULL, 
	"Statement_ID" INTEGER NOT NULL, 
	"Account_Details" VARCHAR(255), 
	PRIMARY KEY ("Account_ID"), 
	FOREIGN KEY("Statement_ID") REFERENCES "Statements" ("Statement_ID")
)

/*
2 rows from Accounts table:
Account_ID	Statement_ID	Account_Details
7	57	495.063
61	57	930.14
*/


CREATE TABLE "Documents" (
	"Document_ID" INTEGER NOT NULL, 
	"Document_Type_Code" CHAR(15) NOT NULL, 
	"Project_ID" INTEGER NOT NULL, 
	"Document_Date" DATETIME, 
	"Document_Name" VARCHAR(255), 
	"Document_Description" VARCHAR(255), 
	"Other_Details" VARCHAR(255), 
	PRIMARY KEY ("Document_ID"), 
	FOREIGN KEY("Document_Type_Code") REFERENCES "Ref_Document_Types" ("Document_Type_Code"), 
	FOREIGN KEY("Project_ID") REFERENCES "Projects" ("Project_ID")
)

/*
2 rows from Documents table:
Document_ID	Document_Type_Code	Project_ID	Document_Date	Document_Name	Document_Description	Other_Details
29	CV	30	2004-08-28 06:59:19	Review on UK files	None	None
42	BK	105	2012-12-27 19:09:18	Review on Canadian files	None	None
*/


CREATE TABLE "Documents_with_Expenses" (
	"Document_ID" INTEGER NOT NULL, 
	"Budget_Type_Code" CHAR(15) NOT NULL, 
	"Document_Details" VARCHAR(255), 
	PRIMARY KEY ("Document_ID"), 
	FOREIGN KEY("Budget_Type_Code") REFERENCES "Ref_Budget_Codes" ("Budget_Type_Code"), 
	FOREIGN KEY("Document_ID") REFERENCES "Documents" ("Document_ID")
)

/*
2 rows from Documents_with_Expenses table:
Document_ID	Budget_Type_Code	Document_Details
57	GV	government
192	GV	government
*/


CREATE TABLE "Projects" (
	"Project_ID" INTEGER NOT NULL, 
	"Project_Details" VARCHAR(255), 
	PRIMARY KEY ("Project_ID")
)

/*
2 rows from Projects table:
Project_ID	Project_Details
30	Society Research project
35	Internet of Things project
*/


CREATE TABLE "Ref_Budget_Codes" (
	"Budget_Type_Code" CHAR(15) NOT NULL, 
	"Budget_Type_Description" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Budget_Type_Code")
)

/*
2 rows from Ref_Budget_Codes table:
Budget_Type_Code	Budget_Type_Description
GV	Government
ORG	Organisation
*/


CREATE TABLE "Ref_Document_Types" (
	"Document_Type_Code" CHAR(15) NOT NULL, 
	"Document_Type_Name" VARCHAR(255) NOT NULL, 
	"Document_Type_Description" VARCHAR(255) NOT NULL, 
	PRIMARY KEY ("Document_Type_Code")
)

/*
2 rows from Ref_Document_Types table:
Document_Type_Code	Document_Type_Name	Document_Type_Description
BK	Book	excellent
CV	CV	excellent
*/


CREATE TABLE "Statements" (
	"Statement_ID" INTEGER NOT NULL, 
	"Statement_Details" VARCHAR(255), 
	PRIMARY KEY ("Statement_ID"), 
	FOREIGN KEY("Statement_ID") REFERENCES "Documents" ("Document_ID")
)

/*
2 rows from Statements table:
Statement_ID	Statement_Details
57	Open Project
192	Private Project
*/
Show ids for all documents with budget types described as 'Government'.
SELECT T1.document_id FROM Documents_with_expenses AS T1 JOIN Ref_Budget_Codes AS T2 ON T1.Budget_Type_code  =  T2.Budget_Type_code WHERE T2.budget_type_Description  =  "Government"
[{'Document_ID': 57}, {'Document_ID': 192}, {'Document_ID': 226}, {'Document_ID': 227}, {'Document_ID': 240}, {'Document_ID': 300}]