Introduction to Database Systems

1. Introduction to Database Systems

2. What is a DBMS? • DBMS = database management system • Manages data (usually on secondary storage - hard disk) • Supports: • efficient access to the data (e.g., using indices that are implemented as search trees or hash tables) • access control (who can read/write the data) • concurrent access (multiple users can access the system simultaneously) • recovery (means to recover data when the system crashes) • data integrity (Rules on the data (called integrity constraints) can be specified. The system does not allow changes to the data that violate these rules).

3. Why use a DBMS? • Programmer can concentrate on the control logic. • Details, such as low-level data management, concurrent access, access control, etc. are taken care by the system. • Advantage: much easier to program. • Disadvantage: efficiency may be an issue, a lot of resources are needed. • If we want to manage huge data with concurrent access, then the cheapest solution is to use a commercial DBMS. • Implementing everything ourselves could be very costly.

4. Levels of Abstraction external schema • Many views, single conceptual (logical) schemaand physical schema. • Views describe how users see the data. • Conceptual schema defines logical structure • Physical schema describes the files and indices used. View 1 View 2 View 3 Conceptual Schema Physical Schema Schemas are defined using data definition language (DDL); data is modified using data manipulation language (DML) and queries using data query language (DQL).

5. Example: 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 (or tables) stored as unordered files. • Index on first column (i.e. attribute) of Students. • External Schema (View): • Course_info(cid:string,enrollment:integer)

6. Data 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!

7. Concurrency Control • 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 humming by working on several user programs concurrently. • Interweaving actions of different user programs 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.

8. Transaction: An Execution of a DB Program • A transactionis an atomicsequence of database actions (reads/writes). • Each transaction, executed completely, must leave the DB in a consistent stateif the DB was consistent when the transaction began. • Users can specify some simple integrity constraintson the data, and the DBMS will enforce these constraints. • Beyond this, the DBMS does not really understand the semantics of the data. (e.g., it does not understand how the interest on a bank account is computed).

9. Ensuring Atomicity • DBMS ensures atomicity (all-or-nothing property) even if system crashes in the middle of a transaction. • Idea: Keep a log(history) of all actions carried out by the DBMS while executing a set of transactions.

10. Databases make these folks happy ... • End users • DB application programmers • E.g. smart webmasters • Database administrator (DBA) • Designs logical /physical schemas • Handles security and authorization • Data availability, crash recovery • Database tuning as needs evolve Must understand how a DBMS works!

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

12. Summary • A DBMS is used to maintain and query a large datasets. • Benefits include recovery from system crashes, concurrent access, quick application development, data integrity, and security. • Levels of abstraction gives data independence. • A DBMS typically has a layered architecture. • DBAs hold responsible jobs and are well-paid! • DBMS is one of the broadest, most exciting area in computer science.