DATABASE MANAGEMENT SYSTEM AND RDBMS

Unit 1

MUHAMMED MASHAHIL P
ASSISTANT PROFESSOR
CAS VAZHAKKAD
Database
• 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 Management System

• Database management system is a software which is used to manage the
database.

Database System
• Database management system and database together is called database
system
 Database Management System
Database management system is a software which is used to manage the
database. For example: MySQL, Oracle, etc are a very popular commercial
database which is used in different applications.
• DBMS provides an interface to perform various operations like database
creation, storing data in it, updating data, creating a table in the database and
a lot more.
• It provides protection and security to the database. In the case of multiple
users, it also maintains data consistency.
DBMS allows users the following tasks:
• Data Definition: It is used for creation, modification, and removal of definition
that defines the organization of data in the database.
• Data Updation: It is used for the insertion, modification, and deletion of the
actual data in the database.
• Data Retrieval: It is used to retrieve the data from the database which can be
used by applications for various purposes.
• User Administration: It is used for registering and monitoring users, maintain
data integrity, enforcing data security, dealing with concurrency control,
monitoring performance and recovering information corrupted by unexpected
failure.
 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
 Applications
Databases are widely used. Here are some representative applications:
 Enterprise Information :-
– Sales: For customer, product, and purchase information.
– Accounting: For payments, receipts, account balances, assets and other accounting information.
– Human resources: For information about employees, salaries, payroll taxes, and benefits, and for
generation of paychecks.
– Manufacturing: For management of the supply chain and for tracking production of items in factories,
inventories of items in warehouses and stores, and orders for items.
 Online retailers: For sales data noted above plus online order tracking, generation of recommendation
lists, and maintenance of online product evaluations.
 Banking and Finance ◦
– Banking: For customer information, accounts, loans, and banking transactions. ◦
– Credit card transactions: For purchases on credit cards and generation of monthly statements.
– Finance: For storing information about holdings, sales, and purchases of financial instruments such
as stocks and bonds; also for storing real-time market data to enable online trading by customers and
automated trading by the firm.
 Universities: For student information, course registrations, and grades (in addition to standard enterprise
information such as human resources and accounting).
 Airlines: For reservations and schedule information. Airlines were among the first to use databases in a
geographically distributed manner.
 Telecommunication: For keeping records of calls made, generating monthly bills, maintaining balances on
prepaid calling cards, and storing information about the communication networks.
Advantages of DBMS Disadvantages of DBMS
• Controls database redundancy: It can control data redundancy • Cost of Hardware and
because it stores all the data in one single database file and that Software: It requires a high
recorded data is placed in the database. speed of data processor and
• Data sharing: In DBMS, the authorized users of an organization large memory size to run
can share the data among multiple users. DBMS software.
• Easily Maintenance: It can be easily maintainable due to the • Size: It occupies a large
centralized nature of the database system.
space of disks and large
• Reduce time: It reduces development time and maintenance memory to run them
need. efficiently.
• Backup: It provides backup and recovery subsystems which create
automatic backup of data from hardware and software failures
• Complexity: Database
and restores the data if required. system creates additional
• multiple user interface: It provides different types of user
complexity and
interfaces like graphical user interfaces, application program requirements.
interfaces • Higher impact of
• Improved data security: Having complete authority over the failure: Failure is highly
operational data, enables the DBA in ensuring that the only mean impacted the database
of access to the database is through proper channels. The DBA can because in most of the
define authorization checks to be carried out whenever access to organization, all the data
sensitive data is attempted. stored in a single database
• Data Independence :Ina database system, the database and if the database is
management system provides the interface between the damaged due to electric
application programs and the data. When changes are made to failure or database
the data representation, the meta data obtained by the DBMS is corruption then the data
changed but the DBMS is continues to provide the data to
may be lost forever.
application program in the previously used way
 DBMS vs. File System
DBMS File System
• DBMS is a collection of data. In DBMS, the • File system is a collection of data. In this
user is not required to write the system, the user has to write the
procedures. procedures for managing the database.
• DBMS gives an abstract view of data that • File system provides the detail of the data
hides the details. representation and storage of data.
• DBMS provides a crash recovery • File system doesn't have a crash
mechanism, i.e., DBMS protects the user mechanism, i.e., if the system crashes
from the system failure. while entering some data, then the
• DBMS provides a good protection content of the file will lost.
mechanism. • It is very difficult to protect a file under
• DBMS contains a wide variety of the file system.
sophisticated techniques to store and • File system can't efficiently store and
retrieve the data. retrieve the data.
• DBMS takes care of Concurrent access of • In the File system, concurrent access has
data using some form of locking. many problems like redirecting the file
while other deleting some information or
updating some information
 Data Models
• Data models define how the logical structure of a database is modelled.
Data Models are fundamental entities to introduce abstraction in a DBMS.
• Data models define how data is connected to each other and how they
are processed and stored inside the system.
– ER MODEL
– RELATIONAL DATA MODEL
– OBJECT ORIENTED DATA MODEL
– SEMI STRUCTURED DATA MODEL
 Entity-Relationship Model
Entity-Relationship (ER) Model is based on the notion of real-world entities
and relationships among them. While formulating real-world scenario into
the database model, the ER Model creates entity set, relationship set, general
attributes and constraints.
ER Model is best used for the conceptual design of a database.
ER Model is based on −
– Entity − An entity in an ER Model is a real-world entity having properties
called attributes. Every attribute is defined by its set of values called domain.
For example, in a school database, a student is considered as an entity.
Student has various attributes like name, age, class, etc.
– Relationship − The logical association among entities is called relationship.
Relationships are mapped with entities in various ways. Mapping cardinalities
define the number of association between two entities.
Mapping cardinalities −
– one to one
– one to many
– many to one
– many to many
 Relational Model
• The most popular data model in DBMS is the Relational Model. It is more scientific a
model than others. The relational model is an example of a record-based model.
Record-based models are so named because 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. The columns of the table
correspond to the attributes of the record type

The main highlights of this model are −

• Data is stored in tables called relations.
• Relations can be normalized.
• In normalized relations, values saved are atomic values.
• Each row in a relation contains a unique value.
• Each column in a relation contains values from a same domain.
 Object-oriented database model
• Object-oriented programming (especially in Java, C++, or C#) has become the dominant software-
development methodology. This led to the development of an object-oriented data model that can be seen
as extending the E-R model with notions of encapsulation, methods (functions), and object identity. The
object-relational data model combines features of the object-oriented data model and relational data
model. This model defines a database as a collection of objects, or reusable software elements, with
associated features and methods.
There are several kinds of object-oriented databases:
• A multimedia database incorporates media, such as images, that could not be stored in a relational
database.
• A hypertext database allows any object to link to any other object. It’s useful for organizing lots of disparate
data, but it’s not ideal for numerical analysis.
• The object-oriented database model is the best known post-relational database model, since it incorporates
tables, but isn’t limited to tables. Such models are also known as hybrid database models.

Semi structured model

In this model, the structural data usually contained in the database schema is embedded
with the data itself. The semistructured data model permits the specification of data
where individual data items of the same type may have different sets of attributes. This
is in contrast to the data models mentioned earlier, where every data item of a
particular type must have the same set of attributes. The Extensible Markup Language
(XML) is widely used to represent semistructured data
Instance
• The data stored in database at a particular moment of time is called instance of database.
• Database schema defines the variable declarations in tables that belong to a particular
database; the value of these variables at a moment of time is called the instance of that
database.

Schema
• A database schema is the skeleton structure that represents the logical view of
the entire database.
• It defines how the data is organized and how the relations among them are
associated. It formulates all the constraints that are to be applied on the data.
• A database schema defines its entities and the relationship among them.
• It contains a descriptive detail of the database, which can be depicted by means
of schema diagrams.
• It’s the database designers who design the schema to help programmers
understand the database and make it useful.
• schemas are changed infrequently
• Schemas partitioned according to the levels of abstraction
– Physical schema: database design at the physical level
– Logical schema: database design at the logical level
– external schema or subschema is the highest level of a schema which defines the views
for the end users.
Schema Instance
• A schema is the design • whereas instance is the
representation of a snapshot of a database at a
database. particular moment
• changes in schema occurs • Instance changes very
rarely. frequently, whenever data
is removed or added in the
database.
 Data Abstraction

Database systems are made-up of complex data structures. To ease the user
interaction with database, the developers hide internal irrelevant details from
users. This process of hiding irrelevant details from user is called data
abstraction.
We have three levels of abstraction:
• Physical level: This is the lowest level of data abstraction. It describes how
data is actually stored in database. You can get the complex data structure
details at this level.
• Logical level: This is the middle level of 3-level data abstraction
architecture. It describes what data is stored in database.
• View level: Highest level of data abstraction. This level describes the user
interaction with database system.
Example: Let’s say we are storing customer
information in a customer table.
• At physical level these records can be
described as blocks of storage (bytes,
gigabytes, terabytes etc.) in memory. These
details are often hidden from the
programmers.
• At the logical level these records can be
described as fields and attributes along with
their data types, their relationship among
each other can be logically implemented. The
programmers generally work at this level
because they are aware of such things about
database systems.
• At view level, user just interact with system
with the help of GUI and enter the details at
the screen, they are not aware of how the
data is stored and what data is stored; such
details are hidden from them.
Three schema architecture

Mapping is used to transform the

request and response between
various database levels of
architecture.
Mapping is not good for small
DBMS because it takes more time.
In External / Conceptual mapping,
it is necessary to transform the
request from external level to
conceptual schema.
In Conceptual / Internal mapping,
DBMS transform the request from
the conceptual to internal level.
1. Internal Level
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- 3. External Level
level data structures in detail. 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
2. Conceptual Level schema.
The conceptual schema describes the design of a Each view schema describes the database
database at the conceptual level. Conceptual level is part that a particular user group is
also known as logical level.
The conceptual schema describes the structure of
interested and hides the remaining
the whole database. database from that user group.
The conceptual level describes what data are to be The view schema describes the end user
stored in the database and also describes what
relationship exists among those data. interaction with database systems.
In the conceptual level, internal details such as an
implementation of the data structure are hidden.
Programmers and database administrators work at
this level.
 Mapping

• Process of transforming request and results between three level it's called
mapping.
• There are the two types of mappings:
• Conceptual/Internal Mapping
• External/Conceptual Mapping
 1. Conceptual/Internal Mapping:
• The conceptual/internal mapping defines the correspondence between
the conceptual view and the store database.
• It specifies how conceptual record and fields are represented at the
internal level.
• It relates conceptual schema with internal schema.
• If structure of the store database is changed.
• If changed is made to the storage structure definition-then the
conceptual/internal mapping must be changed accordingly, so that the
conceptual schema can remain invariant.
• There could be one mapping between conceptual and internal levels.
 2. External/Conceptual Mapping:
• The external/conceptual mapping defines the
correspondence between a particular external view and
conceptual view.
• It relates each external schema with conceptual schema.
• The differences that can exist between these two levels are
analogous to those that can exist between the conceptual
view and the stored database.
• Example: fields can have different data types; fields and
record name can be changed; several conceptual fields can
be combined into a single external field.
• Any number of external views can exist at the same time;
any number of users can share a given external view:
different external views can overlap.
• There could be several mapping between external and
conceptual levels.
 Data Independence
• The three schema architecture further explains the concept of data independence, the
capacity to change the schema at one level without having to change the schema at the
next higher level.
• Metadata itself follows a layered architecture, so that when we change data at one layer,
it does not affect the data at another level. This data is independent but mapped to each
other.
• There are two types of data independence:
– 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.
– 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.
 Transaction management
• A transaction is a logical unit of processing in a DBMS which entails one or
more database access operation.
• In a nutshell, database transactions represent real-world events of any
enterprise.
• All types of database access operation which are held between the
beginning and end transaction statements are considered as a single
logical transaction.
• During the transaction the database is inconsistent. Only once the
database is committed the state is changed from one consistent state to
another.
 Facts about Database Transactions

• A transaction is a program unit whose execution may or may not change

the contents of a database.
• The transaction is executed as a single unit
• If the database operations do not update the database but only retrieve
data, this type of transaction is called a read-only transaction.
• A successful transaction can change the database from one CONSISTENT
STATE to another
• DBMS transactions must be atomic, consistent, isolated and durable
• If the database were in an inconsistent state before a transaction, it would
remain in the inconsistent state after the transaction.
States
 ACID Properties
For maintaining the integrity of data, the DBMS system you have to ensure
ACID properties. ACID stands for Atomicity, Consistency, Isolation,
and Durability.
• Atomicity: A transaction is a single unit of operation. You either execute it
entirely or do not execute it at all. There cannot be partial execution.
• Consistency: Once the transaction is executed, it should move from one
consistent state to another.
• Isolation: Transaction should be executed in isolation from other
transactions (no Locks). During concurrent transaction execution,
intermediate transaction results from simultaneously executed
transactions should not be made available to each other. (Level 0,1,2,3)
• Durability: · After successful completion of a transaction, the changes in
the database should persist. Even in the case of system failures.
• 1-tier architecture
• 2-tier architecture
• 3-tier architecture
 1-tier architecture:
One-tier architecture involves putting all of the required
components for a software application or technology on a single
server or platform.

Basically, a one-tier architecture keeps all of the

elements of an application, including the
interface, Middleware and back-end data, in
one place.
Developers see these types of systems as the
simplest and most direct way.
2-tier architecture:
• The two-tier is based
on Client Server
architecture.
• The direct
communication takes
place between client
and server.
• There is no
intermediate
between client and
server.
3-tier architecture:
• Database (Data) Tier − At this tier, the database resides along with its query
processing languages. We also have the relations that define the data and their
constraints at this level.
• Application (Middle) Tier − At this tier reside the application server and the
programs that access the database. For a user, this application tier presents an
abstracted view of the database. End users are unaware of any existence of the
database beyond the application. At the other end, the database tier is not aware
of any other user beyond the application tier. Hence, the application layer sits in
the middle and acts as a mediator between the end-user and the database.
• User (Presentation) Tier − End-users operate on this tier and they know nothing
about any existence of the database beyond this layer. At this layer, multiple views
of the database can be provided by the application. All views are generated by
applications that reside in the application tier.
System architecture:
 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
 Naive users :- are unsophisticated users who interact with the system by invoking
 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.
 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
 Database Administrator:-One of the main reasons for using DBMSs is to have central
control of both the data and the programs that access those data. A person who has
such central control over the system is called a database administrator (DBA) .The
functions of a DBA are
 The functions of a DBA , Storage structure and access-method definition, Schema and physical-
organization modification, Granting of authorization for data access, Routine maintenance…etc
 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.
 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. 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.
 DISK STORAGE
• The storage manager implements several data structures as
part of the physical system implementation:
– Data files, which store the database itself.
– Data dictionary, which stores metadata about the structure of the
database, in particular the schema of the database.
– Indices, which provide fast access to data items that hold particular
values.
– Statistical data:-to store statistical information about data in database.
This information used by the query processor to select efficient way to
execute query.
 Database Language
• 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.
 1. Data Definition Language

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.
 2. Data Manipulation Language

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.
 3. Data Control Language
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.

4. Transaction Control Language

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.
 DBMS Interfaces
The application poses with the help of SQL, a query language, a query to the database system.Types of
interfaces provided by the DBMS include:
Menu-Based Interfaces for Web Clients or Browsing
Present users with list of options (menus)
Lead user through formulation of request
Query is composed of selection options from menu displayed by system.
Forms-Based Interfaces
Displays a form to each user.
User can fill out form to insert new data or fill out only certain entries.
Designed and programmed for naïve users as interfaces to canned transactions.
Graphical User Interfaces
Displays a schema to the user in diagram form. The user can specify a query by manipulating the diagram. GUIs
use both forms and menus
Natural Language Interfaces
Accept requests in written English, or other languages and attempt to understand them.
Interface has its own schema, and a dictionary of important words. Uses the schema and dictionary to interpret a
natural language request.
Interfaces for Parametric Users
Parametric users have small set of operations they perform.
Analysts and programmers design and implement a special interface for each class of naïve users.
Often a small set of commands included to minimize the number of keystrokes required. (I.e. function keys)
Interfaces for the DBA
Systems contain privileged commands only for DBA staff.
Include commands for creating accounts, setting parameters, authorizing accounts, changing the schema,
reorganizing the storage structures etc.
END…..