数据库作业(1)

兰州交通大学数据库原理及应用作业

作业要求：1.所有作业打印在A4稿纸上

2.在封面写清楚班级、姓名、学号

3.抄写题目（表可以不写），命令与题目对应。

概念基础部分:

⒈简述数据、数据库、数据库管理系统、数据库应用系统的概念。

答：数据是描述事物的符号记录，是信息的载体，是信息的具体表现形式。

数据库就是存放数据的仓库，是将数据按一定的数据模型组织、描述和存储，能够自动进行查询和修改的数据集合。

数据库管理系统是数据库系统的核心，是为数据库的建立、使用和维护而配置的软件。它建立在操作系统的基础上，位于用户与操作系统之间的一层数据管理软件，它为用户或应用程序提供访问数据库的方法，包括数据库的创建、查询、更新及各种数据控制等。

凡使用数据库技术管理其数据的系统都称为数据库应用系统。

⒊什么是实体、属性、码、联系？

答：客观存在并可以相互区分的事物叫实体。

属性是实体所具有的某些特性，通过属性对实体进行描述。

一个实体往往有多个属性，这些属性之间是有关系的，它们构成该实体的属性集合。如果其中有一个属性或属性集能够唯一标识整个属性集合，则称该属性或属性集为该实体的码。

现实世界的事物之间是有联系的，即各实体型之间是有联系的。就两个实体型的联系来说，主要有以下3种情况：一对一联系(1:1)、一对多联系(1 : M)和多对多联系(M : N)。

⒌设某工厂数据库中有四个实体集。一是“仓库”实体集，属性有仓库号、仓库面积等；二是“零件”实体集，属性有零件号、零件名、规格、单价等；三是“供应商”实体集，属性有供应商号、供应商名、地址等；四是“保管员”实体集，属性有职工号、姓名等。(1)试为该工厂的数据库设计一个ER模型，要求标注联系类型，可省略实体属性。

1 N

(2)根据转换规则，将ER模型转换成关系模型，要求标明每个关系模式的主键和外键。

答：（1）ER模型

（2）关系模式：

仓库（仓库号，仓库面积）

零件（零件号，零件名，规格，单价）

供应商（供应商号，供应商名，地址）

保管员（职工号，姓名，仓库号）

库存（仓库号，零件号，库存量）

供应（供应商号，零件号，供应量）

7.简述关系的完整性。

答：关系模型允许定义三类完整性约束：实体完整性、参照完整性和用户自定义的完整性约束。

实体完整性规则要求关系中元组在组成主码的属性上不能有空值。

参照完整性规则：若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应（基本关系R和S可能是相同的关系），则对于R中每个元组在F上的值必须为：或者取空值（F的每个属性值均为空值）；或者等于S中某个元组的主码值。

用户定义的完整性规则由用户根据实际情况对数据库中数据的内容进行的规定，也称为域完整性规则。

9.解释下列术语的含义：函数依赖、平凡函数依赖、非平凡函数依赖、部分函数依赖、完全函数依赖、传递函数依赖、范式。

答：函数依赖：指在关系R中，X、Y为R的两个属性或属性组，如果对于R的所有关系r都存在：对于X的每一个具体值，Y都只有一个具体值与之对应,则称属性Y函数依

X Y。当X→Y且Y→X时，

赖于属性X。记作X→Y。当Y不函数依赖于X时，记作：

则记作：X ? Y。

平凡函数依赖：设关系模式R(U)，U是R上的属性集，X、Y?U；如果X→Y，且Y?X，则称X→Y为平凡的函数依赖。

非平凡函数依赖、如果X→Y，且Y不是X的子集，则称X→Y为非平凡的函数依赖。

完全函数依赖：设关系模式R(U)，U是R上的属性集，X、Y?U；如果X→Y，并且对于X的任何一个真子集Z，Z→Y都不成立，则称Y完全函数依赖于X；

部分函数依赖：如果X→Y，但对于X的某一个真子集Z，有Z→Y成立，则称Y部分函数依赖于X。

传递函数依赖：设关系模式R(U)，X?U，Y?U，Z?U；如果X→Y，Y→Z成立，但Y→X不成立，且Z-X、Z-Y和Y-X均不空，则称X→Z为传递函数依赖。

范式：范式（Normal Form）是符合某一种级别的关系模式的集合，是衡量关系模式规范化程度的标准，达到的关系才是规范化的。

11.将图1学生信息数据库系统的E-R图转换为关系模型。

答：课程（课程号，课程名，学分）

学生（学号，姓名，性别，年龄，班级名）

班级（班级名，班主任）

选修（学号，课程号，成绩）

T-SQL语句部分：

13．在D盘DATABASE文件夹下创建”学生管理”数据库，主数据文件的逻辑文件名为“学生管理_Data”,实际文件名为“学生管理_Data.mdf”，事务日志文件的逻辑文件名为“学生管理_Log”，实际文件名为“学生管理_Log.ldf”。上述文件的初始容量均为5MB，最大容量均为50MB，递增两均为2MB。

答：

create database 学生管理

on primary

( name=学生管理_Data,

filename='d:\学生管理_Data.mdf',

size=5MB,

maxsize=50MB,

filegrowth=2MB

)

log on

( name=学生管理_Log,

filename='d:\学生管理_Log.ldf',

size=2MB,

maxsize=50MB,

filegrowth=2MB

)

15. 根据上面表中数据，插入每一张表的前两条记录。

insert into 成绩 values('0207005','003',98,88)

insert into 成绩(学号，课程号，期末成绩) values('0207005','002',88)

17. 在“学生”表中，查询20%的所有记录。

Select top 20 percent 学号,姓名,班级,住址 from 学生

19. 在“学生”表中查询学号和班级连接显示的记录并将合成的列名为“学号班级”。

select 学号+班级 '学号班级' from 学生信息

21. 在“成绩”表中查询期末成绩在70--85的所有记录。

select * from 学生信息 where 期末成绩 between 70 and 85

23. 在“学生”表中，查询姓刘同学的所有记录。

Select 学号,姓名,班级 from 学生 where 姓名 like …刘%?

25. 在“学生”表中，查询叫刘某同学的所有记录。

select * from学生信息where 姓名 like '刘_'

27. 在“学生”表中，查询学号以2006开头，是以2结尾的，长度为8位的所有记录。

select*from学生信息where邮政编码like'[^2006]2'

29. 在“成绩”表中，查询期末成绩在前3名同学的学号，期中成绩和期末成绩的记录。

select top 3 学号,期中成绩,期末成绩 from 学生信息 order by 期末成绩 desc

31. 通过“学生”、“课程”、“成绩”表，查询学号是20060002的学生的姓名、所选课程名和期末成绩的记录。

select name,课程名称,期末成绩 from student,成绩,课程 where student.xh=成绩.学号 and 课程.课程号=成绩.课程号 and xh='20060002'

33．更新“成绩”表：期中成绩和期末成绩均大于80平时成绩为20。

update 成绩 set 平时成绩=20 where 期中成绩>80 and 期末成绩>80

35．利用基表“学生”创建名为“视图1”的视图，要求列名为：学号、姓名、性别、班级和住址。

create view 视图1 with encryption as select 学号,姓名,性别,出生日期 from 学生

37．在“成绩”表中查询期中成绩在前3名的同学的所有记录。

Select top 3 * from 成绩 order by 期中成绩 desc

39. 统计“学生”表中每个班级的人数。

select bj,count(*) from student group by bj

41.统计有学生选修的课程门数。

select count(distinct 课程号) from 选课

43. 求学分为3的每门课程的学生平均成绩。

select 课程.课程号,AVG(成绩) from 课程,选课

where 课程.课程号=选课.课程号and 学分=3

group by课程.课程号

45.检索学号比王非同学大，而年龄比他小的学生姓名。

select姓名 from 学生

where 学生号>(select学生号from学生where 姓名=?王非?)and 年龄<(select年龄from学生where 姓名=?王非?)

47．求年龄大于女同学平均年龄的男学生姓名和年龄。

select 姓名,年龄 from 学生

where 性别=?男? and 年龄 >(select avg(年龄) from 学生where 性别=?女?) 49.检索所有比王华年龄大的学生姓名、年龄和性别。

select 姓名,年龄,性别 from 学生where 年龄 > (select 年龄 from 学生where 姓名=?王华?)

51.检索学生姓名及其所选修课程的课程号和成绩。

select 姓名, 课程号, 成绩 from 学生,选课

where学生.学生号=选课.学生号

53．利用t-sql语句创建一个查询每个学生的平均成绩的视图，要求包含学生的学生号和姓名。

答：create view 学生_平均成绩

as

select 学生.学生号,姓名,avg(成绩) as平均成绩

from 学生,选课

where 学生.学生号=选课.学生号

group by学生.学生号,姓名

55．聚集索引和非聚集索引有何异同?

答：聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。非聚集索引不会对表和视图进行物理排序。如果表中不存在聚集索引，则表是未排序的。

57．为“学生”表创建一个实现插入、更新的触发器，当执行插入操作时，激活该触发器同时显示表中记录。

create trigger d_tr on dept

for insert

as insert from xuesheng where leader=(select leader from insert) 59. 写出事务的基本操作命令。

（1）定义一个事务的开始begin transaction

(2)提交一个事务commit transaction

(3)回退事务rollback transaction

(4)在事务内设置保存点save transaction