数据库完整版1.docx
《数据库完整版1.docx》由会员分享，可在线阅读，更多相关《数据库完整版1.docx（18页珍藏版）》请在冰豆网上搜索。
数据库完整版1
作业要求：
1.所有作业书写在稿纸上
2.在封面写清楚班级、姓名、学号
3.抄写题目（表可以不写），命令与题目对应。
概念基础部分:
⒈简述数据、数据库、数据库管理系统、数据库应用系统的概念。
答：
数据是描述事物的符号记录，是信息的载体，是信息的具体表现形式。
数 组织、描述和存储，能够自动进行查询和修改的数据集合。
数据库管理系统是数据库系统的核心，是为数据库的建立、使用和维护而配置的软件。
它建立在操作系统的基础上，位于用户与操作系统之间的一层数据管理软件，它为用户或应用程序提供访问数据库的方法，包括数据库的创建、查询、更新及各种数据控制等。
凡使用数据库技术管理其数据的系统都称为数据库应用系统。
⒉简述数据库管理系统的功能。
答：
数据库管理系统是数据库系统的核心软件，一般说来，其功能主要包括以下5个方面。
（1） 数据定义和操纵功能
（2） 数据库运行控制功能
（3） 数据库的组织、存储和管理
（4） 建立和维护数据库
（5） 数据通信接口
⒊什么是实体、属性、码、联系？
：
客观存在并可以相互区分的事物叫实体。
属性是实体所具有的某些特性，通过属性对实体进行描述。
一个实体往往有多个属性，这些属性之间是有关系的，它们构成该实体的属性集合。
如果其中有一个属性或属性集能够唯一标识整个属性集合，则称该属性或属性集为该实体的码。
现实世界的事物之间是有联系的，即各实体型之间是有联系的。
就两个实体型的联系来说，主要有以下3种情况：
一对一联系（1:
1）、一对多联系（1 :
M）和多对多联系（M :
N）。
⒋解释关系模型的基本概念：
关系、元组、属性、域、关系模式、候选关键字、主键、外键、主属性。
关系：
一个关系就是一张二维表。
元组：
二维表中的行称为元组，每一行是一个元组。
属性：
二维表的列称为属性，每一列有一个属性名，属性值是属性的具体值。
域：
是属性的取值范围。
关系模式：
对关系的信息结构及语义限制的描述称为关系模式，用关系名和包含的属性名的集合表示。
候选关键字：
如果在一个关系中，存在多个属性（或属性组合）都能用来唯一标识该关系中的元组，这些属性（或属性组合）都称为该关系的候选关键字或候选码，候选码可以有多个。
主键：
在一个关系的若干候选关键字中，被指定作为关键字的候选关键字称为该关的主键或主码。
主属性：
在一个关系中，包含在任何候选关键字中的各个属性称为主属性。
外键：
一个关系的某个属性（或属性组合）不是该关系的主键或只是主键的一部分，却是另一个关系的主码，则称这样的属性为该关系的外键或外码。
⒌设某工厂数据库中有四个实体集。
一是“仓库”实体集，属性有仓库号、仓库面积等；二是“零件”实体集，属性有零件号、零件名、规格、单价等；三是“供应商”实体集，属性有供应商号、供应商名、地址等；四是“保管员”实体集，属性有职工号、姓名等。
（1）试为该工厂的数据库设计一个ER模型，要求标注联系类型，可省略实体属性。
ER模型
（2）根据转换规则，将ER模型转换成关系模型，要求标明每个关系模式的主键和外键。
（2）关系模式：
仓库（仓库号，仓库面积）
零件（零件号，零件名，规格，单价）
供应商（供应商号，供应商名，地址）
保管员（职工号，姓名，仓库号）
库存（仓库号，零件号，库存量）
供应（供应商号，零件号，供应量）
6.某网上订书系统，涉及如下信息：
（1）客户：
客户号、姓名、地址、联系电话。
（2）图书：
书号、书名、出版社、单价。
（3）订单：
订单号、日期、付款方式、总金额。
其中：
一份订单可订购多种图书，每种图书可订购多本；一位客户可有多份订单，一份订单仅对应一位客户。
（1）根据以上叙述，建立ER模型，要求标注联系类型（可省略实体的属性）。
（2）根据转换规则，将ER模型转换成关系模型，要求标注每个关系模型的主键和外键（如果存在）。
（2）关系模式：
客户（客户号，姓名，地址，联系电话，订单号）
图书（书号，书名，出版社，单价）
订单（订单号，日期，付款方式，总金额）
订购（订单号，书号，数量）
7.简述关系的完整性。
答：
关系模型允许定义三类完整性约束：
实体完整性、参照完整性和用户自定义的完整性约束。
实体完整性规则要求关系中元组在组成主码的属性上不能有空值。
参照完整性规则：
若属性（或属性组）F是基本关系R的外码，它与基本关系S的主码Ks相对应（基本关系R和S可能是相同的关系），则对于R中每个元组在F上的值必须为：
或者取空值（F的每个属性值均为空值）；或者等于S中某个元组的主码值。
用户定义的完整性规则由用户根据实际情况对数据库中数据的内容进行的规定，也称为域完整性规则。
8.根据给定的关系模式进行查询。
设有学生-课程关系数据库，它由三个关系组成，它们的模式是：
学生S（学号S#,姓名SN,所在系SD,年龄SA）、课程C（课程号C#,课程名CN,先修课号PC#）、SC（学号S#,课程号C#,成绩G）。
请用关系代数分别写出下列查询：
（1）检索学生的所有情况。
∏S#, SN, SD, SA（S）
（2）检索学生年龄大于等于20岁的学生姓名。
∏SN（σSA≥20 （S））
（3）检索先修课号为C2的课程号。
∏C#（σPC#=’C2’ （C））
（4）检索课程号C1的成绩为A的所有学生姓名。
∏SN（σC#=’C1’ ∧ G=’A’ （S∞SC））
（5）检索学号为S1的学生选修的所有课程名及先修课号。
∏CN, PC#（σS#=’S1’ （C∞SC））
（6）检索年龄为23岁的学生所选修的课程名。
∏CN（σSA=23 （C∞SC∞S））
9.解释下列术语的含义：
函数依赖、平凡函数依赖、非平凡函数依赖、部分函数依赖、完全函数依赖、传递函数依赖、范式.
函数依赖：
指在关系R中，X、 Y为R的两个属性或属性组，如果对于R的所有关系r都存在：
对于X的每一个具体值，Y都只有一个具体值与之对应,则称属性Y函数依赖于属性X。
记作X→Y。
当Y不函数依赖于X时，记作：
X Y。
当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）是符合某一种级别的关系模式的集合，是衡量关系模式规范化程度的标准，达到的关系才是规范化的
10.简述数据库设计过程的各个阶段上的设计任务。
答：
需求分析的任务是通过详细调查现实世界要处理的对象（组织、 部门、 企业等）， 充分了解原系统（手工系统或计算机系统）工作概况，明确用户的各种需求，然后在此基础上确定新系统的功能。
概念设计阶段要做的工作不是直接将需求分析得到的数据存储格式转换为DBMS能处理的数据库模式，而是将需求分析得到的用户需求抽象为反映用户观点的概念模型。
逻辑设计的主要目标是产生一个DBMS可处理的数据模型和数据库模式。
该模型必须满足数据库的存取、一致性及运行等各方面的用户需求。
将逻辑设计中产生的数据库逻辑模型结合指定的DBMS，设计出最适合应用环境的物理结构的过程，称为数据库的物理结构设计。
根据逻辑和物理设计的结果，在计算机上建立起实际的数据库结构，并装入数据，进行试运行和评价的过程，叫做数据库的实施（或实现）。
维护工作包括以下内容：
数据库的转储和恢复；数据库的安全性和完整性控制；数据库性能的监督、分析和改造；数据库的重组织和重构造。
11.将图1学生信息数据库系统的E-R图转换为关系模型。
图1
习题11图
课程（课程号，课程名，学分）
学生（学号，姓名，性别，年龄，班级名）
班级（班级名，班主任）
选修（学号，课程号，成绩）
12．一个图书馆理系统中有如下信息：
图书：
书号、书名、数量、位置
借书人：
借书证号、姓名、单位
出版社：
出版社名、邮编、地址、电话、E-mail
其中约定：
任何人都可以借多种书，任何一种书可以被多个人借，借书和还书时，要登记相应的借书日期和还书日期；一个出版社可以出版多种书籍，同一本书仅为一个出版社所出版，出版社名具有唯一性。
根据以上情况，完成如下设计：
（1）设计系统的E-R图;
（2）将E-R图转换为关系模式；
（3）指出转换后的每个关系模式的主码。
图书（书号，书名，数量，位置，出版社名）
借书人（借书证号，姓名，单位）
出版社（出版社名，邮编，地址，电话，E-mail）
借阅（书号，借书证号，借书日期，还书日期）
n
T-SQL语句部分：
13．在D盘DATABASE文件夹下创建”学生管理”数据库，主数据文件的逻辑文件名为“学生管理_Data”,实际文件名为“学生管理_Data.mdf”，事务日志文件的逻辑文件名为“学生管理_Log”，实际文件名为“学生管理_Log.ldf”。
上述文件的初始容量均为5MB，最大容量均为50MB，递增两均为2MB。
使用T-SQL语句再次创建该数据库，主文件和日志文件的文件名同上，要求：
仓库库存_data最大尺寸为无限大，增长速度为20%，日志文件初始大小为2MB，最大尺寸为5MB，增长速度为1MB。
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
）
14．在“学生管理”数据库中建立“学生”、“课程”和“成绩”数据表，其中：
学生（学号CHAR（8），姓名CHAR（8），性别CHAR
（2），出生日期DATATIME，邮政编码CHAR（6），住址VARCHAR（50），简历TEXT），其中学号为主键。
课程（课程号CHAR（4），课程名称VARCHAR（50），学分INT），其中课程号为主键。
成绩（学号CHAR（8），课程号CHAR（4），期中成绩INT，期末成绩INT），其中学号与课程号的组合为主键。
20060002
0002
65
90
20060001
0003
85
88
20060004
0004
56
78
20060005
0002
67
68
表中数据如下：
“学生”表
学号
姓名
性别
出生日期
班级
住址
简历
20060001
黎明
男
1980-10-1
土木工程
东阳市八一路
2006年入学
20060002
张扬
男
1978-12-1
市场营销
长岭市五一路
2006年入学
20060003
文海
男
1980-12-3
工商管理
东方市中山路
2006年入学
20060004
肖晓
女
1979-3-4
软件工程
兰州市安宁路
2006年入学
20060005
刘烨
女
1978-5-4
交通运输
衡山市东风路
2006年入学
“课程”表
课程号
课程名称
学分
0001
大学计算机基础
3
0002
C语言程序设计
3
0003
高等数学
4
0004
数据结构
4
“成绩”表
学号
课程号
期中成绩
期末成绩
平时成绩
总成绩
20060001
0001
78
89
20060002
0002
65
90
20060001
0003
85
88
20060004
0004
56
78
20060005
0002
67
68
15. 根据上面表中数据，插入每一张表的前两条记录。
insert into 成绩 values（'0207005','003',98,88）
insert into 成绩（学号，课程号，期末成绩） values（'0207005','002',88）
．修改“成绩”表，添加“平时成绩”列，数据类型为int，默认值为0；再添加“总成绩”列，数据类型为int。
alter table 成绩 add 平时成绩 int default 0
alter table 成绩 add 总成绩 int
16. 在“学生”表中，查询前5条的所有记录。
select top 5 * from 学生信息
17. 在“学生”表中，查询20%的所有记录。
select top 20 percent * from 学生信息
18. 在“学生”表中查询班级不能重复的记录。
select distinct 班级 from 学生信息
19. 在“学生”表中查询学号和班级连接显示的记录并将合成的列名为“学号班级”。
select 学号+班级 '学号班级' from 学生信息
20．在“成绩”表中查询期末成绩大于80的并期中成绩成绩大于70分的记录。
select * from 学生信息 where 期末成绩 between 70 and 80
21. 在“成绩”表中查询期末成绩在70--85的所有记录。
22．在“学生”表中，查询学号为20060001，20060002, 20060003的学生的班级,性别。
select * from 学生信息 where 学号 in（20060001，20060002, 20060003'）
23. 在“学生”表中，查询姓刘同学的所有记录。
select * from 学生信息 where 姓名 like '刘%'
24．在“学生”表中，查询姓刘或姓张同学的学号，姓名和班级的记录。
select 学号,姓名,班级 from 学生信息 where 姓名 like '刘%' or 姓名 like '张%'
25. 在“学生”表中，查询叫刘某同学的所有记录。
select * from 学生信息 where 姓名 like '刘_'
26. 在“学生”表中，查询除了姓刘同学的所有记录。
select * from 学生信息 where 姓名 not like '刘%'
27. 在“学生”表中，查询学号以2006开头，是以2结尾的，长度为8位的所有记录。
select * from 学生信息 where 邮政编码 like '[^2006]2'
28. 在“学生”表中，查询出生日期在1985年内出生的所有女生的所有记录。
select * from 学生信息 where 性别='女' and 出生日期 like '%85%'
29. 在“成绩”表中，查询期末成绩在前3名同学的学号，期中成绩和期末成绩的记录。
select top 3 学号,期中成绩,期末成绩 from 学生信息 order by 期末成绩 desc
30．在“成绩”表中，查询期中成绩在后4名同学的所有记录。
select top 4 * from 学生信息 order by 期中成绩
31. 通过“学生”、“课程”、“成绩”表，查询学号是20060002的学生的姓名、所选课程名和期末成绩的记录。
select name,课程名称,期末成绩 from student,成绩,课程 where student.xh=成绩.学号 and 课程.课程号=成绩.课程号 and xh='0207001'
32．修改“成绩”表，添加“平时成绩”列，数据类型为int，默认值为0；再添加“总成绩”列，数据类型为int。
alter table 成绩 add 平时成绩 int default 0
alter table 成绩 add 总成绩 int
33．更新“成绩”表：
期中成绩和期末成绩均大于80平时成绩为20。
select * from 学生信息 where 性别='男' and 期末成绩>80 and 平时成绩=20
34. 将学号为“20060004”的学生删除。
Delete from student where name=’ 20060004’
35．利用基表“学生”创建名为“视图1”的视图，要求列名为：
学号、姓名、性别、班级和住址。
group by 学号 having count（学号）>=2
select 学号,avg（期末成绩） from 成绩 group by 学号 having count（*）>1
36．在“成绩”表中查询期中成绩大于80，且平时成绩为18分的记录。
update 成绩 set 平时成绩=18where 期中成绩>80
37．在“成绩”表中查询期中成绩在前3名的同学的所有记录。
Select top 3 * from 成绩 order by 期中成绩 desc
38．从“学生”表中获取学生的学号、姓名、班级，从“成绩”表中获取学生的期中成绩和期末成绩。
select xh,name,bj,期中成绩,期末成绩 from student,成绩 where student.xh=成绩.学号
39. 统计“学生”表中每个班级的人数。
select bj,count（*） from student group by bj
40．在“成绩”表中查询高于期末成绩的平均成绩的行。
select * from 成绩 where 期末成绩>（select avg（期末成绩） from 成绩）
41.统计有学生选修的课程门数。
42.求选修C004课程的学生的平均年龄。
SELECT AVG（年龄） FROM 学生,选课
WHERE 学生.学生号=选课.学生号 and 课程号=’C004’
43. 求学分为3的每门课程的学生平均成绩。
SELECT 课程.课程号,AVG（成绩） FROM 课程,选课
WHERE 课程.课程号=选课.课程号and 学分=3
GROUP BY 课程.课程号
44.统计每门课程的学生选修人数，超过3人的课程才统计。
要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列。
SELECT 课程号,COUNT（*） FROM 选课
GROUP BY 课程号
HAVING COUNT（*） >3
ORDER BY COUNT（*） DESC, 课程号
45.检索学号比王非同学大，而年龄比他小的学生姓名。
SELECT姓名 FROM 学生
WHERE 学生号>（SELECT学生号 FROM学生
WHERE 姓名=’王非’）
and 年龄<（SELECT年龄 FROM学生
WHERE 姓名=’王非’）
46.在选课表中检索成绩为空值的学生学号和课程号。
SELECT 学生号,课程号 FROM 选课
WHERE 成绩 IS NULL
47．求年龄大于女同学平均年龄的男学生姓名和年龄。
SELECT 姓名,年龄 FROM 学生
WHERE 性别=’男’
and 年龄 >（SELECT AVG（年龄） FROM 学生
WHERE 性别=’女’）
48.求年龄大于所有女同学年龄的男学生姓名和年龄。
SELECT 姓名,年龄 FROM 学生
WHERE 性别=’男’
and 年龄 > all （SELECT 年龄 FROM 学生
WHERE 性别=’女’）
49.检索所有比王华年龄大的学生姓名、年龄和性别。
SELECT 姓名,年龄,性别 FROM 学生
WHERE 年龄 > （SELECT 年龄 FROM 学生
WHERE 姓名=’王华’）
50.检索选修课程C002的学生中成绩最高的学生的学号。
SELECT 学生号 FROM 选课
WHERE 课程号=’C002’ and 成绩=（SELECT MAX（成绩） FROM选课
WHERE课程号=’C002’）
51.检索学生姓名及其所选修课程的课程号和成绩。
SELECT 姓名, 课程号, 成绩 FROM 学生,选课
WHERE学生.学生号=选课.学生号
52.检索选修4门以上课程的学生总成绩（不统计不及格的课程），并要求按总成绩的降序排列出来。
SELECT 学生号,SUM（成绩） FROM 选课
WHERE 成绩>=60
GROUP BY学生号
HAVING COUNT（*）>=4
ORDER BY SUM（成绩） DESC
53．利用T-SQL语句创建一个查询每个学生的平均成绩的视图，要求包含学生的学生号和姓名。
CREATE VIEW 学生_平均成绩
AS
SELECT 学生.学生号,姓名,avg（成绩） AS平均成绩
FROM 学生,选课
WHERE 学生.学生号=选课.学生号
GROUP BY学生.学生号,姓名
54．按照成绩表的总成绩列升序创建一个普通索引（非唯一、非聚集）。
55．聚集索引和非聚集索引有何异同?
答：
聚集索引会对表和视图进行物理排序，所以这种索引对查询非常有效，在表和视图中只能有一个聚集索引。
非聚集索引不会对表和视图进行物理排序。
如果表中不存在聚集索引，则表是未排序的。
56．创建存储过程，从课程表中返回指定的课程的信息。
该存储过程对传递的参数进行模式匹配，如果没有提供参数，则返回所有课程的信息。
答：
CREATE PROCEDURE Pcourse
@name varchar（20） = '%'
AS
SELECT * FROM 课程
WHERE 课程名 LIKE @name
57．为“学生”表创建一个实现插入、更新的触发器，当执行插入操作时，激活该触发器同时显示表中记录。
CREATE TRIGGER d_tr ON dept
FOR insert
AS insert from xuesheng where leader=（SELECT leader FROM insert）
58．利用T-SQL扩展方式声明一个游标，查询学生表中所有男生的信息，并读取数据。
关闭游标，最后释放游标。
CREATE TRIGGER d_tr ON dept
FOR insert
AS insert from xuesheng where leader=（SELECT leader FROM insert）
59.
升级会员 