What is Database

The Database is an essential part of our life. As we encounter several activities that
involve our interaction with databases, for example in the bank, in the railway station, in
school, in a grocery store, etc. These are the instances where we need to store a large
amount of data in one place and fetch these data easily. Data refers to raw, unprocessed facts and
figures without any context
Data : statistics it is raw and unprocessed . ex- name , class , marks etc.
information = when data is processed . ” record is also information “. example – pass or
fail table etc.
Database : an organized collection of data and information or interrelated data collected
at one place.
DBMS: Collection of interrelated data and set of program to access data example –
MySql, Oracle.
Application : Company Information , Account information, manufacturing, banking
,finance transactions, telecommunications.
A database is a collection of data that is organized, which is also called structured data. It
can be accessed or stored in a computer system. It can be managed through a Database
Management System (DBMS), a software used to manage data. Database refers to related
data in a structured form.
In a database, data is organized into tables consisting of rows and columns and it is
indexed so data can be updated, expanded, and deleted easily. Computer databases
typically contain file records data like transactions money in one bank account to another
bank account, sales and customer details, fee details of students, and product details.
There are different kinds of databases, ranging from the most prevalent approach, the
relational database, to a distributed database, cloud database, and NoSQL databases.
 Relational Database:
A relational database is made up of a set of tables with data that fits into a predefined
category.

 Distributed Database:
A distributed database is a database in which portions of the database are stored in
multiple physical locations, and in which processing is dispersed or replicated among
different points in a network.

 Cloud Database:
A cloud database is a database that typically runs on a cloud computing platform.
Database service provides access to the database. Database services make the
underlying software-stack transparent to the user.
These interactions are the example of a traditional database where data is of one type-that
is textual. In advancement of technology has led to new applications of database systems.
New media technology has made it possible to store images, video clips. These essential
features are making multimedia databases.
Nowadays, people are becoming smart – before taking any decisions they analyze facts
and figures related to it, which come from these databases. As the databases have made it
easier to manage information, we are able to catch criminals and do deep research.

DBMS
INTRODUCTION:
A Database Management System (DBMS) is a software system that is designed
to manage and organize data in a structured manner. It allows users to create,
modify, and query a database, as well as manage the security and access
controls for that database.

Some key features of a DBMS include:

1. Data modeling: A DBMS provides tools for creating and modifying data
models, which define the structure and relationships of the data in a
database.
2. Data storage and retrieval: A DBMS is responsible for storing and retrieving
data from the database, and can provide various methods for searching and
querying the data.
3. Concurrency control: A DBMS provides mechanisms for controlling
concurrent access to the database, to ensure that multiple users can access
the data without conflicting with each other.
4. Data integrity and security: A DBMS provides tools for enforcing data
integrity and security constraints, such as constraints on the values of data
and access controls that restrict who can access the data.
5. Backup and recovery: A DBMS provides mechanisms for backing up and
recovering the data in the event of a system failure.
6. DBMS can be classified into two types: Relational Database Management
System (RDBMS) and Non-Relational Database Management System
(NoSQL or Non-SQL)
7. RDBMS: Data is organized in the form of tables and each table has a set of
rows and columns. The data is related to each other through primary and
foreign keys.
8. NoSQL: Data is organized in the form of key-value pairs, document, graph,
or column-based. These are designed to handle large-scale, high-
performance scenarios.
Advantages of DBMS over File system
File System: A File Management system is a DBMS that allows access to single files or
tables at a time. In a File System, data is directly stored in a set of files. It contains flat
files that have no relation to other files (when only one table is stored in a single file, then
this file is known as a flat file).
DBMS: A Database Management System (DBMS) is application software that allows
users to efficiently define, create, maintain and share databases. Defining a database
involves specifying the data types, structures and constraints of the data to be stored in
the database. Creating a database involves storing the data on some storage medium that
is controlled by DBMS. Maintaining a database involves updating the database whenever
required to evolve and reflect changes in the miniworld and also generating reports for
each change. Sharing a database involves allowing multiple users to access the database.
DBMS also serves as an interface between the database and end users or application
programs. It provides control access to the data and ensures that data is consistent and
correct by defining rules on them.
An application program accesses the database by sending queries or requests for data to
the DBMS. A query causes some data to be retrieved from the database.

Advantages of DBMS over File system:

 Data redundancy and inconsistency: Redundancy is the concept of repetition of

data i.e. each data may have more than a single copy. The file system cannot control
the redundancy of data as each user defines and maintains the needed files for a
specific application to run. There may be a possibility that two users are maintaining
the data of the same file for different applications. Hence changes made by one user
do not reflect in files used by second users, which leads to inconsistency of data.
Whereas DBMS controls redundancy by maintaining a single repository of data that is
defined once and is accessed by many users. As there is no or less redundancy, data
remains consistent.
 Data sharing: The file system does not allow sharing of data or sharing is too
complex. Whereas in DBMS, data can be shared easily due to a centralized system.
 Data concurrency: Concurrent access to data means more than one user is accessing
the same data at the same time. Anomalies occur when changes made by one user get
lost because of changes made by another user. The file system does not provide any
procedure to stop anomalies. Whereas DBMS provides a locking system to stop
anomalies to occur.
 Data searching: For every search operation performed on the file system, a different
application program has to be written. While DBMS provides inbuilt searching
operations. The user only has to write a small query to retrieve data from the database.
 Data integrity: There may be cases when some constraints need to be applied to the
data before inserting it into the database. The file system does not provide any
procedure to check these constraints automatically. Whereas DBMS maintains data
integrity by enforcing user-defined constraints on data by itself.
 System crashing: In some cases, systems might have crashed due to various reasons.
It is a bane in the case of file systems because once the system crashes, there will be
no recovery of the data that’s been lost. A DBMS will have the recovery manager
which retrieves the data making it another advantage over file systems.
 Data security: A file system provides a password mechanism to protect the database
but how long can the password be protected? No one can guarantee that. This doesn’t
happen in the case of DBMS. DBMS has specialized features that help provide
shielding to its data.
 Backup: It creates a backup subsystem to restore the data if required.
 Interfaces: It provides different multiple user interfaces like graphical user interface
and application program interface.
 Easy Maintenance: It is easily maintainable due to its centralized nature.

Application of DBMS
There are different fields where a database management system is utilized. Following are
a few applications which utilize the information base administration framework –
1. Railway Reservation System –
In the rail route reservation framework, the information base is needed to store the
record or information of ticket appointments, status about train’s appearance, and
flight. Additionally, if trains get late, individuals become acquainted with it through
the information base update.

2. Library Management System –

There are lots of books in the library so; it is difficult to store the record of the relative
multitude of books in a register or duplicate. Along these lines, the data set
administration framework (DBMS) is utilized to keep up all the data identified with
the name of the book, issue date, accessibility of the book, and its writer.

3. Banking –
Database the executive’s framework is utilized to store the exchange data of the client
in the information base.

4. Education Sector –
Presently, assessments are led online by numerous schools and colleges. They deal
with all assessment information through the data set administration framework
(DBMS). In spite of that understudy’s enlistments subtleties, grades, courses,
 expense, participation, results, and so forth all the data is put away in the information
base.

5. Credit card exchanges –

The database Management framework is utilized for buying on charge cards and age
of month to month proclamations.

6. Social Media Sites –

We all utilization of online media sites to associate with companions and to impart
our perspectives to the world. Every day, many people group pursue these online
media accounts like Pinterest, Facebook, Twitter, and Google in addition to. By the
utilization of the data set administration framework, all the data of clients are put
away in the information base and, we become ready to interface with others.

7. Broadcast communications –
Without DBMS any media transmission organization can’t think. The Database the
executive’s framework is fundamental for these organizations to store the call
subtleties and month to month postpaid bills in the information base.

8. Account –
The information base administration framework is utilized for putting away data
about deals, holding and acquisition of monetary instruments, for example, stocks and
bonds in a data set.

9. Online Shopping –
These days, web-based shopping has become a major pattern. Nobody needs to visit
the shop and burn through their time. Everybody needs to shop through web based
shopping sites, (for example, Amazon, Flipkart, Snapdeal) from home. So all the
items are sold and added uniquely with the assistance of the information base
administration framework (DBMS). Receipt charges, installments, buy data these are
finished with the assistance of DBMS.

10. Human Resource Management –

Big firms or organizations have numerous specialists or representatives working
under them. They store data about worker’s compensation, assessment, and work with
the assistance of an information base administration framework (DBMS).

11. Manufacturing –
Manufacturing organizations make various kinds of items and deal them consistently.
To keep the data about their items like bills, acquisition of the item, amount,
inventory network the executives, information base administration framework
(DBMS) is utilized.
12. Airline Reservation System –
This framework is equivalent to the railroad reservation framework. This framework
additionally utilizes an information base administration framework to store the
records of flight takeoff, appearance, and defer status.
13. Healthcare: DBMS is used in healthcare to manage patient data, medical records,
and billing information.
14. Data retrieval: DBMS provides a way to retrieve data quickly and easily using
search queries.
15. Data manipulation: DBMS provides tools to manipulate data, such as sorting,
filtering, and aggregating data.
16. Security: DBMS provides security features to ensure that only authorized users have
access to the data.
17. Data backup and recovery: DBMS provides tools to back up data and recover it in
case of system failures or data loss.
18. Multi-user access: DBMS allows multiple users to access and modify data
simultaneously.
19. Reporting and analysis: DBMS provides tools to generate reports and analyze data
to gain insights and make informed decisions.

Different types of Database Users

Database users are categorized based up on their interaction with the database. These are
seven types of database users in DBMS.
1. Database Administrator (DBA) : Database Administrator (DBA) is a person/team
who defines the schema and also controls the 3 levels of database. The DBA will then
create a new account id and password for the user if he/she need to access the
database. DBA is also responsible for providing security to the database and he allows
only the authorized users to access/modify the data base. DBA is responsible for the
problems such as security breaches and poor system response time.
 DBA also monitors the recovery and backup and provide technical support.
 The DBA has a DBA account in the DBMS which called a system or superuser
account.
 DBA repairs damage caused due to hardware and/or software failures.
 DBA is the one having privileges to perform DCL (Data Control Language)
operations such as GRANT and REVOKE, to allow/restrict a particular user from
accessing the database.
2. Naive / Parametric End Users : Parametric End Users are the unsophisticated who
don’t have any DBMS knowledge but they frequently use the database applications in
their daily life to get the desired results. For examples, Railway’s ticket booking users
are naive users. Clerks in any bank is a naive user because they don’t have any DBMS
knowledge but they still use the database and perform their given task.

3. System Analyst :
System Analyst is a user who analyzes the requirements of parametric end users. They
check whether all the requirements of end users are satisfied.

4. Sophisticated Users : Sophisticated users can be engineers, scientists, business

analyst, who are familiar with the database. They can develop their own database
applications according to their requirement. They don’t write the program code but
they interact the database by writing SQL queries directly through the query
processor.

5. Database Designers : Data Base Designers are the users who design the structure of
database which includes tables, indexes, views, triggers, stored procedures and
constraints which are usually enforced before the database is created or populated
with data. He/she controls what data must be stored and how the data items to be
related. It is responsibility of Database Designers to understand the requirements of
different user groups and then create a design which satisfies the need of all the user
groups.

6. Application Programmers : Application Programmers also referred as System

Analysts or simply Software Engineers, are the back-end programmers who writes the
code for the application programs. They are the computer professionals. These
programs could be written in Programming languages such as Visual Basic,
Developer, C, FORTRAN, COBOL etc. Application programmers design, debug, test,
and maintain set of programs called “canned transactions” for the Naive (parametric)
users in order to interact with database.

7. Casual Users / Temporary Users : Casual Users are the users who occasionally
use/access the database but each time when they access the database they require the
new information, for example, Middle or higher level manager.

Structured Query Language (SQL)

 Structured Query Language is a standard Database language which is used to create,
maintain and retrieve the relational database. Following are some interesting facts about
SQL.
 SQL is case insensitive. But it is a recommended practice to use keywords (like
SELECT, UPDATE, CREATE, etc) in capital letters and use user defined things
(liked table name, column name, etc) in small letters.
 We can write comments in SQL using “–” (double hyphen) at the beginning of any
line.
 SQL is the programming language for relational databases (explained below) like
MySQL, Oracle, Sybase, SQL Server, Postgre, etc. Other non-relational databases
(also called NoSQL) databases like MongoDB, DynamoDB, etc do not use SQL
 Although there is an ISO standard for SQL, most of the implementations slightly vary
in syntax. So we may encounter queries that work in SQL Server but do not work in
MySQL.

SQL Datatype
o SQL Datatype is used to define the values that a column can contain.
o Every column is required to have a name and data type in the database table.

1.Binary data types

2.Approximation numeric data type
Datatype of SQL: 3.Exact number data type
4.Character string data type
5.Date and time data types
1. Binary Datatypes
There are Three types of binary Datatypes which are given below:

Data Type Description

binary It has a maximum length of 8000 bytes. It contains fixed-length binary data.

varbinary It has a maximum length of 8000 bytes. It contains variable-length binary data.
image It has a maximum length of 2,147,483,647 bytes. It contains variable-length binary data.

2. Approximate Numeric Datatype :

The subtypes are given below:

Data type From To Description

float -1.79E + 308 1.79E + 308 It is used to specify a floating-point value e.g. 6.2, 2.9 etc.

real -3.40e + 38 3.40E + 38 It specifies a single precision floating point number

3. Exact Numeric Datatype

The subtypes are given below:

Data type Description

int It is used to specify an integer value.

smallint It is used to specify small integer value.

bit It has the number of bits to store.

decimal It specifies a numeric value that can have a decimal number.

numeric It is used to specify a numeric value.

4. Character String Datatype

The subtypes are given below:
Data Description
type

char It has a maximum length of 8000 characters. It contains Fixed-length non-unicode characters.

varchar It has a maximum length of 8000 characters. It contains variable-length non-unicode characters.

text It has a maximum length of 2,147,483,647 characters. It contains variable-length non-unicode

characters.

5. Date and time Datatypes

The subtypes are given below:

Datatype Description

date It is used to store the year, month, and days value.

time It is used to store the hour, minute, and second values.

timestamp It stores the year, month, day, hour, minute, and the second value

SQL | DDL, DQL, DML, DCL and TCL

Commands
Structured Query Language (SQL) as we all know is the database language by the use of
which we can perform certain operations on the existing database and also we can use this
language to create a database. SQL uses certain commands like Create, Drop, Insert, etc.
to carry out the required tasks.
These SQL commands are mainly categorized into five categories as:
1. DDL – Data Definition Language
2. DQL – Data Query Language
3. DML – Data Manipulation Language
4. DCL – Data Control Language
5. TCL – Transaction Control Language
Now, we will see all of these in detail.

DDL (Data Definition Language):

DDL or Data Definition Language 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:
 CREATE: This command is used to create the database or its objects (like table, index,
function, views, store procedure, and triggers).
 DROP: This command is used to delete objects from the database.
 ALTER: This is used to alter the structure of the database.
 TRUNCATE: This is used to remove all records from a table, including all spaces
allocated for the records are removed.
 COMMENT: This is used to add comments to the data dictionary.
 RENAME: This is used to rename an object existing in the database.

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:
 SELECT: It is used to retrieve data from the database.

DML(Data Manipulation Language):

The SQL commands that deals with the manipulation of data present in the database belong
to DML or Data Manipulation Language and this includes most of the SQL statements. 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:
 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 records from a database table.
 LOCK: Table control concurrency.
 CALL: Call a PL/SQL or JAVA subprogram.
 EXPLAIN PLAN: It describes the access path to data.

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:
 GRANT: This command gives users access privileges to the database.
 REVOKE: This command withdraws the user’s access privileges given by using the
GRANT command.
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. Hence, the following TCL commands are used to control the execution of a
transaction:
 BEGIN: Opens a Transaction.
 COMMIT: Commits a Transaction.
 ROLLBACK: Rollbacks a transaction in case of any error occurs.
 SAVEPOINT: Sets a save point within a transaction.
 SET TRANSACTION: Specifies characteristics for the transaction.