MySQL数据库基础练习系列36、科研项目管理系统

CREATE TABLE Users (
    user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户ID',
    username VARCHAR(50) NOT NULL UNIQUE COMMENT '用户名',
    password VARCHAR(255) NOT NULL COMMENT '密码',
    gender ENUM('男', '女') NOT NULL COMMENT '性别',
    email VARCHAR(100) UNIQUE COMMENT '邮箱'
);

CREATE TABLE Roles (
    role_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '角色ID',
    role_name VARCHAR(50) NOT NULL UNIQUE COMMENT '角色名称'
);

CREATE TABLE UserRoles (
    user_id INT COMMENT '用户ID',
    role_id INT COMMENT '角色ID',
    PRIMARY KEY (user_id, role_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id),
    FOREIGN KEY (role_id) REFERENCES Roles(role_id)
);

CREATE TABLE Projects (
    project_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '项目ID',
    project_name VARCHAR(100) NOT NULL COMMENT '项目名称',
    project_description TEXT COMMENT '项目描述',
    principal_investigator_id INT COMMENT '主研人ID',
    start_date DATE NOT NULL COMMENT '开始日期',
    end_date DATE NOT NULL COMMENT '结束日期',
    status ENUM('申请中', '审批中', '执行中', '结题') NOT NULL COMMENT '项目状态',
    FOREIGN KEY (principal_investigator_id) REFERENCES Users(user_id)
);

CREATE TABLE Funds (
    fund_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '资金ID',
    project_id INT NOT NULL COMMENT '项目ID',
    source VARCHAR(100) NOT NULL COMMENT '资金来源',
    amount DECIMAL(10, 2) NOT NULL COMMENT '资金金额',
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

CREATE TABLE Achievements (
    achievement_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '成果ID',
    project_id INT NOT NULL COMMENT '项目ID',
    achievement_name VARCHAR(100) NOT NULL COMMENT '成果名称',
    achievement_type ENUM('论文', '专利', '获奖', '其他') NOT NULL COMMENT '成果类型',
    description TEXT COMMENT '成果描述',
    FOREIGN KEY (project_id) REFERENCES Projects(project_id)
);

CREATE TABLE ProjectLogs (
    log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志ID',
    project_id INT NOT NULL COMMENT '项目ID',
    user_id INT NOT NULL COMMENT '用户ID',
    log_date DATETIME NOT NULL COMMENT '日志日期',
    log_content TEXT NOT NULL COMMENT '日志内容',
    FOREIGN KEY (project_id) REFERENCES Projects(project_id),
    FOREIGN KEY (user_id) REFERENCES Users(user_id)
);

INSERT INTO Roles (role_name) VALUES
('管理员'),
('项目负责人'),
('项目成员');
INSERT INTO Users (username, password, gender, email) VALUES
('诸葛亮', '123', '男', 'zhugeliang@example.com'),
('孙悟空', '123', '男', 'sunwukong@example.com'),
('林黛玉', '123', '女', 'lindaiyu@example.com');
INSERT INTO UserRoles (user_id, role_id) VALUES
(1, 1), -- 诸葛亮是管理员
(2, 2), -- 孙悟空是项目负责人
(2, 3), -- 孙悟空也是项目成员
(3, 3);  -- 林黛玉是项目成员
INSERT INTO Projects (project_name, project_description, principal_investigator_id, start_date, end_date, status) VALUES
('三国历史研究项目', '研究三国历史背景', 1, '2023-01-01', '2023-12-31', '执行中'),
('西游记文化研究', '探究西游记的文学价值', 2, '2023-02-01', '2024-01-31', '申请中'),
('红楼梦解读', '分析红楼梦的深层含义', 2, '2023-03-01', '2023-11-30', '审批中');
INSERT INTO Funds (project_id, source, amount) VALUES
(1, '国家社会科学基金', 50000.00),
(2, '企业赞助', 30000.00),
(3, '学校科研基金', 45000.00),
(1, '地方政府资助', 20000.00); -- 同一个项目可以有多个经费来源
INSERT INTO Achievements (project_id, achievement_name, achievement_type, description) VALUES
(1, '三国历史研究报告', '论文', '详细分析了三国时期的历史事件'),
(2, '西游记文化解读', '论文', '深入探讨了西游记的文化内涵'),
(3, '红楼梦人物分析', '论文', '对红楼梦中的主要人物进行了深入剖析'),
(2, '西游记新发现', '专利', '发现了西游记中的新文学元素'); -- 同一个项目可以有多个成果
INSERT INTO ProjectLogs (project_id, user_id, log_date, log_content) VALUES
(1, 1, '2023-01-10 10:00:00', '项目启动会议召开'),
(2, 2, '2023-02-15 15:30:00', '提交项目申请书至学院'),
(3, 3, '2023-03-20 09:45:00', '开始收集红楼梦相关资料'),
(1, 1, '2023-04-01 14:15:00', '第一阶段研究成果汇报');