学
生
信
息
管
理
系
统
数
据
库
设
计
与
实
现
院系:计算机科学系
班级:计科07-1班
学号:08080605227
姓名:叶尔凯西
系统名称:学生信息管理系统
一.系统需求
a.该“学生信息管理系统”只是对数据库应用技术的一个样本数据库的实例,重在对
数据库一些方法的熟悉与掌握,其中包括从数据库的概念模型到ER图的绘制,再
到数据库模式的建立(即为数据库及其基本表的建立),而后数据的录入(在此从
略),最后完成一些简单的关系运算表达式的表达和相关的Select 查询语句的使用。
b.系统结构简述:
本系统包括三个实体:院系;学生档案;课程;
实体之间的关系转换为其他表的有:选修;
实体“院系”与“学生档案”之间有一对多的关系,把院系表的主码转放到表“学
生档案”中;
实体“学生档案”与“课程”之间有多对多的关系,因此独立形成一个表并共同存
储“学生档案”与“课程”表的主码和一些自己的属性列;
c.该数据库需要进行如下数据处理:
◎从选修表中查询成绩的函数:
输入:学号和课程编号
输出:对应的学号和课程编号的分数即成绩;
◎从学生档案表中查询学生基本信息的函数:
输入:学号
输出:对应的学号的基本信息,即学号,姓名,性别,专业,联系方式,联系地址,院系编号;
◎从学生档案表中删除对应的学生记录:
输入:学号
输出:删除对应的学生记录,若没有对应的学号则输出“没有该学生!”的提示;
◎向学生档案表插入数据的函数:
输入:学生档案表的基本信息,包括所有属性列;即学号,姓名,性别,专业,联
系方式,联系地址,院系编号;
◎级联更新(从院系表到学生档案表、课程表):
输入:院系编号,院系名称
◎成绩表上的触发器,当删除某一个学生的成绩时触发:
当从学生档案表中删除学生记录时,若该学号的学生记录在选修表中也有的话,则
提示
二.概念设计(设计E – R 模型)
表1.实体间的相互联系
实体 联系 实体 院系 属于 课程 学生档案 选修 课程 院系
所属院
学生档案
学生档案 选修 课程
局部E-R 模型Ⅱ 局部E-R 模型Ⅰ
所属院院系 局部E-R 模型Ⅲ 属于 课程
院系 学生档案
三.逻辑设计(转换成关系模型)
系统中的实体有:学生档案;课程;院系; 关系模式如下:
学生档案(学号#,姓名,性别,专业,联系方式,联系地址) PK=学号,NOT NULL
课程(课程编号#,课程名称,学时,院系编号) PK=课程编号,NOT NULL FK=院系编号,参照院系表
选修(学号#,课程编号#,分数) PK=< 学号,课程编号 >,NOT NULL FK=学号,参照学生档案表 FK=课程编号,参照课程表
院系(院系编号#,院系名称) PK=院系编号,NOT NULL
学生档案
选修
课程
分数
m
n
n
1
课程编号#
课程名称
所属院
院系
n
1
院系编号#
院系名称
学号#
姓名
性别
专业
联系方式
联系地址
学时
属于
学生信息管理系统的E-R 模型
四.物理设计
表1。学生档案表结构
字段名类型特殊属性
学号char(12)Not null
姓名char(22)null
性别char(2)null
专业char(16)Null
联系方式char(12)Null
联系地址char(40)null
院系编号smallint null
表2。院系表结构
字段名类型特殊属性
院系编号Smallint Not null
院系名称char(16)null
表3。课程表结构
字段名类型特殊属性
课程编号char(10)Not null
课程名称char(20)null
学时Smallint null
院系编号smallint Null
表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 成绩查询
go
create procedure 成绩查询@Sno char(12),@Cno char(10)
as
if exists(select 学号,课程编号from 选修where 学号=@Sno and 课程编号=@Cno) select 学号,课程编号,分数
from 选修
where 学号=@Sno and 课程编号=@Cno
else
print '没有该学号的学生或课程'
go
execute 成绩查询'08080605227','06232023'
②
--从学生档案表中查询学生基本信息的函数
if exists(select name from sysobjects where name='学生基本信息查询' and type='p')
drop procedure 学生基本信息查询
go
create procedure 学生基本信息查询@Sno char(12)
as
if exists(select 学号from 学生档案where 学号=@Sno)
select *
from 学生档案
where 学号=@Sno
else
print '不存在这样的学生!'
go
execute 学生基本信息查询'08080605227'
③
--从学生档案表中删除对应的学生
if exists(select name from sysobjects where name='删除学生记录'and type='p') drop procedure 删除学生记录
go
create procedure 删除学生记录@Sno char(12)
as
if exists(select * from 学生档案where 学号=@Sno)
delete from 学生档案
where 学号=@Sno
else
print '没有该学生!'
go
execute 删除学生记录'12345678910'
execute 删除学生记录'0908*******'
④
--向学生档案表插入数据的函数
if exists(select name from sysobjects where name='向学生档案表输入'and type='p') drop procedure 向学生档案表输入
go
create procedure 向学生档案表输入@Sno char(12),@Sname char(22),@Ssex char(2),@Szhuanye char(16),@Scontact char(12),@Saddress char(40),@Dno smallint
as
insert into 学生档案values(@Sno,@Sname,@Ssex,@Szhuanye,@Scontact,@Saddress,@Dno)
go
execute 向学生档案表输入'0908*******','jack','男','计算机','150********','abbbb','1010' execute 向学生档案表输入'12345678910','john','男','计算机','152********','abcdefg','1010'
⑤
--级联更新(从院系表到学生档案表、课程表)
if exists(select name from sysobjects where name='更新院系编号'and type='p') drop procedure 更新院系编号
go
create procedure 更新院系编号@Dno smallint,@Dname char(16)
as
update 院系
set 院系编号=@Dno
where 院系名称=@Dname
go
execute 更新院系编号1010,'计算机科学系'
◎学生要查询本学期所开课程详细情况(成绩除外),并按“课程类型”分组
输入:学号
输出:学号,姓名,课程名,学分数,学时数,课程类型
设输入的学号的数值为x
Select S.学号,姓名,课程名,学分数,学时数,课程类型
From 学生as S,选修as X,课程as C
Where S.学号=X.学号and X.课程编号=C.课程编号and S.学号=x
Group by 课程类型
3.创建触发器
①
--成绩表上的触发器,当删除某一个学生的成绩时触发
if exists(select name from sysobjects where name='选修表上的触发器'and type='tr') drop trigger 选修表上的触发器
go
create trigger 选修表上的触发器on 学生档案
for delete
as
if exists(select 学号from deleted where 学号in(select 学号from 选修)) begin
print '该学生有成绩,所以不能删除!'
rollback
end
go
六.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 成绩查询
go
create procedure 成绩查询@Sno char(12),@Cno char(10)
as
if exists(select 学号,课程编号from 选修where 学号=@Sno and 课程编号=@Cno) select 学号,课程编号,分数
from 选修
where 学号=@Sno and 课程编号=@Cno
else
print '没有该学号的学生或课程'
go
execute 成绩查询'08080605227','06232023'
--从学生档案表中查询学生基本信息的函数
if exists(select name from sysobjects where name='学生基本信息查询' and type='p') drop procedure 学生基本信息查询
go
create procedure 学生基本信息查询@Sno char(12)
as
if exists(select 学号from 学生档案where 学号=@Sno)
select *
from 学生档案
where 学号=@Sno
else
print '不存在这样的学生!'
go
execute 学生基本信息查询'08080605227'
--从学生档案表中删除对应的学生
if exists(select name from sysobjects where name='删除学生记录'and type='p') drop procedure 删除学生记录
go
create procedure 删除学生记录@Sno char(12)
as
if exists(select * from 学生档案where 学号=@Sno)
delete from 学生档案
where 学号=@Sno
else
print '没有该学生!'
go
execute 删除学生记录'12345678910'
execute 删除学生记录'0908*******'
--向学生档案表插入数据的函数
if exists(select name from sysobjects where name='向学生档案表输入'and type='p') drop procedure 向学生档案表输入
go
create procedure 向学生档案表输入@Sno char(12),@Sname char(22),@Ssex char(2),@Szhuanye char(16),@Scontact char(12),@Saddress char(40),@Dno smallint
as
insert into 学生档案values(@Sno,@Sname,@Ssex,@Szhuanye,@Scontact,@Saddress,@Dno)
go
execute 向学生档案表输入'0908*******','jack','男','计算机','150********','abbbb','1010' execute 向学生档案表输入'12345678910','john','男','计算机','152********','abcdefg','1010'
--级联更新(从院系表到学生档案表、课程表)
if exists(select name from sysobjects where name='更新院系编号'and type='p') drop procedure 更新院系编号
go
create procedure 更新院系编号@Dno smallint,@Dname char(16)
as
update 院系
set 院系编号=@Dno
where 院系名称=@Dname
go
execute 更新院系编号1010,'计算机科学系'
--成绩表上的触发器,当删除某一个学生的成绩时触发
if exists(select name from sysobjects where name='选修表上的触发器'and type='tr') drop trigger 选修表上的触发器
go
create trigger 选修表上的触发器on 学生档案
for delete
as
if exists(select 学号from deleted where 学号in(select 学号from 选修))
begin
print '该学生有成绩,所以不能删除!'
rollback
end
go
完成日期:2010/12/13