
CREATE TABLE "Asset_Parts" (
	asset_id INTEGER NOT NULL, 
	part_id INTEGER NOT NULL, 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)


CREATE TABLE "Assets" (
	asset_id INTEGER, 
	maintenance_contract_id INTEGER NOT NULL, 
	supplier_company_id INTEGER NOT NULL, 
	asset_details VARCHAR(255), 
	asset_make VARCHAR(20), 
	asset_model VARCHAR(20), 
	asset_acquired_date DATETIME, 
	asset_disposed_date DATETIME, 
	other_asset_details VARCHAR(255), 
	PRIMARY KEY (asset_id), 
	FOREIGN KEY(supplier_company_id) REFERENCES "Third_Party_Companies" (company_id), 
	FOREIGN KEY(maintenance_contract_id) REFERENCES "Maintenance_Contracts" (maintenance_contract_id)
)


CREATE TABLE "Engineer_Skills" (
	engineer_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(engineer_id) REFERENCES "Maintenance_Engineers" (engineer_id)
)


CREATE TABLE "Engineer_Visits" (
	engineer_visit_id INTEGER, 
	contact_staff_id INTEGER, 
	engineer_id INTEGER NOT NULL, 
	fault_log_entry_id INTEGER NOT NULL, 
	fault_status VARCHAR(10) NOT NULL, 
	visit_start_datetime DATETIME, 
	visit_end_datetime DATETIME, 
	other_visit_details VARCHAR(255), 
	PRIMARY KEY (engineer_visit_id), 
	FOREIGN KEY(contact_staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(engineer_id) REFERENCES "Maintenance_Engineers" (engineer_id), 
	FOREIGN KEY(fault_log_entry_id) REFERENCES "Fault_Log" (fault_log_entry_id)
)


CREATE TABLE "Fault_Log" (
	fault_log_entry_id INTEGER, 
	asset_id INTEGER NOT NULL, 
	recorded_by_staff_id INTEGER NOT NULL, 
	fault_log_entry_datetime DATETIME, 
	fault_description VARCHAR(255), 
	other_fault_details VARCHAR(255), 
	PRIMARY KEY (fault_log_entry_id), 
	FOREIGN KEY(recorded_by_staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_id)
)


CREATE TABLE "Fault_Log_Parts" (
	fault_log_entry_id INTEGER NOT NULL, 
	part_fault_id INTEGER NOT NULL, 
	fault_status VARCHAR(10) NOT NULL, 
	FOREIGN KEY(fault_log_entry_id) REFERENCES "Fault_Log" (fault_log_entry_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)


CREATE TABLE "Maintenance_Contracts" (
	maintenance_contract_id INTEGER, 
	maintenance_contract_company_id INTEGER NOT NULL, 
	contract_start_date DATETIME, 
	contract_end_date DATETIME, 
	other_contract_details VARCHAR(255), 
	PRIMARY KEY (maintenance_contract_id), 
	FOREIGN KEY(maintenance_contract_company_id) REFERENCES "Third_Party_Companies" (company_id)
)


CREATE TABLE "Maintenance_Engineers" (
	engineer_id INTEGER, 
	company_id INTEGER NOT NULL, 
	first_name VARCHAR(50), 
	last_name VARCHAR(50), 
	other_details VARCHAR(255), 
	PRIMARY KEY (engineer_id), 
	FOREIGN KEY(company_id) REFERENCES "Third_Party_Companies" (company_id)
)


CREATE TABLE "Part_Faults" (
	part_fault_id INTEGER, 
	part_id INTEGER NOT NULL, 
	fault_short_name VARCHAR(20), 
	fault_description VARCHAR(255), 
	other_fault_details VARCHAR(255), 
	PRIMARY KEY (part_fault_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)


CREATE TABLE "Parts" (
	part_id INTEGER, 
	part_name VARCHAR(255), 
	chargeable_yn VARCHAR(1), 
	chargeable_amount VARCHAR(20), 
	other_part_details VARCHAR(255), 
	PRIMARY KEY (part_id)
)


CREATE TABLE "Skills" (
	skill_id INTEGER, 
	skill_code VARCHAR(20), 
	skill_description VARCHAR(255), 
	PRIMARY KEY (skill_id)
)


CREATE TABLE "Skills_Required_To_Fix" (
	part_fault_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_name VARCHAR(255), 
	gender VARCHAR(1), 
	other_staff_details VARCHAR(255), 
	PRIMARY KEY (staff_id)
)


CREATE TABLE "Third_Party_Companies" (
	company_id INTEGER, 
	company_type VARCHAR(5) NOT NULL, 
	company_name VARCHAR(255), 
	company_address VARCHAR(255), 
	other_company_details VARCHAR(255), 
	PRIMARY KEY (company_id)
)

CREATE TABLE "Asset_Parts" (
	asset_id INTEGER NOT NULL, 
	part_id INTEGER NOT NULL, 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)

/*
2 rows from Asset_Parts table:
asset_id	part_id
5	3
3	3
*/


CREATE TABLE "Assets" (
	asset_id INTEGER, 
	maintenance_contract_id INTEGER NOT NULL, 
	supplier_company_id INTEGER NOT NULL, 
	asset_details VARCHAR(255), 
	asset_make VARCHAR(20), 
	asset_model VARCHAR(20), 
	asset_acquired_date DATETIME, 
	asset_disposed_date DATETIME, 
	other_asset_details VARCHAR(255), 
	PRIMARY KEY (asset_id), 
	FOREIGN KEY(supplier_company_id) REFERENCES "Third_Party_Companies" (company_id), 
	FOREIGN KEY(maintenance_contract_id) REFERENCES "Maintenance_Contracts" (maintenance_contract_id)
)

/*
2 rows from Assets table:
asset_id	maintenance_contract_id	supplier_company_id	asset_details	asset_make	asset_model	asset_acquired_date	asset_disposed_date	other_asset_details
1	2	2	dell laptop1	PT	58 ub	2017-12-25 00:31:27	2018-03-14 10:50:00	None
2	14	1	dell laptop2	IN	35 xt	2018-01-27 00:59:46	2018-03-20 04:24:09	None
*/


CREATE TABLE "Engineer_Skills" (
	engineer_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(engineer_id) REFERENCES "Maintenance_Engineers" (engineer_id)
)

/*
2 rows from Engineer_Skills table:
engineer_id	skill_id
10	2
10	1
*/


CREATE TABLE "Engineer_Visits" (
	engineer_visit_id INTEGER, 
	contact_staff_id INTEGER, 
	engineer_id INTEGER NOT NULL, 
	fault_log_entry_id INTEGER NOT NULL, 
	fault_status VARCHAR(10) NOT NULL, 
	visit_start_datetime DATETIME, 
	visit_end_datetime DATETIME, 
	other_visit_details VARCHAR(255), 
	PRIMARY KEY (engineer_visit_id), 
	FOREIGN KEY(contact_staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(engineer_id) REFERENCES "Maintenance_Engineers" (engineer_id), 
	FOREIGN KEY(fault_log_entry_id) REFERENCES "Fault_Log" (fault_log_entry_id)
)

/*
2 rows from Engineer_Visits table:
engineer_visit_id	contact_staff_id	engineer_id	fault_log_entry_id	fault_status	visit_start_datetime	visit_end_datetime	other_visit_details
1	8	8	13	Waiting	1978-10-12 23:14:40	1988-01-07 06:41:51	None
2	7	15	13	Return	1980-05-02 23:31:18	1990-08-30 22:44:16	None
*/


CREATE TABLE "Fault_Log" (
	fault_log_entry_id INTEGER, 
	asset_id INTEGER NOT NULL, 
	recorded_by_staff_id INTEGER NOT NULL, 
	fault_log_entry_datetime DATETIME, 
	fault_description VARCHAR(255), 
	other_fault_details VARCHAR(255), 
	PRIMARY KEY (fault_log_entry_id), 
	FOREIGN KEY(recorded_by_staff_id) REFERENCES "Staff" (staff_id), 
	FOREIGN KEY(asset_id) REFERENCES "Assets" (asset_id)
)

/*
2 rows from Fault_Log table:
fault_log_entry_id	asset_id	recorded_by_staff_id	fault_log_entry_datetime	fault_description	other_fault_details
1	3	14	2018-03-21 04:25:00	system error	None
2	7	4	2018-03-13 09:43:05	system error	None
*/


CREATE TABLE "Fault_Log_Parts" (
	fault_log_entry_id INTEGER NOT NULL, 
	part_fault_id INTEGER NOT NULL, 
	fault_status VARCHAR(10) NOT NULL, 
	FOREIGN KEY(fault_log_entry_id) REFERENCES "Fault_Log" (fault_log_entry_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)

/*
2 rows from Fault_Log_Parts table:
fault_log_entry_id	part_fault_id	fault_status
12	3	Reported
7	4	Reported
*/


CREATE TABLE "Maintenance_Contracts" (
	maintenance_contract_id INTEGER, 
	maintenance_contract_company_id INTEGER NOT NULL, 
	contract_start_date DATETIME, 
	contract_end_date DATETIME, 
	other_contract_details VARCHAR(255), 
	PRIMARY KEY (maintenance_contract_id), 
	FOREIGN KEY(maintenance_contract_company_id) REFERENCES "Third_Party_Companies" (company_id)
)

/*
2 rows from Maintenance_Contracts table:
maintenance_contract_id	maintenance_contract_company_id	contract_start_date	contract_end_date	other_contract_details
1	15	2017-09-13 11:51:29	2018-03-16 21:21:50	None
2	9	2017-12-18 11:43:16	2018-03-22 06:00:37	None
*/


CREATE TABLE "Maintenance_Engineers" (
	engineer_id INTEGER, 
	company_id INTEGER NOT NULL, 
	first_name VARCHAR(50), 
	last_name VARCHAR(50), 
	other_details VARCHAR(255), 
	PRIMARY KEY (engineer_id), 
	FOREIGN KEY(company_id) REFERENCES "Third_Party_Companies" (company_id)
)

/*
2 rows from Maintenance_Engineers table:
engineer_id	company_id	first_name	last_name	other_details
1	14	Etha	Reinger	Skilled
2	2	Clemens	Orn	None
*/


CREATE TABLE "Part_Faults" (
	part_fault_id INTEGER, 
	part_id INTEGER NOT NULL, 
	fault_short_name VARCHAR(20), 
	fault_description VARCHAR(255), 
	other_fault_details VARCHAR(255), 
	PRIMARY KEY (part_fault_id), 
	FOREIGN KEY(part_id) REFERENCES "Parts" (part_id)
)

/*
2 rows from Part_Faults table:
part_fault_id	part_id	fault_short_name	fault_description	other_fault_details
1	1	PW	Package Wrong	None
2	1	PW	Package Wrong	None
*/


CREATE TABLE "Parts" (
	part_id INTEGER, 
	part_name VARCHAR(255), 
	chargeable_yn VARCHAR(1), 
	chargeable_amount VARCHAR(20), 
	other_part_details VARCHAR(255), 
	PRIMARY KEY (part_id)
)

/*
2 rows from Parts table:
part_id	part_name	chargeable_yn	chargeable_amount	other_part_details
1	top	0	4	None
2	middle	1	9	None
*/


CREATE TABLE "Skills" (
	skill_id INTEGER, 
	skill_code VARCHAR(20), 
	skill_description VARCHAR(255), 
	PRIMARY KEY (skill_id)
)

/*
2 rows from Skills table:
skill_id	skill_code	skill_description
1	ELEC	Electrical
2	MECH	Mechanical
*/


CREATE TABLE "Skills_Required_To_Fix" (
	part_fault_id INTEGER NOT NULL, 
	skill_id INTEGER NOT NULL, 
	FOREIGN KEY(skill_id) REFERENCES "Skills" (skill_id), 
	FOREIGN KEY(part_fault_id) REFERENCES "Part_Faults" (part_fault_id)
)

/*
2 rows from Skills_Required_To_Fix table:
part_fault_id	skill_id
3	2
12	3
*/


CREATE TABLE "Staff" (
	staff_id INTEGER, 
	staff_name VARCHAR(255), 
	gender VARCHAR(1), 
	other_staff_details VARCHAR(255), 
	PRIMARY KEY (staff_id)
)

/*
2 rows from Staff table:
staff_id	staff_name	gender	other_staff_details
1	Audreanne	1	Manager
2	Berneice	1	Manager
*/


CREATE TABLE "Third_Party_Companies" (
	company_id INTEGER, 
	company_type VARCHAR(5) NOT NULL, 
	company_name VARCHAR(255), 
	company_address VARCHAR(255), 
	other_company_details VARCHAR(255), 
	PRIMARY KEY (company_id)
)

/*
2 rows from Third_Party_Companies table:
company_id	company_type	company_name	company_address	other_company_details
1	Maintenance Contractor	Langworth-Funk	615 Jacobs Mews	Uganda
2	Maintenance Contractor	McDermott Group	873 Conrad Creek Apt. 286	China
*/
For all the faults of different parts, what are all the decriptions of the skills required to fix them? List the name of the faults and the skill description.
SELECT T1.fault_short_name ,  T3.skill_description FROM Part_Faults AS T1 JOIN Skills_Required_To_Fix AS T2 ON T1.part_fault_id  =  T2.part_fault_id JOIN Skills AS T3 ON T2.skill_id  =  T3.skill_id
[{'fault_short_name': 'TW', 'skill_description': 'Mechanical'}, {'fault_short_name': 'MW', 'skill_description': 'TV, Video'}, {'fault_short_name': 'PW', 'skill_description': 'Electrical'}, {'fault_short_name': 'MW', 'skill_description': 'TV, Video'}, {'fault_short_name': 'PW', 'skill_description': 'TV, Video'}, {'fault_short_name': 'PW', 'skill_description': 'Electrical'}, {'fault_short_name': 'MW', 'skill_description': 'TV, Video'}, {'fault_short_name': 'TW', 'skill_description': 'Mechanical'}, {'fault_short_name': 'TW', 'skill_description': 'TV, Video'}, {'fault_short_name': 'PW', 'skill_description': 'Mechanical'}, {'fault_short_name': 'PW', 'skill_description': 'Electrical'}, {'fault_short_name': 'PW', 'skill_description': 'Mechanical'}, {'fault_short_name': 'PW', 'skill_description': 'TV, Video'}, {'fault_short_name': 'MW', 'skill_description': 'TV, Video'}, {'fault_short_name': 'MW', 'skill_description': 'Mechanical'}]