数据库系统概论第五版课后习题答案王珊版

12SQL345.ARA()FRSKs(RS)RF(F)SF6SPJSPJSPJ1J1SNOSno(Jno=J1SPJ)2J1P1SNOSno(Jno=J1Pno=P1(SPJ))3J1SNOSno(Sno,,PnoJno=J1(SPJ))PnoCOLOR=P))4JNOJno(SPJ)-JNOcity=Color=SSPJP5S1JNOJnoPno(SPJ)PnoSno=S1SPJ7.=RSAB8??535SQL1sQL(lsQLDDLDMLDCL(2sQLsQL(3sQL(4sQL(52.3(1)select*fromSwhereA='10';(2)selectA,BfromS;(3)selectA,B,S.C,S.D,E,FfromS,TwhereS.C=T.CandS.D=T.D;(4)select*fromS,TwhereS.C=T.C;(5)select*fromS,TwhereS.AT.E;(6)selectS.C,S.D,T.*fromS,T;4sQL64SS(SNO,SNAME,STATUS,CITY);SCREATETABLES(SnoC(2)UNIQUESnameC(6)StatusC(2)CityC(4));PP(PNO,PNAME,COLOR,WEIGHT);PCREATETABLEP(PnoC(2)UNIQUEPnameC(6)COLORC(2)WEIGHTINT);JJ(JNO,JNAME,CITY;JCREATETABLEJ(JnoC(2)UNlQUEJNAMEC(8)CITYC(4))sPJsPJ(sNo,PNo,JNo,QTY;SPJSPJ(SNO,PNO,JNO,QTY)CREATETABLESPJ(SnoC(2)PnoC(2)JNOC(2)QTYINT))4sQL6(lJlSNO;SELECTDISTSNOFROMSPJWHEREJNO=J1(2JlPlSNO;SELECTDISTSNOFROMSPJWHEREJNO='J1'ANDPNO='P1'(3JlSNO;SELECTSNOFROMSPJ,PWHEREJNO='J1'ANDSPJ.PNO=P.PNOANDCOLOR=''(4JNO;SELECTDISTJNOFROMSPJWHEREJNONOTIN(SELEJNOFROMSPJ,P,SWHERES.CITY=''ANDCOLOR=''ANDS.SNO=SPJ.SNOANDP.PNO=SPJ.PNO)(5SlJNO;VFPAS1SELECTDISTPNOFROMSPJWHERESNO='S1'P1P2BP1P2SELECTJNOFROMSPJWHEREPNO='P1'ANDJNOIN(SELECTJNOFROMSPJWHEREPNO='P2')5SQL(1)SELECTSNAME,CITYFROMS(2)SELECTPNAME,COLOR,WEIGHTFROMP(3)S1SELECTDISTJNOFROMSPJWHERESNO='S1'(4)J2SELECTPNAME,QTYFROMSPJ,PWHEREP.PNO=SPJ.PNOANDSPJ.JNO='J2'(5)SELECTPNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDCITY=''(6)SELECTJNAMEFROMSPJ,S,JWHERES.SNO=SPJ.SNOANDS.CITY=''ANDJ.JNO=SPJ.JNO(7):SELECTDISPJNOFROMSPJWHEREJNONOTIN(SELECTDISTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY='')JNO.:SELECTDISTJNOFROMSPJ,SWHERES.SNO=SPJ.SNOANDS.CITY''JNO(8)UPDATEPSETCOLOR=''WHERECOLOR=''(9)S5J4P6S3UPDATESPJSETSNO='S3'WHERESNO='S5'ANDJNO='J4'ANDPNO='P6'(10)S2ADELETEFROMSWHERESNO=S2BDELETEFROMSPJWHERESNO=S2(11)(S2J6P4200)INSERTINTOSPJVALUESS2J6P42006sQL7(l(2(3(48.9(SNO)(PNO)(QTY)CREATEVIEWVSPASSELECTSNO,SPJ.PNO,QTYFROMSPJ,JWHERESPJ.JNO=J.JNOANDJ.JNAME=''VSP(1)SELECTDISTPNO,QTYFROMVSP(2)S1SELECTDIST*FROMVSPWHERESNO='S1'4124(l(2CZ(DAC),BlMAC(3(4DBA(556.(1)GRANTALLPRIVILEGESONStudent,ClassTOU1WITHGRANTOPTION;(2)GRANTSELECT,UPDATE(),DELETEONStudentTOU2(3)GRANTSELECTONClassTOPUBLIC(4)GRANTSELECT,UPDATEONStudentTOR1(5)GRANTR1TOU1WITHADMINOPTION7.SQLSQLGRANTREVOKEGRANTSELECT,INSERTONStudentTOWITHGRANTOPTION;StudentSELECTINSERTWITHGRANTOPTIONREVOKEINSERTONStudentFROMCASCADE;StudentINSERTCASCADEStudentINSERT7SQLGRANTREVOKE():(aSELECTGRANTSELECTON,TO(bINSERTDELETEGRANTINSERT,DELETEON,TO(c)SELECTGRANTSELECTONWHENUSER()=NAMETOALL;(dSELECTGRANTSELECT,UPDATE()ONTO(eGRANTALTERTABLEON,TO;(fGRANTALLPRIVILIGESON,TOWITHGRANTOPTION;(gSELECTCREATEVIEWASSELECT.,MAX(),MIN(),AVG()FROM,WHERE.=.GROUPBY.GRANTSELECTONTO;881---7(1)REVOKESELECTONFROM(2)REVOKEINSERT,DELETEONFROM(3)REOVKESELECTONWHENUSER()=NAMEFROMALI;(4)REVOKESELECT,UPDATEONFROM(5)REVOKEALTERTABLEONFROM(6)REVOKEALLPRIVILIGESONFROM(7)REVOKESELECTONFROMDROPVIEW9MACDBMSDBMSLabelTopSecretSecret(ConfidentialPubliCClearanCevel)ClassificationLevel11DBMSDBA51:2:Garba:eInGarba:eout3(l(2(3(4(5(l(2(3(l(24.DBMS:DBMS(l(2(35.RDBMSRDBMs(l(2llCASCADES);2RESTRICTED);3NULLIFIES(3l2(4UPDATE6sQL60CREATETABLEDEPT(DeptnoNUMBER(2),DeptnameVARCHAR(10),ManagerVARCHAR(10),PhoneNumberChar(12)CONSTRAINTPK_SCRIMARYKEY(Deptno));CREATETABLEEMP(EmpnoNUMBER(4),EnameVARCHAR(10),AgeNUMBER(2),CONSTRAINTC1CHECK(Aage=60),JobVARCHAR(9),SalNUMBER(7,2),DeptnoNUMBER(2),CONSTRAINTFK_DEPTNOFOREIGNKEY(Deptno)REFFERENCESDEPT(Deptno));7:61Allkey1NFZNF3NFBcNF4NF1R(U)UXYUR(U)rrXYXYYXXYXYXYYXXYXYYXXYXYX(Determinant)XYYXXYYXXY2R(U)XYXXXYYXXYYXYX3RR(1NF)4R1NFR2NF1NF2NF5:RUFXYZ(ZY)XYYXYZRUF3NF6:RUF1NFXYYX,XRUFBCNF7:RUF1NFRXY(YXZ=U-X-Y)XRUF4NF2?(1)S(SnoSnameSbirthDeptClassRno)C(ClassPnameDeptCnumCyear)D(DeptDnoOfficeDnum)M(MnameMyearMaddrMnum)(2)AS(SnoSnameSbirthDeptClassRno):SnoSnameSnoSbirthSnoClassClassDeptDEPTRnoSnoDeptDeptSnoDeptRnoSnoRnoClassDeptDeptClassDeptRnoClassRnoSnoClassClassSnoClassDeptSnoDeptBC(ClassPnameDeptCnumCyear):ClassPnameClassCnumClassCyearPnameDept.ClassPnamePnameClassPnameDeptC1assDeptCD(DeptDnoOfficeDnum)DeptDnoDnoDeptDnoOfficeDnoDnumDeptOfficeDeptDnumDM(MnameMyearMaddrMnum)MnameMyearMnameMaddrMnameMnum(3)ASSnoDeptClassBCClassDeptCDDeptDnoDMMname7??13NF2BCNF.:BCNFXY,YXBCNFRR(A1A2)A1A2AA1A2A2A1RA1RBCNFBA1A2A2A1RA2RBCNFRBCNFCR(A1A2)A1A2A2A11(l(2(3(4(5(62(l(2DBMS(3DBMS(4(5DBMS(63(lDBMSER(2ER(Vi(3DBMS5(l(2(3(4l2347ER8ER:9ERDBMS:(l(2DBMS(311BCNF