
CREATE TABLE "Attribute_Definitions" (
	attribute_id INTEGER, 
	attribute_name VARCHAR(30), 
	attribute_data_type VARCHAR(10), 
	PRIMARY KEY (attribute_id)
)


CREATE TABLE "Catalog_Contents" (
	catalog_entry_id INTEGER, 
	catalog_level_number INTEGER NOT NULL, 
	parent_entry_id INTEGER, 
	previous_entry_id INTEGER, 
	next_entry_id INTEGER, 
	catalog_entry_name VARCHAR(80), 
	product_stock_number VARCHAR(50), 
	price_in_dollars DOUBLE, 
	price_in_euros DOUBLE, 
	price_in_pounds DOUBLE, 
	capacity VARCHAR(20), 
	length VARCHAR(20), 
	height VARCHAR(20), 
	width VARCHAR(20), 
	PRIMARY KEY (catalog_entry_id), 
	FOREIGN KEY(catalog_level_number) REFERENCES "Catalog_Structure" (catalog_level_number)
)


CREATE TABLE "Catalog_Contents_Additional_Attributes" (
	catalog_entry_id INTEGER NOT NULL, 
	catalog_level_number INTEGER NOT NULL, 
	attribute_id INTEGER NOT NULL, 
	attribute_value VARCHAR(255) NOT NULL, 
	FOREIGN KEY(catalog_level_number) REFERENCES "Catalog_Structure" (catalog_level_number), 
	FOREIGN KEY(catalog_entry_id) REFERENCES "Catalog_Contents" (catalog_entry_id)
)


CREATE TABLE "Catalog_Structure" (
	catalog_level_number INTEGER, 
	catalog_id INTEGER NOT NULL, 
	catalog_level_name VARCHAR(50), 
	PRIMARY KEY (catalog_level_number), 
	FOREIGN KEY(catalog_id) REFERENCES "Catalogs" (catalog_id)
)


CREATE TABLE "Catalogs" (
	catalog_id INTEGER, 
	catalog_name VARCHAR(50), 
	catalog_publisher VARCHAR(80), 
	date_of_publication DATETIME, 
	date_of_latest_revision DATETIME, 
	PRIMARY KEY (catalog_id)
)

CREATE TABLE "Attribute_Definitions" (
	attribute_id INTEGER, 
	attribute_name VARCHAR(30), 
	attribute_data_type VARCHAR(10), 
	PRIMARY KEY (attribute_id)
)

/*
2 rows from Attribute_Definitions table:
attribute_id	attribute_name	attribute_data_type
1	Green	Bool
2	Black	Bool
*/


CREATE TABLE "Catalog_Contents" (
	catalog_entry_id INTEGER, 
	catalog_level_number INTEGER NOT NULL, 
	parent_entry_id INTEGER, 
	previous_entry_id INTEGER, 
	next_entry_id INTEGER, 
	catalog_entry_name VARCHAR(80), 
	product_stock_number VARCHAR(50), 
	price_in_dollars DOUBLE, 
	price_in_euros DOUBLE, 
	price_in_pounds DOUBLE, 
	capacity VARCHAR(20), 
	length VARCHAR(20), 
	height VARCHAR(20), 
	width VARCHAR(20), 
	PRIMARY KEY (catalog_entry_id), 
	FOREIGN KEY(catalog_level_number) REFERENCES "Catalog_Structure" (catalog_level_number)
)

/*
2 rows from Catalog_Contents table:
catalog_entry_id	catalog_level_number	parent_entry_id	previous_entry_id	next_entry_id	catalog_entry_name	product_stock_number	price_in_dollars	price_in_euros	price_in_pounds	capacity	length	height	width
1	1	5	9	7	Cola	89 cp	200.78	159.84	172.17	1	3	9	5
2	8	6	9	8	Root beer	37 hq	687.59	590.11	471.78	8	6	5	6
*/


CREATE TABLE "Catalog_Contents_Additional_Attributes" (
	catalog_entry_id INTEGER NOT NULL, 
	catalog_level_number INTEGER NOT NULL, 
	attribute_id INTEGER NOT NULL, 
	attribute_value VARCHAR(255) NOT NULL, 
	FOREIGN KEY(catalog_level_number) REFERENCES "Catalog_Structure" (catalog_level_number), 
	FOREIGN KEY(catalog_entry_id) REFERENCES "Catalog_Contents" (catalog_entry_id)
)

/*
2 rows from Catalog_Contents_Additional_Attributes table:
catalog_entry_id	catalog_level_number	attribute_id	attribute_value
5	8	4	1
15	9	3	0
*/


CREATE TABLE "Catalog_Structure" (
	catalog_level_number INTEGER, 
	catalog_id INTEGER NOT NULL, 
	catalog_level_name VARCHAR(50), 
	PRIMARY KEY (catalog_level_number), 
	FOREIGN KEY(catalog_id) REFERENCES "Catalogs" (catalog_id)
)

/*
2 rows from Catalog_Structure table:
catalog_level_number	catalog_id	catalog_level_name
1	1	Category
8	2	Sub-Category
*/


CREATE TABLE "Catalogs" (
	catalog_id INTEGER, 
	catalog_name VARCHAR(50), 
	catalog_publisher VARCHAR(80), 
	date_of_publication DATETIME, 
	date_of_latest_revision DATETIME, 
	PRIMARY KEY (catalog_id)
)

/*
2 rows from Catalogs table:
catalog_id	catalog_name	catalog_publisher	date_of_publication	date_of_latest_revision
1	Chocolate	Koepp-Rutherford handmade chocolate store	2013-03-15 05:09:17	2017-09-26 12:10:36
2	Coffee Bean	Murray Coffee shop	2012-04-13 06:37:09	2017-10-26 01:16:51
*/
Find the name and level of catalog structure with level between 5 and 10.
SELECT catalog_level_name ,  catalog_level_number FROM Catalog_Structure WHERE catalog_level_number BETWEEN 5 AND 10
[{'catalog_level_name': 'Sub-Category', 'catalog_level_number': 8}, {'catalog_level_name': 'Product', 'catalog_level_number': 9}]