数据库技术及应用[马涛主编][习题解答]

数据库技术及应用习题答案济南大学闫明霞等二〇〇七年十二月习题 11.1 选择题1、A2、C3、C4、B5、C6、A7、C8、B9、D 10、A 11、D 12、A 13、A1.2 填空题1.数据2.数据的逻辑独立性3.数据的物理独立性4.层次数据模型，网状数据模型，关系数据模型5.能按照人们的要求真实地表示和模拟现实世界、容易被人们理解、容易在计算机上实现6.实体、记录7.属性、字段8.码9.域10.一对一、一对多、多对多11.E-R模型12.E-R模型13.层次模型、网状模型、关系模型14.数据操作、完整性约束15.矩形、菱形、椭圆形16.层次模型、一对多17.网状模型18.关系模型19.关系20.外模式、模式、内模式21.三级模式、两级映像22.外模式、模式、内模式23.数据、程序24.数据逻辑、数据物理25.DBMS(数据库管理系统)、DBA(数据库管理员)1.4 综合题2.(注：各实体的属性省略)3.习题 22.1 单项选择题1、C2、A3、B4、C5、C6、D7、A8、B2.2 填空题1.集合2. 能唯一标识一个实体的属性3.系编号，学号，系编号4.关系，元组，属性5.关系模型，关系，实体，实体间的联系6.投影2.4 综合题1、πsno(σcno=’2’(SC))2、sno(σcname=’信息系统’(SC COURSE))3、no,SNAME,SAGE(STUDENT)习题 44.1 单项选择题1、B2、A3、C4、A5、A6、C7、C8、D9、B10、A11、B 12、A 13、C14、C15、C4.2 填空题1、drop table2、alter table add <列名或约束条件>3、with check option4、基本表基本表5、distinct group by order by6、数据定义数据操纵数据控制7、distinct8、like % _9、自含式嵌入式10、order by asc desc4.3 综合题1、SELECT XH, XM, ZYM, BH, RXSJ FROM STU_INFO, GFIED WHERE STU_INFO.ZYH = GFIED.ZYH AND NL > 23 AND XBM = '男'2、SELECT ZYM 专业名, count(*) 人数FROM STU_INFO, GFIEDWHERE STU_INFO.XSH = '03' AND STU_INFO.ZYH = GFIED.ZYHGROUP BY ZYM3、SELECT bh,count(*) as 人数FROM STU_INFO GROUP BY bh4、SELECT XH, XM, XBM, BH FROM STU_INFOWHERE ZYH IN ( SELECT ZYH ROM STU_INFO WHERE XM = '李明' ) ORDER BY XH5、SELECT DISTINCT KCH, KM FROM STU_INFO, XKWHERE STU_INFO.XSH = '03' AND STU_INFO.XH = XK.XH6、SELECT COUNT(DISTINCT KCH) AS 选课门数, A VG(KSCJ) AS 平均成绩FROM STU_INFO, XKWHERE STU_INFO.XH = XK.XH AND XSH = '12'7、SELECT DISTINCT STU_INFO.XH, XM, BH, ZYM, KMFROM STU_INFO, XK, GFIEDWHERE KSCJ > 85 AND STU_INFO.XH = XK.XH AND STU_INFO.ZYH = GFIED.ZYH ORDER BY ZYM, BH, STU_INFO.XH8、SELECT STU_INFO.XH, XM, XSM, ZYM, BH, PYCCMFROM STU_INFO, XK, GDEPT, GFIEDWHERE KKNY = '20011' AND STU_INFO.XH = XK.XH AND STU_INFO.XSH =GDEPT.XSH AND STU_INFO.ZYH = GFIED.ZYH GROUP BY STU_INFO.XH, XM, XSM, ZYM, BH, PYCCMHA VING COUNT(*) > 109、SELECT DISTINCT bhFROM STU_INFO10、DELETE FROM STU_INFOWHERE XH LIKE '2000%'或DELETE FROM STU_INFOWHERE LEFT(XH,4) = '2000'11、ALTER TABLE STU_INFOADD BYSJ varchar(8)12、UPDATE XKSET KSCJ = 60WHERE KSCJ BETWEEN 55 AND 59 andXH in(SELECT xh FROM stu_info WHERE zyh = '0501')and k m = '大学英语' )13、UPDATE xkSET KCXF=6WHERE KCH = '090101'14、CREATE TABLE CCOURSE(KCH char(6),KM varchar(30),KCYWM varchar(30))15、CREATE VIEW ISE ASSELECT *FROM STU_INFO WHERE XSH=’03’习题 55.1 单项选择题1、B2、A3、B4、A5、B6、C7、B5.2 填空题1、使属性域变为简单域消除非主属性对主关键字的部分依赖消除非主属性对主关键字的传递依赖2、平凡函数依赖3、Y也相同唯一的Y值5.3简单题2、(1) R的码是(Sno,Cno),R是1NF，因为Teacher和Title属性部分函数依赖于码(Sno,Cno)，所以R∈1NF（2）SC（Sno,Cno,Grade）CT(Cno,teacher) TT(Teacher,title)3、D->B C->A4、需求分析需求分析是数据库设计的第一个阶段，从数据库设计的角度来看，需求分析的任务是对现实世界要处理的对象（组织、部门、企业等）进行详细的调查了解，通过对原系统的了解，收集支持新系统的基础数据并对其进行处理，在此基础上确定新系统的功能。

概念结构设计阶段将需求分析得到的用户需求抽象为信息结构即概念模型的过程就是概念结构设计。

简单地说数据库概念结构设计的任务就是根据需求分析所确定的信息需求，建立信息模型。

如E-R模型。

逻辑结构设计阶段数据库逻辑结构设计的任务是把概念结构设计阶段所得到的与DBMS无关的数据模式，转换成某一个DBMS所支持的数据模型表示的逻辑结构。

数据库物理设计阶段数据库物理设计是对给定的关系数据库模式，根据计算机系统所提供的手段和施加的限制确定一个最适合应用环境的物理存储结构和存取方法数据库实施阶段在数据库实施阶段，设计人员运用DBMS提供的数据语言及其宿主语言，根据逻辑设计和物理设计的结果建立数据库，编制与调试应用程序，组织数据入库，并进行试运行数据库运行和维护阶段数据库应用系统经过试运行后即可投入正式运行。

在数据库系统运行过程中必须不断地对其进行评价、调整与修改。

包括：数据库的转储和恢复、数据库的安全性和完整性控制、数据库性能的监督、分析和改进、数据库的重组织和重构造。

6、E－R图是用来描述某一组织(单位)的概念模型，提供了表示实体、属性和联系的方法。

构成ER图的基本要素是实体、属性和关系。

实体是指客观存在并可相互区分的事特；属性指指实体所具有的每一个特性。

7、（1）(2)商店（商店编号，商店名，地址，电话）码：商店编号顾客（顾客编号，姓名，性别，家庭住址，出生年月）码：顾客编号消费（商店编号，顾客编号，消费金额）码：商店编号，顾客编号习题66.1 单项选择题1、A2、D3、D4、D5、B6、D7、C6.2 填空题1、原子性一致性隔离性持续性ACID2、软故障硬故障3、静态动态4、丢失修改不可重复读读“脏”数据5、自主存取控制强制存取控制6、实体完整性约束参照完整性约束用户自定义完整性约束6.4 综合题1.create table 读者(借书证号char(10) primary key,姓名varchar(10) not null,年龄tinyint not null,所在院系varchar(20) not null)create 图书(图书号char(8) primary key,书名varchar(20) not null,作者varchar(10) not null,出版社varchar(30) not null,价格real not null check(价格>0 and 价格<=120) )create 借阅(借书证号char(10) not null,图书号char(8) not null,借阅日期datetime not null,primary key(借书证号, 图书号),foreign key(借书证号) references 读者(借书证号), foreign key(图书号) references 图书(图书号))实验篇答案实验 31.1、select * from jobs1.2、select emp_id,fname+'-'+lname as 'Name' from employee1.3、select emp_id,fname+'-'+lname as 'Name',Year(getdate())-Year(hire_date) as 'worke time' from employee order by 'worke time'2.1、select * from employee where fname like 'f%'2.2、select * from employee where job_id='11'2.3、select emp_id,fname+'-'+lname as 'Name', Year(getdate())-Year(hire_date) as worketime from employee where (Year(getdate())-Year(hire_date)) >5 order by worketime2.4、select * from employee where cast(job_id as integer)>=5 and cast(job_id as integer)<=8 2.5、select * from employee where fname='Maria'2.6、select * from employee where fname like '%sh%' or lname like '%sh%'3.1、select * from sales where ord_date <'1993-1-1'4.1、select distinct bh, zyh from stu_info where bh in (select bh from stu_infogroup by (bh) having count(*)>30 and count(*)<40 ) order by bh或者是select bh,zyh from stu_infogroup by zyh,bhhaving count(bh)>30 and count(bh)<40order by bh4.2、select * from gban where bh like '计%'4.3、select * from gfied where zym like '%管理%'4.4、select xh,xm,zym,stu_info.bh,rxsj from stu_info,gfied,gban where nl>23 and stu_info.zyh=gfied.zyh and stu_info.bh=gban.bh4.5、select zyh,count(*) from gban where xsh='03' group by zyh实验41、Select xh,xm,xbm from stu_info,gdept where stu_info.xsh=gdept.xsh and gdept.xsm=’信息科学与工程’或Select xh,xm,xbm from stu_info where xsh=(select xsh from gdept where xsm=’信息科学与工程’)2、Select stu_info.xh,xm,km from stu_info,xk,gcourse where stu_info.xh=xk.xh and xk.kch=gcourse.kch and kscj>853、Select xh,xm,xsm from stu_info,gdept where stu_info.xsh=gdept.xsh and xh like ‘2000%’4、Select xh, xm from stu_info where xh in (select xh from xk where kch=’090101’ and kscj<60) 或select stu_info.xh,xm from stu_info,xk where stu_info.xh=xk.xh and kch=’090101’and kscj<605、select stu_info.xh,xm,km from stu_info,xk,gcourse where stu_info.xh=xk.xh and xk.kch=gcourse.kch and xsh=’12’ and kscj<606、select stu_info.xh,xm,xsm from stu_info,xk,gdept where stu_info.xsh=gdept.xsh and stu_info.xh=xk.xh and kkny=’20011’ group by stu_info.xh,xm,xsm having count(*)>=10实验51、2题参考实验4答案3、select xh,xm,xbm,bh from stu_info where zyh in (select zyh from stu_info where xm=’李明’) order by xh实验61、reate view num_ban as select count(distinct bh) from stu_info2、reate view ban as select distinct bh from stu_info3、Create view is_stu as select xm,xbm,bh from stu_info where xsh=’01’ or xsh=’02’4、Create view zhang_stu as select * from stu_info where xsh=’01’ and xm like ‘张%’或Create view zhang_stu as select * from stu_info,gdept where xsm=’材料科学与工程’and xm like ‘张%’and stu_info.xsh=gdept.xsh8、Create view 材0168 as select stu_info.*,xk.* from stu_info,xk where bh=’材0168’ and stu_info.xh=xk.xh9、create view gaoshu _stu as select xm from stu_info,xk,gcourse where stu_info.xh=xk.xh and gcourse.kch=xk.kch and km=’高等数学’或Create view gaoshu _stu as select xm from stu_info where xh in(select xh from xk where kch =(select kch from gcourse where km=’高等数学’) )实验 81、create table s (sno varchar(10) primary key, sname varxhar(20) not null unique, status intdefault 10,city varchar(20) not null )create table p (pno int identity (1,1) primary key, pname varxhar(20) , color varchar(10), weight float check ( weight between 10 and 30) )create table ps (sno varchar(10) references s (sno), pno int references p (pno), qty int ,price float, total as qty*price, primary key(sno,pno) )2、delete from p where pname=’螺母’3、update ps set price=price*2。