
CREATE TABLE "Addresses" (
	address_id INTEGER NOT NULL, 
	address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)


CREATE TABLE "Circulation_History" (
	document_id INTEGER NOT NULL, 
	draft_number INTEGER NOT NULL, 
	copy_number INTEGER NOT NULL, 
	employee_id INTEGER NOT NULL, 
	PRIMARY KEY (document_id, draft_number, copy_number, employee_id), 
	FOREIGN KEY(document_id, draft_number, copy_number) REFERENCES "Draft_Copies" (document_id, draft_number, copy_number), 
	FOREIGN KEY(employee_id) REFERENCES "Employees" (employee_id)
)


CREATE TABLE "Document_Drafts" (
	document_id INTEGER NOT NULL, 
	draft_number INTEGER NOT NULL, 
	draft_details VARCHAR(255), 
	PRIMARY KEY (document_id, draft_number), 
	FOREIGN KEY(document_id) REFERENCES "Documents" (document_id)
)


CREATE TABLE "Documents" (
	document_id INTEGER NOT NULL, 
	document_status_code CHAR(15) NOT NULL, 
	document_type_code CHAR(15) NOT NULL, 
	shipping_agent_code CHAR(15), 
	receipt_date DATETIME, 
	receipt_number VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (document_id), 
	FOREIGN KEY(document_type_code) REFERENCES "Ref_Document_Types" (document_type_code), 
	FOREIGN KEY(document_status_code) REFERENCES "Ref_Document_Status" (document_status_code), 
	FOREIGN KEY(shipping_agent_code) REFERENCES "Ref_Shipping_Agents" (shipping_agent_code)
)


CREATE TABLE "Documents_Mailed" (
	document_id INTEGER NOT NULL, 
	mailed_to_address_id INTEGER NOT NULL, 
	mailing_date DATETIME, 
	PRIMARY KEY (document_id, mailed_to_address_id), 
	FOREIGN KEY(document_id) REFERENCES "Documents" (document_id), 
	FOREIGN KEY(mailed_to_address_id) REFERENCES "Addresses" (address_id)
)


CREATE TABLE "Draft_Copies" (
	document_id INTEGER NOT NULL, 
	draft_number INTEGER NOT NULL, 
	copy_number INTEGER NOT NULL, 
	PRIMARY KEY (document_id, draft_number, copy_number), 
	FOREIGN KEY(document_id, draft_number) REFERENCES "Document_Drafts" (document_id, draft_number)
)


CREATE TABLE "Employees" (
	employee_id INTEGER NOT NULL, 
	role_code CHAR(15) NOT NULL, 
	employee_name VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (employee_id), 
	FOREIGN KEY(role_code) REFERENCES "Roles" (role_code)
)


CREATE TABLE "Ref_Document_Status" (
	document_status_code CHAR(15) NOT NULL, 
	document_status_description VARCHAR(255) NOT NULL, 
	PRIMARY KEY (document_status_code)
)


CREATE TABLE "Ref_Document_Types" (
	document_type_code CHAR(15) NOT NULL, 
	document_type_description VARCHAR(255) NOT NULL, 
	PRIMARY KEY (document_type_code)
)


CREATE TABLE "Ref_Shipping_Agents" (
	shipping_agent_code CHAR(15) NOT NULL, 
	shipping_agent_name VARCHAR(255) NOT NULL, 
	shipping_agent_description VARCHAR(255) NOT NULL, 
	PRIMARY KEY (shipping_agent_code)
)


CREATE TABLE "Roles" (
	role_code CHAR(15) NOT NULL, 
	role_description VARCHAR(255), 
	PRIMARY KEY (role_code)
)

CREATE TABLE "Addresses" (
	address_id INTEGER NOT NULL, 
	address_details VARCHAR(255), 
	PRIMARY KEY (address_id)
)

/*
2 rows from Addresses table:
address_id	address_details
0	IT
1	MX
*/


CREATE TABLE "Circulation_History" (
	document_id INTEGER NOT NULL, 
	draft_number INTEGER NOT NULL, 
	copy_number INTEGER NOT NULL, 
	employee_id INTEGER NOT NULL, 
	PRIMARY KEY (document_id, draft_number, copy_number, employee_id), 
	FOREIGN KEY(document_id, draft_number, copy_number) REFERENCES "Draft_Copies" (document_id, draft_number, copy_number), 
	FOREIGN KEY(employee_id) REFERENCES "Employees" (employee_id)
)

/*
2 rows from Circulation_History table:
document_id	draft_number	copy_number	employee_id
20	17	15	8
1	2	5	1
*/


CREATE TABLE "Document_Drafts" (
	document_id INTEGER NOT NULL, 
	draft_number INTEGER NOT NULL, 
	draft_details VARCHAR(255), 
	PRIMARY KEY (document_id, draft_number), 
	FOREIGN KEY(document_id) REFERENCES "Documents" (document_id)
)

/*
2 rows from Document_Drafts table:
document_id	draft_number	draft_details
1	0	e
1	2	k
*/


CREATE TABLE "Documents" (
	document_id INTEGER NOT NULL, 
	document_status_code CHAR(15) NOT NULL, 
	document_type_code CHAR(15) NOT NULL, 
	shipping_agent_code CHAR(15), 
	receipt_date DATETIME, 
	receipt_number VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (document_id), 
	FOREIGN KEY(document_type_code) REFERENCES "Ref_Document_Types" (document_type_code), 
	FOREIGN KEY(document_status_code) REFERENCES "Ref_Document_Status" (document_status_code), 
	FOREIGN KEY(shipping_agent_code) REFERENCES "Ref_Shipping_Agents" (shipping_agent_code)
)

/*
2 rows from Documents table:
document_id	document_status_code	document_type_code	shipping_agent_code	receipt_date	receipt_number	other_details
1	working	CD	UP	2008-04-21 20:42:25	19	z
2	done	Paper	US	1974-05-08 00:00:46	34	h
*/


CREATE TABLE "Documents_Mailed" (
	document_id INTEGER NOT NULL, 
	mailed_to_address_id INTEGER NOT NULL, 
	mailing_date DATETIME, 
	PRIMARY KEY (document_id, mailed_to_address_id), 
	FOREIGN KEY(document_id) REFERENCES "Documents" (document_id), 
	FOREIGN KEY(mailed_to_address_id) REFERENCES "Addresses" (address_id)
)

/*
2 rows from Documents_Mailed table:
document_id	mailed_to_address_id	mailing_date
2	8	1977-04-01 17:03:50
4	3	1992-11-07 15:03:41
*/


CREATE TABLE "Draft_Copies" (
	document_id INTEGER NOT NULL, 
	draft_number INTEGER NOT NULL, 
	copy_number INTEGER NOT NULL, 
	PRIMARY KEY (document_id, draft_number, copy_number), 
	FOREIGN KEY(document_id, draft_number) REFERENCES "Document_Drafts" (document_id, draft_number)
)

/*
2 rows from Draft_Copies table:
document_id	draft_number	copy_number
2	8	5
4	9	6
*/


CREATE TABLE "Employees" (
	employee_id INTEGER NOT NULL, 
	role_code CHAR(15) NOT NULL, 
	employee_name VARCHAR(255), 
	other_details VARCHAR(255), 
	PRIMARY KEY (employee_id), 
	FOREIGN KEY(role_code) REFERENCES "Roles" (role_code)
)

/*
2 rows from Employees table:
employee_id	role_code	employee_name	other_details
1	ED	Koby	h
2	ED	Kenyon	f
*/


CREATE TABLE "Ref_Document_Status" (
	document_status_code CHAR(15) NOT NULL, 
	document_status_description VARCHAR(255) NOT NULL, 
	PRIMARY KEY (document_status_code)
)

/*
2 rows from Ref_Document_Status table:
document_status_code	document_status_description
working	currently working on
done	mailed
*/


CREATE TABLE "Ref_Document_Types" (
	document_type_code CHAR(15) 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_description
CD	b
Paper	u
*/


CREATE TABLE "Ref_Shipping_Agents" (
	shipping_agent_code CHAR(15) NOT NULL, 
	shipping_agent_name VARCHAR(255) NOT NULL, 
	shipping_agent_description VARCHAR(255) NOT NULL, 
	PRIMARY KEY (shipping_agent_code)
)

/*
2 rows from Ref_Shipping_Agents table:
shipping_agent_code	shipping_agent_name	shipping_agent_description
UP	UPS	g
US	USPS	q
*/


CREATE TABLE "Roles" (
	role_code CHAR(15) NOT NULL, 
	role_description VARCHAR(255), 
	PRIMARY KEY (role_code)
)

/*
2 rows from Roles table:
role_code	role_description
ED	Editor
PT	Photo
*/
What is the shipping agent code of shipping agent UPS?
SELECT shipping_agent_code FROM Ref_Shipping_Agents WHERE shipping_agent_name = "UPS";
[{'shipping_agent_code': 'UP'}]