DROP TABLE Supervisor;
DROP TABLE MachineRecord;
DROP TABLE Machine;
DROP TABLE Asset;
DROP TABLE Mine;

CREATE TABLE Mine (
shaftID SMALLINT PRIMARY KEY NOT NULL,
shaftName VARCHAR2(255) NOT NULL,
location VARCHAR2(255) NOT NULL,
TYPE VARCHAR(255) NOT NULL
);

CREATE TABLE Supervisor (
supervisorID SMALLINT PRIMARY KEY NOT NULL,
shaftID SMALLINT NOT NULL,
name VARCHAR2(255) NOT NULL,
address VARCHAR2(255) NOT NULL,
CONSTRAINT shaftID_fk_1
FOREIGN KEY (shaftID)
REFERENCES Mine ON DELETE CASCADE
);

CREATE TABLE Asset (
assetID SMALLINT PRIMARY KEY NOT NULL,
TYPE SMALLINT NOT NULL,
typeName VARCHAR2(255) NOT NULL,
name VARCHAR2(255) NOT NULL,
buyPrice DECIMAL(24,2) NULL,
sellPrice DECIMAL(24,2) NULL
);

CREATE TABLE Machine (
machineID SMALLINT PRIMARY KEY NOT NULL,
serialNo VARCHAR2(255) UNIQUE NOT NULL,
shaftID SMALLINT NOT NULL,
dateOfPurchase DATE NOT NULL,
TYPE SMALLINT NOT NULL,
CONSTRAINT shaftID_fk_2
FOREIGN KEY (shaftID)
REFERENCES Mine ON DELETE CASCADE
);

CREATE TABLE MachineRecord (
machineID SMALLINT NOT NULL,
recordNo SMALLINT PRIMARY KEY NOT NULL,
serviceDate DATE NOT NULL,
cost DECIMAL(24,2) NOT NULL,
notes VARCHAR2(255) NOT NULL,
CONSTRAINT machineID_fk
FOREIGN KEY (machineID)
REFERENCES Machine ON DELETE CASCADE
);

INSERT INTO Mine VALUES (1, 'Deep in the Bush', 'Vancouver', 'gold');

INSERT INTO Asset VALUES (10001, 1, 'Tool', 'Pickaxe', 5, NULL);

INSERT INTO Asset VALUES (30001, 3, 'Material', 'Tunnel Support Beam', 70, NULL);

INSERT INTO Asset VALUES (10002, 1, 'Tool', 'Shovel', 10, NULL);

INSERT INTO Asset VALUES (20001, 2, 'Machine', 'Crane', 1554322, NULL);

INSERT INTO Asset VALUES (20002, 2, 'Machine', 'Excavator', 365000, NULL);

INSERT INTO Machine (machineID, serialNo, shaftID, TYPE, dateOfPurchase) VALUES (20001, 'TM20001', 1, 2, '05-MAY-2011');

INSERT INTO Machine (machineID, serialNo, shaftID, TYPE, dateOfPurchase) VALUES (20002, 'TM20002', 1, 2, '31-AUG-2011');

INSERT INTO MachineRecord (machineID, recordNo, serviceDate, cost, notes) VALUES (20001, 1, '05-OCT-2011', 250, 'regular maintenance');

INSERT INTO Supervisor (supervisorID, shaftID, name, address) VALUES (1001, 1, 'Bill Nye', '1139 12 Ave');