BANGALORE INSTITUTE OF TECHNOLOGY

K.R.ROAD, V.V.PURAM, BANGALORE-560 004

Department of Artificial Intelligence & Machine Learning

BCS403

DATABASE MANAGEMENT SYSTEM LABORATORY

Manual

IV-SEMESTER

Prepared By:

Prof. Sahana M.
DATABASE MANAGEMENT SYSTEM LABORATORY
SEMESTER – IV
Course Code: BCS403 IA Marks: 25
Number of Lecture Hours/Week 0:2:0 Total Hours: 20

CREDITS – 02
Course Learning objectives:

1. To provide a strong foundation in database concepts, technology, and practice.

2. To practice SQL programming through a variety of database problems.
3. To understand the relational database design principles.
4. To demonstrate the use of concurrency and transactions in database.
5. To design and build database applications for real world problems.
6. To become familiar with database storage structures and access techniques.

Lab Programs:
Sl.
Name of Program
No.
Create a table called Employee & execute the following.
Employee(EMPNO,ENAME,JOB, MANAGER_NO, SAL, COMMISSION)
1. Create a user and grant all permissions to the user.
2. Insert the any three records in the employee table contain attributes EMPNO, ENAME
1
JOB, MANAGER_NO, SAL, COMMISSION and use rollback. Check the result.
3. Add primary key constraint and not null constraint to the employee table.
4. Insert null values to the employee table and verify the result.

Create a table called Employee that contains attributes EMPNO, ENAME, JOB, MGR,
SAL & execute the following.
1. Add a column commission with domain to the Employee table.
2 2. Insert any five records into the table.
3. Update the column details of job.
4. Rename the column of Employ table using alter command.
5. Delete the employee whose Empno is 105.
Queries using aggregate functions (COUNT,AVG,MIN,MAX,SUM),Group by, Orderby.
Employee(E_id, E_name, Age, Salary)
1. Create Employee table containing all Records E_id, E_name, Age, Salary.
2. Count number of employee names from employee table.
3
3. Find the Maximum age from employee table.
4. Find the Minimum age from employee table.
5. Find salaries of employee in Ascending Order.
6. Find grouped salaries of employees.
Create a row level trigger for the customers table that would fire for INSERT or UPDATE
or DELETE operations performed on the CUSTOMERS table. This trigger will display
4
the salary difference between the old & new Salary.
CUSTOMERS(ID,NAME,AGE,ADDRESS,SALARY)

Create cursor for Employee table & extract the values from the table. Declare the

5 variables, Open the cursor & extract the values from the cursor. Close the cursor.
Employee(E_id, E_name, Age, Salary)

Write a PL/SQL block of code using parameterized Cursor that will merge the data
6 available in the newly created table N_RollCall with the data available in the table
O_RollCall. If the data in the first table already exist in the second table then that data
should be skipped.
Install an Open Source NoSQL Data base MongoDB & perform basic CRUD (Create,
7
Read, Update & Delete) operations. Execute MongoDB basic Queries using CRUD.
Laboratory outcomes:
operations.

1. Describe the basic elements of a relational database management system

2. Design entity relationship for the given scenario.
3. Apply various Structured Query Language (SQL) statements for database manipulation.
4. Analyze various normalization forms for the given application.
5. Develop database applications for the given real world problem.
6. Understand the concepts related to NoSQL databases.

25 marks lab component is split up into 15 marks for the conduction of the experiment and
preparation of laboratory record, and 10 marks for the test to be conducted after the completion of
all the laboratory sessions.
To create a new user

Login in to sql.

You can create user only with system admin login.

• CREATE USER myuser IDENTIFIED BY mypassword;

• GRANT ALL PRIVILEGES TO myuser;

Login to sql with the user created.

1
Program 1
Create a table called Employee & execute the following.
Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)

CREATE TABLE Employee (

EMPNO NUMBER,
ENAME VARCHAR2(50),
JOB VARCHAR2(50),
MANAGER_NO NUMBER,
SAL NUMBER,
COMMISSION NUMBER
);

1. Create a user and grant all permissions to the user.

• CREATE USER myuser IDENTIFIED BY mypassword;
• GRANT ALL PRIVILEGES TO myuser;

2. Insert any three records in the employee table contain attributes EMPNO, ENAME JOB,
MANAGER_NO, SAL, COMMISSION and use rollback. Check the result.
• INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL,COMMISSION)
VALUES (1, 'John Doe', 'Manager', NULL, 5000, 1000);
• INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES (2, 'Jane Smith', 'Clerk', 1, 3000, NULL);
2
• INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES (3, 'Alice Johnson', 'Analyst', 1, 4000, 500);

ROLLBACK;

Since none of the row entry was committed all the entry got rolled back.
To save the inserted values or any other operations use Commit; command
If we perform rollback after commit we can only roll back till the commit point.

3
3. Add primary key constraint and not null constraint to the employee table.
• ALTER TABLE Employee ADD CONSTRAINT PK_Employee PRIMARY KEY (EMPNO);
• ALTER TABLE Employee MODIFY (ENAME NOT NULL, JOB NOT NULL, SAL NOT
NULL);

4. Insert null values to the employee table and verify the result.

• INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)

VALUES (4, NULL, NULL, NULL, NULL, NULL);

4
Program 2

Create a table called Employee that contains attributes EMPNO, ENAME, JOB, MGR, SAL &
execute the following.

CREATE TABLE Employee (

EMPNO NUMBER,
ENAME VARCHAR2(50),
JOB VARCHAR2(50),
MGR NUMBER,
SAL NUMBER
);

1. Add a column commission with domain to the Employee table.

• ALTER TABLE Employee ADD COMMISSION NUMBER;

2. Insert any five records into the table.

• INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION) VALUES (101,
'John', 'Manager', NULL, 5000, 1000);
• INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION) VALUES (102,
'Alice', 'Analyst', 101, 4000, 500);
• INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION) VALUES (103,
'Bob', 'Clerk', 101, 3000, NULL);
• INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION) VALUES (104,
'Jane', 'Developer', 102, 6000, 2000);
• INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, COMMISSION) VALUES (105,
'Mike', 'Sales', 102, 3500, 800);

5
3. Update the column details of job.
• UPDATE Employee SET JOB = 'Sales Manager' WHERE ENAME = 'Mike';

4. Rename the column of Employ table using alter command.

• ALTER TABLE Employee RENAME COLUMN MGR TO MANAGER_NO;

5. Delete the employee whose Empno is 105.

• DELETE FROM Employee WHERE EMPNO = 105;

6
Program 3
Queries using aggregate functions (COUNT, AVG, MIN, MAX, SUM), Group by, Orderby.
Employee(E_id, E_name, Age, Salary)

1. Create Employee table containing all Records E_id, E_name, Age, Salary.
CREATE TABLE Employee (
E_id INT,
E_name VARCHAR(50),
Age INT,
Salary DECIMAL(10, 2) );

• Insert into employee values(1,'sahana',20,100.10);

• Insert into employee values(2,'bhavana',21,2000.00);
• Insert into employee values(3,'sinchana',22,30000.11);
• Insert into employee values(4,'rahul',23,600.00);
• Insert into employee values(5,'chandan',24,1000.10);

2. Count number of employee names from employee table.

• SELECT COUNT(E_name) AS Total_Employees FROM Employee;

7
3. Find the Maximum age from employee table.

• SELECT MAX(Age) AS Max_Age FROM Employee;

4. Find the Minimum age from employee table.

• SELECT MIN(Age) AS Min_Age FROM Employee;

5. Find salaries of employee in Ascending Order.

• SELECT E_name, Salary FROM Employee ORDER BY Salary ASC;

6. Find grouped salaries of employees.

• SELECT Salary, COUNT(*) AS Num_Employees FROM Employee GROUP BY Salary;

8
Program 4

Create a row level trigger for the customers table that would fire for INSERT or UPDATE or
DELETE operations performed on the CUSTOMERS table. This trigger will display the salary
difference between the old & new Salary.
CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)

CREATE TABLE CUSTOMERS (

ID INT,
NAME VARCHAR(50),
AGE INT,
ADDRESS VARCHAR(100),
SALARY DECIMAL(10, 2) );

First on serveroutput and then write trigger after trigger is successful do insert delete and update.

SET SERVEROUTPUT ON;

The SET SERVEROUTPUT command specifies whether output from the DBMS_OUTPUT message
buffer is redirected to standard output.

CREATE OR REPLACE TRIGGER salary_difference_trigger

AFTER INSERT OR UPDATE OR DELETE ON CUSTOMERS
FOR EACH ROW
DECLARE
old_salary NUMBER;
new_salary NUMBER;
BEGIN
IF INSERTING OR UPDATING THEN
old_salary := NVL(:OLD.SALARY, 0);
new_salary := NVL(:NEW.SALARY, 0);
DBMS_OUTPUT.PUT_LINE(‘old salary ' ||(old_salary));
DBMS_OUTPUT.PUT_LINE(‘new salary ' || (new_salary));
9
DBMS_OUTPUT.PUT_LINE('Salary Difference for ' || :NEW.NAME || ': ' || (new_salary -
old_salary));
END IF;
IF DELETING THEN
DBMS_OUTPUT.PUT_LINE('Salary Difference for ' || :OLD.NAME || ': ' || -1 *
:OLD.SALARY);
END IF;
END;
/

10
Program 5

Create cursor for Employee table & extract the values from the table. Declare the variables, Open
the cursor & extract the values from the cursor. Close the cursor.

Employee(E_id, E_name, Age, Salary)

CREATE TABLE Employee (

E_id INT,
E_name VARCHAR(50),
Age INT,
Salary DECIMAL(10, 2) );

• Insert into employee values(1,'sahana',20,100.10);

• Insert into employee values(2,'bhavana',21,2000.00);
• Insert into employee values(3,'sinchana',22,30000.11);
• Insert into employee values(4,'rahul',23,600.00);
• Insert into employee values(5,'chandan',24,1000.10);

DECLARE
CURSOR emp_cursor IS
SELECT E_id, E_name, Age, Salary FROM Employee;

-- Declare variables to store data fetched from the cursor

v_E_id Employee.E_id%TYPE;
v_E_name Employee.E_name%TYPE;
v_Age Employee.Age%TYPE;
v_Salary Employee.Salary%TYPE;
BEGIN
-- Open the cursor
OPEN emp_cursor;

-- Fetch data from the cursor

11
LOOP
FETCH emp_cursor INTO v_E_id, v_E_name, v_Age, v_Salary;
EXIT WHEN emp_cursor%NOTFOUND;

-- Process the fetched data (you can perform any actions here)
DBMS_OUTPUT.PUT_LINE('Employee ID: ' || v_E_id || ', Name: ' || v_E_name || ', Age: ' || v_Age ||
', Salary: ' || v_Salary);
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
/

12
Program 6

Write a PL/SQL block of code using parameterized Cursor that will merge the data available in the
newly created table N_RollCall with the data available in the table O_RollCall. If the data in the
first table already exist in the second table then that data should be skipped.

Create table N_RollCall(

id int,

data varchar(50));

Create table O_RollCall(

id int,

data varchar(50));

• insert into N_RollCall values(1,'sahana');

• insert into N_RollCall values(2,'saa');

• insert into O_RollCall values(1,'sahana');

• insert into O_RollCall values(3,'bhavana');

13
DECLARE
CURSOR n_rollcall_cursor IS
SELECT * FROM N_RollCall;

v_n_id N_RollCall.id%TYPE;
v_n_data N_RollCall.data%TYPE;

v_exists NUMBER := 0;
BEGIN
OPEN n_rollcall_cursor;

LOOP
FETCH n_rollcall_cursor INTO v_n_id, v_n_data;
EXIT WHEN n_rollcall_cursor%NOTFOUND;

-- Check if the data from N_RollCall exists in O_RollCall

SELECT COUNT(*) INTO v_exists
FROM O_RollCall
WHERE id = v_n_id;

-- If data doesn't exist in O_RollCall, insert it

IF v_exists = 0 THEN
INSERT INTO O_RollCall (id, data)
VALUES (v_n_id, v_n_data);
COMMIT; -- Commit after each insert
ELSE
-- If data already exists, skip
DBMS_OUTPUT.PUT_LINE('Data with ID ' || v_n_id || ' already exists in O_RollCall.
Skipping...');
END IF;
END LOOP;

CLOSE n_rollcall_cursor;
END;
/

14
Program 7

Install an Open Source NoSQL Data base MongoDB & perform basic CRUD (Create, Read, Update
& Delete) operations. Execute MongoDB basic Queries using CRUD operations.

Install MongoDB by using following link

https://www.youtube.com/watch?v=dEm2AS5amyA
Create operation

//to start mongo client

$ mongosh

//to list out database names

> show dbs

//to create database

> use student

//To know the Mongodb version

db.version()

//to check in which database I am working

> db

//to drop database in which I am working

> db.dropDatabase()

//To create collection

> db.createCollection('details')

//to list out collection names

> show collections

Now drop the collection with the name mycollection

>db.details.drop()

//create collection by inserting document

> db.details.insert({rno:1,name:'Bhavana'})

// create student details like name, rno, email,location,address,marks

//Every row/document can be different than other

> db.details.insert({name:'Amit',rno:2})
> db.details.insert({rno:3, email_id:'[email protected]'})

15
// To display data from collection
> db.details.find()
{ "_id" : ObjectId("5d7d3daf315728b4998f522e"), "rno" : 1, "name" : "Bhavana" }
{ "_id" : ObjectId("5d7d3f28315728b4998f522f"), "name" : "Amit", "rno" : 2 }
{ "_id" : ObjectId("5d7d3f56315728b4998f5230"), "rno" : 3, "email_id" : "[email protected]" }
_id is 12 bytes hexadecimal number unique for every document in a collection.

// trying to insert data with duplicate _id, it will not accept as _id is primary key field
> db.details.insert({_id:1,rno:5,name:"Reena"})
E11000 duplicate key error index: db1.emp.$_id_ dup key: { : 1.0 }

//Insert multiple documents at once

> db.details.insert([{rno:7,name:'a'},{rno:8,name:'b'},{rno:8,name:'c'}])

// to insert multiple values for one key using []

> db.emp.insert({rno:10,name:'Ankit',hobbies:['singing','cricket','swimming',’music’],age:21})

// Embedded document example

> db.emp.insert({rno:11, Name: {Fname:"Bhavana", Mname:"Amit", Lname:"Khivsara"}})
> db.emp.insert({rno:12, Name: "Janvi", Address:{Flat:501, Building:"Sai Appart", area:"Tidke colony",
city: "Nashik", state:"MH", pin:423101}, age:22})

// Multi embedded document with data function

> db. details.insert({rno:17, name:"Ashika",date:Date(), awards:[{name:"Best c -Designer", year:2010,
prize:"winner"},{name:"Wen site competition",year:2012,prize:"Runner-up"},{name:"Fashion show",
year:2015,prize:"winner"}], city:"Nashik"})

Find operation

//To display the nested list values

db.details.find({hobbies:'music'})

//To find items in embedded doc

db.details.find({"address.area":'Tidke colony'})

//To find document having city as Nashik(as city is key of address key specify "address.city"
> db. details.find({"address.city":"Nashik"})
{ "_id" : ObjectId("5d83c04aa44331f62bcd8370"), "rno" : 8, "address" : { "area" : "College Road", "city" :
"Nashik", "state" : "MH" }, "name" : "Arya" }

// ouput using pretty command

> db. details.find().pretty()

16
Update operation

//$set to update the value of rno

>db. details.update({rno:2},{$set:{rno:22}})

//upsert use to update document if condition found otherwise insert document with updates
values.
> db. details.update({rno:50},{$set:{rno:55}},{upsert:true})
db. details.update({rno:50},{$set:{rno:55,location:’kerala’}},{upsert:true})

//multi:true used to update in multiple documents

> db. details.update({rno:5},{$set:{rno:15}},{multiple:true})

Delete operation

//It will remove record having rno as 4

> db. details.remove({rno:4})

//It will remove only one record having rno as 4

> db. details.remove({rno:4},1)

//It will remove all records

> db. details.remove({})

db. details.deleteMany({})
db. details.deleteMany( { location: "chennai" } )
db. details.deleteOne( { location: "chennai" } )

17