年末数据库知识检测(针对才鸟)
用企业管理器创建学生管理数据库;自己设计表。在数据库中创建数据表:学生表S、课程表C、教师表T和选课表SC:
S(S#, Sname, Sbirth, Ssex, Sclass, Sage), 各字段含义分别为学号、姓名、生日、性别、班级、年龄;
C(C#, Cname, T#),各字段含义分别为课号、课名、教授该课程的教师编号,一门课一个老师教;
SC(S#, C#, grade,ord) ,各字段含义分别为学号、课号、成绩、学生在该课程的排名;
T(T#, Tname,Tposition) ,各字段含义分别为教师编号、教师名、职称。
1) S#、C#为字符型,长度都为5;grade类型为tinyint;ord类型为smallint;Sbirth为日期时间型;
2) 看看各表的主码是什么,为各表设置主码;
3) 性别的缺省值为“男”,取值只能有两种:男、女;
4) Sage是一个计算字段,等于当前时间与生日(Sbirth)之间相差的年数;
5) C表的T#是外键,参照T表的T#;
6) C表的Cname不可为空,且值是唯一的,不可重复;
7) C表的Grade 小于等于100,大于等于0;
8) T表的T#是IDENTITY属性的字段,初始值为10,增量为1;
9) T表的Tposition字段是字符型,长度为6,缺省值为“助教”,取值只能有四种:助教、讲师、副教授、教授;
10) 未说明的字段自己根据情况定义其类型。
1. 写出创建各表的SQL语句。
2. 为SC表创建索引S_Name,该索引旨在提高对S表按学生名字进行查询的检索速度。
3. 为SC表创建索引SC_C,该索引旨在提高对SC表按课号查询的检索速度。
4. 写出招入新生的SQL语句;
5. 写出增加新课程的SQL语句;
6. 写出增加新老师的SQL语句;
7. 写出学生增加选课的SQL语句;
8. 写出修改学生信息的SQL语句;
9. 若学号被修改,则选课表中该生所选课程的学号也要做同样的修改。
10. 写出删除学生纪录的SQL语句,要求一次只能删一条记录,且被删学生在选课表中的纪录也要被删除。
11. 写出修改学生成绩的存储过程upd_grade,它有四个参数:@T#(修改成绩的教师编号)、@S#、@C#、@Grade。该存储过程把SC表中学号为@S#、课号为@S#的记录成绩改为@Grade,但只有教@C#这门课的教师才可以修改学@C#这门课的学生的成绩,否则,报错,修改失败。
12. 请创建存储过程give_ord,该存储过程有参数@C#,该存储过程的功能是将SC表中课程号为@C#的记录的ord字段填入学生在该课程的名次。成绩相同的学生的名次也相同。例如,A、B、C、D四人成绩分别是95、93、93、80,则四人的名次分别是1、2、2、4。
创建后,执行该存储过程,为选了课号为DB的课的学生排名次,看看排的结果对不对。(提示:用到游标。)
13. 请创建存储过程give_allord,该存储过程调用存储过程give_ord,其功能是将SC表所有的选课排名。(提示:仍然要用到游标。)
14. 创建登录帐户tchr1和tchr2。
15. 创建普通教师角色tchr,属于该角色的数据库用户具有对SC表的成绩字段的update权限。
16. 创建教务管理员角色adm_teach,属于该角色的数据库用户具有对S表、T表的Insert、delete、update、select权限。
17. 在数据库中添加用户teacher,其登录名为'tchr1',该用户属于角色tchr;添加用户adm_teacher,其登录名为'tchr2',该用户属于角色adm_teach和tchr。
18. 在数据库中添加用户student,属于该角色的数据库用户具有对S表、C表的查询权限,SC表的的Insert、delete、update、select权限。
19. 设计一个数据库备份的方案,用文字描述该方案,并用企业管理器实现该方案。要求:若发生数据库瘫痪,丢失的数据不超过两小时。