数据库大作业学生管理系统

学生信息管理系统数据库设计与实现 院系：计算机科学系 班级 ：计科07-1班 学号：08080605227 姓名：叶尔凯西 系统名称：学生信息管理系统一 系统需求a. 该“学生信息管理系统”只是对数据库应用技术的一个样本数据库的实例，重在对数据库一些方法的熟悉与掌握，其中包括从数据库的概念模型到ER图的绘制，再到数据库模式的建立（即为数据库及其基本表的建立），而后数据的录入（在此从略），最后完成一些简单的关系运算表达式的表达和相关的Select 查询语句的使用。b. 系统结构简述：本系统包括三个实体：院系；学生档案；课程；实体之间的关系转换为其他表的有：选修；实体“院系”与“学生档案”之间有一对多的关系，把院系表的主码转放到表“学生档案”中；实体“学生档案”与“课程”之间有多对多的关系，因此独立形成一个表并共同存储“学生档案”与“课程”表的主码和一些自己的属性列；c. 该数据库需要进行如下数据处理： 从选修表中查询成绩的函数：输入：学号和课程编号输出：对应的学号和课程编号的分数即成绩；从学生档案表中查询学生基本信息的函数：输入：学号输出：对应的学号的基本信息，即学号，姓名，性别，专业，联系方式，联系地址，院系编号；从学生档案表中删除对应的学生记录：输入：学号输出：删除对应的学生记录，若没有对应的学号则输出“没有该学生!”的提示； 向学生档案表插入数据的函数：输入：学生档案表的基本信息，包括所有属性列；即学号，姓名，性别，专业，联系方式，联系地址，院系编号；级联更新（从院系表到学生档案表、课程表）：输入：院系编号，院系名称成绩表上的触发器，当删除某一个学生的成绩时触发：当从学生档案表中删除学生记录时，若该学号的学生记录在选修表中也有的话，则提示二.概念设计（设计E R模型）学生档案选修课程局部E-R模型局部E-R模型所属院系院系局部E-R模型属于课程院系学生档案 表1.实体间的相互联系实体联系实体院系属于课程学生档案选修课程院系所属院学生档案学生档案选修课程分数mnn1课程编号#课程名称所属院系院系n1院系编号#院系名称学号#姓名性别专业联系方式联系地址学时属于学生信息管理系统的E-R模型 三逻辑设计（转换成关系模型）系统中的实体有：学生档案；课程；院系；关系模式如下：学生档案（学号，姓名，性别，专业，联系方式，联系地址）PK=学号，NOT NULL课程（课程编号，课程名称，学时，院系编号）PK=课程编号，NOT NULLFK=院系编号，参照院系表选修（学号，课程编号，分数）PK=< 学号，课程编号 >，NOT NULLFK=学号，参照学生档案表FK=课程编号，参照课程表院系（院系编号，院系名称）PK=院系编号，NOT NULL四 物理设计表1。学生档案表结构字段名类型特殊属性学号char(12)Not null姓名char(22)null性别char(2)null专业char(16)Null联系方式char(12)Null联系地址char(40)null院系编号smallintnull表2。院系表结构字段名类型特殊属性院系编号SmallintNot null院系名称char(16)null表3。课程表结构字段名类型特殊属性课程编号char(10)Not null课程名称char(20)null学时Smallintnull院系编号smallintNull表4。选修表结构字段名类型特殊属性学号char(12)Not null课程编号char(10)Not null分数Numeric(4,1)null五. 数据库的实施1. 用SQL语言建立数据库和定义表的结构（1）建立数据库create database 学生信息管理（2）用SQL语言定义表的结构 建立院系表：create table 院系(院系编号 smallint,院系名称 char(16),primary key(院系编号) 建立学生档案表：create table 学生档案(学号 char(12),姓名 char(22),性别 char(2)check(性别 in ('男','女'),专业 char(16),联系方式 char(12),联系地址 char(40),院系编号 smallint,primary key(学号),foreign key(院系编号)references 院系(院系编号)on update cascade) 建立课程表create table 课程(课程编号 char(10),课程名称 char(20),学时 smallint,院系编号 smallint,primary key(课程编号),foreign key(院系编号)references 院系(院系编号)on update cascade) 建立选修表create table 选修(学号 char(12),课程编号 char(10),分数 numeric(4,1)check(分数<='100.0' and 分数 >='0'),primary key(学号,课程编号),foreign key(学号)references 学生档案(学号) on update no action,foreign key(课程编号)references 课程(课程编号) on update no action)2创建存储过程-从选修表中查询成绩的函数if exists(select name from sysobjects where name='成绩查询'and type='p')drop procedure 成绩查询gocreate procedure 成绩查询 Sno char(12),Cno char(10)asif exists(select 学号,课程编号 from 选修 where 学号=Sno and 课程编号=Cno)select 学号,课程编号,分数from 选修where 学号=Sno and 课程编号=Cnoelseprint '没有该学号的学生或课程'goexecute 成绩查询 '08080605227','06232023'-从学生档案表中查询学生基本信息的函数if exists(select name from sysobjects where name='学生基本信息查询' and type='p')drop procedure 学生基本信息查询gocreate procedure 学生基本信息查询 Sno char(12) asif exists(select 学号 from 学生档案 where 学号=Sno)select *from 学生档案where 学号=Snoelseprint '不存在这样的学生!'goexecute 学生基本信息查询 '08080605227'-从学生档案表中删除对应的学生if exists(select name from sysobjects where name='删除学生记录'and type='p')drop procedure 删除学生记录gocreate procedure 删除学生记录 Sno char(12)asif exists(select * from 学生档案 where 学号=Sno)delete from 学生档案where 学号=Snoelseprint '没有该学生!'goexecute 删除学生记录 '12345678910'execute 删除学生记录 '09080605227'-向学生档案表插入数据的函数if exists(select name from sysobjects where name='向学生档案表输入'and type='p')drop procedure 向学生档案表输入gocreate procedure 向学生档案表输入 Sno char(12),Sname char(22),Ssex char(2),Szhuanye char(16),Scontact char(12),Saddress char(40),Dno smallintasinsert into 学生档案 values(Sno,Sname,Ssex,Szhuanye,Scontact,Saddress,Dno)goexecute 向学生档案表输入 '09080605227','jack','男','计算机','15012345652','abbbb','1010'execute 向学生档案表输入 '12345678910','john','男','计算机','15292768459','abcdefg','1010'-级联更新（从院系表到学生档案表、课程表）if exists(select name from sysobjects where name='更新院系编号'and type='p')drop procedure 更新院系编号gocreate procedure 更新院系编号 Dno smallint,Dname char(16)asupdate 院系set 院系编号=Dnowhere 院系名称=Dnamegoexecute 更新院系编号 1010,'计算机科学系' 学生要查询本学期所开课程详细情况（成绩除外），并按“课程类型”分组输入：学号输出：学号，姓名，课程名，学分数，学时数，课程类型设输入的学号的数值为xSelect S.学号，姓名，课程名，学分数，学时数，课程类型From 学生 as S，选修 as X，课程 as CWhere S.学号X.学号 and X.课程编号C.课程编号 and S.学号xGroup by 课程类型3创建触发器-成绩表上的触发器，当删除某一个学生的成绩时触发if exists(select name from sysobjects where name='选修表上的触发器'and type='tr')drop trigger 选修表上的触发器gocreate trigger 选修表上的触发器 on 学生档案for deleteasif exists(select 学号 from deleted where 学号 in(select 学号 from 选修) beginprint '该学生有成绩，所以不能删除!'rollback endgo六. SQL源代码（提示：执行时应一行指令一行指令的解释运行，不然全部源代码一次性拷过去运行则会出错的，原因很简单，比如当你还没有建立数据库之前不能访问某个表等。）create database 学生信息管理create table 院系(院系编号 smallint,院系名称 char(16),primary key(院系编号)create table 学生档案(学号 char(12),姓名 char(22),性别 char(2)check(性别 in ('男','女'),专业 char(16),联系方式 char(12),联系地址 char(40),院系编号 smallint,primary key(学号),foreign key(院系编号)references 院系(院系编号)on update cascade)create table 课程(课程编号 char(10),课程名称 char(20),学时 smallint,院系编号 smallint,primary key(课程编号),foreign key(院系编号)references 院系(院系编号)on update cascade)create table 选修(学号 char(12),课程编号 char(10),分数 numeric(4,1)check(分数<='100.0' and 分数 >='0'),primary key(学号,课程编号),foreign key(学号)references 学生档案(学号) on update no action,foreign key(课程编号)references 课程(课程编号) on update no action)-从选修表中查询成绩的函数if exists(select name from sysobjects where name='成绩查询'and type='p')drop procedure 成绩查询gocreate procedure 成绩查询 Sno char(12),Cno char(10)asif exists(select 学号,课程编号 from 选修 where 学号=Sno and 课程编号=Cno)select 学号,课程编号,分数from 选修where 学号=Sno and 课程编号=Cnoelseprint '没有该学号的学生或课程'goexecute 成绩查询 '08080605227','06232023'-从学生档案表中查询学生基本信息的函数if exists(select name from sysobjects where name='学生基本信息查询' and type='p')drop procedure 学生基本信息查询gocreate procedure 学生基本信息查询 Sno char(12) asif exists(select 学号 from 学生档案 where 学号=Sno)select *from 学生档案where 学号=Snoelseprint '不存在这样的学生!'goexecute 学生基本信息查询 '08080605227'-从学生档案表中删除对应的学生if exists(select name from sysobjects where name='删除学生记录'and type='p')drop procedure 删除学生记录gocreate procedure 删除学生记录 Sno char(12)asif exists(select * from 学生档案 where 学号=Sno)delete from 学生档案where 学号=Snoelseprint '没有该学生!'goexecute 删除学生记录 '12345678910'execute 删除学生记录 '09080605227'-向学生档案表插入数据的函数if exists(select name from sysobjects where name='向学生档案表输入'and type='p')drop procedure 向学生档案表输入gocreate procedure 向学生档案表输入 Sno char(12),Sname char(22),Ssex char(2),Szhuanye char(16),Scontact char(12),Saddress char(40),Dno smallintasinsert into 学生档案 values(Sno,Sname,Ssex,Szhuanye,Scontact,Saddress,Dno)goexecute 向学生档案表输入 '09080605227','jack','男','计算机','15012345652','abbbb','1010'execute 向学生档案表输入 '12345678910','john','男','计算机','15292768459','abcdefg','1010'-级联更新（从院系表到学生档案表、课程表）if exists(select name from sysobjects where name='更新院系编号'and type='p')drop procedure 更新院系编号gocreate procedure 更新院系编号 Dno smallint,Dname char(16)asupdate 院系set 院系编号=Dnowhere 院系名称=Dnamegoexecute 更新院系编号 1010,'计算机科学系' -成绩表上的触发器，当删除某一个学生的成绩时触发if exists(select name from sysobjects where name='选修表上的触发器'and type='tr')drop trigger 选修表上的触发器gocreate trigger 选修表上的触发器 on 学生档案for deleteasif exists(select 学号 from deleted where 学号 in(select 学号 from 选修) beginprint '该学生有成绩，所以不能删除!'rollback endgo完成日期：2010/12/13 第11页