《数据库原理与应用》分章节测试题四、五、六章

左手的ㄟ右手 2022-01-26 13:01 131阅读 0赞

**第四章****规范化理论** ** **

**第五章** **数据库设计**

**第六章** **关系数据库标准语言SQL**

**一、操作题**

把数据库library附加至你的个人SQL Server数据库服务器，

观察所给关系数据库表结构（重点观察BOOK、READER、BORROW、BLERK表），

在SQL Server中完成以下问题和SQL操作：

问题：

1.  BOOK表和BORROW表的主键如何设置，分析其设置原因
2.  BOOK表设置的约束有哪些，设置规则是什么，分析设置原因

查询：

1.  查询馆藏图书中各出版社图书的数量；
2.  查询未登记作者姓名的图书编号和书名；
3.  查询2001至2002年出版的图书的全部信息；
4.  查询图书价格小于平均图书价格的书的编号和书名；
5.  查询借书但未归还的读者的编号、姓名及所借图书编号、书名；
6.  查询那个图书借阅次数最多的出版社名称；
7.  删除已还图书的借阅记录，并思考真实环境中，已还图书是否为直接删除，更合理的做法应该是什么？

新建查询，执行SQL语句导入mdf、ldf文件：

EXEC  sp_attach_db  @dbname = 'library',     
    @filename1 = 'd:\data\library.mdf',     --mdf文件路径
    @filename2 = 'd:\data\library_log.ldf'     --ldf文件路径

如果导入不成功，则需要修改mdf，ldf文件的权限。

找到你的mdf，ldf文件，右键-属性-安全-编辑，完全控制选择允许。

1) 查询馆藏图书中各出版社图书的数量

select count(*)图书数量,PRESS
    from BOOK
    group by PRESS;

2) 查询未登记作者姓名的图书编号和书名

select BOOK_ID,BOOK_NAME
    from BOOK
    where AUTHOR is null;

3) 查询2001至2002年出版的图书的全部信息

select *
    from BOOK
    where year(PRESS_DATE)='2001' or year(PRESS_DATE)='2002';

4) 查询图书价格小于平均图书价格的书的编号和书名

先计算一下平均图书价格：

select avg(PRICE)平均图书价格 from BOOK;

将其作为子句：

select BOOK_ID,BOOK_NAME
 from BOOK
 where PRICE<(select avg(PRICE) from BOOK);

5) 查询借书但未归还的读者的编号、姓名及所借图书编号、书名

select BORROW.READER_ID,NAME,BORROW.BOOK_ID,BOOK_NAME
    from BOOK,READER,BORROW
    where READER.READER_ID=BORROW.READER_ID 
    and BOOK.BOOK_ID=BORROW.BOOK_ID
    and FLAG_RETURN='N';

6) 查询哪个图书借阅次数最多的出版社名称

先查询哪个图书借阅次数最多：

select top 1 BOOK_ID,count(*)借阅次数 
    from BORROW 
    group by BOOK_ID;

试着查看这个图书是哪个出版社的：

select PRESS
    from BOOK
    where BOOK_ID=10300649;

整合在一起：

select top 1 BORROW.BOOK_ID,count(*)借阅次数,PRESS
    from BORROW,BOOK
    where BOOK.BOOK_ID=BORROW.BOOK_ID
    group by BORROW.BOOK_ID,BOOK.PRESS;

**二、设计题（每题30分，共60分）**

1. 已知某酒店的业务描述：

（1）每一个客户有惟一的身份证号，客户需记录身份证号、姓名、性别、电话等信息；

（2）每个客房有唯一的客房编号，客房有客房类型、收费标准等信息；

（3）一个客房同一时间只能有一个客户入住，要记录客户的入住时间和退房时间；

根据上述业务描述和订单格式得到关系模式R：

R（客户身份证号，姓名，性别，客户电话，客房编号，客房类型，收费标准，入住时间，退房   时间）

解题要求：

（1）写出R的基本函数依赖集。

（2）找出R的候选键。

（3）判断R最高可达到第几范式，为什么？

（4）将R分解为一组满足3NF的模式。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70][]

\[同类题型\]已知新华书店销售订单的屏幕输出格式如题41图所示。

书店的业务描述：

（1）每一个订单有惟一的订单编号；

（2）一个订单可以订购多种图书，且每一种图书可以在多个订单中出现；

（3）一个订单对应一个客户，且一个客户可以有多个订单；

（4）每一个客户有惟一的客户编号；

（5）每一种图书有惟一的图书编号。

根据上述业务描述和订单格式得到关系模式R：

R（订单编号，日期，客户编号，客户名称，客户电话，地址，图书编号，书名，定价，数量）

问：（1）写出R的基本函数依赖集。

（2）找出R的候选键。

（3）判断R最高可达到第几范式，为什么？

（4）将R分解为一组满足3NF的模式。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 1][]

\[同类题型\]

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 2][]

\[同类题型\] 假设某旅馆业务规定，每个账单对应一个顾客，账单的发票号是唯一的，账单中包含一个顾 客姓名、到达日期和顾客每日的消费明细，账单的格式如题 41图所示。

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 3][]

如果根据上述业务规则， 设计一个关系模式： R（发票号， 到达日期，顾客姓名，消费日期，项目，金额）。

试回答下列问题：

（1）找出 R的候选键  
（2）判断 R最高可达到第几范式，为什么？  
（3）给出 R的一个可能的 3NF分解

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 4][]

2. 某企业集团有若干工厂，每个工厂生产多种产品，且每一种产品可以在多个工厂生产，每个工厂按照固定的计划数量生产产品；每个工厂聘用多名职工，且每名职工只能在一个工厂工作，工厂聘用职工有聘期和工资。工厂的属性有工厂编号、厂名、地址，产品的属性有产品编号、产品名、规格，职工的属性有职工号、姓名。

解题要求：

（1）根据上述语义画出E-R图，在E-R图中需注明实体的属性、联系的类型及实体的码。（8分）

（2）将E-R模型转换成关系模型，并指出每个关系模式的主键和外键。（6分）

解：

（1）

![watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 5][]

（2）

关系模型为：

工厂(工厂编号,厂名,地址)

主键是工厂编号

产品(产品编号,产品名,规格)

主键是产品编号

生产(工厂编号,产品编号,计划数量)

主键是(工厂编号,产品编号)  
       外键是(工厂编号,产品编号)

职工(职工号,姓名,聘期,工资,工厂编号)

主键是职工号  
       外键是工程编号

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70]: /images/20220126/522e3ab0b7a54c468a467b38fe1df49b.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 1]: /images/20220126/fb5646b7209048be951906fc9f1e9d19.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 2]: /images/20220126/5ffdebe056784722b160c4fbc1950659.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 3]: /images/20220126/5e7d8279a69f4c73a38130397fef3ed4.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 4]: /images/20220126/49b7551e9c3944f7a9bce0892025499e.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2R5d182NjY2NjY_size_16_color_FFFFFF_t_70 5]: /images/20220126/c4de496962574b2ca1cd1e6ea1c58804.png