《企业数据库应用》试卷

《企业数据库应用》试卷

一、 数据库方案设计（20分）

1． 某企业生产若干产品，每种产品由不同的零件组成，有的零件可用在不同的产品上；

这些零件由不同的原材料构成，不同的零件所使用的材料可以相同；这些零件按所属的不同产品分别放在仓库中。使用E-R图描述该企业产品、零件、材料、仓库等信息关联关系的概念模型，并建立其对应的关系模型。要求：

1) 分别描述各局部关系的概念模型，合理确定各实体和联系的主要属性和关键属

性。（12分） 产品（产品号，产品名，规格型号，单价）； 零件（零件号，零件名称，零件数量，供应商号，仓库号，单价，描述）； 材料（材料号，材料名称，类别）； 仓库（仓库号，面积，管理员，所在城市）； 存放（仓库号，材料号，数量）； 组成（产品号，零件号，数量）； 制造（零件号，材料号，数量）； 储存（仓库号，零件号，数量）；

2) 将各局部概念模型整合成全局概念模型（课省略属性描述），并进行优化处理。

（3分）

二、 3) 将全局概念模型转化为关系数据模型，并使其符合3NF规范。（5分） 数据定义语言运用（30分）

存储，主文件组包括主要数据文件SalesDat1.mdf，初始大小为10MB，按10%自动增长，最大大小不受限制；次文件组dbgroup_S包括辅助数据文件SalesDat2.ndf，初始大小为5MB，按5MB自动增长，最大限制为20MB；日志文件存放在SalesLog.ldf中，初始大小为5MB，按10%自动增长，最大大小不受限制。（10分）

USE master

GO

CREATE DATABASE Sales_db 2． 在C：\Sales目录中创建一个名为Sales_db的数据库，将数据文件分为两个文件组

《企业数据库应用》试卷

ON

/*主文件组包括主要数据文件SalesDat1.mdf，初始大小为10MB，按10%自动增长，最大大小不受限制*/

PRIMARY

(NAME=” SalesDat1”,

FILENAME=”C：\Sales\SalesDat1.mdf”,

SIZE=10MB,

MAXSIZE= UNLIMITED,

FILEGROWTH =10%),

FILEGROUP dbgroup_S

/*次文件组dbgroup_S包括辅助数据文件SalesDat2.ndf，初始大小为5MB，按5MB自动增长，最大限制为20MB*/

(NAME=”SalesDat2”,

FILENAME=”C：\Sales\SalesDat2.ndf”,

SIZE=5MB,

MAXSIZE= 20MB,

FILEGROWTH =5MB),

LOG ON

/*日志文件存放在SalesLog.ldf中，初始大小为5MB，按10%自动增长，最大大小不受限制*/

(NAME=”SalesLog”,

FILENAME=”C：\Sales\SalesLog.ldf”,

SIZE=5MB,

MAXSIZE= UNLIMITED,

FILEGROWTH =10%);

GO

3． 在数据库Ex_db积）、职工（仓库号、职工号，工资）、供应商（供应商号、供应商名、地址、电话）、订购单（职工号、供应商号、订单号、订购日期）。约束要求如下：仓库面积不小于100M2，每座城市只能设一个仓库；职工最低工资为800元，最高5000元，缺省值为1200元；订购单中的职工和供应商信息不能为空。该关系模型的关系示意图如下：（20分）

《企业数据库应用》试卷

答案：

USE Ex_db

IF EXISTS(SELECT name FROM sysobjects

WHERE name=’仓库’ AND xtype=’U’DROP TABLE 仓库)

GO

/*（1）建立仓库表*/

CREATE TABLE 仓库(

仓库号 CHAR(5) PRIMARY KEY,

城市 CHAR(10) constraint un_city unique,

面积 INT CHECK (面积 > 100) )

/*（2）建立职工表*/

CREATE TABLE 职工(

仓库号 CHAR(5) FOREIGN KEY REFERENCES 仓库,

职工号 CHAR(5) PRIMARY KEY,

工资 INT CHECK (工资 >= 800 AND 工资 <= 5000)

DEFAULT 1200 )

/*（3）建立供应商表*/

CREATE TABLE 供应商(

供应商号 CHAR(5) PRIMARY KEY,

供应商名 CHAR(20),

地址 CHAR(20),

电话 INT)

/*（4）建立订购单表*/

CREATE TABLE 订购单(

职工号 CHAR(5) NOT NULL FOREIGN KEY REFERENCES 职工,

供应商号 CHAR(5) NOT NULL FOREIGN KEY REFERENCES 供应商,

订购单号 CHAR(5) PRIMARY KEY,

订购日期 DATETIME DEFAULT getdate())

GO

《企业数据库应用》试卷

三、 数据查询语言应用（每小题5分，共计20分）

某个学籍数据库有如下表结构：学生（学号、姓名、性别、出生日期、班级）

课程（课程号、课程名称、课程类别、课时） 选课（学号、课程号、成绩）

请完成下列查询要求：

4． 查询出“信管0701”班的所有男生的学号和姓名。

SELECT 学号，姓名

FROM 学生

WHERE 班级=’信管0701’ AND 性别=’男’

5． 查询出各班级学生人数。

SELECT 班级, COUNT(*) AS 人数

FROM 学生

GROUP BY班级

6． 查询出“C语言”课程成绩前5名的学生的学号、姓名和班级

SELECT TOP 5学生.学号, 姓名, 班级

FROM学生 JOIN 选课 ON 学生.学号 = 选课.学号 JOIN 课程 ON课程.课程号 = 选课.课程号

WHERE课程名称 = ‘C语言’

ORDER BY 成绩 DESC

7． 查询出“企业数据库应用”课程成绩高于张三同学此门课程成绩的学生学号。

SELECT学生.学号, 姓名

FROM 学生JOIN 选课 ON 学生.学号 = 选课.学号 JOIN 课程 ON课程.课程号 = 选课.课程号

WHERE课程名称 = ‘企业数据库应用’ AND 成绩 > (

SELECT成绩

FROM 学生JOIN 选课 ON 学生.学号 = 选课.学号 JOIN 课程 ON课程.课程号 = 选课.课程号

WHERE课程名称 = ‘企业数据库应用’ AND姓名 = ‘张三’

)

四、 数据库应用编程（每小题10分，共计30分）

8． 请定义一个可更新游标cur_update_book，该游标的功能为：选择图书

本书，并指定对书价进行更新，然后利用fetch语句提取该游标数

折。图书表book的结构如下：BOOK（book_name，author,press,

DECLARE cur_update_book CURSOR

FOR

SELECT TOP 10 book_name,author,press,price FROM book

ORDER BY price

FOR UPDATE OF price

GO

OPEN cur_update_book --打开游标

FETCH NEXT FROM cur_update_book --逐行提取记录

UPDATE book SET price = price*0.7 WHERE CURRENT OF

cur_update_book --更新价格

《企业数据库应用》试卷

WHILE @@FETCH_STATUS = 0

BEGIN

FETCH NEXT FROM cur_update_book

UPDATE book SET price = price*0.7 WHERE CURRENT OF

cur_update_book

END

CLOSE cur_update_book --关闭并删除游标

DEALLOCATE cur_update_book

9． 创建一个查看商品库存量并返回执行状态码和库存信息的存储过程

编号为输入参数，然后判断该商品的库存量，如果数量大于15，则认为数量充裕！并返回状态码3；如果数量小于5，则输出商品库存量不多并返回状态码1；如果数量在5至15之间，则输出商品库存量良好，供应正常。该存储过程使用了“经销商”数据库中的库存表（库存编号，商品编号）

CREATE PROCEDURE pr_store_good

@商品编号 varchar(20)

@返回值1 varchar(20) output

AS

BEGIN

DECLARE @库存 int DECLARE @返回值2 INT SELECT @库存 =库存量 FROM 库存表 WHERE 商品编号=@商品编号

IF @库存>15

SET @返回值1 = '状态码为：'+ CAST(3 as varchar(10))

IF @库存>5 AND @库存<15

SET @返回值1 = '状态码为：'+ CAST(1 as varchar(10))

SELECT @返回值2 = 库存量 FROM 库存表 WHERE 商品编号=@商品编号

return @返回值2

END

GO

--返回值的接收

declare @接收值1 varchar(20)

declare @接收值2 int

exec pr_store_good @接收值1 output

exec @接收值2 = pr_store_good

print @接收值1

print @接收值2

10． 你在开发某公司的管理信息系统，其中要根据预算来跟踪控制各采购 经现在预算表中都有一个记录。预算表和购买表的结构为：预算（经

现在预算金额），购买（购买代号，购买现金，经理代号），其中：

现有预算金额=预算总金额-购买金额

经理的每次购买都要与“现有预算金额”做比较，当该次的“购

算金额”时。才允许插入到购买表中（一次购买只插入一条记录）。

《企业数据库应用》试卷

的“现有预算金额”（扣减“购买金额”），请在购买表上编写一个触发器 CREATE TRIGGER Purchase_Insert ON 购买表

FOR INSERT

AS

BEGIN

IF (SELECT count(*) FROM Inserted) <> 1

BEGIN

RAISERROR(‘一次购买只允许插入一个记录！’,16, 1) ROLLBACK TRANSACTION

RETURN

END

IF ( SELECT a.现有预算金额-b.购买金额

FROM 预算表 AS a INNER JOIN

Inserted AS b ON a.经理代号=b.经理代号)<0

BEGIN

RAISERROR(‘现有预算金额不足支付购买金额 ！’,16, 1) ROLLBACK TRANSACTION

RETURN

END

UPDATE 预算表

SET 现有预算金额 = a.现有预算金额 – b.购买金额

FROM 预算表 AS a INNER JOIN

Inserted AS b ON a.经理代号=b.经理代号

END

分析以下程序，说明各语句的功能，并写出程序运行的结果。 Declare @sum int , @count int

Set @sum=0

Set @count=1

Label_1:

Select @sum=@sum+@count

Select @count=@count+1

If @count<=5

Goto label_1

Select @count , @sum

DECLARE @A INT, @B INT

Declare @sum int , @count int

Set @sum=0Set @count=1Label_1:

Select @sum=@sum+@count

Select @count=@count+1

If @count<=5 Gotolabel_1