Introduction to Database Management System (DBMS)

Introduction to Database Management System (DBMS)

Database Management System (DBMS) is a software system

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.

Key Features of DBMS

• Data Modeling: Tools for creating and modifying data models, defining the structure and relationships of the data.

• Data Storage and Retrieval: Methods for storing and retrieving data efficiently.

• Concurrency Control: Mechanisms to ensure multiple users can access data without conflicts.

• Data Integrity and Security: Tools for enforcing data integrity and security constraints.

• Backup and Recovery: Mechanisms for backing up and recovering data in case of system failure.

Types of DBMS

1. Relational Database Management System (RDBMS): Data is organized into tables with rows and columns, and
relationships are managed through primary and foreign keys. SQL is used for querying and manipulating data.

2. NoSQL DBMS: Designed for high-performance scenarios and large-scale data, storing data in various non-relational
formats such as key-value pairs, documents, graphs, or columns.

3. Object-Oriented DBMS (OODBMS): Stores data as objects, similar to those used in object-oriented programming,
allowing for complex data representations and relationships.

Database Languages

• Data Definition Language (DDL): Deals with database schemas and descriptions.

• Data Manipulation Language (DML): Used for data retrieval and manipulation.

• Data Control Language (DCL): Manages access controls.

• Transactional Control Language (TCL): Manages transactions within the database

DBMS plays a crucial role in modern data management, providing an environment to store and retrieve data
conveniently and efficiently.
 Advantages of Database Management System (DBMS)

1. Data Security: DBMS provides robust data privacy and security policies, ensuring that data is protected from
unauthorized access.

2. Data Integration: It allows for well-managed and synchronized data, giving an integrated view of an organization’s
operations.

3. Data Abstraction: Users interact with the system through various levels of data abstraction, simplifying complex
data handling.

4. Reduction in Data Redundancy: DBMS prevents duplicate data entries, ensuring efficient storage and consistency.

5. Data Sharing: It facilitates data sharing across multiple applications and users, enhancing productivity and
collaboration.

6. Data Consistency and Accuracy: By enforcing data integrity constraints, DBMS ensures data is consistent and
accurate.

7. Efficient Data Access and Retrieval: Indexing and query optimization techniques in DBMS speed up data retrieval,
improving system performance.

8. Concurrency and Atomicity: DBMS maintains data consistency even when multiple users access or modify the
data simultaneously.

Disadvantages of Database management System (DBMS)

1. Increased Cost: Implementing a DBMS requires significant investment in hardware, software, and trained
personnel. The costs for licensing, maintenance, and regular updates can be high.

2. Complexity: DBMS systems are complex and require specialized knowledge to design, implement, and maintain.
This complexity can lead to challenges in managing and using the system effectively.

3. Performance Issues: As the volume of data grows, performance can degrade. This can result in slower query
responses and longer processing times.

4. Data Security: While DBMS systems offer robust security features, they are also prime targets for cyber-attacks.
Unauthorized access and data breaches can pose significant risks.

5. Data Conversion and Migration: Converting existing data to a new DBMS can be time-consuming and costly. Data
migration also carries the risk of data loss or corruption.

6. Frequent Updates/Upgrades: Regular updates and upgrades are necessary to keep the system secure and
efficient, but they can be disruptive and require additional resources.

7. Potential for Database Failure: System crashes or power outages can lead to database failures, resulting in data
loss or downtime.
 Introduction to SQL

SQL (Structured Query Language) is a domain-specific language used to communicate with databases. It's designed
to manage data effectively, allowing users to retrieve, insert, update, and delete information from relational
databases.

Key Concepts in SQL

• Relational Database: A database organized into tables, where each table represents a collection of related data.
• Table: A collection of rows and columns, where each row represents a record and each column represents a field.
• Row: A single record within a table, containing values for each field.
• Column: A field within a table, representing a specific type of data.
• Query: A request to retrieve information from a database, based on specific criteria.

Basic SQL Operations

• SELECT: Retrieves data from one or more tables.

• INSERT: Adds new data to a table.
• UPDATE: Modifies existing data in a table.
• DELETE: Removes data from a table.

Characteristics of SQL

SQL (Structured Query Language) is a powerful language designed for interacting with relational databases. It
offers several key characteristics that make it well-suited for data management tasks:

1. Declarative Language: SQL is primarily a declarative language, meaning you specify what you want to do (e.g.,
retrieve data) without detailing how to do it.
2. Standardized: SQL is standardized by ANSI (American National Standards Institute) and ISO (International
Organization for Standardization), ensuring consistency across different database systems.
3. Portability: SQL code can be used across different database systems with minimal changes, making it highly
portable.
4. Data Manipulation: SQL allows for various data manipulation operations such as inserting, updating, deleting, and
querying data.
5. Data Definition: SQL provides commands to define the structure of the database, including creating, altering, and
dropping tables and indexes.
6. Data Control: SQL includes features for controlling access to data, such as granting and revoking permissions.
7. Ease of Use: SQL is relatively easy to learn and use, even for those without extensive programming experience.
 Discuss about SQL data type

SQL data types define the kind of data that can be stored in a column of a database table. Here are some common
categories of SQL data types:

1. Numeric Data Types

• INT: Used for integer values.

• FLOAT: Used for floating-point numbers.

• DECIMAL: Used for exact numeric values with a fixed number of decimal places.

2. String Data Types

• CHAR: Fixed-length character string.

• VARCHAR: Variable-length character string.

• TEXT: Large text data.

3. Date and Time Data Types

• DATE: Stores date values.

• TIME: Stores time values.

• DATETIME: Stores both date and time values.

4. Binary Data Types

• BINARY: Fixed-length binary data.

• VARBINARY: Variable-length binary data.

• BLOB: Binary Large Object, used for storing large binary data.

5. Boolean Data Types

• BIT: Stores Boolean values (0 or 1).

6. Other Data Types

• ENUM: A string object that can have only one value, chosen from a list of possible values.

• SET: A string object that can have 0 or more values, chosen from a list of possible values.
 Define different SQL data language
SQL (Structured Query Language) is divided into several sublanguages, each serving a specific purpose in database
management. Here are the main types:

1. Data Definition Language (DDL):

o Purpose: Defines the structure of the database.

o Commands:

▪ CREATE: Creates a new table or database.

▪ ALTER: Modifies an existing database object.

▪ DROP: Deletes a table or database.

▪ TRUNCATE: Removes all records from a table, but not the table itself.

2. Data Query Language (DQL):

o Purpose: Retrieves data from the database.

o Commands:

▪ SELECT: Fetches data from the database.

3. Data Manipulation Language (DML):

o Purpose: Manages data within the database.

o Commands:

▪ INSERT: Adds new data to the database.

▪ UPDATE: Modifies existing data.

▪ DELETE: Removes data from the database.

4. Data Control Language (DCL):

o Purpose: Controls access to the data.

o Commands:

▪ GRANT: Gives user access privileges.

▪ REVOKE: Removes user access privileges.

5. Transaction Control Language (TCL):

o Purpose: Manages transactions within the database.

o Commands:

▪ COMMIT: Saves all changes made in the transaction.

▪ ROLLBACK: Undoes changes made in the current transaction.

▪ SAVEPOINT: Sets a point within a transaction to which you can later roll back.
 Explain SQL Command

a) Create Table statement is used to create a new table in database.

• Syntax

CREATE TABLE table_name

(
column_name1 datatype constraint,
column_name2 datatype constraint,
column_name3 datatype constraint,
);
• Example

CREATE TABLE table_name

(
“ID” int8 PRIMARY KEY,
“Name” varchar(50) NOT NULL,
“Age” int NOT NULL,
“City” char(50),
“Salary” numeric,
);
b) Alter Table statement is used to alter a new table in database.

Use table_name
Select * from table_name;
alter table table_name
ADD Father int;
C) Drop Table statement is used to drop the table in database.
Use table_name
Select * from table_name;
alter table table_name
DROP COLUMN Father;
 Explain clauses in SQL

a. Select Clause Statement

SELECT Statement The SELECT statement is used to select data from a database.
• Syntax
SELECT column_name FROM table_name;
To select all the fields available in the table
• Syntax
SELECT * FROM table_name;
To select distinct/unique fields available in the table

• Syntax

SELECT DISTINCT Column_name FROM table_name;

Example : SELECT rollno, name,grade FROM table_name;

b. From Clause Statement

It is simlour to select clauses.
c. Where Clause statement
The WHERE clause is used to filter records. It is used to extract only those records that fulfill a specified
condition
• Syntax
SELECT column_name FROM table_name
WHERE conditions;
Example
SELECT name FROM classroom
WHERE grade='A';
 Define Cursor in SQL

A cursor in SQL is a database object used to retrieve and manipulate data row by row, rather than fetching an
entire result set at once. SQL Server Cursor Syntax provides a way to work with records within a database table
in a procedural manner, which can be useful for complex data processing tasks.
A cursor in SQL is a database object that allows you to retrieve and manipulate data row by row from a result set. It is
particularly useful when you need to process individual rows sequentially rather than in a set-based operation. Here are
the key steps involved in using a cursor:

1. Declare a Cursor: Define the cursor and the SQL query that generates the result set.

DECLARE cursor_name CURSOR FOR select_statement;

2. Open the Cursor: Execute the SQL query and populate the cursor with the result set.

OPEN cursor_name;

3. Fetch Rows: Retrieve rows from the cursor one at a time and process them.

FETCH NEXT FROM cursor_name INTO variable_list;

4. Close the Cursor: Release the cursor when done.

CLOSE cursor_name;

5. Deallocate the Cursor: Remove the cursor definition and release associated resources.

DEALLOCATE cursor_name;
 Define Statement In SQL
a) Commit
The COMMIT statement in SQL is used to permanently save all changes made during the current
transaction. Here are the key points:
• Purpose: Ends the current transaction and makes all changes permanent.
Syntax:
COMMIT;
Usage: Typically used after a series of INSERT, UPDATE, or DELETE statements to ensure that all changes are
saved to the database.
Example:
BEGIN TRANSACTION;
DELETE FROM Customers WHERE State = 'Texas';
COMMIT;
Effect: Once COMMIT is executed, the changes cannot be undone by a ROLLBACK statement.

b) Roll Back
The ROLLBACK statement in SQL is used to undo changes made during the current transaction. Here are
the key points:
• Purpose: Reverts the database to the state it was in before the transaction began.
Syntax:
ROLLBACK;
Usage: Typically used when an error occurs during a transaction, and you want to undo all changes made in that
transaction.
Example:
BEGIN TRANSACTION;
DELETE FROM Customers WHERE State = 'Texas';
ROLLBACK;
Effect: All changes made during the transaction are undone, and the database is restored to its previous state.