数据库作业夏磊.docx

数据库作业夏磊.docx

《数据库作业夏磊.docx》由会员分享，可在线阅读，更多相关《数据库作业夏磊.docx（14页珍藏版）》请在冰豆网上搜索。

数据库作业夏磊数据库作业夏磊数据库作业指导教师：

陈立军老师2010级秋季班学生：

夏磊学生证号：

S1048035练习一：

ER设计题目一：

1.列举聚集、弱实体、细化/泛化的实用例子，记得不要同讲义上的相同聚集：

客户签订合同与采购产品之间是聚集关系弱实体：

下图中教科书属于弱实体细化/泛化：

教师与（幼师、教授）属于细化/泛化关系2.已知有如下关系模式：

E1（a1,a2,a3），E2（a3,a4），E3（a5,a6），E4（a3,a5,a7），其中带下划线的属性标识为所在关系模式的主码。

试画出相应的E-R图，使得可以从该E-R图推导出上述关系模式。

E-R图如下：

题目二：

考虑设计一个关系数据库，它要存储以下信息，请给出其ER图：

教师有教工号、教工名、职称；项目有项目号、项目名称、项目类型、起始年份、截至时间、资助额；学生有学号、学生名、年龄、学位。

学生分为本科生和研究生，老师按职称可以分为讲师、副教授、教授，副教授以上职称的可以作为研究生的导师。

一个教工可以负责多个项目；每个项目只能有一个负责人；一个老师可以参与多个项目；一个本科生只能参与一个项目，一个研究生学生可以参与多个项目；一个项目可以有多个学生和老师参与。

E-R图如下：

练习二：

关系代数题目一：

S（SNO,SNAME,STATUS,CITY）P（PNO,PNAME,COLOR,WEIGHT,CITY）J（JNO,JNAME,CITY）SPJ（SNO,PNO,JNO,QTY）S表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在城市；P表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的城市；J表示工程，各属性依次为工程号，工程名，工程所在城市；SPJ表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量。

基于以上SPJ关系模式用关系代数表达查询：

1.求向北京的工程供应了红色零件的供应商姓名。

S.SNAME（SP.COLOR=红色（P）J.CITY=北京（J）SPJ）2.求只向北京的工程供应零件的供应商姓名。

S.SNAME（S（X.SNO（J.CITY=北京（J）X（SPJ）Y.SNO（J.CITY北京（J）Y（SPJ）3.求至少供应了两种不同零件的供应商姓名。

S.SNAME（S（X.SNO=Y.SNOX.PNOY.PNO（X（SPJ）Y（SPJ）4.求只供应了两种不同零件的供应商姓名。

S.SNAME（S（M.SNO（M.SNO=N.SNOM.PNON.PNO（M（SPJ）N（SPJ）X.SNO（X.SNO=Y.SNOX.SNO=Z.SNOX.PNOY.PNOX.PNOZ.PNOY.PNOZ.PNO（X（SPJ）Y（SPJ）Z（SPJ）（说明：

只供应两种=至少供应了两种零件供应商-至少供应了三种零件供应商）5.求没有供应任何零件的供应商姓名。

S.SNAME（S（S.SNOSSPJ.SNOSPJ）6.求供应了所有零件的供应商姓名。

S.SNAME（S（SPJ.SNO,SPJ.PNOSPJP.PNOP）或者S.SNAME（S（SNOSPJSNO（SNOSPJPNOPSNO,PNOSPJ）7.求供应了所有红色零件的供应商姓名。

S.SNAME（S（SPJ.SNO,SPJ.PNOSPJP.PNO（P.COLOR=红色P）8.求没有供应相同零件的供应商的号码。

SPJ.SNOSPJX.SNO（X.PNO=Y.PNOX.SNOY.SNO（X（SNO,PNOSPJ）Y（SNO,PNOSPJ）9.求供应了s1号供应商所供应的所有零件的供应商的供应商号。

SNO,PNOSPJPNO（SNO=s1SPJ）10.求和s1号供应商所供应的零件完全相同的供应商的供应商号。

SNOSPJSNO（SNOSPJPNO（SNO=s1SPJ）SNO,PNOSPJ）SNO（SNO,PNOSPJSNOSPJPNO（SNO=s1SPJ）（说明：

从所有提供零件的供应商中少于指定数目的多余指定数目的）题目二：

对于关系R（A,B,C），用关系代数来检验A是否取值唯一。

关系R并且R中元组a,b,c是唯一的，则：

X.A=Y.A（X.BY.BX.CY.C）（XRYR）结果如果恒定为，说明A的取值是唯一的练习三：

基本SQLS（SNO,SNAME,STATUS,CITY）P（PNO,PNAME,COLOR,WEIGHT，（PRICE）,CITY）J（JNO,JNAME,CITY）SPJ（SNO,PNO,JNO,QTY）S表示供应商，各属性依次为供应商号，供应商名，供应商状态值，供应商所在城市；P表示零件，各属性依次为零件号，零件名，零件颜色，零件重量，零件存放的城市；J表示工程，各属性依次为工程号，工程名，工程所在城市；SPJ表示供货关系，各属性依次为供应商号，零件号，工程号，供货数量。

1.求没有供应p1和p2两种零件的供应商姓名。

selectSNAMEfromSwherenotexists（selectSPJ.SNOfromSPJwhereS.SNO=SPJ.SNOand（PNO=p1orPNO=p2）2.列出所有供应商的信息，包括供应商姓名、所供应的零件名（没有供应零件的供应商也要列出，最后结果中不要出现重复元组）selectdistinctSNAME,T1.PNAMEfromSrightouterjoin（selectSPJ.SNO,P.PNAMEfromSPJ,PwhereSPJ.PNO=P.PNO）asT1onS.SNO=T1.SNO3.求只向与自己位于不同城市的工程供应零件的供应商姓名。

selectdistinctT1.SNO,T1.SNAMEfromST1,SPJT2,JT3whereT1.SNO=T2.SNOandT2.JNO=T3.JNOandT1.CITY!

=T3.CITYexceptselectdistinctT4.SNO,T4.SNAMEfromST4,SPJT5,JT6whereT4.SNO=T5.SNOandT5.JNO=T6.JNOandT4.CITY=T6.CITY4.求只向与自己位于相同城市的工程供应零件的供应商姓名。

selectdistinctT4.SNO,T4.SNAMEfromST4,SPJT5,JT6whereT4.SNO=T5.SNOandT5.JNO=T6.JNOandT4.CITY=T6.CITYexceptselectdistinctT1.SNO,T1.SNAMEfromST1,SPJT2,JT3whereT1.SNO=T2.SNOandT2.JNO=T3.JNOandT1.CITY!

=T3.CITY5.求供应了所有零件的供应商姓名。

selectS.SNAMEfromSwherenotexists（selectP.PNOfromPwherenotexists（select*fromSPJwhereSPJ.SNO=S.SNOandSPJ.PNO=P.PNO）6.求供应了所有红色零件的供应商姓名。

selectS.SNAMEfromSwherenotexists（selectP.PNOfromPwherenotexists（select*fromSPJwhereSPJ.SNO=S.SNOandSPJ.PNO=P.PNOandP.COLOR=红）7.列出每个城市的工程所使用的零件总的数量。

selectsum（J.QTY）fromSPJ,JwhereSPJ.JNO=J.JNOgroupbyJ.CITY8.按零件数量总和的降序列出每项工程所使用的每种红色零件的总的金额（工程可以向不同的供应商购买同一零件，总金额单价*供货数量）。

（好像没有单价属性）selectJ.JNO,sum（SPJ.QTY）*J.PRICEfromSPJ,JwhereSPJ.JNO=J.JNOandJ.COLOR=红色groupbyJ.JNOorderbysum（SPJ.QTY）9.求供应零件数量最多的供应商姓名。

selectT1.SNAMEfromST1,（selectSPJ.SNOfromSPJgroupbySPJ.SNOhavingsum（SPJ.QTY）=all（selectsum（T1.QTY）fromSPJT1groupbyT1.SNO）T2whereT1.SNO=T2.SNO10.求每个城市中供应零件数量最多的供应商姓名。

selectT3.SNAME,T2.MAXSUMQTYfrom（selectS.SNO,sum（SPJ.QTY）asSUMQTYfromSPJ,SwhereSPJ.SNO=S.SNOgroupbyS.SNO）T1,（selectmax（T4.SUMQTY）ASMAXSUMQTYfrom（selectS.CITY,S.SNO,sum（SPJ.QTY）asSUMQTYfromSPJ,SwhereSPJ.SNO=S.SNOgroupbyS.CITY,S.SNO）T4groupbyT4.CITY）T2,ST3whereT1.SNO=T3.SNOandT2.MAXSUMQTY=T1.SUMQTY（或者使用with子句，Oracle9i版本后支持，sqlserver不支持）withT1（CITY,SNO,SUMQTY）asselectS.CITY,S.SNO,sum（SPJ.QTY）fromSPJ,SwhereSPJ.SNO=S.SNOgroupbyS.CITY,S.SNOwithT2（MAXSUMQTY）asselectmax（T1.SUMQTY）fromT1groupbyT1.CITYselectT3.SNAMEfromT1,T2,ST3whereT1.SNO=T3.SNOandT2.MAXSUMQTY=T1.SUMQTY11.把每个工程作为一个购物篮，它所使用的零件看作商品。

求出现频率最高的零件对。

selectT1.PNO,T2.PNO,count（*）asJCOUNTfromSPJT1,SPJT2whereT1.JNO=T2.JNOandT1.PNO=all（selectcount（*）fromSPJT3,SPJT4whereT3.JNO=T4.JNOandT3.PNO1）

（2）验证AB是否成立（a）关系代数语句：

若AB（R）AB（R）等于R，则AB成立，否则不成立（b）SQL语句：

CreateassertionA2check（notexists（select*fromRR1,R,R2whereR1.A=R2.AandR1.B=R2.Bandnotexists（select*fromRR3,RR4whereR3.A=R1.AandR4.A=R2.AandR3.B=R1.BandR4.B=R2.BandR3.C=R1.CandR4.C=R2.C）练习五：

事务考虑关系Employee（ID,salary）,表示职工的工资号和工资数额，Employee关系中有一个元组（A,10）。

现有如下两个事务T1、T2,T1:

updateEmployeesetsalary=salary*2whereID=AupdateEmployeesetsalary=salary*3whereID=AT2:

selectsalaryassal1fromEmployee;selectsalaryassal2fromEmployee;试给出T1和T2运行在不同隔离性级别下时，所有可能的（sal1,sal2）值的情况。

另外如果将T1的第一行改为insertintoEmployeevalues（B,50）,此时的结果又会是什么？

解答：

所有可能共6种情况，设事务的各个操作为T1（t11,t12）,T2（t21,t22）操作序列及对应的（sal1,sal2）如下：

1t11,t12,t21,t22:

（60,60）2t11,t21,t12,t22:

（20,60）3t11,t21,t22,t12:

（20,20）4t21,t11,t12,t22:

（10,60）5t21,t11,t22,t12:

（10,20）6t21,t22,t11,t12:

（10,10）T1T2readuncommittedreadcommittedrepeatablereadserializablereaduncommittedreadcommittedrepeatablereadserializable如果将T1的第一行改为insertintoEmployeevalues（B,50），所有可能共6种情况，设事务的各个操作为T1（t31,t32）,T2（t41,t42）操作序列及对应的（sal1,sal2）如下（XXX-YYY表示两行的数据）：

1t31,t32,t41,t42:

（30-50,30-50）2t31,t41,t32,t42:

（10-50,30-50）3t31,t41,t42,t32:

（10-50,10-50）4t41,t31,t32,t42:

（10,30-50）5t41,t31,t42,t32:

（10,10-50）6t41,t42,t31,t32:

（10,10）T1T2Readuncommittedreadcommittedrepeatablereadserializablereaduncommittedreadcommittedrepeatablereadserializable