数据库原理与应用

DatabasePrincipleandApplication,WangYangting,1BasicConceptofDatabasePrinciple,Database数据库是存储在外存储器上逻辑相关的数据的集合，并按一定的方式进行组织和管理。Adatabaseisacollectionofdatastoredinstandardizedformat,designedtobesharedbymultipleusers.BasicPropertyofDatabasePermanenceArrangedShared,1BasicConceptofDatabasePrinciple,DatabaseTechnologyHowtoarrangeandmanagedataindatabase(数据库技术是研究如何科学有效地组织和管理数据库中的数据)ArrangeandsavedataeffectivelyQueryandprocessdataeffectivelyReducedataredundancyEnsuredatasecurityImplementdatashared,1BasicConceptofDatabasePrinciple,DatabaseManagementSystem（DBMS）DBMSissoftwarepackagedesignedtostoreandmanagedatabases.DBMSSubLanguageDataDefineLanguage(数据定义语言)DDLDataManipulateLanguage(数据操作语言)DMLDataControlLanguage(数据控制语言)DCLBasicFunctionsofDBMSDatadefineandarrangeDatamanipulateandretrieveoptimizeDatacontrolandmaintenanceDatamanagementProvidemultidatabaseconnection.,2.HistoryofDatabase,1.Manualdatamanagement（1946tomidof1950）(1)Nodatasave(2)Nospecialsoftwaretomanagedata(3)Nocomputerizedfile(4)Noindependentdata*数据和程序不能相互独立,2.HistoryofDatabase,4.AdvancedDatabase(1)Distributedatabase(2)ObjectorientedDatabase(3)Intelegentknowledgebase(4)Client/Serverdatabase(5)Paralleldatabase,Databasecan:DataIndependenceDataaccessEffectivelyReducetimeofapplicationdevelopmentDataconsistencyandsecurityUnifieddatamanagementDataconcurrencyaccess,2.HistoryofDatabase,3.DataModel,Datamodel定义：实现数据抽象的主要工具。它决定了数据库系统的结构、数据定义语言和数据操纵语言、数据库设计方法、数据库管理系统软件的设计与实现。AdatamodelisacollectionofconceptsanddefinitionsfordescribingdataCategoryConceptualModel：EntityrelationshipmodelSemanticnetworkmodelDataModel:HierarchicalmodelNetworkmodelRelationshipmodelThreefactorsDatastructureDatamanipulateDataintegrality,3.1ERDataModel,Entity：Definition：somethingintherealworldthatyouwishtodescribeortrackExamples：student,course,blackboardAttribute：Definition：acharacteristicordescriptorofanentityExamples:Name,sex,age(employee)(spouse?)RelationshipDefinition：relationshipbetweenormoreentities.Examples：learn(studentandcourse),work(employeeandcompany)ERDiagramDiscusslater,3.2Hierarchicalmodel,ManythingsinrealworldareorganizedinhierarchyHierarchicalmodelmanagestodescriberealworldintreestructureNode:entityPCR:ParentChildRelationship.Product：IBMIMS(InformationManagementSystem),3.2HierarchicalModel,Examples:,3.3NetworkModel,Networkmodelisnamedfromthenetworkofconnectionsbetweendataelements.GraphicstructureTosolvetheproblemofHierarchyExamples：RoadnetworkProduct：DBTG,3.4RelationalDataModel,Thebasicdatastructureis“table”.Thethingsandtherelationshipsbetweentheminrealworldareallexpressedastables,asitcanberesearchedinstrictmathematicmethods.Itsfeatures:Basedonsettheory,highabstractlevelShieldalllowerdetails,simpleandclear,easytounderstandCanestablishnewalgebrasystemrelationalalgebraNonprocedurequerylanguageSQLSoftwarelinktheessentialdifferencewithformerdatamodels,3.5DataSchema,DataschemaAschemaisadescriptionofaparticularcollectionofdata,usingagivendatamodel.数据库系统的体系结构是数据库系统的总体框架。尽管实际的数据库系统软件产品多种多样，支持不同的数据模型，使用不同的数据库语言，建立在不同的操作系统之上，数据的存储结构也不尽相同，但是绝大多数数据库系统在总体框架上都具有三级模式的结构特征。数据库系统的三级模式结构是：外模式、模式和内模式。Externalschema,Conceptualschema,Physicalschema,3.5DataSchema,3.5DataSchema,ExternalschemaDescribehowuserseethedataUserviewsNoteSubsetofconceptualschemaExistmanyexternalschemainsameDBExamplesViewUserresultdatafromdatabase,3.5DataSchema,Conceptualschema：SchemaorLogicschemaDefinelogicstructureNoteSchemaisuniqueContentofviewExamplesStudentCourseElection,3.5DataSchema,PhysicalschemaStoredschema,Innerschema描述数据库物理结构和存储方式包括文件结构、索引、存取路径和方式NotePhysicalschemaisunique内模式是文件级操作ExampleIndexonfirstcolumn,4.LeadingCommercialDatabase,OracleInformixSybaseSQLServerAccessXBase,1.RationalDataModel,Examples:Relation:studentRow,Column,Cell,1.RationalDataModel,AttributesThefeaturesofanentityinrealworldareexpressedasattributesinrelationalmodelExample:Entity:StudentAttributes:name,sid,age,gender,birthday,etcDomainThescopeofanattributeiscalleditsdomainAtomicdataorNullExamples:Age:0-200,1.RationalDataModel,关系：把关系看成一个集合，转换成数学的集合以及集合的运算问题。笛卡尔定义：笛卡尔积：设D1，D2，Dn为n个集合，称D1D2Dn(d1，d2，dn)|diDi，(i=1,2,n)为集合的笛卡儿积。Di(i=1,2,n)即为以上定义的域；di为该域的一个值；其中的每一个元素(d1,d2,dn)即为以上定义的元组；n表示参与笛卡儿积的域的个数，称作度。同时它也表示了每一个元组中分量的个数，n=1,称为一元组，n=2，称为二元组，n=p称为p元组,1.RationalDataModel,Example：D1=MAN=Jack,John,WhiteD2=WOMAN=Susan,AnnaD3=CHILD=Jim,Ali,JoannaQuestion?Supposewemeettheminstreet,aman,awomanandachildfromonefamilycanformagroup.Letusguesshowmanygroups?,1.RationalDataModel,Probableresult：D1D2D3Result=Jack,Susan,Jim，Jack,Susan,Ali,Jack,Susan,Joanna，Jack,Anna,Jim,Jack,Anna,Ali，Jack,Anna,Joanna,John,Susan,Jim，John,Susan,Ali,John,Susan,Joanna，John,Anna,Jim,John,Anna,Ali，John,Anna,Joanna,White,Susan,Jim，White,Susan,Ali,White,Susan,Joanna，White,Anna,Jim,White,Anna,Ali，White,Anna,Joanna,1.RationalDataModel,Conceptiondomain（set）：D1，D2，D3Descartesproduct：Result=D1D2D3Johniselementof(Domain)setManTuple:John,Susan,Jimn=3,degreeis3,1.RationalDataModel,回顾一下-笛卡尔定义：笛卡尔积：设D1，D2，Dn为n个集合，称D1D2Dn(d1，d2，dn)|diDi，(i=1,2,n)为集合的笛卡儿积。Di(i=1,2,n)即为以上定义的域；di为该域的一个值；其中的每一个元素(d1,d2,dn)即为以上定义的元组；n表示参与笛卡儿积的域的个数，称作度。同时它也表示了每一个元组中分量的个数，n=1,称为一元组，n=2，称为二元组，n=p称为p元组,1.RationalDataModel,Relation的定义D1D2Dn的子集叫作在域D1，D2，Dn上的关系，用R(D1，D2，Dn)表示。这里R表示关系的名字，n是关系的目或度（Degree）。说明D1D2Dn是笛卡尔乘积关系R是所有元组的一个子集,1.RationalDataModel,Examples：Family=Jack,Susan,Jim，White,Anna,Joanna，White,Anna,Ali,1.RationalDataModel,Expressastable:,1.RationalDataModel,RelationSupposearelationRwithAttributeA1,A2,An,thecorrespondingdomainsareD1,D2,Dn,thenRcanbeexpressedas:R=(A1,A2,An)NoteR=(A1,A2,An)iscalledschemaofRnisnumberofattributes,calleddegreeAiisattributename,1.RationalDataModel,TupleAninstance(value)ofRcanbeexpressedasr,itisasetofn-tuple.R=t1,t2,tnT=viDi1inRelationandtableRelationisalsocalledtableAttributecalledcolumnTupleiscalledrow,1.RationalDataModel,Examples:Relation:studentAttribute:name,age,gender,gradetuple,2.RelationalAlgebra,关系数据库常用的语言关系代数用对关系的运算来表达查询的语言，称为代数式语言，简称关系代数关系演算用查询所得到的元组所应满足的谓词来表达查询的语言称为演算式语言，简称关系演算,2.RelationalAlgebra,定义：关系代数是关系运算的总和关系运算分为两类：一类是传统的集合运算：主要是对行进行运算。另一类是专门的关系运算：主要是对列进行运算。,2.RelationalAlgebra,传统的集合运算如并(Union)、交(Intersection)、差(Difference)这类运算将关系看成元组的集合，运算是从行的角度进行。专门的关系运算，如选择(Select)、投影(Projection)、连接(Jion)、除。这些运算不仅涉及行而且也涉及到列。,2.RelationalAlgebra,1.并（Union）运算RS=t|tRtS2.差（Difference）运算RS=t|tR!(tS)3.交（Intersection）运算RS=t|tRtS,关系运算（续）,并运算举例,2.RelationalAlgebra,差运算举例,2.RelationalAlgebra,交运算举例,2.RelationalAlgebra,专门关系运算选择（Selection）运算按照一定的条件在给定的关系中选取若个元组投影（Projection）运算选取关系特定的列投影后不仅某些列消失了，某些行也可能消失连接（Join）运算从两个关系中选取符合条件的元组可以有条件连接和自然连接,2.RelationalAlgebra,选择举例：所在系为CS,2.RelationalAlgebra,投影举例：在SName、SD上的投影,关系运算（续）,连接举例：连接条件C3,4GroupClause,SelectstatementSelectFrom.WhereGroupByHavingOrderBy,5Update,UpdatestatementUpdateDeleteInsert,5UpdateStatement,UpdateUpdateSet=(Expression)，.WhereExampleUpdateSSetSA=SA+1ExerciseModifystudentnametojohn,whichstudentcodeisS12,5UpdateStatement,DeletestatementDeleteFromWhereExamplesDeleteFromSWhereSNO=S124ExerciseDeleteallcomputerdepartmentstudentinformationDeleteallelectiveinformation,5UpdateStatement,InsertstatementINSERTINTO(atttribure1，attribute2，.)VALUES(value1，value2，.)ExamplesINSERTINTOSC(SNO,CNO,G)VALUES(S15,C10,A)ExerciseInsertastudentinformation：S23、李四、22、CS、HNJohnelectcourseC+,1TableDesign,DefineFieldValidationRulesSelectthefieldnameintheupperpaneoftheDesignwindowclickValidationRuleintheFieldPropertiespane.Typetheexpressionyouwantinthepropertybox.ThenmovetotheValidationTextpropertyboxandenterthemessageyouwanttodisplaywhentheruleisbroken.,1TableDesign,RequireanEntryandPreventDuplicatesFieldRequired-whichissettoYesorNo.YoucanusetheIndexedpropertytohelppreventduplicatevalues.,1TableDesign,HandleBlankFieldsTheproblemofblankfieldTheRequiredpropertyTheAllowZeroLengthproperty,whensettoYes,permitszero-lengthstrings,1TableDesign,AssignaDefaultValueOneofyourfieldsusuallyhasthesamevalueAdefaultvaluecanbeassignedtoanytypeoffieldexceptanAutoNumberorOLEObject.,1TableDesign,CopyanExistingTableStructure1.SelecttheexistingtablenameintheDatabasewindow.2.ClickCopyonthetoolbarorchooseEdit|Copy.4.ClickPasteorchooseEdit|PastetoopenthePasteTableAsdialogbox.5.EnteranameforthenewtableandchooseStructureOnly,thenclickOK.,2Relatetables,DefineaRelationshipUsetheRelationshipsWindow,2Relatetables,DefineaRelationshipDrawtheRelationshipLine,2Relatetables,DefineaRelationshipRelatetoTwoorMoreForeignKeys,2Relatetables,DefineaRelationshipEnforceReferentialIntegrity,2Relatetables,Whatisjoin?,2Relatetables,Option1includesonlyrecordswherebothparentandchildhavethesamevaluesinthelinkingfields(noorphansandnochildlessparents).Thisiscalledaninnerjoinoranequi-join,anditsthedefaultjointype.,2Relatetables,Option2includesalltherecordsfromthetableontheleft,evenifnocorrespondingvaluesareintheotherandonlythematchingrecordsfromthetableontheright.Thisiscalledaleftouterjoin.,2Relatetables,Option3istheoppositeofoption2andincludesalltherecordsfromthetableontherightandonlymatchingrecordsfromthelefttable.Thisiscalledarightouterjoin.,2Relatetables,ModifyorDeleteaRelationshipPrinttheRelationships,3EnterandEditData,EnterNewData,3EnterandEditData,CopyandMoveDataInsertPicturesAddCustomInputMasksCreateLookupFields,3EnterandEditData,ChangetheDatasheetAppearanceMoveandResizeColumnsandRowsRearrangetheColumnsChangetheColumnWidthChangetheRowHeightFreezeandHideColumnsChangetheFontChangeGridLinesandCellAppearance,3EnterandEditData,ChangeTableDefinitioninDatasheetViewInsert/DeleteaSubdatasheetInsert/DeleteaColumn,3EnterandEditData,InputRecorddataEditRecorddataLocateRecordsDeleteData,1Whatisareport,ReportispresentationofdataPartofManagementSystemCanbeprintedAccessprovideareportobject,2AutoReport,AutoReportDemonstrate,3Createreport,ClickNewontheReportspageoftheDatabasewindow.ChooseInsert|Reportfromanyobjectpage.ClicktheNewObjectbuttonandchooseReportfromthelist.ClickTablesorQueriesunderObjectsandselectatableorqueryname.,3.1UseReportWizard,DatasourceTableandquerySingledatasourceMultipledatasourceGroupinglevelSortorderLayoutReportwindowDemonstrate,3.2ChangeReportDesign,DesignviewPreviewandprintReportViewmultiplepagesReportsectionPageheadersectionsPagefootersectionsReportheadersectionsReportfootersectionsSetreportandsectionproperties,3.2ChangeReportDesign,ChangeReportStyleAddPageNumbersandData/TimecontrolsAddapagenumberChangepagenumberformatAddaDate/TimecontrolSavethereportdesign,3.2ChangeReportDesign,AddgroupsectionAddacountsummaryAddarunningtotalAddasubreport,3.3Printmaillabels,UselabelwizardDemonstrate,4Createchartandgraphs,UseaccesschartwizardChoosethefieldsyouwanttouseinthechart,ClickNext.SelectthetypeofchartyouwanttocreateandclickNext.Youcanclickeachtypeofchartandreadadescriptionintherightpane.,4Createchartandgraphs,4Createchartandgraphs,4Createchartandgraphs,ChangechartlayoutDragtheSumOfYearlabeltotheareabelowthecharttoreplaceit.ThelabelchangestoYearbecauseitrepresentsacategoryontheXaxisratherthananumericvalueontheYaxis.DragtheNumberofCrimesfieldbuttontotheDataareabelowthePreviewChart.ThelabelchangestoSumOfNumberofCrimes.,4Createchartandgraphs,1IntroductiontoVBA,WhatisVBAVBA:VisualBasicforApplicationsVBAisanobjectofAccessdatabaseVBAisthemacroorscriptinglanguageVBAisinterfacelikeAPIVBAishostlanguagesfortheJetDBMSofMSAccess,1IntroductiontoVBA(Con.),WhyweneedVBAGraphicinterfaceisveryeasy,butnotpowerfulAccessAccessdatabasebyprogramExamples:Getalistofthefieldsofagiventable,2VBAeditor,WhatisIDEIntegratedDevelopmentEnvironmentAccessIDEDemonstrateDisplayastring“Helloworld”,2VBAeditor(Con.),TheProjectWindow,2VBAeditor(Con.),ThePropertiesWindow,2VBAeditor(Con.),TheCodeWindow,2VBAeditor(Con.),TheObjectandProcedureListboxesTheObjectboxcontainsalistoftheobjectsthatareassociatedwiththecurrentprojectTheProcedureboxcontainsalistofalloftheproceduresassociatedwiththeobjectselectedintheObjectbox.TheprecisecontentsoftheseboxesvarydependingonthetypeofobjectselectedintheProjectExplorer,2VBAeditor(Con.),TheImmediateWindowDemonstrate,2VBAeditor(Con.),Immediatewindowfunctions:First,wecansendoutputtothiswindowusingthecommandDebug.Print.TheothermainfunctionoftheImmediatewindowistoexecutecommands.(*),2VBAeditor(Con.),DockingManyofthewindowsintheIDE(includingtheProject,Properties,andImmediatewindows)canbeinoneoftwostates:DockingFloating,3Debugging,TracingcodeInDebugmenuitem,3Debugging(Con.),ModeRunMode:procedureisrunningBreakMode:runningprocedurestopanerroroccursdeliberatelyenterbreakDesignMode:towritecode,3Debugging(Con.),Anerrormessage,3Debugging(Con.),Error:ErrorBugDefectThedifferenceof3terms,3Debugging(Con.),ErrorstypesbasedonwhentheyoccurDesigntime:occursduringthewritingofcodeCompiletime:IfAccessencountersanerrorwhilecompilingcode,itdisplaysacompile-errormessage.Runtime:Anerrorthatoccurswhileaprogramisrunning,3Debugging(Con.),LogicalErrorsAlogicalerrorcanbedefinedastheproductionofanunexpectedandincorrectresult.Example:computetheaverageofnumbers:Dimx(3)AsIntegerDimAveAsSinglex(0)=1x(1)=3x(2)=8x(3)=5Ave=(x(0)+x(1)+x(2)+x(3)/3MsgBoxAverageis:result=InputBox(“Inputanumberx”)ifx=resultthenMsgBox“youareright”Endsub,3.1Ifthenstatement(Cou.),Subtest()dimresultasinteger,xasintegerx=10;result=InputBox(“Inputanumberx”)ifx=resultthenMsgBox“Youareright”elseMsgBox“Youarewrong”Endsub,3.1Ifthenstatement(Cou.),Subtest()dimresultasinteger,xasintegerx=10;result=InputBox(“Inputanumberx”)ifx=resultthenMsgBox“Youareright”EndifEndsub,3.1Ifthenstatement(Cou.),Subtest()dimresultasinteger,xasintegerx=10;result=InputBox(“Inputanumberx”)ifx=resultthenMsgBox“Youareright”elseMsgBox“Youarewrong”EndifEndsub,3.1Ifthenstatement(Cou.),Subtest()dimresultasinteger,xasintegerx=10;result=InputBox(“Inputanumberx”)ifx=resultthenMsgBox“Youareright”elseifresultxthenMsgBox“bigger”elseMsgBox“smaller”EndifEndsub,3.2Forloopstatement,SubEvenNumber()dimIasintegerforI=2to20step2MsgBox“-”-”NextIEndsub,3.3Doloopstatement,SubAverage()dimIasinteger,avgassingle,xasIntegerx=Input(“Inputanumber:”)dowhilex-1avg=avg+xI=I+1loopMsgBox“theavgis:”Tab(6);B;Tab(12);A+BDebug.PrintC;Tab(6);D;Tab(12);C+DCallLine1Debug.PrintA+C;Tab(6);B+D;Tab(12);A+B+C+DCallLine1EndSubSubLine1()DimIAsIntegerForI=1To15Debug.Print-;NextIDebug.PrintEndSub,4.2Sub(Con.),SubInputData()DimXAsInteger,YAsIntegerX=InputBox(“Datum1):Y=InputBox(“Datum2)CallDataPlus(X,Y)DataSubX,YEndSubSubDataPlus(AAsInteger,BAsInteger)DimSumAsIntegerSum=A+BMsgBoxAData