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

第四章 规范化理论

第五章 数据库设计

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

一、操作题

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

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

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

问题：

在设计数据库结构时，请详细说明BOOK表与BORROW表主键字段的具体配置方案，并阐述这种设计选择背后的逻辑依据。
请列举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. 已知某酒店的业务描述：

每个客户都有一个独一无二的身份证号码。为了准确识别每位客户的身份信息，请记录以下内容：包括身份证号码、姓名、性别以及联系方式。

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

每个客房在同一时间段内仅允许一名顾客入住，并需记录入住房间时间和退房时间。

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

记录R（客户的身份证信息、姓名、性别的信息、联系方式、房间编号、房型类别、定价标准、入住房间时间）

解题要求：

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

（2）找出R的候选键。

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

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

2. 该集团包含多个分厂，在各个分厂均可生产多种规格的产品；同一规格的产品可在不同分厂进行生産。各厂均依照既定的月度或年度生产计划安排生産作业；各分厂均配备多個技术岗与管理人员配置；每位雇员的工作身份仅限于一个分厂；员工与企业之间签订用工合同后即享有一年期的工作承诺与薪酬保障。从属机构的基本资料包括机构代码、名称与办公地址等字段信息；各产品的标识信息包括型号名称与技术参数等内容；员工业务档案中的主记录字段包括考勤记录与个人 identifiable information等数据项

解题要求：

基于所述信息，请您绘制相应的ER图，并在绘制好的ER图中标注实体属性、联系类型以及主码。（8分）

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

（2）

关系模型为：

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

主键是工厂编号

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

主键是产品编号

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

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

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

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