Database Management Systems Chapter 1What we will coverWhat Is a DBMS?Custom application & Files vs. DBMSWhy Use a DBMS?Why Study Databases??Data ModelsLevels of AbstractionExample: University DatabaseData Independence *Concurrency ControlTransaction: An Execution of a DB ProgramScheduling Concurrent TransactionsEnsuring AtomicityThe LogDatabases make these folks happy ...Structure of a DBMSSummarySummary cont.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 1Database Management SystemsChapter 1Instructor: Deborah [email protected] ClappDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 2What we will coverBasicsModeling (capturing the relevant aspects of) the “real world” in a databaseQuestioning databasesDesigning consistent databasesSecuring databasesAdvanced topicsSemi structured data – XML vs relational databaseDistributed and parallel databasesData warehousingData miningInformation RetrievalSpatial DatabasesExtended “reasoning” over database ( deductive)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 3What Is a DBMS?What is a database?A (possibly very large, ) integrated collection of data.Models real-world enterprise.• Entities (e.g., students, courses)• Relationships (e.g., Madonna is taking CS341)A Database Management System (DBMS) is a software package designed to store and manage databases.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 4Custom application & Files vs. DBMSApplication must move large datasets between main memory and secondary storage (e.g., buffering, page-oriented access, 32-bit addressing, etc.)Special code for different queries (questions to be answered)Must protect data from inconsistency due to multiple concurrent usersCrash recoverySecurity and access controlDatabase Management Systems 3ed, R. Ramakrishnan and J. Gehrke 5Why Use a DBMS?Data independence and efficient access.Reduced application development time.Data integrity and security.Uniform data administration.Concurrent access, recovery from crashes.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 6Why Study Databases??Shift from computation to informationDatasets increasing in diversity and volume. Digital libraries, interactive video, Human Genome project, EOS project ... need for DBMS explodingDBMS encompasses most of CSOS, languages, theory, AI, multimedia, logic?Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 7Data ModelsA data model is a collection of high level concepts for describing data.A schema is a description of a particular collection of data, using the a given data model.The relational model of data is the most widely used model today.Main concept: relation, basically a table with rows and columns. A set of recordsEvery relation has a schema, which describes the columns, or fields.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 8Levels of AbstractionMany views, a conceptual (logical) schema, and a physical schema.Views describe how users see the data. Conceptual schema defines logical structurePhysical schema describes the files and indexes used. Schemas are defined using DDL; data is modified/queried using DML.Physical SchemaConceptual SchemaView 1 View 2 View 3Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 9Example: University DatabaseConceptual 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)Physical schema:Relations stored as unordered files. Index on first column of Students.External Schema (View): Course_info(cid:string,enrollment:integer)Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 10Data Independence *Applications insulated from how data is structured and stored.Logical data independence: Protection from changes in logical structure of data.Physical data independence: Protection from changes in physical structure of data. One of the most important benefits of using a DBMS!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 11Concurrency ControlConcurrent execution of user programs is essential for good DBMS performance.Shared use Keep cpu working on several user programs concurrently (disk accesses are frequent, and slow).Interleaving actions 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 single-user system.Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 12Transaction: An Execution of a DB ProgramKey concept is transaction, which is an atomic sequence of database actions (reads/writes).A transaction, executed completely, must leave the DB in a consistent state if it is consistent when the transaction begins.the DBMS does not understand the semantics of the data. (e.g., how the interest on account is computed). Users can specify integrity constraints on the data, and the DBMS will enforce them.Ensuring that a transaction (run alone) preserves consistency is ultimately the user’s responsibility!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 13Scheduling Concurrent TransactionsDBMS ensures that execution of {T1, ... , Tn} is equivalent to some serial execution T1’ ... Tn’.Before reading/writing an object, a transaction requests a lock on the object, and waits till the DBMS gives it the lock. All locks are released at the end of the transaction. Idea: If an action of Ti (say, writing X) affects Tj (which perhaps reads X), one of them, say Ti, will obtain the lock 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 a lock on Y? (Deadlock!) Ti or Tj is aborted and restarted!Database Management Systems 3ed, R. Ramakrishnan and J. Gehrke 14Ensuring AtomicityDBMS ensures atomicity (all-or-nothing property) even if system crashes in the middle of a Xact.Idea: Keep a log (history) of all actions carried out by the DBMS while executing a set of Xacts:Before a change is made to the database, the corresponding log entry is forced to a safe location.