数据库习题解答newPPT课件

.,1,【习题12】(P38)学校中有若干系，每个系有若干班级和教研室，每个教研室有若干教员，其中有的教授和副教授每人各带若干研究生；每个班有若干学生，每个学生选修若干课程，每门课可由若干学生选修。请用E一R图画出此学校的概念模型。,.,2,.,3,P74习题5设有一个SPJ数据库，包括S，P，J，SPJ四个关系模式：1）求供应工程J1零件的供应商号码SNO：Sno(Sno=J1(SPJ)2）求供应工程J1零件P1的供应商号码SNO：Sno(Sno=J1Pno=P1(SPJ)3）求供应工程J1零件为红色的供应商号码SNO：Sno(Pno=P1（COLOR=红（P）SPJ）)4）求没有使用天津供应商生产的红色零件的工程号JNO：Jno(SPJ)-JNO（city=天津Color=红（SSPJP）5）求至少用了供应商S1所供应的全部零件的工程号JNO：Jno，Pno(SPJ)Pno（Sno=S1（SPJ）,.,4,除（Division）,给定关系R(X，Y)和S(Y，Z)，其中X，Y，Z为属性组。R中的Y与S中的Y可以有不同的属性名，但必须出自相同的域集。R与S的除运算得到一个新的关系P(X)，P是R中满足下列条件的元组在X属性列上的投影：元组在X上分量值x的象集Yx包含S在Y上投影的集合，记作：RS=trX|trRY(S)YxYx：x在R中的象集，x=trX,.,5,除(续),例6设关系R、S分别为下图的(a)和(b)，RS的结果为图(c),.,6,分析,在关系R中，A可以取四个值a1，a2，a3，a4a1的象集为(b1，c2)，(b2，c3)，(b2，c1)a2的象集为(b3，c7)，(b2，c3)a3的象集为(b4，c6)a4的象集为(b6，c6)S在(B，C)上的投影为(b1，c2)，(b2，c1)，(b2，c3)只有a1的象集包含了S在(B，C)属性组上的投影所以RS=a1,.,7,综合举例,以学生-课程数据库为例(P56)例7查询至少选修1号课程和3号课程的学生号码首先建立一个临时关系K：然后求：Sno,Cno(SC)K,.,8,综合举例(续),例7续Sno,Cno(SC)95001象集1，2，395002象集2，3K=1，3于是：Sno,Cno(SC)K=95001,.,9,P127习题3用sQL语句建立第二章习题5(P74)中的4个表。答：对于S表：S(SNO,SNAME,STATUS,CITY);建S表：CREATETABLES(SnoC(2)UNIQUE，SnameC(6)，StatusC(2)，CityC(4);对于P表：P(PNO,PNAME,COLOR,WEIGHT);建P表：CREATETABLEP(PnoC(2)UNIQUE，PnameC(6)，COLORC(2)，WEIGHTINT);对于J表：J(JNO,JNAME,CITY）;建J表：CREATETABLEJ(JnoC(2)UNlQUE，JNAMEC(8)，CITYC(4);对于SPJ表：SPJ(sNo,PNo,JNo,QTY）;建SPJ表：SPJ(SNO,PNO,JNO,QTY)CREATETABLESPJ(SnoC(2)，PnoC(2)，JNOC(2)，QTYINT);,.,10,【P127】4.针对上题中建立的4个表试用SQL语言完成第二章习题5中的查询。(l）求供应工程Jl零件的供应商号码SNO;SELECTDISTINCTSNOFROMSPJWHEREJNO=J1;(2）求供应工程Jl零件Pl的供应商号码SNO;SELECTDISTINCTSNOFROMSPJWHEREJNO=J1ANDPNO=P1;(3）求供应工程Jl零件为红色的供应商号码SNO;SELECTSNOFROMSPJ,PWHEREJNO=J1ANDSPJ.PNO=P.PNOANDCOLOR=红;,.,11,(4)求没有使用天津供应商生产的红色零件的工程号JNO;SELECTDISTINCTJNOFROMSPJWHEREJNONOTIN(SELECTJNOFROMSPJ,P,SWHERES.CITY=天津ANDCOLOR=红ANDS.SNO=SPJ.SNOANDP.PNO=SPJ.PNO);,.,12,(5)求至少用了供应商S1所供应的全部零件的工程号JNO;,p:供应商S1供应了零件yq:工程x选用了零件y找这样的x，使得对于所有的零件y,p-q都成立等价转换为：不存在零件y，供应商S1供应了y，而工程x没有选用ySELECTDISTINCTJNOFROMSPJSPJXWHERENOTEXISTS(SELECT*FROMSPJSPJYWHERESPJY.SNO=S1ANDNOTEXISTS(SELECT*FROMSPJSPJZWHERESPJZ.SNO=SPJX.SNOANDSPJZ.PNO=SPJY.PNO);,.,13,5.(1)找出所有供应商的姓名和所在城市。SELECTSNAME,CITYFROMS;(2)找出所有零件的名称、颜色、重量。SELECTPNAME,COLOR,WEIGHTFROMP;(3)找出使用供应商S1所供应零件的工程号码。SELECTDISTJNOFROMSPJWHERESNO=S1;(4)找出工程项目J2使用的各种零件的名称及其数量。SELECTPNAME,QTYFROMSPJ,PWHEREP.PNO=SPJ.PNOANDSPJ.JNO=J2;,.,14,(5)找出上海厂商供应的所有零件号码。SELECTPNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDCITY=上海;(6)找出使用上海产的零件的工程名称。SELECTJNAMEFROMSPJ,S,JWHERES.SNO=SPJ.SNOANDS.CITY=上海ANDJ.JNO=SPJ.JNO;,.,15,(7)找出没有使用天津产的零件的工程号码。SELECTDISTINCTJNOFROMSPJWHEREJNONOTIN(SELECTDISTINCTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY=天津);,.,16,(8)把全部红色零件的颜色改成蓝色。UPDATEPSETCOLOR=蓝WHERECOLOR=红;(9)由S5供给J4的零件P6改为由S3供应。UPDATESPJSETSNO=S3WHERESNO=S5ANDJNO=J4ANDPNO=P6;,.,17,(10)从供应商关系中删除供应商号是S2的记录，并从供应情况关系中删除相应的记录。A、DELETEFROMSPJWHERESNO=S2;B、DELETEFROMSWHERESNO=S2;(11)请将(S2，J6，P4，200)插入供应情况关系。INSERTINTOSPJVALUES（S2，J6，P4，200）;,.,18,习题解答,请用SQL的GRANT和REVOKE语句(加上视图机制)完成以下授权定义或存取控制功能:(d)用户刘星对职工表有SELECT权力，对工资字段具有更新权力:GRANTSELECT,UPDATE(工资)ON职工TO刘星;,.,19,(e)用户张新具有修改这两个表的结构的权力。GRANTALTERTABLEON职工,部门TO张新;,.,20,(f)用户周平具有对两个表所有权力（读，插，改，删数据），并具有给其他用户授权的权力GRANTALLPRIVILIGESON职工,部门TO周平WITHGRANTOPTION;,.,21,(g)用户杨兰具有从每个部门职工中SELECT最高工资、最低工资、平均工资的权力，他不能查看每个人的工资。CREATEVIEW部门工资ASSELECT部门.名称,MAX(工资),MIN(工资),AVG(工资)FROM职工,部门WHERE职工.部门号=部门.部门号GROUPBY职工.部门号GRANTSELECTON部门工资TO杨兰;,.,22,假设有下面两个关系模式：职工（职工号，姓名，年龄，职务，工资，部门号），其中职工号为主码；部门（部门号，名称，经理名，电话），其中部门号为主码。用SQL语言定义这两个关系模式，要求在模式中完成以下完整性约束条件的定义：定义每个模式的主码；定义参照完整性；定义职工年龄不得超过60岁。CREATETABLEDEPT(DeptnoNUMBER(2),DeptnameVARCHAR(10),ManagerVARCHAR(10),PhoneNumberChar(12)CONSTRAINTPK_SCPRIMARYKEY(Deptno);,.,23,CREATETA