数据库-学生管理系统设计

1、数据库大作业学生管理系统组 长：赵呈亮00101303设计成员：汤雨00101304 桑迪00101308 指导老师：祁建军 设计时间：2013/6/20 组长:赵呈亮00101303主要任务:1、根据语义画出分E-R图,并集成基本E-R图 将E-R图转换为关系模式，并进行模式优化 2、linux中MYSQL下编写SQL代码创建视图、触 发器、存储过程、游标等 3、全部文档撰写 4、答辩主讲主要成员1：汤雨00101304主要任务：1、 linux环境下用PHP语言编写前端，实现查 询、修改、插入等操作的可视化。2、参与后台sql代码编写的讨论3、带电脑参与答辩、展示。主要成员2：桑迪0010

2、1308主要任务：1、根据优化后的关系模式创建基本表，并填充 数据。2、参与优化模式讨论。学生管理系统-数据库设计7步(1) 规划阶段建立数据库的必要性和可行性分析。(2) 需求分析全面、准确了解用户的实际要求。 (3) 概念结构设计设计数据库的概念结构，形成一个独立于具体DBMS的概念模型。今要建立关于系、学生、班级、学会等诸信息的一个关系数据库。一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某学会有一个入会年份。描述各个实体的属性（加下划线者为主码）如下： 学生：学号、姓名、年龄、系名、班号、宿舍

3、区。 班级：班号、专业名、入校年份、系名、人数。 系 ：系号、系名、系办公室地点、人数。 学会：学会号、学会名、成立年份、地点。1、实体及属性E-R图描述在设计E-R图时，能作为属性的就不作为实体，这样有利于E-R图的简化。clyearclmjnamesdnamesageclno sclnocldnamesnamesdor cltotalsno class student stuunionuplacedno deptubiryeardnamedtotaldwkplaceunouname2、设计子E-R图确定实体与属性的两条准则：(1) 属性是不可再分的数据项，属性不可以再有属性；(2) 属性不

4、能与其他实体发生联系，联系只能存在于实体与实体之间。sdorsclnosdnamesagesnamesnosdorsdnamesagesnamesno studentcltotalcldnameclmjnameclnojoinyearsjoin student n nbelong m stuunionuplace 1 class ubiryearunameunoclyear student属性"sdor"和"dept"有联系,故将"sdor"属性转变成实体"dormitory"同理,class属性"clm

5、jname"与dept有联系,故将"clmjname"转变成实体"major"。clno classmjname majorestablishdnodname deptdwkplacedtotalclyearcldnamesagesnamesclno studentdnodname deptdtotaldormitorysnobelongcltotalopen n 1 1 1dwkplace p 1live 1 1dorplace3、视图集成1)视图集成的作用： 一方面可以完整地描述企业的信息及其联系，另一方面在集成过程中可以 解决冲突和消除冗

6、余。2)视图集成中E-R图之间的三类冲突： (1) 属性冲突：同一属性可能会存在于不同的分E-R图，由于设计人员不同或是出发点不同，对属性的类型、取值范围、数据单位等可能会不一致，这些属性对应的数据将来只能以一种形式在计算机中存储，这就需要在设计阶段进行统一。 (2) 命名冲突：相同意义的属性，在不同的分E-R图上有着不同的命名（异名同义），或是名称相同的属性在不同的分E-R图中代表着不同的意义（同名异义），这些也要进行统一。 (3) 结构冲突：同一实体在不同的分E-R图中有不同的属性，同一对象在某一分E-R图中被抽象为实体而在另一分 E-R图中又被抽象为属性，需要统一。3)视图集成消除冗余注

7、意点: 1. 在E-R图的综合的过程中，同名实体只能出现一次，还要去掉不必要的联系，且不能出现环路，这样才能消除冗余数据和冗余联系。 2. 一般来说，从总体E-R图必须能导出原来所有局部E-R视图，包括所有的实体、属性和联系。考虑冲突和冗余如下:ubiryearunameuplaceuno stuunionsjoin studentjoinyear m sagesclno nsdorsname sdnamesno nbelongcltotalcldnamebelong 1clyear class n mjname 1open 1clnoclmjname major 1establish pdn

8、odname dept 1livedormitorydorplace 1 1dwkplacedtotal消除冲突和冗余后得基本E-R图如下：uplaceubiryearunameuno stuunion stuunion ( uno，uname，ubiryear，uplace)joinyear Msjoinsagesnamesno student join (uno，sno，joinyear) N student ( sno ，sname，sage，clno) belong n classclyearcltotal 1 class ( clno，clyear，cltotal) openclno

9、 1 mjname 1 major major ( mjname，clno，dno) pestablish 1dnodname deptlivedorplacedormitory 1 1dtotal dormitory ( dorplace，dno)dwkplacedept ( dno，dname，dwkplace，dtotal)(4) 逻辑结构设计逻辑结构设计的任务就是将E-R图转换为关系模式。随后应该进行的工作是对关系模式的规范化和优化，以及面向应用程序设计的用户子模式设计。F 逻辑结构设计过程示意图：1、E-R图向关系模式的转换1）. 一个实体型转换为一个关系模式Ø 实体型的属

10、性转换为关系的属性Ø 实体标识符转换为关系的码2）. 联系的转换a) 一个 1:1 联系可以转换为一个独立的关系模式，也可以与任意一端对应的关系模式合并。 转换为一个独立的关系模式Ø 关系的属性：与该联系相连的各实体的标识符以及 联系本身的属性Ø 关系的候选码：每个实体标识符均是该关系的候选 码 与某一端对应的关系模式合并Ø 合并后关系的属性：加入另一关系的码和联系本身 的属性Ø 合并后关系的码：不变b) 一个 1:n 联系可以转换为一个独立的关系模式，也可以与n端对应的关系模式合并。 转换为一个独立的关系模式Ø 关系的属性：与该联系

11、相连的各实体的标识符以及 联系本身的属性Ø 关系的码：n端实体的标识符 与n端对应的关系模式合并Ø 合并后关系的属性：在n端关系中加入1端关系的 码和联系本身的属性Ø 合并后关系的码：不变c) 一个m:n联系转换为一个关系模式。Ø 关系的属性：与该联系相连的各实体的标识符以及 联系本身的属性Ø 关系的码：各实体标识符的组合d) 三个或三个以上实体间的一个多元联系转换为一个关系模式。Ø 关系的属性：与该多元联系相连的各实体的标识符 以及联系本身的属性Ø 关系的码：各实体标识符的组合转换结果如下：stuunion ( uno，u

12、name，ubiryear，uplace)sjoin (uno，sno，joinyear)student ( sno ，sname，sage，clno) class ( clno，clyear，cltotal)major ( mjname，clno，dno)dept ( dno，dname，dwkplace，dtotal)dormitory ( dorplace，dno)2、关系模式优化现阶段由概念模型到逻辑模型的过程通常就是将E-R图转换为关系模式，而经过这一转换过程之后得到的一组关系模式未必是最“好”的，还需要经过进一步的优化。F 关系模式的优化应该从以下几方面实施：(1) 关系模式规范化

13、规范化的目的在于解决更新异常和数据冗余，应该分解关系模式使其达到3NF或BCNF，对于存在MVD的关系模式应达到4NF。1NF定义：如果一个关系模式R的所有属性都是不可分的基本数据项，则称关系R为第一范式的关系模式(First Normal Form)，简称关系R属于一范式，记为：R1NF。 经检验，以上7个关系模式，均满足第一范式要求。2NF定义：若关系模式R1NF，并且每一个非主属性都完全函数依赖于R的码，则R2NF。 经检验， 7个关系模式均满足满足第二范式。3NF定义： 关系模式R<U,F> 中若不存在这样的码X、属性组Y及非主属性 Z (Z Í Y ), 使得

14、XY， Y X ，YZ 成立，则称R3NF。即：若R3NF，则R中的每一个非主属性既不部分依赖于码，也不传递依赖于码。经检验， 7个模式均满足第三范式要求。所以，经关系范式规范后，7个模式为：stuunion ( uno，uname，ubiryear，uplace)sjoin (uno，sno，joinyear)student ( sno ，sname，sage，clno) class ( clno，clyear，cltotal)major ( mjname，clno，dno)dept ( dno，dname，dwkplace，dtotal)dormitory ( dorplace，dno)(

15、2) 对关系模式进行必要的合并对具有关联的关系模式进行合并，通常这类关系模式会经常被查询而频繁地进行连接运算而降低查询的效率，合并后的关系模式可能会带来冗余(存在部分依赖或传递依赖)，但这样做还是值得的。经考察，不需要合并。(3) 进行合理的分解(不是为了达到更高范式)¯ 水平分解 把(基本)关系的元组分解为若干子集合，定义每个子集合为一个子关系(相同结构的关系模式，但名称不同)，以提高系统的效率。¯ 垂直分解把关系模式R的属性分解为若干子集合，形成若干关系模式。Ø 垂直分解的原则：把经常在一起使用的属性从R中分解出来形成一个关系模式。垂直分解注意事项：Ø

16、; 应从所有事务执行的总效率出发考虑分解的必要性Ø 垂直分解必须不损失关系模式的语义(保持无损连接性和保持函数依赖)经考察，不需要分解。最终优化后的关系模式如下：stuunion ( uno，uname，ubiryear，uplace)sjoin (uno，sno，joinyear)student ( sno ，sname，sage，clno) major(mjname，clno，dno)class ( clno，clyear，cltotal)dept ( dno，dname，dwkplace，dtotal)dormitory ( dorplace，dno)(5) 数据库物理设计为数

17、据库设计一个合理的存储结构和存取方法。F 关系数据库物理设计的内容¯ 为关系模式选择存取方法(建立存取路径)¯ 2. 设计关系、索引等数据库文件的物理存储结构F 确定数据库存储结构的基本原则 根据应用情况将易变部分与稳定部分、存取频率较高部分与存 取频率较低部分分开存放，以提高系统性能F 评价内容 对数据库物理设计过程中产生的多种方案进行细致的评价，从 中选择一个较优的方案作为数据库的物理结构。F 评价方法¯ 定量估算各种方案Ø 存储空间Ø 存取时间Ø 维护代价¯ 对估算结果进行权衡、比较，选择出一个较优的合理的物理结构&#

18、175; 如果该结构不符合用户需求，则需要修改设计(6) 数据库实施 编写与调试应用程序（后台数据库、前端界面），组织数据入库，并进行试运行。 1、定义数据库结构确定了数据库的逻辑结构与物理结构后，就可以用所选用的DBMS提供的数据定义语言（DDL）来严格描述数据库结构。1.表stuunion ( uno，uname，ubiryear，uplace)create table stuunion(uno char(6) primary key,uname varchar(20) not null unique,ubiryear int,uplace varchar(20) );2.表sjoin (

19、uno，sno，joinyear)create table sjoin(uno char(6) references stuunion(uno),sno char(8) references student(sno),joinyear int,primary key (uno,sno);3.表student ( sno ，sname，sage，clno) create table student(sno char(8) primary key,sname varchar(20) not null unique,sage smallint,clno char(6) references clas

20、s(clno) ;4.表major( mjname，clno， dno)create table major(mjname varchar(20) primary key,clno char(6) references class(clno),dno char(2) references dept(dno);5.表class ( clno，clyear，cltotal)create table class (clno char(6) primary key,clyear int,cltotal int );6.表dept ( dno，dname，dwkplace，dtotal)create t

21、able dept(dno char(2) primary key,dname varchar(20) not null unique,dwkplace varchar(20),dtotal int);7.表dormitory ( dorplace，dno)create table dormitory(dorplace varchar(20),dno char(2) references dept(dno);常用命令实例：1、导出数据和表结构：mysqldump -u用户名 -p密码 数据库名 > 数据库名.sql2、文本数据应符合的格式：字段数据之间用tab键隔开，例： name du

22、ty 2006-11-23数据传入命令 load data local infile "文件名" into table 表名;3、插入记录：insert into sjoin values(100001,00101313,2011);4、修改记录值：update sjoin set sno=00101303 where sno=101303;5、修改属性：alter table class change column cltoal cltotal int;创建视图：create view uname_utotal(uname,utotal) -> as -> s

23、elect uname,count(*) -> from stuunion,sjoin -> where stuunion.uno=sjoin.uno -> group by stuunion.uno / -> with check option;创建触发器：DELIMITER |create trigger triinsert_cltotal_dtotal after inserton student for each rowbegindeclare c char(2);update class set cltotal = cltotal + 1 where clno

24、= new.clno;set c=(select dept.dno from major,dept where major.dno =dept.dno and major.clno=new.clno);update dept set dtotal=dtotal+1 where dno=c;end|DELIMITER ;DELIMITER |create trigger tridelete_cltotal_dtotal after deleteon student for each rowbegindeclare c char(2);update class set cltotal = clto

25、tal - 1 where clno= old.clno;set c=(select dept.dno from major,dept where major.dno =dept.dno and major.clno=old.clno);update dept set dtotal=dtotal-1 where dno=c;end|DELIMITER ;创建存储过程：delimiter #create procedure modifycno(in oldcno char(6),in newcno char(6),out class_total int)beginupdate student s

26、et clno=newcno where clno=oldcno;update major set clno=newcno where clno=oldcno;update class set clno=newcno where clno=oldcno;select cltotal into class_total from class where clno=newcno;end #delimiter ; 调用：call modifycno('001013','123456',class_total);select class_total;创建游标：游标实现了m

27、ysql的存储过程中循环读取数据表中的对象的过程。声明游标： DECLARE cursor_name CURSOR FOR select_statement这个语句声明一个光标。也可以在子程序中定义多个光标，但是一个块中的每一个光标必须有唯一的名字。打开游标: OPEN cursor_name游标FETCH：FETCH cursor_name INTO var_name , var_name .这个语句用指定的打开光标读取下一行（如果有下一行的话），并且前进光标指针。 关闭游标CLOSE： CLOSE cursor_name这个语句关闭先前打开的光标。如果未被明确地关闭，光标在它被声明的复合语句的末尾被关闭。delimiter #create procedure checkdtotal()begindeclare done2 int default 0;declare student_no char(8);declare class_no char(6);declare dept_no char(2);declare dept_name varchar(20);declare a,b,total int;declare cur1 cursor for select sno,clno from student;declare cur2 cursor for select dno,d