Driving-School-Management-System/final/all.sql

-- 创建数据库

-- 创建数据表
CREATE TABLE admin (
    admin_name VARCHAR(255) PRIMARY KEY,
    password VARCHAR(255) NOT NULL
);

CREATE TABLE account (
    account_id SERIAL PRIMARY KEY,
    type VARCHAR(50),
    status VARCHAR(50),
    created_by VARCHAR(255) REFERENCES admin(admin_name)
);

CREATE TABLE venue (
    venue_id SERIAL PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    capacity INT NOT NULL CHECK (capacity > 0)
);

CREATE TABLE student (
    student_id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    personal_info_id INT UNIQUE,
    FOREIGN KEY (personal_info_id) REFERENCES personal_info(info_id)
);

CREATE TABLE coach (
    coach_id VARCHAR(255) PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    password VARCHAR(255) NOT NULL,
    personal_info_id INT UNIQUE,
    FOREIGN KEY (personal_info_id) REFERENCES personal_info(info_id)
);

CREATE TABLE appointment (
    appointment_id SERIAL PRIMARY KEY,
    student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
    coach_id VARCHAR(255) NOT NULL REFERENCES coach(coach_id),
    venue_id INT NOT NULL REFERENCES venue(venue_id),
    period VARCHAR(50) NOT NULL,
    status VARCHAR(50) NOT NULL,
    feedback_id INT REFERENCES feedback(feedback_id)
);

CREATE TABLE feedback (
    feedback_id SERIAL PRIMARY KEY,
    content TEXT NOT NULL,
    date DATE NOT NULL
);

CREATE TABLE invoice (
    invoice_id SERIAL PRIMARY KEY,
    amount DECIMAL(10, 2) NOT NULL CHECK (amount >= 0),
    date DATE NOT NULL,
    appointment_id INT REFERENCES appointment(appointment_id)
);

CREATE TABLE personal_info (
    info_id SERIAL PRIMARY KEY,
    student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
    phone VARCHAR(20),
    email VARCHAR(255)
);

CREATE TABLE training_record (
    record_id SERIAL PRIMARY KEY,
    student_id VARCHAR(255) NOT NULL REFERENCES student(student_id),
    coach_id VARCHAR(255) NOT NULL REFERENCES coach(coach_id),
    feedback TEXT,
    appointment_id INT REFERENCES appointment(appointment_id)
);

CREATE TABLE booking_slot (
    slot_id SERIAL PRIMARY KEY,
    start_time TIME NOT NULL,
    end_time TIME NOT NULL CHECK (end_time > start_time),
    venue_id INT NOT NULL REFERENCES venue(venue_id)
);
-- 初始化ADMIN表
INSERT INTO admin (admin_name, password) VALUES ('admin1', 'password1');
INSERT INTO admin (admin_name, password) VALUES ('admin2', 'password2');

-- 初始化ACCOUNT表
INSERT INTO account (type, status, created_by) VALUES ('student', 'active', 'admin1');
INSERT INTO account (type, status, created_by) VALUES ('coach', 'active', 'admin1');

-- 初始化VENUE表
INSERT INTO venue (name, capacity) VALUES ('Venue A', 10);
INSERT INTO venue (name, capacity) VALUES ('Venue B', 15);

-- 初始化STUDENT表
INSERT INTO personal_info (phone, email) VALUES ('1234567890', 'student1@example.com');
INSERT INTO student (student_id, name, password, personal_info_id) VALUES ('S001', 'Student One', 'pass1', 1);

-- 初始化COACH表
INSERT INTO personal_info (phone, email) VALUES ('0987654321', 'coach1@example.com');
INSERT INTO coach (coach_id, name, password, personal_info_id) VALUES ('C001', 'Coach One', 'pass1', 2);

-- 初始化APPOINTMENT表
INSERT INTO appointment (student_id, coach_id, venue_id, period, status) VALUES ('S001', 'C001', 1, 'Morning', 'Scheduled');

-- 初始化FEEDBACK表
INSERT INTO feedback (content, date) VALUES ('Great session!', '2024-12-01');

-- 初始化INVOICE表
INSERT INTO invoice (amount, date, appointment_id) VALUES (100.00, '2024-12-01', 1);

-- 初始化TRAINING_RECORD表
INSERT INTO training_record (student_id, coach_id, feedback, appointment_id) VALUES ('S001', 'C001', 'Good progress.', 1);

-- 初始化BOOKING_SLOT表
INSERT INTO booking_slot (start_time, end_time, venue_id) VALUES ('09:00:00', '10:00:00', 1);
-- 学生信息视图
CREATE VIEW StudentInfoView AS
SELECT s.student_id, s.name, p.phone, p.email
FROM student s
JOIN personal_info p ON s.personal_info_id = p.info_id;

-- 教练信息视图
CREATE VIEW CoachInfoView AS
SELECT c.coach_id, c.name, p.phone, p.email
FROM coach c
JOIN personal_info p ON c.personal_info_id = p.info_id;

-- 预约详情视图
CREATE VIEW AppointmentDetailView AS
SELECT a.appointment_id, s.name AS student_name, c.name AS coach_name, v.name AS venue_name, a.period, a.status
FROM appointment a
JOIN student s ON a.student_id = s.student_id
JOIN coach c ON a.coach_id = c.coach_id
JOIN venue v ON a.venue_id = v.venue_id;

-- 训练记录视图
CREATE VIEW TrainingRecordView AS
SELECT t.record_id, s.name AS student_name, c.name AS coach_name, t.feedback
FROM training_record t
JOIN student s ON t.student_id = s.student_id
JOIN coach c ON t.coach_id = c.coach_id;

-- 发票详情视图
CREATE VIEW InvoiceDetailView AS
SELECT i.invoice_id, i.amount, i.date, a.student_id, a.coach_id, a.venue_id
FROM invoice i
JOIN appointment a ON i.appointment_id = a.appointment_id;
-- 在appointment表的period字段上创建索引
CREATE INDEX idx_appointment_period ON appointment(period);

-- 在feedback表的date字段上创建索引
CREATE INDEX idx_feedback_date ON feedback(date);

-- 在invoice表的date字段上创建索引
CREATE INDEX idx_invoice_date ON invoice(date);

-- 在training_record表的record_id字段上创建索引
CREATE INDEX idx_training_record_record_id ON training_record(record_id);

-- 在booking_slot表的start_time和end_time字段上创建复合索引
CREATE INDEX idx_booking_slot_start_end_time ON booking_slot(start_time, end_time);

-- 在account表的status字段上创建索引
CREATE INDEX idx_account_status ON account(status);

-- 在student表的name字段上创建索引
CREATE INDEX idx_student_name ON student(name);

-- 在coach表的name字段上创建索引
CREATE INDEX idx_coach_name ON coach(name);

-- 在venue表的name字段上创建索引
CREATE INDEX idx_venue_name ON venue(name);

-- 在personal_info表的email字段上创建唯一索引
CREATE UNIQUE INDEX idx_personal_info_email ON personal_info(email);
-- 添加新学员的存储过程
CREATE OR REPLACE FUNCTION AddNewStudent(
    _student_id VARCHAR(255),
    _name VARCHAR(255),
    _password VARCHAR(255),
    _phone VARCHAR(20),
    _email VARCHAR(255)
) RETURNS VOID AS $$
DECLARE
    _info_id INT;
BEGIN
    INSERT INTO personal_info (phone, email) VALUES (_phone, _email) RETURNING info_id INTO _info_id;
    INSERT INTO student (student_id, name, password, personal_info_id) VALUES (_student_id, _name, _password, _info_id);
END;
$$ LANGUAGE plpgsql;

-- 更新教练电话的存储过程
CREATE OR REPLACE FUNCTION UpdateCoachPhone(
    _coach_id VARCHAR(255),
    _new_phone VARCHAR(20)
) RETURNS VOID AS $$
BEGIN
    UPDATE personal_info SET phone = _new_phone WHERE info_id = (SELECT personal_info_id FROM coach WHERE coach_id = _coach_id);
END;
$$ LANGUAGE plpgsql;

-- 获取某个学生的所有预约的存储过程
CREATE OR REPLACE FUNCTION GetStudentAppointments(
    _student_id VARCHAR(255)
) RETURNS TABLE(appointment_id INT, coach_name VARCHAR(255), venue_name VARCHAR(255), period VARCHAR(50), status VARCHAR(50)) AS $$
BEGIN
    RETURN QUERY
    SELECT a.appointment_id, c.name AS coach_name, v.name AS venue_name, a.period, a.status
    FROM appointment a
    JOIN coach c ON a.coach_id = c.coach_id
    JOIN venue v ON a.venue_id = v.venue_id
    WHERE a.student_id = _student_id;
END;
$$ LANGUAGE plpgsql;

-- 获取某个教练的所有反馈的存储过程
CREATE OR REPLACE FUNCTION GetCoachFeedbacks(
    _coach_id VARCHAR(255)
) RETURNS TABLE(feedback_id INT, content TEXT, date DATE) AS $$
BEGIN
    RETURN QUERY
    SELECT f.feedback_id, f.content, f.date
    FROM feedback f
    JOIN training_record t ON f.feedback_id = t.feedback_id
    WHERE t.coach_id = _coach_id;
END;
$$ LANGUAGE plpgsql;

-- 获取某个场地的所有可用时间段的存储过程
CREATE OR REPLACE FUNCTION GetAvailableSlotsForVenue(
    _venue_id INT
) RETURNS TABLE(slot_id INT, start_time TIME, end_time TIME) AS $$
BEGIN
    RETURN QUERY
    SELECT bs.slot_id, bs.start_time, bs.end_time
    FROM booking_slot bs
    LEFT JOIN appointment a ON bs.venue_id = a.venue_id AND bs.start_time <= a.period::time AND bs.end_time >= a.period::time
    WHERE bs.venue_id = _venue_id AND a.appointment_id IS NULL;
END;
$$ LANGUAGE plpgsql;
-- 当插入新的学生时，自动在personal_info表中插入一条记录
CREATE OR REPLACE FUNCTION InsertPersonalInfoOnStudentInsert()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO personal_info (phone, email) VALUES ('', '');
    NEW.personal_info_id := currval('personal_info_info_id_seq');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_insert_personal_info_on_student_insert
BEFORE INSERT ON student
FOR EACH ROW EXECUTE FUNCTION InsertPersonalInfoOnStudentInsert();

-- 当删除一个教练时，自动删除其关联的personal_info记录
CREATE OR REPLACE FUNCTION DeletePersonalInfoOnCoachDelete()
RETURNS TRIGGER AS $$
BEGIN
    DELETE FROM personal_info WHERE info_id = OLD.personal_info_id;
    RETURN OLD;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_delete_personal_info_on_coach_delete
AFTER DELETE ON coach
FOR EACH ROW EXECUTE FUNCTION DeletePersonalInfoOnCoachDelete();

-- 当插入新的预约时，检查是否已经有重叠的时间段
CREATE OR REPLACE FUNCTION CheckOverlapBeforeAppointmentInsert()
RETURNS TRIGGER AS $$
DECLARE
    overlap_count INT;
BEGIN
    SELECT COUNT(*) INTO overlap_count
    FROM appointment a
    WHERE a.venue_id = NEW.venue_id
      AND ((a.period = NEW.period)
       OR (a.period < NEW.period AND a.period + INTERVAL '1 hour' > NEW.period)
       OR (a.period > NEW.period AND a.period < NEW.period + INTERVAL '1 hour'));
    IF overlap_count > 0 THEN
        RAISE EXCEPTION 'There is an overlapping appointment at the same time and place.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_check_overlap_before_appointment_insert
BEFORE INSERT ON appointment
FOR EACH ROW EXECUTE FUNCTION CheckOverlapBeforeAppointmentInsert();

-- 当更新发票金额时，确保金额不小于零
CREATE OR REPLACE FUNCTION EnsureInvoiceAmountNonNegative()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.amount < 0 THEN
        RAISE EXCEPTION 'Invoice amount cannot be negative.';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_ensure_invoice_amount_non_negative
BEFORE UPDATE ON invoice
FOR EACH ROW EXECUTE FUNCTION EnsureInvoiceAmountNonNegative();
-- 创建角色
CREATE ROLE AdminRole LOGIN PASSWORD 'admin_password';
CREATE ROLE CoachRole LOGIN PASSWORD 'coach_password';
CREATE ROLE StudentRole LOGIN PASSWORD 'student_password';

-- 授予AdminRole权限
GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA public TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE account_account_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE venue_venue_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE student_student_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE coach_coach_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE appointment_appointment_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE feedback_feedback_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE invoice_invoice_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE personal_info_info_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE training_record_record_id_seq TO AdminRole;
GRANT USAGE, SELECT, UPDATE ON SEQUENCE booking_slot_slot_id_seq TO AdminRole;

-- 授予CoachRole权限
GRANT SELECT, INSERT, UPDATE ON appointment TO CoachRole;
GRANT SELECT, INSERT, UPDATE ON feedback TO CoachRole;
GRANT SELECT, INSERT, UPDATE ON training_record TO CoachRole;

-- 授予StudentRole权限
GRANT SELECT, INSERT, UPDATE ON appointment TO StudentRole;
GRANT SELECT, INSERT, UPDATE ON feedback TO StudentRole;
GRANT SELECT, INSERT, UPDATE ON training_record TO StudentRole;
GRANT SELECT ON invoice TO StudentRole;