数据库第一讲introduction

数据库原理王建民清华大学软件学院2014年/春

Lecture1Introduction2Textbooks授课时间 第1周－第16周课程教材

DatabaseSystemConcepts(6thEdition)

高等教育出版社参考教材 数据库系统概论(第3/4版)， 萨师煊、王珊 高等教育出版社3Evaluation另见4TA&Others课程辅导 黄向东纪律要求 不要迟到,不要早退其它信息 清华大学网络学堂教者王建民

62781776/5BasicConceptsData－数据

Database－数据库（DB）DatabaseManagementSystem

－数据库管理系统（DBMS）DatabaseSystem－数据库系统DatabaseApplication－数据库应用6MainContentsWhyuseDatabases?DatabaseSystemsandFileSystemsWhatisaDatabase?HowtoPerformOperationsonaDatabase?WhousesDatabase?PurposesofDatabasesHowtoDeployaDatabase?ThePastandFutureofDatabases7DatabaseManagementSystemCollectionofinterrelateddataandSetofprogramstoaccessthedata

DBMSprovidesanenvironmentthatisbothconvenientandefficienttouse狭义广义SetofprogramsInterrelateddata8DatabaseApplicationsBanking,Airlines，Enterprises……DBMSDBAppDBSysDBData9Intheearlydaysdata-centricapplicationswerebuiltontopoffilesystems10Drawbacksoffilesystems1.DataredundancyandinconsistencyMultiplefileformats,duplicationofinformationindifferentfilesE.g.addressandtelephonenumbersavedin savings-accountrecord checking-accountrecord11Drawbacksoffilesystems(c1)DifficultyinaccessingdataNeedtowriteanewprogramtocarryouteachnewtaskDataisolation—multiplefilesandformatsAutoCAD,2Ddrawingtools dwgfile–binaryfileformat differentversion,differentformatMSWord,docfile12Drawbacksoffilesystems(c2)IntegrityproblemsIntegrityconstraints(e.g.accountbalance>0)epartofprogramcodeHardtoaddnewconstraintsorchangeexistingonesAtomicityofupdatesFailuresmayleavedatabaseinaninconsistentstatewithpartialupdatescarriedoutE.g.transferoffundsfromoneaccounttoanothershouldeithercompleteornothappenatall13Drawbacksoffilesystems(c3)ConcurrentaccessbymultipleusersConcurrentaccessedneededforperformanceUncontrolledconcurrentaccessescanleadtoinconsistenciesE.g.twopeoplereadingabalanceandupdatingAccountAwith$500atthesametime,onewithdraws$50,theotherwithdraws$100SecurityproblemsPasswordsAccountbalance14MainContentsWhyuseDatabases?WhatisaDatabase?DataanditsModelsHowtoPerformOperationsonaDatabase?WhousesDatabase?PurposesofDatabasesHowtoDeployaDatabase?ThePastandFutureofDatabases15DataAbstractionDBMSprovidesuserswithanabstractviewofthedataHidesthedetailsofstorageandmaintenanceSimplifytheuser’sinteractionwiththesystem16SchemasandInstancesSimilartotypesandvariablesinprogramminglanguagesSchema－模式

thelogicalstructureofthedatabase

AnalogoustotypeinformationofavariableinaprogramInstance

－实例

theactualcontentofthedatabaseataparticularpointintimeAnalogoustothevalueofavariable17SchemaInstance18LevelsofDataAbstractionAdministratorProgrammerEndUser19PhysicallevelPhysicalleveldescribeshowarecord(e.g.,customer)isstored20LogicallevelLogicalleveldescribesdatastoredindatabase,andtherelationshipsamongthedata.

typecustomer=record

name:string;

street:string;

city:integer;

end;21Viewlevelapplicationprogramshidedetailsofdatatypes，Viewscanalsohideinformation(e.g.,salary)forsecuritypurposes22DataIndependenceIngeneral,theinterfacesbetweenthevariouslevelsandcomponentsshouldbewelldefinedsothatchangesinsomepartsdonotseriouslyinfluenceothers.23PhysicalDataIndependencetheabilitytomodifythephysicalschemawithoutchangingthelogicalschemaApplicationsdependonthelogicalschema24DataModelsAcollectionoftoolsfordescribingDataDatarelationshipsDatasemanticsDataconstraintsTypicalDataModelsRelationalmodelEntity-Relationshipmodel P.P.Chen25OtherDataModelsObject-orientedmodelSemi-structureddatamodelsXMLEarlymodelsnetworkmodelhierarchicalmodel26RelationalmodelofBankingData27EntityRelationshipModelE-RmodelofrealworldEntities(objects)E.g.customers,accounts,bankbranchRelationshipsbetweenentitiesE.g.AccountA-101isheldbycustomerJohnsonRelationshipsetdepositorassociatescustomerswithaccounts28WidelyusedfordatabasedesignDatabasedesigninE-Rmodelusuallyconvertedtodesignintherelationalmodelwhichisusedforstorageandprocessing29MainContentsWhyuseDatabases?WhatisaDatabase?HowtoPerformOperationsonaDatabase?DatabaseLanguagesWhousesDatabase?PurposesofDatabasesHowtoDeployaDatabase?ThePastandFutureofDatabases30DatabaseLanguagesDatadefinitionlanguageSpecifythedatabaseschemaDatamanipulationlanguageExpressdatabasequeriesandupdates31DataDefinitionLanguageLanguagefordefiningthedatabaseschemaE.g.

createtable

account(

account-number

char(10),

balance

integer)DDLcompilergeneratesasetoftablesstoredinadatadictionaryDatadictionarycontainsmetadata(i.e.,dataaboutdata)32DataManipulationLanguageLanguageforaccessingandmanipulatingthedataorganizedbytheappropriatedatamodelDMLalsoknownasquerylanguageTwoTypesoflanguagesProcedural–userspecifieswhatdataisrequiredandhowtogetthosedataNonprocedural–userspecifieswhatdataisrequiredwithoutspecifyinghowtogetthosedata33SQLHistoryStructuredQueryLanguageSQL=DDL+DMLDevelopedinIBMatearly1970sSQLStandardSQL-86SQL-89SQL-92SQL:1999SQL:200334InteractiveSQLSQL:widelyusednon-procedurallanguageE.g.findthenameofthecustomerwithcustomer-id192-83-7465

select

customer.customer-name

from

customer

where

customer.customer-id=‘192-83-7465’35EmbeddedSQLApplicationprogramsgenerallyaccessdatabasesthroughoneofLanguageextensionstoallowembeddedSQLC/Java/Cobol…Applicationprograminterface(e.g.ODBC/JDBC)whichallowSQLqueriestobesenttoadatabase36MainContentsWhyuseDatabases?WhatisaDatabase?HowtoPerformOperationsonaDatabase?WhousesDatabase?DatabaseUsersandAdministratorsPurposesofDatabasesHowtoDeployaDatabase?ThePastandFutureofDatabases37DatabaseUsers

UsersaredifferentiatedbythewaytheyexpecttointeractwiththesystemApplicationprogrammers

–interactwithsystemthroughDMLcallsSophisticatedusers

–formrequestsinadatabasequerylanguage38DatabaseUsers(c1)Specializedusers

–writespecializeddatabaseapplicationsthatdonotfitintothetraditionaldataprocessingframeworkNaïveusers

–invokeoneofthepermanentapplicationprogramsthathavebeenwrittenpreviouslyE.g.peopleaccessingdatabaseovertheweb,banktellers,clericalstaff39DatabaseAdministratorCentralcontrolofboththedataandtheprogramsCoordinatesalltheactivitiesofthedatabasesystemthedatabaseadministratorhasagoodunderstandingoftheenterprise’sinformationresourcesandneeds40DatabaseAdministrator’sDutiesDatabaseadministrator'sdutiesinclude:SchemadefinitionStoragestructureandaccessmethoddefinitionSchemaandphysicalorganizationmodificationGrantinguserauthoritytoaccessthedatabaseRoutinemaintenanceBackupthedatabaseEnsureenoughfreediskspaceEnsurethedatabaseperformance41MainContentsWhyuseDatabases?WhatisaDatabase?HowtoPerformOperationsonaDatabase?WhousesDatabase?PurposesofDatabasesTransactionManagementHowtoDeployaDatabase?ThePastandFutureofDatabases42PurposesofDBMSLarge-ScaleDataStorageandSearchConcurrencyControlwhenmulti-useraccessingthedatabaseRecoverywhenthesystemcrashedAccessControltopreventillegaldataaccess43TransactionManagement AtransactionisacollectionofoperationsthatperformsasinglelogicalfunctioninadatabaseapplicationACIDfeaturesAtomicity 原子性Consistency 一致性Isolation 隔离性Durability 持久性44MainContentsWhyuseDatabases?WhatisaDatabase?HowtoPerformOperationsonaDatabase?WhousesDatabase?PurposesofDatabasesHowtoDeployaDatabase?OverallSystemStructureThePastandFutureofDatabases45OverallSystemStructure46StorageManagementStoragemanagerisaprogrammodulethatprovidestheinterfacebetweenthelow-leveldatastoredinthedatabaseandtheapplicationprogramsandqueriessubmittedtothesystemThestoragemanagerisresponsibletothefollowingtasks:interactionwiththefilemanagerefficientstoring,retrievingandupdatingofdata47QueryProcessorDDLinterpreterInterpretsDDLsta