《数据库原理及课程设计》实验指导书(共18页).doc

精选优质文档-倾情为你奉上辽东学院自编教材数据库原理实验指导书鲁琴 李荣霞 编（信息管理与信息系统专业用）信息技术学院 2011年1月目 录专心-专注-专业实验一 SQL Server环境、数据定义与数据更新实验类型： 验证 实验课时： 2 指导教师： 时 间：20 年 月 日课 次：第 节 教学周次：第 周 实验分室： 实验台号： 实 验 员： 一、 实验目的1. 熟悉SQL Server 2000开发环境，了解各常用工具的使用。2. 掌握数据库服务器的本机注册，熟悉网络中其他数据库服务器的注册。3. 掌握数据库的创建、删除。4. 熟练掌握命令方式下各种难度表的创建。5. 掌握命令方式下不同类型索引的创建与删除。6. 熟练掌握命令方式下对表中数据的增、删、改。二、 实验内容及要求要将所有实验内容写在“实验结果”，并在本实验完成时完成实验报告并上交。1. 创建数据库要求：（1） 创建的数据库名为你的姓名。（2） 在默认路径下建库，能指出这个默认的路径。（3） 会在某一盘符下以你的班级姓名学号命名的文件夹中创建数据库。2. 定义基本表要求：（1） 在建好的数据库中用命令方式创建三个关系，分别为学生关系student、课程关系course、选课关系sc。（2） 学生关系的关系模式为：student(sid,sname,ssex,sage, sdept)，各字段分别代表学号、姓名、性别、年龄、所在系。sid设为主键。（3） 课程关系的关系模式为：course(cid,cname,credit)，各字段分别代表课程号、课程名、学分。cid设为主键。（4） 选课关系的关系模式为：sc(sid,cid,score)， 各字段分别代表学号、课程号、成绩。cid和sid为复和主键，sid和cid为外键。（5） 各字段数据类型要设置恰当，并对ssex、score设置合理的约束。（6） 将建表命令存成脚本文件，可存为一个文件也可存为独立的多个脚本文件。3. 修改基本表要求：（1） 向student表增加“入学时间”列，其数据类型为日期型。（2） 将course表中的cname列加宽到60个字符。（3） *在student 表中增加完整性约束定义，使sage取值在1540之间。（4） *增加课程名称必须取唯一值的约束条件。（5） 删除student表中“入学时间”列。4. 删除基本表要求：*删除sc表。5. 索引的建立、删除要求：（1） 为表sc按学号升序和课程号降序建唯一索引sci。（2） *在实例数据库pubs中，为表authors在au_id上建立聚集索引。（3） 删除表sc的索引sci。6. 插入数据要求：在建好的3个表student,course,sc中各插入2条记录，要求内容如下。用命令方式写出相关命令(注意顺序)：CoursecidcnamecreditC801高等数学3C802C+语言4ScsidcidscoreC80154C80270studentsidsnamessexsagesdept葛文卿女22国际贸易郑秀丽男21<NULL>7. 修改数据要求：（1） 将“郑秀丽”的系改为“国际贸易”系。（2） 将所有学生的成绩提高到原来的1.1倍。（3） 将所有学生的年龄加1岁。（4） 将“国际贸易”系所有学生的成绩置零。8. 删除数据要求：（1） 删除学号为“”的学生记录。（2） 删除所有学生选课记录。（3） 删除计算机科学系所有学生的选课记录。三、 实验结果-创建基本表-建立学生表create table Student(sid char(7) not null primary key,sname char(5),ssex char(1),sage int,sdept char(20);-建立课程表create table Course(cid char(4) not null primary key,cname char(20),credit float,);-成绩表create table Sc(sid char(7),cid char(4),score int,primary key(sid,cid),foreign key(sid)references Student(sid),foreign key(cid)references Course(cid);-修改Course的cid长度，失败alter table Course alter column cid char(4);-删除course表drop table Course;-3.修改基本表-向student表增加“入学时间”列，其数据类型为日期型alter table Student add 入学时间 date;-将course表中的cname列加宽到个字符alter table Course alter column cname varchar(60);-*在student 表中增加完整性约束定义，使sage取值在之间alter table Student-*增加课程名称必须取唯一值的约束条件-删除student表中“入学时间”列alter table Student drop column 入学时间;-5.索引的建立,删除-为表sc按学号升序和课程号降序建唯一索引scicreate unique index sci on Sc(sid asc,cid desc);-*在实例数据库pubs中，为表authors在au_id上建立聚集索引-删除表sc的索引scidrop index Sc.sci;-6.插入数据/*在建好的个表student,course,sc中各插入条记录CoursecidcnamecreditC801高等数学3C802C+语言4ScsidcidscoreC80154C80270studentsidsnamessexsagesdept葛文卿女22国际贸易郑秀丽男21<NULL>*/insert into Course(cid,cname,credit)values('C801','高等数学',3);insert into Course values('c802','c+语言',4);insert into Student(sid,sname,ssex,sage,sdept)values('','葛文卿','女',22,'国际贸易');insert into Student (sid,sname,ssex,sage,sdept)values('','郑秀丽','男',21); -7.修改数据-（）将“郑秀丽”的系改为“国际贸易”系esupdate Student set sdept='国际贸易' where sname='郑秀丽'-（）将所有学生的成绩提高到原来的.1倍update Sc set score=score*1.1;-（）将所有学生的年龄加岁update Student set sage=sage+1;-（）将“国际贸易”系所有学生的成绩置零update Sc set score=0 where sid in (select sid from Student where sdept='国际贸易');-8.删除数据-（1）删除学号为“”的学生记录DELECT FROM Student WHERE sid=;-（2）删除所有学生选课记录Delect from Sc;-（3）删除计算机科学系所有学生的选课记录Delect from Sc where (select cid from Course where cname=计算机科学);四、 实验中遇到的问题及解决方法本次实验成绩项目及比例实验操作（40%）报告书写（40%）出勤和课堂纪律（10%）课堂提问（10%）得分成绩合计教师签字：批改日期：实验二 数据查询实验类型： 设计 实验课时： 2 指导教师： 时 间：20 年 月 日课 次：第 节 教学周次：第 周 实验分室： 实验台号： 实 验 员： 一、 实验目的1. 掌握查询的基本语法格式。2. 熟练掌握单表查询。3. 掌握连接查询。4. 熟悉嵌套查询。二、 实验内容及要求要求将所有实验内容写在“实验结果”，并在本实验完成时完成实验报告并上交。对实验一所创建的数据库中的三个表：student、course和sc，用SQL完成下面的操作。1. 简单查询（1） 查询全体学生的学号与姓名。（2） *查询全体学生的姓名及其出生年份。（3） *查询选修了课程的学生学号。（4） 查询平均成绩大于60分的课程号和平均成绩。（5） 查询考试成绩有不及格的学生的学号。（6） 查询年龄不在2025岁之间的学生学号、姓名和年龄。（7） *查询计算机系、国际贸易系、数学系学生的姓名和性别。（8） *查询有多少学生专业不确定。（9） *查询所有姓刘的学生的姓名、学号和性别。（10） 查询名字中第2个字为“阳”字的学生的学号、姓名。（11） 查询所有不姓张的学生姓名。（12） *查询选修课程号为“c801”或“c802”且分数不低于90分的学生的学号、课程号和成绩。（13） *查询没有考试成绩的学生的学号和课程号。（14） *查询选修课程号为“c801”的学生的学号及成绩，按成绩降序排列。（15） 查询选修了课程的学生人数。（16） 查询各个课程号及相应的选课人数。（17） 查询选修了3门以上课程的学生学号。2. 复杂检索（1） *查询单科成绩不及格的学生的学号、姓名、课程名、系别及成绩。（2） *查询选修了2门以上课程的学生学号。（3） *查询既不是外语系、数学系，也不是财务会计系的学生的姓名和系别。（4） 查询每个学生及其选修课程的情况，要求结果当中包含学号、姓名、性别、所在系、课名、成绩。（5） *查询选修了2号课程且成绩在90分以上的所有学生的学号、姓名。（6） *查询与“张三”在同一个系学习的学生的学号、姓名、系别。（7） 查询选修了课程名为“信息系统”的学生学号和姓名。（8） *查询每个学生超过他选修课程平均成绩的课程号。（9） *查询其它系中比计算机科学系所有学生年龄都小的学生姓名及年龄。（10） *查询所有选修了1号课程的学生姓名。三、 实验结果-实验二 数据查询-1.简单查询-（）查询全体学生的学号与姓名。select sid,sname from Student;-（）*查询全体学生的姓名及其出生年份。select sname,DATENAME(YEAR,GETDATE()-sage from Student;select YEAR(GETDATE() from student;-获取当前年份-（）*查询选修了课程的学生学号。select sid from Sc where cid is not null;-（）查询平均成绩大于分的课程号和平均成绩。select AVG(score),cid from Sc where AVG(score)>60;-（）查询考试成绩有不及格的学生的学号。select sid from Sc where score<60;-（）查询年龄不在岁之间的学生学号、姓名和年龄。select sid,sname,sage from Student where sage between 20 and 25;-（）*查询计算机系、国际贸易系、数学系学生的姓名和性别。select sname,ssex from Student where sdept in('计算机','国际贸易','数学');-（）*查询有多少学生专业不确定。select COUNT(*) from Student where sdept is null;-（）*查询所有姓刘的学生的姓名、学号和性别。select sname,sid,ssex from Student where sname like '刘%'-（）查询名字中第个字为“阳”字的学生的学号、姓名。select sid,sname from Student where sname like '_阳%'-（）查询所有不姓张的学生姓名。select sname from Student where sname not like '张%'-（）*查询选修课程号为“c801”或“c802”且分数不低于分的学生的学号、课程号和成绩。select student.sid,cid,score from Student,Sc where Student.sid=sc.sid and cid='c801,c802' and score>=90;select sid,cid,score from Sc where cid='c801,c802' and score>=90;-（）*查询没有考试成绩的学生的学号和课程号。select sid,cid from Sc where score is null;-（）*查询选修课程号为“c801”的学生的学号及成绩，按成绩降序排列。select sid,score from Sc where cid='c801' order by score desc;-（）查询选修了课程的学生人数。select COUNT(*) from Sc where cid is not null; -（）查询各个课程号及相应的选课人数。select cid,COUNT(sid)from Sc group by cid; -（）查询选修了门以上课程的学生学号。select sid from Sc group by sid having COUNT(cid)>3;-2.复杂检索-（）*查询单科成绩不及格的学生的学号、姓名、课程名、系别及成绩。select sc.sid,sname,cname,sdept,score from Student,Sc,Course where Student.sid=sc.sid and Course.cid=sc.cid and score<60;-（）*查询选修了门以上课程的学生学号。select sid from Sc group by sid having COUNT(*)>=2; -（）*查询既不是外语系、数学系，也不是财务会计系的学生的姓名和系别。select sname,sdept from Student where sdept not in ('外语','数学','财务会计');-（）查询每个学生及其选修课程的情况，要求结果当中包含学号、姓名、性别、所在系、课名、成绩。select student.sid,sname,ssex,sdept,cname,score from Student,Sc,Course where Student.sid=sc.sid and sc.cid=Course.cid;-（）*查询选修了c802号课程且成绩在分以上的所有学生的学号、姓名。select student.sid,sname from Sc,Student where student.sid=sc.sid and score>=90;-（）*查询与“张三”在同一个系学习的学生的学号、姓名、系别。select sid,sname,sdept from Student where sdept in (select sdept from Student where sname='张三');-（）查询选修了课程名为“信息系统”的学生学号和姓名。select student.sid,sname from Student,Course,Sc where student.sid=sc.sid and sc.cid=Course.cid and cname='信息系统'-（）*查询每个学生超过他选修课程平均成绩的课程号。select cid from Sc group by cid,score having score>AVG(score);-（）*查询其它系中比计算机科学系所有学生年龄都小的学生姓名及年龄。select sname,sage from Student group by sname,sage,sdept having sdept!='计算机'-（）*查询所有选修了（号）课程名为的学生姓名。select sname from Course,Student,Sc where student.sid=sc.sid and sc.cid=Course.cid and cname='1'四、 实验中遇到的问题及解决方法本次实验成绩项目及比例实验操作（40%）报告书写（40%）出勤和课堂纪律（10%）课堂提问（10%）得分成绩合计教师签字：批改日期：实验三 数据安全性实验类型： 验证 实验课时： 2 指导教师： 时 间：20 年 月 日课 次：第 节 教学周次：第 周 实验分室： 实验台号： 实 验 员： 一、 实验目的1. 掌握视图的定义以及与基表的区别。2. 掌握视图的建立、删除、查询、更新。3. 熟悉用户的创建。4. 掌握用户权限的授予。5. 掌握用户权限的收回。6. 熟悉数据库角色。二、 实验内容及要求要求将所有实验内容写在“实验结果”，并在本实验完成时完成实验报告并上交。1. 建立信息系学生情况视图Info_student，通过该视图可查询学生的学号、姓名、年龄。2. 建立信息系选修了1号课程的学生视图Info_student1，通过该视图可查询学生的学号、姓名、成绩。3. 在Info_student1的基础上，建立信息系选修了1号课程且成绩在90分以上的学生视图。4. 建立视图，将其定义为学生的学号及他的平均成绩。5. 建一视图myview，通过该视图可查询某个学生某门课的成绩，要求内容有学号、姓名、课程名、成绩。6. 删除视图Info_student1。7. 查询信息系学生视图Info_student中年龄小于20岁的学生。8. 将信息系学生视图Info_student中学号为1的学生姓名改为“马丽”。9. 向信息系学生视图Info_student中插入一个新的学生记录“，刘一，19”。10. 删除信息系学生视图Info_student中学号为“”的记录。11. 利用可视化方式在企业管理器下完成如下操作题：（1） 新建登录账号aa和bb。（2） 将aa转换成pubs库下的用户，并授予对authors表的select、 insert、 update、 delete权限。（3） 新建角色my_role，为其授予对pubs库下authors表的select、 insert、 update、 delete权限，通过将用户添加到角色的方法，使bb用户自动具有my_role拥有的权限。12. 用命令方式完成权限的授予与收回：（1） 授予用户aa查询student表的权限。（2） 把查询student表和修改学生学号的权限授给用户bb。（3） 收回用户aa和bb查询student表的权限。（4） 收回用户bb修改学生学号的权限。三、 实验结果-实验三 数据安全性-1.建立信息系学生情况视图Info_student，通过该视图可查询学生的学号、姓名、年龄。create view Info_student (sid,sname,sage) as select sid,sname,sage from Student;-2.建立信息系选修了号课程的学生视图Info_student1，通过该视图可查询学生的学号、姓名、成绩。create view Info_student1 (sid,sname,scoure) as select student.sid,sname,score from Student,Sc where student.sid=sc.sid and student.sdept='信息' and cid='c801'-3.在Info_student1的基础上，建立信息系选修了号课程且成绩在分以上的学生视图。create view Info_student2 as select * from Info_student1 where scoure>90;-4.建立视图，将其定义为学生的学号及他的平均成绩。create view Info_student3 (sid,avgscore) as select sid,AVG(score) from Sc group by sid;-5.建一视图myview，通过该视图可查询某个学生某门课的成绩，要求内容有学号、姓名、课程名、成绩。create view myview (sid,sname,cname,score) as select student.sid,sname,cname,score from Student,Sc,Coursewhere student.sid=sc.sid and sc.cid=Course.cid;-6.删除视图Info_student1。drop view Info_student1;-7.查询信息系学生视图Info_student中年龄小于岁的学生。select * from Info_student where sage<20;-8.将信息系学生视图Info_student中学号为的学生姓名改为“马丽”。update Info_student set sname='马丽' where sid='1'-9.向信息系学生视图Info_student中插入一个新的学生记录“，刘一，”。insert Info_student values('','刘一',19);-10.删除信息系学生视图Info_student中学号为“”的记录。delete from Info_student where sid=''-/*11.利用可视化方式在企业管理器下完成如下操作题：-（）新建登录账号aa和bb。-（）将aa转换成pubs库下的用户，并授予对authors表的select、insert、update、delete权限。-（）新建角色my_role，为其授予对pubs库下authors表的select、insert、update、delete权限，-通过将用户添加到角色的方法，使bb用户自动具有my_role拥有的权限。*/-12.用命令方式完成权限的授予与收回：-（）授予用户aa查询student表的权限。grant select to aa;-（）把查询student表和修改学生学号的权限授给用户bb。GRANT update(sid),SELECT ON Student to bb;-（）收回用户aa和bb查询student表的权限。revoke select from aa,bb;-（）收回用户bb修改学生学号的权限。revoke update(sid) on student from bb;四、实验中遇到的问题及解决方法本次实验成绩项目及比例实验操作（40%）报告书写（40%）出勤和课堂纪律（10%）课堂提问（10%）得分成绩合计教师签字：批改日期：课程设计实验类型： 综合设计 实验课时： 指导教师： 时 间：20 年 月 日课 次：第 节 教学周次：第 周 实验分室： 实验台号： 实 验 员： 一、实验目的1. 学习对应用系统的需求分析2. 学习数据库设计3. 数据库设计在SQL Server中的实现二、实验内容及要求1学生可查阅相关资料，结合数据库原理课程的内容，自拟应用系统数据库设计题目。可供参考的题目：XX学生管理信息系统数据库设计XX教学排课管理系统数据库设计XX工厂生产管理系统数据库设计XX网络教学管理系统数据库设计XX图书管理系统数据库设计XX超市仓库管理系统数据库设计XX教学科研管理系统数据库设计XX在线考试系统数据库设计XX人事管理系统数据库设计要求：题目要结合具体的应用（如数据库原理课程在线考试系统），以便进行下一步具体的数据库分析、设计与实现。2系统需求分析3数据库设计要求：（1）画出系统ER图。（2）将ER图转化为关系模式，写出各关系模式并标明各自的主键和外键。4. 数据库实现要求：选择一种关系数据库如SQL Server，在SQL Server中实现应用系统数据库。包括建立数据库，数据表的定义与修改，索引的建立与删除，数据的更新（包括向数据表中插入数据、修改数据、删除数据），数据的查询（包括单表查询、连接查询、嵌套查询），定义视图，查询视图，更新视图等。5系统实现（选做）要求：选择一种软件开发工具实现系统。三、实验结果四、实验中遇到的问题及解决方法本次实验成绩项目及比例实验操作（40%）报告书写（40%）出勤和课堂纪律（10%）课堂提问（10%）得分成绩合计教师签字：批改日期：