Transcript Database systems

Text Book
•Database System Concepts- Silberschatz,
Korth, Sudarshan, Fifth Edition, McGraw
Hill
Basic Concepts
•
•
•
•
Data
Database
Database Systems
Database Management System
• Data : It is a raw fact. E.g. 20
• The collection of data referred to as the
database , contains information relevant to an
enterprise. E.g. banking: for customer
information , accounts, loans etc. University :
students, course registration ,grades etc.
• Database systems are designed to manage
large bodies of information. It includes both
defining structures for storage of information
and providing mechanisms for manipulation
of information. It must ensures the safety of
the information stored.
• DBMS is set of programs to access data.
Advantages of DBMS over File
Systems
• By storing data in a DBMS rather than as a
collection of operating system files , one can
use the DBMS features to manage the data in
robust and efficient manner.
File processing system has a number of major
disadvantages :
• Data redundancy and inconsistency : since
different programmers create the files, the
various files are likely to have different format.
The same information may be duplicated in
several files. E.g. address and telephone no of
particular customer may appear in file that
consist of saving account records and in file that
consist of checking account records. It leads to
higher storage.
It may also lead to inconsistency i.e. various
copies of the same data may no longer agree.
E.g. a changed customer address may be
reflected in saving account records but not
elsewhere in the system.
•Difficulty in accessing data:E.g. bank officer needs to find out names of all
customers who live within a particular postal code
area. Because the designer of the system did not
anticipate this request, there is no application
program on hand. But there is an application
program to generate list of all customers. There are
two choices before officer: obtain list of all customer
and extract information manually or ask the system
programmer to write necessary application program.
• Data isolation:- because data are scattered in
various files , and files may be in different
format, writing new application programs to
retrieve the appropriate data is difficult. Files
with different format get isolated.
• Integrity :- Constraints are not provided. E.g.
if -10 is entered in balance field , it will not
be recognized as wrong amount by file system.
• Atomicity:-in many applications, it is
crucial that, if a failure occurs, the data to
be restored to the consistent state that
existed prior to the failure. The
transaction must happen in its entirety or
not at all. It is difficult to ensure this in
file processing e.g. transfer of money from
one account to another account.
• E.g. program of transferring Rs. 500 from
account A to account B. If a system failures
occurs during the execution of the program, it
is possible that the Rs. 500 was removed from
account A but was not credited to account B,
resulting in an inconsistent database state.
(either both (debit/ credit) or none should
occur)
• Concurrent access anomalies :-many systems
allow multiple users to update the data
simultaneously. System must maintain some
form of supervision for this. But it is very
difficult to implement using file processing
system. problems of read- write operation.
• E.g. consider bank account A, containing Rs.500. If
two customers withdraw funds (Rs.50 and Rs.100
respectively) at about same time, the result of
concurrent executions may leave the account in an
incorrect state.
• If two programs run concurrently , they may both
read the value 500,and write back Rs.450 and Rs.400
respectively.
• Depending on which one writes the value last, the
account may contain either Rs.450 or Rs.400 , rather
than Rs.350.
• Security problems :Every user of the database
system should not be able to access all the
data. It is very difficult to enforce such security
problem in file processing system
View of Data -Three levels of
Abstraction / Three level Architecture
• A major purpose of a database system is to
provide users with abstract view of the data. i.e.
the system hides certain details of how the data
are stored and maintained.
• Physical level (Internal level):- How the data is
actually stored in the database.
• Logical level (Conceptual level): describes
what data are stored in database and what
relationships exist among those data. (like
what is the datatype etc.) DBAs , who must
decide what information to keep in the
database use the logical level of abstraction.
• View level (External level) :- highest level of
abstraction. It describes only part of the entire
database. Many users of the database system
do not need all information; instead they need
to access only a part of the database. The
system may provide many views for the same
database. E.g. tellers ( cashier) in bank see
only that part of the database that has
information on customer accounts; they can
not access information about salaries of
employees.
• Physical independence: The physical level may
be modified independently of the conceptual
level. This means that the user cannot see all
the hardware components of the database,
which is simply a transparent structure for
representing the stored information.
• Examples
• Logical independence: The conceptual level
must be editable without disrupting the
physical level. In other words, the database's
administrator must be able to make
improvements without affecting the users'
experience
• Examples
Instances & Schemas
• The collection of information stored in the
database at a particular moment is called an
instance of the database.
• The overall design of the database is called
database schemas.
• Physical Schema:- Describes the database
design at the physical level.
• Logical Schema:- Describes the database
design at the logical level.
• Subschema:- describes different views of the
database.
Structure of DBMS
The functional components of a database
system can be broadly divided into :
• Storage Manager
• Query Processor
• Storage manager is important because
databases require a large amount of storage
space.
• The query processor is important because it
helps the database system simplify and
facilitate access to data.
1. Storage manager:
• Is 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.
• Is responsible for storing, retrieving and
updating data in the database.
 Components of storage manager:
• Authorization & Integrity manager: tests for
the satisfaction of integrity constraints
(incorrect data like balance = -10) and checks
the authority of users to access data.
• Transaction Manager: Which ensures that the
database remains in a consistent (correct)
state despite system failure , and that
concurrent transaction executions proceed
without conflicting.
• File Manager: Manages allocation of space on
the disk storage.
• Buffer Manager: responsible for fetching data
from disk storage to main memory.
• Storage manager implements several data
structure as a part of the physical system
implementation:
• Data files : stores database itself
• Data dictionary : Stores metadata about the
structure of the database, the schema of the
database.
• Indices : which provides fast access to data items
that hold particular values e.g. book index
2. Query Processor:
• Compiles & Executes DDL and DML statements
• [DDL : Database schema specified by a set of
definitions expressed by a special language called
Data Definition Language. ]
• DDL Interpreter: Interprets DDL statements and
records the definitions into data dictionary.
• [DML : (Data Manipulation language) language that
enables users to access or manipulate data, like
Retrieval, Insertion, Deletion, Modification.]
• DML Compiler: Translates DML into query evaluation
plan (low level instructions)
• Query evaluation engine : executes low-level
instructions generated by the DML compiler.
Overall Architecture / Structure of
DBMS
• Three levels : Physical level, Logical Level, View
level
• Storage manager (Authorization & Integrity
manager, Transaction Manager, File Manager,
Buffer manager)
• Query Processor (DDL Interpreter, DML
Compiler, Query evaluation engine )
Difference between two-tier and three
tier architecture
• In two tier architecture , the application is
partitioned into components that resides at the
client machine , which invokes database system
functionality at the server machine through the
query language statements.
• In three tier architecture, the client machine acts as
merely a front end and does not contain any direct
database calls. The client end communicates with
application server. The business logic of the
application is embedded in the application server ,
instead of being distributed across multiple client.
Data Models
• Structure of the database is the Data Model
• Provides a way to describe the design of a
database at the physical, logical, and view level.
• Relational Model:- Uses collection of tables to
represent both data and the relationships
among those data.
• Relational Model is an example of a record
based model
• The database structured in fixed format records
of several types.
• The ER model:- Consists of a collection of basic
objects, called entities and of relationships
among those entities.
• Object based data model:- ER model with
notions of encapsulation, methods and object
identity.
• Combines features of Object Oriented Data
Model and Relational Data Model.
• Semi-structured data model:- permits the
specification of data where individual data items
of the same type may have different set of
attributes. XML can be used to represent
semistructured data.
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.
• DDL : database schema. creation of table.
Constraints are also provided. (create, alter,
drop)
• DML: Retrieval, insertion, deletion,
modification. (Data within structure)
• Procedural DML : require a user to specify
what data are needed and how to get those
data. PL/SQL
• Declarative (nonprocedural) DML: require a
user to specify what data are needed without
specifying how to get those data. SQL
• Query : statement requesting retrieval of
information. The portion of DML that involves
information retrieval is called query language.
• Metadata:- Data about Data. Example. It provides
information about a certain item's content. For
example, an image may include metadata that
describes how large the picture is, the color depth,
the image resolution and other data.
Database Users
•
•
Naive users: - they interact with the system
by invoking one of the application programs
that have been written previously. E.g. a
user who wishes to find her account
balances over WWW.
Application programmers:- they are
computer professionals who write the
application programs. They can choose from
many tools to develop interface.
•
•
Sophisticated users:-interact with the system
without writing the programs. They form their
request in database query language. They submit
each such query to query processor.
Specialized users: - they write specialized database
applications that do not fit into the traditional
data-processing framework. Among these
applications are computer-aided design systems,
expert system etc.
DBA
Role of DBA
• Schema definition: - DBA cerates the original
database schema by executing a set of data
definition statements in the DDL.
• DBA defines storage structure and access
methods
•
Schema and physical organization modification:The DBA carries out changes to the schema and
physical organization to reflect the changing needs
of the organization. He alters the physical
organization to improve the performance.
•
Granting of authorization for data access:-DBA can
regulate which parts of the database various users
can access.
•
Routine maintenance: - taking backup of database
ensures free disk space availability, monitors job
running on the database etc
Components of DBMS
•
•
•
•
•
•
Data
Hardware
Software
Users
Storage manager
Query Processor
Questions
• Definitions: Data, Database, DBMS, Information,
Instance, Schemas, DML, DDL
• Give 10 examples of Database-System Applications.
• Write down the advantages of DBMS over File
System.
• Explain different views of data.
• Which are the different types of Data Models are
available? Explain.
• Write a short note on Overall Architecture /
Structure of DBMS.
• List down the functions carried out by the DBA OR
Role of DBA