CREATE DATABASE IF NOT EXISTS planting_db;
USE planting_db;
CREATE TABLE Farmer (
farmer_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
phone VARCHAR(20) NOT NULL UNIQUE,
address VARCHAR(100),
email VARCHAR(50),
specialty VARCHAR(100),
password VARCHAR(100) NOT NULL
);
CREATE TABLE Cooperative (
cooperative_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
establish_date DATE NOT NULL,
president_id INT,
FOREIGN KEY (president_id) REFERENCES Farmer(farmer_id)
);
CREATE TABLE Greenhouse (
greenhouse_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
farmer_id INT NOT NULL,
location VARCHAR(100) NOT NULL,
area DECIMAL(10, 2) NOT NULL,
establish_date DATE NOT NULL,
FOREIGN KEY (farmer_id) REFERENCES Farmer(farmer_id)
);
CREATE TABLE Base (
base_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
farmer_id INT NOT NULL,
location VARCHAR(100) NOT NULL,
area DECIMAL(10, 2) NOT NULL,
establish_date DATE NOT NULL,
FOREIGN KEY (farmer_id) REFERENCES Farmer(farmer_id)
);
CREATE TABLE Region (
region_id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
cooperative_id INT NOT NULL,
location VARCHAR(100) NOT NULL,
area DECIMAL(10, 2) NOT NULL,
establish_date DATE NOT NULL,
FOREIGN KEY (cooperative_id) REFERENCES Cooperative(cooperative_id)
);
CREATE TABLE Planting (
planting_id INT AUTO_INCREMENT PRIMARY KEY,
crop_name VARCHAR(50) NOT NULL,
location_id INT NOT NULL,
location_type VARCHAR(20) NOT NULL CHECK(location_type IN ('greenhouse', 'base', 'region')),
plant_time DATE NOT NULL,
farmer_id INT NOT NULL,
FOREIGN KEY (farmer_id) REFERENCES Farmer(farmer_id)
);
CREATE TABLE Fertilization (
fertilization_id INT AUTO_INCREMENT PRIMARY KEY,
apply_time DATE NOT NULL,
planting_id INT NOT NULL,
fertilizer_type VARCHAR(50) NOT NULL,
fertilizer_name VARCHAR(50) NOT NULL,
dosage DECIMAL(10, 2) NOT NULL,
farmer_id INT NOT NULL,
FOREIGN KEY (planting_id) REFERENCES Planting(planting_id),
FOREIGN KEY (farmer_id) REFERENCES Farmer(farmer_id)
);
CREATE TABLE Pesticide (
pesticide_id INT AUTO_INCREMENT PRIMARY KEY,
apply_time DATE NOT NULL,
planting_id INT NOT NULL,
pesticide_type VARCHAR(50) NOT NULL,
pesticide_name VARCHAR(50) NOT NULL,
dosage DECIMAL(10, 2) NOT NULL,
farmer_id INT NOT NULL,
FOREIGN KEY (planting_id) REFERENCES Planting(planting_id),
FOREIGN KEY (farmer_id) REFERENCES Farmer(farmer_id)
);
CREATE TABLE Pollution (
pollution_id INT AUTO_INCREMENT PRIMARY KEY,
test_time DATE NOT NULL,
planting_id INT NOT NULL,
ph_value DECIMAL(4, 2),
farmer_id INT NOT NULL,
FOREIGN KEY (planting_id) REFERENCES Planting(planting_id),
FOREIGN KEY (farmer_id) REFERENCES Farmer(farmer_id)
);
CREATE TABLE Heavy_Metal (
heavy_metal_id INT AUTO_INCREMENT PRIMARY KEY,
pollution_id INT NOT NULL,
metal_name VARCHAR(50) NOT NULL,
amount DECIMAL(10, 4) NOT NULL,
FOREIGN KEY (pollution_id) REFERENCES Pollution(pollution_id)
);
CREATE TABLE Organic_Toxin (
organic_toxin_id INT AUTO_INCREMENT PRIMARY KEY,
pollution_id INT NOT NULL,
toxin_name VARCHAR(50) NOT NULL,
amount DECIMAL(10, 4) NOT NULL,
FOREIGN KEY (pollution_id) REFERENCES Pollution(pollution_id)
);
CREATE TABLE Other_Pollutant (
other_pollutant_id INT AUTO_INCREMENT PRIMARY KEY,
pollution_id INT NOT NULL,
pollutant_name VARCHAR(50) NOT NULL,
amount DECIMAL(10, 4) NOT NULL,
FOREIGN KEY (pollution_id) REFERENCES Pollution(pollution_id)
);