（100<=ALL（SELECT PRICE
FROM SHOP A，SALE B，GOODS C
WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST'））；
或 CREATE ASSERTION ASSE8 CHECK
（NOT EXISTS（SELECT *
FROM SHOP A，SALE B，GOODS C
WHERE A.S#=B.S# AND B.G#=C.G#
AND AREA='EAST' AND PRICE<100））；
? （5）试写出下列操作的SQL语句：
? 统计区域名为“EAST”的所有商店销售的每一种商品的总数量和总价值。 ? 要求显示（G#，GNAME，SUM_QUANTITY，SUM_VALUE），其属性为商品编号、商品名称、销售数量、销售价值。
解：SELECT C.G#，GNAME，SUM（QUANTITY）AS SUM_QUANTITY，
PRICE*SUM（QUANTITY）AS SUM_VALUE
FROM SHOP A，SALE B，GOODS C
WHERE A.S#=B.S# AND B.G#=C.G# AND AREA='EAST' GROUP BY C.G#，GNAME；
（注：SELECT子句中的属性C.G#，GNAME应在分组子句中出现）
4、今有一个层次数据库实例，试用子女一兄弟链接法和层次序列链接法画出它的存储结构示意图。
? 5、请设计一个图书馆数据库，此数据库中对每个借阅者保存读者记录，包括：读者 号，姓名，地址，性别，年龄，单位。对每本书存有：书号，书名，作者，出版社。对 每本被借出的书存有读者号、借出日期和应还日期。要求：给出E 一R 图，再将其转换为关系模型。 答：
关系模型为：读者（读者号，姓名，地址，性别书（书号，书名，作者，出版社）借书 （读者号，书号，借出日期，年龄，单位）应还日期）
? 6、设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：供应商（供应商代码，供应商姓名，供应商状态，供应商所在城市） S(SNO,SNAME,STATUS,CITY)
?零件（零件代码，零件名，颜色，重量） ?P(PNO，PNAME，COLOR。WEIGHT)
? 工程项目（工程项目代码，工程项目名，工程项目所在城市） ? J(JNO,JNAME,CITY)
? 供应情况（供应商代码，零件代码，工程项目代码，供应数量） ? SPJ(SNO,PNO,JNO,QTY)
? 试用关系代数和SQL语言完成下列查询。 ? （1）求供应工程J1零件的供应商号码SNO： SELECT DIST SNO FROM SPJ WHERE JNO='J1' ? （2）求供应工程J1零件P1的供应商号码SNO：
SELECT DIST SNO FROM SPJ WHERE JNO='J1' AND PNO='P1'；
? （3）求供应工程J1零件为红色的供应商号码SNO：
SELECT SNO FROM SPJ,P WHERE JNO='J1' AND SPJ PNO=P PNO AND COLOR='红'； ? （4）求没有使用天津供应商生产的红色零件的工程号JNO：
SELECT DIST JNO FROM SPJ WHERE JNO NOT IN (SELE JNO FROM SPJ,P,S WHERE S.CITY=' 天津 ' AND COLOR=' 红 ' AND S.SNO=SPJ.SNO AND P.PNO=SPJ.PNO)；
? （5）求至少用了供应商S1所供应的全部零件的工程号JNO： 由于 VFP 不允许子查询嵌套太深，将查询分为两步 A、查询S1 供应商供应的零件号
SELECT DIST PNO FROM SPJ WHERE SNO='S1'结果是（P1，P2）； B、查询哪一个工程既使用 P1 零件又使用 P2 零件。 SELECT JNO FROM SPJ WHERE PNO='P1'
AND JNO IN (SELECT JNO FROM SPJ WHERE PNO='P2')； ? 7、设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：
? 供应商（供应商代码，供应商姓名，供应商状态，供应商所在城市） ? S(SNO,SNAME,STATUS,CITY)
? 零件（零件代码，零件名，颜色，重量） ? P(PNO，PNAME，COLOR。WEIGHT)
? 工程项目（工程项目代码，工程项目名，工程项目所在城市） ? J(JNO,JNAME,CITY)
? 供应情况（供应商代码，零件代码，工程项目代码，供应数量） ? SPJ(SNO,PNO,JNO,QTY)
? 试用SQL语言完成以下各项操作：
? （1）找出所有供应商的姓名和所在城市。
SELECT SNAME,CITY FROM S
? （2）找出所有零件的名称、颜色、重量。
SELECT PNAME,COLOR,WEIGHT FROM P
? （3）找出使用供应商S1所供应零件的工程号码。
SELECT DIST JNO FROM SPJ WHERE SNO='S1'
? （4）找出工程项目J2使用的各种零件的名称及其数量。 SELECT PNAME,QTY FROM SPJ,P
WHERE P.PNO=SPJ.PNO AND SPJ.JNO='J2'
? （5）找出上海厂商供应的所有零件号码。
SELECT PNO FROM SPJ,S WHERE S.SNO=SPJ.SNO AND CITY='上海'
? （6）找出使用上海产的零件的工程名称。
SELECT JNAME FROM SPJ,S,J
WHERE S.SNO=SPJ.SNO AND S.CITY='上海' AND J.JNO=SPJ.JNO
? （8）把全部红色零件的颜色改成蓝色。
UPDATE P SET COLOR='蓝' WHERE COLOR='红'
? （9）由S5供给J4的零件P6改为由S3供应。
UPDATE SPJ SET SNO='S3' WHERE SNO='S5' AND JNO='J4' AND PNO='P6' ? （10）请将(S2，J6，P4，200)插入供应情况关系。 INSERT INTO SPJ VALUES（‘S2’，‘J6’，‘P4’，200）
综合题
? 1、假设某商业集团数据库中有一关系模式R如下： ? R (商店编号，商品编号，数量，部门编号，负责人) ? 如果规定：
? (1) 每个商店的每种商品只在一个部门销售； ? (2) 每个商店的每个部门只有一个负责人； ? (3) 每个商店的每种商品只有一个库存数量。 ? 试回答下列问题：
? (1) 根据上述规定，写出关系模式R的基本函数依赖； （商店编号，商品编号）→ 部门编号 （商店编号，商品编号）→ 数量 （商店编号，部门编号）→ 负责人 ? (2) 找出关系模式R的候选码； 候选码（商店编号，商品编号）
? (3) 试问关系模式R最高已经达到第几范式？为什么？ 1NF，存在部分函数和传递函数依赖。
? (4) 如果R不属于3NF，请将R分解成3NF模式集。
R1（商店编号，商品编号，商品库存数量，部门编号）；R2（商店编号，负责人） ? 2、建立一个关于系、学生、班级、学会等诸信息的关系数据库。 ? 学生：学号、姓名、出生年月、系名、班号、宿舍区。 ? 班级：班号、专业名、系名、人数、入校年份。 ? 系：系名、系号、系办公地点、人数。
? 学会：学会名、成立年份、办公地点、人数。
? 语义如下：一个系有若干专业，每个专业每年只招一个班，每个班有若干学生。一个系的学生住在同一宿舍区。每个学生可参加若干学会，每个学会有若干学生。学生参加某学会有一个入会年份。
? 请给出关系模式，写出每个关系模式的极小函数依赖集，指出是否存在传递函数依赖，对于函数依赖左部是多属性的情况讨论函数依赖是完全函数依赖，还是部分函数依赖。指出各
关系模式的候选码、外部码，有没有全码存在?
解：(1)关系模式如下：
学生：S(Sno，Sname，Sbirth，Dept，Class，Rno) 班级：C(Class，Pname，Dept，Cnum，Cyear) 系：D(Dept，Dno，Office，Dnum) 学会：M(Mname，Myear，Maddr，Mnum) (2)每个关系模式的最小函数依赖集如下：
A、学生S (Sno，Sname，Sbirth，Dept，Class，Rno) 的最小函数依赖集如 下:Sno?Sname，Sno?Sbirth，Sno?Class，Class?Dept，DEPT?Rno 传递依赖如下：
由于Sno?Dept，而Dept?Sno ，Dept?Rno（宿舍区） 所以Sno与Rno之间存在着传递函数依赖。 由于Class?Dept，Dept ? Class，Dept?Rno 所以Class与Rno之间存在着传递函数依赖。 由于Sno?Class，Class?Sno，Class?Dept 所以Sno与Dept之间存在着传递函数依赖。
B、班级C(Class，Pname，Dept，Cnum，Cyear)的最小函数依赖集如下: Class?Pname，Class?Cnum，Class?Cyear，Pname?Dept. 由于Class?Pname，Pname?Class，Pname?Dept 所以C1ass与Dept之间存在着传递函数依赖。
C、系D(Dept，Dno，Office，Dnum)的最小函数依赖集如下： Dept?Dno，Dno?Dept，Dno?Office，Dno?Dnum
根据上述函数依赖可知，Dept与Office，Dept与Dnum之间不存在传递依赖。 D、学会M(Mname，Myear，Maddr，Mnum)的最小函数依赖集如下： Mname?Myear，Mname?Maddr，Mname?Mnum 该模式不存在传递依赖。
(3)各关系模式的候选码、外部码，全码如下： A、学生S候选码：Sno；外部码：Dept、Class；无全码 B、班级C候选码：Class；外部码：Dept；无全码 C、系D候选码：Dept或Dno；无外部码；无全码 D、学会M候选码：Mname；无外部码；无全码
? 3、现有一个未规范化的表，包含了项目、部件和部件向项目已提供的数量信息。请采用规范化方法，将该表规范化到3NF要求。
数据库系统概论 期末考试复习题(附答案)
