数据库实验报告

1、精选优质文档-倾情为你奉上学生管理系统数据库设计报告一：实验小组成员：组长： 主要工作：设计关系模式图，设计存储过程和检测脚本组员： 主要工作：设计ER图，触发器 主要工作：设计表，视图，并完成系统测试二：数据库设计要求建立关于系、学生、班级、学会等诸信息的一个关系数据库。一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某学会有一个入会年份。描述各个实体的属性（加下划线者为主码）如下： 学生：学号、姓名、年龄、系名、班号，性别，宿舍区。 班级：班号、专业名、入校年份、系名、人数。 系 ：系号、系名、系办

2、公室地点、人数。 学会：学会号、学会名、成立年份、地点。三：ER图设计1：学生实体的ER图2：班级实体的ER图3：系实体的ER图4：学会实体的ER图5：实体及联系ER图6：完整的ER图四：关系模式1：由ER图直接得到的关系模型学生（学号，姓名，年龄，系名，班号，性别，宿舍区）班级（班号，专业名，人数，系名，入校年份）系（系号，系名，人数，系办公室地点）学会（学会号，学会名，地点，成立年份）参加学会（学号，学会号，入会年份）2：关系模型的合并和分解（1） 学生模型中的系名属性是冗余的，可以由学生所在的班级查到该同学所在的 系。（2） 班级中的专业名属性是冗余的，由于一个专业可以有多名学生，如果专

3、业名 发生变化，则要求改很多的学生信息，不符合数据库的规范化原理，所以分 解这个模型为两个模型。班级（班号，专业号，人数，系名，入校年份）专业（专业号，系号，专业名）（3） 经过2的分解后，班级关系中的系名是冗余项，可以由专业查到，删除。（4） 经过以上的分解合并后的关系模型如下： 学生（学号，班号，姓名，年龄，性别，宿舍区）班级（班号，专业号，人数，入校年份）专业（专业号，系号，专业名）系（系号，系名，人数，系办公室地点）学会（学会号，学会名，地点，成立年份）参加学会（记录号，学号，学会号，入会年份）五：建表1：代码如下CREATE TABLE class ( class_id varcha

4、r(8) collate utf8_unicode_ci NOT NULL, major_id varchar(8) collate utf8_unicode_ci NOT NULL, class_count int(11) default '0', year_in date NOT NULL, PRIMARY KEY (class_id), KEY major_id (major_id), FOREIGN KEY (major_id) REFERENCES major (major_id) ON DELETE CASCADE ON UPDATE CASCADE) CREATE

5、 TABLE dept ( dept_id varchar(8) collate utf8_unicode_ci NOT NULL, dept_name varchar(20) collate utf8_unicode_ci NOT NULL, dept_count int(11) default '0', dept_dest varchar(20) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (dept_id) CREATE TABLE major ( major_id varchar(8) collate utf8_unico

6、de_ci NOT NULL, dept_id varchar(8) collate utf8_unicode_ci NOT NULL, major_name varchar(20) collate utf8_unicode_ci NOT NULL, PRIMARY KEY (major_id), KEY dept_id (dept_id), FOREIGN KEY (dept_id) REFERENCES dept (dept_id) ON DELETE CASCADE ON UPDATE CASCADE) CREATE TABLE org ( org_id varchar(8) colla

7、te utf8_unicode_ci NOT NULL, org_name varchar(20) collate utf8_unicode_ci NOT NULL, org_des varchar(20) collate utf8_unicode_ci NOT NULL, org_year date NOT NULL, PRIMARY KEY (org_id) CREATE TABLE org_stu ( org_stu_id varchar(8) collate utf8_unicode_ci NOT NULL, s_id varchar(8) collate utf8_unicode_c

8、i NOT NULL, org_id varchar(8) collate utf8_unicode_ci NOT NULL, year_in date NOT NULL, PRIMARY KEY (org_stu_id), KEY s_id (s_id), KEY org_id (org_id), FOREIGN KEY (s_id) REFERENCES student (s_id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (org_id) REFERENCES org (org_id) ON DELETE CASCADE ON U

9、PDATE CASCADE) CREATE TABLE student ( s_id varchar(8) collate utf8_unicode_ci NOT NULL, class_id varchar(8) collate utf8_unicode_ci NOT NULL, name varchar(20) collate utf8_unicode_ci NOT NULL, age tinyint(3) unsigned NOT NULL, sex tinyint(1) NOT NULL, area varchar(20) collate utf8_unicode_ci NOT NUL

10、L, PRIMARY KEY (s_id), KEY class_id (class_id), FOREIGN KEY (class_id) REFERENCES class (class_id) ON DELETE CASCADE ON UPDATE CASCADE) 六：创建视图1：创建视图 org_count(org_name,org_count) 这个视图能显示每个学会的学会名，学生数。CREATE VIEW org_count AS select _name AS org_name,count(org_stu.s_id) AS org_count from (org j

11、oin org_stu) where (_id = org__id) group by org__id;七：创建触发器触发器根据每个班的学生变动情况自动增减班级表和系表的人数字段的值。总共三个触发器，在学生人数发生增减，或者学生所转班之后都会触发触发器，更新数据DELIMITER ;CREATE TRIGGER student_insert AFTER INSERT ON student FOR EACH ROW begin Update class set class_count=class_count+1 where class_id=new.cl

12、ass_id; update dept set dept_count=dept_count+1 where dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=new.class_id); end;DELIMITER ;DELIMITER ;CREATE TRIGGER student_update AFTER UPDATE ON student FOR EACH ROW begin update class set class_count=class_coun

13、t+1 where class_id=new.class_id; update class set class_count=class_count-1 where class_id=old.class_id; update dept set dept_count=dept_count+1 where dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=new.class_id); update dept set dept_count=dept_count-1 w

14、here dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=old.class_id);end;DELIMITER ;DELIMITER ;CREATE TRIGGER student_delete AFTER DELETE ON student FOR EACH ROW begin update class set class_count=class_count-1 where class_id=old.class_id;update dept set de

15、pt_count=dept_count-1 where dept_id=(select dept_id from major where major_id=(select major_id from class where class_id=old.class_id);end;DELIMITER ;八：创建存储过程 这个存储过程实现如下功能：给定一个班的旧班号和新班号，把所有相关表中此班的旧班号改为新班号，并返回此班的人数（使用输出参数）。调用方法： call new_class_id(arg1,arg2,arg3);其中arg1和arg2分别输入旧班号和新班号，arg3输出这个班的人数DRO

16、P PROCEDURE IF EXISTS new_class_id;DELIMITER ;CREATE PROCEDURE new_class_id(IN old_idvarchar(8), INnew_id varchar(8),OUT counts int)BEGINdeclare temp varchar(8);declare class_counts int;declare fetcherro int default 0;declare cur cursor for select class_id,class_count from class where class_id=old_i

17、d;declare continue handler for not found set fetcherro=1;open cur;fetch cur into temp,class_counts;if fetcherro=0 thenupdate class set class_id=new_id where class_id=old_id;select class_counts as "本班人数" ;set counts=class_counts;elseselect "这个班不存在" as "参数错误"end if;END;DE

18、LIMITER ;九：脚本本脚本实现如下功能，检查系中的人数是否与实际情况相同，若不同，则改为实际情况，并返回该记录，包括改前的记录和改后的记录。脚本中使用了临时表保存查到的不正确的数据！delimiter /create table temp(dept_id varchar(8),dept_name varchar(20),dept_count_old int(11),dept_count_new int(11)/drop procedure if exists counts_check/create procedure counts_check()BEGINdeclare temp1,te

19、mp2 varchar(8);declare name varchar(20);declare class_counts,dept_counts,students_counts1, students_counts2 int;declare cur_class cursor for select class_id,class_count from class;declare cur_dept cursor for select dept_id,dept_name,dept_count from dept;select count(*) into class_counts from class;s

20、elect count(*) into dept_counts from dept;open cur_class;while class_counts>0 dofetch cur_class into temp1,students_counts1;select count(*) into students_counts2 from student where class_id=temp1;if students_counts1!=students_counts2 then update class set class_count=students_counts2 where class_

21、id=temp1;end if;set class_counts=class_counts-1;end while;close cur_class;open cur_dept;while dept_counts>0 dofetch cur_dept into temp2,name,students_counts1;select count(*) into students_counts2 from student where class_id=(select class_id from class where major_id=(select major_id from major where dept_id=temp2);if students_counts1!=students_counts2 thenupdate dept set dept_count=students_counts2 where dept_id=temp2;insert into temp values(temp2,name,students_counts1,students_counts