USE nathantompkinsdb;

CREATE TABLE Decorator (
    empId MEDIUMINT(5) NOT NULL PRIMARY KEY,
    fName VARCHAR(50) NOT NULL,
    lName VARCHAR(50) NOT NULL,
    address VARCHAR(150),
    phone VARCHAR(16),
    mobilePhone VARCHAR(12),
    dateHired DATE,
    salary FLOAT(7),
    jobNo INT(7)
);

CREATE TABLE Client (
    clientId INT(7) NOT NULL PRIMARY KEY,
    fName VARCHAR(50) NOT NULL,
    lName VARCHAR(50) NOT NULL,
    address VARCHAR(140),
    phone VARCHAR(16),
    referredBy VARCHAR(100),
    jobNo INT(7)
);

CREATE TABLE Job (
    jobNo INT(7) NOT NULL PRIMARY KEY,
    description VARCHAR(1000),
    estTime VARCHAR(9),
    estCost FLOAT(7),
    plannedStartDate DATE,
    plannedEndDate DATE,
    actualStartDate DATE,
    actualEndDate DATE,
    actualTime VARCHAR(9),
    actualCost FLOAT(7),
    itemNo INT(7),
    clientId INT(7),
    empId MEDIUMINT(5),
    activityName VARCHAR(20)
);

CREATE TABLE Activity (
    activityName VARCHAR(200) NOT NULL PRIMARY KEY,
    description VARCHAR(100),
    estTime VARCHAR(9),
    estCost FLOAT(7),
    plannedStartDate DATE,
    plannedEndDate DATE,
    actualStartDate DATE,
    actualEndDate DATE,
    actualTime VARCHAR(9),
    actualCost FLOAT(7),
    itemNo INT(7)
);

CREATE TABLE Contractor (
    licNo VARCHAR(25) NOT NULL PRIMARY KEY,
    fName VARCHAR(50) NOT NULL,
    lName VARCHAR(50) NOT NULL,
    address VARCHAR(140),
    phone VARCHAR(16),
    rating TINYINT(1)
);

CREATE TABLE Material (
    itemNo INT(7) NOT NULL PRIMARY KEY,
    manufacturer VARCHAR(50),
    description VARCHAR(25),
    supplier VARCHAR(50),
    quantity MEDIUMINT(6),
    cost FLOAT(6),
    jobNo INT(7),
    activityName VARCHAR(20)
);

ALTER TABLE Decorator ADD CONSTRAINT fk_jobNo FOREIGN KEY (jobNo) REFERENCES Job(jobNo) ON DELETE CASCADE;

ALTER TABLE Client ADD CONSTRAINT fk_jobNo1 FOREIGN KEY (jobNo) REFERENCES Job(jobNo) ON DELETE CASCADE;

ALTER TABLE Job ADD CONSTRAINT fk_itemNo FOREIGN KEY (itemNo) REFERENCES Material (itemNo) ON DELETE CASCADE;
ALTER TABLE Job ADD CONSTRAINT fk_clientId FOREIGN KEY (clientId) REFERENCES Client (clientId) ON DELETE CASCADE;
ALTER TABLE Job ADD CONSTRAINT fk_empId FOREIGN KEY (empId) REFERENCES Decorator (empId) ON DELETE CASCADE;
ALTER TABLE Job ADD CONSTRAINT fk_activityName FOREIGN KEY (activityName) REFERENCES Activity (activityName) ON DELETE CASCADE;

ALTER TABLE Activity ADD CONSTRAINT fk_itemNo1 FOREIGN KEY (itemNo) REFERENCES Material(itemNo) ON DELETE CASCADE;
ALTER TABLE Activity ADD CONSTRAINT fk_licNo FOREIGN KEY (licNo) REFERENCES Contractor(licNo) ON DELETE CASCADE;

ALTER TABLE Contractor ADD CONSTRAINT fk_activityName FOREIGN KEY (activityName) REFERENCES Activity(activityName) ON DELETE CASCADE;

ALTER TABLE Material ADD CONSTRAINT fk_jobNo2 FOREIGN KEY (jobNo) REFERENCES Job(jobNo) ON DELETE CASCADE;
ALTER TABLE Material ADD CONSTRAINT fk_activityName1 FOREIGN KEY (activityName) REFERENCES Activity(activityName) ON DELETE CASCADE;