Introduction to Database Systems

Introduction to Database Systems

Transcript

1. ICS$321$Data$Storage$&$Retrieval$$ Introduc8on$to$Database$Systems$ Prof.$$Lipyeow$Lim$ Informa8on$&$Computer$Science$Department$ University$of$Hawaii$at$Manoa$ 1$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$
2. Data,$Database,$DBMS$ !  A$database$:$a$collec8on$of$related$data.$ !  Represents$some$aspect$of$the$real$world$(aka$ universe$of$discourse).$ !  Logically$coherent$collec8on$of$data$ !  Designed$and$built$for$specific$purpose$$ !  Data$are$known$facts$that$can$be$recorded$and$ that$have$implicit$meaning.$ !  A$data(model!is$a$collec8on$of$concepts$for$ describing$data.$ !  A$schema!is$a$descrip8on$of$a$par8cular$ collec8on$of$data,$using$the$a$given$data$model.$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 2$
3. DBMS$ •  A$database(management(system((DBMS)$is$a$ collec&on!of!programs!that$enables$users$to$ – Create$new$DBs$and$specify$the$structure$using$ data$defini8on$language$(DDL)$ – Query$data$using$a$query$language$or$data$ manipula8on$language$(DML)$ – Store$very$large$amounts$of$data$ – Support$durability$in$the$face$of$failures,$errors,$ misuse$ – Control$concurrent$access$to$data$from$many$ users$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 3$
4. Types$of$Databases$ !  OnFline$Transac8on$ Processing$(OLTP)$ !  Banking$ !  Airline$reserva8ons$ !  Corporate$records$ !  OnFline$Analy8cal$ Processing$(OLAP)$ !  Data$warehouses,$data$ marts$ !  Business$intelligence$(BI)$ !  Specialized$databases$ !  Mul8media$ !  XML$ !  Geographical$Informa8on$ Systems$(GIS)$ !  RealF8me$databases$ (telecom$industry)$ !  Special$Applica8ons$ !  Customer$Rela8onship$ Management$(CRM)$ !  Enterprise$Resource$ Planning$(ERP)$ !  Hosted$DB$Services$ !  Amazon,$Salesforce$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 4$
5. A$Bit$of$History$ !  1970$Edgar$F$Codd$$(aka$“Ted”)$invented$the$rela8onal$ model$in$the$seminal$paper$“A$Rela8onal$Model$of$ Data$for$Large$Shared$Data$Banks”$ "  Main$concept:$$rela&on$=$a$table$with$rows$and$columns.$ "  Every$rela8on$has$a$schema,$which$describes$the$columns.$ !  Prior$1970,$no$standard$data$model.$$ !  Network$model$used$by$Codasyl$ !  Hierarchical$model$used$by$IMS$ !  Aaer$1970,$IBM$built$System$R$as$proofFofFconcept$for$ rela8onal$model$and$used$SQL$as$the$query$language.$ SQL$eventually$became$a$standard.$$$$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 5$
6. DBMS$ •  A$database(management(system((DBMS)$is$a$ collec&on!of!programs!that$enables$users$to$ – Create$new$DBs$and$specify$the$structure$using$ data$defini8on$language$(DDL)$ – Query$data$using$a$query$language$or$data$ manipula8on$language$(DML)$ – Store$very$large$amounts$of$data$ – Support$durability$in$the$face$of$failures,$errors,$ misuse$ – Control$concurrent$access$to$data$from$many$ users$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 6$
7. DBMS$Components$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 7$ Storage$ Storage$Manager$ Buffer$Manager$ Index/file/record$ Manager$ Execu8on$Engine$ Query$Compiler$ Transac8on$Manager$ Logging$&$Recovery$ DDL$compiler$ Concurrency$ Control$ Lock$Table$ Buffers$ User/Application Database Administrator
8. Transac8on:$An$Execu8on$of$a$DB$Program$ !  A$transac&on$is$an$atomic$sequence$of$database$ ac8ons$(reads/writes).$ !  Each$transac8on,$executed$completely,$must$leave$the$ DB$in$a$consistent$state$if$DB$is$consistent$when$the$ transac8on$begins.$ !  A$DBMS$executes$mul8ple$transac8ons$concurrently$ !  Instead$of$serially$one$aaer$another$ !  Results$in$beger$DBMS$performance.$Why$?$ !  Interleaving$ac8ons$of$different$transac8ons$can$lead$to$ inconsistency$e.g.,$check$is$cleared$while$account$balance$ is$being$computed.$ !  DBMS$ensures$such$problems$don’t$arise:$$users$can$ pretend$they$are$using$a$singleFuser$system.$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 8$
9. ACID$Proper8es$ •  Atomicity$:$allForFnothing$execu8on$of$ transac8ons$ •  Consistency:$$constraints$on$data$elements$is$ preserved$ •  Isola8on:$each$transac8on$executes$as$if$no$ other$transac8on$is$execu8ng$concurrently$ •  Durability:$effect$of$an$executed$transac8on$ must$never$be$lost$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 9$
10. Ensuring$Isola8on$ !  Scheduling$concurrent$transac8ons$ !  DBMS$ensures$that$execu8on$of${T1,$...$,$Tn}$is$ equivalent$to$some$serial$execu8on$T1’$...$Tn’.$ "  Idea:$use$locks$to$serialize$access$to$shared$ objects$ "  Strict$2$Phase$locking$protocol:$ "  Before$reading/wri8ng$an$object,$a$transac8on$ requests$a$lock$on$the$object,$and$waits$8ll$the$DBMS$ gives$it$the$lock.$$$ "  All$locks$are$released$at$the$end$of$the$transac8on.$$$ "  What$if$Tj$already$has$a$lock$on$Y$and$Ti$later$requests$ a$lock$on$Y?$(Deadlock!)$Ti$or$Tj$is$aborted$and$ restarted!$$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 10$
11. Ensuring$Atomicity$ !  DBMS$ensures$atomicity!even$if$system$crashes$ in$the$middle$of$a$Xact.$ !  Idea:$Keep$a$log$(history)$of$all$ac8ons$carried$ out$by$the$DBMS$while$execu8ng$a$set$of$Xacts.$ "  Write$Ahead$Log$(WAL)$protocol$ "  Before$a$change$is$made$to$the$database,$the$ corresponding$log$entry$is$forced$to$disk.$$ "  Aaer$a$crash,$the$effects$of$par8ally$executed$ transac8ons$are$undone$using$the$log.$$ "  WAL$property:$if$log$entry$wasn’t$saved$before$the$ crash,$corresponding$change$was$not$applied$to$ database!$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 11$
12. Summary$ •  Defini8ons$of$data,$databases,$data$models,$ schema$ •  When$to$use$or$not$use$a$DBMS$ •  DBMS$major$components$ •  Transac8ons$and$concurrency$ •  ACID$proper8es$of$transac8ons$ •  Techniques$for$ensuring$ACID$proper8es$in$ DBMSs.$ Lipyeow$Lim$FF$University$of$Hawaii$at$Manoa$ 12$