Introduction to DBMS PDF

Chapter 1: Introduction Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use  Overview What is Data? Data is a collection of a distinct small unit of inform...

Chapter 1: Introduction Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use  Overview What is Data? Data is a collection of a distinct small unit of information. It can be used in a variety of forms like text, numbers, media, bytes, etc. it can be stored in pieces of paper or electronic memory, etc. Word 'Data' is originated from the word 'datum' that means 'single piece of information.' It is plural of the word datum. In computing, Data is information that can be translated into a form for efficient movement and processing. Data is interchangeable. What is Database? A database is an organized collection of data, so that it can be easily accessed and managed. The database is a collection of inter-related data which is used to retrieve, insert and delete the data efficiently. It is also used to organize the data in the form of a table, schema, views, and reports, etc. For example: The college Database organizes the data about the admin, staff, students and faculty etc. Using the database, you can easily retrieve, insert, and delete the information. Database System Concepts - 6 th 1.2 ©Silberschatz, Korth and  Database Management System (DBMS) DBMS contains information about a particular enterprise Collection of interrelated data Set of programs to access the data An environment that is both convenient and efficient to use DBMS allows users the following tasks: Data Definition: Data Updation: Data Retrieval: User Administration: Database System Concepts - 6 th 1.3 ©Silberschatz, Korth and  Characteristics of DBMS It uses a digital repository established on a server to store and manage the information. It can provide a clear and logical view of the process that manipulates data. DBMS contains automatic backup and recovery procedures. It contains ACID properties which maintain data in a healthy state in case of failure. It can reduce the complex relationship between data. It is used to support manipulation and processing of data. It is used to provide security of data. It can view the database from different viewpoints according to the requirements of the user. Database System Concepts - 6 th 1.4 ©Silberschatz, Korth and  University Database Example Database Applications: Airlines: reservations, schedules Universities: registration, grades Sales: customers, products, purchases, order tracking Employee records, salaries, ….. Databases touch all aspects of our lives Application program examples Add new students, instructors, and courses Register students for courses, and generate class rosters Assign grades to students, compute grade point averages (GPA) and generate transcripts Clicker data: record attendance and quiz answers 4 Database System ConceptsQuiz Q1: Click 1 now! - 6 th 1.5 ©Silberschatz, Korth and  Database Applications Database System Concepts - 6 th 1.6 ©Silberschatz, Korth and  Purpose of Database Systems Drawbacks of using file systems to store data: Data redundancy and inconsistency 4 Multiple file formats, duplication of information in different files Difficulty in accessing data 4 Need to write a new program to carry out each new task Data isolation — multiple files and formats Integrity problems 4 Integrity constraints (e.g. “dept_name of student must be a valid department name”) become “buried” in program code rather than being stated explicitly 4 Hard to add new constraints or change existing ones Database System Concepts - 6 th 1.7 ©Silberschatz, Korth and  Purpose of Database Systems (Cont.) Drawbacks of using file systems (cont.) Atomicity of updates 4 Failures may leave database in an inconsistent state with partial updates carried out 4 Example: Transfer of funds from one account to another should either complete or not happen at all Concurrent access by multiple users 4 Concurrent accessed needed for performance 4 Uncontrolled concurrent accesses can lead to inconsistencies – Example: Two people reading a balance (say 100) and updating it by withdrawing money (say 50 each) at the same time Security problems 4 Hard to provide user access to some, but not all, data Database systems offer solutions to all the above problems Database System Concepts - 6 th 1.8 ©Silberschatz, Korth and  Advantages of DBMS Controls database redundancy: It can control data redundancy because it stores all the data in one single database file and that recorded data is placed in the database. Data sharing: In DBMS, the authorized users of an organization can share the data among multiple users. Easily Maintenance: It can be easily maintainable due to the centralized nature of the database system. Reduce time: It reduces development time and maintenance need. Backup: It provides backup and recovery subsystems which create automatic backup of data from hardware and software failures and restores the data if required. multiple user interface: It provides different types of user interfaces like graphical user interfaces, application program interfaces Database System Concepts - 6 th 1.9 ©Silberschatz, Korth and  Disadvantages of DBMS Cost of Hardware and Software: It requires a high speed of data processor and large memory size to run DBMS software. Size: It occupies a large space of disks and large memory to run them efficiently. Complexity: Database system creates additional complexity and requirements. Higher impact of failure: Failure is highly impacted the database because in most of the organization, all the data stored in a single database and if the database is damaged due to electric failure or database corruption then the data may be lost forever. Database System Concepts - 6 th 1.10 ©Silberschatz, Korth and  Instances and Schemas Similar to types and variables in programming languages Schema – the logical structure of the database Analogous to type information of a variable in a program Physical schema: database design at the physical level 4 E.g. data storage structures, indices for fast access Logical schema: database design at the logical level Instance – the actual content of the database at a particular point in time Analogous to the value of a variable Physical Data Independence – the ability to modify the physical schema without changing the logical schema Applications depend on the logical schema Database System Concepts - 6 th 1.11 ©Silberschatz, Korth and  Data Models A collection of tools for describing Data Data relationships Data semantics Data constraints Relational model Entity-Relationship data model (mainly for database design) Object-based data models (Object-oriented and Object- relational) Semistructured data model (XML) Other older models: Network model Hierarchical model Database System Concepts - 6 th 1.12 ©Silberschatz, Korth and  Evolution of Databases File-Based 1968 was the year when File-Based database were introduced. In file-based databases, data was maintained in a flat file. Though files have many advantages, there are several limitations. One of the major advantages is that the file system has various access methods, e.g., sequential, indexed, and random. It requires extensive programming in a third-generation language such as COBOL, BASIC. Database System Concepts - 6 th 1.13 ©Silberschatz, Korth and  Hierarchical Data Model 19681980 was the era of the Hierarchical Database. Prominent hierarchical database model was IBM's first DBMS. It was called IMS Information Management System). In this model, files are related in a parent/child manner. Below diagram represents Hierarchical Data Model. Small circle represents objects. Like file system, this model also had some limitations like complex implementation, lack structural independence, can't easily handle a many-many relationship, etc. Database System Concepts - 6 th 1.14 ©Silberschatz, Korth and  Network data model : A network model is nothing but a generalization of the hierarchical data model as this data model allows many to many relationships therefore in this model a record can also have more than one parent. The network model in DBMS can be represented as a graph and hence it replaces the hierarchical tree with a graph in which object types are the nodes and relationships are the edges. Database System Concepts - 6 th 1.15 ©Silberschatz, Korth and  In the network model, there can be many possible paths to reach a node from the root node (College is the root node in the above case), therefore the data can be accessed efficiently when compared to the hierarchical data model. But, on the other hand, the process of insertion and deletion of data is quite complex. Database System Concepts - 6 th 1.16 ©Silberschatz, Korth and  Entity-Relationship Model (ER Model) An Entity-Relationship model is a high-level data model that describes the structure of the database in a pictorial form which is known as ER-diagram. In simple words, an ER diagram is used to represent logical structure of the database easily. ER model develops a conceptual view of the data hence it can be used as a blueprint to implement the database in the future. Developers can easily understand the system just by looking at ER diagram. Let's first have a look at the components of an ER diagram. Entity - Anything that has an independent existence about which we collect the data. They are represented as rectangles in the ER diagram. For example - Car, house, employee. Entity Set - A set of the same type of entities is known as an entity set. For example - Set of students studying in a college. Attributes - Properties that define entities are called attributes. They are represented by an ellipse shape. Relationships - A relationship in DBMS is used to describe the association between entities. They are represented as diamond or rhombus shapes in the ER diagram. Database System Concepts - 6 th 1.17 ©Silberschatz, Korth and  Semistructured Data Model: The semistructured data model allows the data specifications at places where the individual data items of the same type may have different attributes sets. The Extensible Markup Language, also known as XML, is widely used for representing the semistructured data. Object-based Data Model: An extension of the ER model with notions of functions, encapsulation, and object identity, as well. This model supports a rich type system that includes structured and collection types. Thus, in 1980s, various database systems following the object-oriented approach were developed. Here, the objects are nothing but the data carrying its properties. Database System Concepts - 6 th 1.18 ©Silberschatz, Korth and  Relational Model Relational model: This is the most widely accepted data model. Column In this model, the database is represented as a collection s of relations in the form of rows and columns of a two-dimensional table. Each row is known as a tuple (a tuple contains all the data for an individual record) while each column represents an attribute. For example - Row s Example of tabular data in the relational model Database System Concepts - 6 th 1.19 ©Silberschatz, Korth and  A Sample Relational Database instructo r departmen t Database System Concepts - 6 th 1.20 ©Silberschatz, Korth and  Database Languages in DBMS A DBMS has appropriate languages and interfaces to express database queries and updates. Database languages can be used to read, store and update the data in the database. Types of Database Languages Database System Concepts - 6 th 1.21 ©Silberschatz, Korth and  1. Data Definition Language (DDL) DDL stands for Data Definition Language. It is used to define database structure or pattern. It is used to create schema, tables, indexes, constraints, etc. in the database. Using the DDL statements, you can create the skeleton of the database. Data definition language is used to store the information of metadata like the number of tables and schemas, their names, indexes, columns in each table, constraints, etc. Here are some tasks that come under DDL Create: It is used to create objects in the database. Alter: It is used to alter the structure of the database. Drop: It is used to delete objects from the database. Truncate: It is used to remove all records from a table. Rename: It is used to rename an object. Comment: It is used to comment on the data dictionary. These commands are used to update the database schema that's why they come under Data definition language. 1.22 Database System Concepts - 6 th ©Silberschatz, Korth and  2. Data Manipulation Language (DML) DML stands for Data Manipulation Language. It is used for accessing and manipulating data in a database. It handles user requests. Here are some tasks that come under DML Select: It is used to retrieve data from a database. Insert: It is used to insert data into a table. Update: It is used to update existing data within a table. Delete: It is used to delete all records from a table. Merge: It performs UPSERT operation, i.e., insert or update operations. Call: It is used to call a structured query language or a Java subprogram. Explain Plan: It has the parameter of explaining data. Lock Table: It controls concurrency. Database System Concepts - 6 th 1.23 ©Silberschatz, Korth and  3. Data Control Language (DCL) DCL stands for Data Control Language. It is used to retrieve the stored or saved data. The DCL execution is transactional. It also has rollback parameters. But in Oracle database, the execution of data control language does not have the feature of rolling back.) Here are some tasks that come under DCL Grant: It is used to give user access privileges to a database. Revoke: It is used to take back permissions from the user. There are the following operations which have the authorization of Revoke: CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE and SELECT. Database System Concepts - 6 th 1.24 ©Silberschatz, Korth and  4. Transaction Control Language (TCL) TCL is used to run the changes made by the DML statement. TCL can be grouped into a logical transaction. Here are some tasks that come under TCL Commit: It is used to save the transaction on the database. Rollback: It is used to restore the database to original since the last Commit. Database System Concepts - 6 th 1.25 ©Silberschatz, Korth and  Data Manipulation Language (DML) Language for accessing and manipulating the data organized by the appropriate data model DML also known as query language Two classes of languages Procedural – user specifies what data is required and how to get those data Declarative (nonprocedural) – user specifies what data is required without specifying how to get those data SQL is the most widely used query language Database System Concepts - 6 th 1.26 ©Silberschatz, Korth and  Data Definition Language (DDL) Specification notation for defining the database schema Example: create table instructor ( ID char(5), name varchar(20), dept_name varchar(20), salary numeric(8,2)) DDL compiler generates a set of tables Data dictionary contains metadata (i.e., data about data) Database schema 4 Which tables are present, what are their attributes, … Integrity constraints 4 Which attributes are primary keys, foreign keys, … Database System Concepts - 6 th 1.27 ©Silberschatz, Korth and  Database Design The process of designing the general structure of the database: Logical Design – Deciding on the database schema. Database design requires that we find a “good” collection of relation schemas. Business decision – What attributes should we record in the database? Computer Science decision – What relation schemas should we have and how should the attributes be distributed among the various relation schemas? Physical Design – Deciding on the physical layout of the database Database System Concepts - 6 th 1.28 ©Silberschatz, Korth and  Database Design? Is there any problem with this design? Quiz Q2: The problem is: (1) missing information (2) repeated information (3) there is no problem (4) these instructor salaries are too low Database System Concepts - 6 th 1.29 ©Silberschatz, Korth and  Design Approaches Normalization Theory (Chapter 8) Formalize what designs are bad, and test for them Entity Relationship Model (Chapter 7) Models an enterprise as a collection of entities and relationships 4 Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects – Described by a set of attributes 4 Relationship: an association among several entities Represented diagrammatically by an entity-relationship diagram: Database System Concepts - 6 th 1.30 ©Silberschatz, Korth and  History of Database Systems 1950s and early 1960s: Data processing using magnetic tapes for storage 4 Tapes provide only sequential access Punched cards for input Late 1960s and 1970s: Hard disks allow direct access to data Network and hierarchical data models in widespread use Ted Codd defines the relational data model 4 Would win the ACM Turing Award for this work 4 IBM Research begins System R prototype 4 UC Berkeley begins Ingres prototype High-performance (for the era) transaction processing Database System Concepts - 6 th 1.31 ©Silberschatz, Korth and  History (cont.) 1980s: Research relational prototypes evolve into commercial systems 4 SQL becomes industrial standard Parallel and distributed database systems Object-oriented database systems 1990s: Large decision support and data-mining applications Large multi-terabyte parallel data warehouses Emergence of Web commerce Early 2000s: XML and XQuery standards Automated database administration Later 2000s: Big Data massively parallel storage systems 4 Google BigTable, Yahoo PNuts, Amazon,.. Parallel data analysis, using MapReduce Database System Concepts - 6 th 1.32 ©Silberschatz, Korth and  Levels of Abstraction Physical level: describes how a record (e.g., customer) is stored. Logical level: describes data stored in database, and the relationships among the data. type instructor = record ID : string; name : string; dept_name : string; salary : integer; end; View level: application programs hide details of data types. Views can also hide information (such as an employee’s salary) for securityDatabase purposes. System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use Database System Concepts - 6 th 1.34 ©Silberschatz, Korth and  DBMS Architecture The DBMS design depends upon its architecture. The basic client/server architecture is used to deal with a large number of PCs, web servers, database servers and other components that are connected with networks. The client/server architecture consists of many PCs and a workstation which are connected via the network. DBMS architecture depends upon how users are connected to the database to get their request done. Types of DBMS Architecture Database System Concepts - 6 th 1.35 ©Silberschatz, Korth and  Database architecture can be seen as a single tier or multi-tier. But logically, database architecture is of two types like: 2-tier architecture and 3-tier architecture. 1-Tier Architecture In this architecture, the database is directly available to the user. It means the user can directly sit on the DBMS and uses it. Any changes done here will directly be done on the database itself. It doesn't provide a handy tool for end users. The 1Tier architecture is used for development of the local application, where programmers can directly communicate with the database for the quick response. Database System Concepts - 6 th 1.36 ©Silberschatz, Korth and  2-Tier Architecture The 2Tier architecture is same as basic client-server. In the two-tier architecture, applications on the client end can directly communicate with the database at the server side. For this interaction, API's like: ODBC, JDBC are used. The user interfaces and application programs are run on the client- side. The server side is responsible to provide the functionalities like: query processing and transaction management. To communicate with the DBMS, client-side application establishes a connection with the server side. Database System Concepts - 6 th 1.37 ©Silberschatz, Korth and  3-Tier Architecture The 3Tier architecture contains another layer between the client and server. In this architecture, client can't directly communicate with the server. The application on the client-end interacts with an application server which further communicates with the database system. End user has no idea about the existence of the database beyond the application server. The database also has no idea about any other user beyond the application. The 3Tier architecture is used in case of large web application. Database System Concepts - 6 th 1.38 ©Silberschatz, Korth and Database System Concepts - 6 th 1.39 ©Silberschatz, Korth and  View of Data An architecture for a database system Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use  Three schema Architecture Database System Concepts - 6 th 1.41 ©Silberschatz, Korth and  This framework is used to describe the structure of a specific database system. The three schema architecture is also used to separate the user applications and physical database. The three schema architecture contains three-levels. It breaks the database down into three different categories. Database System Concepts - 6 th 1.42 ©Silberschatz, Korth and  Objectives of Three schema Architecture The main objective of three level architecture is to enable multiple users to access the same data with a personalized view while storing the underlying data only once. Thus it separates the user's view from the physical structure of the database. This separation is desirable for the following reasons: Different users need different views of the same data. The approach in which a particular user needs to see the data may change over time. The users of the database should not worry about the physical implementation and internal workings of the database such as data compression and encryption techniques, hashing, optimization of the internal structures etc. All users should be able to access the same data according to their requirements. DBA should be able to change the conceptual structure of the database without affecting the user's Internal structure of the database Database System Concepts - 6 th 1.43 should be unaffected by changes ©Silberschatz, Korth and  1. Internal Level/Physical Schema The internal level has an internal schema which describes the physical storage structure of the database. The internal schema is also known as a physical schema. It uses the physical data model. It is used to define that how the data will be stored in a block. The physical level is used to describe complex low-level data structures in detail. Database System Concepts - 6 th 1.44 ©Silberschatz, Korth and  Storage space allocations. For Example: BTrees, Hashing etc. Access paths. For Example: Specification of primary and secondary keys, indexes, pointers and sequencing. Data compression and encryption techniques. Optimization of internal structures. Representation of stored fields. Database System Concepts - 6 th 1.45 ©Silberschatz, Korth and  2. Conceptual Level The conceptual schema describes the design of a database at the conceptual level. Conceptual level is also known as logical level. The conceptual schema describes the structure of the whole database. The conceptual level describes what data are to be stored in the database and also describes what relationship exists among those data. In the conceptual level, internal details such as an implementation of the data structure are hidden. Programmers and database administrators work at this level. Database System Concepts - 6 th 1.46 ©Silberschatz, Korth and  3. External Level At the external level, a database contains several schemas that sometimes called as subschema. The subschema is used to describe the different view of the database. An external schema is also known as view schema. Each view schema describes the database part that a particular user group is interested and hides the remaining database from that user group. The view schema describes the end user interaction with database systems. Database System Concepts - 6 th 1.47 ©Silberschatz, Korth and  Mapping between Views The three levels of DBMS architecture don't exist independently of each other. There must be correspondence between the three levels i.e. how they actually correspond with each other. DBMS is responsible for correspondence between the three types of schema. This correspondence is called Mapping. There are basically two types of mapping in the database architecture: Conceptual/ Internal Mapping External / Conceptual Mapping Conceptual/ Internal Mapping The Conceptual/ Internal Mapping lies between the conceptual level and the internal level. Its role is to define the correspondence between the records and fields of the conceptual level and files and data structures of the internal level. External/ Conceptual Mapping The external/Conceptual Mapping lies between the external level and the Conceptual level. Its role is to define the correspondence between a particular external and the conceptual view. Database System Concepts - 6 th 1.48 ©Silberschatz, Korth and  Data Independence Database System Concepts - 6 th 1.49 ©Silberschatz, Korth and  Data Independence Data independence can be explained using the three-schema architecture. Data independence refers characteristic of being able to modify the schema at one level of the database system without altering the schema at the next higher level. There are two types of data independence: 1. Logical Data Independence Logical data independence refers characteristic of being able to change the conceptual schema without having to change the external schema. Logical data independence is used to separate the external level from the conceptual view. If we do any changes in the conceptual view of the data, then the user view of the data would not be affected. Logical data independence occurs at the user interface level. Database System Concepts - 6 th 1.50 ©Silberschatz, Korth and  2. Physical Data Independence Physical data independence can be defined as the capacity to change the internal schema without having to change the conceptual schema. If we do any changes in the storage size of the database system server, then the Conceptual structure of the database will not be affected. Physical data independence is used to separate conceptual levels from the internal levels. Physical data independence occurs at the logical interface level. Database System Concepts - 6 th 1.51 ©Silberschatz, Korth and  SQL SQL: widely used non-procedural language Example: Find the name of the instructor with ID 22222 select name from instructor where instructor.ID = ‘22222’ select instructor.ID, department.dept name from instructor, department where instructor.dept name= department.dept name and department.budget > 95000 Application programs generally access databases through one o Language extensions to allow embedded SQL Application program interface (e.g., ODBC/JDBC) which allo SQL queries to be sent to a database Chapters 3, 4 and 5 Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use  The Entity-Relationship Model Models an enterprise as a collection of entities and relationships Entity: a “thing” or “object” in the enterprise that is distinguishable from other objects 4 Described by a set of attributes Relationship: an association among several entities Represented diagrammatically by an entity-relationship diagram: What happened to dept_name of instructor? Quiz Q3: Database System Concepts, 6 th Ed. instructor is a (1) relationship (2) diagram ©Silberschatz, (3)and Korth entity (4) not checking if I’m awake! Sudarshan See www.db-book.com for conditions on re- use  Other Data Models Relational model: flat, “atomic” values Object Relational Data Models Extend the relational data model by including object orientation and constructs to deal with added data types. Allow attributes of tuples to have complex types, including non- atomic values such as nested relations. XML Allows representation of semi-structured data 4 optional attributes, repeating attributes Originally intended as a document markup language not a data model XML has become the basis for many data interchange formats. 4 more recently, the JavaScript Object model and its textual representation, JSON, are being used for data exchange Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use Database System Internals Query processor Storage manager Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use Database System Concepts - 6 th 1.56 ©Silberschatz, Korth and  Query Processor: The query processor components include · DDL interpreter, which interprets DDL statements and records the definitions in the data dictionary. · DML compiler, which translates DML statements in a query language into an evaluation plan consisting of low-level instructions that the query evaluation engine understands. A query can usually be translated into any of a number of alternative evaluation plans that all give the same result. The DML compiler also performs query optimization, that is, it picks the lowest cost evaluation plan from among the alternatives. Query evaluation engine, which executes low-level instructions generated by the DML compiler. Database System Concepts - 6 th 1.57 ©Silberschatz, Korth and  Storage Manager: A storage manager is a program module that provides the interface between the lowlevel data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system. The storage manager translates the various DML statements into low-level file- system commands. Thus, the storage manager is responsible for storing, retrieving, and updating data in the database. Database System Concepts - 6 th 1.58 ©Silberschatz, Korth and  The storage manager components include: Authorization and integrity manager, which tests for the satisfaction of integrity constraints and checks the authority of users to access data. · Transaction manager, which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting. · File manager, which manages the allocation of space on disk storage and the data structures used to represent information stored on disk. · Buffer manager, which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory. Database System Concepts - 6 th 1.59 ©Silberschatz, Korth and  Transaction Manager: A transaction is a collection of operations that performs a single logical function in a database application. Each transaction is a unit of both atomicity and consistency. Thus, we require that transactions do not violate any database- consistency constraints. That is, if the database was consistent when a transaction started, the database must be consistent when the transaction successfully terminates. Transaction - manager ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures. Database System Concepts - 6 th 1.60 ©Silberschatz, Korth and Database Users and Administrators Database Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use  Database Administrators and Database Users A primary goal of a database system is to retrieve information from and store new information in the database. People who work with a database can be categorized as database users or database administrators. Database Users and User Interfaces There are four different types of database-system users, differentiated by the way they expect to interact with the system. Different types of user interfaces have been designed for the different types of users. Naive users are unsophisticated users who interact with the system by invoking one of the application programs that have been written previously. For example, a bank teller who needs to transfer $50 from account A to account B invokes a program called transfer. This program asks the teller for the amount of money to be transferred, the account from which the money is to be transferred, and the account to which the money is to be transferred. Database System Concepts - 6 th 1.62 ©Silberschatz, Korth and  Application programmers are computer professionals who write application programs. Application programmers can choose from many tools to develop user interfaces. Rapid application development (RAD) tools are tools that enable an application programmer to construct forms and reports without writing a program. There are also special types of programming languages that combine imperative control structures (for example, for loops, while loops and if-then-else statements) with statements of the data manipulation language. These languages, sometimes called fourth-generation languages, often include special features to facilitate the generation of forms and the display of data on the screen. Most major commercial database systems include a fourth generation language. Sophisticated users interact with the system without writing programs. Instead, they form their requests in a database query language. They submit each such query to a query processor, whose function is to break down DML statements into instructions that the storage manager understands. Analysts who submit queries to explore data in the database fall in this category. Database System Concepts - 6 th 1.63 ©Silberschatz, Korth and  Various responsibilities of Database Administrator (DBA) : Responsible for designing overall database schema (tables & fields). To select and install database software and hardware. Responsible for deciding on access methods and data storage. DBA selects appropriate DBMS software like oracle, SQL server or MySQL. Used in designing recovery procedures. DBA decides the user access level and security checks for accessing, modifying or manipulating data. DBA is responsible for specifying various techniques for monitoring the database performance. DBA is responsible for operation managements. The operation management deals with the data problems which arises on day to day basis, and the responsibilities include are: 1. Investigating if any error is been found in the data. 2. Supervising of restart and recovery procedures in case of any event failure. 3. Supervising reorganization of the databases. 4. Controlling and handling all periodic dumps of data. Database System Concepts - 6 th 1.64 ©Silberschatz, Korth and  Query Processing 1. Parsing and translation 2. Optimization 3. Evaluation Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use  Transaction Management What if system fails? What if more than one user is concurrently updating same data? A transaction is a collection of operations that performs a single logical function in a database application Transaction-management component ensures that the database remains in a consistent (correct) state despite system failures (e.g., power failures and operating system crashes) and transaction failures Concurrency-control manager controls the interaction among the concurrent transactions, to ensure the consistency of the database. Database System Concepts, 6 th Ed. ©Silberschatz, Korth and Sudarshan See www.db-book.com for conditions on re- use  Storage Manager: A storage manager is a program module that provides the interface between the lowlevel data stored in the database and the application programs and queries submitted to the system. The storage manager is responsible for the interaction with the file manager. The raw data are stored on the disk using the file system, which is usually provided by a conventional operating system. The storage manager translates the various DML statements into low-level file- system commands. Thus, the storage manager is responsible for storing, retrieving, and updating data in the database. Database System Concepts - 6 th 1.67 ©Silberschatz, Korth and  The storage manager components include: Authorization and integrity manager, which tests for the satisfaction of integrity constraints and checks the authority of users to access data. · Transaction manager, which ensures that the database remains in a consistent (correct) state despite system failures, and that concurrent transaction executions proceed without conflicting. · File manager, which manages the allocation of space on disk storage and the data structures used to represent information stored on disk. · Buffer manager, which is responsible for fetching data from disk storage into main memory, and deciding what data to cache in main memory. The buffer manager is a critical part of the database system, since it enables the database to handle data sizes that are much larger than the size of main memory. Database System Concepts - 6 th 1.68 ©Silberschatz, Korth and