编辑代码

-- 创建数据库
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)
);