一、引言

1. 编写目的

本文档是概要设计文档的组成部分，编写数据库设计文档的目的是：明确数据库的表名、字段名等数据信息，用来指导后期数据库脚本的开发。本文档的读者对象是需求人员、系统设计人员、开发人员、测试人员。
2. ### 术语表

序号	术语或缩略语	说明性定义
1	team	球队
2	player	队员
3	schedule	赛程表
4	scoreofteam	球队积分榜
5	goalofpalyer	队员进球信息
6	judge	裁判处罚信息
7	shooterlist	队员射手榜

二、需求规约

1. 业务描述

1.1数据库创建的背景

足球比赛往往会产生许多的比赛数据，如球队的比分，进球数、失球数、球员的罚单信息等。除此之外，足球比赛往往还会涉及球队的基本信息以及球员的基本信息。这些信息少则几千条，多则几万条。为了对比赛数据进行有效地分类管理以及对比赛数据的后期计算，可以考虑使用数据库进行数据处理。

1.2 数据库系统要解决的问题

​ (1) 对比赛数据进行分类管理。

​ (2) 建立数据集之间的联系。

​ (3) 设计算法，完成数据的计算。

1.3 数据库系统要完成的业务流程

2. 需求分析

2.1 业务的内在联系

足球比赛的数据表之间的关系的实现依赖于触发器的建立，触发器根据数据表数据的插入、修改、删除，触发不同的事件。触发器所依赖的算法，由存储过程单独来实现。

2.2 创建数据库系统的目的、目标

创建数据的目的是为了更好的存储和管理数据，又由于数据库具有非常方便插入、修改、删除、查询等功能，所以建立数据库系统是处理数据的较好选择。

三、数据库环境说明

数据库实例	数据库系统	数据库部署环境	数据库设计工具	数据库存放位置	说明
	Mysql,版本8.0	软硬件、网络环境	PowerDesigner

四、数据库的命名规则

1.数据库对象命名规则

所有的数据库命名都是以模块的缩写加上具体表的英文词汇组成，这样能够统一数据库表的命名，也能够更好的规范数据库表命名。

2.数据项编码规则

组别采用大写英文字母A、B、C、D进行编码。

比赛轮数采用数字1-15进行编码。

组别+比赛轮数可以唯一确定一场比赛。

五、概念结构设计

足球联赛评分系统的实体-属性图。

图1 球队实体-属性图

图2 球员实体-属性图

图3 赛程实体-属性图

图4 积分榜实体-属性图

图5 球员进球实体-属性图

图6 裁判惩罚实体-属性图

图7 射手榜实体-属性图

六、逻辑结构设计

足球联赛评分系统的E-R图。

图8 足球联赛评分系统的E-R图

七、物理结构设计

1. 表汇总

2. 表

表 1

表 2

表 3

表 4

表 5

表 6

表 7

触发器一: 插入球队触发插入积分榜。当向球队表Team中插入一行数据时，会自动向积分榜scoreofteam中插入一条对应的数据。

create trigger teaminsert   

after insert on team   

for each row   

insert into scoreofteam(ranking,changes,teamname,matchs,win,even,beaten,goal,lost,net,avergoal,averlost,avernet,averpoint) values(0,0,new.name,0,0,0,0,0,0,0,0,0,0,0);

触发器二: 删除球队触发删除射手榜、球员、比赛进程、积分榜。

delimiter $  
create trigger teamdel   
before delete on team  
for each row  
begin  
delete from shooterlist where teamname=old.name;  
delete from player where team=old.name;  
delete from schedule where hometeam=old.name or visitingteam=old.name;  
delete from scoreofteam where teamname=old.name;  
end$  
delimiter ;  

触发器三: 插入比赛进程时调整积分榜。

delimiter $  
create trigger schedule_insert before insert on schedule for each row  
begin  
if not exists(select teamname from scoreofteam where teamname=new.hometeam) then  
    insert into scoreofteam values(0,0,new.hometeam,0,0,0,0,0,0,0,0,0,0,0);  
end if;  
if not exists(select teamname from scoreofteam where teamname=new.visitingteam) then  
    insert into scoreofteam values(0,0,new.visitingteam,0,0,0,0,0,0,0,0,0,0,0);  
end if;  
if new.homescore>new.visitingscore then  
        update scoreofteam  
        set  
            matchs=matchs+1,  
            win=win+1,  
            goal=goal+new.homescore,  
            lost=lost+new.visitingscore,  
            net=net+(new.homescore-new.visitingscore),  
            avergoal = (goal * 1.0) / matchs,   
            averlost = (lost * 1.0)/ matchs,  
            avernet = (net * 1.0)/ matchs,  
            averpoint = (win * 3.0 + even)/ matchs  
        where teamname=new.hometeam;  
        update scoreofteam  
        set  
            matchs=matchs+1,  
            beaten = beaten+1,  
            goal = goal+new.visitingscore,  
            lost = lost+new.homescore,  
            net=net+(new.visitingscore-new.homescore),  
            avergoal = (goal * 1.0) / matchs,   
            averlost = (lost * 1.0)/ matchs,  
            avernet = (net * 1.0)/ matchs  
        where teamname=new.visitingteam;  
elseif new.homescore=new.visitingscore then  
        update scoreofteam  
        set   
            matchs=matchs+1,  
            even=even+1,  
            goal=goal+new.homescore,  
            lost=lost+new.visitingscore,  
            avergoal = (goal * 1.0) / matchs,   
            averlost = (lost * 1.0)/ matchs,  
            averpoint = (win * 3.0 + even)/ matchs  
        where teamname=new.hometeam;  
        update scoreofteam  
        set   
            matchs=matchs+1,  
            even=even+1,  
            goal=goal+new.visitingscore,  
            lost=lost+new.homescore,  
            avergoal = (goal * 1.0) / matchs,   
            averlost = (lost * 1.0)/ matchs,  
            averpoint = (win * 3.0 + even)/ matchs  
        where teamname=new.visitingteam;  
else   
        update scoreofteam  
        set   
            matchs=matchs+1,  
            beaten=beaten+1,  
            goal=goal+new.homescore,  
            lost=lost+new.visitingscore,  
            net=net+(new.homescore-new.visitingscore),  
            avergoal = (goal * 1.0) / matchs,   
            averlost = (lost * 1.0)/ matchs,  
            avernet = (net * 1.0)/ matchs  
        where teamname=new.hometeam;  
        update scoreofteam  
        set    
            matchs=matchs+1,  
            win=win+1,  
            goal = goal+new.visitingscore,  
            lost = lost+new.homescore,  
            net=net+(new.visitingscore-new.homescore),  
            avergoal = (goal * 1.0) / matchs,   
            averlost = (lost * 1.0)/ matchs,  
            avernet = (net * 1.0)/ matchs,  
            averpoint = (win * 3.0 + even)/ matchs  
        where teamname=new.visitingteam;  
end if;  
if new.hometeam in(select distinct team from player where gender='女') then  
    call updateRanking_score_for_female();  
elseif new.hometeam in(select name from team where college=NULL) then  
    call updateRanking_score_for_adult();  
else  
    call updateRanking_score_for_male();  
end if;  
end$  
delimiter ;  

触发器四: 插入进球时调整射手榜(进球信息，更新排名)。

delimiter $  
create trigger goal_insert after insert on goalofplayer for each row  
begin  
if not exists(select name from shooterlist where teamname=new.teamname and numbers=new.numbers) then  
    insert into shooterlist values(0,(select name from player where team=new.teamname and numbers=new.numbers),new.teamname,new.numbers,0,0,0);  
end if;  
  
update shooterlist set goals=goals+1 where teamname=new.teamname and numbers=new.numbers;  
if new.teamname in(select distinct team from player where gender='女') then   
    call updateRanking_shooter_for_female();  
elseif new.teamname in(select name from team where college=NULL) then  
    call updateRanking_shooter_for_adult();  
else  
    call updateRanking_shooter_for_male();  
end if;  
end$  
delimiter ;  

触发器五: 修改球队名称(或球队信息)时自动调整相关表格中的球队名称

delimiter $  
create trigger TeamUpdate after update on team for each row  
begin  
    if old.name!= new.name then  
        delete from scoreofteam where teamname=new.name;  
        update goalofplayer set teamname=new.name where teamname=old.name;  
        update player set team=new.name where team=old.name;  
        update schedule set hometeam=new.name where hometeam=old.name;  
       update schedule set visitingteam=new.name where visitingteam=old.name;  
       update scoreofteam set teamname=new.name where teamname=old.name;  
       update shooterlist set teamname=new.name where teamname=old.name;  
setTime=new.setTime where name=old.name;  
    end if;  
end$  

触发器六: 插入比赛处罚信息时调整射手榜

delimiter $  
create trigger judge_insert after insert on judge for each row  
begin  
if not exists(select name from shooterlist where teamname=new.teamna	me and numbers=new.numbers) then  
    insert into shooterlist values(0,(select name from player where team=new.teamname and numbers=new.numbers),new.teamname,new.numbers,0,0,0);  
end if;  
  
update shooterlist set redcard=new.redcard+redcard, yellowcard=new.yellowcard+yellowcard where teamname=new.teamname and numbers=new.numbers;  
end$  
delimiter ;  

3.2 存储过程的设计

存储过程一: 利用游标对名次进行处理

delimiter $  
drop procedure if exists updateRanking_score;  
create procedure updateRanking_score_for_female()  
begin  
    declare temp_teamname varchar(20);  
    declare temp_ranking int;  
    declare done int default false;   
    declare scoreofteam_cursor cursor for select teamname from scoreofteam where teamname in(select distinct team from player where gender='女')  order by averpoint desc,net desc,goal desc;  
    declare continue HANDLER for not found set done = true;   
    set temp_ranking=0;  
    open scoreofteam_cursor;  
    fetch scoreofteam_cursor into temp_teamname;    
        while(not done) do  
            set temp_ranking=temp_ranking+1;  
            update scoreofteam set changes = ranking-temp_ranking, ranking=temp_ranking where teamname=temp_teamname;  
            fetch scoreofteam_cursor into temp_teamname;  
        end while;    
    close scoreofteam_cursor;   
end$  
delimiter ;  

存储过程二：

delimiter $  
drop procedure if exists updateRanking_score;  
create procedure updateRanking_score_for_male()  
begin  
    declare temp_teamname varchar(20);  
    declare temp_ranking int;  
    declare done int default false;   
    declare scoreofteam_cursor cursor for select teamname from scoreofteam   
    where teamname not in(select distinct team from player where gender='女') and teamname not in(select name from team where college='无')  
    order by averpoint desc,net desc,goal desc;  
    declare continue HANDLER for not found set done = true;   
    set temp_ranking=0;  
    open scoreofteam_cursor;  
    fetch scoreofteam_cursor into temp_teamname;    
        while(not done) do  
            set temp_ranking=temp_ranking+1;  
            update scoreofteam set changes = ranking-temp_ranking, ranking=temp_ranking where teamname=temp_teamname;  
            fetch scoreofteam_cursor into temp_teamname;  
        end while;    
    close scoreofteam_cursor;   
end$  

存储过程三：

delimiter $  
drop procedure if exists updateRanking_score;  
create procedure updateRanking_score_for_adult()  
begin  
    declare temp_teamname varchar(20);  
    declare temp_ranking int;  
    declare done int default false;   
    declare scoreofteam_cursor cursor for select teamname from scoreofteam where teamname in(select name from team where college='无') order by averpoint desc,net desc,goal desc;  
    declare continue HANDLER for not found set done = true;   
    set temp_ranking=0;  
    open scoreofteam_cursor;  
    fetch scoreofteam_cursor into temp_teamname;    
        while(not done) do  
            set temp_ranking=temp_ranking+1;  
            update scoreofteam set changes = ranking-temp_ranking, ranking=temp_ranking where teamname=temp_teamname;  
            fetch scoreofteam_cursor into temp_teamname;  
        end while;    
    close scoreofteam_cursor;   
end$  
delimiter ;  

存储过程四：

**存储过程五：**见附录

delimiter $  
drop procedure if exists updateRanking_shooter;  
create procedure updateRanking_shooter_for_female()  
begin  
    declare temp_ranking int;  
    declare temp_teamname varchar(20);  
    declare temp_numbers varchar(20);  
    declare done int default false;   
    declare shooterlist_cursor cursor for select teamname,numbers from shooterlist where teamname in(select distinct team from player where gender='女') order by goals desc,name;  
    declare continue HANDLER for not found set done = true;   
    set temp_ranking=0;     
    open shooterlist_cursor;   
    fetch shooterlist_cursor into temp_teamname,temp_numbers;  
        while(not done) do  
            set temp_ranking=temp_ranking+1;  
            update shooterlist set ranking=temp_ranking where teamname=temp_teamname and numbers=temp_numbers;  
            fetch shooterlist_cursor into temp_teamname,temp_numbers;  
        end while;  
    close shooterlist_cursor;  
end$  
delimiter ;  

存储过程五：

delimiter $  
drop procedure if exists updateRanking_shooter;  
create procedure updateRanking_shooter_for_adult()  
begin  
    declare temp_ranking int;  
    declare temp_teamname varchar(20);  
    declare temp_numbers varchar(20);  
    declare done int default false;   
    declare shooterlist_cursor cursor for select teamname,numbers from shooterlist where teamname in(select name from team where college='无') order by goals desc,name;  
    declare continue HANDLER for not found set done = true;  
    set temp_ranking=0;     
    open shooterlist_cursor;   
    fetch shooterlist_cursor into temp_teamname,temp_numbers;  
        while(not done) do  
            set temp_ranking=temp_ranking+1;  
            update shooterlist set ranking=temp_ranking where teamname=temp_teamname and numbers=temp_numbers;  
            fetch shooterlist_cursor into temp_teamname,temp_numbers;  
        end while;  
    close shooterlist_cursor;  
end$  
delimiter ;  

存储过程六：

delimiter $  
drop procedure if exists updateRanking_shooter;  
create procedure updateRanking_shooter_for_male()  
begin  
    declare temp_ranking int;  
    declare temp_teamname varchar(20);  
    declare temp_numbers varchar(20);  
    declare done int default false;   
    declare shooterlist_cursor cursor for select teamname,numbers from shooterlist where teamname not in(select name from team where college='无') and teamname not in(select distinct team from player where gender='女') order by goals desc,name;  
    declare continue HANDLER for not found set done = true;   
    set temp_ranking=0;     
    open shooterlist_cursor;   
    fetch shooterlist_cursor into temp_teamname,temp_numbers;  
        while(not done) do  
            set temp_ranking=temp_ranking+1;  
            update shooterlist set ranking=temp_ranking where teamname=temp_teamname and numbers=temp_numbers;  
            fetch shooterlist_cursor into temp_teamname,temp_numbers;  
        end while;  
    close shooterlist_cursor;  
end$  
delimiter ;  

♻️ 资源

大小： 9.43MB
➡️ 资源下载：https://download.csdn.net/download/s1t16/87248890