数据库原理期末复习样题

一、简答题（共10分）

• 1.什么叫数据与程序的物理独立性？什么叫数据与程序的逻辑独立性？为什么数据库系统具有数据与程序的独立性？（3分）

数据与程序的的逻辑独立性：
当模式改变时（例如增加新的关系，新的属性，改变属性的数据类型等），由数据库管理员对各个外模式/模式的映像做相应的改变，可以使外模式保持不变。应用程序是依据数据的外模式编写的，从而应用程序不必修改，保证了数据与程序的逻辑独立性，简称数据的逻辑独立性。
数据于程序的物理独立性：
当数据库的存储结构改变了，由数据库管理员对模式/内模式映像做响应的改变，可以使模式保持不变，从而应用程序也不必改变，保证了数据与程序的物理独立性，简称数据的物理独立性。
数据库管理系统在三级模式之间提供的两层映像保证了数据库系统中的数据能够具有较高的逻辑独立性和物理独立性

• 2.什么是关系模式？（2分）

关系的描述称为关系模式。
它可以形象化地表示为R（ U, D, DOM, F）
R:关系名
U:组成该关系的属性名集合
D:U中属性所来自的域
DOM:属性向域的映像集合
F:属性间数据的依赖关系集合

• 3.在关系系统中，当操作违反实体完整性、参照完整性和用户定义的完整性约束条件时，一般是如何分别进行处理的？（5分）

对于违反实体完整性和用户定义的完整性的操作一般都采用拒绝执行的方式进行处理。而对于违反参照完整性的操作，并不都是简单地拒绝执行，有时要根据应用语义执行一些附加的操作，以保证数据库的正确性。

二、计算题（37分）

设宾馆酒店业数据库有四张表：
Hotel(hotelNo, hotelName, city)
Room(roomNo, hotelNo, type(房间类型), price)
Booking(roomNo, guestNo, dateFrom(起始日期), dateTo(终止日期))
Guest(guestNo, guestName, guestAddress)
备注：price为int类型，其余字段均为字符串类型（VARCHAR(n)），下划线标识的字段为主码。

1.试用关系代数完成如下查询：（7分）
1）列出“长春”的所有宾馆的宾馆号和宾馆名称；（2分）

ΠhotelNo,hotelName(σcity=‘长春’（Hotel))

2）列出“长春宾馆”的所有房间类型和价格；（2分）

Πtype,price(σhotelName=‘长春宾馆’(Hotel⋈Room))

3）检索2017年12月2号（’20171202’）入住“长春宾馆”的客人号码和客人姓名。（3分）

ΠguestNo，guestName（Guest）⋈ ΠguestNo（ΠroomNo，guestNo（σdataFrom=‘20171202’(Booking)）⋈ ΠroomNo(ΠhotelNo（σhotelName=‘长春宾馆’（Hotel））⋈ Room)）

2.写出SQL语句：（30分）
1）定义关系模式Hotel和Room，要求在模式中完成以下完整性约束条件的定义：（7分）
（1）定义模式的主码；
（2）定义参照完整性；
（3）房间价格不能低于100元；
（4）定义type、price和hotelName不能为空。

create table Hotel
(hotelNo char(10) primary key,
hotelName char(10) not null,
city char(10)
foreign key(hotelNo) references Room(hotelNo));

create table Room
(roomNo char(10) primary key,
hotelNo char(10),
type char(10) not null,
price char(10) not null check(price>=100));

2）统计各个宾馆的房间数；（2分）

select count(roomNo) from Room group by Hotel.hotelNo;

3）删除2017年12月2号（’20171202’）退房的订房信息；（2分）

delete from Booking where dateTo=‘20171202’);

4）增加一条宾馆信息（hotelNo:H1035, hotelName:前进宾馆, city:长春）；（2分）

insert into Hotel values(‘H1035’,‘前进宾馆’,‘长春’);

5）将“长春宾馆”（宾馆名）的‘369’（房间号）房间的房间类型修改为“单人间”；（3分）

update Room set type = ‘单人间’
where roomNo = ‘369’ and hotelNo in
(select hotelNo from Hotel where hotelName = ‘长春宾馆’)；

6）创建一个断言，限制每个宾馆单人间的房间数不能多于8个；（4分）

create assertion roomnum check(8>=all(select count(*) from Room where
type=‘单人间’ group by hotelNo))；

7）创建一个视图，包含宾馆号、宾馆名称、所在城市、房间号、房间类型和房间价格，并将该视图的查看权限授予用户U1；（4分）

create view h (hotelNo,hotelName,city,roomNo,type,price)
as
(select Hotel.hotelNo,hotelName,city,roomNo,type,price from Hotel,Room
where Hotel.hotelNo = Room.hotelNo);

grant select on h to U1;

8）建立触发器，当对表Room的price属性进行修改时，若价格增加了10%，则将此次操作记录到另一个表Room_U (roomNo, hotelNo, type(房间类型), Oldprice, Newprice)中，其中Oldprice是修改前的价格，Newprice是修改后的价格。（6分）

create trigger Room_T
after update of price on Room
referencing
old row as oldTuple,
new row as newTuple
for each row
when(newTuple.price>=1.1*oldTuple.price)
insert into Room_U (roomNo, hotelNo, type, oldTuple.price, newTuple.price)

三、设计题（23分）

1.设有关系模式R（A,B,C,D,E），给定数据依赖如下：AB→C, A→D, D→E。（8分）
1)列出R的码；

A、B

2)R为第几范式? 说明理由；

第一范式

3)如果R不属于3NF，规范化R为3NF，并阐述规范化过程。

消除部份依赖和传递依赖
消除部份依赖 : 解决办法是用投影把关系模式R分解为两个关系模式：
R1（A，B，C）和 R2（A，D，E）
消除传递依赖： 解决办法是将R2分解为：AD（A，D）和 DE（D，E）
分解后的关系模式AD与DE中不再存在传递依赖

2.把图1中的E-R图转换为关系模型。（7分）

图1. 教务系统E-R图（部分）

教材（教材号，教材名，出版社，价格，课程号）
学生（学号）
课程（课程号）
教师（职工号）
选课（学号，课程号）
授课（课程号，职工号）

3.设有表SC(sno, cno, grade)，编写存储过程将学生选课成绩grade从百分制改为等级制（即A、B、C、D、E）。（8分）

create or replace procedure change_c()
as declare
chgrade char(1);
currecord record;
begin
alter table SC add column(newgrade char(1));
for currecord in select*from SC
loop
if currecord.grade<60 then chgrade=‘E’;
else if currecord.grade<70 then chgrade=‘D’;
else if currecord.grade<80 then chgrade=‘C’;
else if currecord.grade<90 then chgrade=‘B’;
else chgrade=‘A’;
end if;
update SC set newgrade=chgrade
where sno=currecord.sno and
cno=currecord.cno;
end loop;
alter table SC drop column grade;
alter table SC rename newgrade to grade;
end;