数据库试验,崔巍.doc

上机实验报告 课程：数据库原理 实验1名称：数据库设计1、 实验内容： 以所在学校选课和课程管理为实际应用背景，设计一个教学管理数据库。假设至少包含如下需求：学生信息管理课程信息管理教师信息管理学生选修课程及成绩信息管理教师负责课程和讲授课程信息管理2、 实验目的： 通过实践，掌握本章介绍的数据库设计方法。学会使用PowerDesigner来完成数据库设计过程。3. 、实验方法（或程序源代码）：（1） 根据实验内容明确要完成的系统功能。（2） 运行PowerDesigner创建概念数据模型，建立实体、属性和联系。对关键字、空值、域完整性等做出必要的描述，根据实际情况确定联系的类型。（3） 将检查无误的概念数据模型转换成逻辑数据模型，并对生成的逻辑数据模型作必要的修改。（4） 选择一个实际的DBMS软件（如SQL Server），根据逻辑数据模型生成物理数据模型，并对生成的物理数据模型作必要的修改。（1） 教学管理E-R图（2）（转换了多对多联系）（3）逻辑数据模型（4）物理数据模型4、 实验数据、结果分析、总结问题： 经过对这个实验的制作，信息比较多，做表格有点复杂，涉及的框架知识多，对关键字、空值、域完整性做的描述基本熟练。对概念数据模型、物理数据模型、逻辑数据模型的基本掌握。开始对数据库的设计有了一定的了解。 实验日期：2013 年10月 16 日实验2名称：建立数据库、架构、表和定义完整性约束。1、 实验内容： 参见2.1.7建立数据库的CREATE DATABASE命令首先建立数据库，然后参照图5-1和5-8建立表并定义完整性约束，可以在此基础上根据自己学校的实际情况增加表、字段和约束等。2、 实验目的： 熟练掌握表的建立和数据库完整性约束的定义方法，实践DBMS提供的数据完整性功能，加深对数据完整性的理解。三.、实验方法（或程序源代码）：使用CREATE DATABASE命令建立数据库，使用CREATE SCHEMA命令建立架构，用CREATE TABLE命令建立表并定义数据完整性约束，用ALTER TABLE命令修改表架构。程序源代码： CREATE DATABASE 美美ON ( NAME = 美美,FILENAME = E:美美.mdf,SIZE = 10,MAXSIZE = 50,FILEGROWTH = 5 )LOG ON( NAME = order_log,FILENAME = E:美美.ldf,SIZE = 5MB,MAXSIZE = 25MB,FILEGROWTH = 5MB )use 美美 go create schema acreate schema bcreate schema cCREATE TABLE 院系(编号 CHAR(6) PRIMARY KEY,名称 CHAR(20)UNIQUE NOT NULL,分责人 CHAR(10),办公地点 CHAR(20)CREATE TABLE 学生(学号 CHAR(8) PRIMARY KEY,院系 CHAR(6) FOREIGN KEY REFERENCES 院系(编号),姓名 CHAR(10) NOT NULL,性别 CHAR(2) check (性别 in(男,女),生源 CHAR(6),状态 CHAR(4) check (状态 in(正常,留级,休学,退学)create table 教师(教师编号 CHAR(8) PRIMARY KEY,院系 CHAR(6) FOREIGN KEY REFERENCES 院系(编号),姓名 CHAR(10) NOT NULL,性别 CHAR(2) check (性别 in(男,女),职称 CHAR(6) CHECK (职称 IN(教授,副教授,讲师,助教),专业 CHAR(10)create table 课程(课程编号 CHAR(8) PRIMARY KEY,课程名称 CHAR(20) NOT NULL,责任教师 CHAR(8) FOREIGN KEY REFERENCES 教师(教师编号),学时 SMALLINT NOT NULL,课程性质 CHAR(10) CHECK (课程性质 IN(公共基础,专业基础,专业选修,任意选修)create table 选课(学号 CHAR(8) FOREIGN KEY REFERENCES 学生(学号),课程编号 CHAR(8) FOREIGN KEY REFERENCES 课程(课程编号),成绩 SMALLINT CHECK (成绩=0 AND 成绩=60 AND 成绩=60 OR 成绩=70 OR 成绩=0 OR 成绩=70 AND 成绩=70 AND 成绩=90 )-(27)SELECT * FROM 学生 WHERE 学号 NOT IN (SELECT 学号 FROM 选课 WHERE 成绩=80 )SELECT * FROM 选课 WHERE 学号 NOT IN (SELECT 学号 FROM 选课 WHERE 成绩= any(SELECT 成绩 FROM 选课 WHERE 学号= 20110923 )SELECT DISTINCT 姓名 FROM 学生 WHERE 平均成绩= (SELECT MIN(成绩) FROM 选课 WHERE 学号= 20110923 )-SOMESELECT DISTINCT 姓名 FROM 学生 WHERE 平均成绩= SOME(SELECT 成绩 FROM 选课 WHERE 学号= 20110927 )-(30)SELECT DISTINCT 姓名 FROM 学生 WHERE 平均成绩= ALL(SELECT 成绩 FROM 选课 WHERE 学号= 20110923 )SELECT DISTINCT 姓名 FROM 学生 WHERE 平均成绩= (SELECT MAX(成绩) FROM 选课 WHERE 学号= 20110923 )-(31)-?SELECT 姓名,职称,课程名称,课程学时 FROM 教师 WHERE 职称 IN(SELECT 名称,职称,负责课程 FROM 院系 WHERE )-(32)SELECT Da.学号, Da.姓名,Da.性别,Da.生源,Da.状态,Da.平均成绩 FROM 学生 Da WHERE Da.平均成绩 = (SELECT Max(Sa.平均成绩) FROM 学生 Sa WHERE Sa.学号 = Da.学号)-(33)SELECT *FROM 教师 WHERE EXISTS (SELECT *FROM 课程 WHERE 教师编号=KC.责任教师)SELECT *FROM 教师 WHERE 教师编号 IN (SELECT 责任教师 FROM 课程)-(34)SELECT *FROM 教师 WHERE NOT EXISTS (SELECT *FROM 课程 WHERE JS.教师编号=KC.责任教师)SELECT *FROM 教师 WHERE 教师编号 NOT IN (SELECT 责任教师 FROM 课程)-(35)SELECT COUNT(DISTINCT 生源)FROM 学生SELECT COUNT(生源)FROM 学生-(36)SELECT SUM(工资)FROM 教师-(37)SELECT SUM(工资),AVG(工资),COUNT(教师编号),MAX(工资),MIN(工资)FROM 教师-(38)-?SELECT 姓名,AVG(平均成绩)FROM 学生 WHERE 平均成绩60 GROUP BY 姓名HAVING COUNT(*)=60-(40)-?SELECT 姓名,AVG(平均成绩)FROM 学生 GROUP BY 姓名-(41)-?SELECT 学号,AVG(成绩),MAX(成绩),MIN(成绩),COUNT(*)FROM 选课 GROUP BY 学号-(42)-?-(43)SELECT *FROM 教师 ORDER BY 教师编号COMPUTE AVG(工资),SUM(工资) BY 教师编号COMPUTE AVG(工资),SUM(工资)SELECT *FROM 学生 ORDER BY 姓名COMPUTE AVG(平均成绩),SUM(平均成绩) BY 姓名COMPUTE AVG(平均成绩),SUM(平均成绩)-(44)SELECT *FROM 教师 ORDER BY 教师编号COMPUTE COUNT(姓名),AVG(工资),SUM(工资) -(45)SELECT *FROM 教师 ORDER BY 教师编号COMPUTE COUNT(姓名),AVG(工资),SUM(工资) BY 教师编号4、 实验数据、结果分析、总结问题： 通过实验，学会使用SQL SELECT的数据查询操作 ，对左连接、右连接、内连接全连接的掌握，对简单嵌套查询、条件查询等等各种查询的练习的基本熟悉，同时也对SQL SELECT查询命令的基本框架的熟悉与理解。让自己的能力有了更好的提升。 实验日期：2013年11月7日实验5名称：视图的建议和应用。1、 实验内容： 在实验2和实验3的基础上定义视图，并在视图上完成查询以及插入、更新和删除操作。二、实验目的： 掌握视图定义和应用的方法，体会视图和基本表的异同之处。三.、实验方法（或程序源代码）：在实验2和实验3的基础用CREATE VIEW命令定义视图，然后使用SELECT命令完成查询，使用INSERT UPDATE和DELETE命令分别完成插入、更新和删除操作。程序源代码：-(1)建立查询教师编号,工资,性别的教师视图表CREATE VIEW 教师视图 ASSELECT 教师编号,工资,性别 FROM 教师-(2)建立查询工资的教师视图表CREATE VIEW 教师视图2 ASSELECT 工资 FROM 教师 where 工资 =1000-(3)建立查询工资大于1000的教师编号,性别 ,工资的教师视图表CREATE VIEW 教师视图3 ASSELECT 教师编号,性别 ,工资 FROM 教师 where 工资 =1000-(4)建立教师与课程视图CREATE VIEW 教师与课程视图 ASSELECT 教师编号,工资,性别,课程名称,课程编号,责任教师 FROM 教师 JOIN 课程 ON 教师编号 = 责任教师-(5)建立学生与选课视图CREATE VIEW 学生与选课视图 ASSELECT 姓名,院系,生源 FROM 学生 WHERE 学号 IN (SELECT 学号 FROM 选课 WHERE 成绩=80 )-(6)建立教师视图4(教师编号,工资,性别,年工资) CREATE VIEW 教师视图4(教师编号,工资,性别,年工资 )ASSELECT 教师编号,工资,性别,工资*12 FROM 教师-2-(1)查询教师视图的COUNT(*),AVG(工资)SELECT COUNT(*),AVG(工资) FROM 教师视图-(2)查询教师视图2的工资SELECT 工资FROM JG.教师视图2 ORDER BY 工资 DESC-(3)查询教师视图3的工资大于9000的并按工资升序排序SELECT 工资FROM JG.教师视图3 WHERE 工资9000 ORDER BY 工资 DESC-(4)查询学生与选课视图学生平均成绩等于89的学生姓名SELECT 姓名 FROM 学生与选课视图 WHERE 院系=(SELECT 院系 FROM 学生 WHERE 平均成绩=89)-(5)查询学生与选课视图学生平均成绩等于89的学生姓名SELECT 姓名 FROM 学生与选课视图 WHERE 院系 in (SELECT 院系 FROM 学生 WHERE 平均成绩=89)-(6)查询学生的生源，姓名SELECT 学生与选课视图.生源,学生与选课视图.姓名 FROM 学生与选课视图 JOIN 学生 ON学生与选课视图.院系 = XS.院系-3-(1)在教师视图3插入操作INSERT INTO 教师视图3 VALUES(20111115,030,梁海秋,女,讲师,英语,5300)-(2)在教师视图进行更新操作UPDATE 教师视图.教师编号,教师视图.工资,教师视图.性别SET 教师编号,工资,性别,职称 4、 实验数据、结果分析、总结问题： 在本实验上，基于对单个表按投影、选择、选择和投影、根据链接、根据嵌套查询操作定义视图的基本熟悉,明白了SQL SELECT查询是CREATE VIEW,而对于基本的命令格式也已经掌握，使用起来还是出现了一些命令未完成。 实验日期：2013年11月 14日实验7名称：存储过程的设计和使用。1、 实验内容： 在SQL Server环境下设计、创建并执行存储过程。2、 实验目的： 理解和掌握数据库存储过程的创建和调用方法。三.、实验方法（或程序源代码）：在SQL Server环境下按要求创建和调用存储过程完成指定的操作。程序源代码：-(1)查询分数在X到Y之间的学生信息CREATE PROCEDURE ABCX INT ,Y INT ASSELECT 学号,姓名,院系,平均成绩 FROM 学生 WHERE 平均成绩 =X AND 平均成绩 =YExecute ABC X=50 , Y=100-更新以学号，课程编号和考试成绩做参数，-的指定的绩学生和课程的考试成并返回该学生的平均成绩CREATE PROCEDURE ABD学号 INT ,课程编号 INT,成绩 INT AS DECLARE 平均成绩 intUPDATE 学生 set 平均成绩 =成绩 where 学号 IN (select 学号 from 选课 WHERE 学号=学号 AND 课程编号=课程编号 )return 成绩 DECLARE 平均成绩 INTExecute ABD 成绩=88 ,学号=20110902 ,课程编号=202-(2)更新以学号,院系为参数的指定的学生的存储过程操作CREATE PROCEDURE ABE学号 INT , 院系 INT ASUPDATE 学生 set 院系 =院系 WHERE 学号 =学号 Execute ABE 院系=025, 学号=20110903 -查询以学号,院系为参数的指定的学生的姓名的存储过程CREATE PROCEDURE ABF学号 INT , 院系 INT ASselect 姓名 from 学生 WHERE 学号 =学号 Execute ABE 院系=022, 学号=20110902 -(3)存储过程的查询与select查询功能的比较CREATE PROCEDURE ABH学号 INT , 院系 INT ASselect 姓名 from 学生 WHERE 学号 =学号 Execute ABE 院系=024, 学号=20110923-select查询select 姓名 from 学生 WHERE 学号=20110923AND 院系=0244、 实验数据、结果分析、总结问题： 通过本实验学习，在SQL Server环境下设计，CREATE PROCEDURE创建存储过程的命令及基本格式的熟悉，EXECUTE实质性存储过程的语句。理解调用和创建数据库存储过程，让能力上得到了提升。 实验日期：2013年11月