Introduction to Database Systems Module 1, Lecture 1

1 Database Management Systems , R. Ramakrishnan1 Introduction to Database SystemsModule 1, Lecture 1 Instructor: Raghu Management Systems , R. Ramakrishnan2 What Is a DBMS?vA very large, integrated collection of real-world enterprise. Entities ( , students, courses) Relationships ( , Madonna is taking CS564)vA Database Management system (DBMS) is asoftware package designed to store andmanage Management Systems , R. Ramakrishnan3 Why Use a DBMS?vData independence and efficient application development integrity and data access, recovery from Management Systems , R. Ramakrishnan4 Why Study Databases??vShift from computation to information at the low end : scramble to webspace (a mess!)

2 At the high end : scientific applicationsvDatasets increasing in diversity and volume. Digital libraries, interactive video, HumanGenome project, EOS project .. need for DBMS explodingvDBMS encompasses most of CS OS, languages, theory, A I, multimedia, logic? Database Management Systems , R. Ramakrishnan5 Data ModelsvA data model is a collection of concepts fordescribing schema is a description of a particularcollection of data, using the a given relational model of data is the most widelyused model today. Main concept: relation, basically a table with rowsand columns. Every relation has a schema, which describes thecolumns, or Management Systems , R.

3 Ramakrishnan6 Levels of AbstractionvMany views, singleconceptual (logical) schemaand physical schema. Views describe how userssee the data. Conceptual schema defineslogical structure Physical schema describesthe files and indexes used.* Schemas are defined using DDL; data is modified/queried using SchemaConceptual SchemaView 1 View 2 View 3 Database Management Systems , R. Ramakrishnan7 Example: University DatabasevConceptual schema: 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)vPhysical schema: Relations stored as unordered files.

4 Index on first column of Schema (View): Course_info(cid:string,enrollment:intege r) Database Management Systems , R. Ramakrishnan8 Data IndependencevApplications insulated from how data isstructured and data independence: Protection fromchanges in logical structure of data independence: Protection fromchanges in physical structure of data.* One of the most important benefits of using a DBMS! Database Management Systems , R. Ramakrishnan9 Concurrency ControlvConcurrent execution of user programsis essential for good DBMS performance. Because disk accesses are frequent, and relativelyslow, it is important to keep the cpu humming byworking on several user programs actions of different user programscan lead to inconsistency: , check is clearedwhile account balance is being ensures such problems don t arise: userscan pretend they are using a single-user Management Systems , R.

5 Ramakrishnan10 Transaction: An Execution of a DB ProgramvKey concept is transaction, which is an atomicsequence of Database actions (reads/writes).vEach transaction, executed completely, mustleave the DB in a consistent state if DB isconsistent when the transaction begins. Users can specify some simple integrity constraints onthe data, and the DBMS will enforce these constraints. Beyond this, the DBMS does not really understand thesemantics of the data. ( , it does not understandhow the interest on a bank account is computed). Thus, ensuring that a transaction (run alone) preservesconsistency is ultimately the user s responsibility! Database Management Systems , R.

6 Ramakrishnan11 Scheduling Concurrent TransactionsvDBMS ensures that execution of {T1, .. , Tn} isequivalent to some serial execution T1 .. Tn . Before reading/writing an object, a transaction requestsa lock on the object, and waits till the DBMS gives it thelock. All locks are released at the end of the transaction.(Strict 2PL locking protocol.) Idea: If an action of Ti (say, writing X) affects Tj (whichperhaps reads X), one of them, say Ti, will obtain thelock on X first and Tj is forced to wait until Ti completes;this effectively orders the transactions. What if Tj already has a lock on Y and Ti later requests alock on Y? (Deadlock!) Ti or Tj is aborted and restarted!

7 Database Management Systems , R. Ramakrishnan12 Ensuring AtomicityvDBMS ensures atomicity (all-or-nothing property)even if system crashes in the middle of a : Keep a log (history) of all actions carried outby the DBMS while executing a set of Xacts: Before a change is made to the Database , thecorresponding log entry is forced to a safe location.(WAL protocol; OS support for this is often inadequate.) After a crash, the effects of partially executedtransactions are undone using the log. (Thanks to WAL, iflog entry wasn t saved before the crash, correspondingchange was not applied to Database !) Database Management Systems , R. Ramakrishnan13 The LogvThe following actions are recorded in the log: Ti writes an object: the old value and the new record must go to disk before the changed page!

8 Ti commits/aborts: a log record indicating this records chained together by Xact id, so it s easy toundo a specific Xact ( , to resolve a deadlock).vLog is often duplexed and archived on stable log related activities (and in fact, all CC relatedactivities such as lock/unlock, dealing with deadlocksetc.) are handled transparently by the Management Systems , R. Ramakrishnan14 Databases make these folks happy ..vEnd users and DBMS vendorsvDB application programmers smart webmastersvDatabase administrator (DBA) Designs logical /physical schemas Handles security and authorization Data availability, crash recovery Database tuning as needs evolveMust understand how a DBMS works!

9 Database Management Systems , R. Ramakrishnan15 Structure of a DBMSvA typical DBMS has alayered figure does notshow the concurrencycontrol and is one of severalpossible architectures;each system has its Optimizationand ExecutionRelational OperatorsFiles and Access MethodsBuffer ManagementDisk Space ManagementDBThese layersmust considerconcurrencycontrol andrecoveryDatabase Management Systems , R. Ramakrishnan16 SummaryvDBMS used to maintain, query large include recovery from system crashes,concurrent access, quick applicationdevelopment, data integrity and of abstraction give data DBMS typically has a layered hold responsible jobsand are well-paid!

10 VDBMS R&D is one of the broadest,most exciting areas in CS.