实验二用SQL Server实现数据库设计实验目的:1.掌握使用企业管理器创建和管理SQL Server数据库及相关数据库对象。
2.掌握使用SQL语言中的SELECT命令实现查询功能。
上机准备:1.复习有关关系数据库的基本知识和概念;2.复习有关SQL语言中SELECT命令的使用;3.了解有关SQL Server系统的组成;4.复习有关SQL Server服务器的使用和管理;5.复习有关企业管理器的基本操作;6.复习有关查询分析器的基本操作;7.了解有关SQL Server服务器的登陆账号,密码;实验内容:本实验将建立一个学校教学管理数据库,在这个数据库中,存储以下信息:1.有关学生的信息,包括学生的学号,姓名,班级,性别,出生时间,政治面貌,籍贯,入学时间,联系电话,简历;Student(Sno,Sname, Ssex,Classno,Sbirth,Status,Hometown,Enrollment,Telephone,Resume)2.有关班级的信息,包括班级的班名,专业,年级,人数,班主任;SClass(ClassNo,ClassName,Major,Grade,Number,Advisor)3.有关课程的信息,包括课程编号,课程名称,课程类别(是指该课程为必修,指选还是任选),适用专业,适用年级,开课时间(是指在每个学年的第一学期,还是第二学期),学分,周学时;Course(CourseNo,CourseName,Category,Major,Grade,StartTM,Credits, WeekHours)4.有关学年课程安排的信息,包括课程编号,开设课程的学年(例如:2002学年),开设课程的班级,任课教师;Schedule(CourseNo,StartYear,ClassNo,Teacher)5.有关学生选课的信息,包括课程编号,学生的学号,成绩;SC(CourseNo,Sno,Score)一、熟悉开发环境练习:1.启动和停止SQL Server服务2.注册服务器3.使用企业管理器和查询分析器二、使用企业管理器完成数据库及数据库对象的创建和管理实验步骤:1.创建数据库(1)要求参数:数据库名称:teaching数据库逻辑文件名:teaching_data 日志逻辑文件名:teaching_log操作系统数据文件名:e:\xxx\teaching.mdf 操作系统日志文件名:e:\xxx\teaching.ldf数据文件初始大小:5MB 日志文件初始大小:1MB 数据文件最大大小:10MB 日志文件最大大小:2MB数据文件增长增量:10% 日志文件增长增量:10%(2)创建后在database节点查看是否已包含了刚创建的数据库teaching;(3)展开teaching 节点,以下的所有对象均创建在teaching 数据库中;2.创建用户定义的数据类型(1)练习按要求创建以下数据类型数据类型名称数据描述student_no 长度为10且不允许空值的字符型;member_no 整数,且存储的值不超过30000shortstring 最多15个字符的可变长字符型(2)考虑在该数据库中可能使用的自定义数据类型(例如:学号的数据类型),并创建3.创建表(1)根据上述数据库存储的信息,决定创建几个表,并给每一个表指定一个有意义的表名;(2)根据每个表中每列存储的数据的情况,为每个列指定列名,数据类型,数据的长度,是否允许为空等列的属性;(3)使用企业管理器建立所有的表;(4)创建之后,还可以根据具体情况,再对表的结构进行修改;(包括添加列,删除列,修改已存在的列)4.实现数据完整性(1)针对每一个表分析并定义主码(Primary Key)(2)定义UIQUE约束用来规定一个列中的两行不能有相同的值;例如:希望学生的姓名是唯一的;StudentSClassCourseScheduleSC(3)针对每一个表分析外部码,并利用“关系图”定义外部码(Foreign Key),建立表之间的参照关系;Create table SC(Sno char(9),CourseNo char(4),Score smallint,Primary key(Sno,CourseNo),Foreign key(Sno) refrences Student(Sno),Foreign key(CourseNo) refrences Course(CourseNo))(4)定义缺省值方法1:直接在表设计时,定义列的default属性;例如:练习在定义“性别”列时,定义它的缺省值为“男”;方法2:创建一个缺省值对象,然后绑定到任何一个需要的列;例如:练习创建并绑定一个缺省值到学生的联系电话,缺省值为“unknown”;Create defult telephone_defult as ‘unknown’(5)创建以上约束后,练习修改约束的操作(包括增加,修改和删除以上约束);5.根据自己所在班级的情况,对已经创建的表自己输入一些相关示例数据,在输入的过程中,特地输入一些违反上述约束条件的数据,观察处理的效果;(1)使用企业管理器,完成一些记录的插入、修改和删除;(2)练习使用INSERT、UPDATE、DELETE命令完成记录的插入、修改和删除;(3)练习使用TRUNCA TE TABLE命令删除表数据;t runcate table sc(4)在练习使用命令删除数据之前,可以使用SELECT INTO命令把数据保存到一个新建的表中;select * into B from SCselect * from Bdrop table Cselect Sno,CourseNo into C from SCselect * from C三、使用企业管理器创建和管理索引1.利用表的属性对话框,观察每个表已经自动创建的索引;exec sp_help Studentexec sp_help SClassexec sp_help Scheduleexec sp_help Courseexec sp_help SC2.针对学生选课信息表创建如下索引(1)按学号+课程编号建立主键索引,索引组织方式为聚簇索引;create clustered index PK_SC on SC(Sno,CourseNo)alter table SCdrop SC_primexec sp_help SC(2)按学号建立索引,考虑是否需要创建唯一索引,索引组织方式为非聚簇索引;create unique index PK_Sno on Student(Sno)exec sp_help Student(3)按课程编号建立索引,考虑是否需要创建唯一索引,索引组织方式为非聚簇索引;create index PK_CoursNo on Course(CourseNo)drop index Course.PK_CoursNoexec sp_help Course(4)再针对学生选课信息表输入一些数据,观察表中记录位置的变化;通过观察比较非聚簇索引和聚簇索引的不同之处;3.针对其它表,练习创建索引;(考虑:是否对每个列都应创建索引?应该从哪些方面考虑是否应为此列创建索引?)4.练习索引的修改和删除操作exec sp_helpindex SCexec sp_rename'SC.PK_SC','PL_SCindex'drop index Student.PK_Snoexec sp_helpindex Student四、使用查询分析器实现以下查询1.练习课堂上举例介绍的几类查询;2.实现以下查询(1) 检索选修了课程号为C1或C2课程,且成绩高于或等于70分的学生的姓名,课程名和成绩。
SELECT sname,coursename,score FROM student,sc WHERE student.sno = sc.sno and (courseno=’C1’ or courseno=’C2’) and score >= 70(2) 检索姓“王”的所有学生的姓名和年龄。
SELECT sname, year(GETDATE()) - YEAR(birth) FROM sWHERE sname LIKE ‘王%’(3) 检索没有考试成绩的学生姓名和课程名。
SELECT sname,coursename FROM student,sc,course WHERE student.sno=sc.sno and sc.courseno=course.courseno and score is null(4) 检索年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT sname,year(GETDATE()) - YEAR(birth) age FROM student WHERE ssex =’男’ and year(GETDATE()) - YEAR(birth) > (SELECT AVG(year(GETDATE()) - YEAR(birth)) FROM student WHERE ssex =’女’)注:上述查询中所用的课程号和学号的值,可以根据自己表中的数据作修改;为了验证查询的正确,可能还需要输入或修改表中的示例数据;五、创建和管理视图1.创建视图:使用企业管理器或使用CREATE VIEW命令例1:创建视图,包含所有通信工程专业的学生的信息;CREATE VIEW TX_Student ASSELECT * FROM Student WHERE Major = ‘通信工程’ WITH CHECK OPTION 例2:创建视图,包含所有学生的学号,姓名,选课的课程名和成绩;CREATE VIEW SC_Grade ASSELECT sno,sname,coursename,score FROM Student,Course,SCWHERE Student.sno=SC.sno and o=oWITH ENCRYPTION OPTION例3:创建视图,包含所有课程的课程号,名,班级名称及每班选课的人数;CREATE VIEW SC_CLASS ASSELECT courseno,coursename,(SELECT classname,count(*) FROM SC,(SELECT student.sno sno,classname FROM sclass,student WHEREsclass.classno = student.classno and student.sno = SC.sno)CStudentWHERE CStudent.sno = SC.sno and SC.courseno = course.coursenoGROUP BY classname)FROM course2.练习修改视图的定义第一个视图,包括with check option 选项第二个视图,包括with encryption选项;(查看视图的属性,观察和以前有和不同)3.利用已经创建的视图进行查询;select distinct Sno from SC_Grade4.利用已经创建的视图修改数据(观察是否所有通过视图的修改都能实现?)5.练习删除视图。