CREATE TABLE Item (	
	cat_ref VARCHAR(5)PRIMARY KEY,
	descrip VARCHAR(50), 
	date_added SMALLDATETIME,
        date_last_pricecheck SMALLDATETIME,
	cat_type VARCHAR(20),
	contract VARCHAR(10),
        cost_price SMALLMONEY,
        installation_charge SMALLMONEY,
        commercial_markup SMALLMONEY,
        supplier_name VARCHAR(20),
	supplier_phone VARCHAR(20),
notes VARCHAR(500))

CREATE TABLE QuoteItem ( 
	quoted_price SMALLMONEY,
        quote_id INT FOREIGN KEY 
             REFERENCES Quote(quote_id), 
        cat_ref varchar(5) FOREIGN KEY 
             REFERENCES Item(cat_ref) 
CONSTRAINT pk_QuoteItem PRIMARY KEY (quote_id, cat_ref) )


CREATE TABLE Quote (	
	quote_id INT PRIMARY KEY,
	date_of_quote SMALLDATETIME,
	service_desk_contact VARCHAR(20),
	category INTEGER,
	first_name VARCHAR(10),
	surname VARCHAR(10),
	customer_phone VARCHAR(20),
	FOREIGN KEY (first_name, surname, customer_phone) 
		REFERENCES Customer(first_name, surname, customer_phone)
ON DELETE CASCADE
ON UPDATE CASCADE)

CREATE TABLE Customer (		
	first_name VARCHAR(10),
	surname VARCHAR(10),
	customer_phone VARCHAR(20),
	contract VARCHAR(10),
	location VARCHAR(20),
	email VARCHAR(50),
	cust_id INT IDENTITY (1,1),
PRIMARY KEY (first_name, surname, customer_phone))