1． 设教学数据库中有三个基本表：

学生表S（SNO，SNAME，AGE，SEX），其属性分别表示学号、学生姓名、年龄、性别。课程表C（CNO，CNAME，TEACHER），其属性分别表示课程号、课程名、上课教师名。选修表SC（SNO，CNO，GRADE），其属性分别表示学号、课程号、成绩。

有如下SQL查询语句：

SELECT CNO

FROM C

WHERE CNO NOT IN

(SELECT CNO

FROM S,SC

WHERE S.SNO=SC.SNO

AND SNAME='张三');

请完成下列问题：

（1）用汉语句子阐述上述SQL语句的含义；

（2）用等价的关系代数表达式表示上述SQL查询语句。

（1）查询张三同学没有选修的课程的课程号。（2分）

（2）πCNO(C)- πCNO (sSNAME='张三' (S)SC) 或

πCNO(C)- πCNO (sSNAME='张三' (SSC)) （2分）

 

2.  设有如图3所示的三个关系。其中各个属性的含义如下：A＃（商店代号）、ANAME（商店名）、WQTY（店员人数）、CITY（所在城市）、B＃（商品号）、BNAME（商品名称）、PRICE（价格）、QTY（商品数量）。

 

A                                      B

A#	ANAME	WQTY	CITY
101	韶山商店	15	长沙
204	前门百货商店	89	北京
256	东风商场	501	北京
345	铁道商店	76	长沙
620	第一百货公司	413	上海

 

B＃	BNAME	PRICE
1	毛笔	21
2	羽毛球	784
3	收音机	1325
4	书包	242

AB

A＃	B＃	QTY
101	1	105
101	2	42
101	3	25
101	4	104
204	3	61
256	1	241
256	2	91
345	1	141
345	2	18
345	4	74
620	4	125

图3

试用SQL语言写出下列查询：

（1）找出店员人数不超过100人或者在长沙市的所有商店的代号和商店名。

（2）找出至少供应了代号为’256’的商店所供应的全部商品的其它商店的商店名和所在城市。

（1）SELECT A#, ANAME FROM A

WHERE WQTY<=100 OR CITY='长沙'; （2分）

（2）SELECT ANAME,CITY FROM A

WHERE NOT EXISTS

(SELECT * FROM B 

WHERE EXISTS

(SELECT * FROM AB AB1

WHERE A#='256' AND B#=B.B#)

AND NOT EXISTS

(SELECT * FROM AB AB2

WHERE A#!='256' AND A#=A.A# AND B#=B.B#)

); （4分）

3.  设有职工基本表：EMP（ENO，ENAME，AGE，SEX，SALARY），其属性分别表示职工号、姓名、年龄、性别、工资。为每个工资低于1000元的女职工加薪200元，试写出这个操作的SQL语句。

UPDATE EMP

SET SALARY=SALARY+200

WHERE SALARY<1000 AND SEX='女';（3分）

4. 设某工厂数据库中有两个基本表：

车间基本表：DEPT（DNO，DNAME，MGR_ENO），其属性分别表示车间编号、车间名和车间主任的职工号。

职工基本表：ERP（ENO，ENAME，AGE，SEX，SALARY，DNO），其属性分别表示职工号、姓名、年龄、性别、工资和所在车间的编号。

建立一个有关女车间主任的职工号和姓名的视图，其结构如下：

VIEW6（ENO，ENAME）。试写出创建该视图VIEW6的SQL语句。

CREATE VIEW VIEW6

AS

SELECT ENO, ENAME FROM EMP

WHERE SEX='女' AND ENO IN

(SELECT MGR_ENO FROM DEPT)

5. 设有关系R和函数依赖F：

R（A，B，C，D，E），F = { ABC→DE，BC→D，D→E }。

试求下列问题：

（1）关系R的侯选码是什么？R属于第几范式？并说明理由。（3分）

（2）如果关系R不属于BCNF，请将关系R逐步分解为BCNF。（5分）

要求：写出达到每一级范式的分解过程，并指明消除什么类型的函数依赖。

（1）关系R的候选码是（A，B，C），R∈1NF，因为R中存在非主属性D，E对候选码（A，B，C）的部分函数依赖。　（3分）

（2）首先消除部分函数依赖

将关系分解为：

R1(A，B，C) （A，B，C）为候选码，

R1中不存在非平凡的函数依赖

R2(B，C，D，E)，（B，C）为候选码，

R2的函数依赖集为：F2={（B，C）→D，D→E}

在关系R2中存在非主属性E对候选码（B，C）的传递函数依赖，所以将R2进一步分解：

R21(B，C，D) ，（B，C）为候选码，

R21的函数依赖集为：F21 = { （B，C）→D }

R22(D，E) ，D为候选码，

R22的函数依赖集为：F22 = { D→E }

在R1中已不存在非平凡的函数依赖，在R21、R22关系模式中函数依赖的决定因素均为候选码，所以上述三个关系模式均是BCNF。

五、综合题（15分）

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

（1）根据上述语义画出E-R图；（5分）

（2）将该E-R模型转换为关系模型；（5分）

（要求：1:1和1:n的联系进行合并）

1. 指出转换结果中每个关系模式的主码和外码。（5分）

（1）本题的E-R图如下图所示。

 

 

（2）转化后的关系模式如下：

工厂（工厂编号，厂名，地址）

       产品（产品编号，产品名，规格）

         职工（职工号，姓名，工厂编号，聘期，工资）

         生产（工厂编号，产品编号，计划数量）

   

    （3）每个关系模式的主码、外码如下：

       工厂：主码是工厂编号，无外码；

         产品：主码是产品编号，无外码；

         职工：主码职工号，外码是工厂编号；

         生产：主码是（工厂编号，产品编号），

               外码是工厂编号、产品编号。