《网络数据库》期中作业-学生成绩管理系统设计

网络数据库期中作业课题：学生成绩管理系统 2011年05月17日学生成绩管理系统、学生成绩管理系统设计背景本项目的设计者是08电信（2）第十四小组，服务对象是西北民族大学在校学生，用户是学生、办公室。由于考试后成绩整理及成绩查询所耗费的时间较大，所以开发此软件对学生成绩进行管理。具体包括以下的内容:（1）某大学有若干名学生，每个学生每学期必须学习若干门课程。（2）每个学生有学号、姓名、班级等基本信息。（3）没门课程有课程号课程名称、任课教师、学分等信息。（4）学校需要对学生的基本信息、所学课程、成绩进行统一管理，以便于对信息进行查询、浏览和修改。根据其建立的表有stuxx、teaxx、stuzy、choose、course表。choose（学号、课程号、成绩）course（课程号、课程名、学时数、职工号）stuxx（学号、姓名、性别、年龄、专业）teaxx（职工号、姓名、性别、职称）stuzy（学院名、专业）、建立的五个表及其数据类型如下：表一：stuxxcreate database sgmsgouse sgms create table stuxx(-学号sno varchar(3) not null primary key,-姓名sname varchar(6) not null,-性别ssex varchar(2) not null,-年龄sage smallint,-专业dept varchar(4),)插入数据：insert into stuxxvalues(101,张明,男,20,电信)insert into stuxxvalues(102,王楠,女,21,土木)insert into stuxxvalues(103,黎明,男,19,化工)insert into stuxxvalues(104,淳嫔,女,19,化工)insert into stuxxvalues(875,李丽,女,19,电信)insert into stuxxvalues(111,郭美,女,21,管理)insert into stuxxvalues(117,张灿,女,22,生科)insert into stuxxvalues(118,李波,男,22,土木)insert into stuxxvalues(123,安宁,男,21,电信)insert into stuxxvalues(128,王海,男,23,电信)insert into stuxxvalues(130,小强,男,16,土木)insert into stuxxvalues(132,保良,男,18,管理)insert into stuxxvalues(113,符强,男,23,生科)insert into stuxxvalues(289,米粒,女,21,化工)insert into stuxxvalues(163,刘雨,女,20,电信)insert into stuxxvalues(164,宇通,男,23,生科)insert into stuxxvalues(178,郭结,女,21,管理)insert into stuxxvalues(190,绿叶,女,21,土木)insert into stuxxvalues(193,安华,男,21,土木)insert into stuxxvalues(152,灿加,男,24,电信)列名数据类型长度字段含义Snovarchar3学生学号Snamevarchar6学生姓名Ssexvarchar2性别Sagesmallint4年龄Deptvarchar4专业表二：teaxxcreate table teaxx(-教工号tno varchar(3) not null primary key,-姓名tname varchar(4) not null,-性别tsex varchar(2) not null,-职称prof varchar(6),)插入数据：insert into teaxxvalues(001,郭华,男,副教授)insert into teaxxvalues(002,纳迦,男,副教授)insert into teaxxvalues(003,刘阳,女, 助教)insert into teaxxvalues(004,李勇,男, 讲师)insert into teaxxvalues(005,李湘,女, 教授)insert into teaxxvalues(006,张三,男,副教授)insert into teaxxvalues(007,李斯,男, 助教)insert into teaxxvalues(008,杨雪,女, 讲师)insert into teaxxvalues(009,马家,男, 教授)insert into teaxxvalues(010,馨予,女, 教授)insert into teaxxvalues(011,小丹,女,副教授)insert into teaxxvalues(012,灿梅,女, 教授)insert into teaxxvalues(013,孙俪,女, 助教)insert into teaxxvalues(014,杨丹,女, 讲师)insert into teaxxvalues(015,周迅,女,副教授)insert into teaxxvalues(016,逸迅,男, 教授)insert into teaxxvalues(017,德华,男,副教授)insert into teaxxvalues(018,品超,男, 助教)insert into teaxxvalues(019,王欢,女,副教授)insert into teaxxvalues(020,李丽,女,副教授)列名数据类型长度字段含义TnoVarchar3教工编号TnameVarchar4教师姓名TsexVarchar2教师性别Profvarchar6教师职称表三：stuzycreate table stuzy(-专业dept varchar(10),-学院coll varchar(10),)insert into stuzyvalues(电信,电气)insert into stuzyvalues(自动化,电气)insert into stuzyvalues(藏物,电气)insert into stuzyvalues(通信,电气)insert into stuzyvalues(制药,化工)insert into stuzyvalues(藏数,计算机)insert into stuzyvalues(岩石力学,土木)insert into stuzyvalues(土建,土木)insert into stuzyvalues(桥梁,土木)insert into stuzyvalues(测绘,土木)insert into stuzyvalues(藏工商,管理)insert into stuzyvalues(统计学,管理)列名数据类型长度字段含义DeptVarchar10专业Collvarchar10学院表四：choosecreate table choose( -学号sno varchar(3) not null references stuxx(sno),-课程号cno varchar(5) not null references course(cno),-成绩degree decimal(4,1)插入数据:insert into choosevalues(101,10001,72)insert into choosevalues(102,10001,88)insert into choosevalues(103,10001,90)insert into choosevalues(104,10001,89)insert into choosevalues(101,10007,89)insert into choosevalues(875,10005,78)insert into choosevalues(117,10007,90)insert into choosevalues(118,10004,98)insert into choosevalues(123,10007,97)insert into choosevalues(128,10010,54)insert into choosevalues(130,10011,88)insert into choosevalues(132,10013,56)insert into choosevalues(113,10019,77)insert into choosevalues(289,10012,72)insert into choosevalues(163,10015,60)insert into choosevalues(164,10018,75)insert into choosevalues(178,10017,56)insert into choosevalues(190,10020,56)insert into choosevalues(193,10013,59)insert into choosevalues(152,10014,60)列名数据类型长度字段含义SnoVarchar3学生学号CnoVarchar5课程编号Degreedecimal（4,1）学生成绩表五：coursecreate table course(-课程号cno varchar(5) not null primary key,-课程名cname varchar(10) not null,-教工号tno varchar(3) references teaxx(tno),-学时 ct smallint,)插入数据：insert into coursevalues(10001,软件工程,001,36)insert into coursevalues(10002,数据库,001,72)insert into coursevalues(10003,C语言,002,72)insert into coursevalues(10004,计算机网络,003,108)insert into coursevalues(10005,大学英语,004,108)insert into coursevalues(10006,物理学,005,72)insert into coursevalues(10007,概率论,005,108)insert into coursevalues(10008,大学语文,006,72)insert into coursevalues(10009,马原,007,72)insert into coursevalues(10010,电磁场,008,72)insert into coursevalues(10011,高等数学,009,108)insert into coursevalues(10012,制药学,010,72)insert into coursevalues(10013,控制原理,011,72)insert into coursevalues(10014,电路,012,108)insert into coursevalues(10015,信号与系统,011,108)insert into coursevalues(10016,软件技术,013,72)insert into coursevalues(10017,混泥土结构,014,72)insert into coursevalues(10018,公共关系,015,72)insert into coursevalues(10019,民族理论,016,72)insert into coursevalues(10020,计算机基础,017,72)列名数据类型长度字段含义CnoVarchar5课程编号CnameVarchar10课程名称TnoVarchar3教师编号Ctsmallint4学时、SQL语句：、对stuzy表增加两列，列名为：辅导员（fdy）和班级（class）。ALTER TABLE stuzy add fdy varchar；ALTER TABLE stuzy add class varchar；、建立基于学生信息表土木系学生、教师表男老师的视图。create view tm_studentasselect sno,sname,ssex,sage,deptfrom stuxxwhere dept=土木;create view man_teacherasselect tno,tname,tsex,proffrom teaxxwhere tsex=男; 删除tm_student 的视图drop view tm_student; 、创建用户 设置权限。create login student with password=411;use sgms;create user student for login student;gogrant select on choose TO student、收回权限：revoke select on choose TO student修改权限：deny select on choose TO student、更新：修改choose表中对学号为193的学生的成绩加1分。update choose set degree=degree+1 where sno=193;更新后：更新前：、删除学号为101学生的记录。deletefrom stuxxwhere sno=101;deletefrom choosewhere sno=101;、建立四个查询。 单表查询：查询stuxx中的所有记录的Sname、Ssex和dept列。select Sname,Ssex,deptFrom stuxx;查询Score表中成绩在55到75之间的所有记录。SELECT snoFROM choosewhere degree55 AND degree75;以degree降序查询choose表的所有记录。SELECT *FROM chooseorder by degree DESC ;、集合函数查询查询学生信息表中的男生的总人数及平均年龄。select count(*),avg(sage)from stuxxwhere ssex=男;查询学生信息表中的最大年龄。select max(sage)from stuxx、嵌套查询查询choose表中最高分学生的学号和课程号。select sno, cnofrom choosewhere degree=(select max(degree)from choose)查询选修了课程名为”软件工程”的学生学号和姓名select sno,snamefrom stuxxwhere sno in (select sno from choose where cno in (select cno from course where cname=软件工程);多表格查询查询学生的选课信息及其成绩select stuxx.sno,stuxx.sname,stuxx.dept,choose.degree,ofrom stuxx,choosewhere stuxx.sno=choose.sno;查询选修了课程“10001”或者选修了课程“10007”的学生。select snofrom choosewhere cno=10001unionselect snofrom choosewhere cno=10007;、 数据库安全措施有哪些？数据库数据安全包括：1、用户操作管理权限。2、服务器设置系统安全。3、服务器物理位置安全。4、线路通讯安全、加密传输.。5、数据库安全备份、转移、灾难恢复。.6、数据库应用系统安全。索引的功能是？引入索引的目的是为了加快查询的速度。在无条件查询的情况下会查询所有的纪录，但是在有条件查询的情况下，如果仍然还要先查询出所有的纪录，然后才能找到符合条件的纪录的话，未免有些迟钝，显然缺失技巧。设想一个包含数百万纪录的表，要在其中挑出符合条件的一条纪录，如果没有索引，RDBMS就要顺序地、逐条地读取纪录