数据库实验报告

1学生管理系统数据库设计报告一：实验小组成员：组长：主要工作：设计关系模式图，设计存储过程和检测脚本组员：主要工作：设计ER图，触发器主要工作：设计表，视图，并完成系统测试二：数据库设计要求建立关于系、学生、班级、学会等诸信息的一个关系数据库。一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某学会有一个入会年份。描述各个实体的属性（加下划线者为主码）如下：学生：学号、姓名、年龄、系名、班号，性别，宿舍区。班级：班号、专业名、入校年份、系名、人数。系：系号、系名、系办公室地点、人数。学会：学会号、学会名、成立年份、地点。三：ER图设计1：学生实体的ER图2：班级实体的ER图3：系实体的ER图4：学会实体的ER图5：实体及联系ER图6：完整的ER图四：关系模式1：由ER图直接得到的关系模型学生（学号，姓名，年龄，系名，班号，性别，宿舍区）班级（班号，专业名，人数，系名，入校年份）系（系号，系名，人数，系办公室地点）学会（学会号，学会名，地点，成立年份）参加学会（学号，学会号，入会年份）2：关系模型的合并和分解（1）学生模型中的系名属性是冗余的，可以由学生所在的班级查到该同学所在的系。（2）班级中的专业名属性是冗余的，由于一个专业可以有多名学生，如果专业名发生变化，则要求改很多的学生信息，不符合数据库的规范化原理，所以分解这个模型为两个模型。班级（班号，专业号，人数，系名，入校年份）专业（专业号，系号，专业名）（3）经过2的分解后，班级关系中的系名是冗余项，可以由专业查到，删除。2（4）经过以上的分解合并后的关系模型如下：学生（学号，班号，姓名，年龄，性别，宿舍区）班级（班号，专业号，人数，入校年份）专业（专业号，系号，专业名）系（系号，系名，人数，系办公室地点）学会（学会号，学会名，地点，成立年份）参加学会（记录号，学号，学会号，入会年份）五：建表1：代码如下CREATETABLE`class`(`class_id`varchar(8)collateutf8_unicode_ciNOTNULL,`major_id`varchar(8)collateutf8_unicode_ciNOTNULL,`class_count`int(11)default'0',`year_in`dateNOTNULL,PRIMARYKEY(`class_id`),KEY`major_id`(`major_id`),FOREIGNKEY(`major_id`)REFERENCES`major`(`major_id`)ONDELETECASCADEONUPDATECASCADE)CREATETABLE`dept`(`dept_id`varchar(8)collateutf8_unicode_ciNOTNULL,`dept_name`varchar(20)collateutf8_unicode_ciNOTNULL,`dept_count`int(11)default'0',`dept_dest`varchar(20)collateutf8_unicode_ciNOTNULL,PRIMARYKEY(`dept_id`))CREATETABLE`major`(`major_id`varchar(8)collateutf8_unicode_ciNOTNULL,`dept_id`varchar(8)collateutf8_unicode_ciNOTNULL,`major_name`varchar(20)collateutf8_unicode_ciNOTNULL,PRIMARYKEY(`major_id`),KEY`dept_id`(`dept_id`),FOREIGNKEY(`dept_id`)REFERENCES`dept`(`dept_id`)ONDELETECASCADEONUPDATECASCADE)CREATETABLE`org`(`org_id`varchar(8)collateutf8_unicode_ciNOTNULL,`org_name`varchar(20)collateutf8_unicode_ciNOTNULL,`org_des`varchar(20)collateutf8_unicode_ciNOTNULL,`org_year`dateNOTNULL,PRIMARYKEY(`org_id`))CREATETABLE`org_stu`(3`org_stu_id`varchar(8)collateutf8_unicode_ciNOTNULL,`s_id`varchar(8)collateutf8_unicode_ciNOTNULL,`org_id`varchar(8)collateutf8_unicode_ciNOTNULL,`year_in`dateNOTNULL,PRIMARYKEY(`org_stu_id`),KEY`s_id`(`s_id`),KEY`org_id`(`org_id`),FOREIGNKEY(`s_id`)REFERENCES`student`(`s_id`)ONDELETECASCADEONUPDATECASCADE,FOREIGNKEY(`org_id`)REFERENCES`org`(`org_id`)ONDELETECASCADEONUPDATECASCADE)CREATETABLE`student`(`s_id`varchar(8)collateutf8_unicode_ciNOTNULL,`class_id`varchar(8)collateutf8_unicode_ciNOTNULL,`name`varchar(20)collateutf8_unicode_ciNOTNULL,`age`tinyint(3)unsignedNOTNULL,`sex`tinyint(1)NOTNULL,`area`varchar(20)collateutf8_unicode_ciNOTNULL,PRIMARYKEY(`s_id`),KEY`class_id`(`class_id`),FOREIGNKEY(`class_id`)REFERENCES`class`(`class_id`)ONDELETECASCADEONUPDATECASCADE)六：创建视图1：创建视图org_count(org_name,org_count)这个视图能显示每个学会的学会名，学生数。CREATEVIEW`org_count`ASselect`org`.`org_name`AS`org_name`,count(`org_stu`.`s_id`)AS`org_count`from(`org`join`org_stu`)where(`org`.`org_id`=`org_stu`.`org_id`)groupby`org_stu`.`org_id`;七：创建触发器触发器根据每个班的学生变动情况自动增减班级表和系表的人数字段的值。总共三个触发器，在学生人数发生增减，或者学生所转班之后都会触发触发器，更新数据DELIMITER;;CREATETRIGGER`student_insert`AFTERINSERTON`student`FOREACHROWbeginUpdateclasssetclass_count=class_count+1whereclass_id=new.class_id;updatedeptsetdept_count=dept_count+1wheredept_id=(selectdept_idfrommajorwheremajor_id=(selectmajor_idfromclasswhereclass_id=new.class_id));end;;DELIMITER;4DELIMITER;;CREATETRIGGER`student_update`AFTERUPDATEON`student`FOREACHROWbeginupdateclasssetclass_count=class_count+1whereclass_id=new.class_id;updateclasssetclass_count=class_count-1whereclass_id=old.class_id;updatedeptsetdept_count=dept_count+1wheredept_id=(selectdept_idfrommajorwheremajor_id=(selectmajor_idfromclasswhereclass_id=new.class_id));updatedeptsetdept_count=dept_count-1wheredept_id=(selectdept_idfrommajorwheremajor_id=(selectmajor_idfromclasswhereclass_id=old.class_id));end;;DELIMITER;DELIMITER;;CREATETRIGGER`student_delete`AFTERDELETEON`student`FOREACHROWbeginupdateclasssetclass_count=class_count-1whereclass_id=old.class_id;updatedeptsetdept_count=dept_count-1wheredept_id=(selectdept_idfrommajorwheremajor_id=(selectmajor_idfromclasswhereclass_id=old.class_id));end;;DELIMITER;八：创建存储过程这个存储过程实现如下功能：给定一个班的旧班号和新班号，把所有相关表中此班的旧班号改为新班号，并返回此班的人数（使用输出参数）。调用方法：callnew_class_id(arg1,arg2,arg3);其中arg1和arg2分别输入旧班号和新班号，arg3输出这个班的人数DROPPROCEDUREIFEXISTS`new_class_id`;DELIMITER;;CREATEPROCEDURE`new_class_id`(IN`old_id`varchar(8),IN`new_id`varchar(8),OUTcountsint)BEGINdeclaretempvarchar(8);declareclass_countsint;declarefetcherrointdefault0;declarecurcursorforselectclass_id,class_countfromclasswhereclass_id=old_id;declarecontinuehandlerfornotfoundsetfetcherro=1;opencur;fetchcurintotemp,class_counts;iffetcherro=0then5updateclasssetclass_id=new_idwhereclass_id=old_id;selectclass_countsas本班人数;setcounts=class_counts;elseselect这个班不存在as参数错误;endif;END;;DELIMITER;九：脚本本脚本实现如下功能，检查系中的人数是否与实际情况相同，若不同，则改为实际情况，并返回该记录，包括改前的记录和改后的记录。脚本中使用了临时表保存查到的不正确的数据！delimiter//createtabletemp(dept_idvarchar(8),dept_namevarchar(20),dept_count_oldint(11),dept_count_newint(11))//dropprocedureifexistscounts_check//create