NOIDA INSTITUTE OF ENGINEERING AND

TECHNOLOGY GREATER NOIDA-201306

(An Autonomous Institute)
School of Computer Sciences & Engineering in
Emerging Technologies

Department of CSBS

Session (2024 – 2025)

Database Management Systems Lab (BCSBS0454)

(4th Semester)

Submitted To: Submitted By:

Ms. Rifa NIzam Khan Name: Vidish Kumar
Roll No: 2301331560058

Affiliated to Dr. A.P.J Abdul Kalam Technical University, Uttar Pradesh, Lucknow.
 INDEX
S.NO Program Title Date Page Grade Sign

1 Installing ORACLE/ MYSQL/NOSQL

2 Creating Entity-Relationship Diagram using

case tools with Identifying

3 Implement DDL commands –Create, Alter, Drop

etc.
Implement DML commands- Insert, Select,
Update, Delete

4 Implement DCL commands-Grant and Revoke

Implement TCL commands- Rollback, Commit,
Save point
Implement different type key: -Primary Key,
Foreign Key and Unique etc.

5 Converting ER Model to Relational Model

(Represent entities and relationships in Tabular
form, represent attributes as columns,
identifying keys).

6 Practice Queries using COUNT, SUM, AVG, MAX,

MIN, GROUP BY, HAVING, VIEWS Creation and
Dropping.

7 Practicing Queries using ANY, ALL, IN, EXISTS,

NOT EXISTS, UNION, INTERSECT,
CONSTRAINTS etc.

8 Practicing Sub queries (Nested, Correlated) and

Joins (Inner, Outer and Equi).

9 Practicing on Triggers - creation of trigger,

Insertion using trigger, Deletion using trigger,
Updating using trigger

10 Procedures- Creation of Stored Procedures,

Execution of Procedure, and Modification of
Procedure

11 Cursors- Declaring Cursor, Opening Cursor,

Fetching the data, closing the cursor.
 SESSION-2024-25
Introduction
SEM-4th (EVEN)

MYSQL
MySQL is an open-source, fast reliable, and flexible relational database management
system, typically used with PHP. This chapter is an introductory chapter about
MySQL, what is MySQL, and the main features of MySQL are described here.

-MySQL is a database system used for developing web-based software applications.

-MySQL used for both small and large applications.
-MySQL is a relational database management system (RDBMS).
-MySQL is fast, reliable, and flexible and easy to use.
-MySQL supports standard SQL (Structured Query Language).
-MySQL is free to download and use.
-MySQL was developed by Michael Widenius and David Axmark in 1994.
-MySQL is presently developed, distributed, and supported by Oracle Corporation.
-MySQL Written in C, C++.
-MySQL server design is multi-layered with independent modules.
-MySQL is fully multithreaded by using kernel threads. It can handle multiple CPUs if
they are available.
-MySQL provides transactional and non-transactional storage engines.
-MySQL has a high-speed thread-based memory allocation system.
-MySQL supports in-memory heap table.
-MySQL Handles large databases.
-MySQL Server works in client/server or embedded systems.
-MySQL Works on many different platforms.

Vidish Kumar 1 2301331560058

NOSQL
NoSQL Databases:
We know that MongoDB is a NoSQL Database, so it is very necessary to know about
NoSQL Database to understand MongoDB thoroughly.

Databases can be divided in 3 types:

RDBMS (Relational Database Management System)

OLAP (Online Analytical Processing)
NoSQL (recently developed database)

NoSQL Database:
NoSQL Database is used to refer a non-SQL or non relational database.
It provides a mechanism for storage and retrieval of data other than tabular
relations model used in relational databases. NoSQL database doesn't use tables for
storing data. It is generally used to store big data and real-time web applications.

ORACLE
Oracle DB is also known as Oracle RDBMS and, sometimes, simply as Oracle.
Oracle Database (Oracle DB) is a relational database management system (RDBMS)
from Oracle Corporation. Originally developed in 1977 by Lawrence Ellison and other
developers, Oracle DB is one of the most trusted and widely used relational
database engines for storing, organizing and retrieving data by type while still
maintaining relationships between the various types.

The system is built around a relational database framework in which data objects
may be directly accessed by users (or an application front end) through structured
query language (SQL). Oracle is a fully scalable relational database architecture and
is often used by global enterprises which manage and process data across wide and

Vidish Kumar 2 2301331560058

local area networks. The Oracle database has its own network component to allow
communications across networks.

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 deleted
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.

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.

Vidish Kumar 3 2301331560058

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 Commands in SQL

In SQL, TCL stands for Transaction control language.
A single unit of work in a database is formed after the consecutive execution of
commands is known as a transaction.
There are certain commands present in SQL known as TCL commands that help the
user manage the transactions that take place in a database.
COMMIT. ROLLBACK and SAVEPOINT are the most commonly used TCL commands in
SQL.

Primary Key
A primary key is a column of a table or a set of columns that helps to identify every
record present in that table uniquely. There can be only one primary Key in a table.
Also, the primary Key cannot have the same values repeating for any row. Every
value of the primary key has to be different with no repetitions.
Primary Key
A primary key is a column of a table or a set of columns that helps to identify every
record present in that table uniquely. There can be only one primary Key in a table.

Vidish Kumar 4 2301331560058

Also, the primary Key cannot have the same values repeating for any row. Every
value of the primary key has to be different with no repetitions.
Unique Key
Unique Key is a column or set of columns that uniquely identify each record in a
table. All values will have to be unique in this Key. A unique Key differs from a
primary key because it can have only one null value, whereas a primary Key cannot
have any null values.

Triggers
Triggers are the SQL statements that are automatically executed when there is any change in the
database. The triggers are executed in response to certain events(INSERT, UPDATE or DELETE) in
a particular table. These triggers help in maintaining the integrity of the data by changing the
data of the database in a systematic fashion.

Syntax
create trigger Trigger_name
(before | after)
[insert | update | delete]
on [table_name]
[for each row]
[trigger_body]

Cursors
A cursor is a temporary work area created in the system memory when a SQL statement is
executed. A cursor contains information on a select statement and the rows of data accessed by
it. This temporary work area is used to store the data retrieved from the database, and
manipulate this data.
There are two types of cursors in PL/SQL :
Implicit cursors.
Explicit cursors.

Implicit Cursors

Vidish Kumar 5 2301331560058

Implicit cursors are automatically created by Oracle whenever an SQL statement is executed,
when there is no explicit cursor for the statement. Programmers cannot control the implicit
cursors and the information in it.
Explicit Cursors
Explicit cursors are programmer-defined cursors for gaining more control over the context area.
An explicit cursor should be defined in the declaration section of the PL/SQL Block. It is created
on a SELECT Statement which returns more than one row.

The syntax for creating an explicit cursor is −

CURSOR cursor_name IS select_statement;

Vidish Kumar 6 2301331560058

 SESSION-2024-25
Program No-1
SEM-4th (EVEN)

Installing ORACLE/ MYSQL/NOSQL

Vidish Kumar 7 2301331560058

Vidish Kumar 8 2301331560058
 SESSION-2024-25
Program No-2
SEM-4th (EVEN)

Creating Entity-Relationship Diagram using case tools with Identifying

(Entities, attributes, keys and relationships between entities, cardinalities, generalization,
specialization etc.)

ER DIAGRAM ON LIBRARY MANAGEMENT SYSTEM

Vidish Kumar 9 2301331560058

 SESSION-2024-25
Program No-3
SEM-4th (EVEN)

I. Implement DDL commands –Create, Alter, Drop etc.

II. Implement DML commands- Insert, Select, Update, Delete

DDL COMMANDS
CREATE

ALTER

DROP

TRUNCATE

Vidish Kumar 10 2301331560058

DML COMMANDS
INSERT

SELECT

Vidish Kumar 11 2301331560058

UPDATE

DELETE

Vidish Kumar 12 2301331560058

 SESSION-2024-25
Program No-4
SEM-4th (EVEN)

1. Implement DCL commands-Grant and Revoke

2. Implement TCL commands- Rollback, Commit, Save point
3. Implement different type key: -Primary Key, Foreign Key and Unique etc.
GRANT

REVOKE

Vidish Kumar 13 2301331560058

2.

3.

Vidish Kumar 14 2301331560058

Vidish Kumar 15 2301331560058
 SESSION-2024-25
Program No-5
SEM-4th (EVEN)

Converting ER Model to Relational Model (Represent entities and relationships in Tabular

form, represent attributes as columns, identifying keys).

There are some rules for converting the ER diagram into tables which are as follows:
Rule1: Conversion of an entity set into a table
a) Representation of Strong entity set with simple attributes

Table: Employe e
employee_id name Age city mobile
no

Rule 2: Conversion of Relationship into relation

Vidish Kumar 16 2301331560058

Vidish Kumar 17 2301331560058
 SESSION-2024-25
Program No-6
SEM-4th (EVEN)

Practice Queries using COUNT, SUM, AVG, MAX, MIN, GROUP BY, HAVING, VIEWS Creation and
Dropping.

Vidish Kumar 18 2301331560058

Vidish Kumar 19 2301331560058
Vidish Kumar 20 2301331560058
 SESSION-2024-25
Program No-7
SEM-4th (EVEN)

Practicing Queries using ANY, ALL, IN, EXISTS, NOT EXISTS, UNION, INTERSECT, CONSTRAINTS
etc.

Vidish Kumar 21 2301331560058

Vidish Kumar 22 2301331560058
 SESSION-2024-25
Program No-8
SEM-4th (EVEN)

Practicing Sub queries (Nested, Correlated) and Joins (Inner, Outer and Equi).

Vidish Kumar 23 2301331560058

 SESSION-2024-25
Program No-9
SEM-4th (EVEN)

Practicing on Triggers - creation of trigger, Insertion using trigger, Deletion using

trigger, Updating using trigger

Vidish Kumar 24 2301331560058

Vidish Kumar 25 2301331560058
Vidish Kumar 26 2301331560058
 SESSION-2024-25
Program No-10
SEM-4th (EVEN)

Procedures- Creation of Stored Procedures, Execution of Procedure, and Modification of

Procedure

Vidish Kumar 27 2301331560058

 SESSION-2024-25
Program No-11
SEM-4th (EVEN)

Cursors- Declaring Cursor, Opening Cursor, Fetching the data, closing the cursor.
Declaring Cursor

2. Opening Cursor

3. Fetching the data

4. Closing the Cursor

Vidish Kumar 28 2301331560058