数据库系统概论复习资料(20161205183025)

1.Data2.Database3.DBMS4.DBSDBA5.6.7.DBMS8.9.10.E-R-11.12.type/value13.14.15.16.17.//1.2.3.34.35.6.7.8.1.DBMS()2.3.4.DBMS5.DBMSSecurityIntegrityConcurrencyRecovery6.1237.8.39.E-R()10.E-R()11.12.1mnnm1nn11mnn11nnm13.DBMS//14.DBA1.(Primarykey)2.()(Foreignkey)3.4.5..6.7.1.()()()+2.3.4.51.2.RUDDOMFRUDUDOMF3.-()+()()4.Rk1nSk2mRSRS={trtstrRtsS}(n+m)nRmSk1k2RS5.()F(R)={ttRF(t)='}RFtRF(t)6.()A(R)={t[A]tR}RARtt[A]A1.SQLSQL2.3.WHEREHAVING1.SQL2.SQLCREATEDROPALTER3.RDBMSB+HASH4.5.===!=!!BETWEENANDNOTBETWEENANDINNOTINLIKENOTLIKEISNULLISNOTNULLANDORNOTCREATESCHEMADROPSCHEMACREATETABLEDROPTABLEALTERTABLECREATEVIEWDROPVIEWCREATEINDEXDROPINDEX1.SQL(DDL)(DML)(DCL)92.SQLCREATETABLE([][[]],[])()PRIMARYKEYREFERENCES()UNIQUENOTNULLPRIMARYKEY()FOREIGNKEYREFERENCES()3.SQLCREATE[UNIQUE][CLUSTER]INDEXON()UNIQUECLUSTERASCDESC/4.SQLSELECT[ALLDISTINCT]FROM[WHERE1][GROUPBY1[HAVING2]][ORDERBY2[ASCDESC]]ALLDISTINCTALLDISTINCT()*1212ASCDESC/5.-P826.SQLINSERTINTO[()]VALUES()INTOVALUES7.insertinto123values123insertinto123Select123FromupdateSet+WheredeleteFromWherecreateviewAsSelectdeleteview[cascade]updateviewSet+WhereSnochar9primarykey--------------Primarykeysnocno--------------1.CREATEROLEGRANT2.(DAC)DBA3.(MAC)DAC()4.()()5.CREATETRIGGER1.2.TCSEC/TDI3.(DAC)(MAC)4.(DAC)SQLGRANTREVOKE5.CREATEUSERCREATESCHEMACREATETABLECRUDDBARESOURCECONNECT6.(MAC)7.MAC8.9.1.DBMS2.SQL1)StudentCourseGRANTALLPRIVILIGESONTABLEStudentCourseTOPUBLIC2)StudentU4GRANTSELECTUPDATE(Sname)ONTABLEStudentTOU43)SCU5GRANTINSERTONTABLESCTOU5WITHGRANTOPTION4)U5SCINSERTREVOKEINSERTONTABLESCFROMU5CASCADE5)R1StudentCREATEROLER1GRANTSELECTUPDATEONTABLEStudentTOR16)StudentAUDITALTERONStudentCreateroleGrantontabletowithgrantoptionRevokeontablefromcascade3.studentSC---------//---//DBMSDBMSDBMS4.CREATETRIGGER{BEFOREAFTER}ONFOREACH{ROWSTATEMENT}[WHEN]ASBEGINENDINSERTDELETEUPDATE[OF]BEFOREAFTERFOREACHROWFOREACHSTATEMENTSQLPL/SQLNEWOLD1.2.3.XYXYYXXY4.XYXYYXXYXYYXXY5.XYXYXX'X'YYX6.7.8.1NF1NF9.2NF1NF2NF10.3NF3NF11.BCNFBCNF12.13.614.DBMS15.E-RDBMS16.17.1.2.3.4.IPO5.6.7.E-R8.HASH9.1.StudentUFU=,,,,FF={(,)}S,SC,,(,)DEPT,2.3.1)2)DBMS3)DBMS4)5)DBMSSQL6)4.5.6.={}7.={:}8.={,,,,,:,,,}9.()()()()()10.DBA1)2)3)4)1.()2.1.2.-3.I/O()CPU()1.RDBMS(),,SQL2.AB()(C)-ABCABCCABCABCACBAAChashhashABhash(B)hashhash(A)AhashB3.1.BEGINTRANSACTIONCOMMITROLLBACK2.()3.4.1.2.15.BEGINTRANSACTIONCOMMITROLLBACK1)2)3)4)16.17.BEGINTRANSACTIONCOMMITROLLBACK18.19.1),2)3),,4),,20.1)(REDO)(Undo)2)(Undo)(UNDO),UNDO3)(Redo)(REDO),REDO21.1)2)22.1),2)ACTIVE-LIST(UNDO-LISTREDO-LIST)ACTIVE-LISTUNDO-LIST,REDO3),Ti,TiUNDO-LISTTj,TjUNDO-LISTREDO-LIST4)UNDO-LISTUNDOREDO-LISTREDO