sql数据库管理系统大作业

第1页学生信息管理系统数据库设计与实现一、系统需求1、该“学生信息管理系统”是对数据库应用技术的一个样本数据库的实例，其中包括从数据库的概念模型到ER图的绘制，再到数据库模式的建立（即为数据库及其基本表的建立），而后数据的录入（在此从略），最后完成一些简单的关系运算表达式的表达和相关的Select查询语句的使用。2、系统结构简述：本系统包括三个实体：院系；学生档案；课程；实体之间的关系转换为其他表的有：选修；实体“院系”与“学生档案”之间有一对多的关系，把院系表的主码转放到表“学生档案”中；实体“学生档案”与“课程”之间有多对多的关系，因此独立形成一个表并共同存储“学生档案”与“课程”表的主码和一些自己的属性列；3、该数据库需要进行如下数据处理：◎从选修表中查询成绩的函数：输入：学号和课程编号输出：对应的学号和课程编号的分数即成绩；◎从学生档案表中查询学生基本信息的函数：输入：学号输出：对应的学号的基本信息，即学号，姓名，性别，专业，联系方式，联系地址，院系编号；◎从学生档案表中删除对应的学生记录：输入：学号输出：删除对应的学生记录，若没有对应的学号则输出“没有该学生!”的提示；◎向学生档案表插入数据的函数：输入：学生档案表的基本信息，包括所有属性列；即学号，姓名，性别，专业，联系方式，联系地址，院系编号；◎级联更新（从院系表到学生档案表、课程表）：输入：院系编号，院系名称◎成绩表上的触发器，当删除某一个学生的成绩时触发：当从学生档案表中删除学生记录时，若该学号的学生记录在选修表中也有的话，则提示第2页二、概念设计（设计E–R模型）表1.实体间的相互联系实体联系实体院系属于课程学生档案选修课程院系所属院学生档案学生档案选修课程局部E-R模型Ⅱ局部E-R模型Ⅰ所属院系院系局部E-R模型Ⅲ属于课程院系学生档案第3页学生信息管理系统的E-R模型三、逻辑设计（转换成关系模型）系统中的实体有：学生档案；课程；院系；关系模式如下：学生档案（学号＃，姓名，性别，专业，联系方式，联系地址）PK=学号，NOTNULL课程（课程编号＃，课程名称，学时，院系编号）PK=课程编号，NOTNULLFK=院系编号，参照院系表选修（学号＃，课程编号＃，分数）PK=学号，课程编号，NOTNULL学生档案选修课程分数mnn1课程编号#课程名称所属院系院系n1院系编号#院系名称学号#姓名性别专业联系方式联系地址学时属于第4页FK=学号，参照学生档案表FK=课程编号，参照课程表院系（院系编号＃，院系名称）PK=院系编号，NOTNULL四、物理设计表1学生档案表结构字段名类型特殊属性学号char(12)Notnull姓名char(22)null性别char(2)null专业char(16)Null联系方式char(12)Null联系地址char(40)null院系编号smallintnull表2院系表结构字段名类型特殊属性院系编号SmallintNotnull院系名称char(16)null表3课程表结构字段名类型特殊属性课程编号char(10)Notnull课程名称char(20)null学时Smallintnull院系编号smallintNull表4选修表结构字段名类型特殊属性学号char(12)Notnull课程编号char(10)Notnull分数Numeric(4,1)null五、SQL源代码第5页1、createdatabase学生信息管理createtable院系(院系编号smallint,院系名称char(16),primarykey(院系编号))2、createtable学生档案(学号char(12),姓名char(22),性别char(2)check(性别in('男','女')),专业char(16),联系方式char(12),联系地址char(40),院系编号smallint,primarykey(学号),foreignkey(院系编号)references院系(院系编号)onupdatecascade)3、createtable课程(课程编号char(10),课程名称char(20),学时smallint,院系编号smallint,primarykey(课程编号),foreignkey(院系编号)references院系(院系编号)onupdatecascade)4、createtable选修(学号char(12),课程编号char(10),分数numeric(4,1)check(分数='100.0'and分数='0'),primarykey(学号,课程编号),foreignkey(学号)references学生档案(学号)onupdatenoaction,foreignkey(课程编号)references课程(课程编号)onupdatenoaction)5、从选修表中查询成绩的函数ifexists(selectnamefromsysobjectswherename='成绩查询'andtype='p')第6页dropprocedure成绩查询gocreateprocedure成绩查询@Snochar(12),@Cnochar(10)asifexists(select学号,课程编号from选修where学号=@Snoand课程编号=@Cno)select学号,课程编号,分数from选修where学号=@Snoand课程编号=@Cnoelseprint'没有该学号的学生或课程'goexecute成绩查询'08080605227','06232023'6、从学生档案表中查询学生基本信息的函数ifexists(selectnamefromsysobjectswherename='学生基本信息查询'andtype='p')dropprocedure学生基本信息查询gocreateprocedure学生基本信息查询@Snochar(12)asifexists(select学号from学生档案where学号=@Sno)select*from学生档案where学号=@Snoelseprint'不存在这样的学生!'goexecute学生基本信息查询'08080605227'7、从学生档案表中删除对应的学生ifexists(selectnamefromsysobjectswherename='删除学生记录'andtype='p')dropprocedure删除学生记录gocreateprocedure删除学生记录@Snochar(12)asifexists(select*from学生档案where学号=@Sno)deletefrom学生档案where学号=@Snoelseprint'没有该学生!'goexecute删除学生记录'12345678910'execute删除学生记录'09080605227'8、向学生档案表插入数据的函数第7页ifexists(selectnamefromsysobjectswherename='向学生档案表输入'andtype='p')dropprocedure向学生档案表输入gocreateprocedure向学生档案表输入@Snochar(12),@Snamechar(22),@Ssexchar(2),@Szhuanyechar(16),@Scontactchar(12),@Saddresschar(40),@Dnosmallintasinsertinto学生档案values(@Sno,@Sname,@Ssex,@Szhuanye,@Scontact,@Saddress,@Dno)goexecute向学生档案表输入'09080605227','jack','男','计算机','15012345652','abbbb','1010'execute向学生档案表输入'12345678910','john','男','计算机','15292768459','abcdefg','1010'9、级联更新（从院系表到学生档案表、课程表）ifexists(selectnamefromsysobjectswherename='更新院系编号'andtype='p')dropprocedure更新院系编号gocreateprocedure更新院系编号@Dnosmallint,@Dnamechar(16)asupdate院系set院系编号=@Dnowhere院系名称=@Dnamegoexecute更新院系编号1010,'计算机科学系'10、成绩表上的触发器，当删除某一个学生的成绩时触发ifexists(selectnamefromsysobjectswherename='选修表上的触发器'andtype='tr')droptrigger选修表上的触发器gocreatetrigger选修表上的触发器on学生档案fordeleteasifexists(select学号fromdeletedwhere学号in(select学号from选修))beginprint'该学生有成绩，所以不能删除!'rollbackendgo