家教管理系统 数据库设计

一、需求分析

社会的不断发展和进步，人们对于教育的重视程度不断提高，家教这种教育方式也越来越受到人们的青睐。同时，随着互联网技术的普及和发展，人们的生活方式和消费习惯也发生了很大的变化。越来越多的人开始通过网络寻找家教，这种新的方式也给家教市场带来了很大的变化和机遇。然而，家教市场的发展也面临着一些挑战和问题。传统的家教市场存在着信息不对称、家教质量参差不齐、交易安全等问题。同时，传统的家教市场也存在着信息化程度不高、效率低下等问题。因此，需要建立一种新型的家教管理系统，通过互联网技术来解决这些问题，提高家教市场的效率和质量。根据家教管理系统的业务活动和用户需求，可以得到以下信息要求和功能处理要求：

信息要求：

学生信息：包括姓名、年龄、性别、联系方式、家庭住址等基本信息；

家长信息：包括姓名、联系方式等基本信息；

家教信息：包括姓名、年龄、性别、教育经历、联系方式等基本信息；

课程信息：包括课程名称、价格、描述等基本信息；

上课记录信息：包括学生、家教、课程、时间、地点等基本信息；

支付记录信息：包括学生、家教、课程、支付金额、支付时间等基本信息。

功能处理要求：

学生管理：包括学生信息的添加、修改、查询和删除等功能；

家长管理：包括家长信息的添加、修改、查询和删除等功能；

家教管理：包括家教信息的添加、修改、查询和删除等功能；

课程管理：包括课程信息的添加、修改、查询和删除等功能；

上课记录管理：包括上课记录信息的添加、修改、查询和删除等功能；

支付记录管理：包括支付记录信息的添加、查询和删除等功能；

数据统计：包括统计每个家教的课时数、收入等信息。

安全性要求：

对用户的密码进行加密存储；

对用户的输入数据进行有效性检验，避免SQL注入等攻击；

对数据库进行定期备份和恢复，防止数据丢失和损坏；

对系统进行权限管理，确保用户只能访问其具备权限的数据。

完整性要求：

确保每个学生、家长、家教和课程具备唯一的标识，避免重复数据的出现；

确保每个上课记录和支付记录都有对应的学生、家教和课程信息，避免数据的不一致性。

根据上述信息要求和功能处理要求，可以确定该家教管理系统的子系统包括：

学生管理子系统：包括学生信息的添加、修改、查询和删除等功能；

家长管理子系统：包括家长信息的添加、修改、查询和删除等功能；

家教管理子系统：包括家教信息的添加、修改、查询和删除等功能；

课程管理子系统：包括课程信息的添加、修改、查询和删除等功能；

上课记录管理子系统：包括上课记录信息的添加、修改、查询和删除等功能；

支付记录管理子系统：包括支付记录信息的添加、查询和删除等功能；

数据统计子系统：包括统计每个家教的课时数、收入等信息的功能。系统还需要一个用户认证和权限管理子系统，用于对用户进行身份认证和权限管理，确保只有经过授权的用户才能访问系统中的数据。同时，需要一个数据备份和恢复子系统，用于对数据库进行定期备份和恢复，确保数据的完整性和安全性。

3.数据流图

 

4.数据字典

1.学生信息

字典值	数据类型	说明
学生ID	整数	主码，学生唯一标识
姓名	字符串	学生姓名
年龄	整数	学生年龄
性别	字符串	学生性别
家庭住址	字符串	学生家庭住址
家长ID	整数	外码，家长唯一标识

2：家长信息

字典值	数据类型	说明
家长ID	整数	主码，家长唯一标识
姓名	字符串	家长姓名
年龄	整数	家长年龄
性别	字符串	家长性别
电话	字符串	家长联系电话

3：家教信息

字典值	数据类型	说明
家教ID	整数	主码，家教唯一标识
姓名	字符串	家教姓名
年龄	整数	家教年龄
性别	字符串	家教性别
教育经历	字符串	家教教育经历
电话	字符串	家教联系电话
课程ID	整数	外码，课程唯一标识

4：课程信息

字典值	数据类型	说明
课程ID	整数	主码，课程唯一标识
课程名	字符串	课程名称
价格	浮点数	课程价格
描述	字符串	课程描述

5：上课记录信息

字典值	数据类型	说明
记录ID	整数	主码，记录唯一标识
学生ID	整数	外码，学生唯一标识
家教ID	整数	外码，家教唯一标识
开始时间	时间日期
结束时间	时间日期
上课地点	字符串

6：支付记录信息

字典值	数据类型	说明
记录ID	整数	主码，记录唯一标识
学生ID	整数	外码，学生唯一标识
家教ID	整数	外码，家教唯一标识
课程ID	整数	外码，课程唯一标识
金额	浮点数	支付金额
支付时间	时间	支付时间

二、概念结构设计

 

三、逻辑结构设计

实体集合转换为关系模型：将实体集合中的每个实体转换为一个关系模型，实体的每个属性对应关系模型的一个属性，实体的主键对应关系模型的主键。

一对多关系的转换：对于一对多关系，将“多”的一方的主键添加到“一”的一方的关系模型中作为外键。

多对多关系的转换：对于多对多关系，需要创建一个新的关系模型，包含两个关系模型的主键，这个新的关系模型的主键由两个外键组成。关系模型转换如下：

家长表 (家长ID,姓名,年龄,性别,电话)

学生表 (学生ID,姓名,年龄,性别,家庭住址,家长ID)

课程表(课程ID,课程名,价格,描述)

家教表(家教ID,姓名,年龄,性别,教育经历,电话,课程ID)

上课记录表(记录ID,学生ID,家教ID,课程ID,开始时间,结束时间,上课地点)

支付记录表(记录ID,学生ID,家教ID,课程ID,金额,支付时间)

-- 创建数据库
CREATE DATABASE spteachmg;
USE spteachmg;

-- 创建家长表
CREATE TABLE parents (
  parent_id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  age INT  NOT NULL,
  gender VARCHAR(10),
  phone VARCHAR(20)
);

-- 创建学生表
CREATE TABLE students (
  student_id INT PRIMARY KEY,
  name VARCHAR(50)  NOT NULL,
  age INT,
  gender VARCHAR(10),
  address VARCHAR(100),
  parent_id INT,
  FOREIGN KEY (parent_id) REFERENCES parents(parent_id)
);
-- 创建课程表
CREATE TABLE courses (
  course_id INT PRIMARY KEY,
  name VARCHAR(50)  NOT NULL,
  price DECIMAL(10,2),
  description VARCHAR(200)
);
-- 创建家教表
CREATE TABLE tutors (
  tutor_id INT PRIMARY KEY,
  name VARCHAR(50)  NOT NULL,
  age INT,
  gender VARCHAR(10),
  education VARCHAR(100),
  phone VARCHAR(20),
  course_id INT,
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 创建上课记录表
CREATE TABLE class_records (
  record_id INT PRIMARY KEY,
  student_id INT,
  tutor_id INT,
  course_id INT,
  start_time DATETIME,
  end_time DATETIME,
  location VARCHAR(100),
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (tutor_id) REFERENCES tutors(tutor_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 创建支付记录表
CREATE TABLE payment_records (
  record_id INT PRIMARY KEY,
  student_id INT,
  tutor_id INT,
  course_id INT,
  amount DECIMAL(10,2),
  payment_time DATETIME,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  FOREIGN KEY (tutor_id) REFERENCES tutors(tutor_id),
  FOREIGN KEY (course_id) REFERENCES courses(course_id)
);

-- 家长表
INSERT INTO parents VALUES
  (1, '张三的父亲', 40, '男', '13800138001'),
  (2, '李四的母亲', 38, '女', '13800138002'),
  (3, '王五的父亲', 42, '男', '13800138003'),
  (4, '赵六的母亲', 36, '女', '13800138004'),
  (5, '钱七的父亲', 44, '男', '13800138005'),
  (6, '孙八的母亲', 40, '女', '13800138006'),
  (7, '周九的父亲', 42, '男', '13800138007'),
  (8, '吴十的母亲', 38, '女', '13800138008'),
  (9, '郑一的父亲', 40, '男', '13800138009'),
  (10, '王二的母亲', 44, '女', '13800138010');

-- 学生表
INSERT INTO students VALUES
  (1, '张三', 10, '男', '北京市海淀区', 1),
  (2, '李四', 12, '女', '北京市朝阳区', 2),
  (3, '王五', 11, '男', '北京市西城区', 3),
  (4, '赵六', 9, '女', '北京市东城区', 4),
  (5, '钱七', 13, '男', '北京市丰台区', 5),
  (6, '孙八', 10, '女', '北京市海淀区', 6),
  (7, '周九', 11, '男', '北京市朝阳区', 7),
  (8, '吴十', 12, '女', '北京市西城区', 8),
  (9, '郑一', 9, '男', '北京市东城区', 9),
  (10, '王二', 13, '女', '北京市丰台区', 10);

INSERT INTO courses (course_id, name, price, description)
VALUES
(1, '数学', 100.00, '包含初中数学和高中数学'),
(2, '英语', 120.00, '包含英语听说读写'),
(3, '编程', 150.00, '包含Python和Java编程语言'),
(4, '物理', 90.00, '初中和高中物理'),
(5, '化学', 90.00, '初中和高中化学'),
(6, '历史', 80.00, '初中和高中历史'),
(7, '地理', 80.00, '初中和高中地理'),
(8, '政治', 80.00, '初中和高中政治'),
(9, '生物', 90.00, '初中和高中生物'),
(10, '音乐', 70.00, '钢琴、吉他、小提琴等音乐课程');

-- 家教表
INSERT INTO tutors VALUES
  (1, '李老师', 30, '女', '北京大学', '13800138011', 1),
  (2, '张老师', 28, '男', '清华大学', '13800138012', 2),
  (3, '王老师', 32, '男', '北京师范大学', '13800138013', 3),
  (4, '赵老师', 29, '女', '北京航空航天大学', '13800138014', 4),
  (5, '钱老师', 31, '男', '中国人民大学', '13800138015', 5),
  (6, '孙老师', 30, '女', '北京大学', '13800138015', 6);

-- 插入上课记录数据
INSERT INTO class_records (record_id, student_id, tutor_id, course_id, start_time, end_time, location)
VALUES
(1, 1, 1, 1, '2023-05-08 14:00:00', '2023-05-08 15:30:00', '学生家中'),
(2, 2, 2, 2, '2023-05-07 16:00:00', '2023-05-07 17:30:00', '学生家中'),
(3, 3, 3, 3, '2023-05-06 10:00:00', '2023-05-06 12:00:00', '线上课堂'),
(4, 4, 4, 4, '2023-05-05 13:00:00', '2023-05-05 14:30:00', '学生家中'),
(5, 5, 5, 5, '2023-05-04 15:00:00', '2023-05-04 16:30:00', '学生家中'),
(6, 6, 3, 6, '2023-05-03 10:00:00', '2023-05-03 11:30:00', '线上课堂'),
(7, 7, 2, 7, '2023-05-02 16:00:00', '2023-05-02 17:30:00', '学生家中'),
(8, 8, 1, 8, '2023-05-01 14:00:00', '2023-05-01 15:30:00', '学生家中');

-- 插入10条支付记录数据
INSERT INTO payment_records (record_id, student_id, tutor_id, course_id, amount, payment_time) VALUES
(1, 1, 1, 1, 100, '2023-05-01 09:00:00'),
(2, 2, 1, 2, 120, '2023-05-02 10:00:00'),
(3, 3, 2, 1, 150, '2023-05-03 11:00:00'),
(4, 4, 2, 2, 200, '2023-05-04 12:00:00'),
(5, 5, 3, 1, 180, '2023-05-05 13:00:00'),
(6, 6, 3, 2, 160, '2023-05-06 14:00:00'),
(7, 7, 4, 1, 170, '2023-05-07 15:00:00'),
(8, 8, 4, 2, 220, '2023-05-08 16:00:00'),
(9, 9, 5, 1, 190, '2023-05-09 17:00:00'),
(10, 10, 5, 2, 240, '2023-05-10 18:00:00');

-- 1.在 parents 表的 phone 列上创建索引：
CREATE INDEX idx_parents_phone ON parents(phone);

-- 2.在 students 表的 parent_id 列上创建索引：
CREATE INDEX idx_students_parent_id ON students(parent_id);

-- 3.在 tutors 表的 course_id 列上创建索引：
CREATE INDEX idx_tutors_course_id ON tutors(course_id);

-- 4.在 courses 表的 price 列上创建索引：
CREATE INDEX idx_courses_price ON courses(price);

-- 5.在 class_records 表的 student_id 列上创建索引：
CREATE INDEX idx_class_records_student_id ON class_records(student_id);

-- 6.在 payment_records 表的 payment_time 列上创建索引：
CREATE INDEX idx_payment_records_payment_time ON payment_records(payment_time);

-- 为这些表创建视图：
-- 创建一个 parents_with_children 视图，该视图显示每个家长的姓名、电话和孩子数量：
CREATE VIEW parents_with_children AS
SELECT p.name, p.phone, COUNT(s.student_id) AS num_children
FROM parents p
LEFT JOIN students s ON p.parent_id = s.parent_id
GROUP BY p.parent_id;

-- 创建一个 students_with_parents 视图，该视图显示每个学生的姓名、家长姓名和家长电话：
CREATE VIEW students_with_parents AS
SELECT s.name AS student_name, p.name AS parent_name, p.phone AS parent_phone
FROM students s
LEFT JOIN parents p ON s.parent_id = p.parent_id;

-- 创建一个 tutors_with_courses 视图，该视图显示每个家教的姓名、授课课程名称和价格：
CREATE VIEW tutors_with_courses AS
SELECT t.name AS tutor_name, c.name AS course_name, c.price
FROM tutors t
LEFT JOIN courses c ON t.course_id = c.course_id;

-- 创建一个 class_records_with_students 视图，该视图显示每个上课记录的开始时间、学生姓名和家教姓名：
CREATE VIEW class_records_with_students AS
SELECT cr.start_time, s.name AS student_name, t.name AS tutor_name
FROM class_records cr
LEFT JOIN students s ON cr.student_id = s.student_id
LEFT JOIN tutors t ON cr.tutor_id = t.tutor_id;

-- 创建一个 payment_records_with_courses 视图，该视图显示每个支付记录的支付时间、课程名称和价格：
CREATE VIEW payment_records_with_courses AS
SELECT pr.payment_time, c.name AS course_name, c.price AS course_price
FROM payment_records pr
LEFT JOIN courses c ON pr.course_id = c.course_id;

-- 创建一个 students_paid_amount 视图，该视图显示每个学生的姓名和已支付的总金额：
CREATE VIEW students_paid_amount AS
SELECT s.name AS student_name, SUM(pr.amount) AS total_paid
FROM students s
LEFT JOIN payment_records pr ON s.student_id = pr.student_id
GROUP BY s.student_id;