create_db_NEW.txt

CREATE TABLE Users (
    UserID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    UserName varchar(50) NOT NULL, 
    UserPassword varchar(150) NOT NULL,
    UserType TINYINT(1) DEFAULT 0,
    UserEmail varchar(100) NOT NULL,
    UserFirstName varchar(50) NOT NULL,
    UserLastName varchar(50) NOT NULL,
    lab int,
    request int,
    UNIQUE(UserName)
);

CREATE TABLE Protocols (
    ProtID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Creater text,
    ProtName varchar(100),
    ProtMethod text, 
    EquipmentID int
);

CREATE TABLE Supplement (
    SupID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    SupName varchar(50) NOT NULL,
    SupPrice int,
    Stock int
);

CREATE TABLE Experiment (
    ExpAutoID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ExpNum int,
    ConsumeAmount int,
    Result text,
    SupID int,
    CalenID int,
    UserID int,
    ProtID int
);

CREATE TABLE ProtocolGuide (
    GuideID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    Dosage int,
    ProtID int,
    SupID int
);

CREATE TABLE Inventory (
    InventID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    InventName varchar(250),
    Amount int,
    Unit varchar(50),
    Unitprice int,
    SupID int,
    UserID int
);

CREATE TABLE UserCalendar (
    CalenID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    FromDateTime datetime,
    TillDateTime datetime,
    UserID int,
    ProtID int
);

CREATE TABLE lab (
    LabID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    LabName varchar(100),
    Manager int
);

CREATE TABLE log (
    LogID int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    ActionID int,
    UserAction int,
    Timestamp DateTime
);

CREATE TABLE Contain (
    LabID int,
    ProtID int
);

ALTER TABLE Users 
    ADD (FOREIGN KEY (lab) REFERENCES lab(LabID), 
    FOREIGN KEY (request) REFERENCES lab(LabID));
    
ALTER TABLE Experiment 
    ADD (FOREIGN KEY (SupID) REFERENCES Supplement(SupID),
    FOREIGN KEY (CalenID) REFERENCES UserCalendar(CalenID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (ProtID) REFERENCES Protocols(ProtID));

ALTER TABLE ProtocolGuide
    ADD (FOREIGN KEY (ProtID) REFERENCES Protocols(ProtID),
    FOREIGN KEY (SupID) REFERENCES Supplement(SupID));

ALTER TABLE Inventory 
    ADD (FOREIGN KEY (SupID) REFERENCES Supplement(SupID),
    FOREIGN KEY (UserID) REFERENCES Users(UserID));

ALTER TABLE UserCalendar 
    ADD (FOREIGN KEY (UserID) REFERENCES Users(UserID),
    FOREIGN KEY (ProtID) REFERENCES Protocols(ProtID));

ALTER TABLE lab 
    ADD FOREIGN KEY (Manager) REFERENCES users(UserID);
    
ALTER TABLE log 
    ADD FOREIGN KEY (UserAction) REFERENCES users(UserID);

ALTER TABLE Contain
    ADD (FOREIGN KEY (ProtID) REFERENCES Protocols(ProtID),
    FOREIGN KEY (LabID) REFERENCES Lab(LabID));