根据“《数据库系统》期末考核设计任务书”文档的要求，按照“数据库代码”文档里的知识点，设计一个“健身房系统”的数据库设计书（包含所有要用到的代码）

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

#### 一、设计目的

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

#### 二、设计任务

##### 1. 系统分析与 E-R 图

**E-R 图**
- 实体：会员、教练、课程、预约、支付
- 关系：会员参加课程、教练教授课程、会员预约课程、会员支付费用

**数据表结构**
- 会员 (Member)
- 教练 (Trainer)
- 课程 (Course)
- 预约 (Reservation)
- 支付 (Payment)

##### 2. 创建数据库和表

CREATE DATABASE GymDB;

USE GymDB;

-- 创建会员表
CREATE TABLE Member (
    MemberID INT PRIMARY KEY,
    Name VARCHAR(50),
    Gender CHAR(1),
    BirthDate DATE,
    PhoneNumber VARCHAR(15),
    Email VARCHAR(50)
);

-- 创建教练表
CREATE TABLE Trainer (
    TrainerID INT PRIMARY KEY,
    Name VARCHAR(50),
    Gender CHAR(1),
    BirthDate DATE,
    PhoneNumber VARCHAR(15),
    Email VARCHAR(50)
);

-- 创建课程表
CREATE TABLE Course (
    CourseID INT PRIMARY KEY,
    Name VARCHAR(50),
    Description TEXT,
    Duration INT,
    TrainerID INT,
    FOREIGN KEY (TrainerID) REFERENCES Trainer(TrainerID)
);

-- 创建预约表
CREATE TABLE Reservation (
    ReservationID INT PRIMARY KEY,
    MemberID INT,
    CourseID INT,
    ReservationDate DATE,
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID),
    FOREIGN KEY (CourseID) REFERENCES Course(CourseID)
);

-- 创建支付表
CREATE TABLE Payment (
    PaymentID INT PRIMARY KEY,
    MemberID INT,
    Amount DECIMAL(10, 2),
    PaymentDate DATE,
    FOREIGN KEY (MemberID) REFERENCES Member(MemberID)
);

##### 3. 表约束

-- 添加唯一约束
ALTER TABLE Member ADD CONSTRAINT UQ_Member_PhoneNumber UNIQUE (PhoneNumber);
ALTER TABLE Trainer ADD CONSTRAINT UQ_Trainer_PhoneNumber UNIQUE (PhoneNumber);

-- 添加检查约束
ALTER TABLE Member ADD CONSTRAINT CHK_Gender CHECK (Gender IN ('M', 'F'));
ALTER TABLE Trainer ADD CONSTRAINT CHK_Trainer_Gender CHECK (Gender IN ('M', 'F'));

-- 添加默认值约束
ALTER TABLE Member ALTER COLUMN BirthDate SET DEFAULT '1990-01-01';
ALTER TABLE Trainer ALTER COLUMN BirthDate SET DEFAULT '1990-01-01';

-- 修改表结构
ALTER TABLE Course ADD COLUMN Capacity INT;

##### 4. 插入、修改、删除数据

-- 插入数据
INSERT INTO Member (MemberID, Name, Gender, BirthDate, PhoneNumber, Email) VALUES
(1, 'John Doe', 'M', '1990-01-01', '1234567890', 'john@example.com'),
(2, 'Jane Smith', 'F', '1992-05-15', '0987654321', 'jane@example.com');

INSERT INTO Trainer (TrainerID, Name, Gender, BirthDate, PhoneNumber, Email) VALUES
(1, 'Mike Johnson', 'M', '1985-03-10', '1122334455', 'mike@example.com'),
(2, 'Lisa Williams', 'F', '1988-07-20', '5544332211', 'lisa@example.com');

INSERT INTO Course (CourseID, Name, Description, Duration, TrainerID, Capacity) VALUES
(1, 'Yoga', 'Basic yoga for beginners', 60, 1, 20),
(2, 'Zumba', 'High-energy dance fitness class', 45, 2, 25);

INSERT INTO Reservation (ReservationID, MemberID, CourseID, ReservationDate) VALUES
(1, 1, 1, '2023-10-01'),
(2, 2, 2, '2023-10-02');

INSERT INTO Payment (PaymentID, MemberID, Amount, PaymentDate) VALUES
(1, 1, 100.00, '2023-10-01'),
(2, 2, 150.00, '2023-10-02');

-- 修改数据
UPDATE Member SET Email = 'john.doe@example.com' WHERE MemberID = 1;
UPDATE Trainer SET Email = 'mike.johnson@example.com' WHERE TrainerID = 1;
UPDATE Course SET Capacity = 25 WHERE CourseID = 1;
UPDATE Reservation SET ReservationDate = '2023-10-03' WHERE ReservationID = 1;
UPDATE Payment SET Amount = 120.00 WHERE PaymentID = 1;

-- 删除数据
DELETE FROM Reservation WHERE ReservationID = 2;

##### 5. 创建视图

-- 创建视图
CREATE VIEW MemberDetails AS
SELECT m.MemberID, m.Name, m.Gender, m.BirthDate, m.PhoneNumber, m.Email, p.PaymentID, p.Amount, p.PaymentDate
FROM Member m
LEFT JOIN Payment p ON m.MemberID = p.MemberID;

CREATE VIEW TrainerCourses AS
SELECT t.TrainerID, t.Name, c.CourseID, c.Name AS CourseName, c.Description, c.Duration
FROM Trainer t
JOIN Course c ON t.TrainerID = c.TrainerID;

CREATE VIEW PopularCourses AS
SELECT c.CourseID, c.Name, COUNT(r.ReservationID) AS ReservationCount
FROM Course c
JOIN Reservation r ON c.CourseID = r.CourseID
GROUP BY c.CourseID, c.Name
ORDER BY ReservationCount DESC;

CREATE VIEW MemberReservations AS
SELECT m.MemberID, m.Name, r.ReservationID, c.Name AS CourseName, r.ReservationDate
FROM Member m
JOIN Reservation r ON m.MemberID = r.MemberID
JOIN Course c ON r.CourseID = c.CourseID;

CREATE VIEW TrainerPayments AS
SELECT t.TrainerID, t.Name, SUM(p.Amount) AS TotalAmount
FROM Trainer t
JOIN Course c ON t.TrainerID = c.TrainerID
JOIN Payment p ON c.TrainerID = p.MemberID
GROUP BY t.TrainerID, t.Name;

CREATE VIEW MonthlyRevenue AS
SELECT EXTRACT(MONTH FROM p.PaymentDate) AS Month, SUM(p.Amount) AS TotalAmount
FROM Payment p
GROUP BY EXTRACT(MONTH FROM p.PaymentDate);

-- 修改视图
CREATE OR REPLACE VIEW MemberDetails AS
SELECT m.MemberID, m.Name, m.Gender, m.BirthDate, m.PhoneNumber, m.Email, p.PaymentID, p.Amount, p.PaymentDate
FROM Member m
LEFT JOIN Payment p ON m.MemberID = p.MemberID
WHERE p.PaymentDate >= '2023-01-01';

CREATE OR REPLACE VIEW TrainerCourses AS
SELECT t.TrainerID, t.Name, c.CourseID, c.Name AS CourseName, c.Description, c.Duration
FROM Trainer t
JOIN Course c ON t.TrainerID = c.TrainerID
WHERE c.Capacity > 10;

-- 删除视图
DROP VIEW PopularCourses;
DROP VIEW MonthlyRevenue;

##### 6. 查询数据

-- 单表查询
SELECT * FROM Member WHERE Gender = 'M';
SELECT * FROM Trainer WHERE BirthDate < '1990-01-01';
SELECT * FROM Course WHERE Duration > 45;
SELECT * FROM Reservation WHERE ReservationDate BETWEEN '2023-10-01' AND '2023-10-31';
SELECT * FROM Payment WHERE Amount > 100;

-- 多表查询
SELECT m.Name, c.Name AS CourseName, r.ReservationDate
FROM Member m
JOIN Reservation r ON m.MemberID = r.MemberID
JOIN Course c ON r.CourseID = c.CourseID;

SELECT t.Name, c.Name AS CourseName, AVG(c.Duration) AS AverageDuration
FROM Trainer t
JOIN Course c ON t.TrainerID = c.TrainerID
GROUP BY t.Name, c.Name;

SELECT m.Name, SUM(p.Amount) AS TotalSpent
FROM Member m
JOIN Payment p ON m.MemberID = p.MemberID
GROUP BY m.Name
HAVING SUM(p.Amount) > 100;

SELECT m.Name, c.Name AS CourseName, r.ReservationDate
FROM Member m
JOIN Reservation r ON m.MemberID = r.MemberID
JOIN Course c ON r.CourseID = c.CourseID
UNION ALL
SELECT m.Name, c.Name AS CourseName, r.ReservationDate
FROM Member m
JOIN Reservation r ON m.MemberID = r.MemberID
JOIN Course c ON r.CourseID = c.CourseID;

SELECT m.Name, c.Name AS CourseName, r.ReservationDate
FROM Member m
JOIN Reservation r ON m.MemberID = r.MemberID
JOIN Course c ON r.CourseID = c.CourseID
WHERE c.TrainerID = 1;

SELECT m.Name, c.Name AS CourseName, r.ReservationDate
FROM Member m
LEFT JOIN Reservation r ON m.MemberID = r.MemberID
LEFT JOIN Course c ON r.CourseID = c.CourseID
WHERE c.Name IS NULL;

SELECT m.Name, c.Name AS CourseName, r.ReservationDate
FROM Member m
RIGHT JOIN Reservation r ON m.MemberID = r.MemberID
RIGHT JOIN Course c ON r.CourseID = c.CourseID
WHERE m.Name IS NULL;

##### 7. 存储过程

-- 带输入参数的存储过程
DELIMITER //
CREATE PROCEDURE AddNewMember(IN newMemberID INT, IN newName VARCHAR(50), IN newGender CHAR(1), IN newBirthDate DATE, IN newPhoneNumber VARCHAR(15), IN newEmail VARCHAR(50))
BEGIN
    INSERT INTO Member (MemberID, Name, Gender, BirthDate, PhoneNumber, Email) VALUES (newMemberID, newName, newGender, newBirthDate, newPhoneNumber, newEmail);
END //
DELIMITER ;

-- 带输出参数的存储过程
DELIMITER //
CREATE PROCEDURE GetTotalSpent(IN memberID INT, OUT totalSpent DECIMAL(10, 2))
BEGIN
    SELECT SUM(Amount) INTO totalSpent
    FROM Payment
    WHERE MemberID = memberID;
END //
DELIMITER ;

-- 无参存储过程
DELIMITER //
CREATE PROCEDURE ListAllMembers()
BEGIN
    SELECT * FROM Member;
END //
DELIMITER ;

##### 8. 触发器

-- 事前触发器
DELIMITER //
CREATE TRIGGER BeforeInsertMember
BEFORE INSERT ON Member
FOR EACH ROW
BEGIN
    IF NEW.BirthDate > CURDATE() THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid birth date';
    END IF;
END //
DELIMITER ;

-- 事后触发器
DELIMITER //
CREATE TRIGGER AfterInsertPayment
AFTER INSERT ON Payment
FOR EACH ROW
BEGIN
    UPDATE Member SET LastPaymentDate = NEW.PaymentDate WHERE MemberID = NEW.MemberID;
END //
DELIMITER ;

-- 替换触发器
DELIMITER //
CREATE TRIGGER InsteadOfUpdateMember
INSTEAD OF UPDATE ON Member
FOR EACH ROW
BEGIN
    UPDATE Member SET Name = NEW.Name, Gender = NEW.Gender, BirthDate = NEW.BirthDate, PhoneNumber = NEW.PhoneNumber, Email = NEW.Email WHERE MemberID = OLD.MemberID;
END //
DELIMITER ;

-- 级联触发器
DELIMITER //
CREATE TRIGGER CascadeDeleteMember
AFTER DELETE ON Member
FOR EACH ROW
BEGIN
    DELETE FROM Payment WHERE MemberID = OLD.MemberID;
    DELETE FROM Reservation WHERE MemberID = OLD.MemberID;
END //
DELIMITER ;

-- 系统触发器
DELIMITER //
CREATE TRIGGER LogDatabaseChanges
AFTER UPDATE ON Member
FOR EACH ROW
BEGIN
    INSERT INTO AuditLog (TableName, Action, OldValue, NewValue, ChangeTime)
    VALUES ('Member', 'UPDATE', CONCAT('Name: ', OLD.Name, ', Gender: ', OLD.Gender, ', BirthDate: ', OLD.BirthDate, ', PhoneNumber: ', OLD.PhoneNumber, ', Email: ', OLD.Email),
            CONCAT('Name: ', NEW.Name, ', Gender: ', NEW.Gender, ', BirthDate: ', NEW.BirthDate, ', PhoneNumber: ', NEW.PhoneNumber, ', Email: ', NEW.Email), NOW());
END //
DELIMITER ;

##### 9. 游标

DELIMITER //
CREATE PROCEDURE CalculateAverageAge()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE totalAge INT DEFAULT 0;
    DECLARE count INT DEFAULT 0;
    DECLARE averageAge DECIMAL(10, 2);
    DECLARE cur CURSOR FOR SELECT DATEDIFF(CURDATE(), BirthDate) / 365 AS Age FROM Member;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO totalAge;
        IF done THEN
            LEAVE read_loop;
        END IF;
        SET count = count + 1;
        SET totalAge = totalAge + totalAge;
    END LOOP;

    CLOSE cur;

    SET averageAge = totalAge / count;
    SELECT averageAge;
END //
DELIMITER ;

##### 10. 数据备份与恢复

-- 备份数据库
mysqldump -u username -p GymDB > GymDB_backup.sql

-- 恢复数据库
mysql -u username -p GymDB < GymDB_backup.sql

#### 三、设计要求

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

#### 四、任务安排

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

#### 五、考核方式

课程的考核以考察学生数据库系统知识的掌握情况、数据库系统分析与设计能力为主要目的，以检查案例操作情况为重点内容，包括平时考核和期末考核两部分。
- 平时成绩 100 分，占总成绩的 40%；
- 期末成绩 100 分，占总成绩的 60%。

1. 平时考核重在考核学生的数据库设计能力和数据库对象使用情况，以作业（6次）和实训（4次）的形式进行考核。
2. 期末考核以学生完成大作业的方式进行。设置一个数据库综合设计的作业，每个学生单独完成。然后授课教师根据学生提交的文档的设计情况，结合该设计项目与生活实际的符合程度进行打分，对学生的知识和能力进行综合考核。