DBMS NOTES

What is a Database?
A database is a collection of related data which represents some aspect of the
real world. A database system is designed to be built and populated with data
for a certain task.

What is DBMS?
Database Management System (DBMS) is a software for storing and retrieving
users’ data while considering appropriate security measures. It consists of a
group of programs which manipulate the database. The DBMS accepts the
request for data from an application and instructs the operating system to
provide the specific data. In large systems, a DBMS helps users and other third-
party software to store and retrieve data.
DBMS allows users to create their own databases as per their requirement. The
term “DBMS” includes the user of the database and other application programs.
It provides an interface between the data and the software application.

DBMS Flat File Management System

Multi-user access It does not support multi-user access
Design to fulfill the need for small and It is only limited to smaller DBMS
large businesses system.
Remove redundancy and Integrity Redundancy and Integrity issues
Expensive. But in the long term Total
It’s cheaper
Cost of Ownership is cheap
Easy to implement complicated No support for complicated
transactions transactions

Tha main Four Types of Database Management System are:

 Hierarchical database
 Network database
 Relational database
 Object-Oriented database

Hierarchical DBMS
In a Hierarchical database, model data is organized in a tree-like structure. Data
is Stored Hierarchically (top down or bottom up) format. Data is represented
using a parent-child relationship. In Hierarchical DBMS parent may have many
children, but children have only one parent.

Network Model
The network database model allows each child to have multiple parents. It
helps you to address the need to model more complex relationships like as the
orders/parts many-to-many relationship. In this model, entities are organized in
a graph which can be accessed through several paths.

Relational Model
Relational DBMS is the most widely used DBMS model because it is one of the
easiest. This model is based on normalizing data in the rows and columns of the
tables. Relational model stored in fixed structures and manipulated using SQL.

Object-Oriented Model
In Object-oriented Model data stored in the form of objects. The structure which
is called classes which display data within it. It is one of the components of
DBMS that defines a database as a collection of objects which stores both data
members values and operations.

Advantages of DBMS
 DBMS offers a variety of techniques to store & retrieve data
 DBMS serves as an efficient handler to balance the needs of multiple
applications using the same data
 Uniform administration procedures for data
 Application programmers never exposed to details of data representation
and storage.
 A DBMS uses various powerful functions to store and retrieve data
efficiently.
 Offers Data Integrity and Security
 The DBMS implies integrity constraints to get a high level of protection
against prohibited access to data.
 A DBMS schedules concurrent access to the data in such a manner that
only one user can access the same data at a time
 Reduced Application Development Time

Disadvantage of DBMS
DBMS may offer plenty of advantages but, it has certain flaws-

 Cost of Hardware and Software of a DBMS is quite high which increases

the budget of your organization.
 Most database management systems are often complex systems, so the
training for users to use the DBMS is required.
 In some organizations, all data is integrated into a single database which
can be damaged because of electric failure or database is corrupted on
the storage media
 Use of the same program at a time by many users sometimes lead to the
loss of some data.
 DBMS can’t perform sophisticated calculations

When not to use a DBMS system?

Although, DBMS system is useful. It is still not suited for specific task mentioned
below:

Not recommended when you do not have the budget or the expertise to operate
a DBMS. In such cases, Excel/CSV/Flat Files could do just fine.

What is a DBA?
Short for database administrator, a DBA designs, implements,
administers, and monitors data management systems and
ensures design, consistency, quality, and security.
The day-to-day activities that a DBA performs as outlined
in ITIL® Service Operation include:

 Creating and maintaining database standards and

policies
 Supporting database design, creation, and testing
activities
 Managing the database availability and
performance, including incident and
problem management
 Administering database objects to achieve optimum
utilization
 Defining and implementing event triggers that will alert
on potential database performance or integrity issues
 Performing database housekeeping, such as tuning,
indexing, etc.
  Monitoring usage, transaction volumes, response times,
concurrency levels, etc.
 Identifying reporting, and managing database security
issues, audit trails, and forensics
 Designing database backup, archiving, and storage
strategy

Difference Between Two-Tier And Three-Tier database

architecture

 1. Two-Tier Database Architecture –

In two-tier, the application logic is either buried inside the User
Interface on the client or within the database on the server (or both).
With two-tier client/server architectures, the user system interface is
usually located in the user’s desktop environment and the database
management services are usually in a server that is a more powerful
machine that services many clients.
 2. Three-Tier Database Architecture –
In three-tier, the application logic or process lives in the middle-tier, it
is separated from the data and the user interface. Three-tier systems
are more scalable, robust and flexible. In addition, they can integrate
data from multiple sources. In the three-tier architecture, a middle tier
was added between the user system interface client environment and
the database management server environment. There are a variety of
ways of implementing this middle tier, such as transaction processing
monitors, message servers, or application servers.

Types of database languages

Here are four types of database languages and their uses:

1. Data definition language (DDL)

Data definition language (DDL) creates the framework of the database by

specifying the database schema, which is the structure that represents the
organization of data. Its common uses include the creation and alteration of
tables, files, indexes and columns within the database. This language also
allows users to rename or drop the existing database or its components. Here's
a list of DDL statements:
  CREATE: Creates a new database or object, such as a table, index
or column
 ALTER: Changes the structure of the database or object
 DROP: Deletes the database or existing objects
 RENAME: Renames the database or existing objects
2. Data manipulation language (DML)

Data manipulation language (DML) provides operations that handle user

requests, offering a way to access and manipulate the data that users store
within a database. Its common functions include inserting, updating and
retrieving data from the database. Here's a list of DML statements:

 INSERT: Adds new data to the existing database table

 UPDATE: Changes or updates values in the table
 DELETE: Removes records or rows from the table
 SELECT: Retrieves data from the table or multiple tables
3. Data control language (DCL)

Data control language (DCL) controls access to the data that users store within
a database. Essentially, this language controls the rights and permissions of the
database system. It allows users to grant or revoke privileges to the database.
Here's a list of DCL statements:

 GRANT: Gives a user access to the database

 REVOKE: Removes a user's access to the database
4. Transaction control language (TCL)

Transaction control language (TCL) manages the transactions within a

database. Transactions group a set of related tasks into a single, executable
task. All the tasks must succeed in order for the transaction to work. Here's a list
of TCL statements:

 COMMIT: Carries out a transaction

 ROLLBACK: Restores a transaction if any tasks fail to execute
 S**AVEPOINT**: Sets a point in a transaction to save

Difference between Schema and Instance in DBMS

1. Instances :
Instances are the collection of information stored at a particular moment. The
instances can be changed by certain CRUD operations as like addition,
deletion of data. It may be noted that any search query will not make any
kind of changes in the instances.
Example –
Let’s say a table teacher in our database whose name is School, suppose
the table has 50 records so the instance of the database has 50 records for
now and tomorrow we are going to add another fifty records so tomorrow the
instance have total 100 records. This is called an instance.
2. Schema :
Schema is the overall description of the database. The basic structure of
how the data will be stored in the database is called schema.

Schema is of three types: Logical Schema, Physical Schema and view

Schema.
1. Logical Schema – It describes the database designed at logical
level.
2. Physical Schema – It describes the database designed at physical
level.
3. View Schema – It defines the design of the database at the view
level.

Subschema

A subschema provides a view of the database as seen by an application

program. This view is often a subset of the complete schema definition. A
subschema is used at runtime to provide the DBMS with a description of
those portions of the database that are accessible to the application program.

Data Abstraction
Data Abstraction refers to the process of hiding irrelevant
details from the user. So, what is the meaning of irrelevant
details

There are mainly three levels of data abstraction and we

divide it into three levels in order to achieve Data
Independence. Data Independence means users and data
should not directly interact with each other. The user
should be at a different level and the data should be
present at some other level. By doing so, Data
Independence can be achieved. So, let's see in details
what are these three levels of data abstraction:

1. View Level
2. Conceptual Level
3. Physical Level

View Level or External Schema

This level tells the application about how the data should
be shown to the user.

Refrential Integrity

Referential Integrity Rule in DBMS is based on Primary and Foreign Key.

The Rule defines that a foreign key have a matching primary key.
Reference from a table to another table should be valid.
Referential Integrity Rule example −
<Employee>

EMP_ID EMP_NAME DEPT_ID

<Department>

DEPT_ID DEPT_NAME DEPT_ZONE

The rule states that the DEPT_ID in the Employee table has a matching
valid DEPT_ID in the Department table.
To allow join, the referential integrity rule states that the Primary Key and
Foreign Key have same data types.

Conceptual Level or Logical Level

This level tells how the data is actually stored and
structured. We have different data models by which we
can store the data.