II B.

Tech I-Semester CSM-A

Database Management Systems (123AM)

Mrs. Nayan Rai

Assistant Professor
Dept. of CSE(DS), GNITS
 UNIT - I (a)
1. History of Database Systemof Database System
2. Database System Applications
3. Purpose of Database Systems
4. View of Data
5. Database Languages - DDL, DML
6. Relational Databases
7. Database Architecture
8. Database Users and Administrators
9. Data Storage and Querying
10. Transaction Management
 History of Database Systems
 Techniques for data storage & processing have evolved over the years:

 1950s and early 1960s:

 Magnetic tapes were developed for data storage.
 Input data from punched card decks & output to printers.
 Sequential access only & Data size is larger than main memory.

 Late 1960s and 1970s:

 Hard disks allowed direct access (10s of milliseconds) to data
 Network and hierarchical data models with lists & trees.
 Codd [1970] defines the relational data model
 won the ACM Turing Award for this work
 Hide implementation details completely from the programmer
 Performance is not good compared to network, hierarchical models.
 History (cont.)
 1980s (Relational model is supreme among data models)
 IBM Research begins System R prototype
 SQL becomes industrial standard
 Research on Parallel, distributed, Object Oriented database
systems.
 Early 1990s:
 SQL supports Large decision support applications
 Query Intensive & Transaction Processing applications
 Parallel database products & object-relational model
 Late 1990s & Early 2000s:
 Explosive growth of the WWW
 High transaction processing rates & Web Interface to data
 High reliability & 24×7 availability
 Later 2000s:
 Giant data storage systems
 Google BigTable, Yahoo PNuts, Amazon, ..
 Parallel data analysis, using MapReduce
 Introduction to DBMS
Database: The collection of data relevant to an enterprise (or)
a very large, integrated collection of data.
DBMS:
 A collection of interrelated data and Set of programs to access
and modify those data.
 A software package designed to store and manage databases.
 The goal is to store and retrieve database information that is

both convenient and efficient.

 It manage large bodies of information (Storage structure,
manipulation of information)
 Provide safety of Information from system crashes
 Share data among many users & avoid unauthorized
access also.
1. Database System Applications
 2. Purpose of Database Systems
 University organization keeps information & programs to
 Add new students, instructors, and courses
 Register students for courses, and generate class rosters
 Assign grades to students, compute GPA and generate
transcripts
 Record attendance and quiz answers

 In the early days, database applications were built directly on

top of file systems.

What is a File system: supported by a conventional OS

 It stores permanent records in various files
 It needs different application programs to extract records
from, and add records to, the appropriate files.
(a) What is a file system?
(b) What is a DBMS?
 Drawbacks of using file systems to store data:

i. Data redundancy and inconsistency

 Multiple file formats, duplication of information in different
files. (Eg: Customer and Savings A/C data)

ii. Difficulty in accessing data

 Need to write a new program to carry out each new task

iii. Data isolation — multiple files and formats

i. Integrity problems
 Consistency constraints (e.g. “dept_name of student must
be a valid department name”)

 Hard to add new constraints or change existing ones

 Eg: Bank A/C balance never fall below an amount 1000

v. Atomicity problems
 Failures may leave database in an inconsistent state with
partial updates carried out
 Example: Transfer of funds from one account to another
should either complete or not happen at all
vi. Concurrent access anomalies
 Concurrent access is needed for performance
 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.
vii. Security problems
 Not every user of the file system should be able to access
all the data.
 Database systems offer solutions to all the above problems
 Characteristics of a DBMS
 Data stored into Tables: Data is never directly stored into the
database. Data is stored into tables, created inside the database.
 Reduced Redundancy: In the modern world hard drives are very
cheap, but earlier when hard drives were too expensive,
unnecessary repetition of data in database was a big problem. But
DBMS follows Normalisation which divides the data in such a way
that repetition is minimum.
 Data Consistency: On Live data, i.e. data that is being continuosly
updated and added, maintaining the consistency of data can
become a challenge. But DBMS handles it all by itself.
 Support Multiple user and Concurrent Access: DBMS allows
multiple users to work on it(update, insert, delete data) at the same
time and still manages to maintain the data consistency.
 Query Language: DBMS provides users with a simple Query
language, using which data can be easily fetched, inserted, deleted
and updated in a database.
 Advantages of a 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.
 Disadvantages of a 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.
 4. TYPES OF DBMS ARCHITECTURE
There are three types of DBMS architecture:

1. Single tier architecture

2. Two tier architecture
3. Three tier architecture

1.Single -Tier Architecture:

•In this type of architecture, the database is readily available on the

client machine, any request made by client doesn’t require a network
connection to perform the action on the database.
•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.
 4. TYPES OF DBMS ARCHITECTURE
2. Two -Tier Architecture:
• In two-tier architecture, the Database system is present at the server
machine and the DBMS application is present at the client machine,
these two machines are connected with each other through a reliable
network.
• Whenever client machine makes a request to access the database
present at server using a query language like sql, the server perform the
request on the database and returns the result back to the client.
• The application connection interface such as JDBC, ODBC are used for
the interaction between server and client.
 4. TYPES OF DBMS ARCHITECTURE
3. Three -Tier Architecture:
• In three-tier architecture, another layer is present between the
client machine and server machine.
• In this architecture, the client application doesn’t communicate
directly with the database systems present at the server machine,
rather the client application communicates with server
application and the server application internally communicates
with the database system present at the server.
 Database Architecture
 Most users of a database system today are not present at the
site of the database system, but connect to it through a
network.

 Client Machines, on which remote database users work.

 Server Machines, on which the database system runs.

 Database applications partitioned into two parts:

 Two-tier architecture
 Three-tier architecture
(i) Two-tier architecture:

 The application is partitioned into a component that resides at

the client machine, which invokes database system
functionality at the server machine through query language
statements.

 API standards like ODBC and JDBC are used for interaction
between the client and the server.

(ii) Three-tier architecture: suitable for large WWW applications

 The client end communicates with an application server,

usually through a forms interface. The application server in
turn communicates with a database system to access data.

 The business logic of the application is embedded in the

application server.
 View of Data
 DBMS provide users with an abstract view of the data.
(a) Data Abstraction: Developers hide the complexity from
users through 3 layers or levels of abstraction to reduce User
Interaction.

 Physical level: describes how the data are actually stored

and also complex low-level Data structures in detail. (DBAs)

 Logical level: describes what data are stored in database,

and what rrelationships exist among those data.
(programmers & DBAs)

 View level: Simplify the user interaction with the system

(Users)
 Views can also hide information (such as an employee’s
salary) for security purposes.
 The highest level of abstraction describes only part of the
entire database.
The Three levels of Data Abstraction
 (b) Instances and Schemas
 Schema – The overall design of the database (logical
structure).
 Physical schema: database design at the physical level
 Logical schema: database design at the logical level
 subschema – schemas/DB design at the view level

 Instance – The collection of information stored in the

database at a particular moment (value of a variable)

 Physical Data Independence – The ability to modify the

physical schema without changing the logical schema.

 programmers construct applications by using the logical

schema

 In general, the interfaces between the various levels and

components should be well defined so that changes in some
parts do not seriously influence others.
 (c) Data Models
 Data model: A collection of conceptual tools for describing
 Data
 Data relationships
 Data semantics
 Consistency constraints

 It provides a way to describe the design of a database at the

physical, logical, and view levels.

Examples:
 Entity-Relationship data model (mainly for database design)
 Relational model - Record based model (Oracle, DB2)
 Object-based data models (Object-oriented & Object-relational)
 Semistructured data model (XML)
 Other older models:
 Network model (IDMS)
 Hierarchical model (IMS)
 (i) The Entity-Relationship Model
 Consists of a collection of basic objects, called entities, and of
relationships among these objects.

 Entity: a thing/object in the real world that is distinguishable

from other objects. Eg: Person, bank A/C, customer

 Entity set: The set of all entities of the same type

 Attribute: Entities are described in a DB by a set of attributes

Eg: A/C No, Balance for a bank A/C entity
cid, cname, ccity for a customer entity

 Relationship: An association among several entities.

Eg: a depositor relationship associates a customer with his
account.

 Relationship set: The set of all relationships of the same type

 (i) The Entity-Relationship Model
 E-R Model Components:
 Rectangles – entity sets
 Ellipses - attributes
 Diamonds - relationships among entity sets
 Lines - link attributes to entity sets and
Entity sets to relationships
 Mapping Cardinalities: The no. of entities to which another
entity can be associated via a relationship set.

 A Sample E-R Diagram of Banking System:

 (ii) The Relational Model
 Uses a collection of tables to represent both data and the
relationships among those data.

 Each table has multiple columns, and each column has a

unique name.

 It is an example of a record-based model.

 The database is structured in fixed-format records of several

types.

 Each table contains records of a particular type. Each record

type defines a fixed number of fields, or attributes.

 Hides such low-level implementation details from database

developers and users.
A Sample Relational Database of 3 Tables:
A Sample Relational Database of 2 Tables:

instructor

department
 Other data models
(iii) Object-oriented data model:
 Extension of E-R Model with encapsulation, methods
(functions), and object identity notations.

(iv) Object-relational data model:

 combines features of the object-oriented data model and
relational data model

(v) Extensible Markup Language (XML) Model:

 Used to represent semi-structured data.
(vi) Hierarchical data model:
 Organizes data in a tree structure
 Hierarchy of parent and child segments
 Data is represented by a collection of record types
 No child segment having more than one parent

Eg: Information Management System (IMS) from IBM

(vii) Network Data Model:

 Data is represented by a collection of records
 Relationships among data are represented by links/pointers
 The records in the database are collection of graphs

Eg: Integrated Data Management System (IDMS), Honeywell

 Database Languages
 A database system provides a data definition language to
specify the database schema and a data manipulation
language to express database queries and updates.

 Database Language types:

(i) DDL (Data Definition Language)
(ii) DML (Data Manipulation Language)
(iii) DQL (Data Query Language)
(iv) DCL (Data Control Language)
(v) TCL (Transaction Control Language)
 Database Languages
(i) DDL (Data Definition Language)
 Data Definition Language(DDL) actually consists of the SQL commands that can be used
to define the database schema.
 It simply deals with descriptions of the database schema and is used to create and modify
the structure of database objects in the database.
 DDL is a set of SQL commands used to create, modify, and delete database structures but
not data.
 These commands are normally not used by a general user, who should be accessing the
database via an application.

List of DDL commands:

1. CREATE: This command is used to create the database or its objects (like table, index,
function, views, store procedure, and triggers).
2. DROP: This command is used to delete objects from the database.
3. ALTER: This is used to alter the structure of the database.
4. TRUNCATE: This is used to remove all records from a table, including all spaces
allocated for the records are removed.
5. COMMENT: This is used to add comments to the data dictionary.
6. RENAME: This is used to rename an object existing in the database.
 Database Languages
(i) DDL (Data Definition Language)
i. CREATE command:
 Create is a DDL command used to create a table or a database.

Creating a database
To create a database in RDBMS, create command is uses.
Following is the Syntax,
Create database database-name;
Example for creating database
Create database Test;
The above command will create a database named Test.
 Database Languages
(i) DDL (Data Definition Language)
i. CREATE command:
Creating a table
 create command is also used to create a table.
 We can specify names and datatypes of various columns along.
Following is the Syntax,
create table table-name
(Column-name1 datatype1, Column-name2 datatype2(size), Column-name1
datatype3(size),....,Column-nameN datatypeN);
Create table command will tell the database system to create a new table with given table
name and column information.
Example for creating table
Create table Student
(id int, name varchar(20), age int(2));
The above command will create a new table Student in database system with 3 columns,
namely id, name and age.
(i) DDL (Data Definition Language)
ii. Alter command:
 Alter command is used for alteration of table structures.
 There are various uses of alter command, such as,
 to add a column to existing table
 to rename any existing column
 to change datatype of any column or to modify its size.
 alter is also used to drop a column.
To ADD column to existing table
Using alter command we can add a column to an existing table.
Following is the Syntax,
Alter table table-name add(column-name datatype);
Here is an Example for this,
Alter table student add(address char);
The above command will add a new column address to the Student
table.
 (i) DDL (Data Definition Language)

ii. Alter command:

 To add multiple column to existing table. Using alter command we
can even add multiple columns to an existing table.
Following is the Syntax,
Alter table table-name add(column1 datatype1, column2 datatype2,
column3 datatype3, colum4 datatype4);
Here is an Example for this,
Alter table student add(father_name varchar(60), mother_name
varchar(60), DOB date);
Date input format is:- ’date-month-year’ i.e ’10-jan-2016’
The above command will add three new columns to the Student table
 (i) DDL (Data Definition Language)
ii. Alter command:
 To add column with default value.
 Alter command can add a new column to an existing table with
default values.
Following is the Syntax,
alter table table_name add (column_name datatype default data);

Here is an Example for this,

alter table Student add(branch char default ‘CSE');

The above command will add a new column with default value to the
Student table
 (i) DDL (Data Definition Language)
ii. Alter command with modify:
 To modify an existing column.
 Alter command is used to modify data type of an existing column .

Following is the Syntax,

alter table table-name modify(column-name datatype);

Here is an Example for this,

alter table Student modify(address varchar(50));

The above command will modify address column of the Student table
(i) DDL (Data Definition Language)
ii. Alter command with rename:
 To rename a column
 Using alter command you can rename an existing column.

Following is the Syntax,

alter table table-name rename old-column-name to new-column-
name;

Here is an Example for this,

alter table Student rename address to Location;

The above command will rename address column to Location.

(i) DDL (Data Definition Language)
ii. Alter command with Drop:
To drop a column
 alter command is also used to drop columns also.

Following is the Syntax,

alter table table-name drop(column-name);

Here is an Example for this,

alter table Student drop(address);

The above command will drop address column from the Student
table
(i) DDL (Data Definition Language)
iii. Truncate command:
 Truncate command is different from delete command.
 Delete command will delete all the rows from a table whereas
truncate command re-initializes a table(like a newly created table).
 Truncate command removes all records from a table. But this
command will not destroy the table's structure.
 When we apply truncate command on a table its Primary key is
initialized.
 Eg. If you have a table with 10 rows and an auto_increment primary
key, if you use delete command to delete all the rows, it will delete all
the rows, but will not initialize the primary key, hence if you will insert
any row after using delete command, the auto_increment primary key
will start from 11. But in case of truncate command, primary key is re-
initialized.
Following is its Syntax,
truncate table table-name
Here is an Example explaining it.
truncate table Student;
The above query will delete all the records of Student table.
(i) DDL (Data Definition Language)
iv. Drop command:
 Drop query completely removes a table from database.
 This command will also destroy the table structure.
Following is its Syntax,
drop table table-name;
Here is an Example explaining it.
drop table Student;
The above query will delete the Student table completely.

 It can also be used on Databases.

drop database database-name;
For Example, to drop a database,
drop database Test;
The above query will drop a database named Test from the system.
(i) DDL (Data Definition Language)
v. Rename command:
 Rename command is used to rename a table.

Following is its Syntax,

rename table old-table-name to new-table-name;

Here is an Example explaining it.

rename table Student to Student-record;

The above query will rename Student table to Student-record.

 Database Languages
(ii) DML (Data Manipulation Language)
 The SQL commands that deals with the manipulation of data present in the
database belong to Data Manipulation Language(DML).
 It is the component of the SQL statement that controls access to data and to the
database.
 Basically, DCL statements are grouped with DML statements.

List of DML commands:

1. INSERT : It is used to insert data into a table.

2. UPDATE : It is used to update existing data within a table.

3. DELETE : It is used to delete records from a database table.

4. LOCK: Table control concurrency.

5. CALL: Call a PL/SQL or JAVA subprogram.

6. EXPLAIN PLAN: It describes the access path to data.

(i) DML (Data Manipulation Language)
i. INSERT INTO command:
 Insert command is used to insert data into a table.
Following is its general syntax,
insert into table_name values(value1,value2,……,valueN);
Consider a table Student with following fields.
S_id,S_Name,age

INSERT into Student values(101,'Adam',15);

The above command will insert a record into Student table.

S_id S_Name age

101 Adam 15
(i) DML (Data Manipulation Language)
ii. UPDATE command:
 Update command is used to update a row of a table.
Following is its general syntax,
UPDATE table-name set column-name = value where condition;

Lets see an example,

update Student set age=18 where s_id=102;

Example to Update multiple columns

UPDATE Student set s_name='Abhi',age=17 where s_id=103;
(i) DML (Data Manipulation Language)
ii. DELETE command:
 Delete command is used to delete data from a table. Delete
command can also be used with condition to delete a
particular row.
Following is its general syntax,
DELETE from table- name;
Example to Delete all Records from a Table
DELETE from Student;
The above command will delete all the records from Student table.

Example to Delete a particular Record from a Table

DELETE from Student where s_id=103;
The above command will delete the record where s_id is 103 from
Student table.
 Database Languages
III. DQL (Data Query Language)
 DQL statements are used for performing queries on the data within
schema objects.
 The purpose of the DQL Command is to get some schema relation
based on the query passed to it.
 We can define DQL as follows it is a component of SQL statement that
allows getting data from the database and imposing order upon it.
 It includes the SELECT statement. This command allows getting the
data out of the database to perform operations with it.
 When a SELECT is fired against a table or tables the result is compiled
into a further temporary table, which is displayed or perhaps received
by the program i.e. a front-end.
List of DQL commands:
1. SELECT: It is used to retrieve data from the database.
 Database Languages
iv. DCL (Data Control Language)
 DCL includes commands such as GRANT and REVOKE which mainly
deal with the rights, permissions, and other controls of the database
system.

List of DCL commands:

1. GRANT : This command gives users access privileges to the
database.
2. REVOKE : This command withdraws the user’s access privileges
given by using the GRANT command.
 4. Database Languages
iv. DCL (Data Control Language)
CONNECTING TO ORACLE:
CONNECT<USER NAME>/<PASSWORD>@<DATABASE NAME>;

Create user login:

CREATE USER <USER_NAME> IDENTIFIED BY <PASSWORD>;

Provide roles:
GRANT CONNECT, CREATE SESSION, RESOURCE TO <USER_NAME>;

Provide privileges:
GRANT ALL PRIVILEGES TO <USER_NAME>;
 4. Database Languages
V. TCL (Transaction Control Language)
 Transactions group a set of tasks into a single execution unit.
 Each transaction begins with a specific task and ends when all the
tasks in the group successfully complete.
 If any of the tasks fail, the transaction fails. Therefore, a transaction
has only two results: success or failure.

List of DCL commands:

1. BEGIN: Opens a Transaction.

2. COMMIT: Commits a Transaction.
3. ROLLBACK: Rollbacks a transaction in case of any error occurs.
4. SAVEPOINT: Sets a save point within a transaction.
5. SET TRANSACTION: Specifies characteristics for the transaction.
 4. Database Languages
v. TCL (Transaction Control Language)
 Transaction Control Language(TCL) commands are used to
manage transactions in database.These are used to manage
the changes made by DML statements. It also allows
statements to be grouped together into logical transactions.
1. Commit command:
 Commit command is used to permanently save any
transaaction into database.
Following is Commit command's syntax,
commit;
 4. Database Languages
v. TCL (Transaction Control Language)
2. Rollback command:
 This command restores the database to last commited state. It
is also use with savepoint command to jump to a savepoint in a
transaction.
Following is Rollback command's syntax,
rollback to savepoint-name;

3. Savepoint command:
savepoint command is used to temporarily save a transaction so
that you can rollback to that point whenever necessary.
Following is savepoint command's syntax,
savepoint savepoint-name;
 (i) Data Definition Language (DDL)
 Specification notation for defining the database schema
Syntax: create table table_name (
ID char(5),
name varchar(20),
dept_name varchar(20),
salary numeric(8,2));
 DDL compiler generates a set of tables stored in a data dictionary
 Data Dictionary contains metadata (i.e., data about data)
 Domain Constraints
 Integrity /Consistency constraints
 Primary key (ID uniquely identifies instructors)
Referential integrity (references constraint in SQL)
– e.g. dept_name value in any instructor tuple must appear in
department relation
 Assertions- Every dept must have at least five courses in every semester
 Authorization (Read, Insert, Update, Delete authorizations)
 (ii) Data-Manipulation Language
 A Language for accessing and manipulating the data organized
by the appropriate data model.
 DML also known as query language
 DML performs –
 The retrieval of information stored in the database
 The insertion of new information into the database
 The deletion of information from the database
 The modification of information stored in the database
 Two classes of languages
 Procedural DMLs– user specifies what data is required and
how to get those data also.
 Declarative DMLs (nonprocedural) – user specifies what
data is required without specifying how to get those data.
 Declarative DMLs are easy to learn & use than procedural DMLs
 SQL
 A query is a statement requesting the retrieval of information

 The portion of a DML that involves information retrieval is

called a query language

 SQL: widely used non-procedural language

 Example: Find the name of the instructor with ID 22222
select name
from instructor
where instructor.ID = ‘22222’

 Queries may involve information from more than one table.

Eg: Joins
 5. Relational Databases
 It is based on the relational model and uses a collection of
tables to represent both data and the relationships among
those data.

i. Tables - Each table has multiple columns and each column has a
unique name.
ii. Data-Manipulation Language (DML)
 select instructor.name from instructor where instructor.deptname
= ’History’;
 select instructor.ID, department.dept name from instructor,
department where instructor.dept name= department.dept name
and department.budget > 95000;
iii. Data-Definition Language (DDL)
 create table department (dept name char (20), building char (15),
budget numeric (12,2));
A Sample Relational Database of 2 Tables:

instructor

department
 (iv) Database Access from Application Programs
 Used to interact with the database
 Usually written in a host language, such as Cobol, C, C++,Java
 To access the database, DML statements need to be executed
from the host language.
 Two ways to access the database:
 Application program interface (e.g., ODBC/JDBC) which
allow SQL queries to be sent to a database
 By extending the host language syntax to embed DML calls
within the host language program.
 DML precompiler, converts the DML statements to normal
procedure calls in the host language.
Database System Architecture
 Database Users and Administrators
 People who work with a database can be categorized as
 Database Users/Database Administrators.
(a)Database Users and User Interfaces: 4 types of users
 Naive users
 Application programmers
 Sophisticated users
 Specialized users
(b) Database Administrator: A person who has central control of
both the data and the programs that access those data.
Functions:
i. Schema definition
ii. Storage structure and access-method definition
iii. Schema and physical-organization modification
iv. Granting of authorization for data access
v. Routine maintenance
 (a) Database Users and User Interfaces
4 types of users
 Naive users
 Unsophisticated users who interact with the system by invoking one of
the application programs that have been written previously. (Form I/F)
 Simply read reports generated from the database. (Clerk/student)
 Application programmers
 Computer professionals who write application programs using RAD
Tools, which construct forms and reports with minimal programming
efforts.
 Sophisticated users
 Interact with the system without writing programs.
 Data analysts can do the query operations by using OLAP tools and
Data Mining Tools.
 Specialized users
 Write specialized database applications that do not fit into the
traditional data-processing framework.
Eg: CADs, Expert Systems to store data with complex data types
 (b) Database Administrator & Functions
i. Schema definition - DBA creates the original database schema
by executing a set of DDL statements.

i. Storage structure and access-method definition.

i. Schema and physical-organization modification. The DBA

carries out changes to the schema and physical organization
to reflect the changing needs of the organization which
improves performance.

i. Granting of authorization for data access. Granting different

types of authorization to different types of users for data
accessing.

i. Routine maintenance
- Periodically backing up the database (avoid data loss)
- enough free disk space is available (normal operations)
- Monitoring jobs running on the database (Performance)
 Data Storage and Querying
 A database system is partitioned into modules that deal with
each of the responsibilities of the overall system.
 The functional components :
- The storage manager
- The query processor

 The storage manager can minimize the need to move data

between disk and main memory.

 The query processor can simplify and facilitate access to

data.

 Any Database system can translate updates and queries

written in a nonprocedural language, at the logical level, into
an efficient sequence of operations at the physical level
 (i) Storage Manager
 A program module that provides the interface between the
low-level data stored in the database and the application
programs and queries submitted to the system.
 Responsible for the interaction with the file manager.
 Components:
 Authorization and integrity manager (Integrity constraints)
 Transaction manager (Consistency, Concurrent access issues)
 File manager (Space, Data structure)
 Buffer manager (Fetch data from disk to Main memory)

 Data structures used in physical system implementation:

 Data files (store the database itself)
 Data dictionary (Metadata about the structure of the DB)
 Indices (fast access to data items that hold particular
values)
 (ii) Query Processor
Components:
 DDL Interpreter
 DML Compiler
 Query Evaluation Engine

 DDL Interpreter - interprets DDL statements and records the

definitions in the data dictionary.

 DML Compiler - translates DML statements in a query

language into an evaluation plan consisting of low-level
instructions that the query evaluation engine understands. It
performs query optimization also.

 Query Evaluation Engine - executes low-level instructions

generated by the DML compiler.
 7. Transaction Management
 A transaction is a collection of operations that performs a
single logical function in a database application.

 What if system fails?

 What if more than one user is concurrently updating same

data?

 Atomicity: All-or-None requirement

 Consistency: Correctness Requirement (sum before/after)

 Durability: Persistence requirement after a failure even.

 Transaction-management component Ensuring the

atomicity and durability properties, failure recovery.
 Concurrency-control manager controls the interaction
among the concurrent transactions, to ensure the consistency
of the database.

 Failure recovery detect system failures and restore the

database to the state that existed prior to the occurrence of
the failure.

 The transaction manager consists of the concurrency-

control manager and the recovery manager.

 Applications
 Finance
 Telecommunications
 Product designs
 Administrative workflows
 Summary
1. Database System Applications
2. Purpose of Database Systems
3. View of Data
4. Database Languages - DDL, DML
5. Relational Databases
6. Database Architecture
7. Database Users and Administrators
8. Data Storage and Querying
9. Transaction Management
End of Unit-I (a)