数据库原理与 SQL 全部答案

数据库原理与 SQL 全部答案

第一章习题答案

DDCB DADD

2、某医院病房计算机管理中需要如下信息：

一个科室有多个病房、多个医生，一个病房只能属于一个科室，一个医生只属于一个科室，但可负责多个病人的诊治，一个病人的主管医生

只有一个。

完成：设计该计算机管理系统的E-R图

4．假设某公司的业务规则如下：

（1）公司下设几个部门，如技术部、财务部、市场部等；

（2）每个部门承担多个工程项目，每个工程项目属于一个部门； （3）每个部门有多名职工，每一名职工只能属于一个部门；

（4）一个部门可能参与多个工程项目，且每个工程

项目有多名职工参与施工。根据职工在工程项目中完

成的情况发放酬金；

（5）工程项目有工程号、工程名两个属性；部门有部门号、部门名称两个属性；职工有职工号、姓名、性别属性。

请根据上述规则设计E-R模型。

第3章 设计题 答案

1．学校有若干个系，每个系有若干班级和教研室，每个教研室有若干教师，每名教师只教一门课，每门课可由多个教师教；每个班有若干学生，每个学生选修若干课程，每门课程可由若干学生选修。请用E-R图画出该学校的概念模型，注明联系类型，再将E-R模型转换为关系模型。

假定：主码____ 或 #

或 _____

n

数据库原理与 SQL 全部答案

2．工厂生产的每种产品由不同的零件组成，有的零件可用于不同的产品。这些零件由不同的原材料制成，不同的零件所用的材料可以相同。一个仓库存放多种产品，一种产品存放在一个仓库中。零件按所属的不同产品分别放在仓库中，原材料按照类别放在若干仓库中（不跨仓库存放）。请用E-R图画出此关于产品，零件，材料，仓库的概念模型，注明联系类型，再将E-R模型转换为关系模型。

4个实体各自为一个关系模式，2个c）

1:N的联系没有成为一个独立的关系模式，而是合并到多方实体，合a）

并时包括联系得属性

产品-b）

零件-材料（材料编号，零件名，生产日期a） 零件-仓库（零件名，仓库号，数量b）

3、一个图书馆理系统中有如下 信息： 图书：书号、书名、数量、位置 借书人：借书证号、姓名、单位

出版社：出版社名、邮编、地址、电话、E-mail

其中约定：任何人可以借多种书，任何一种书可以被多个人借，借书和还书时，要登记相应的借书日期和还书日期；一个出版社可以出版多种书籍，同一本书仅为一个出版社所出版，出版社名具有惟一性。

根据以上情况，完成如下设计： （1）设计系统的E-R图；

（2）将E-R图转换为关系模式，并指出函数依赖；

（3）指出转换后的每个关系模式的关系键（主键，外键）。 解答： （1）

数据库原理与 SQL 全部答案

借书人关系模式：借书人（借书证号，姓名，单位）

出版社关系模式：出版社（出版社名，邮编，地址，电话，E-mail） 借阅关系模式：借阅（借书证号，书号，借书日期，还书日期） 关系主键：（借书证号，书号） 外键1：借书证号 外键2：书号

第五章 二 设计题 答案 --3.

CREATE DATABASE zygl ON

(NAME = 'zygl_Data',

FILENAME ='E:\sql\zygl_Data.MDF' ,

SIZE = 1, MAXSIZE = 30, FILEGROWTH = 10% )

LOG ON

(NAME = 'zygl_Log',

FILENAME ='E:\sql\zygl_Log.LDF' , SIZE = 3, FILEGROWTH = 1 ) GO --4

ATLER DATABASE ZYGL MODIFY FILE

数据库原理与 SQL 全部答案

( NAME =ZYGL_DATA, FILEGROWTH =5 ) --5

ATLER DATABASE ZYGL ADD FILE

(NAME =ZYGLBAK ,

FILENAME ='E:\sql\zygl_Data1 .NDF' ,

SIZE = 1, MAXSIZE = 50, FILEGROWTH = 10% )

--6

DROP DATABASE ZYGL

第六章 二 设计题 答案 --1.

create database scd go

Use scd Go

CREATE TABLE department (系号 char(3) PRIMARY KEY, 系名 char(20) not null unique ) Go

CREATE TABLE class

(班号 char(3) PRIMARY KEY, 专业名 char(20),

系名 char(20) references department(系名) , 入学年份 int ) Go

CREATE TABLE student (学号 char(7) PRIMARY KEY, 姓名 char(8) NOT NULL,

年龄 int constraint ck_nl CHECK(年龄 >=15 and 年龄<=40)

constraint df_nl DEFAULT 18, --指定约束名ck_nl 和df_nl,以便以后好删除 班号 char(3) references class(班号) )

Insert into department values ('001', '数学'); Insert into department values ('002', '计算机'); Insert into department values ('003', '化学')

Insert into class values ( '101','软件','计算机',2005); Insert into class values ( '102','微电子','计算机',2006);

数据库原理与 SQL 全部答案

Insert into class values ('111','无机化学','化学',2004); Insert into class values ('112','高分子化学','化学',2006 )

Insert into student values('2008101','张山',18,'101'); Insert into student values('2008102','李斯',16,'102'); Insert into student values( '2008103','王玲',17,'111') Insert into student values( '2008104','李飞',19,'112') --2. --1)

Insert into department values(‘006’,’物理系‘) Insert into department values(‘008’,’经济系‘) --2)

Delete class where 入学年份<2004 --3)

Update student set 班号='111' where 姓名='张山'

--4）

Update student set 年龄=年龄+1 --3. --1)

--要删除年龄列，应先删除该列上的约束，再删除 Alter table student drop constraint ck_nl, df_nl --删除年龄列

Alter table student drop column 年龄

Alter table student add 出生日期 datetime --2)

Alter table class

Add 班长学号 char(7) references student(学号)

--上机题答案

--创建数据库ZYCL create database ZYGL go

--使用数据库ZYGL use zygl go

--创建数据表 --1.部门表

create table 部门表 (

部门号 char(2) primary key, 部门名 char(10) not null unique, 电话 char(4)

数据库原理与 SQL 全部答案

go

--2.职员表

create table 职员表 (

员工号 char(3) primary key, 姓名 char(8) not null, 性别 char(2),

部门号 char(2) references 部门表(部门号), 出生日期 smalldatetime , 手机号码 char(11),

工龄 tinyint constraint ck_gl check(工龄>=0 and 工龄<=35), 备注 text ); go

--3.工资表

create table 工资表 (

员工号 char(3) null references 职员表(员工号), 基本工资 decimal(7,2) , 津贴 decimal(5,2), 三金扣款 decimal(7,2),

应发工资 as 基本工资+津贴,

实发工资 as 基本工资+津贴-三金扣款 ) go

--4.工资发放表

create table 工资发放表

(发放编号 int identity(200701,2) , 发放年月 smalldatetime,

员工号 char(3) null references 职员表(员工号),

实发工资 decimal(7,2) --以后可以用函数调用dbo.fn(员工号) ) go

第7章 习题 答案

1. 查询“数据库原理”这门课程的学分和开课学期。 Select 课程号，开课学期 From kc

Where 课程名 =’ 数据库原理’

数据库原理与 SQL 全部答案

2. 查询身高在175cm以上的男生信息。 Select * From xs

Where 性别=’男’ and 身高>175

3. 查询所有姓“李”同学的基本情况。 Select * From xs

Where 姓名 like ‘李%’

4.查询至少选修了两门课程的学生学号。 Select 学号 From xs_kc Group by 学号

Having count(课程号)>=2

5. 将所有学生信息按身高逆序输出。 Select * From xs

Order by 身高 desc

6 检索平均成绩在75分以上的学生姓名、性别和专业。 方法一：

Select 姓名，性别，专业名 From xs

Where 学号 in ( Select 学号 From xs_kc

Group by 学号

Having avg(成绩)>75) 方法二：

Select 姓名，性别，专业名

From xs_kc JOIN xs ON xs.学号=xs_kc.学号 Group by xs.学号, 姓名，性别，专业名 Having avg(成绩)>75

7. 检索“李林”同学不学的课程号。 Select 课程号 From kc

Where 课程号 not in (

Select 课程号 from xs, xs_kc

Where xs.学号=xs_kc.学号 and xs.姓名=’李林’)

数据库原理与 SQL 全部答案

8. 查询只有两门课不及格的学生姓名。 Select 姓名 From xs

Where 学号 in (Select 学号 From xs_kc Where 成绩<60 Group by 学号

Having count(课程号)=2)

9. 检索选修课程包含“程明”同学所选课程之一的学生学号。 Select distinct 学号 From xs_kc

Where 课程号 in (

Select 课程号 from xs, xs_kc

Where xs.学号=xs_kc.学号 and xs.姓名=’ 程明’)

10. 检索同时选修了课程号为101和102这两门课程的学生学号。 方法一，使用自连接： Select a.学号

From xs_kc a , xs_kc b

Where a.学号=b.学号 and a.课程号=’101’ and b.课程号=’102’

方法二，使用子查询： Select 学号 From xs

Where 学号 in (select 学号 From xs_kc where 课程号=’101’ ) And 学号 in ( select 学号 From xs_kc where 课程号=’102’)

Select 学号 From xs

Where 学号 in (select 学号 From xs_kc where 课程号=’101’ And 学号学号 From xs_kc where 课程号=’102’) )

方法三、 Select 学号 From xs_kc

Where 课程号=’101’ or 课程号=’102’ group by学号 having count(学号)=2

11. 检索选修课程名为“数据库原理”的学生学号和姓名。 Select a.学号,姓名

in ( select

数据库原理与 SQL 全部答案

From xs a , xs_kc b , kc c

Where a.学号=b.学号 and b.课程号=c. 课程号 and 课程名=’ 数据库原理’

12. 查询每门课的最高分的学生姓名。 相关查询法

SELECT A.学号, 姓名, B.课程号, 成绩 FROM XS A, WHERE A.学号=B.学号 AND 成绩=

(SELECT MAX(成绩) FROM WHERE B.课程号=C.课程号) 或 派生表法

SELECT A.姓名, B.课程号, B.成绩, C.最高成绩

FROM , , WHERE A.学号=B.学号 AND B.课程号= C.课程号 and B.成绩=C.最高成绩

13. 检索选修课程包含学号为081101的学生所修课程的学生学号。

方法一，

select distinct 学号 from xs_kc

where 课程号 in ( select 课程号 from xs_kc

where 学号='081101') group by 学号 having count(课程号)= (select count (课程号) from xs_kc

where 学号='081101')

方法二，

SELECT 学号，姓名 FROM xs

WHERE NOT EXISTS( SELECT *

FROM kc , xs_kc a

WHERE kc.课程号=a. 课程号 and 学号=’ 081103’

and NOT EXISTS(

SELECT * FROM xs_kc b

WHERE b.学号=xs.学号 AND 课程号=kc.课程号 ) )

14. 创建一个名为avg75的视图，包含所有平均成绩在75分以上的学生信息。 Create view avg75

数据库原理与 SQL 全部答案

As Select * From xs

Where 学号 in ( Select 学号 From xs_kc

Group by 学号

Having avg(成绩)>75)

Create view avg75 As

Select 姓名，性别，专业名，avg(成绩)

From xs_kc JOIN xs ON xs.学号=xs_kc.学号 Group by 姓名，性别，专业名 Having avg(成绩)>75

第8章习题答案

对于数据库scd，库中包含以下系、学生、班级各表：

student （学号，姓名，年龄，班号） class（班号，专业名，系名，入学年份） department （系号，系名） 请使用T-SQL语言完成以下各题。

1． 将student表的班号与姓名这两列组合创建一个升序的非聚集索引。

Create nonclustered index ix_bj_xm on student (班号 asc , 姓名 asc)

2． 为department表的系名建立一个唯一索引，如果输入了重复的键，将忽略该INSERT

或UPDATE语句，并使用填充因子FILLFACTOR 为50%。

Create unique index uqix_xm on department (系名 asc) WITH IGNORE_DUP_KEY， FILLFACTOR = 50

3． 为数据库scd建立一个默认对象，使其对应于年龄为18，将其绑定到student表的

年龄列上。

Create default d_age as 18 Go

Exec sp_bindefault ‘d_age’ , ‘student.年龄’

4． 为数据库scd建立一个规则对象，并将其绑定到学生表的专业名列上，规定专业名

的取值只能为‘护理学’、 ‘地质勘探’和‘考古学’之一。 Create rule r_zym as @zym in (‘护理学’, ‘地质勘探’,’考古学’) Go

Exec sp_bindrule ‘r_zym’, ‘xs.专业名’

5． 修改class表为其建立一个CHECK约束，检查入学年份是否小于2008。

Alter table class

数据库原理与 SQL 全部答案

Add constraint ck_rxnf check (入学年份<2008 )

第9章 答案： 三、设计题

1．使用流程控制语句编写程序：

（1）在CJGL数据库中，使用case函数处理：如果课程的学时在80以上显示学习时间长, 学时在54~80 显示学习时间一般, 否则显示学习时间短。

SELECT 课程号，课程名，学习时间= CASE

WHEN 学时>=80 THEN '学习时间长' WHEN 学时>=54 THEN '学习时间一般' ELSE ‘学习时间短’ END FROM KC

（2）在CJGL数据库中，使用case函数处理：如果学生的专业是“计算机”，显示热门，是“通信工程”显示一般，否则显示冷门。

SELECT 学号, 姓名, 专业热度= CASE 专业名

WHEN ‘计算机’ THEN '热门' WHEN ‘通信工程’ THEN '一般' ELSE '冷门 ' END FROM xs

（3）计算18！=18*17*16…*2*1。 DECLARE @i int , @p bigint SELECT @i=1, @p=1 WHILE @i<=18 BEGIN

SET @p=@p*@i SET @i=@i+1 END

PRINT '18*17*16…*2*1='+CAST(@p AS char(20))

（4）计算S = 1！+2！+3！+……+10！

DECLARE @i int , @p bigint, @s bigint SELECT @i=1, @p=1, @s=0

数据库原理与 SQL 全部答案

WHILE @i<=10 BEGIN

SET @p=@p*@i SET @s=@s+@p

SET @i=@i+1

END

PRINT '1！+2！+3！+……+10！='+CAST(@s AS char(20))

2．以下各题在CJGL数据库中实现：

（1）创建一个自定义函数名为f_nl，根据一个出生日期计算年龄。并用‘1989-8-8’调用该函数求年龄。

create function f_年龄(@出生日期datetime) returns int as Begin

Declare @年龄int

Set @年龄=year( getdate())- year(@出生日期) Return @年龄 end Go --调用

select dbo.f_年龄('1988-1-2')

（2）创建一个自定义函数，可以求解任意数n的阶乘，即n!。并用n=10调用该函数。 CREATE FUNCTION F_C(@n INT) RETURNS INT AS BEGIN

DECLARE @i int , @p bigint SELECT @i=1, @p=1 WHILE @i<=@n BEGIN

SET @p=@p*@i SET @i=@i+1 END Return @ p END Go --调用

select dbo. F_C (10)

（3）创建一个名为“某学期开课一览表”的内嵌表值函数，实现输入某个学期，输出该学期所开课程的课程名，学时和学分的功能；调用该函数检索第一学期的开课情况。

Create function f某学期开课一览表 (@para int ) returns table As return

(select 课程名，学时, 学分 from CJGL.dbo.kc where 开课学期=@para) Go

数据库原理与 SQL 全部答案

以下调用即为内嵌表值函数的调用： Select * from f某学期开课一览表(1) Go

（4）分别用函数或存储过程实现以下功能：给定输入参数课程名，统计该课程成绩介于58~59分的学生人数。使用输入参数“数据库原理”调用该函数或该存储过程。

----函数：

Create function f_cj58 (@cna char(20)) returns int As BEGIN

DECLARE @c int

SELECT @c=count(*) from CJGL.dbo.xs_kc a , CJGL.dbo. kc b

where a.课程号= b.课程号 and 课程名=@cna and (成绩 between 58 and 59) Return @c END Go

以下函数调用：

Select dbo.f_cj58 (‘数据库原理’) as [58~59分人数] Go

-----存储过程：

create procedure p_cj58

@cna char(20), @c int output as

SELECT @c=count(*) from CJGL.dbo.xs_kc a , CJGL.dbo. kc b

where a.课程号= b.课程号 and 课程名=@cna and (成绩 between 58 and 59) return Go

--存储过程调用

DECLARE @c1 int

Exec p_cj58 ‘数据库原理’, @c1 output

（5）创建存储过程来完成求解给定某个学生的学号返回该生的姓名和平均分的功能。 Create procedure p_pj

@sno char(8) , @sna char(8) output , @savg int output as

SELECT @sna=姓名,@ savg=avg(成绩) from CJGL.dbo.xs a , CJGL.dbo.xs_kc b where a.学号= b.学号 and GROUP BY 姓名 return Go --调用

DECLARE @sna char(8) , @savg int

数据库原理与 SQL 全部答案

Exec p_pj ‘061101’ , @sna output , @savg output Select @sna, @savg

（6）分别使用约束和触发器实现以下功能：检查在课程表中插入或修改的课程号是否在101~108之间。

---------------------------使用触发器 Create trigger T_KC on kc For INSERT, UPDATE As

Declare @cno char(4)

Select @cno=课程号 from inserted If @cno not like ’10[1-8]’ rollback transaction go

----------------------------使用约束 Alter talbe kc

Add constraint ck_kh check( 课程号 like ’20[1-8]’ )

3．以下各题在第六章习题（设计题）给出的数据库SCD中实现：

（1）创建一个自定义函数用于统计各班级的人数，将class表增加一个人数字段，然后调用该函数将人数定义为计算列。

USE SCD GO

CREATE FUNCTION F_CNT (@CNUM CHAR(8)) RETURNS INT AS BEGIN

DECLARE @CNT INT SELECT @CNT= COUNT(*) FROM student WHERE 班号=@CNUM RETURN @CNT END GO

USE SCD /*假定用户函数F_CNT在此数据库中已定义*/ CREATE TABLE CLASS (班号 CHAR(3) PRIMARY KEY, 专业名 CHAR(20),

系名 CHAR(20) REFERENCES DEPARTMENT(系名) , 入学年份 INT

人数 AS DBO. F_CNT (班号) )

（2）创建一个可以按给定学号输出学生的姓名、系名信息的存储过程。

数据库原理与 SQL 全部答案

CREATE PROCEDURE stu_info @sno char (7) AS

SELECT a.学号, 姓名, 系名

FROM student a INNER JOIN class b ON a.班号 = b.班号 WHERE 学号=@sno GO

调用：

EXEC stu_info '2008101'

（3）创建一个触发器，当插入或更新某个学生的记录时，检查该班级的学生是否超过50人，如果超过，就发出警告信息同时撤消所作的插入或更新操作。

Create trigger T_stu on student For INSERT, UPDATE As

Declare @cno char(4)

Select @cno=班号 from inserted

If (select count(*) from student

where 班号=@cno ) >50

Raiserror ('该班人数超过50人，插入或修改已撤销.', 16, 1) Rollback transaction

GO