数据库物理结构设计（高校竞赛管理系统）

管理员（账号，密码，用户名）

学生（学号，用户名，密码，邮箱）

比赛（赛号，赛事名称，指导老师，简介）

比赛结果（赛号，学号，学生姓名，赛事名称，指导老师，成绩）

参赛（队号，赛号，成绩）​​​​​​​

1. 1. 视图设计

E-R图：

外模式（用户模式）的设计，为了向用户提供友好的用户界面，需要设计一些视图。首先需要确定有三种用户：老师，学生，然后是视图已经可查询视图的用户

1. 竞赛基本信息视图 （竞赛名称，赛号，指导老师，简介）-管理员，学生查看赛事
2. 学生基本信息视图（学号，学生姓名，学生密码，学生邮箱）--管理员，学生注册
3. 参赛基本信息视图（序号，赛事名称，指导老师，简介）--学生查看已报名竞赛
4. 成绩基本信息视图（序号，学生姓名，赛事名称，指导老师，成绩）--管理员，学生（查看权限）

   高校竞赛管理系统物理结构设计

1. 1. 数据库表结构的设计

物理结构设计的目的是为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构。高校竞赛管理系统数据库中各个表结构设计结果见表。每个表表示在数据库中的一张表。 表1 学生 表

字段名	数据类型	长度	是否空	备注
学号	char	10	Not null	主键
姓名	varchar	20
年龄	smallint		Not null
性别	char	4
专业 参赛队号	varcher char	20 8	Nut null	外键

表1.1 学生表数据

学号	姓名	年龄	性别	专业	团队
20230001	柳雨晴	20	男	计算机	团队A
20230002	宋心如	22	女	软件工程	团队B
20230003	花梦华	21	男	信息管理	团队C
20230004	刘宇航	19	女	电子工程	团队A
20230005	陈思婷	23	男	计算机	团队C
20230006	李浩然	20	女	软件工程	团队B
20230007	张雨萌	21	男	信息管理	团队A
20230008	王文彦	22	男	电子工程	团队C
20230009	黄明阳	20	女	计算机	团队B
20230010	赵丽娜	21	男	软件工程	团队A
20230011	程思琪	22	男	信息管理	团队C
20230012	周晨曦	19	女	电子工程	团队B
20230013	徐晓宇	20	男	计算机	团队A
20230014	杨静雯	21	女	软件工程	团队C
20230015	郭梦琪	22	男	信息管理	团队B

表2 信息办老师 表

字段名	数据类型	长度	是否空	备注
工号	char	8	Not null	主键
姓名	varchar	20
年龄	smallint
性别	char	4	Not null
专业	varchar	20	Not null

表2.2 信息办老师表数据

工号	姓名	年龄	性别	专业
X000001	李晓涵	35	男	计算机
X000002	张雨薇	39	女	软件工程
X000003	王雅琪	40	男	物联网
X000004	赵心怡	41	女	电子工程
X000005	孙悦然	37	男	计算机
X000006	周思涵	29	女	软件工程
X000007	吴梦琪	35	男	人工智能
X000008	郑欣怡	28	女	电子工程
X000009	陈诗涵	30	男	计算机
X000010	高若菲	46	女	信息安全
X000012	郭佳怡	33	男	信息管理
X000013	何婉清	45	女	电子工程
X000015	胡语嫣	36	男	计算机
X000015	冯雪萍	56	女	软件工程

表3 竞赛信息 表

字段名	数据类型	长度	是否空	备注
赛号	char	6	Not null	主键
赛事名称	varchar	20	Not null
申报人工号	char	8	Not null	外键
报名时间	datetime		Not null
比赛时间 比赛地点 申报状态 申报等级	datetime varchar char char	20 8 4	Not null Not null Not null	外键

表3.3 竞赛信息表数据表

赛号	赛事名称	申报 人工号	报名时间	比赛时间	比赛地点	申报状态	申报等级
S0001	数学竞赛	SX1001	2024/7/10	2025/4/20	镇江	已审核	B
S0002	物理竞赛	WL1002	2024/5/15	2024/10/25	镇江	拒绝	B+
S0003	英语竞赛	YY1003	2024/8/24	2024/1/20	徐州	待审核	A
S0004	互联网 +	IN1004	2024/9/5	2025/5/16	南京	已审核	A+
S0005	数学建模	JM1005	2024/8/15	2025/4/15	南京	已审核	A
S0006	创新大赛	CX1006	2024/5/30	2024/11/25	南京	待审核	A-

表4 评审老师 表

字段名	数据类型	长度	是否空	备注
工号	char	8	Not null	主键
姓名	varchar	20
年龄	smallint
性别	char	4
专业 赛号	varchar char	20 6	Not null

表4.4 评审老师表数据表

工号	姓名	年龄	性别	专业	赛号
P000001	刘宇	35	男	数学分析	S0001
P000002	李华	37	男	经济管理	S0001
P000003	王翔慧	41	女	人工智能	S0004
P000004	李木子	29	女	声学	S0002
P000005	何可人	48	女	软件工程	S0005
P000006	张长弓	39	男	信息与计算科学	S0006
P000007	黄煜	46	男	计算机科学与技术	S0006
P000008	刘钊	36	男	英语教育专业	S0003
P000009	李凤来	32	女	翻译专业	S0003

表5 评分标准 表

物理结构设计的目的是为一个给定的逻辑数据模型选取一个最适合应用环境的物理结构。高校竞赛管理系统数据库中各个表结构设计结果见表。每个表表示在数据库中的一张表。 表1 竞赛信息表 字段名 数据类型 长度 是否空 备注 cid float Not null 主键 Cname nvarchar 225 Not null Lteacher nvarchar 225 Not null Detail nvarchar 225 Not null 表1.1 竞赛信息表数据 cid Cname Lteacher Detail 202410 大学生物流设计大赛 黎明 2024.07.21-07.25于重庆举办 202411 大学生电子商务 天空 2024.09.21-09.25于天津举办 202412 大学生制药工程设计大赛 皇后 2024.11.01-11.03于北京举办 202413 大学生模拟法庭竞赛 起始 2024.12.01-12.03于河口举办 202414 国际商事仲裁模以仲裁庭 张三 2024.05.11-05.13于云南举办 202415 大学生机构设计大赛 里斯 2024.06.01-07.25于南京举办 202416 大学生工程设计大赛 齐柏林 2024.12.11-12.26于南京举办 表2 学生表 字段名 数据类型 长度 是否空 备注 sid float Not null 主键 sname nvarchar 225 Not null pwd float Not null email nvarchar 225 Not null 表2.2学生表数据 sid sname pwd email 2224589 艾莉 123456 12345678931qq.com 2224678 孔建 123456 12345678914qq.com 2324513 莫里 123456 12345678985qq.com 2124478 七四 123456 12345648931qq.com 2224608 三起 123456 12345668914qq.com 2324510 二黄 123456 12345698985qq.com 2127478 斯地 123456 12345699931qq.com 2224978 刘噢 123456 12345687914qq.com 2324553 刘吖 123456 12345678115qq.com 2125378 刘篇 123456 11145678931qq.com 2224698 王数 123456 12345672214qq.com 2327513 王里 123456 12345644985qq.com 2125471 李偶 123456 12345673331qq.com 2223678 张花球 123456 12345671314qq.com 表3 管理 表 字段名 数据类型 长度 是否空 备注 id float Not null 主键 pwd float Not null username nvarchar 225 Not null 表3.3 管理 表 id pwd username 1114 123456 刘芬 1122 123456 亲启 1134 123456 王数 1156 123456 淑芬 表4 比赛结果 表 字段名 数据类型 长度 是否空 备注 sid float Not null 主键/外键 cid float Not null sname nvarchar 225 Not null Cname nvarchar 225 Not null Lteather nvarchar 225 Not null grade float Not null 表4.4 比赛结果表 sid cid sname Cname Lteacher grade 2224589 202410 艾莉 大学生物流设计大赛 黎明 95 2224678 202411 孔建 大学生电子商务 天空 86 2324513 202412 莫里 大学生制药工程设计大赛 皇后 77 2124478 202413 七四 大学生模拟法庭竞赛 起始 91 2224608 202414 三起 国际商事仲裁模以仲裁庭 张三 75 2324510 202415 二黄 大学生机构设计大赛 里斯 81 2127478 202410 斯地 大学生工程设计大赛 齐柏林 89 2224978 202411 刘噢 大学生物流设计大赛 黎明 79 2324553 202412 刘吖 大学生电子商务 天空 90

1. 1. 索引的建立

物理结构设计的任务之一是根据数据库管理系统支持的存取方式确定选取，常见的存取方式为索引和聚簇方法。

1. 聚簇索引 

设计聚簇，一般来说需要考虑以下条件：经常使用连接操作的关系；一个关系的一组属性经常出现在相等比较条件中；一个关系的一个或者一组属性值重复率高；不经常更新的表中属性。

在MySQL中，聚簇索引（Clustered Index）是指数据行按照索引键的值物理上排序存储的索引。一个表只能有一个聚簇索引。当您创建一个聚簇索引时，MySQL会重新组织表的数据文件，以使数据行按照聚簇索引键的值排序。

以下是聚簇索引的列表：

1.学生表的学号字段：因为学号是主键，MySQL会自动将主键列作为聚簇索引。

2.竞赛信息表的赛号字段：赛号是主键，因此它也是一个聚簇索引。

其他索引，如外键索引和复合索引，不是聚簇索引。它们用于加速查询操作，但不会影响数据的物理存储顺序。

1. 索引设计 

对于索引的建立，一般来说需要考虑下面的条件： (1)一个或者一组属性经常出现在查询条件中

1. 属性经常作为聚集函数的参数(3)属性经常出现在连接条件中 

1. 属性的属性值变动大，数据不集中(5)基于上诉原则，我们建立以下索引:

学生表（学生表1.1）：

对于主键学号，可以创建主键索引（CREATE INDEX idx_student_sid ON 学生表 (学号)）。

对于外键，如团队字段，如果经常用于查询，可以创建外键索引（CREATE INDEX idx_team ON 学生表 (团队)）。

管理表（表2.2）：

对于主键工号，创建主键索引（CREATE INDEX idx_info_admin_id ON 管理表 (工号)）。

竞赛信息表（表3.3）：

对于主键比赛号，创建主键索引（CREATE INDEX idx_competition_cid ON 竞赛信息 (赛号)）。

竞赛成果表（表4.4）：

对于主键比赛号和学号，创建主键索引（CREATE INDEX idx_sid_reviewer_cid ON 竞赛成果表 (比赛号和学号)）。

对于外键赛号，创建外键索引（CREATE INDEX idx_competition_reviewer_cid ON  竞赛信息表(塞号)）,创建外键索引（CREATE INDEX idx_competition_reviewer_sid ON  学生表(学号)）。

触发器：

根据需求，我们可能需要创建以下类型的触发器：

在学生报名时创建或更新触发器来记录活动。

在竞赛信息提交时创建或更新触发器来审核竞赛信息。

在评审成绩提交时创建或更新触发器来计算和更新队伍的最终得分和获奖等级。

1. 学生报名触发器：

1. DELIMITER //
2. CREATE TRIGGER after_student_enroll
3. AFTER INSERT ON student
4. FOR EACH ROW
5. BEGIN
6. insert into sc(sid,sname)values(学号，姓名)
7. END
8. //
9. DELIMITER ;

1. 竞赛报名触发器

DELIMITER //

CREATE TRIGGER after_competition_Application

BEFORE UPDATE ON 竞赛报名视图

FOR EACH ROW

BEGIN

    insert into sc(cid,Cname,Lteacher)values(比赛号，比赛名，负责老师)

END;

//

DELIMITER ;

简单存储结构实现：

-- 学生表

CREATE TABLE 学生表 (

    学号 CHAR(10) PRIMARY KEY NOT NULL,

    用户名 VARCHAR(20)，

    密码 VARCHAR(40),

    邮箱 VARCHAR(50)

);

-- 竞赛信息表

CREATE TABLE 竞赛信息表 (

    赛号 Float PRIMARY KEY NOT NULL,

    赛事名称 VARCHAR(20),

    赛事简介 VARCHAR(225) ,

    负责老师 VARCHAR(20)

);

-- 管理员表

CREATE TABLE 管理员 (

    账号 CHAR(8) PRIMARY KEY NOT NULL,

    姓名 VARCHAR(20) ,

    密码 VARCHAR(40)

);

-- 成绩表

CREATE TABLE 成绩 (

    赛号 CHAR(6) NOT NULL,

    学号 CHAR(10) NOT NULL,

    学生姓名 VARCHAR(20) ,

    赛事名称 VARCHAR(20),

    负责老师 VARCHAR(20) ,

    成绩 SMALLINT ,

    PRIMARY KEY (赛号, 学号)

);