-- The Jonson Brothers Ltd. company database
-- MIMER 8.2
-- Create the company database

-- Suppress error messages when dropping non-existing tables
WHENEVER ERROR CONTINUE;
SET OUTPUT OFF;
DROP TABLE supply CASCADE;
DROP TABLE debit CASCADE;
DROP TABLE sale CASCADE;
DROP TABLE parts CASCADE;
DROP TABLE item CASCADE;
DROP TABLE supplier CASCADE;
DROP TABLE dept CASCADE;
DROP TABLE store CASCADE;
DROP TABLE city CASCADE;
DROP TABLE employee CASCADE;
DROP TABLE months CASCADE;
DROP VIEW  sale_supply CASCADE;

-- Show error messages and exit
SET OUTPUT ON;
WHENEVER ERROR EXIT;

-- Schema definition starts here 

CREATE TABLE employee
	(number INTEGER CONSTRAINT pk_employee PRIMARY KEY,
	 name VARCHAR(20),
	 salary INTEGER,
	 manager INTEGER,
	 birthyear INTEGER,
	 startyear INTEGER);

CREATE TABLE dept
	(number INTEGER CONSTRAINT pk_dept PRIMARY KEY,
	 name VARCHAR(20),
	 store INTEGER NOT NULL,
	 floor INTEGER,
	 manager INTEGER);

CREATE TABLE item
        (number INTEGER CONSTRAINT pk_item PRIMARY KEY,
         name VARCHAR(20),
	 dept INTEGER NOT NULL,
         price INTEGER,
         qoh INTEGER CONSTRAINT ck_item_qoh CHECK (qoh >= 0),
         supplier INTEGER NOT NULL);

CREATE TABLE parts
	(number INTEGER CONSTRAINT pk_parts PRIMARY KEY,
	 name VARCHAR(20),
	 color VARCHAR(8),
	 weight INTEGER,
	 qoh INTEGER);

CREATE TABLE supply
	(supplier INTEGER NOT NULL,
	 part INTEGER NOT NULL,
	 shipdate DATE NOT NULL,
	 quan INTEGER,
	 CONSTRAINT pk_supply PRIMARY KEY (supplier, part, shipdate));

CREATE TABLE sale
	(debit INTEGER NOT NULL,
	 item INTEGER NOT NULL,
	 quantity INTEGER,
	 CONSTRAINT pk_sale PRIMARY KEY (debit, item));

CREATE TABLE debit
	(number INTEGER CONSTRAINT pk_debit PRIMARY KEY,
	 sdate DATE DEFAULT CURRENT_DATE NOT NULL,
	 employee INTEGER NOT NULL,
	 account INTEGER NOT NULL);

CREATE TABLE city
	(name VARCHAR(15) CONSTRAINT pk_city PRIMARY KEY,
	 state VARCHAR(6));

CREATE TABLE store
	(number INTEGER CONSTRAINT pk_store PRIMARY KEY,
	 city VARCHAR(15) NOT NULL);

CREATE TABLE supplier
	(number INTEGER CONSTRAINT pk_supplier PRIMARY KEY,
	 name VARCHAR(20),
	 city VARCHAR(15) NOT NULL);

-- Add foreign keys 

ALTER TABLE dept
	ADD CONSTRAINT fk_dept_store FOREIGN KEY (store) REFERENCES store (number);
ALTER TABLE dept
	ADD CONSTRAINT fk_dept_employee FOREIGN KEY (manager) REFERENCES employee (number)
        ON DELETE SET NULL;

ALTER TABLE item
	ADD CONSTRAINT fk_item_dept FOREIGN KEY (dept) REFERENCES dept (number);
ALTER TABLE item
	ADD CONSTRAINT fk_item_supplier FOREIGN KEY (supplier) REFERENCES supplier (number);

ALTER TABLE supply
	ADD CONSTRAINT fk_supply_supplier FOREIGN KEY (supplier) REFERENCES supplier (number);
ALTER TABLE supply
	ADD CONSTRAINT fk_supply_parts FOREIGN KEY (part) REFERENCES parts (number);

ALTER TABLE sale
	ADD CONSTRAINT fk_sale_item FOREIGN KEY (item) REFERENCES item (number);
ALTER TABLE sale
	ADD CONSTRAINT fk_sale_debit FOREIGN KEY (debit) REFERENCES debit(number);
-- implies that a debit/transaction must be created before a sale record.

ALTER TABLE debit
	ADD CONSTRAINT fk_debit_employee FOREIGN KEY (employee) REFERENCES employee (number);

ALTER TABLE store
	ADD CONSTRAINT fk_store_city FOREIGN KEY (city) REFERENCES city (name);

ALTER TABLE supplier
	ADD CONSTRAINT fk_supplier_city FOREIGN KEY (city) REFERENCES city (name);

-- Create the view that has to be modified in lab 2, question 17

CREATE VIEW sale_supply(supplier, item, quantity) as
	SELECT supplier.name, item.name, sale.quantity 
	FROM supplier, item, sale
	WHERE	supplier.number = item.supplier AND
		sale.item = item.number;
