山东科技大学数据库原理试卷A与参考答案及评分标准

部分依赖。 5、该关系模式属于 1NF ,因为存在非主属性队码的部分依赖。 6、Teacher(Tno ,Tname ,Tel ,Dpartment) ;BOOK (Bno ,Bname )BR(Tno ,Bno ,BorrowDate , Rdate ,Backup) 。评分标准:共 30 分。每小题 5 分。按照叙述定义的准确和全面程度酌情给分。五、设计题( 30 分) 1 .解:关系代数表达式: π S# , SNAME (σ GNAME= ' 冰箱'( SHOP ? SALE ? GOODS )) (5 分) 2 .解: SELECT 语句如下: SELECT A.S# , SNAME FROM SHOP A, SALE B, GOODS C WHERE A.S#=B.S# AND B.G#=C.G# AND GNAME= ' 冰箱';(5 分) 3 .解: DELETE FROM SALE WHERE S# IN( SELECT S# FROM SHOP WHERE SNAME= ' 开开商店') AND G# IN( SELECT G# FROM GOODS WHERE PRICE>1000 ); (5 分) 4 .解: 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 应在分组子句中出现)(5分) 评分标准:共 30 分。每小题按照叙述定义的准确和全面程度酌情给分。