NOIDA INSTITUTE OF ENGINEERING AND TECHNOLOGY GREATER NOIDA-201306

(An Autonomous Institute)

School of Computer Sciences & Engineering in Emerging Technologies

Department of CSE

Session (2022 – 2023)

LAB FILE

ON

Database Management Systems (ACSAI0452)

(4th Semester)

Submitted To: Submitted By:

MS JYOTI RANI
NAME: MD SAMAN
(Assistant Professor) MAZHAR

ERP ID-0221DCSML165

INDEX
 B. TECH. SECOND YEAR
Course ACSAI0452 LTP Credit
Code
Course Database Management Systems Lab 0 0 2 1
Title
List of Experiments:
Sr. No. Name of Experiment CO Date of Faculty
Experiment Sign
1. Installing ORACLE/ MYSQL/NOSQL. CO1

2. Creating Entity-Relationship Diagram using case tools CO1

with Identifying
(Entities, attributes, keys and relationships between
entities, cardinalities, generalization, specialization etc.)
3. I. Implement DDL commands –Create, Alter, CO2
Drop etc. II. Implement DML commands- Insert,
Select, Update, Delete
4. I. Implement DCL commands-Grant and Revoke CO2
II. Implement TCL commands- Rollback,
Commit, Save point
III. Implement different type key: -Primary
Key, Foreign Key and Unique etc.
5. Converting ER Model to Relational Model CO1,
(Represent entities and relationships in Tabular CO2
form, represent attributes as columns, identifying
keys).
6. Practice Queries using COUNT, SUM, AVG, MAX, MIN, CO2
GROUP BY, HAVING, VIEWS Creation and Dropping.
7. Practicing Queries using ANY, ALL, IN, EXISTS, NOT CO2
EXISTS, UNION, INTERSECT, CONSTRAINTS etc.
8. Practicing Sub queries (Nested, Correlated) and Joins CO2
(Inner, Outer and Equi).
9. Practicing on Triggers - creation of trigger, Insertion CO4
using trigger, Deletion using trigger, Updating using
trigger
10. Procedures- Creation of Stored Procedures, Execution CO4
of Procedure, and Modification of Procedure
 11. Cursors- Declaring Cursor, Opening Cursor, Fetching CO4
the data, closing the cursor.
12. Study of Open Source NOSQL Database: CO5
MongoDB (Installation, Basic CRUD operations,
Execution)

13. Design and Develop MongoDB Queries using CO5

CRUD operations. (Use CRUD operations, SAVE
method, logical operators)
14. Implement aggregation and indexing with suitable CO5
example using MongoDB.

Student Signature Faculty Signature

Remarks (If Any)

 PROGRAM 1
Installing ORACLE/
INSTALLING SQL MYSQL/NOSQL.

Figure 1: Open any Browser and search for "SQL community installer
download".
XD

MD
Pa ge |4
 Figure 2:Click on any one of the download options.

Figure 3:Click on "No thanks, just start my download" and the

download will begin.

MD
Pa ge |5
 Figure 4:Select the option "Full" and click on "Next"

Figure 5:Click on "Execute" to download the required applications.

MD
Pa ge |6
 Figure 6:when download is complete click on "Next"

Figure 7:Click on "Execute" to install necessary products.

MD
Pa ge |7
 Figure 8:Once installed click on "next

Figure 9:Click on"Next".

MD
Pa ge |8
 Figure 10:click on "next".

Figure 11 :click on "next"

MD
Pa ge |9
 Figure 12:set a SQL password and click on "next".

Figure 13:click on "next".

MD
P a g e | 10
 Figure 14:to apply configuration click "execute".

Figure 15:after the configuration is applied click on "Finish".

MD
P a g e | 11
 Figure 16:click on "next".

Figure 17:click on "Finish".

MD
P a g e | 12
 Figure 18:connect to the server by entering the password and then click on”
check”.

Figure 19: after the connection succeeds ,click on "next".

MD
P a g e | 13
 Figure 20: click on " execute " to apply configuration.

Figure 21:once configuration completed click on "Finish".

MD
P a g e | 14
 Figure 22:click on "Finish". and the installation is completed.

MD
P a g e | 15
 INSTALLING ORACLE

Figure 23:open any browser and search “oracle express edition download”.

Figure 24:click on the oracle version according to your operating system and
download it.

MD
P a g e | 16
 Figure 25:Click on "Next".

Figure 26:Accept the terms and condition and click on "Next".

MD
P a g e | 17
 Figure 27:Select the Destination and then click on "Next".

Figure 28:Enter the password and confirm it and then click on "Next".

MD
P a g e | 18
 Figure 29:Click on "Install".

MD
P a g e | 19
 PROGRAM 2
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

Natique Parwez 2101331540069 P a g e | 20

 PROGRAM 3
1. Implement DDL commands –Create, Alter, Drop etc.
2. Implement DML commands- Insert, Select, Update, Delete

DDL COMMANDS: DDL is a subset of SQL and a part of DBMS(DataBase Management System). DDL
consists to commands like CREATE, ALTER, TRUNCATE and DROP. These commands are used to create or
modify the tables in SQL.

CREATE :- create command is use for creating database and also use to create a table

ALTER :- The ALTER command in SQL DDL is used to modify the structure of an already existing table

DROP :- The DROP command is a type of SQL DDL command, that is used to delete an existing database or an
object within a database.

TRUNCATE :- The TRUNCATE command in SQL DDL is used to remove all the records from a table. Let’s
insert a few records in the Books table

Natique Parwez 2101331540069 P a g e | 21

 DML COMMANDS: DML is an abbreviation of Data Manipulation Language.The DML commands in Structured
Query Language change the data present in the SQL database. We can easily access, store, modify, update and
delete the existing records from the database using DML commands.

INSERT :- The INSERT statement is used to insert a new row in the database that is adding data to a table.

SELECT :- The SELECT statement is used to retrieve record from one or more tables.

UPDATE :- To update a table or row or column in the table we use the update command.

Natique Parwez 2101331540069 P a g e | 22

 DELETE :- The DELETE statement is used to delete a row from the table in the database.

Natique Parwez 2101331540069 P a g e | 23

 PROGRAM 4
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 :- Used to provide any user access privileges or other privileges for the
database.

REVOKE :- USED TO TAKE BACK PERMISSIONS FROM ANY USER.

2. COMMIT :- COMMIT command is used to permanently save any transaction into the database.
ROLLBACK :- ROLLBACK is a command that causes all data changes since the last BEGIN WORK , or START
TRANSACTION to be discarded by the relational database management systems

SAVEPOINT :- SAVEPOINT command is used to temporarily save a transaction so that you can rollback to
that point whenever required.

Natique Parwez 2101331540069 P a g e | 24

 3. PRIMARY KEY :- A Primary Key is the minimal set of attributes of a table that has the task to
uniquelyidentify the rows
FOREIGN KEY :- it is the one that is used to link two tables together via the primary key
UNIQUE KEY :- it is a column or set of columns that uniquely identify each record in a table.

Natique Parwez 2101331540069 P a g e | 25

Natique Parwez 2101331540069 P a g e | 26
 PROGRAM 5
Converting ER Model to Relational Model (Represent entities and relationships in Tabularform, 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

Natique Parwez 2101331540069 P a g e | 27

Natique Parwez 2101331540069 P a g e | 28
 PROGRAM 6
Practice Queries using COUNT, SUM, AVG, MAX, MIN, GROUP BY, HAVING, VIEWS Creation
and Dropping.

COUNT:- The COUNT() function returns the number of rows that matches a specified criteria.
SUM:- SUM() function is an aggregate function that calculates the sum of all or distinct values in an
expression
AVG:- The AVG() function provides the average value of a numeric column.
MAX:- MAX() is used to find the maximum value or highest value of a certain column or expression.

MIN:- The MIN() function returns the smallest value of the selected column.
GROUP BY:- The GROUP BY statement groups rows that have the same values into summary rows
HAVING:- A HAVING clause in SQL specifies that an SQL SELECT statement must only return rows where
aggregate values meet the specified conditions.
VIEWS Creation:- A view is created with the CREATE VIEW statement.

Natique Parwez 2101331540069 P a g e | 29

Natique Parwez 2101331540069 P a g e | 30
Natique Parwez 2101331540069 P a g e | 31
 PROGRAM 7
Practicing Queries using ANY, ALL, IN, EXISTS, NOT EXISTS, UNION,
INTERSECT, CONSTRAINTS etc.
ANY:- ANY return true if any of the subqueries values meet the condition.
ALL:- ALL operator is used to select all tuples of SELECT STATEMENT
IN:- allows you to easily test if an expression matches any value in a list of values
EXISTS:- The EXISTS operator is used to test for the existence of any record in a subquery. NOT

EXISTS:- It is used to restrict the number of rows returned by the SELECT Statement. UNION:- It is
used to combine the result set of two select queries

INTERSECT:- is used to combine two SELECT statements, but returns rows only from the first
SELECT statement that are identical to a row in the second SELECT statement.
CONSTRAINTS:- Constraints are used to limit the type of data that can go into a table.

Natique Parwez 2101331540069 P a g e | 32

Natique Parwez 2101331540069 P a g e | 33
Natique Parwez 2101331540069 P a g e | 34
 PROGRAM 8
Practicing Sub queries (Nested, Correlated) and Joins (Inner, Outer and Equi).

Natique Parwez 2101331540069 P a g e | 35

 PROGRAM 9
Practicing on Triggers - creation of trigger, Insertion using trigger, Deletion
using trigger, Updating using trigger

Natique Parwez | 2101331540069 P a g e | 36

Natique Parwez | 2101331540069 P a g e | 37
Natique Parwez | 2101331540069 P a g e | 38
 PROGRAM 10
Procedures- Creation of Stored Procedures, Execution of Procedure, and Modification of
Procedure.

Natique Parwez 2101331540069 P a g e | 39

Natique Parwez 2101331540069 P a g e | 40