PRACTICAL – 1

Introduction to DBMS.

Database Management Software (DBMS)

A Database Management Software (DBMS) is used for storing, manipulating, and managing
data, such as format, names of fields, and record and file structures in a database. Users can
construct their own databases using a DBMS to satisfy their business requirements. For
example, dBase was one of the first DBMS for the micro-computers.

To interact with a database, a DBMS package generally uses SQL queries. It receives a
command from a database administrator (DBA) and prompts the system to perform the
necessary action. These instructions can be about loading, retrieving, or modifying existing
data in the system.

Data and Information

Popular Database Management Software (DBMS)

Database management software features data independence, as the storage mechanism and
formats can be changed without altering the entire application within the database. Some
common examples of popular, free business database software include MySQL, Microsoft
SQL Server, Microsoft Access, Oracle, IBM DB2, and FoxPro. For example, MySQL, free
business database software, is a high-performance database software that helps enterprise
users build scalable database applications.

[Type here]
Main Features of a DBMS

Some of the significant features of database management software include:

· Low Repetition and Redundancy

In a database, the chances of data duplication are quite high as several users use one database.
A DBMS reduces data repetition and redundancy by creating a single data repository that can
be accessed by multiple users, even allowing easy data mapping while performing ETL.

· Easy Maintenance of Large Databases

Most organizational data are stored in large databases. A DBMS helps maintain these
databases by enforcing user-defined validation and integrity constraints, such as user-based
access.

· Enhanced Security

When handling large amounts of data, security becomes the top-most concern for all
businesses. Database management software doesn’t allow full access to anyone except the
database administrator or the departmental head. Only they can modify the database and
control user access, making the database more secure. All other users are restricted,
depending on their access level.

· Improved File Consistency

By implementing a database management system, organizations can create a standardized

way to use files and ensure consistency of data with other systems and applications. This
streamlines data management and manipulation because the same rules can be applied to all
the data throughout the organization.

· Multi-User Environment Support

Database management software features and supports a multi-user environment, allowing

several users to access and work on data concurrently. It also supports several views of the
data. A view is a subsection of a database that is distinct and dedicated for specific operators
of the system.

As a database is typically accessed by multiple operators simultaneously, these operators may

need different database views. For example, operator A may want to print a bank statement,
whereas Operator B would want to only check the bank balance. Although both are querying
the same database, they will be presented with different views.

Types of Database Management Systems

There are several types of database management software, which can be broadly classified
into four types. The most popular database management systems include:

1- Hierarchical

[Type here]
A hierarchical DBMS organizes data in a tree-like arrangement, in the form of a hierarchy,
either in a top-down or bottom-up design. The hierarchy is defined by a parent-child
relationship, where a parent may have numerous children, but children can only have a single
parent.

This type of DBMS commonly includes one-to-one and one-to-many relationships. A one-to-
one relationship exists when a parent has a single child. Whereas, in a one-to-many
relationship, a parent has multiple children.

As data is hierarchical, it becomes a complicated network, if one-to-many relationships are

disrupted.

2- Network

A network DBMS is a slightly complex extension of hierarchical DBMS in which data has
many-to-many relationships that appear in the form of a network. The records are arranged in
a graph that can be accessed via numerous data paths.

In this database structure, a child can have multiple parents. Therefore, it allows you to model
more intricate relationships. The ability to build more relationships among different data
types makes these databases more efficient.

3- Relational

A relational model is one of the most extensively used arrangements of organizing databases.
It normalizes data and organizes it as logically independent tables. You can perform
operations like “Select” and “Join” on these tables. The data is stored in fixed structures and
manipulated using SQL.

Shared data depicts relationships between different tables. As data in a table can reference
similar data in another table, it preserves the reliability of the connections between them. This
is called referential integrity, which is a critical concept in this database model.

[Type here]
Advantages of a Database Management Software

 Simplified Data Sharing

A DBMS allows users (onsite as well as remote) to easily share the data by following the
correct authorization protocols. It provides operators access to well-managed data. As a
result, they can rapidly respond to variations in the environment.

 Enhanced Data Safety

The threats of data security breaches become more pronounced when several users access the
database. A database management software offers better implementation of data
confidentiality and safety guidelines through controlled user access.

 Improved Data Integration

A DBMS stimulates an integrated view of the company’s data. The company can quickly see
how activities in one division of the organization influence other divisions.

 Better Decision-Making

One of the uses of database management software is to provide access to well-managed data,
making it possible for users to make accurate and timely decisions. It offers a streamlined
framework to enable data quality initiatives, improving data management procedures and
yielding better-quality information.

 Improved Efficiency

[Type here]
Streamlined data access, along with the tools that convert data into valuable information,
enable operators to make swift, knowledgeable decisions. This improves a database’s
performance and efficiency.

Applications of Database Management Software

 Banks: Storing client info, account activities, disbursements, credits, and mortgages
 Airlines: Flight bookings and scheduling info
 Academies: Learner info, course registrations, grading, and result
 Telecommunication: Keeping call archives, monthly bills, and retaining balances.
 Economics and Finance: Storing data about bonds, transactions, and acquisitions of
fiscal instruments, such as shares and stocks
 Sales and Marketing: Storing data about consumers, merchandises, and sales
 Engineering and Manufacturing: Managing supply chain, and pursuing
manufacturing of items and inventory statuses in storerooms
 Human Resources: Keeping records about workers, remunerations, payroll,
deduction, generating salaries, and more

All of these applications of database management software are also often called database
management software examples and they serve the same purpose i.e. to help beginners
understand the importance of DBMS and their use cases.

Who Uses DBMS: Types of Database Users

There are different categories of DBMS users, such as:

 Database Administrator: Their main task is to manage the entire database

management system.
 Application Programmers: They write programs in different programming
languages in order to interact with the database.
 End-Users: They are the individuals who interact with the DBMS and perform
different tasks on databases, such as updating, deleting, retrieving, etc.

PRACTICAL – 2

[Type here]
 INTRODUCTION TO SQL

Introduction to SQL

Structure Query Language (SQL) is a database query language used for storing and
managing data in Relational DBMS. SQL was the first commercial language introduced
for E.F Codd's Relational model of database. Today almost all RDBMS (MySql, Oracle,
Infomix, Sybase, MS Access) use SQL as the standard database query language. SQL is
used to perform all types of data operations in RDBMS.

SQL Command

SQL defines following ways to manipulate data stored in an RDBMS.

DDL: Data Definition Language

This includes changes to the structure of the table like creation of table, altering table,
deleting a table etc. All DDL commands are auto-committed. That means it saves all the
changes permanently in the database.

DML: Data Manipulation Language

DML commands are used for manipulating the data stored in the table and not the table
itself. DML commands are not auto-committed. It means changes are not permanent to
database, they can be rolled back.

TCL: Transaction Control Language

These commands are to keep a check on other commands and their affect on the database.
These commands can annul changes made by other commands by rolling the data back to
its original state. It can also make any temporary change permanent.

[Type here]
 DCL: Data Control Language

Data control languages are the commands to grant and take back authority from any
database user.

DQL: Data Query Language

Data query language is used to fetch data from tables based on conditions that we can
easily apply.

DATATYPES
A data type specifies a particular type of data, such as integer, floating-point, Boolean etc.

Types Description Size

VARCHAR2(size Variable-length character From 1 byte to 4KB.

[BYTE | CHAR]) string.

[Type here]
 NVARCHAR2(size) Variable-length Unicode Maximum size is
character string having determined with an upper
maximum length size limit of 4000 bytes.You
characters. must specify size for
NVARCHAR2.

NUMBER [ (p [, Number having precision A NUMBER value requires

s]) ] p and scale s. from 1 to 22 bytes.
Range of p : From 1 to
38.
Ranges of s : From -84
to 127.
Both precision and scale
are in decimal digits.

FLOAT [(p)] A FLOAT value is A FLOAT value requires

represented internally as from 1 to 22 bytes.
NUMBER.
Range of p: From 1 to
126 binary digits.

LONG Character data of 231 -1 bytes

variable length up to 2
gigabytes, used for
backward compatibility.

CHAR [(size [BYTE Fixed-length character Maximum size is 2000

| CHAR])] data of length size bytes bytes or characters. Default
or characters. and minimum size is 1
byte.

Practical – 3

AIM- To create the table and insert values into it and show table using SELECT
command
create table khush i (sno number(10),department varchar2(20),student_name varchar2(20));

[Type here]
insert into khushi values(1,'BCA','Abhishek');
insert into khushi values(2,'BBA','Tamanna');
insert into khushi values(3,'CSE','saloni');
insert into khushi values(4,'BCA','Harsh');
insert into khushi values(5,'BBA','himanshu');
insert into khushi values(6,'IT','Dhruv');
insert into khushi values(7,'BBA','Sanjana');
insert into khushi values(8,'CSE','Divyansh');
select * from khushi;
output

Practical – 4

AIM- To use WHERE clause for showing the table with condition select * from table
name where anything stored.
create table khushi (sno number(10),department varchar2(20),student_name varchar2(20));

[Type here]
insert into khushi values(1,'BCA','Abhishek');
insert into khushi values(2,'BBA','Tamanna');
insert into khushi values(3,'CSE','saloni');
insert into khushi values(4,'BCA','Harsh');
insert into khushi values(5,'BBA','himanshu');
insert into khushi values(6,'IT','Dhruv');
insert into khushi values(7,'BBA','Sanjana');
insert into khushi values(8,'CSE','Divyansh');

select * from abhishek123 where department='BBA';

Practical – 5
AIM- To use FILTIRING data from data base using employee table

create table employee4321(emp_id varchar2(20),emp_name varchar2(20),emp_add

varchar2(20),salary number(10),department varchar2(20));
[Type here]
insert into employee4321
values('&emp_id','&emp_name','&emp_add' ,'&salary','&department');
select * from employee4321;

select emp_id,salary from employee4321;

select * from employee4321 where(salary>=10000 AND department='accounts');

select emp_id,salary from employee4321 where emp_name='abhishek';

[Type here]
 Practical – 6
AIM- To use distinct key words for duplicate data
select distinct salary from employee4321 order by salary;
output

[Type here]
 Practical – 7

AIM- To Sort data items in DBMS

a) Using sort of single coloum

[Type here]
 select * from employee4321 order by emp_add;

b) Using sort of multiple coloum

select * from employee4321 order by department,emp_name;

[Type here]