Database Management Systems Chapter 1 Overview of DBMSs

Database Management Systems Chapter 1 Overview of DBMSs Professor: Iluju Kiringa kiringa@site. uottawa. ca http: //www. site. uottawa. ca/~kiringa SITE 5072 1

Objectives v Definition of a DBMS § § v Data models § v Definition -- Data abstraction -- Data independence Main topics of study § § v What is a DBMS? -- Why use and study DBMSs? Why is a DBMS different than a file system? File management -- Query and transaction processing Data independence --Architecture of DBMSs Miscellaneous § Actors -- History 2

What Is a DBMS? v Definition § A database is a very large, integrated collection of data. § A Database Management System (DBMS) is a software package designed to store and manage databases. v Models real-world organization / enterprise. § § Entities (e. g. , students, courses, faculty, and classrooms) Relationships (e. g. , Sue is enrolled in CSI 3317; Iluju teaches CSI 3317, CSI 3317 is taught in TBT 070) 3

Drawbacks of Files Systems Each application must move large datasets between main memory and secondary storage (must deal with, e. g. , buffering, page-oriented access, etc). v Each application must deal with some method of identifying all data items in case the available addressing mode is not sufficient (e. g. , 32 -bit addressing cannot directly access more than 4 GB). v 4

Drawbacks of Files Systems (Cont’d) Need special code for different queries (i. e. all query codes are ad hoc). v Must protect data from inconsistency due to multiple concurrent users changing it. v Must ensure consistent crash recovery. v Must provide more security and access control than the password mechanism offered by operating systems. v 5

Why Use a DBMS? v v v Data independence: application don’t see details of data representation and storage. Efficient access: use of very sophisticated data storage and access methods. Reduced application development time: functionalities of a DBMS need not be duplicated. Data integrity and security: enforcing integrity constraints and access control. Uniform data administration: experienced users administer data that is used by inexperienced ones. Concurrent access, recovery from crashes: users access data without thinking of whoever else uses it. 6

Why Study Databases? ? v Datasets increasing in diversity and volume § § § v v Today’s HUGE amounts of data need to be efficiently managed – Some applications involve PBs of data Digital libraries, interactive video, Human Genome project, EOS project, Mars exploration projects, …. . . need for DBMS exploding Efficient management of such a diverse amount of data involves research in many fundamental issues DBMS encompasses most of CS § OS, languages, theory, AI, multimedia, logic, etc. 7

Data Models v v A data model is a collection of high-level constructs for describing data. A schema is a description of a particular collection of data, using a given data model. An instance of a schema is a sample set of data organized using a given schema. The relational model is the most widely used model today. § § Main concept: relation instance (relation), i. e. a table with rows and columns. Every relation has a relation schema (schema), which describes the name and columns, or fields. 8

Levels of Abstraction v v Data schemas are given at 3 levels of abstractions. Many views, single conceptual (logical) schema and physical schema. § § § Views (or external schemas) describe how users see the data. Conceptual schema defines logical structure Physical schema describes the files and indexes used. View 1 View 2 View 3 Conceptual Schema Physical Schema * Schemas are defined using a data definition language (DDL). 9

Example: Enterprise Database v Conceptual schema: § § § v Physical schema: § § v Emp(eid: integer, ename: string, age: integer, sal: real) Works_in(eid: integer, did: integer, rating: real) Dept(did: integer, budget: real, mgr: integer) Relations stored as ordered files. Index on first column of Emp, and Dept; index on all columns of Works_in, etc. External Schema (View): § Dept_count(did: integer, count: integer) 10

Example: University Database v Conceptual schema: § § § v Physical schema: § § v 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) Relations stored as unordered files. Index on first column of Students, and Courses; index on two first columns of Enrolled, etc. External Schema (View): § Course_info(cid: string, enrollment: integer) 11

Data Independence Applications insulated from how data is structured and stored. v Each level of abstraction protected from changes in the structure of the level below it. v Logical data independence: Protection from changes in logical structure of data. v Physical data independence: Protection from changes in physical structure of data. v 12

Querying Data v A query is a question involving the stored data, e. g. , § § v v v What is the name of the professor who teaches CSI 3317? Which percentage of students got an A+ in CSI 3317? A query language is a special purpose language in which queries can be posed against databases. Data is modified/queried using a data manipulation language (DML). So a query language is a subset of a DML The relational model supports 2 query languages: § § Relational calculus: logic-based query language Relational algebra: based on a set of operators for manipulating relations. 13

Transactions v v A transaction is an atomic sequence of database actions (reads/writes) corresponding to the execution of a DB transaction program. Each transaction, executed completely, must leave the DB in a consistent state if DB is consistent when the transaction begins. § § Users can specify some simple integrity constraints on the data, and the DBMS will enforce these constraints. Beyond this, the DBMS does not really understand the semantics of the data. Thus, ensuring that a transaction preserves consistency is ultimately the user’s responsibility! 14

Transactions: Concurrency Control v Concurrent execution of user programs is essential for good DBMS performance. § Because disk accesses are frequent, and relatively slow, it is important to keep the cpu working by processing several user programs concurrently. Interleaving actions of different user programs can lead to inconsistency: e. g. , depositing money while account balance is being computed may lead to lost of money if not done properly. v DBMS ensures such problems don’t arise: users can pretend they are using a single-user system. v 15

Scheduling Concurrent Transactions v A DBMS ensures that execution of {T 1, . . . , Tn} is equivalent to some serial execution of T 1, . . . , 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. (Strict 2 PL locking protocol. ) 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. If Tj already has a lock on Y and Ti later requests a lock on Y, there is Deadlock! Ti or Tj is aborted and restarted! 16

Transactions: Crash Recovery A DBMS ensures atomicity (all-or-nothing property) even if there is a crash in the middle of a transaction. v Idea: Keep a log (history) of all actions carried out by the DBMS while executing a set of transactions: v § § Before a change is made to the database, the corresponding log entry is forced to a safe location. (WAL protocol) After a crash, the effects of partially executed transactions are undone using the log. (Thanks to WAL, if log entry wasn’t saved before the crash, corresponding change was not applied to database!) 17

People Involved with Databases v v End users: just use an interface to a DBMS! DBMS vendors: IBM, Oracle, Informix, Microsoft, etc DBMS researchers and implementers: invent new theories and algorithms for, and write code of DBMSs DB application programmers: write C/C++/Java/… programs that interact with DBMSs § v E. g. smart webmasters, CSI 3317 students, etc Database administrator (DBA) § § Designs logical /physical schemas Handles security and authorization Data availability, crash recovery Database tuning as needs evolve DBAs must understand how a DBMS works! 18

These layers must consider concurrency control and recovery Structure of a DBMS v v v A typical DBMS has a Query Optimization layered architecture. and Execution The figure does not show Relational Operators the concurrency control and recovery components. Files and Access Methods This is one of several Buffer Management possible architectures; Disk Space Management each system has its own variations. DB 19

Historical Perspective v v v Early 1960 s: GE designed 1 st general-purpose DBMS, based on the network data model. Late 1960 s: IBM developed IMS, based on the hierarchical data model. Early 1970 s: E. Codd introduced the relational model to solve problems related to the previous models. Late 1970 s and early 1980 s: work on transaction processing, mainly by Jim Gray and P. Bernstein, All the 1980 s: use of relational DBs became standard practice in large corporations, many vendors entered the market, standardization efforts on SQL, the query language for relational DBs, etc. 20

Historical Perspective (Cont’d) v v v Late 1980’s and early 1990 s: richer data models (objectoriented, object-relational, etc), and more expressive query languages (Datalog, nested relations, etc) are introduced. Late 1990 s: major vendors extend relational DBMSs towards new data types (images, text, multimedia content) and queries based hereon. All along the way: § Scientific recognition: Turing Awards to DB researchers E. Codd and J. Gray § Birth of a huge, multibillion industry (IBM, Oracle …) 21

Summary v v v v DBMS used to maintain, query large datasets Benefits include recovery from system crashes, concurrent access, quick application development, data integrity and security Levels of abstraction give data independence A DBMS typically has a layered architecture DB researchers, implementers, and administrators hold responsible jobs and are well-paid DBMS R&D is a very broad and exciting area of CS Database companies form a multibillion industry 22