实验报告.docx

1、实验报告实验_一_题目_ 数据库及基本表的建立 实验_二_题目_ 查询数据库 实验_三_题目_ 创建和使用视图、索引、存储过程 实验_四_题目_ 小型数据库规划设计 _ 实验平台：1计算机及操作系统：PC机，Windows 7；2数据库管理系统：SQL sever 2008； 计算机 学院 专业 班 学号： 姓名： 协作者：_ 教师评定： 实验题目 数据库及基本表的建立 一、实验目的1、掌握SQL SERVER的查询分析器和企业管理器的使用；2、掌握创建数据库和表的操作；二、实验内容和要求1、分别使用SQL语句、企业管理器（Enterprise Manager）创建数据库；2、使用SQL语句、

2、企业管理器（Enterprise Manager）创建数据库表；三、实验主要仪器设备和材料1计算机及操作系统：PC机，Windows 7；2数据库管理系统：SQL sever 2008；四、实验方法、步骤及结果测试创建一个教学管理数据库SC，其描述的信息有：学生信息、课程信息、教师信息、学生选课成绩、授课信息、班级信息、系部信息、专业信息。创建：student表(学生信息表)、course表（课程信息表)、teacher表（教师信息表)、student _course表（学生选课成绩表)、teacher_course表（教师上课课表）等。1、创建数据库：确定数据库名称；数据库用于学生管理，命名

3、为SC确定数据库的位置；要求：数据文件和日志文件分别存储在E盘自己的目录下。确定数据库的大小；根据实际的数据量确定数据文件的初始大小为30MB，日志文件的初始大小为3MB。确定数据库的增长；根据实际情况，确定数据文件按20%增长，日志文件按1MB增长。（1）、利用查询分析器（Query Analyzer），使用SQL语句指定参数创建数据库；实现代码及截图：SQL语句CREATE DATABASE SC ON PRIMARY ( NAME = NSC, FILENAME = NE:DBtestSC.mdf , SIZE = 30720KB , MAXSIZE = UNLIMITED, FILEG

4、ROWTH = 20%) LOG ON ( NAME = NSC_log, FILENAME = NE:DBtestSC_log.ldf , SIZE = 3072KB , MAXSIZE = 2048GB , FILEGROWTH = 1024KB )GO查询分析器执行情况：SQL语句及执行结果截图显示2、创建基本表利用查询分析器，使用SQL语句方式创建方式将下面各表建立到教学管理数据库中。(1) 创建student表字段名代码类型约束学号s_nochar(8)主键姓名snamechar(8)非空性别sexchar(2) 出生日期sbirthdaySmalldatetime学生所在院系编号d

5、nochar(6) 外键专业代码spnochar(8)外键班级编码class_nochar(4) (2)创建Course表字段名代码类型约束课程编号cnochar(10)主键课程名称cnamechar(20)非空专业代码spnochar(8)外键课程类型编号ctnotinyint理论学时lecturetinyint实验学时experimenttinyint开课学期semestertinyint课程学分credittinyint（3）创建student _course表字段名代码类型约束学号s_nochar(8)主键,与student表中s_no 外键关联,级联删除上课编号tcidsmallin

6、t主键学生成绩score tinyint (4)创建teacher表 字段名代码类型约束教师编号t_nochar(8)主键教师姓名t_namechar(8)非空性别t_sexchar(2)出生日期t_birthdaysmalldatetime教师所在院系编号dnochar(6) 外键职称tech_titlechar(10)(5)创建系部表(department) 字段名代码类型约束院系编号dnochar(6)主键院系名称dept_namechar(20)非空院系负责人headerchar(8) (6)创建专业信息表(speciality) 字段名代码类型约束专业代码spnochar(8)主键院

7、系编号dnochar(6)外键,非空专业名称spnamechar(20)非空(7)创建teacher_course表字段名代码类型约束上课编号tcidsmallint主键教师编号t_nochar(8)外键专业代码spnochar(8)外键班级编码class_nochar(4)课程编号cnochar(10)非空,外键学期semesterChar(6)学年schoolyear Char(10)(8)创建班级表(class) 字段名代码类型约束专业代码spnochar(8)主键，与 speciality 表中spno 外键关联,班级编码class_nochar(4)主键,班负责人headerchar

8、(8)3、查看各数据表之间的关系，生成数据库关系图。生成数据库关系图截图显示4、创建各表的实现代码及截图：SQL语句-创建student表USE SCGOCREATE TABLE student( s_no char(8) NOT NULL, sname char(8) NOT NULL, sex char(2) NULL, sbirthday smalldatetime NULL, dno char(6) NULL, spno char(8) NULL, class_no char(4) NULL, CONSTRAINT PK_student PRIMARY KEY CLUSTERED (s

9、_no ASC)GO-创建Course表USE SCGOCREATE TABLE Course( cno char(10) NOT NULL, cname char(20) NOT NULL, spno char(8) NULL, ctno tinyint NULL, lecture tinyint NULL, experiment tinyint NULL, semester tinyint NULL, credit tinyint NULL, CONSTRAINT PK_Course PRIMARY KEY CLUSTERED (cno ASC)GO-创建student_course表US

10、E SCGOCREATE TABLE student_course( s_no char(8) NOT NULL, tcid smallint NOT NULL, score tinyint NULL, CONSTRAINT PK_student_course PRIMARY KEY CLUSTERED(s_no ASC,tcid ASC)GO-创建teacher表USE SCGOCREATE TABLE teacher( t_no char(8) NOT NULL, t_name char(8) NOT NULL, t_sex char(2) NULL, t_birthday smallda

11、tetime NULL, dno char(6) NULL, tech_title char(10) NULL, CONSTRAINT PK_teacher PRIMARY KEY CLUSTERED (t_no ASC)GO-创建department表USE SCGOCREATE TABLE department( dno char(6) NOT NULL, dept_name char(20) NOT NULL, header char(8) NULL, CONSTRAINT PK_department PRIMARY KEY CLUSTERED (dno ASC)GO-创建special

12、ity表USE SCGOCREATE TABLE speciality( spno char(8) NOT NULL, dno char(6) NOT NULL, spname char(20) NOT NULL, CONSTRAINT PK_speciality PRIMARY KEY CLUSTERED (spno ASC)GO-创建teacher_course表USE SCGOCREATE TABLE teacher_course( tcid smallint NOT NULL, t_no char(8) NULL, spno char(8) NULL, class_no char(4)

13、 NULL, cno char(10) NOT NULL, semester char(6) NULL, schoolyear char(10) NULL, CONSTRAINT PK_teacher_course PRIMARY KEY CLUSTERED (tcid ASC)GO-创建class表USE SCGOCREATE TABLE class( spno char(8) NOT NULL, class_no char(4) NOT NULL, header char(8) NULL, CONSTRAINT PK_class PRIMARY KEY CLUSTERED (spno AS

14、C,class_no ASC) )GO-添加外键USE SCGO-为student表添加外键ALTER TABLE student ADD CONSTRAINT FK_student_department FOREIGN KEY(dno) REFERENCES department (dno)GOALTER TABLE student ADD CONSTRAINT FK_student_speciality FOREIGN KEY(spno) REFERENCES speciality (spno)GO-为Course表添加外键ALTER TABLE Course ADD CONSTRAINT

15、 FK_Course_speciality FOREIGN KEY(spno) REFERENCES speciality (spno)GO-为student_course表添加外键ALTER TABLE student_course ADD CONSTRAINT FK_student_course_student FOREIGN KEY(s_no) REFERENCES student (s_no)GO-为teacher表添加外键ALTER TABLE teacher ADD CONSTRAINT FK_teacher_department FOREIGN KEY(dno) REFERENC

16、ES department (dno)GO-为speciality表添加外键ALTER TABLE speciality ADD CONSTRAINT FK_speciality_department FOREIGN KEY(dno) REFERENCES department (dno)GO-为teacher_course表添加外键ALTER TABLE teacher_course ADD CONSTRAINT FK_teacher_course_teacher FOREIGN KEY(t_no) REFERENCES teacher (t_no)GOALTER TABLE teacher

17、_course ADD CONSTRAINT FK_teacher_course_speciality FOREIGN KEY(spno) REFERENCES speciality (spno)GOALTER TABLE teacher_course ADD CONSTRAINT FK_teacher_course_Course FOREIGN KEY(cno) REFERENCES Course (cno)GO-为class表添加外键ALTER TABLE class ADD CONSTRAINT FK_class_speciality FOREIGN KEY(spno) REFERENC

18、ES speciality (spno)GO查询分析器执行情况：SQL语句及执行结果截图显示创建student表创建Course表创建student_course表-创建teacher表-创建department表-创建speciality表-创建teacher_course表-创建class表-添加外键-为student表添加外键-为Course表添加外键-为student_course表添加外键-为teacher表添加外键-为speciality表添加外键-为teacher_course表添加外键-为class表添加外键5、利用查询分析器修改上述各表。(1)、用INSERT语句向各个表中插

19、入数据录入5 条记录。录入时注意体会外键约束。实现代码及截图：查询分析器执行情况：SQL语句及执行结果显示-向department表插入记录use SCgoinsert into department values(510000,计算机,吴伟民);insert into department values(510002,轻工化工,余林);insert into department values(510003,信息工程,章国豪);insert into department values(510004,材料与能源,张海燕);insert into department values(51000

20、5,物理光电,胡义华);-为teacher表插入数据use SCgoinsert into teacher values(02002731,吴伟民,男,1956-02-05,510000,教授);insert into teacher values(02002735,谢光强,男,1979-11-12,510000,讲师);insert into teacher values(02003187,郭大昌,男,1946-08-12,510005,教授);insert into teacher values(02004312,刘怡俊,男,1976-10-23,510000,讲师);insert int

21、o teacher values(02005576,丁国方,男,1974-02-10,510000,讲师); -为speciality表插入数据use SC;insert into speciality values(08005501,510000,计算机科学与技术);insert into speciality values(08005502,510002,化学工业);insert into speciality values(08005503,510003,通讯技术);insert into speciality values(08005504,510004,制冷工程);insert in

22、to speciality values(08005505,5iality;-为student表插入数据use SC;insert into student values(31101032,李四,女,1990-03-08,510003,08005503,1001);insert into student values(31102234,张学究,男,1991-03-02,510005,08005505,1005);insert into student values(31103745,张三,女,1990-05-04,510002,08005502,1002);insert into studen

23、t values(31104862,彭志伟,男,1990-01-12,510000,08005501,1001);insert into student values(31105808,黄大志,男,1990-11-12,510000,08005501,1002);-为class表插入数据use SC;insert into class values(08005501,1001,彭志伟);insert into class values(08005501,1002,黄大志);insert into class values(08005502,1002,张三);insert into class

24、values(08005503,1001,李四);insert into class values(08005504,1003,王五);-为student_course表插入数据use SC;insert into student_course values(31101032,1,78);insert into student_course values(31101032,103,83);insert into student_course values(31102234,1,81);insert into student_course values(31102234,105,46);inse

25、rt into student_course values(31103745,1,54);-为Course表插入数据use SC;insert into Course values(1010105500,高等数学,08005505,1,160,0,1,5);insert into Course values(1010105501,数据结构,08005501,1,80,20,4,5);insert into Course values(1010105502,操作系统,08005501,1,80,16,5,4);insert into Course values(1010105504,计算机网络,

26、08005501,1,70,18,6,4);insert into Course values(1010105505,C程序设计,08005501,1,120,40,1,4);-为teacher_course表插入数据use SC;insert into teacher_course values(1,02002735,08005501,1002,1010105505,1,1);insert into teacher_course values(102,02002731,08005501,10021010105501,4,2);insert into teacher_course values

27、(103,02003187,08005505,1001,1010105500,1,1);insert into teacher_course values(104,02005576,08005501,1002,1010105502,5,3);insert into teacher_course values(105,02004312,08005501,1002,1010105504,6,4);实验结果截图显示（插入数据的表格）-department表-teacher表-speciality表-student表-为class表插入数据student_course表-为Course表插入数据-为teacher_course表插入数据共8组（共8个表）(2)、用UPDATE语句更改student表中数据；实现代码：-将student表中学号均改为由原值加上对应的出生年use SC;update student set s_no=s_no+YEAR(sbirthday);student表更改前