根据“数据库代码”文档里的的考核要求和知识点，设计一个“健身房系统”的数据库设计书（包含所有要用到的代码）（所设计的代码必须是文档里所学过的知识点代码形式或衍生出来的代码形式，不可以是其他类型的代码）

### 健身房系统数据库设计书

#### 1. 设计目的
通过本课程的学习，设计一个健身房管理系统，实现会员管理、教练管理、课程管理和预约管理等功能。具体目标如下：

- **目标1**：掌握数据库系统设计的专业知识，将实际问题抽象转化为计算机可处理的问题。
- **目标2**：应用数据库系统分析与设计的基本原理确定数据库系统中的功能需求和非功能需求。
- **目标3**：设计、开发满足特定需求的数据库系统及其相关的功能模块，并体现创新意识。
- **目标4**：分析相关操作产生的数据结果，并得出其产生原因。
- **目标5**：使用Oracle数据库管理系统进行数据库系统设计、开发与测试。

#### 2. 设计任务
根据生活实际创建一个健身房管理系统，包含以下表格：
- `member`：会员表
- `trainer`：教练表
- `course`：课程表
- `appointment`：预约表
- `feedback`：反馈表

#### 3. E-R图
绘制E-R图，生成数据表结构。

#### 4. 数据库设计

##### 4.1 创建数据库和表

CREATE DATABASE gym_management;

USE gym_management;

-- 会员表
CREATE TABLE member (
    member_id CHAR(6) NOT NULL PRIMARY KEY,
    name CHAR(20) NOT NULL,
    gender CHAR(3) NOT NULL,
    birthdate DATE NOT NULL,
    phone CHAR(15),
    email CHAR(50)
);

-- 教练表
CREATE TABLE trainer (
    trainer_id CHAR(6) NOT NULL PRIMARY KEY,
    name CHAR(20) NOT NULL,
    gender CHAR(3) NOT NULL,
    birthdate DATE NOT NULL,
    specialty CHAR(50)
);

-- 课程表
CREATE TABLE course (
    course_id CHAR(6) NOT NULL PRIMARY KEY,
    name CHAR(50) NOT NULL,
    duration INT NOT NULL,
    capacity INT NOT NULL,
    trainer_id CHAR(6),
    FOREIGN KEY (trainer_id) REFERENCES trainer(trainer_id)
);

-- 预约表
CREATE TABLE appointment (
    appointment_id CHAR(6) NOT NULL PRIMARY KEY,
    member_id CHAR(6) NOT NULL,
    course_id CHAR(6) NOT NULL,
    appointment_date DATE NOT NULL,
    status CHAR(10) DEFAULT '待确认',
    FOREIGN KEY (member_id) REFERENCES member(member_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

-- 反馈表
CREATE TABLE feedback (
    feedback_id CHAR(6) NOT NULL PRIMARY KEY,
    member_id CHAR(6) NOT NULL,
    course_id CHAR(6) NOT NULL,
    rating INT NOT NULL,
    comment VARCHAR(200),
    FOREIGN KEY (member_id) REFERENCES member(member_id),
    FOREIGN KEY (course_id) REFERENCES course(course_id)
);

##### 4.2 表约束

-- 添加主键约束
ALTER TABLE member ADD CONSTRAINT pk_member PRIMARY KEY (member_id);
ALTER TABLE trainer ADD CONSTRAINT pk_trainer PRIMARY KEY (trainer_id);
ALTER TABLE course ADD CONSTRAINT pk_course PRIMARY KEY (course_id);
ALTER TABLE appointment ADD CONSTRAINT pk_appointment PRIMARY KEY (appointment_id);
ALTER TABLE feedback ADD CONSTRAINT pk_feedback PRIMARY KEY (feedback_id);

-- 添加外键约束
ALTER TABLE course ADD CONSTRAINT fk_course_trainer FOREIGN KEY (trainer_id) REFERENCES trainer(trainer_id);
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_member FOREIGN KEY (member_id) REFERENCES member(member_id);
ALTER TABLE appointment ADD CONSTRAINT fk_appointment_course FOREIGN KEY (course_id) REFERENCES course(course_id);
ALTER TABLE feedback ADD CONSTRAINT fk_feedback_member FOREIGN KEY (member_id) REFERENCES member(member_id);
ALTER TABLE feedback ADD CONSTRAINT fk_feedback_course FOREIGN KEY (course_id) REFERENCES course(course_id);

-- 添加检查约束
ALTER TABLE course ADD CONSTRAINT ck_course_duration CHECK (duration > 0);
ALTER TABLE course ADD CONSTRAINT ck_course_capacity CHECK (capacity > 0);
ALTER TABLE feedback ADD CONSTRAINT ck_feedback_rating CHECK (rating BETWEEN 1 AND 5);

##### 4.3 插入、修改、删除操作

-- 插入数据
INSERT INTO member VALUES ('M00001', '张三', '男', TO_DATE('19900101', 'YYYYMMDD'), '1234567890', 'zhangsan@example.com');
INSERT INTO member VALUES ('M00002', '李四', '女', TO_DATE('19920202', 'YYYYMMDD'), '0987654321', 'lisi@example.com');

INSERT INTO trainer VALUES ('T00001', '王五', '男', TO_DATE('19850303', 'YYYYMMDD'), '健身');
INSERT INTO trainer VALUES ('T00002', '赵六', '女', TO_DATE('19880404', 'YYYYMMDD'), '瑜伽');

INSERT INTO course VALUES ('C00001', '力量训练', 60, 10, 'T00001');
INSERT INTO course VALUES ('C00002', '瑜伽基础', 45, 15, 'T00002');

INSERT INTO appointment VALUES ('A00001', 'M00001', 'C00001', TO_DATE('20231001', 'YYYYMMDD'), '已确认');
INSERT INTO appointment VALUES ('A00002', 'M00002', 'C00002', TO_DATE('20231002', 'YYYYMMDD'), '待确认');

INSERT INTO feedback VALUES ('F00001', 'M00001', 'C00001', 5, '非常满意');
INSERT INTO feedback VALUES ('F00002', 'M00002', 'C00002', 4, '还不错');

-- 修改数据
UPDATE member SET phone = '1111111111' WHERE member_id = 'M00001';
UPDATE trainer SET specialty = '普拉提' WHERE trainer_id = 'T00002';

-- 删除数据
DELETE FROM appointment WHERE appointment_id = 'A00002';

##### 4.4 视图创建与操作

-- 创建视图
CREATE OR REPLACE VIEW v_member_info AS
SELECT m.member_id, m.name, m.gender, m.birthdate, m.phone, m.email, c.course_id, c.name AS course_name
FROM member m
JOIN appointment a ON m.member_id = a.member_id
JOIN course c ON a.course_id = c.course_id;

-- 查询视图
SELECT * FROM v_member_info;

-- 更新视图
UPDATE v_member_info SET phone = '2222222222' WHERE member_id = 'M00001';

-- 删除视图
DROP VIEW v_member_info;

##### 4.5 查询操作

-- 单表查询
SELECT * FROM member WHERE gender = '男';
SELECT * FROM trainer WHERE specialty LIKE '%瑜伽%';

-- 多表查询
SELECT m.name, c.name, a.appointment_date
FROM member m
JOIN appointment a ON m.member_id = a.member_id
JOIN course c ON a.course_id = c.course_id;

-- 内连接
SELECT m.name, c.name, a.appointment_date
FROM member m
INNER JOIN appointment a ON m.member_id = a.member_id
INNER JOIN course c ON a.course_id = c.course_id;

-- 左连接
SELECT m.name, c.name, a.appointment_date
FROM member m
LEFT JOIN appointment a ON m.member_id = a.member_id
LEFT JOIN course c ON a.course_id = c.course_id;

-- 右连接
SELECT m.name, c.name, a.appointment_date
FROM course c
RIGHT JOIN appointment a ON c.course_id = a.course_id
RIGHT JOIN member m ON a.member_id = m.member_id;

-- 交集
SELECT m.name FROM member m
INTERSECT
SELECT f.name FROM feedback f;

-- 并集
SELECT m.name FROM member m
UNION
SELECT f.name FROM feedback f;

-- 差集
SELECT m.name FROM member m
MINUS
SELECT f.name FROM feedback f;

-- 嵌套查询
SELECT m.name, m.phone
FROM member m
WHERE m.member_id IN (SELECT a.member_id FROM appointment a WHERE a.status = '已确认');

##### 4.6 存储过程

-- 无参存储过程
CREATE OR REPLACE PROCEDURE print_welcome AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('欢迎来到健身房管理系统');
END;

-- 输入参数存储过程
CREATE OR REPLACE PROCEDURE get_member_info (p_member_id IN CHAR) AS
    v_name member.name%TYPE;
    v_gender member.gender%TYPE;
BEGIN
    SELECT name, gender INTO v_name, v_gender FROM member WHERE member_id = p_member_id;
    DBMS_OUTPUT.PUT_LINE('会员ID: ' || p_member_id || ' 名字: ' || v_name || ' 性别: ' || v_gender);
END;

-- 输出参数存储过程
CREATE OR REPLACE PROCEDURE set_member_info (p_member_id IN CHAR, p_phone OUT CHAR) AS
BEGIN
    SELECT phone INTO p_phone FROM member WHERE member_id = p_member_id;
END;

-- 输入输出参数存储过程
CREATE OR REPLACE PROCEDURE swap_numbers (p_num1 IN OUT NUMBER, p_num2 IN OUT NUMBER) AS
    v_swap NUMBER;
BEGIN
    v_swap := p_num1;
    p_num1 := p_num2;
    p_num2 := v_swap;
END;

##### 4.7 触发器

-- 事前触发器
CREATE OR REPLACE TRIGGER before_insert_member
BEFORE INSERT ON member
FOR EACH ROW
DECLARE
    v_age INT;
BEGIN
    v_age := FLOOR(MONTHS_BETWEEN(SYSDATE, :NEW.birthdate) / 12);
    IF v_age < 18 THEN
        RAISE_APPLICATION_ERROR(-20001, '年龄必须大于18岁');
    END IF;
END;

-- 事后触发器
CREATE OR REPLACE TRIGGER after_delete_member
AFTER DELETE ON member
FOR EACH ROW
BEGIN
    DELETE FROM appointment WHERE member_id = :OLD.member_id;
END;

-- 系统触发器
CREATE TABLE ddl_events (
    event_time TIMESTAMP,
    user_name VARCHAR2(100),
    event_type VARCHAR2(50),
    object_type VARCHAR2(50),
    object_name VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER log_ddl_events
AFTER DDL ON SYSTEM.SCHEMA
BEGIN
    INSERT INTO ddl_events (event_time, user_name, event_type, object_type, object_name)
    VALUES (SYSTIMESTAMP, ORA_LOGIN_USER, ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME);
END;

##### 4.8 游标

-- 显示游标
DECLARE
    CURSOR cur_members IS
    SELECT member_id, name, gender, birthdate, phone, email
    FROM member;
    v_member_id member.member_id%TYPE;
    v_name member.name%TYPE;
    v_gender member.gender%TYPE;
    v_birthdate member.birthdate%TYPE;
    v_phone member.phone%TYPE;
    v_email member.email%TYPE;
BEGIN
    OPEN cur_members;
    LOOP
        FETCH cur_members INTO v_member_id, v_name, v_gender, v_birthdate, v_phone, v_email;
        EXIT WHEN cur_members%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('会员ID: ' || v_member_id || ' 名字: ' || v_name || ' 性别: ' || v_gender || ' 生日: ' || v_birthdate || ' 电话: ' || v_phone || ' 邮箱: ' || v_email);
    END LOOP;
    CLOSE cur_members;
END;

-- 游标FOR循环
DECLARE
    CURSOR cur_courses IS
    SELECT course_id, name, duration, capacity, trainer_id
    FROM course;
BEGIN
    FOR v_course IN cur_courses LOOP
        DBMS_OUTPUT.PUT_LINE('课程ID: ' || v_course.course_id || ' 名称: ' || v_course.name || ' 时长: ' || v_course.duration || ' 容量: ' || v_course.capacity || ' 教练ID: ' || v_course.trainer_id);
    END LOOP;
END;

#### 5. 设计要求
1. 同班同学每人设计一个系统，所有系统均不可以重复。
2. 所有系统设计要与实际生活相符，所设计的数据库对象如表、视图、存储过程、触发器、游标等要有实际作用，查询语句要有实际意义。
3. 所有代码均要写清楚注释，明确说明代码的功能。
4. 设计结束后需要提交一个Word文档，包括所有题目设计结果。

#### 6. 任务安排
学生从即日起开展工作，包括数据库系统的分析与设计（设计主要包括概要设计和逻辑结构设计）、数据库对象的使用、数据库查询等，11月29日提交设计的Word文档。

#### 7. 考核方式
课程的考核以考察学生数据库系统知识的掌握情况、数据库系统分析与设计能力为主要目的，以检查案例操作情况为重点内容，包括平时考核和期末考核两部分。
- **平时成绩**：100分，占总成绩的40%，主要考核学生的数据库设计能力和数据库对象使用情况，以作业（6次）和实训（4次）的形式进行考核。
- **期末成绩**：100分，占总成绩的60%，以学生完成大作业的方式进行。设置一个数据库综合设计的作业，每个学生单独完成。授课教师根据学生提交的文档的设计情况，结合该设计项目与生活实际的符合程度进行打分，对学生的知识和能力进行综合考核。