数据库原理lec1overview.ppt

OverviewofDatabaseSystems JianlinFengSchoolofSoftwareSUNYAT SENUNIVERSITY WhatisaDatabase ADatabase Averylarge integratedcollectionofdata Modelsareal world enterprise Entitiese g students coursesRelationshipse g ZhangSanistakingtheDatabaseSystemcourse WhatisaDatabaseManagementSystem ADatabaseManagementSystem DBMS is Asoftwaresystemdesignedtostore manage andfacilitatequerytodatabases PopularDBMSOracleIBMDB2MicrosoftSQLServerDatabaseSystem Databases DBMS DBMSmakesabigindustry Theworld slargestsoftwarecompaniesbyForbesGlobal2000 TypicalApplicationsSupportedbyDatabaseSystems OnlineTransactionProcessing OLTP RecordingsalesdatainsupermarketsBookingflightticketsElectronicbankingOnlineanalyticalprocessing OLAP andDataWarehousingBusinessreportingforsalesdataCustomerRelationshipManagement CRM IstheWWWaDBMS TheWeb SurfaceWeb DeepWebSurfaceWeb simplytheHTMLpagesAccessedby search Posekeywordsinsearchbox DeepWeb contenthiddenbehindHTMLformsAccessedby query Fillinqueryforms Search as SimpleSearch Query as AdvancedSearch Search vs Query Searchisstructure free Thekeywords databasesystems canappearinanyplaceinaHTMLpages Queryisstructure aware Say werestructthatthekeywords databasesystems canonlyappearinthe TITLE field i e weassumethereisanunderlyingSTRUCTURE ofabook Whatisa STUCTURE ReferringtotheCprogramminglanguagestructBOOK charTITLE 256 charAUTHOR 256 floatPRICE intYEAR Inthiscourse westudydatabasemanagementsystemsthatfocusonprocessingstructureddata AnAnecdoteabout Search Google sFoundersarefromDatabaseGroupofStanford Filesvs DBMS WecanstoredatainOSfiles E g GooglehasitsowndistributedfilesystemcalledGoogleFileSystem GFS WhataretheadvantagesofDBMS GooddatamodelingDataIndependenceDataIntegrityandSecuritySimpleandefficientad hocqueriesReducedapplicationdevelopmenttimeConcurrencycontrolCrashrecovery WhyStudyDatabases ShiftfromcomputationtoinformationTrueincorporatecomputingforyearsWebmadethisclearfor therestofus bytheendof90 sIncreasinglytrueofscientificcomputingNeedforDBtechnologyhasexplodedinthelastyearsCorporate retailswipe clickstreams customerrelationshipmgmt supplychainmgmt datawarehouses etc Web notjust documents Searchengines maps e commerce blogs wikis socialnetworks Web2 0 Scientific digitallibraries genomics satelliteimagery physicalsensors simulationdataPersonal Music photo videolibraries Emailarchives Filecontents desktopsearch A DatabasesystemsarethecoreofCS courtesyofJoeHellerstein AHistoricalPerspective 1 IntegratedDataStore IDS byCharlesBachman early1960s NetworkdatamodelTuringAwardSpeech TheProgrammerasNavigator 1973 InformationManagementSystem IMS byIBM late1960s Hierarchicaldatamodel AHistoricalPerspective 2 RelationalDataModel byEdgarCodd 1970 Codd E F 1970 ARelationalModelofDataforLargeSharedDataBanks CommunicationsoftheACM13 6 377 387 TuringAward 1981 SystemR byIBM startedin1974StructuredQueryLanguage SQL INGRES byBerkeley startedin1974POSTGRES Mariposa C Store AHistoricalPerspective 3 DatabaseTransactionProcessing mainlybyJimGray JGray AReuter Transactionprocessing conceptsandtechniques 1993 TuringAward 1998 Object RelationalDBMS 1990s Stonebraker MichaelwithMoore Dorothy Object RelationalDBMSs TheNextGreatWave 1996 Postgres UCBerkeley PostgreSQL IBM sDB2 Oracledatabase andMicrosoftSQLServer DescribingData DataModels Adatamodelisacollectionofconceptsfordescribingdata Aschemaisadescriptionofaparticularcollectionofdata usingagivendatamodel Therelationaldatamodelisthemostwidelyusedmodeltoday Mainconcept relation basicallyatablewithrowsandcolumns Everyrelationhasaschema whichdescribesthecolumns orfields theirnames types constraints etc SchemainRelationDataModel Students sid string name string login string age integer gpa real Table1 AnInstanceoftheStudentsRelation DefinitionoftheStudentsSchema ArelationschemaisaTEMPLATEofthecorrespondingrelation LevelsofAbstractioninaDBMS Manyviewsdescribehowusersseethedata Personalizedaccessofdata Conceptualschemadefineslogicalstructurei e whatrelationstostore Physicalschemaspecifiesphysicalstructure Howthe logical relationsarephysicallystoredonexternalstoragesuchasdisk courtesyofJoeHellerstein Example UniversityDatabase Conceptualschema Students sid string name string login string age integer gpa real Courses cid string cname string credits integer Enrolled sid string cid string grade string Physicalschema Relationsstoredasunorderedfiles IndexonfirstcolumnofStudents ExternalSchema View Course info cid string enrollment integer DataIndependence Applicationsinsulatedfromhowdataisstructuredandstored Logicaldataindependence Protectionfromchangesinlogicalstructureofdata Physicaldataindependence Protectionfromchangesinphysicalstructureofdata OneofthemostimportantbenefitsofusingaDBMS QueriesinaRelationalDBMS SpecifiedinaNon ProceduralwayUsersonlyspecifywhatdatatheyneed ADBMStakescaretoevaluatequeriesasefficientlyaspossible aNon ProceduralQueryLanguage SQL StructuredQueryLanguageBasicformofaSQLquery SELECTtarget listFROMrelation listWHEREqualification ASimpleSQLExample Atanairport agateagentclicksonaformtorequestthepassengerlistforaflight SELECTnameFROMPassengerWhereflight 510275 Passenger pid string name string flight integer Concurrentexecutionofuserprograms Why UtilizeCPUwhilewaitingfordiskI O databaseprogramsmakeheavyuseofdisk Avoidshortprogramswaitingbehindlongonese g ATMwithdrawalwhilebankmanagersumsbalanceacrossallaccounts courtesyofJoeHellerstein Concurrentexecution Interleavingactionsofdifferentuserprogramscanleadtoinconsistency Example Billtransfers 100fromsavingstocheckingSavings 100 Checking 100Meanwhile Bill swiferequestsaccountinfo Badinterleaving Savings 100PrintbalancesChecking 100Printoutismissing 100 courtesyofJoeHellerstein ConcurrencyControl DBMSensuressuchproblemsdon tarise Userscanpretendtheyareusingasingle usersystem Keyconcept Transaction AnTransactionisanatomicsequenceofdatabaseactions reads writes Eachtransaction executedcompletely mustleavetheDBinaconsistentstateifDBisconsistentwhenthetransactionbegins Userscanspecifysomesimpleintegrityconstraintsonthedata andtheDBMSwillenforcetheseconstraints ExampleofTransaction Here consistencyisbasedonourknowledgeofbanking semantics Ingeneral uptowriteroftransactiontoensuretransactionpreservesconsistencyDBMSprovides limited automaticenforcement viaintegrityconstraintse g balancesmustbe 0 courtesyofJoeHellerstein SchedulingConcurrentTransactions Goal executetransactions T1 T2 Tn andensureaconsistentoutcomeOneoption serial schedule oneafteranother Better allowinterleavingoftransactionactions aslongasoutcomeisequivalenttosomeserialschedule courtesyofJoeHellerstein PossibleEnforcementMethods Optimistic Permitarbitraryinterleaving thencheckequivalencetoserialschedule Pessimistic Transactionssetlocksondataobjects suchthatillegalinterleavingisimpossible StrictTwo Phase 2PL LockingProtocol Itisthemostwidelyusedlockingprotocol Tworules 1 IfatransactionTwantstoread respectively modify adatabaseobject itfirstrequiresashared respectively exclusive lockontheobject 2 Alllocksheldbyatransactionarereleasedwhenthetransactioniscompleted Lockingexample T1 Bill Savings 100 Checking 100T2 Bill swife Print Checking Print Savings T1andT2bothlockSavingsandCheckingobjects IfT1locksSavings Checkingfirst T2mustwait AnOverviewofTransactionProperties ADBMSmustensurefourpropertiesoftransactionstokeepdatabaseconsistentinthefaceofconcurrentexecutionsandsystemcrashes AtomicityConsistencyIsolationDurability SoCalledACIDProperties EnsuringAtomicity DBMSensuresatomicity all or nothingproperty evenifsystemcrashesinthemiddleofaXact i e transaction Idea Keepalog history ofallactionscarriedoutbytheDBMSwhileexecutingasetofXacts Beforeacha