Notes

CREATE TABLE Users (
User_ID INT AUTO_INCREMENT PRIMARY KEY,
User_Name VARCHAR(50) NOT NULL,
User_Password VARCHAR(500) NOT NULL,
Full_Name VARCHAR(100) NOT NULL,
Phone_Number VARCHAR(20) NOT NULL,
Is_Admin BOOLEAN NOT NULL DEFAULT FALSE
);


CREATE TABLE Plant (
Plant_ID INT AUTO_INCREMENT PRIMARY KEY,
Plant_Name VARCHAR(100) NOT NULL,
Scientific_Name VARCHAR(200) NOT NULL,
Plant_Description TEXT,
Image LONGBLOB
);


CREATE TABLE Culture_Medium (
Culture_Medium_ID INT AUTO_INCREMENT PRIMARY KEY,
Culture_Medium_Name VARCHAR(100) NOT NULL,
Culture_Medium_Description TEXT
);


CREATE TABLE Growth_Parameters (
Growth_Parameters_ID INT AUTO_INCREMENT PRIMARY KEY,
Temperature DECIMAL(5,2),
Photoperiod VARCHAR(50),
Light_Intensity VARCHAR(50),
Humidity DECIMAL(5,2),
pH DECIMAL(4,2),
Container VARCHAR(50),
Container_Size VARCHAR(50)
);


CREATE TABLE Laboratory (
Laboratory_ID INT AUTO_INCREMENT PRIMARY KEY,
Laboratory_Name VARCHAR(100) NOT NULL,
Address VARCHAR(200) NOT NULL,
Phone_Number VARCHAR(20) NOT NULL
);


CREATE TABLE Nursery (
Nursery_ID INT AUTO_INCREMENT PRIMARY KEY,
Nursery_Name VARCHAR(100) NOT NULL,
Address VARCHAR(200) NOT NULL,
Phone_Number VARCHAR(20) NOT NULL
);


CREATE TABLE Greenhouse (
Greenhouse_ID INT AUTO_INCREMENT PRIMARY KEY,
Greenhouse_Name VARCHAR(100) NOT NULL,
Address VARCHAR(200) NOT NULL,
Phone_Number VARCHAR(20) NOT NULL
);


CREATE TABLE Field (
Field_ID INT AUTO_INCREMENT PRIMARY KEY,
Field_Name VARCHAR(100) NOT NULL,
Address VARCHAR(200) NOT NULL,
Field_Size DECIMAL(10,2) NOT NULL
);


CREATE TABLE Culture (
Culture_ID INT AUTO_INCREMENT PRIMARY KEY,
Plant_ID INT,
Culture_Medium_ID INT,
Laboratory_ID INT,
Nursery_ID INT,
Greenhouse_ID INT,
Field_ID INT,
Start_Date DATE,
Duration_Of_Nursery_1 INT,
Duration_Of_Laboratory INT,
Duration_Of_Nursery_2 INT,
Duration_Of_GreenHouse INT,
Duration_Of_Field INT,
Growth_Parameters_ID INT,
FOREIGN KEY (Plant_ID) REFERENCES Plant(Plant_ID),
FOREIGN KEY (Culture_Medium_ID) REFERENCES Culture_Medium(Culture_Medium_ID),
FOREIGN KEY (Laboratory_ID) REFERENCES Laboratory(Laboratory_ID),
FOREIGN KEY (Nursery_ID) REFERENCES Nursery(Nursery_ID),
FOREIGN KEY (Greenhouse_ID) REFERENCES Greenhouse(Greenhouse_ID),
FOREIGN KEY (Field_ID) REFERENCES Field(Field_ID),
FOREIGN KEY (Growth_Parameters_ID) REFERENCES Growth_Parameters(Growth_Parameters_ID)
);


CREATE TABLE Growth_Record (
Growth_Record_ID INT AUTO_INCREMENT PRIMARY KEY,
Culture_ID INT,
Observation_Date DATE,
Height DECIMAL(10,2),
Width DECIMAL(10,2),
Number_of_Leaves INT,
Health_Status VARCHAR(50),
Image VARCHAR(100),
FOREIGN KEY (Culture_ID) REFERENCES Culture(Culture_ID)
);


CREATE TABLE Tasks (
Task_ID INT AUTO_INCREMENT PRIMARY KEY,
Task_Category VARCHAR(20) NOT NULL,
Title VARCHAR(50) NOT NULL,
Description VARCHAR(500),
Priority VARCHAR(10) NOT NULL,
Due_Date DATE NOT NULL,
Status VARCHAR(20) NOT NULL DEFAULT 'Incomplete'
);