北邮数据库实验二用SQL Server实现数据库设计

实验二用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．练习删除视图。