UNIT I :DATABASE AND DATABASE USERS

DataBase (DB): It’s a collection of related data. Data means known fact that can have implicit
meaning and can be recorded. Eg: MSaccess, Excel, Mysql, Oracle
DataBase Management System (DBMS): A DBMS is a collection of programs that enables
users to create, and maintain the database.
A DBMS is a general purpose software that facilitates for defining, constructing ,manipulating
and sharing database among various users.
Defining of data base involves specifying datatypes, data structure and a constraints of data to be
stored in database.
Constructing the data base is the process of storing the data itself on some storage medium that
is controlled by DBMS.
Manipulating the data base includes querying the database, updating a database and generating
the reports from database.
Sharing the database allows multiple users and program to access the database simultaneously
The database definition is stored in the database in the form of database catalog as dictionary
it is called metadata.
An application program access the database by sending the Queries/Request for DBMS.

Characteristics of the database approach:

The main characteristics of database approach v/s file processing approach are the
following.

 Self describing nature of database system.

 Insulation between program and data and data abstraction.
 Support of multiple views of data.
 Sharing of data and multiuser transaction processing.
Explanation:

 Self describing nature of data base :

The definition is stored in the DBMS catalog structure, type,
storage format and various constraints on data. The database system not only contains
database itself , but also the complete definition of database structure and constraints. The
information stored in catalog is metadata.

 Insulation b/w program and data and data obstruction:

The structure of data files is stored in DBMS catalog separately from access
program. This property is called program data independence.
User application programs can operate on data by invoking
interface and implementation operation through their names and arguments. This is
program-operation independence.
The characteristic that allows program-data independence and
program-operation independence is called data abstraction.

 Support of multiple views of data:

A database has many users , each of whom may require a
different perspective or view of database. A view may be a subset of database and it
contains virtual data that is derived from the database type.

 Sharing of data and multi user transaction processing:

A DBMS allows multiple user to access the simultaneously. It includes concurrency
control software to ensure that several user trying to update the same date in a controlled
manner and the result of update is correct
Transaction is an executing program that includes one or more database. The DBMS
must have several transaction property.

a)The isolation property ensures that each transaction appears to execute in isolation
from other.
b)The atomicity property ensures that either all database operation in the transaction are
executed or none.

Actors on the scene:

Identifying the people whose job will involve day to day activity a large database.

 Database administrator(DBA): Many people uses the same resources, so chief

administrator manages these resources.
1. The primary resource is database itself and secondary resource is
DBMS and related software.
2. The DBA is responsible for authorizing access, co-ordination and
monitoring its use.
3. Accruing hardware and software resources.
4. DBA is accountable for problems. such as security breaches and poor
system response time
 Data base designer(DBD):
It is responsible for identifying data to be stored in database and choosing appropriate
structure, to represent and store the data
1. DBD is responsible to communicate with users in order to understand their
requirements and create a design for it.
2. DBD assigns staff responsible to complete the design.
3. DBD interact with users group and develop view.
4. View is the analyzed and integrated to create a design.

 End users:
They are the people whose job require access to database for querying
updating and generating report. They are several categories of end users
1. Casual End Users
2. Navie /Parametric End Users
3. Sophisticated End Users
4. Standalone End Users
1. Casual end users:
Occasionally access database but they need different information each time

2. Navie/Parametric End Users:

Make up a sizeable portion of database end users. The main job includes constantly
querying updating and using standard types for querying and updating called canned transaction.
Eg: bank tellers
3. Sophisticated end users:
Includes engineers, business analyst and others.
4. Standalone end users:
They maintain personal database by using read made program packages that provide
menu based/graphic based interface.

 System analysts and application programmer:

They determine the requirement of end users and develop specification for meet
their requirements.
Application programmers implement these specification as programs, then they test
debug, document and maintain these transaction such analyst and programmer are called
software engineers.

Workers behind the scene:

Those who work to maintain the database system environment.
The categories of workers behind the scene are:
1. DBMS system designers and implementer
Design and implement the DBMS modules and interface for software packages. A
DBMS consist of many modules for implementing the catalog, processing query
language, the interface, accessing and buffering data, controlling concurrency and data
recovery and security.
2. Tool developers:
Design and implement tools the [software packages that facilitates database
modeling and design, database system design and improved performance. Tools are
optional packages it includes packages for database design performance monitoring,
graphics, interface, simulation and test the data generation
3. Operator and maintenances personnel:
They are responsible for actual running and maintenance of hardware and software in
database system.

Advantages of using DBMS:

1. Controlling redundancy:
Redundancy means storing data multiple times leads to problem
1. Duplication of data
2. Storing space is wasted
3. inconsistent
Storing the data in one place ensures data consistency and save storage
space in order to improve the performance.

2. Restricting unauthorized access:

When multiple user share a large database, most of users will not have authorization to
access all information. Hence DBA has to provide security and authorization subsystem
to create accounts and to specify restriction.

3. Providing persistent storage for program object:

Database can be used to provide persistent storage for program object and data structure.
DBMS is incompatible with programming language data structure. Object oriented
database system offer compatibility with one or more object oriented programming
languages.

4. Providing backup and recovery:

A DBMS must provide facilities for recovery from hardware and software
failures. The backup and recovery sub system is responsible for recovery.

5. Providing multiple user interface:

Because of many type of users with varying level of technical knowledge, a database
should provides a database should provide a verity of user interface. Font style interface
and menu driven interface are commonly in use and it is known as GUI’s.

6. Providing storage structure for efficient query processing.

7. Representing complex relationship among data.
8. Enforcing integrity constrains.
9. Permitting interference and action using rules.
10. Reduces application development time
A brief history of database application:

 Early database application users are hierarchal and network systems.

 Providing application flexibility with relation database.
 Interchanging data on the web for e-comers.
 Extending database capabilities for new application
 Object oriented application and need for more complex database’s.

Where not to use a DBMS:

The over head of using DBMS are

1. High initial investment in hardware software and training.
2. Over head for providing security, concurrency control and recovery functions.
3. The generality that a DBMS provides for defending and processing data.
4. Simple, well defined database application are not expected to change

Database system concepts and architecture

Data Model: A collection of concepts that can be used to describe the structure of a
database. The structure of a database includes data type, relationship and constraints.
Categories of data models
• Conceptual (high-level) data models: Provide concepts that are close to the way many
users perceive data. It includes entities, attribute & relationships.

• Physical (low-level) data models: Provide concepts that describe details of how data is
stored in the computer. Data of this level is meant for specialists.

• Implementation (representational) data models: Provide a concept that may be

understood by the end users & it among schema levels are needed to.

• Record-based data models: Representational data models represents data by using

record structure, hence it is called Record-based data models.

Schemas,Instances
Database Schema: The description of a database is called database schema. It includes
descriptions of the database structure and the constraints.
• Schema Diagram: A diagrammatic display of (some aspects of) a database schema.

Student
Name Student_number Class Major

 Database Instance: The actual data stored in a database at a particular moment in time.
Also called database state (or occurrence).
Three-Schema Architecture
• Defines DBMS schemas at three levels:

• Internal level has an internal schema, which describes the physical storage
structures of the database. It uses a physical data model & describe the complete
details of data storage and access paths.

• Conceptual level has an conceptual schema,which describe the structure of the

whole database for a community of users. It uses a conceptual data model &
hides the details of physical storage structure and describe entities, data type,
relationships, constraints.

• External schemas has an external level, which includes a number user views. It
describe the part of the database that a particular user group is interested & it
hides the rest of the database from users.

Mappings: The process of transforming requests and response between the levels

Data independence:
Data independence which can be defined as the capacity to change the schema at one level
without having to change the schema at the next higher level.
Two types of data independence are :
• Logical data independence :

It is the capacity to change the conceptual schema without

changing external schemas. The conceptual schema is changed to expand the database , or to
reduce the database
• Physical data independence:

It is the capacity to change the internal schema without having to change the
conceptual schema. Hence, the external schemas need not be changer as well. Change to
internal schema may be needed because some physical files were reorganized. To
improve the performance of retrieval or update.
Database languages and interfaces:
The types of languages provided by a DBMS are :
• Data definition language(DDL)

It used by the DBA and by database designer to define both schemas. The DBMS will have a
DDL compiler whose function is to process DDl statements in order to identify description of the
schema constructs and to store the schema description in the DBMS catalog.
• The storage definition language(SDL): It is used to specify the internal schema. The
internal schema is specified by a combination of parameters and specifications related to
storage—the DBA staff typically controls indexing and mapping of data to storage.

• The view definition language(VDL): to specify user views and their mapping to the
conceptual schema, SQL is used in the role of VDL to define user or application views as
result of predefined quires.

• data manipulation language(DML):Manipulations includes retrival, insertion, deletion

,and modification of the data . The DBMS provides a set of operations or a language
called the data manipulation language(DML).

There are two main types of DMLs.

• A high-level or nonprocedural DML: It can be used on its own to specify complex
database operation concisely.

• A low-level or procedural DML: It is used in general purpose programming language. It

includes retrieval of individual records from the database and process it separately.

DBMS interfaces:
User-friendly interfaces provided by a DBMS may include the following:
1. Menu-based interfaces for web clients or browsing.
These interfaces present the user with lists of options (called menus) that lead the user
through the formulation of a request. Pull-down menus are very popular technique in web-based
user interfaces.
2. Forms-based interfaces.
A form-based interface displays a form to each user. User can fill out all the form entries
to insert new data , or they canfill out only certain entries retrieve matching data.
3. Graphical user interfaces.
A GUI typically displays a schema to the user in diagrammatic form. GUIs utilize both
menus and forms. GUIs use a pointing device
4. Natural language interfaces.
These interfaces accept requests written in English or some other language and attempts
to understand them. A natural language interface usually has its own schema, search engines that
accept strings of natural language (like English or Spanish) words and match them with
document at specific site.
5. Speech input and output.
Limited use of speech as an input query and speech as an answer to a question or result of
a request is becoming commonplace.
6. Interfaces for parametric users.
Parametric users, such as bank tellers, often have a small set of operations that they must
perform .
7. Interface for the DBA.
Most database system contain privileged commands that can be used only by the DBA’s staff.

THE DATABASE SYSTEM ENVIRONMENT:

DBMS component modules.

The component is divided into 2 halves,the top half refers to the various users and their
interfaces the lower half responsible for storage of data and processing transaction.

The database and DBMS catalog are stored on disc and controlled by operating system.
Stored data manager model control access to DBMS information stored on the disc.
Let us consider the top half of the diagram:

1.DBA staff : Works on defining the database and changes to the definition is performed by
using DDL and previlized commands. The DDL compiler , process the definition and store
descriptions in the catalog.

2.Casual users or occasional users need different type of information each time. The interactive
query are parsed compile analyzed by query compiler and subjected to query optimizer to
rearrange and reorder the operations.

3.Application programer write program in host language and compiled by precompiler to

extract DML commands, these commands are compiled by DML compiler to object code.

4. Parametric end users:who uses the parameters to the canned transaction, so they can run
repeatedly as separate transaction.

The lower half, the run time data processor execute :

1.Previlized commands
2.Executable query
3.canned transaction with run time parameters

Database System Utilities:

DBMSs have database utilities that help the DBA manage the database system.
Common utilities have the following types of functions:
 Loading. A loading utility is used to load existing data files—such as text
files or sequential files—into the database.

 Backup. A backup utility creates a backup copy of the database, usually by

dumping the entire database onto tape or other mass storage medium

 Database storage reorganization. This utility can be used to reorganize a set

of database files into different file organizations, and create new access paths
to improve performance.

 Performance monitoring. Such a utility monitors database usage and provides

statistics to the DBA. This statistics makes decisions of indexes to improve performance.

Centralized and Client-Server Architectures

Centralized DBMS: combines everything into single system including- DBMS
software, hardware, application programs and user interface processing software

Basic Client-Server Architectures

• Specialized Servers with Specialized functions
• Clients
• DBMS Server

Specialized Servers with Specialized functions

• File Servers
• Printer Servers
• Web Servers
• E-mail Servers
Clients:
• Provide appropriate interfaces and a client-version of the system to access and utilize the
server resources.
• Clients maybe diskless machines or PCs or Workstations with disks with only the client
software installed.
Connected to the servers via some form of a network.
(LAN: local area network, wireless network, etc.)

DBMS Server:
• Provides database query and transaction services to the clients
• Sometimes called query and transaction servers

Two Tier Client-Server Architecture:

• User Interface Programs and Application Programs run on the client side
Interface called ODBC (Open Database Connectivity – see Ch 9) provides an Application
program interface (API) allow client side programs to call the DBMS. Most DBMS vendors
provide ODBC drivers.
• A client program may connect to several DBMSs.
Other variations of clients are possible: e.g., in some DBMSs, more functionality is transferred to
clients including data dictionary functions, optimization and recovery across multiple servers,
etc. In such situations the server may be called the Data Server

Three Tier Client-Server Architecture

• Common for Web applications
• Intermediate Layer called Application Server or Web Server:
• stores the web connectivity software and the rules and business logic
(constraints) part of the application used to access the right amount of data from
the database server
• acts like a conduit for sending partially processed data between the database
server and the client.
• Additional Features- Security:
• encrypt the data at the server before transmission
decrypt data at the client
CLASSIFICATION OF DBMS:

DBMS is classified into the following criteria:

1. Based on Data Model.
2. Based on number of users.
3. Based on number of sites.
4. Based on cost.
5. Based on types of access path.

1. Based on data model: DBMS can be categorized as relational, object, network, hierarchical
data model.

• Relational data model: Represents the collection of relations(tables).

• Object data model: It defines in terms of objects, properties and operations on data base.
• Network: It represents as record type.
• Hierarchical data model: It represents data in tree structure.
• Object relational data model: It incorporates the concepts in object data base.

2. Based on number of users:

• Single user system: Supports only one user at a time and mostly used with PC.
• Multiuser system: It includes the mojority of DBMS support,concurrent multiple user.

3. Based on number of sites:

• centralized DBMS: data is stored in single computer and supports multiple users.
• Distributed DBMS: The actual database and software distributed over many sites connected
by a computer network.

4. Based on cost : Classification is based on cost and the majority of DBMS packages between
ten thousand dollars to one lakh dollars or none.

5. Based on types acess path for storing files:

• General Purpose DBMS: It is used for all applications.
• Special Purpose DBMS: It is designed and built for specific application.
Example: Airline Reservation, Telephone Dictionary.