VISVESVARAYA TECHNOLOGICAL UNIVERSITY

JNANA SANGAMA, BELGAVI-590018, KARNATAKA

A J INSTITUTE OF ENGINEERING & TECHNOLOGY

(A unit of Laxmi Memorial Education Trust. (R))
NH - 66, Kottara Chowki, Kodical Cross, Mangalore- 575 006.

DEPARTMENT OF COMPUTER SCIENCE AND ENGINEERING

(Accredited by NBA)

MASTER MANUAL
Course: DBMS LABORATORY

(Subject Code: BCS403)

IV-SEMESTER

Prepared by:
Mrs. Vidya Myageri
Assistant Professor
Department of Computer Science & Engineering, AJIET, Mangalore

ACADEMIC YEAR: 2023-24

Database Management System BCS403

VISION OF THE INSTITUTE

“To produce top-quality engineers who are groomed for attaining excellence in their profession and
competitive enough to help in the growth of nation and global society.”

MISSION OF THE INSTITUTE

M1: To offer affordable high-quality graduate program in engineering with value education
and make the students socially responsible.
M2: To support and enhance the institutional environment to attain research excellence in
both faculty and students and to inspire them to push the boundaries of knowledge base.
M3: To identify the common areas of interest amongst the individuals for the effective
industry- institute partnership in a sustainable way by systematically working together.
M4: To promote the entrepreneurial attitude and inculcate innovative ideas among the
engineering professionals.

VISION OF THE DEPARTMENT

“To adapt the evolutionary changes in computer science and expose the students to the cutting-edge
technologies to produce globally competent professionals.”

MISSION OF THE DEPARTMENT

M1: To ensure holistic professional education in the field of computer science & engineering and
produce efficient industry ready IT graduates capable of solving societal problems to be a part of nation
building.

M2: To provide an inspirational atmosphere in the department to nurture research and innovation
capabilities among students and faculties making them good innovators and visionaries.

M3: To Establish a vibrant Industry-Academic relationships and collaborations among the individuals
having the opportunities to work on the latest technologies and professional challenges with integrity.

M4: To Cultivate students with professional skills, foster innovative research endeavors, and
cultivate entrepreneurial capabilities.

PROGRAM EDUCATIONAL OBJECTIVES (PEOs)

After 4 years of graduation, graduates will be able to
PEO1: To develop in students, the ability to solve real life problems by applying fundamental
science and elementary strengths of computer science courses.

Page | 2
Database Management System BCS403

PEO2: To mould students, to have a successful career in the IT industry where graduates will
be able to design and implement the needs of society and nation.
PEO3: To transform students, to excel in a competitive world through higher education and
indulge in research through continuous learning process

PROGRAM OUTCOMES (POs)

PO1: Engineering Knowledge: Apply the knowledge of mathematics, science, engineering
fundamentals, and an engineering specialization to the solution of complex engineering
problems.
PO2: Problem Analysis: Identify, formulate, review research literature, and analyze
complex engineering problems reaching substantiated conclusions using first principles of
mathematics, natural sciences, and engineering sciences.
PO3: Design/Development of Solutions: Design solutions for complex engineering
problems and design system components or processes that meet the specified needs with
appropriate consideration for the public health and safety, and the cultural, societal, and
environmental considerations.
PO4: Conduct Investigations of Complex Problems: Use research-based knowledge and
research methods including design of experiments, analysis and interpretation of data, and
synthesis of the information to provide valid conclusions.
PO5: Modern Tool Usage: Create, select, and apply appropriate techniques, resources, and
modern engineering and IT tools including prediction and modelling to complex engineering
activities with an understanding of the limitations.
PO6: The Engineer and Society: Apply reasoning informed by the contextual knowledge
to assess societal, health, safety, legal and cultural issues and the consequent responsibilities
relevant to the professional engineering practice.
PO7: Environment and Sustainability: Understand the impact of the professional
engineering solutions in societal and environmental contexts, and demonstrate the knowledge
of, and need for sustainable development.
PO8: Ethics: Apply ethical principles and commit to professional ethics and responsibilities
and norms of the engineering practice.
PO9: Individual and Team work: Function effectively as an individual, and as a member
or leader in diverse teams, and in multidisciplinary settings.
PO10: Communication: Communicate effectively on complex engineering activities with the
engineering community and with society at large, such as, being able to comprehend and write
effective reports and design documentation, make effective presentations, and give and receive
clear instructions.
PO11: Project Management and Finance: Demonstrate knowledge and understanding of
the engineering and management principles and apply these to one’s own work, as a member
and leader in a team, to manage projects and in multidisciplinary environments.
PO12: Life-Long Learning: Recognize the need for, and have the preparation and ability to
engage in independent and life-long learning in the broadest context of technological change.

Page | 3
Database Management System BCS403

PROGRAM SPECIFIC OUTCOMES (PSOs)

At the end of the program, graduates will be able to
PSO1: Apply engineering principles, professional ethics and fundamental science in designing
systems and communication models (protocols).
PSO2: Design and develop smart and intelligent based applications in computational
environment.
GENERAL LAB GUIDELINES

Do's
1. Maintain discipline in the Laboratory.
2. Before entering the Laboratory, keep the footwear on the shoe rack.
3. Proper dress code has to be maintained while entering the Laboratory.
4. Students should carry a lab observation book, student manual and record book completed in
all aspects.
5. Read and understand the logic of the program thoroughly before coming to the laboratory.
6. Enter the login book before switching on the computer.
7. Enter your batch member names and other details in the slips for hardware kits.
8. Students should be at their concerned places; unnecessary movement is restricted.
9. Students should maintain the same computer until the end of the semester.
10. Report any problems in computers/hardware kits to the faculty member in-charge/laboratory
technician immediately.
11. The practical result should be noted down into their observation and the result must be shown
to the faculty member in-charge for verification.
12. After completing the experiments, students should switch off the computers, enter logout
time, return the hardware kits and keep the chairs properly.

Don'ts
1. Do not come late to the Laboratory.
2. Do not enter the laboratory without an ID card, lab dress code, observation book and record.
3. Do not leave the laboratory without the permission of the faculty in-charge.
4. Never eat, drink while working in the laboratory.
5. Do not handle any equipment before reading the instructions/instruction manuals.
6. Do not exchange the computers with others and hardware kits also.
7. Do not misbehave in the laboratory.
8. Do not alter computer settings/software settings.
9. External Disk/drives should not be connected to computers without permission, doing so
will attract fines.
10. Do not remove anything from the kits/experimental set up without permission. Doing so will
attract fines.
11. Do not mishandle the equipment / Computers.
12. Do not leave the laboratory without verification of hardware kits by the lab instructor.
13. Usage of Mobile phones, tablets and other portable devices are not allowed in restricted
places.
Page | 4
Database Management System BCS403

INSTRUCTIONS TO STUDENTS

● Students must bring Observation book, record and manual along with pen, pencil, and eraser etc.,
no borrowing from others.
● Students must handle the trainer kit and other components carefully, as they are expensive.
● Before switch on the trainer kit, must show the connections to one of the faculties or instructors.
● After the completion of the experiment should return the components to the respective lab
instructors.
● Before leaving the lab, should check whether they have switch off the power supplies and keep
their chairs properly.
● Be regular to the Lab Do not come late to the Lab
● Do not throw connecting wires on the Floor
● Wear your College ID card Do not operate the IC trainer kits without permission
● Avoid unnecessary talking while doing the experiment
● Avoid loose connection and short circuits
● Take the signature of the lab in charge before taking the components
● Do not interchange the ICs while doing the experiment
● Handle the trainer kit properly
● Do not panic if you do not get the output
● Keep your work area clean after completing the experiment.
● After completion of the experiment switch off the power and return the components
● Arrange your chairs and tables before leaving.

Page | 5
Database Management System BCS403

RULES FOR MAINTAINING LABORATORY RECORD

● Put your name, USN and subject on the outside front cover of the record. Put that
same information on the first page inside.
● Update Table of Contents every time you start each new experiment or topic
● Always use pen and write neatly and clearly
● Start each new topic (experiment, notes, calculation, etc.) on a right-side (odd numbered) page
● Obvious care should be taken to make it readable, even if you have bad handwriting
● Date to be written every page on the top right side corner
● On each right-side page

⮚ Title of experiment
⮚ Aim/Objectives
⮚ Components Required
⮚ Theory
⮚ Procedure described clearly in steps
⮚ Result
● On each left side page
⮚ Pin diagrams
⮚ Circuit diagram
⮚ Tables
⮚ Graphs
● Use labels and captions for figures and tables
● Attach printouts and plots of data as needed. Stick printouts (A4 Size) on the right
ide of the lab record

● Strictly observe the instructions given by the Teacher/ Lab Instructor.

Page | 6
Database Management System Lab BCS403

SYLLABUS
DBMS LABORATORY WITH MINI PROJECT
[As per Choice Based Credit System (CBCS) scheme] (Effective
from the academic year 2023 -2024) SEMESTER – IV

Subject Code BCS403 CIE Marks 50

Number of Lecture Hours/Week 0:0:2:0 SEE Marks 50
Total Number of Lecture Hours 8-10 lab slots Exam Hours 03
CREDITS – 04
Course objectives:
● To Provide a strong foundation in database concepts, technology, and practice.
● To Practice SQL programming through a variety of database problems.
● To Understand the relational database design principles.
● To Demonstrate the use of concurrency and transactions in database.
● To Design and build database applications for real world problems.
● To become familiar with database storage structures and access techniques.

Teaching-Learning Process
These are sample Strategies, which teachers can use to accelerate the attainment of the various course
outcomes.
1. Lecturer method (L) needs not to be only a traditional lecture method, but alternative effective
teaching methods could be adopted to attain the outcomes.
2. Use of Video/Animation to explain functioning of various concepts.
3. Encourage collaborative (Group Learning) Learning in the class.
4. Ask at least three HOT (Higher order Thinking) questions in the class, which promotes critical
thinking.
5. Adopt Problem Based Learning (PBL), which fosters students’ Analytical skills, develop design
thinking skills such as the ability to design, evaluate, generalize, and analyze information rather than
simply recall it.
6. Introduce Topics in manifold representations.
7. Show the different ways to solve the same problem with different circuits/logic and
encourage the students to come up with their own creative ways to solve them.
8. Discuss how every concept can be applied to the real world - and when that's possible, it helps
improve the students' understanding
9. Use any of these methods: Chalk and board, Active Learning, Case Studies
MODULE1.

Laboratory Component

Page | 7
Database Management System Lab BCS403

1 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 theuser.
2. Insert the any three records in the employee table contains attributes
EMPNO,ENAME 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.

2 Create a table called Employee that contain attributes EMPNO,ENAME,JOB,

MGR,SAL &
execute the following.
1. Add a column commission with domain to the Employeetable.
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.
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.
2. Count number of employee names from employeetable
3. Find the Maximum age from employee table.
4. Find the Minimum age from employeetable.
5. Find salaries of employee in Ascending Order.
6. Find grouped salaries of employees.
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)
5 Create cursor for Employee table & extract the values from the table. Declare
the variables
,Open the cursor & extrct the values from the cursor. Close the cursor.
Employee(E_id, E_name, Age, Salary)
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.
7 Install an Open Source NoSQL Data base MangoDB & perform basic
CRUD(Create, Read,Update & Delete) operations. Execute MangoDB basic
Queries using CRUD operations.

Page | 8
Database Management System Lab BCS403

Course outcomes (Course Skill Set):

At the end of the course, the student will be able to:
● Describe the basic elements of a relational database management system
● Design entity relationship for the given scenario.
● Apply various Structured Query Language (SQL) statements for database manipulation.
● Analyse various normalization forms for the given application.
● Develop database applications for the given real world problem.
● Understand the concepts related to NoSQL databases.

Suggested Learning Resources:

Text Books:
1. Fundamentals of Database Systems, Ramez Elmasri and Shamkant B. Navathe, 7th Edition,
2017, Pearson.
2. Database management systems, Ramakrishnan, and Gehrke, 3rd Edition, 2014, McGraw
Hill
Activity Based Learning (Suggested Activities in Class)/ Practical Based learning
Mini Project:

• Project Based Learning

Page | 9
Database Management System Lab BCS403

ASSESSMENT PROCESS FOR INTEGRATED PROFESSIONAL CORE COURSE (IPCC)

IPCC
DATABASE MANAGEMENT
SYSTEM [BCS403]

Continuous Internal Semester End

Evaluation (CIE) Exam (SEE) weightage
weightage is 50% is 50%

Internal Assignment Practical Sessions

Assessment (IA) (Assign) weightage is
25%
weightage is weightage is [25 MARKS]

Practical Sessions- CE Practical Sessions-IA

weightage is weightage
15% is10%
[15 MARKS] [10 MARKS]

Page | 10
Database Management System BCS403

ASSESSMENT DETAILS (BOTH CIE AND SEE)

● The weightage of Continuous Internal Evaluation (CIE) is 50% and for Semester End Exam (SEE)
is 50%.
● The minimum passing mark for the CIE is 40% of the maximum marks (20 marks).
● A student shall be deemed to have satisfied the academic requirements and earned the credits
allotted to each subject/ course if the student secures not less than 35% (18 Marks out of 50) in the
semester-end examination (SEE), and a minimum of 40% (40 marks out of 100) in the sum total
of the CIE (Continuous Internal Evaluation) and SEE (Semester End Examination) taken together

CONTINUOUS INTERNAL EVALUATION (CIE):

CIE of Theory:
❖ Two Unit Tests each of 25 Marks (duration 01 hour)
-scale down the average marks to 15 marks.
❖ Two assignments each of 10 Marks(Any Two Assessment Methods are followed)
-scale down the average marks to 10 marks.
❖ Total CIE=25 Marks(minimum 10 marks)
CIE of Practical:
❖ Practical Sessions need to be assessed by appropriate rubrics and viva-voce method. This will
contribute to 25 marks(minimum 10 marks).
Note: Minimum of 80% of the laboratory components have to be coveredNote: Minimum of
80% of the laboratory components have to be covered.

Split-up of Marks used Practical Sessions:

Split Practical Sessions- Continuation Evaluation (CE) Marks

up of Methodology / Process Steps per Experiment
Marks
#R1 Observation, Write up of Procedure / Algorithm/ Program and 07
Execution of experiment
#R2 Record writing 05
#R3 Viva – Voce (Questions & Answers on relevant Experiment /Topic) 03
Total Marks for each experiment 15 marks
Practical Sessions-Internal Assessment (IA)

Page | 3
 Database Management System BCS403

#R1 Write-up of Procedure/Program/Algorithm 15

#R2 Conduction/Execution 25
#R3 Viva-Voce 10
Total Marks 50(Scale down to
10 marks)

● The sum of two tests, two assignments, and practical sessions will be out of 50 marks and will
CIE methods /question paper has to be designed to attain the different levels of Bloom’s
taxonomy as per the outcome defined for the course

SEMESTER END EXAMINATION(SEE):

● Theory SEE will be conducted by University as per the scheduled timetable, with common
question papers for the subject (duration 03 hours)
1. The question paper will have ten questions. Each question is set for 20 marks.
2. There will be 2 questions from each module. Each of the two questions under a module
(with a maximum of 3 sub-questions), should have a mix of topics under that module.
● The students have to answer 5 full questions, selecting one full question from each module

Page | 4
 Database Management System BCS403

RUBRICS FOR PRACTICAL SESSIONS

Practical Sessions- Continuous Evaluation (CE)

Evaluation Level of Achievement
Parameter
#R1: Excellent (7-6) Good (5-3) Average (2-1) Poor (1-0)
Observation Observation neatly Observation neatly Observation is written in Observation is
/ written. Handwriting is written. Handwriting is unclear manner. written in unclear
Conduction clear. Programs written clear. Programs written Handwriting is not very manner. Handwriting
(07 Marks) with no mistakes. with very few mistakes. clear. Programs written is not clear. Programs
Programs executed with Programs executed with with fewer mistakes. executed with a large
no errors very less errors Programs executed with number of errors.
few errors
#R2: Excellent (05-04) Good (3-2) Average (2-1) Poor (1-0)
Record Record is neatly written, Record is neatly written, Record is written in an Record is written in
(05 Marks) handwriting is clear. handwriting is clear. Most unclear manner. an unclear manner..
Mistakes are covered and mistakes are covered and Mistakes are sometimes Mistakes are not
corrected properly and corrected properly and corrected properly. corrected. Record
neatly. Record submitted neatly. Record submitted Record submitted with a submitted after a
on time with a delay of 1 - 3 days delay of 4 to 5 days delay of 1 week
#R3: Excellent (3) Good (2) Average (1) Poor (0)
Viva Answered all questions Answered most of the Answered a few Not able to answer
(03 Marks) with elaboration has questions Failed to questions. Subject any of the questions.
excellent understanding elaborate some of the knowledge is not Subject knowledge
of the topic. concepts adequate not adequate
Practical Sessions- Internal Assessment (IA)
#R1: Excellent (15-12) Good (11-09) Average (08-5) Poor (4-0)
Write-Up Program neatly written. Program neatly written. Program is written in Program is written in
(15Marks) Handwriting is clear. Handwriting is clear. unclear manner. unclear manner.
Programs written with no Programs written with Handwriting is not very Handwriting is not
mistakes. very few mistakes. clear. Programs written clear, Programs
with fewer mistakes. written with lot of
mistakes.
#R2: Excellent (25-18) Good (17-11) Average (10-5) Poor (5-0)
Conduction/ Execution of the program Execution of the program Execution of the Execution of the
Execution done as per the done as per the procedure. program done as per the program was not
(25 Marks) procedure.. The Result Programs had less than 20 procedure. The Result done as per the
was tabulated for all the errors. The errors were was tabulated for few of procedure. The errors
cases. debugged with a little the cases were resolved by the
help. The Result was instructor. The Result
tabulated for almost all was tabulated only
the cases for 1 or 2 Cases

#R3: Excellent (10-6) Good (5-3) Average (2-1) Poor (1-0)

Viva Answered all questions Answered most of the Answered a few Not able to answer
(10 Marks) with elaboration has questions Failed to questions. Subject any of the questions.
excellent understanding elaborate some of the knowledge is not Subject knowledge
of the topic. concepts adequate not adequate

Course Instructor Domain Co-Ordinator Head of Department

Page | 5
Database Management System BCS403

LIST OF MAJOR EQUIPMENT

Sl. Name of the Equipment Specialization Quantity

No.
1 Desktop Intel(R) Core (TM) i3-4170 3, 8.00 GB RAM, 36
500 GB HDD, 18.5”ACER- Monitor, Keyboard
and Mouse.

2 UPS 20KVA 1

3 Switches 24 Port Gigabytes 1

4 Internet 150mbps 1

5 Projector EPSON Projector with HDMI Port 1

Room Number : A-304

Total Area of the laboratory : 115 Sq. Meters

Total Amount Spent : Rs. 12, 30, 563 /-

Name of the HOD : Dr. Antony P. J

Name of the lab in charge : Mrs.Shilpa Shetty

Name of the lab instructor : Mrs. Ashalatha A.S

Page | 6
Database Management System BCS403

INTRODUCTION TO DBMS COMMAND’S

INTRODUCTION TO ORACLE

SQL
SQL stands for Structured Query Language. SQL is used to create, remove, alter the
database and database objects
in a database management system and to store, retrieve, update the data in a database.
SQL is a standard language
for creating, accessing, manipulating database management system. SQL works for all
modern relational database
management systems, like SQL Server, Oracle, MySQL, etc.

Different types of SQL commands

SQL commands can be categorized into five categories based on their functionality

Different types of SQL commands

SQL commands can be categorized into five categories based on their functionality

DDL (Data Definition language)

A Data Definition Language (DDL) statement is used to define the database structure
or schema.
Aim: To study and execute the DDL commands in RDBMS.
DDL commands:
1. CREATE
2. ALTER
3. DROP
4. RENAME
5. TRUNCATE
1. CREATE Command
CREATE is a DDL command used to create databases, tables, triggers and other
database objects.
Syntax to create a new table:
CREATE TABLE table_name
(
column_Name1 data_type ( size of the column ) ,
column_Name2 data_type ( size of the column) ,
column_Name3 data_type ( size of the column) ,
...

Dept of CSE,AJIET, Mangalore 1

Database Management System BCS403

column_NameN data_type ( size of the column )

);
Suppose, you want to create a Student table with five columns in the SQL
database. To do this, you have to write the following DDL command:

Example 1:
CREATE TABLE Student
(
Roll_No. int ,
First_Name varchar (20) ,
Last_Name varchar (20) ,
Age int ,
Marks int,
Dob Date
);
SQL>desc Student;
Example 2:
create table Employee
(
empid varchar(10),
empname varchar(20) ,
gender varchar(7),
age number(3),
dept varchar(15) ,
doj Date
);
SQL> desc Employee

Example 3:
create table BOOK
(
Book_id varchar(4),
Title varchar(10),
Publisher_name varchar(10),
Pub_year int
);
SQL> desc BOOK;

Dept of CSE,AJIET, Mangalore 2

Database Management System BCS403

2.ALTER
This command is used to add, delete or change columns in the existing table. The
user needs to know the existing table name and can do add, delete or modify tasks
easily.

Syntax: –

ALTER TABLE table_name

ADD column_name datatype;

.ADD:
SQL> alter table employee add(designation varchar(15));
Table altered.
II.MODIFY
SQL> alter table employee modify (designation varchar(20));
Table altered
Example 1:
ALTER TABLE Student
ADD CGPA number;
SQL>desc Student;
Example 2:
ALTER TABLE Employee
ADD Salary number;

SQL>desc Employee;

Example 3:
ALTER TABLE BOOK
ADD Author_nmae varchar(20);

SQL>desc Student;

Dept of CSE,AJIET, Mangalore 3

Database Management System BCS403

3. RENAME:
It is possible to change name of table with or without data in it using simple
RENAME command.
We can rename any table object at any point of time.

Syntax –
RENAME <Table Name> To <New_Table_Name>;

Example:
RENAME TABLE Employee To EMP;

4.TRUNCAT:
This command is used to remove all rows from the table, but the structure of the
table still exists.
Syntax –
Syntax to remove an existing table.

TRUNCATE TABLE table_name;

Example:
TRUNCATE TABLE Student;

5. DROP
This command is used to remove an existing table along with its structure from
the Database.
Syntax –
Syntax to drop an existing table.
DROP TABLE table_name;
Example: DROP TABLE Student_info;
DML(DATA MANIPULATION LANGUAGE):
Data manipulation language allows the users to query and manipulate data in
existing schema in object.
It allows following data to insert, delete, update and recovery data in schema
object.
DML COMMANDS:

Dept of CSE,AJIET, Mangalore 4

Database Management System BCS403

❖ INSERT

❖ UPDATE

❖ DELETE
1.INSERT
This command is used to enter the information or values into a row. We can
connect one or more records to a single table within a repository using this
instruction.
Syntax:
Insert into Table_ Name Values(column1, column2, ....);

Example:
CREATE TABLE Student
(
Roll_No int ,
First_Name varchar (20) ,
Last_Name varchar (20) ,
Marks int,
Dob Date
);

SQL>desc Student;
SQL> insert into Student values(‘01’,’Adit,’k’’,25,’11-02-2004’);
SQL> insert into Student values(02,“Arpitha”,”S”, 20,’21-12-2003’);
SQL> insert into Student values(03,“Jorge”,”D”, 20, 18,’10-08-2001’);
Insert 2 more rows.
SQL>desc Student;
2.UPDATE
This allows the user to update the particular column value using the where
clause condition. This command is used to alter existing table records.
Syntax:
UPDATE <table_ name>
SET <column_ name = value>
WHERE condition;

Dept of CSE,AJIET, Mangalore 5

Database Management System BCS403

Example:

UPDATE Students
SET Marks= 21
WHERE First_ name = “Arpitha”;

SQL>desc Students;

3. DELETE
a) Delete some rows
DELETE statement is used to delete rows from a table. Generally DELETE
statement removes one or more records from a table.
Syntax:
DELETE FROM table_name WHERE condition;
Example:
Delete from Students where Roll_no=’111’;

b) Delete All rows:

● It will remove all the rows from the table and does not free the space
contained by the table.
Syntax:
DELETE FROM table_name;
Example:
Delete from student;

DQL(Data Query Language)

DQL stands for the. DQL command is used for fetching the data. DQL command is
used for selecting data from the table, view, temp table, table variable, etc. There
is only one command under DQL which is the SELECT command.
Syntax :SELECT * FROM Employee;

The SELECT statement can be used in many ways.

1. Selecting some columns :

To select specified number of columns from the table the Following command is
used.
Syntax:

Dept of CSE,AJIET, Mangalore 6

Database Management System BCS403

SELECT column_ name FROM table_ name;

Example:
Select First_ name, Last_ name from Students;

SQL> desc Students;

2. Select All Columns:
To select all columns from the table * is used instead of column names.
Syntax:

SELECT * FROM table_name;

Example:
Select * from Students;
SQL> desc Students;

3. Select using DISTINCT:

The DISTINCT keyword is used to return only different values (i.e. ) this
command does not select the duplicate values from the table.
Syntax:
SELECT DISTINCT column name(s) FROM table_name;

Example:
SELECT DISTINCT Roll_No FROM Students;

4. Select using IN:

If you want to get the rows which contain certain values, the best way to do it is
to use the IN conditional expression.
Syntax:
SELECT column name(s) FROM table_name
WHERE Column name IN (value1, value2,……,value-n);

Example:
SELECT * FROM students
WHERE students_name IN ( Arpitha, Jorge);

5.Select using BETWEEN:

BETWEEN can be used to get those items that fall within a range.
Syntax:
SELECT column name FROM table_name
WHERE Column name BETWEEN value1 AND value2;

Dept of CSE,AJIET, Mangalore 7

Database Management System BCS403

Example:
SELECT * FROM student WHERE mark BETWEEN 80 and 100;

6.Renaming:
The select statement can be used to rename either a column or the entire table.
Syntax:
Renaming a column:
SELECT column name AS new name FROM table_name;

Example:

SELECT First_name As Name FROM Students;

Renaming a table:

RENAME old_table_name TO new_table_name;

Example:
RENAME Student TO Stu_details;

7.SELECT DATE
It is used to retrieve a date from a database. If you want to find a particular date
from a database, you can use this statement.
Syntax:
SELECT Column_Names(S) from table-name
WHERE condition(date_column);

Example: SELECT * FROM Students WHERE DOB >= '11-12-2000';

8. SELECT NULL
Null values are used to represent missing unknown data.
There can be two conditions:

1. Where SQL is NULL

2. Where SQL is NOT NULL

If in a table, a column is optional, it is very easy to insert data in column or update

an existing record without adding a value in this column. This means that field has
null value.

Dept of CSE,AJIET, Mangalore 8

Database Management System BCS403

Where SQL is NULL:

Syntax:
SELECT COLUMN_NAME(S) FROM TABLE_NAME
WHERE COLUMN_NAME IS NULL;
Example:
SELECT Student_name, Marks FROM STUDENTS
WHERE MARKS IS NULL;

Where SQL is NOT NULL:

Syntax:
SELECT COLUMN_NAME(S) FROM TABLE_NAME
WHERE COLUMN_NAME IS NOT NULL;
Example:
SELECT Student_name, Marks FROM STUDENTS
WHERE MARKS IS NOT NULL;

ORDER BY Clause
ORDER BY is a clause in SQL which shows the result-set of the SELECT statement
in either ascending or descending order.
a) with one row
Syntax:
SELECT Column_Name FROM Table_Name
ORDER BY Column_Name;
Example:
SELECT * FROM Students ORDER BY Reg_no;

b) With Multiple row

Syntax:
SELECT Column_Name(S) FROM Table_Name
ORDER BY Column_Name(S);
Example:
SELECT reg_no,first_name,marks FROM Students ORDER BY first_name,
marks;

Dept of CSE,AJIET, Mangalore 9

Database Management System BCS403

c) Ascending Order(ASC)/Descending Order(DESC)

SELECT Column_Name(S) FROM Table_Name
ORDER BY Column_Name(S) ASC;
Example:
SELECT * FROM Students ORDER BY Marks ASC;

d) with WHERE Clause

Syntax:
SELECT Column_Name(S)FROM Table_Name

WHERE [condition] ORDER BY Column_Name [ASC | DESC];

Example:
SELECT * FROM Students WHERE Marks >80 ORDER BY Marks;

GROUP BY clause
GROUP BY is an SQL keyword used in the SELECT query for arranging the same
values of a column in the group by using SQL functions.
Syntax:
SELECT Column_Name(S) FROM Table_Name
GROUP BY Column_Name(S);
Example:
SELECT COUNT (marks), Student_name GROUP BY marks;

a) with MIN clause

Group by with MIN clause shows the minimum value for the given where
clause.
Syntax:
SELECT MIN(Column_Name) FROM Table_Name;
Example:
SELECT MIN (Marks) FROM Students;

Dept of CSE,AJIET, Mangalore 10

Database Management System BCS403

b) with MAX clause

Group by with MIN clause shows the minimum value for the given where
clause.
Syntax:
SELECT Column_Name, MAX(Column_Name) FROM Table_Name;
Example:
SELECT Stu_Subject, MAX (Marks) FROM Students;

c) COUNT() Function
The COUNT() function returns the number of rows in a database table.
Syntax:
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )

Example:
1. SELECT COUNT(*)
FROM Student;

2. SELECT COUNT(Marks)
FROM Student
GROUP BY marks
HAVING COUNT(*) > 50;

d) SUM() clause
It is used to return the total summed value of an expression.
Syntax:
SELECT SUM(aggregate_expression)
FROM tables
[WHERE conditions];
Example:
SELECT SUM(Marks)
FROM Student
Where roll_no=’111’;

Dept of CSE,AJIET, Mangalore 11

Database Management System BCS403
DCL (DATA CONTROL LANGUAGE)
DCL stands for data control language. DCL commands are used for providing and
taking back the access rights on the database and database objects. DCL
command used for controlling user’s access on the data. Most used DCL
commands are GRANT and REVOKE

GRANT
used to provide access right to the user.

Syntax: GRANT INSERT, DELETE ON Employee TO user;

REVOKE
REVOKE command is used to take back access right from the user, it cancels
access right of the user from the
database object.

Syntax
REVOKE ALL ON Employee FROM user;

TCL ( Transaction Control Language)

TCL commands are used for handling transactions in the database.
Transactions ensure data integrity in the multi-user environment.

TCL commands can rollback and commit data modification in the database. The
most used TCL commands are COMMIT, ROLLBACK, SAVEPOINT, and SET
TRANSACTION.

COMMIT
COMMIT command is used to save or apply the modification in the database.

ROLLBACK
ROLLBACK command is used to undo the modification.

SAVEPOINT
SAVEPOINT command is used to temporarily save a transaction, the transaction
can roll back to this point when it's needed.

Syntax :
Just write COMMIT or ROLLBACK or SAVEPOINT

Dept of CSE,AJIET, Mangalore 12

Database Management System BCS403
Experiment-1

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 contains attributes
EMPNO, ENAME JOB, MANAGER_NO, SAL, COMMISSION and use rollback.
Check the result.
3. Add primary key constraint and not null constraint to the
employeetable.
4. Insert null values to the employee table and verify the result.

Solutions:
Create a table employee with the given constraints:
SQL> create table employee (empno number,ename varchar2(10), job
varchar2(10),mgr
number,sal number);
SQL> create table employee (empno number,
ename varchar(10),
job varchar(10),
mgr_no number,
sal number,
commission number );
Table created

SQL>desc employee;

Insert any five records into the table

insert into Employee values(101,'abhi','manager',1234,10000,'70');

insert into employee values(102,'rohith','analyst',2345,9000,'65');

insert into employee values(103,'david','analyst',3456,9000,'65');

insert into employee values(104,'rahul','clerk',4567,7000,'55');

insert into employee values(105,'pramod','salesman',5678,5000,'50');

Dept of CSE,AJIET, Mangalore 13

Database Management System BCS403

SQL>select * from Employee;

Solutions:

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

//connect to oracle database first

Provide user name as: ‘/’ as sysdba

//create user

Create user c##dbms identified by dbms403;

User created.

//grant the permission

Grant connect, resource, dba to c##dbms;

Grant succeeded.

//connect to the user now

Connect c##dbms/dbms403;

Connected.

Dept of CSE,AJIET, Mangalore 14

Database Management System BCS403

//check the user now

Show user;

2. Insert the any three records in the employee table and use rollback.
Check the result

SQL>select * from Employee;

Insert new row

SQL> insert into employee values(106,'shashi','HR',5509,50000,'80');

SQL>select * from Employee;

SQL>rollback
Rollback completed

Dept of CSE,AJIET, Mangalore 15

Database Management System BCS403

3. Add primary key constraint and not null constraint to the employee
table.

SQL> alter table employee modify(empno number primary key,

ename varchar(10) not null);
Table altered

SQL>desc Employee;

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

insert into employee values(106,'shashi','HR',5509,’ ‘ ,80);

1 row inserted

SQL> select * from Employee;

Dept of CSE,AJIET, Mangalore 16

Database Management System BCS403

Viva Question:
1. What is data?
Data is a collection of information gathered by observations, measurements,
research or analysis.

2. What is database?
A database is an electronically stored, systematic collection of data. It can contain
any type of data, including words, numbers, images, videos, and files.

3. What is DBMS?
Database Management Systems (DBMS) are software systems used to store,
retrieve, and run queries on data.

4. What is a Database system?

A database is an organized collection of structured information, or data, typically
stored electronically in a computer system.

5. What are the advantages of DBMS?

The advantages of database management include improved data integrity,
consistency, and security, efficient data access and sharing, and reduced data
redundancy and inconsistency.

6. What is relational database?

A relational database is a collection of information that organizes data in
predefined relationships where data is stored in one or more tables (or "relations")
of columns and rows.

7. What is Table?
A table is an arrangement of data in rows and columns, or possibly in a more
complex structure.

Dept of CSE,AJIET, Mangalore 17

Database Management System BCS403

8. What is a Tuple?
A tuple is an ordered sequence of values. The values can be repeated, but their
number is always finite.

9. What is Columns?
column or pillar in architecture and structural engineering is a structural element
that transmits, through compression, the weight of the structure above to other
structural elements below.

10. What is a query?

A query is a question or a request for information expressed in a formal manner.

Dept of CSE,AJIET, Mangalore 18

Database Management System BCS403

Experiment-2

Create a table called Employee that contain attributes EMPNO,ENAME,JOB,

MGR,SAL)
execute the following.
1. Add a column commission with domain to the Employeetable.
2. Insert any five records into the table.
3. Update the column details of job
4. Rename the column of Employee table using alter command.
5. Delete the employee whose Empno is 105.

Solution:

SQL> create table employee (empno number,

ename varchar(10),
job varchar(10),
mgr number,
sal number);
Table created.

SQL> desc employee;

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

SQL> alter table employee add(commission number);

Table altered.

Dept of CSE,AJIET, Mangalore 19

Database Management System BCS403

SQL> desc employee

2. Insert any five records into the table.

Insert any five records into the table

insert into Employee values(101,'abhi','manager',1234,10000,'70');

insert into employee values(102,'rohith','analyst',2345,9000,'65');

insert into employee values(103,'david','analyst',3456,9000,'65');

insert into employee values(104,'rahul','clerk',4567,7000,'55');

insert into employee values(105,'pramod','salesman',5678,5000,'50');

SQL>select * from Employee;

3. Update the column details of job

SQL> update employee set job='trainee' where empno=103;

Dept of CSE,AJIET, Mangalore 20

Database Management System BCS403

1 row updated.

SQL> select * from employee;

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

SQL> alter table employee rename column mgr to manager_no;

Table altered.

SQL>desc employee;

5. Delete the employee whose Empno is 105

SQL> delete employee where empno=105;

1 row deleted

SQL> select * from Employee;

Dept of CSE,AJIET, Mangalore 21

Database Management System BCS403

Viva Questions

1. What is an Attribute?
A quality, character, or characteristic ascribed to someone or something has
leadership attributes.

2. What is Single valued Attributes ?

Single-valued attributes Single-valued attributes accept only one value. For

single-valued attributes, the syntax is: attribute = value attribute = "value with
spaces" Multi-valued attributes.

3. What is Multi valued Attributes?

A multivalued attribute of an entity is an attribute that can have more than one
value associated with the key of the entity.

4. What is Compound /Composite Attribute?

A multivalued attribute of an entity is an attribute that can have more than one
value associated with the key of the entity.

5. What is Simple/Atomic Attributes?

A simple, or atomic, attribute is one that cannot be decomposed into meaningful
components.

Dept of CSE,AJIET, Mangalore 22

Database Management System BCS403

6. What is Stored Attribute?

Stored attributes are those attributes that are stored in the physical database for
e.g date of birth.

7. What is Derived Attribute ?

A derived attribute is one that can be figured out from other information. An
example is "age". A person's age can be derived from date of birth.

8. What is Complex Attributes?

Complex attributes are formed by grouping together the attributes of composite
and multi-valued attributes.

9. What is Key Attribute ?

In DBMS, key attributes refer to the specific fields or columns in a table that are
used to uniquely identify each record in the table.

10. What is Non Key Attributes ?

The values of a primary key cannot be duplicated. Non-prime (non-key)
attributes are those that are not the primary key attributes.

Dept of CSE,AJIET, Mangalore 23

Database Management System BCS403

Experiment-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.
2. Count number of employee names from employee table
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.

Solution:
1. Create Employee table containing all Records E_id, E_name, Age, Salary.

SQL> create table employee (E_id number,

E_name varchar(10),
age number,
sal number);
Table created.

SQL>desc Employee;

2. Count number of employee names from employee table

Insert any five records into Employee table

insert into Employee values(10,'abhi',25 ,10000);

insert into employee values(20,'rohith',30,9000);

insert into employee values(30,'david',28,9000);

insert into employee values(40,'rahul',29,7000);

insert into employee values(50,'pramod',31,8000);

Dept of CSE,AJIET, Mangalore 24

Database Management System BCS403

SQL>select * from employee;

SQL> select count(E_name) from Employee;

3.Find the Maximum age from employee table.

SQL>select max(age) from Employee;

4. Find the Minimum age from employee table

SQL>select min(age) from Employee;

Dept of CSE,AJIET, Mangalore 25

Database Management System BCS403

5. Find salaries of employee in Ascending Order.

SQL> SELECT * FROM Students ORDER BY salary ASC;

6. Find grouped salaries of employees.

SQL> select salary from employee group by salary;

Dept of CSE,AJIET, Mangalore 26

Database Management System BCS403

Viva Questions

1. What is an Attribute?
A table consists of several records(row), each record can be broken down into
several smaller parts of data known as Attributes. The above Employee table
consist of four attributes, ID, Name, Age and Salary.

2. What is Single valued Attributes ?

An attribute, that has a single value for a particular entity. For example, age of a
employee entity.

3. What is Multi valued Attributes?

An attributes that may have multiple values for the same entity. For example
colors of a car entity.

4. What is Compound /Composite Attribute?

Attribute can be subdivided into two or more other Attribute. For Example, Name
can be divided into First name, Middle name and Last name.

5. What is Simple/Atomic Attributes?

The attributes which cannot be divided into smaller subparts are called simple or
atomic attributes. For example, age of employee entity

6. What is Stored Attribute?

An attribute, which cannot be derived from other attribute, is known as stored
attribute. For example, BirthDate of employee.

7. What is Derived Attribute ?

Attributes derived from other stored attribute. For example age from Date of
Birth and Today’s date.

Dept of CSE,AJIET, Mangalore 27

Database Management System BCS403

8. What is Complex Attributes?

If an attribute of an entity, is built using composite and multivalued attributes,
then these attributes are called complex attributes. For example, a person can have
more than one residence and each residence can have multiple phones, an
addressphone for a person entity can be specified as – {Addressphone (phone
{(Area Code, Phone Number)}, Address(Sector Address (Sector Number,House
Number), City, State, Pin))}.Here {} are used to enclose multivalued attributes
and () are used to enclose composite attributes with comma separating individual
attributes.

9. What is Key Attribute ?

It represents primary key. It is an attribute, that has distinct value for each
entity/element in an entity set. For example, Roll number in a Student Entity Type.

10. What is Non Key Attributes ?

These are attributes other than candidate key attributes in a table. For example
Firstname is a non key attribute as it does not represent the main characteristics of
the entity.

Dept of CSE,AJIET, Mangalore 28

Database Management System BCS403

Experiment 4:
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)

Solution:
1. Create Customer Table:

SQL> create table Customers (id number,

name varchar(10),
age number,
sal number,
address varchar(50));

Table created.

2. Insert values into the table

Insert any five records into Customers table

insert into Customers values(10,'abhi',25 ,10000,”Bangalore”);

insert into Customers values(20,'rohith',30,9000, ”Delhi”);

insert into Customers values(30,'david',28,9000, ”Pune”);

insert into Customers values(40,'rahul',29,7000, ”Mumbai”);

insert into Customers values(50,'pramod',31,8000,”Mysore”);

ID Name Age Sal Address

10 Abhi 25 10000 Bangalore

20 Rohith 30 9000 Delhi
30 David 28 9000 Pune
40 Rahul 29 7000 Mumbai
50 Pramod 31 8000 Mysore

Dept of CSE,AJIET, Mangalore 29

Database Management System BCS403

3.Creating trigger

SQL>set serveroutput on;

1 create or replace TRIGGER sal_diff
2 Before Delete or INSERT OR UPDATE on Customer

3 for each row

4 when(new.id>0)
5 Declare
6 sal_diff number;
7 BEGIN
8 sal_diff := :NEW.sal - :OLD.sal;
9 dbms_output.put_line('Previous salary: ' || : OLD.sal);
10 dbms_output.put_line('Current salary ' || : NEW.sal);
11 dbms_output.put_line ('salary difference: ' || sal_diff);
12 END;
13 /

Trigger created.

4.Finding salary difference

SQL>UPDATE CUSTOMERS SET salary =’10000’ where id=’50’;

Previous salary:8000
Current salary: 10000
Salary difference:2000

Dept of CSE,AJIET, Mangalore 30

Database Management System BCS403

Viva Questions

1. What is a primary key?

A primary key is a column whose values uniquely identify every row in a table.

2. What are the conditions for a field to be a primary key?

• No two rows can have the same primary key value.

• Every row must have a primary key value.
• The primary key field cannot be null.
• Value in a primary key column can never be modified or updated, if any
foreign key refers to that primary key.

3. What is a Foreign Key ?

When a "one" table's primary key field is added to a related "many" table in
order to create the common field which relates the two tables, it is called a
foreign key in the "many" table.
For example, the salary of an employee is stored in salary table. The relation is
established via foreign key column “Employee_ID_Ref” which refers
“Employee_ID” field in the Employee table.

4. What is Super Key?

A set of attributes (one or more) that collectively identifies an entity in an entity
set.

5. What is Candidate Key

A minimal super key is called a candidate key. An entity set may have more than
one candidate key.

6. What is a query?
A query with respect to DBMS relates to user commands that are used to interact
with a data base. The query language can be classified into data definition
language and data manipulation language.

Dept of CSE,AJIET, Mangalore 31

Database Management System BCS403
7. Define SQL Insert Statement ?
SQL INSERT statement is used to add rows to a table.

8. Define SQL Update Statement ?

SQL Update is used to update data in a row or set of rows specified in the filter
condition.

9. Define SQL Delete Statement ?

SQL Delete is used to delete a row or set of rows specified in the filter condition.

11.What is order by clause?

ORDER BY clause helps to sort the data in either ascending order to descending

Dept of CSE,AJIET, Mangalore 32

Database Management System BCS403

Experiment-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)

Solution:

1. Create table Employee

create table employee (E_id number,

E_name varchar(10),
age number,
sal number);

Table created.

2. Insert values into the table

Insert any five records into Employee table

insert into Employee values(10,'abhi',25 ,10000);

insert into employee values(20,'rohith',30,9000);

insert into employee values(30,'david',28,9000);

insert into employee values(40,'rahul',29,7000);

insert into employee values(50,'pramod',31,8000);

SQL>select * from employee;

Dept of CSE,AJIET, Mangalore 33

Database Management System BCS403

3. Create the cursor ,extracting the value from Employee table and close
the cursor.

SQL> set serveroutput on;

SQL> declare cursor c1 is select id, sal from Cust;
2 vid int;
3 vsal int;
4 begin
5 dbms_output.put_line('Emp ID' ||’ ‘||'Emp sal');
6 dbms_output.put_line(' ----------------------------------------’));
7 open c1;
8 loop
9 fetch c1 into vid,vsal;
10 exit when c1%notfound;
11 dbms_output.put_line(vid || ' ' || vsal);
12 end loop;
13 close c1;
14 end;
15 /

Output:

Dept of CSE,AJIET, Mangalore 34

Database Management System BCS403

Viva Questions

1. Define Normalization.
Organized data void of inconsistent dependency and redundancy within a database is
called normalization.
2. Enlist the advantages of normalizing database.
Advantages of normalizing database are:
• No duplicate entries
• Saves storage space
• Boasts the query performances.

3. What is Entity?
An entity can be a real-world object, either animate or inanimate, that can be easily
identifiable. For example, in a school database, students, teachers, classes, and courses
offered can be considered as entities.

4. What is entity set?

An entity set is a collection of similar types of entities. An entity set may contain
entities with attribute sharing similar values. For example, a Students set may contain
all the students of a school; likewise a Teachers set may contain all the teachers of a
school from all faculties. Entity sets need not be disjoint.

5. What is Relationship?
The association among entities is called a relationship. For example, an
employee works_at a department, a student enrolls in a course. Here, Works_at and
Enrolls are called relationships.

6. What is Relationship Set?

A set of relationships of similar type is called a relationship set.

7. What is Degree of Relationship?

The number of participating entities in a relationship defines the degree of the
relationship.

Dept of CSE,AJIET, Mangalore 35

Database Management System BCS403

8. Name the Degree of Relationship?

• Binary = degree 2
• Ternary = degree 3
• n-ary = degree n

9. What is Data Model?

A collection of conceptual tools for describing data, data relationships data semantics
and constraints.

10. What is E-R model?

This data model is based on real world that consists of basic objects called entities
and of relationship among these objects. Entities are described in a database by a
set of attributes.

Dept of CSE,AJIET, Mangalore 36

Database Management System BCS403

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

Solution:

Create table O-Rollcall:

Create table O_Rollcall

( roll int, name varchar(20));

insert values into the table

insert into O_Rollcall(‘10’,’AJIET’);
insert into O_Rollcall(‘20’.’MITE’);
insert into O_Rollcall(‘30’.’NITTE’);
insert into O_Rollcall(‘40’.’RVC’);
insert into O_Rollcall(‘50’.’IIT’);

SQL>select * from O_Rollcall;

Dept of CSE,AJIET, Mangalore 37

Database Management System BCS403

Create table N_Rollcall:

Create table N_Rollcall

( roll int, name varchar(20));

insert values into the table

insert into N_Rollcall(‘60’,’ALIET’);
insert into N_Rollcall(‘70’.’NITK’);
insert into N_Rollcall(‘80’.’MIT’);
insert into N_Rollcall(‘40’.’RVC’);
insert into N_Rollcall(‘50’.’IIT’);

SQL>select * from N_Rollcall;

Create Procedure roll_details:

SQL>create procedure roll_details AS

2 rno1 int;
3 nm1 varchar(20);
4 rno2 int;
5 nm2 varchar(20);
6 done number :=0;
7 cursor c1 IS select roll,name from O_Rollcall;
8 cursor c2 IS select roll,name from N_Rollcall
9 begin
10 open c1;
11 loop

Dept of CSE,AJIET, Mangalore 38

Database Management System BCS403

12 fetch c1 into rno1,nm1;

13 exit when c1%notfound;
14 done :=0;
15 open c2;
16 loop
17 fetch c2 into rno2,nm2;
18 exit when c2%notfound;
19 if rno1=rno2 then
20 exit;
21 end if;
22 end loop;
23 if c2%notfound then
24 insert into N_Rollcall values(rno1,nm1);
25 end if;
26 close c2;
27 end loop;
28 close c1;
29 end;
30 /

Procedure created.

SQL>call roll_details();

Call completed.

SQL>select * from N_Rollcall;

Dept of CSE,AJIET, Mangalore 39

Database Management System BCS403

Viva Questions

1. What is Mapping Cardinalities

Cardinality defines the number of entities in one entity set, which can be associated
with the number of entities of other set via relationship set.

2. What are the different types of Mapping

• One to one
• One to many
• Many to one
• Many to many

3. What is One-to-one mapping?

One entity from entity set A can be associated with at most one entity of entity set B
and vice versa.

4. What is One-to-many mapping?

One entity from entity set A can be associated with more than one entities of entity set
B however an entity from entity set B, can be associated with at most one entity.

Dept of CSE,AJIET, Mangalore 40

Database Management System BCS403

5. What is Many-to-one mapping?

More than one entities from entity set A can be associated with at most one entity
of entity set B, however an entity from entity set B can be associated with more than
one entity from entity set A.

6. What is Many-to-many mapping?

One entity from A can be associated with more than one entity from B and vice
versa.

7. What is DDL?

DDL stands for Data Definition Language. SQL queries like CREATE,
ALTER, DROP and RENAME come under this.

8. What
is DML?
DML stands for Data Manipulation Language. SQL queries like SELECT,
INSERT and UPDATE come under this.

9. What is
DCL?
DCL stands for Data Control Language. SQL queries like GRANT and
REVOKE come under this.

Dept of CSE,AJIET, Mangalore 41

 Database Management System BCS403

Experiment-7:

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

How to Install and Configure MongoDB in Ubuntu?

MongoDB is a popular NoSQL database offering flexibility, scalability, and ease of use. Installing
and configuring MongoDB in Ubuntu is a straightforward process, but it requires careful attention to
detail to ensure a smooth setup.
In this guide, we’ll learn how to install and configure MongoDB in Ubuntu. We’ll walk you through
each step, from installation to configuration, enabling you to harness the power of MongoDB on
your Ubuntu system.
Let’s look at the requirements for installing MongoDB in Ubuntu.

Steps to Install and Configure MongoDB in Ubuntu

MongoDB can be installed on Ubuntu with the use of the following commands. These commands
are easy to run on the terminal and make the installation process handy. Follow the steps given
below to install MongoDB:
Step 1: First you need to update and upgrade your system repository to install MongoDB. Type the
following command in your terminal and then press Enter.

sudo apt update && sudo apt upgrade

Step 2: Now, install the MongoDB package using ‘apt‘. Type the following command and press
Enter.

sudo apt install -y mongodb

Dept of CSE,AJIET, Mangalore 42

Database Management System BCS403

Step 3: Check the service status for MongoDB with the help of following command:

sudo systemctl status mongodb

systemctl verifies that MongoDB server is up and running.

Step 4: Now check if the installation process is done correctly and everything is working fine. Go
through the following command:

mongo --eval 'db.runCommand({ connectionStatus: 1 })'

Dept of CSE,AJIET, Mangalore 43

Database Management System BCS403

the value “1” in ok field indicates that the server is working properly with no errors.

Step 5: MongoDB services can be started and stopped with the use of following commands: To stop
running the MongoDB service, use command :

sudo systemctl stop mongodb

MongoDB service has been stopped and can be checked by using the status command:

sudo systemctl status mongodb

Dept of CSE,AJIET, Mangalore 44

 Database Management System BCS403
As it can be seen that the service has stopped, to start the service we can use :
sudo systemctl start mongodb

Step 6: Accessing the MongoDB Shell

MongoDB provides a command-line interface called the MongoDB shell, which allows you to
interact with the database.
To access the MongoDB shell, simply type the following command in your terminal:

mongo

You are now connected to the MongoDB server, and you can start executing commands
to create databases, collections, and documents.

CRUD Operations:
1. Create (Insert)

To create or insert data into a MongoDB collection, you use the insertOne() or
insertMany() methods.

Insert a single document:

db.collection('yourCollection').insertOne({ key: value });

Insert multiple documents:

db.collection('yourCollection').insertMany([
{ key1: value1 },
{ key2: value2 },

Dept of CSE,AJIET, Mangalore 45

 Database Management System BCS403
// more documents
]);

2. Read (Query)

To read or retrieve data from a MongoDB collection, you use the find() method.

Find all documents:

db.collection('yourCollection').find();

Find documents with a specific condition:

db.collection('yourCollection').find({ key: value });

3. Update

To update existing documents in a MongoDB collection, you use the updateOne() or updateMany()
methods.

Update a single document:

db.collection('yourCollection').updateOne(

{ key: value }, // filter

{ $set: { newField: newValue } } // update operation

);

Update multiple documents:

db.collection('yourCollection').updateMany(

{ key: value }, // filter

{ $set: { newField: newValue } } // update operation

);

4. Delete

To delete documents from a MongoDB collection, you use the deleteOne() or deleteMany() methods.

Delete a single document:

db.collection('yourCollection').deleteOne({ key: value });

Delete multiple documents:

db.collection('yourCollection').deleteMany({ key: value });

Dept of CSE,AJIET, Mangalore 46

Database Management System BCS403
Viva Questions

1. How do you perform CRUD operations create, read, update, deleteMongoDB?

MongoDB CRUD Operations

The Create operation is used to insert new documents in the MongoDB database.The Read
operation is used to query a document in the database.
The Update operation is used to modify existing documents in the database.The Delete
operation is used to remove documents in the database.
2. What are the CRUD operations in NoSQL database?

CRUD is the acronym for CREATE, READ, UPDATE and DELETE. These terms describe the
four essential operations for creating and managing persistentdata elements, mainly in relational
and NoSQL databases.

3. How to update in CRUD operations?

You can perform update, insert and delete operation in the Grid. While performingthese
operations, the corresponding event is invoked. In that event SQL query is used to update the
database. The events for performing CRUD operation are declared.

4. How can we create updating and deleting documents in MongoDB?

The MongoDB shell provides the following methods to update documents in acollection:
1. To update a single document, use db. collection. updateOne() .
2. To update multiple documents, use db. collection. updateMany() .
3. To replace a document, use db. collection. replaceOne() .

5.What is the full form of CRUD in MongoDB?

The basic methods of interacting with a MongoDB server are called CRUD operations. CRUD
stands for Create, Read, Update, and Delete. These CRUDmethods are the primary ways you will
manage the data in your databases.

6. What are the CRUD methods in REST API?

CRUD stands for Create, Read, Update, and Delete. These are the four fundamental operations
of persistent storage. In the context of RESTful APIs , they correspond to the HTTP methods
POST, GET, PUT/PATCH, and DELETE.

7.How to create a collection in MongoDB?

Several ways can be employed to create and remove collections in MongoDB. Of which one
way is by using db. Create Collection (name, options). MongoDB creates a collection for an
inserted command automatically if no similar collectionalready exists in the MongoDB
database.

Dept of CSE,AJIET, Mangalore 47