rem ************************************
rem * Scott R. Armstrong
rem * Director of Information Technology
rem * Kishwaukee College
rem * November - December 2004
rem ************************************

rem ************************************
rem * Create Tables for basic inventory
rem * item and item types
rem ************************************

CREATE TABLE inventory_items   (kish_id_barcode	VARCHAR2(15),
				serial_number	VARCHAR2(25),
				item_type	CHAR(8)
			CHECK (item_type IN ('COMPUTER', 'DISPLAY', 'PRINTER', 'OTHER')),
				date_purchased	VARCHAR2(10),
				original_cost	NUMERIC(8,2),
				warranty_months	NUMERIC,
				location_bldg	VARCHAR2(10),
				location_room	VARCHAR2(15),
				location_dept	VARCHAR2(20),
				primary_user	VARCHAR2(15),
CONSTRAINT INVENTORY_ITEMS_PK PRIMARY KEY (kish_id_barcode));

CREATE TABLE computer_item     (kish_id_barcode	VARCHAR2(15),
				serial_number	VARCHAR2(25),
				manufacturer_id	VARCHAR2(35),
				model_id	VARCHAR2(15),
				mac_address_1	CHAR(17),
				mac_address_2	CHAR(17),
				mac_address_3	CHAR(17),
				ip_address_1	CHAR(15),
				ip_address_2	CHAR(15),
				computer_type	CHAR(12)
			CHECK (computer_type IN ('WORKSTATION', 'SERVER', 'LAPTOP', 'FIREWALL', 'STORAGE', 'OTHER')),
				operating_system VARCHAR2(15),
				processor_type	VARCHAR2(10),
				processor_qty	NUMERIC,
				processor_speed	NUMERIC(4,2),
				memory		NUMERIC(5),
				hdd_interface	VARCHAR2(10),
CONSTRAINT COMPUTER_ITEM_PK PRIMARY KEY (kish_id_barcode));


CREATE TABLE display_item      (kish_id_barcode	VARCHAR2(15),
				serial_number	VARCHAR2(25),
				manufacturer_id	VARCHAR2(25),
				model_id	VARCHAR2(15),
				display_type	CHAR(6)
			CHECK (display_type IN ('LCD', 'CRT', 'OTHER')),
				diagonal_size	NUMERIC(5,2),
CONSTRAINT DISPLAY_ITEM_PK PRIMARY KEY (kish_id_barcode));

CREATE TABLE printer_item      (kish_id_barcode	VARCHAR2(15),
				serial_number	VARCHAR2(25),
				manufacturer_id	VARCHAR2(25),
				model_id	VARCHAR2(15),
				printer_type	CHAR(12)
			CHECK (printer_type IN ('COLOR', 'INK', 'B/W LASER', 'COLOR LASER', 'DOT MATRIX', 'OTHER')),
				ip_address	VARCHAR2(16),
				ext_prnt_srvr	CHAR(3)
			CHECK (ext_prnt_srvr IN ('YES', 'NO')),
CONSTRAINT PRINTER_ITEM_PK PRIMARY KEY (kish_id_barcode));

CREATE TABLE other_it_item	(kish_id_barcode	VARCHAR2(15),
				serial_number	VARCHAR2(25),
				manufacturer_id	VARCHAR2(25),
				model_id	VARCHAR2(15),
				description	VARCHAR2(45),
CONSTRAINT OTHER_IT_ITEM_PK PRIMARY KEY (kish_id_barcode));

rem ************************************
rem * End of Tables for basic inventory
rem * item and item types
rem ************************************


rem ************************************
rem * Create Default Views
rem ************************************


rem ************************************
rem * View it_computer_inventory with
rem * location, ID, hardware details, OS
rem ************************************

CREATE VIEW it_computer_inventory AS
	SELECT inventory_items.kish_id_barcode, inventory_items.serial_number,
		inventory_items.location_room, computer_item.memory,
		computer_item.processor_type, computer_item.operating_system
	FROM inventory_items, computer_item
	WHERE inventory_items.item_type = 'COMPUTER'
	AND computer_item.computer_type = 'WORKSTATION';


rem ************************************
rem * View it_display_inventory with
rem * location, ID, hardware details, OS
rem ************************************

CREATE VIEW it_display_inventory AS
	SELECT inventory_items.kish_id_barcode, inventory_items.serial_number,
		inventory_items.location_room, display_item.manufacturer_id,
		display_item.display_type, display_item.diagonal_size
	FROM inventory_items, display_item
	WHERE inventory_items.item_type = 'DISPLAY';