Dayananda Sagar Academy of Technology & Management

Opp. Art of living, Udayapura, Kanakapura road, Bengaluru- 560082

(Affiliated to VTU, Belagavi & Approved by AICTE, New Delhi)

Department of Information Science & Engineering

Accredited by NBA, New Delhi

2023-2024
Database Management Laboratory
BCS403
TABLE OF CONTENTS
Sl.NO Experiments
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 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.
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.
● Analyze various normalization forms for the given application.
● Develop database applications for the given real-world problem.
● Understand the concepts related to NoSQL databases.
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 out of 50) and for the SEE minimum
passing mark is 35% of the maximum marks (18 out of 50 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 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.
CIE for the theory component of the IPCC (maximum marks 50)
● IPCC means practical portion integrated with the theory of the course.
● CIE marks for the theory component are 25 marks and that for the practical component is 25 marks.
● 25 marks for the theory component are split into 15 marks for two Internal Assessment Tests (Two
Tests, each of 15 Marks with 01-hour duration, are to be conducted) and 10 marks for other assessment
methods mentioned in 22OB4.2. The first test at the end of 40-50% coverage of the syllabus and the
second test after covering 85-90% of the syllabus.
● Scaled-down marks of the sum of two tests and other assessment methods will be CIE marks
for the theory component of IPCC (that is for 25 marks).
● The student has to secure 40% of 25 marks to qualify in the CIE of the theory component of IPCC.
CIE for the practical component of the IPCC
● 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.
● On completion of every experiment/program in the laboratory, the students shall be evaluated
including viva-voce and marks shall be awarded on the same day.
● The CIE marks awarded in the case of the Practical component shall be based on the continuous
evaluation of the laboratory report. Each experiment report can be evaluated for 10 marks. Marks of all
experiments’ write-ups are added and scaled down to 15 marks.
● The laboratory test (duration 02/03 hours) after completion of all the experiments shall be conducted
for 50 marks and scaled down to 10 marks.
● Scaled-down marks of write-up evaluations and tests added will be CIE marks for the laboratory
component of IPCC for 25 marks.
● The student has to secure 40% of 25 marks to qualify in the CIE of the practical component of the IPCC.
 DAYANANDA SAGAR ACADEMY OF TECHNOLOGY & MANAGEMENT
(Affiliated to Visvesvaraya Technological University,Belagavi & Approved by AICTE,New Delhi)
Opp. Art of Living, Udayapura, Kanakapura Road, Bangalore – 560082
DEPARTMENT OF INFORMATION SCIENCE & ENGINEERING
Accredited by NBA, New Delhi

INSTITUTION VISION AND MISSION

Vision of the Institution

To strive at creating the institution a center of highest caliber of learning, so as to create an overall
intellectual atmosphere with each deriving strength from the other to be the best of engineers, scientists
with management &design skills.

Mission of the Institution:

 To serve its region, state, the nation and globally by preparing students to make
 meaningful contributions in an increasing complex global society challenges.
 To encourage, reflection on and evaluation of emerging needs and priorities with state of art
infrastructure at institution.
 To support research and services establishing enhancements in technical, health, economic, human and
cultural development.
 To establish inter disciplinary center of excellence, supporting/ promoting student’s implementation.
 To increase the number of Doctorate holders to promote research culture on campus.
 To establish IIPC, IPR, EDC, innovation cells with functional MOU’s supporting student’s quality
growth.
 DAYANANDA SAGAR ACADEMY OF TECHNOLOGY & MANAGEMENT
(Affiliated to Visvesvaraya Technological University,Belagavi & Approved by AICTE,New Delhi)
Opp. Art of Living, Udayapura, Kanakapura Road, Bangalore – 560082
DEPARTMENT OF INFORMATION SCIENCE & ENGINEERING
Accredited by NBA, New Delhi

QUALITY POLICY

Dayananda Sagar Academy of Technology and Management aims at achieving academic

excellence through continuous improvement in all spheres of Technical and Management
education. In pursuit of excellence cutting-edge and contemporary skills are imparted to the
utmost satisfaction of the students and the concerned stakeholders.

OBJECTIVES & GOALS

 Creating an academic environment to nurture and develop competent entrepreneurs,

leaders and professionals who are socially sensitive and environmentally conscious.
 Integration of Outcome Based Education and cognitive teaching and learning
strategies to enhance learning effectiveness.
 Developing necessary infrastructure to cater to the changing needs of Business and
Society.
 Optimum utilization of the infrastructure and resources to achieve excellence in all
areas of relevance.
 Adopting learning beyond curriculum through outbound activities and creative
assignments.
 Imparting contemporary and emerging techno-managerial skills to keep pace with the
changing global trends.
 Facilitating greater Industry-Institute Interaction for skill development and
employability enhancement.
 Establishing systems and processes to facilitate research, innovation and
entrepreneurship for holistic development of students.
 Implementation of Quality Assurance System in all Institutional processes
 DAYANANDA SAGAR ACADEMY OF TECHNOLOGY & MANAGEMENT
Opp. Art of Living, Udayapura, Kanakapura Road, Bangalore- 560082
(Affiliated to Visvesvaraya Technological University, Belagavi & Approved by AICTE, New Delhi)
DEPARTMENT OF INFORMATION SCIENCE AND ENGINEERING
Accredited by NBA, New Delhi

VISION OF THE DEPARTMENT

Impart magnificent learning atmosphere establishing innovative practices among the students aiming to
strengthen their software application knowledge and technical skills.

MISSION OF THE DEPARTMENT

M1: To deliver quality technical training on software application domain.
M2: To nurture teamwork in order to transform individual as responsible leader and entrepreneur for future
trends.
M3: To inculcate research practices in teaching thus ensuring research blend among students.
M4: To ensure more doctorates in the department, aiming at professional strength.
M5: To inculcate the core information science engineering practices with hardware blend by providing
advanced laboratories.
M6: To establish innovative labs, start-ups and patent culture.

Program Educational Objectives (PEOs)

PEO1: Graduates shall have successful careers as information science engineers and will be able to lead and
manage teams across the globe.
PEO2: Graduates shall be professional in engineering practice and shall demonstrate good problem solving,
communication skills and contribute to address societal issues.
PEO3: Graduates shall be pursuing distinctive education, entrepreneurship and research in an excellent
environment which helps in the process of life-long learning.
DAYANANDA SAGAR ACADEMY OF TECHNOLOGY & MANAGEMENT
(Affiliated to Visvesvaraya Technological University,Belagavi & Approved by AICTE,New Delhi)
Opp. Art of Living, Udayapura, Kanakapura Road, Bangalore – 560082
DEPARTMENT OF INFORMATION SCIENCE & ENGINEERING
Accredited by NBA, New Delhi

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 modeling 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
independent and life-long learning in the broadest context of technological change.
\
 DAYANANDA SAGAR ACADEMY OF TECHNOLOGY &
MANAGEMENT
(Affiliated to Visvesvaraya Technological University,Belagavi & Approved by
AICTE,New Delhi) Opp. Art of Living, Udayapura, Kanakapura Road,
Bangalore- 560082
DEPARTMENT OF INFORMATION SCIENCE & ENGINEERING
Accredited by NBA, New Delhi

SUBJECT: Database Management Laboratory

SUBJECT CODE: BCS403

SEMESTER: IV

Course Outcomes
At the end of the course the student will be able to:

CO1 Infer SQL commands to create simple database.

CO2 Create, update and query on the database.

CO3 Demonstrate the working of different concepts of DBMS using SQL.

CO4 Implementation application using database concepts for real time requirements
through mini projects.
DBMS Lab Manual

INTRODUCTION TO SQL

Pronounced as SEQUEL: Structured English QUERY Language

 Pure non-procedural query language
 Designed and developed by IBM, Implemented by Oracle
 1978 System/R IBM- 1st Relational DBMS
 1979 Oracle and Ingres
 1982 SQL/DS and DB2 IBM
 Accepted by both ANSI + ISO as Standard Query Language for any RDBMS
 SQL86 (SQL1) : first by ANSI and ratified by ISO (SQL-87), minor revision on 89
(SQL-89)
 SQL92 (SQL2) : major revision
 SQL99 (SQL3) : add recursive query, trigger, some OO features, and non-scholar type
 SQL2003 : XML, Window functions, and sequences (Not free)
 Supports all the three sublanguages of DBMS: DDL, DML, DCL
 Supports Aggregate functions, String Manipulation functions, Set theory operations,
Date Manipulation functions, rich set of operators ( IN, BETWEEN, LIKE, IS NULL,
EXISTS)
 Supports REPORT writing features and Forms for designing GUI based applications

REQUIREMENTS:

Aim: Study of Open-Source Relational Databases: MySQL

Objective: To learn and understand open-source relational databases.

Hardware requirements: Any CPU with Pentium Processor or similar,

256 MB
RAM or more, 1 GB Hard Disk or more.

Software requirements: Ubuntu 14 Operating System, MySQL WorkBench

Dept. Of ISE, DSATM 2023-2024 Page 1

DBMS Lab Manual

DATA DEFINITION, CONSTRAINTS, AND SCHEMA CHANGES

Used to CREATE, ALTER, and DROP the descriptions of the database tables (relations)
Data Definition in SQL

CREATE, ALTER and DROP

table ................................................................ relation
row .................................................................. tuple
column….........................................................attribute

DATA TYPES
 Numeric: NUMBER, NUMBER(s,p), INTEGER, INT, FLOAT, DECIMAL
 Character: CHAR(n), VARCHAR(n), VARCHAR2(n), CHAR VARYING(n)
 Bit String: BLOB, CLOB
 Boolean: true, false, and null
 Date and Time: DATE (YYYY-MM-DD) TIME( HH:MM:SS)
 Timestamp: DATE + TIME
 USER Defined types

Dept. Of ISE, DSATM 2023-2024 Page 2

DBMS Lab Manual

 CREATE SCHEMA
Specifies a new database schema by giving it a name

Ex: CREATE SCHEMA COMPANY AUTHORIZATION Jsmith;

CREATE TABLE
 Specifies a new base relation by giving it a name, and specifying each of its attributes and
their data types
Syntax of CREATE Command:
CREATE TABLE <table name>( <AttributeA1><Data TypeD1> [<
Constarints>],<Attribute A2><Data Type D2> [< Constarints>],
…….
<Attribute An><Data Type Dn> [< Constarints>],
[<integrity-constraint1>, <integrity-constraint k> ] );
- A constraint NOT NULL may be specified on an
attribute A constraint NOT NULL may be specified on an
attribute Ex: CREATE TABLE DEPARTMENT (
DNAME VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9), MGRSTARTDATE CHAR(9) );
 Specifying the unique, primary key attributes, secondary keys, and referential integrity
constraints (foreign keys).
Ex: CREATE TABLE DEPT ( DNAME
VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9),
MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP(SSN));

Dept. Of ISE, DSATM 2023-2024 Page 3

DBMS Lab Manual

 We can specify RESTRICT, CASCADE, SET NULL or SET DEFAULT on referential

integrity constraints (foreign keys)
Ex: CREATE TABLE DEPT ( DNAME
VARCHAR(10) NOT NULL,
DNUMBER INTEGER NOT NULL,
MGRSSN CHAR(9), MGRSTARTDATE CHAR(9),
PRIMARY KEY (DNUMBER),
UNIQUE (DNAME),
FOREIGN KEY (MGRSSN) REFERENCES EMP
ON DELETE SET DEFAULT ON UPDATE CASCADE);
DROP TABLE
 Used to remove a relation (base table) and its definition.
 The relation can no longer be used in queries, updates, or any other commands since its
description no longer exists

Example: DROP TABLE DEPENDENT;

ALTER TABLE:
 Used to add an attribute to/from one of the base relations drop constraint -- The new
attribute will have NULLs in all the tuples of the relation right after the command is
executed; hence, the NOT NULL constraint is not allowed for such an attribute.
Example: ALTER TABLE EMPLOYEE ADD JOB VARCHAR2 (12);
 The database users must still enter a value for the new attribute JOB for each
EMPLOYEE tuple. This can be done using the UPDATE command.

DROP A COLUMN (AN ATTRIBUTE)

 ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS CASCADE; All

constraints and views that reference the column are dropped automatically, along with the
column. ALTER TABLE COMPANY.EMPLOYEE DROP ADDRESS RESTRICT;
Successful if no views or constraints reference the column. ALTER TABLE
COMPANY.DEPARTMENT ALTER MGRSSN DROP DEFAULT;

 ALTER TABLE COMPANY.DEPARTMENT ALTER MGRSSN SET DEFAULT

“333445555”;

Dept. Of ISE, DSATM 2023-2024 Page 4

DBMS Lab Manual

BASIC QUERIES IN SQL

 SQL has one basic statement for retrieving information from a database; the SLELECT
statement
 This is not the same as the SELECT operation of the relational algebra
 Important distinction between SQL and the formal relational model;
 SQL allows a table (relation) to have two or more tuples that are identical in all their
attribute values
 Hence, an SQL relation (table) is a multi-set (sometimes called a bag) of tuples; it is not a
set of tuples
 SQL relations can be constrained to be sets by using the CREATE UNIQUE INDEX
command, or by using the DISTINCT option
 Basic form of the SQL SELECT statement is called a mapping of a SELECT-FROM-
WHERE block
SELECT <attribute list> FROM <table list> WHERE <condition>
 <attribute list> is a list of attribute names whose values are to be retrieved by the query
 <table list > is a list of the relation names required to process the query
 <condition> is a conditional (Boolean) expression that identifies the tuples to be retrieved
by the query

SIMPLE SQL QUERIES

Basic SQL queries correspond to using the following operations of the relational algebra:
SELECT
PROJECT
JOIN
All subsequent examples uses COMPANY database as shown below:
Example of a simple query on one relation
Query 0: Retrieve the birth date and address of the employee whose name is 'John B.
Smith'.
Q0: SELECT BDATE, ADDRESS FROM EMPLOYEE

Dept. Of ISE, DSATM 2023-2024 Page 5

DBMS Lab Manual

WHERE FNAME='John' AND MINIT='B’ AND LNAME='Smith’

Similar to a SELECT-PROJECT pair of relational algebra operations: The SELECT-
clause specifies the projection attributes and the WHERE-clause specifies the selection condition
However, the result of the query may contain duplicate tuples

Dept. Of ISE, DSATM 2023-2024 Page 6

DBMS Lab Manual

Example of a simple query on two relations

Query 1: Retrieve the name and address of all employees who work for the 'Research'
department.
Q1: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE, DEPARTMENT
WHERE DNAME='Research' AND DNUMBER=DNO
Similar to a SELECT-PROJECT-JOIN sequence of relational algebra operations
(DNAME='Research') is a selection condition (corresponds to a SELECT operation in relational
algebra) (DNUMBER=DNO) is a join condition (corresponds to a JOIN operation in relational
algebra)
Example of a simple query on three relations
Query 2: For every project located in 'Stafford', list the project number, the controlling
department number, and the department manager's last name, address, and birth date.
Q2: SELECT PNUMBER, DNUM, LNAME, BDATE, ADDRESS FROM PROJECT,
DEPARTMENT, EMPLOYEE WHERE DNUM=DNUMBER AND MGRSSN=SSN
AND PLOCATION='Stafford'

In Q2, there are two join conditions The join condition DNUM=DNUMBER relates a project to
its controlling department The join condition MGRSSN=SSN relates the controlling department
to the employee who manages that department

ALIASES, * AND DISTINCT, EMPTY WHERE-CLAUSE

 In SQL, we can use the same name for two (or more) attributes as long as the attributes
are in different relations
 A query that refers to two or more attributes with the same name must qualify the
attribute name with the relation name by prefixing the relation name to the attribute name
Example: EMPLOYEE.LNAME, DEPARTMENT.DNAME
 Some queries need to refer to the same relation twice. In this case, aliases are given to the
relation name
Example
Query 3: For each employee, retrieve the employee's name, and the name of his or her
immediate supervisor.

Dept. Of ISE, DSATM 2023-2024 Page 7

DBMS Lab Manual

Q3: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE E S

WHERE E.SUPERSSN=S.SSN
In Q3, the alternate relation names E and S are called aliases or tuple variables for the
EMPLOYEE relation We can think of E and S as two different copies of EMPLOYEE; E
represents employees in role of supervisees and S represents employees in role of supervisors
Aliasing can also be used in any SQL query for convenience. Can also use the AS
keyword to specify aliases

Q3: SELECT E.FNAME, E.LNAME, S.FNAME, S.LNAME FROM EMPLOYEE AS E,

EMPLOYEE AS S WHERE E.SUPERSSN=S.SSN

UNSPECIFIED WHERE-clause
A missing WHERE-clause indicates no condition; hence, all tuples of the relations in the
FROM-clause are selected. This is equivalent to the condition WHERE TRUE Example:

Query 4: Retrieve the SSN values for all employees.

Q4: SELECT SSN FROM EMPLOYEE
If more than one relation is specified in the FROM-clause and there is no join condition, then the
CARTESIAN PRODUCT of tuples is selected
Example:
Q5: SELECT SSN, DNAME FROM EMPLOYEE, DEPARTMENT

Note: It is extremely important not to overlook specifying any selection and join conditions inthe
WHERE-clause; otherwise, incorrect and very large relations may result

USE OF *
To retrieve all the attribute values of the selected tuples, a * is used, which stands for all the
attributes
Examples:
Retrieve all the attribute values of EMPLOYEES who work in department 5.
Q1a: SELECT * FROM EMPLOYEE WHERE DNO=5
Retrieve all the attributes of an employee and attributes of DEPARTMENT he works in for
every employee of ‘Research’ department.

Dept. Of ISE, DSATM 2023-2024 Page 8

DBMS Lab Manual

Q1b: SELECT * FROM EMPLOYEE, DEPARTMENT WHERE DNAME='Research'

AND DNO=DNUMBER

USE OF DISTINCT
SQL does not treat a relation as a set; duplicate tuples can appear. To eliminate duplicate
tuples in a query result, the keyword DISTINCT is used
Example: the result of Q1c may have duplicate SALARY values whereas Q1d does not have any
duplicate values

Q1c: SELECT SALARY FROM EMPLOYEE Q1d: SELECT DISTINCT

SALARY FROM EMPLOYEE

SET OPERATIONS
SQL has directly incorporated some set operations such as union operation (UNION), set
difference (MINUS) and intersection (INTERSECT) operations. The resulting relations of these
set operations are sets of tuples; duplicate tuples are eliminated from the result. The set
operations apply only to union compatible relations; the two relations must have the same
attributes and the attributes must appear in the same order
Query 5: Make a list of all project numbers for projects that involve an employee whose
last name is 'Smith' as a worker or as a manager of the department that controls the
project.
Q5: (SELECT PNAME FROM PROJECT, DEPARTMENT, EMPLOYEE WHERE
DNUM=DNUMBER AND MGRSSN=SSN AND LNAME='Smith')
UNION

(SELECT PNAME FROM PROJECT, WORKS_ON, EMPLOYEE WHERE

PNUMBER=PNO AND ESSN=SSN AND NAME='Smith')

NESTING OF QUERIES

A complete SELECT query, called a nested query, can be specified within the WHERE-
clause of another query, called the outer query. Many of the previous queries can be specified in
an alternative form using nesting
Query 6: Retrieve the name and address of all employees who work for the 'Research'
department.

Dept. Of ISE, DSATM 2023-2024 Page 9

DBMS Lab Manual

Q6: SELECT FNAME, LNAME, ADDRESS FROM EMPLOYEE WHERE DNO IN

(SELECT DNUMBER FROM DEPARTMENT WHERE DNAME='Research' )
Note: The nested query selects the number of the 'Research' department. The outer query
selectsan EMPLOYEE tuple if its DNO value is in the result of either nested query. The
comparison operator IN compares a value v with a set (or multi-set) of values V, and evaluates to
TRUE if v is one of the elements in V

In general, we can have several levels of nested queries. A reference to an unqualified

attribute refers to the relation declared in the innermost nested query. In this example, the nested
query is not correlated with the outer query

CORRELATED NESTED QUERIES

If a condition in the WHERE-clause of a nested query references an attribute of a relation
declared in the outer query, the two queries are said to be correlated. The result of a correlated
nested query is different for each tuple (or combination of tuples) of the relation(s) the outer
query
Query 7: Retrieve the name of each employee who has a dependent with the same first
name as the employee.
Q7: SELECT E.FNAME, E.LNAME FROM EMPLOYEE AS E WHERE E.SSN IN
(SELECT ESSN FROM DEPENDENT WHERE ESSN=E.SSN AND
E.FNAME=DEPENDENT_NAME)
In Q7, the nested query has a different result in the outer query. A query written with nested
SELECT... FROM… WHERE... blocks and using the = or IN comparison operators can always
be expressed as a single block query. For example, Q7 may be written as in Q7a

Q7a: SELECT E.FNAME, E.LNAME FROM EMPLOYEE E, DEPENDENT D

WHERE E.SSN=D.ESSN AND E.FNAME=D.DEPENDENT_NAME

THE EXISTS FUNCTION

EXISTS is used to check whether the result of a correlated nested query is empty
(contains no tuples) or not. We can formulate Query 7 in an alternative form that uses EXIST.
Q7b: SELECT FNAME, LNAME FROM EMPLOYEE
WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN
AND FNAME=DEPENDENT_NAME)

Dept. Of ISE, DSATM 2023-2024 Page

10
DBMS Lab Manual
BCS403

Query 8: Retrieve the names of employees who have no dependents.

Q8: SELECT FNAME, LNAME FROM EMPLOYEE
WHERE NOT EXISTS
(SELECT * FROM DEPENDENT WHERE SSN=ESSN)

Note: In Q8, the correlated nested query retrieves all DEPENDENT tuples related to
anEMPLOYEE tuple. If none exist, the EMPLOYEE tuple is selected

EXPLICIT SETS
It is also possible to use an explicit (enumerated) set of values in the WHERE-clause
rather than a nested query
Query 9: Retrieve the social security numbers of all employees who work on project
number 1, 2, or 3.

Q9: SELECT DISTINCT ESSN FROM WORKS_ON WHERE PNO IN (1, 2, 3)

NULLS IN SQL QUERIES

SQL allows queries that check if a value is NULL (missing or undefined or not
applicable). SQL uses IS or IS NOT to compare NULLs because it considers each NULL value
distinct from other NULL values, so equality comparison is not appropriate.
Query 10: Retrieve the names of all employees who do not have supervisors.
Q10: SELECT FNAME, LNAME FROM EMPLOYEE
WHERE SUPERSSN IS NULL

Note: If a join condition is specified, tuples with NULL values for the join attributes are
notincluded in the result

AGGREGATE FUNCTIONS
Include COUNT, SUM, MAX, MIN, and AVG
Query 11: Find the maximum salary, the minimum salary, and the average salary among
all employees.
Q11: SELECT MAX (SALARY), MIN(SALARY), AVG(SALARY)
FROM EMPLOYEE
Note: Some SQL implementations may not allow more than one function in the SELECT-clause

Dept. Of ISE, DSATM 2023-2024 Page11

DBMS Lab Manual
BCS403

Query 12: Find the maximum salary, the minimum salary, and the average salary among
employees who work for the 'Research' department.
Q12: SELECT MAX (SALARY), MIN(SALARY), AVG(SALARY) FROM
EMPLOYEE, DEPARTMENT WHERE DNO=DNUMBER AND DNAME='Research'
Queries 13 and 14: Retrieve the total number of employees in the company (Q13), and the
number of employees in the 'Research' department (Q14).
Q13: SELECT COUNT (*) FROM EMPLOYEE
Q14: SELECT COUNT (*) FROM EMPLOYEE, DEPARTMENT

WHERE DNO=DNUMBER AND DNAME='Research’

GROUPING
 In many cases, we want to apply the aggregate functions to subgroups of tuples in a
relation
 Each subgroup of tuples consists of the set of tuples that have the same value for the
grouping attribute(s)
 The function is applied to each subgroup independently
 SQL has a GROUP BY-clause for specifying the grouping attributes, which must also
appear in the SELECT-clause
Query 15: For each department, retrieve the department number, the number of
employees in the department, and their average salary.
Q15: SELECT DNO, COUNT (*), AVG (SALARY)
FROM EMPLOYEE GROUP BY DNO
 In Q15, the EMPLOYEE tuples are divided into groups. Each group having the same
value for the grouping attribute DNO
 The COUNT and AVG functions are applied to each such group of tuples separately
 The SELECT-clause includes only the grouping attribute and the functions to be applied
on each group of tuples
 A join condition can be used in conjunction with grouping
Query 16: For each project, retrieve the project number, project name, and the number of
employees who work on that project.
Q16: SELECT PNUMBER, PNAME, COUNT (*)

Dept. Of ISE, DSATM 2023-2024 Page12

DBMS Lab Manual
BCS403

FROM PROJECT, WORKS_ON

WHERE PNUMBER=PNO

GROUP BY PNUMBER, PNAME

THE HAVING-CLAUSE
Sometimes we want to retrieve the values of these functions for only those groups that
satisfy certain conditions. The HAVING-clause is used for specifying a selection condition on
groups (rather than on individual tuples)
Query 17: For each project on which more than two employees work, retrieve the project
number, project name, and the number of employees who work on that project.
Q17: SELECT PNUMBER, PNAME, COUNT
(*) FROM PROJECT, WORKS_ON WHERE
PNUMBER=PNO GROUP BY PNUMBER,
PNAME

HAVING COUNT (*) > 2

SUBSTRING COMPARISON
The LIKE comparison operator is used to compare partial strings. Two reserved
characters are used: '%' (or '*' in some implementations) replaces an arbitrary number of
characters, and '_' replaces a single arbitrary character.
Query 18: Retrieve all employees whose address is in Houston, Texas. Here, the value of the
ADDRESS attribute must contain the substring 'Houston,TX‘ in it.
Q18: SELECT FNAME, LNAME
FROM EMPLOYEE WHERE ADDRESS LIKE '%Houston,TX%'
Query 19: Retrieve all employees who were born during the 1950s.
Here, '5' must be the 8th character of the string (according to our format for date), so the
BDATE value is ' 5_', with each underscore as a place holder for a single arbitrary
character.
Q19: SELECT FNAME, LNAME
FROM EMPLOYEE WHERE BDATE LIKE '_ 5_’
Note: The LIKE operator allows us to get around the fact that each value is considered atomic
and indivisible. Hence, in SQL, character string attribute values are not atomic

Dept. Of ISE, DSATM 2023-2024 Page13

DBMS Lab Manual
BCS403

ARITHMETIC OPERATIONS

The standard arithmetic operators '+', '-'. '*', and '/' (for addition, subtraction,
multiplication, and division, respectively) can be applied to numeric values in an SQL query
result
Query 20: Show the effect of giving all employees who work on the 'ProductX' project a
10% raise.
Q20: SELECT FNAME, LNAME, 1.1*SALARY
FROM EMPLOYEE, WORKS_ON, PROJECT
WHERE SSN=ESSN
AND PNO=PNUMBER AND PNAME='ProductX’

ORDER BY

The ORDER BY clause is used to sort the tuples in a query result based on the values of
some attribute(s)
Query 21: Retrieve a list of employees and the projects each works in, ordered by the
employee's department, and within each department ordered alphabetically by employee
last name.
Q21: SELECT DNAME, LNAME, FNAME, PNAME
FROM DEPARTMENT, EMPLOYEE, WORKS_ON, PROJECT
WHERE DNUMBER=DNO
AND SSN=ESSN
AND PNO=PNUMBER
ORDER BY DNAME, LNAME
The default order is in ascending order of values. We can specify the keyword DESC if
we want a descending order; the keyword ASC can be used to explicitly specify ascending order,
even though it is the default
Ex: ORDER BY DNAME DESC, LNAME ASC, FNAME ASC

MORE EXAMPLE QUERIES:

Query 22: Retrieve the names of all employees who have two or more dependents.
Q22: SELECT LNAME, FNAME FROM
EMPLOYEE

Dept. Of ISE, DSATM 2023-2024 Page14

DBMS Lab Manual
BCS403

WHERE (SELECT COUNT (*) FROM DEPENDENT

WHERE SSN=ESSN) ≥ 2);
Query 23: List the names of managers who have least one dependent.
Q23: SELECT FNAME, LNAME
FROM EMPLOYEE
WHERE EXISTS (SELECT * FROM DEPENDENT WHERE SSN=ESSN)

AND EXISTS ( SELECT * FROM DEPARTMENT WHERE SSN=MGRSSN );

SPECIFYING UPDATES IN SQL

There are three SQL commands to modify the database: INSERT, DELETE, and UPDATE.

INSERT

 In its simplest form, it is used to add one or more tuples to a relation

 Attribute values should be listed in the same order as the attributes were specified in the
CREATE TABLE command
Example:
INSERT INTO EMPLOYEE VALUES ('Richard','K','Marini', '653298653', '30-DEC-52',
'98 Oak Forest,Katy,TX', 'M', 37000,'987654321', 4 )
 An alternate form of INSERT specifies explicitly the attribute names that correspond to
the values in the new tuple. Attributes with NULL values can be left out
Example: Insert a tuple for a new EMPLOYEE for whom we only know the FNAME,
LNAME,and SSN attributes.
INSERT INTO EMPLOYEE (FNAME, LNAME, SSN)VALUES ('Richard', 'Marini',
'653298653')
Important Note: Only the constraints specified in the DDL commands are
automaticallyenforced by the DBMS when updates are applied to the database. Another variation
of INSERT allows insertion of multiple tuples resulting from a query into a relation
Example: Suppose we want to create a temporary table that has the name, number of
employees,and total salaries for each department. A table DEPTS_INFO is created first, and is
loaded with the summary information retrieved from the database by the query.
CREATE TABLE DEPTS_INFO
(DEPT_NAME VARCHAR (10),

Dept. Of ISE, DSATM 2023-2024 Page15

DBMS Lab Manual
BCS403

NO_OF_EMPS INTEGER, TOTAL_SAL INTEGER);

INSERT INTO DEPTS_INFO (DEPT_NAME, NO_OF_EMPS, TOTAL_SAL)

SELECT DNAME, COUNT (*), SUM (SALARY) FROM DEPARTMENT,
EMPLOYEE WHERE DNUMBER=DNO GROUP BY DNAME ;

Note: The DEPTS_INFO table may not be up-to-date if we change the tuples in either
theDEPARTMENT or the EMPLOYEE relations after issuing the above. We have to create a
view (see later) to keep such a table up to date.

DELETE
 Removes tuples from a relation. Includes a WHERE-clause to select the tuples to be
deleted
 Referential integrity should be enforced
 Tuples are deleted from only one table at a time (unless CASCADE is specified on a
referential integrity constraint)
 A missing WHERE-clause specifies that all tuples in the relation are to be deleted; the
table then becomes an empty table
 The number of tuples deleted depends on the number of tuples in the relation that satisfy
the WHERE-clause
Examples:
1: DELETE FROM EMPLOYEE WHERE LNAME='Brown’;
2: DELETE FROM EMPLOYEE WHERE SSN='123456789’;
3: DELETE FROM EMPLOYEE WHERE DNO IN (SELECT DNUMBER
FROM DEPARTMENT WHERE DNAME='Research');

4: DELETE FROM EMPLOYEE;

UPDATE
 Used to modify attribute values of one or more selected tuples
 A WHERE-clause selects the tuples to be modified
 An additional SET-clause specifies the attributes to be modified and their new values
 Each command modifies tuples in the same relation

 Referential integrity should be enforced

Dept. Of ISE, DSATM 2023-2024 Page16

DBMS Lab Manual BCS403

Example1: Change the location and controlling department number of project number 10
to'Bellaire' and 5, respectively.
UPDATE PROJECT
SET PLOCATION = 'Bellaire', DNUM = 5 WHERE PNUMBER=10;
Example2: Give all employees in the 'Research' department a 10% raise in salary.

UPDATE EMPLOYEE
SET SALARY = SALARY *1.1
WHERE DNO IN (SELECT DNUMBER FROM DEPARTMENT

WHERE DNAME='Research');

SQL TRIGGERS
 Objective: to monitor a database and take initiate action when a condition occurs
 Triggers are nothing but the procedures/functions that involve actions and fired/executed
automatically whenever an event occurs such as an insert, delete, or update operation or
pressing a button or when mouse button is clicked
VIEWS IN SQL
 A view is a single virtual table that is derived from other tables. The other tables could be
base tables or previously defined view.
 Allows for limited update operations Since the table may not physically be stored
 Allows full query operations
 A convenience for expressing certain operations
 A view does not necessarily exist in physical form, which limits the possible update
operations that can be applied to views.

Dept. Of ISE, DSATM 2023-2024 Page 17

 DBMS Lab Manual BCS403

LAB EXPERIMENTS

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 employee table.
4. Insert null values to the employee table and verify the result.

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

CREATE USER myuser IDENTIFIED BY mypassword;

GRANT ALL PRIVILEGES ON Employee TO myuser;

2. Create the Employee table

CREATE TABLE Employee (

EMPNO INT,
ENAME VARCHAR(50),
JOB VARCHAR(50),
MANAGER_NO INT,
SAL DECIMAL(10, 2),
COMMISSION DECIMAL(10, 2)
);

3. Insert three records into the employee table and rollback

INSERT INTO Employee (EMPNO, ENAME, JOB, MANAGER_NO, SAL, COMMISSION)
VALUES
(1, 'John Doe', 'Manager', NULL, 5000.00, 1000.00),
(2, 'Jane Smith', 'Developer', 1, 4000.00, 500.00),
(3, 'Bob Johnson', 'Analyst', 1, 3500.00, NULL);

ROLLBACK;

Check if the records were inserted

SELECT * FROM Employee;

Dept. Of ISE, DSATM 2023-2024 Page 18

 DBMS Lab Manual BCS403

4.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 (EMPNO INT NOT NULL,
ENAME VARCHAR(50) NOT NULL,
JOB VARCHAR(50) NOT NULL,
SAL DECIMAL(10, 2) NOT NULL);

5. Insert null values into the employee table and verify the result

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

VALUES (4, NULL, 'Intern', NULL, NULL, NULL);

SELECT * FROM Employee;

OUTPUT:

EMPNO ENAME JOB MANAGER_NO SAL COMMISSION

1 John Doe Manager NULL 5000 1000

2 Jane Smith Developer 1 4000 500
3 Bob Analyst 1 3500 NULL
Johnson
4 NULL Intern NULL NULL NULL

Dept. Of ISE, DSATM 2023-2024 Page 19

 DBMS Lab Manual BCS403

2. Create a table called Employee that contain attributes EMPNO, ENAME, JOB, MGR, SAL
and execute the following.
1. Add a column commission with domain to the Employee table.
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.

1. Create the Employee table with attributes EMPNO, ENAME, JOB, MGR, SAL

CREATE TABLE Employee (

EMPNO INT,
ENAME VARCHAR(50),
JOB VARCHAR(50),
MGR INT,
SAL DECIMAL(10, 2)
);

2. Add a column 'commission' to the Employee table

ALTER TABLE Employee

ADD commission DECIMAL (10, 2);

3. Insert five records into the table

INSERT INTO Employee (EMPNO, ENAME, JOB, MGR, SAL, commission)

VALUES
(101, 'John Doe', 'Manager', NULL, 5000.00, 1000.00),
(102, 'Jane Smith', 'Developer', 101, 4000.00, 500.00),
(103, 'Bob Johnson', 'Analyst', 101, 3500.00, NULL),
(104, 'Alice Jones', 'Designer', 101, 3800.00, 800.00),
(105, 'Michael Brown', 'Engineer', 101, 4200.00, 700.00);

4. Update the column details of 'JOB'

ALTER TABLE Employee

MODIFY (JOB VARCHAR(50) NOT NULL);

5. Rename the column 'MGR' to 'MANAGER' in the Employee table

ALTER TABLE Employee

RENAME COLUMN MGR TO MANAGER;

6.Delete the employee whose Empno is 105

Dept. Of ISE, DSATM 2023-2024 Page 20

 DBMS Lab Manual BCS403
DELETE FROM Employee WHERE EMPNO = 105;

Select*from Employee;

OUTPUT:

EMPNO ENAME JOB MANAGER SAL commission

101 John Doe Manager NULL 5000 1000

102 Jane Smith Developer 101 4000 500

103 Bob Johnson Analyst 101 3500 NULL

104 Alice Jones Designer 101 3800 800

Dept. Of ISE, DSATM 2023-2024 Page 21

 DBMS Lab Manual BCS403

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.

1. Create the Employee table

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

2. Insert records into the Employee table

INSERT INTO Employee (E_id, E_name, Age, Salary)VALUES

(1, 'John Doe', 30, 50000.00),
(2, 'Jane Smith', 25, 45000.00),
(3, 'Bob Johnson', 35, 60000.00),
(4, 'Alice Jones', 28, 52000.00),
(5, 'Michael Brown', 32, 55000.00);

Select * from Employee;

3.Count number of employee names from employeetable

SELECT COUNT(E_name) AS num_employees FROM Employee;

4. Find the Maximum age from employee table

SELECT MAX(Age) AS max_age FROM Employee;

35

5. Find the Minimum age from employee table

SELECT MIN(Age) AS min_age FROM Employee;

Dept. Of ISE, DSATM 2023-2024 Page 22

 DBMS Lab Manual BCS403
25

6. Find salaries of employee in Ascending Order

SELECT E_name, Salary FROM Employee ORDER BY Salary ASC;

Jane Smith 45000.00

John Doe 50000.00
Alice Jones 52000.00
Michael Brown 55000.00
Bob Johnson 60000.00

7. Find grouped salaries of employees

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

50000.00 1
45000.00 1
60000.00 1
52000.00 1
55000.00 1

Dept. Of ISE, DSATM 2023-2024 Page 23

 DBMS Lab Manual BCS403

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 the customers table

CREATE TABLE customers (

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

-- Create a sequence for trigger

CREATE SEQUENCE salary_diff_seq;

-- Create the trigger

CREATE OR REPLACE TRIGGER salary_diff_trigger
AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW
DECLARE
old_salary DECIMAL(10, 2);
new_salary DECIMAL(10, 2);
salary_diff DECIMAL(10, 2);
BEGIN
-- Get the old and new salary values
IF INSERTING OR UPDATING THEN
old_salary := NVL(:OLD.SALARY, 0);
new_salary := NVL(:NEW.SALARY, 0);
END IF;

IF DELETING THEN
old_salary := NVL(:OLD.SALARY, 0);
new_salary := 0;
END IF;

-- Calculate the salary difference

salary_diff := new_salary - old_salary;

-- Display the salary difference

DBMS_OUTPUT.PUT_LINE('Salary difference for ID ' || :OLD.ID || ': ' || salary_diff);

-- Increment sequence
SELECT salary_diff_seq.NEXTVAL INTO NULL FROM DUAL;
Dept. Of ISE, DSATM 2023-2024 Page 24
 DBMS Lab Manual BCS403
END;
/

5.Create cursor for Employee table & extract the values from the table. Declare the variables
,Open the cursor &amp; extract the values from the cursor. Close the cursor.
Employee(E_id, E_name, Age, Salary).

DECLARE
-- Declare variables to hold values 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;

-- Declare cursor for the Employee table

CURSOR emp_cursor IS
SELECT E_id, E_name, Age, Salary
FROM Employee;
BEGIN
-- Open the cursor
OPEN emp_cursor;

-- Fetch and process each row from the cursor

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

-- Process the values, for example, you can print them

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;
/

Dept. Of ISE, DSATM 2023-2024 Page 25

 DBMS Lab Manual BCS403

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 database sql6;

use sql6;

create table o_rollcall(roll_no int,name varchar(20),address varchar(20));

create table n_rollcall(roll_no int,name varchar(20),addressvarchar(20));
insert into o_rollcall values('1','Hitesh','Nandura');
insert into o_rollcall values('2','Piyush','MP');
insert into o_rollcall values('3','Ashley','Nsk');
insert into o_rollcall values('4','Kalpesh','Dhule');
insert into o_rollcall values('5','Abhi','Satara');

delimiter //
create procedure p3(in r1 int)
begin
declare r2 int;
declare exit_loop boolean;
declare c1 cursor for select roll_no from o_rollcall
where roll_no>r1;
declare continue handler for not found set
exit_loop=true;
open c1;
e_loop:loop
fetch c1 into r2;
if not exists(select * from n_rollcall where
roll_no=r2)
then
insert into n_rollcall select * from o_rollcall where
roll_no=r2;
end if;
if exit_loop
then
close c1;
leave e_loop;
end if;
end loop e_loop;
end
//

call p3(3);
select * from n_rollcall;
call p3(0);
select * from n_rollcall;
Dept. Of ISE, DSATM 2023-2024 Page 26
 DBMS Lab Manual BCS403
insert into o_rollcall values('6','Patil','Kolhapur');
call p3(4);

select * from n_rollcall;

OUTPUT:

Roll
Name Address
no
4 Kalpesh Dhule
5 Abhi Satara
1 Hitesh Nandura
1 Hitesh Nandura
2 Piyush MP
3 Ashley Nsk
6 Patil Kolhapur

Dept. Of ISE, DSATM 2023-2024 Page 27

 DBMS Lab Manual BCS403

7. Install an Open Source NoSQL Data base MangoDB & perform basic CRUD(Create, Read, Update & Delete) operations.
Execute MangoDB basic Queries using CRUD operations.
Installation:
1. Download the MongoDB Installer:
 Visit the official MongoDB download center:
 URL: https://www.mongodb.com/try/download/community
 Select the appropriate version (latest stable is recommended for most cases).
 Under "Platform," choose "Windows."
 For "Package," select "MSI."
 Click "Download."

2. Run the Installer:

 Locate the downloaded .msi file (usually in your Downloads folder).
 Double-click the file to launch the MongoDB Setup Wizard.

3. Follow the Installation Wizard:

 Click "Next" on the initial screen.
 Accept the license agreement and click "Next."
 Choose the installation type:
o Complete: Installs all MongoDB components (recommended for most users).
o Custom: Allows you to select specific features (advanced users).
 Click "Next."

4. Configure Service Options (Optional):

 In the "Service Configuration" step (available in newer versions), you can choose how MongoDB runs as a service:
o Run service as Network Service user: This is the default and recommended option.
o Run service as a custom user account: If you have specific security requirements, you can create a dedicated
service account.
 Click "Next."

5. Review and Install:

 Review the installation summary.
 Click "Install" to begin the installation process.

6. Verify Installation

Basic CRUD(Create, Read, Update & Delete) operations

Now that you’ve installed MongoDB Compass on your local machine, you can connect it to the MongoDB instance running on
your remote server.
Create new database “mflix”
Create a collection “movies”
Create Operation
Insert data into the collection.

Dept. Of ISE, DSATM 2023-2024 Page 28

 DBMS Lab Manual BCS403
Click on “Add Data”. There will be two options:
1. Import JSON/CSV file
2. Insert Document

Click on “Insert Document” and enter the record details:

Read Operation
Click on a specific collection to view its contents.
To filter the contents, enter the query in the filter box.

Update Operation
Select the desired collection and click on Update button to modify the values of a record. Set new values for the desired fields.

Dept. Of ISE, DSATM 2023-2024 Page 29

 DBMS Lab Manual BCS403

Delete Operation
Click on the Delete button to delete records of a specific collection.

To delete a specific record, filter the record and then delete.

Dept. Of ISE, DSATM 2023-2024 Page 30

 DBMS Lab Manual BCS403

Viva Questions

1. What is SQL?
Structured Query Language
2. What isdatabase?
A database is a logically coherent collection of data with some inherent meaning,
representing some aspect of real world and which is designed, built and populated with data
for a specificpurpose.
3. What isDBMS?
It is a collection of programs that enables user to create and maintain a database. In other
words it is general-purpose software that provides the users with the processes of defining,
constructing and manipulating the database for various applications.
4. What is a Databasesystem?
The database and DBMS software together is called as Database system.
5. Advantages ofDBMS?
 Redundancy iscontrolled.
 Unauthorized access isrestricted.
 Providing multiple userinterfaces.
 Enforcing integrityconstraints.
 Providing backup andrecovery.
6. Disadvantage in File ProcessingSystem?
 Data redundancy &inconsistency.
 Difficult in accessingdata.
 Dataisolation.
 Dataintegrity.
 Concurrent access is notpossible.
 SecurityProblems.
7. Describe the three levels of dataabstraction?
There are three levels of abstraction:
 Physical level: The lowest level of abstraction describes how data arestored.
 Logical level: The next higher level of abstraction, describes what data are stored in
database and what relationship among those data.

Dept. Of ISE, DSATM 2023-2024 Page 31

DBMS Lab Manual BCS403
 View level:The highest level of abstraction describes only part of entiredatabase.
8. Define the "integrityrules"
There are two Integrity rules.
 Entity Integrity:States that “Primary key cannot have NULLvalue”
 Referential Integrity:States that “Foreign Key can be either a NULL value or
should be Primary Key value of otherrelation.
9. What is extension andintension?
Extension - It is the number of tuples present in a table at any instance. This is time
dependent.
Intension -It is a constant value that gives the name, structure of table and the constraints laid
on it.
10. What is DataIndependence?
Data independence means that “the application is independent of the storage structure
and access strategy of data”. In other words, The ability to modify the schema definition in one
level should not affect the schema definition in the next higher level.
Two types of Data Independence:
 Physical Data Independence: Modification in physical level should not affect the
logicallevel.
 Logical Data Independence: Modification in logical level should affect the view
level.
NOTE: Logical Data Independence is more difficult to achieve
11. What is a view? How it is related to dataindependence?
A view may be thought of as a virtual table, that is, a table that does not really exist in its
own right but is instead derived from one or more underlying base table. In other words, there is
no stored file that direct represents the view instead a definition of view is stored in data
dictionary.
Growth and restructuring of base tables is not reflected in views. Thus the view can
insulate users from the effects of restructuring and growth in the database. Hence accounts for
logical data independence.
12. What is DataModel?
A collection of conceptual tools for describing data, data relationships data semantics and
constraints.
13. What is E-Rmodel?

Dept. Of ISE, DSATM 2023-2024 Page 32

DBMS Lab Manual BCS403
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.
14. What is Object Orientedmodel?
This model is based on collection of objects. An object contains values stored in instance
variables within the object. An object also contains bodies of code that operate on the object.
These bodies of code are called methods. Objects that contain same types of values and the same
methods are grouped together into classes.
15. What is anEntity?
It is an 'object' in the real world with an independent existence.
16. What is an Entitytype?
It is a collection (set) of entities that have same attributes.
17. What is an Entityset?
It is a collection of all entities of particular entity type in the database.
18. What is an Extension of entitytype?
The collections of entities of a particular entity type are grouped together into an entity
set.
19. What is anattribute?
It is a particular property, which describes the entity.
20. What is a Relation Schema and aRelation?
A relation Schema denoted by R(A1, A2, …, An) is made up oftherelation name
R and the list of attributes Ai that it contains. A relation isdefinedas a set of tuples.Letr
be the relation which contains set tuples (t1,t2,t3, ...,tn). Each tuple is an ordered list of n-
values t=(v1,v2, ...,vn).
21. What is degree of aRelation?
It is the number of attribute of its relation schema.
22. What isRelationship?
It is an association among two or more entities.
23. What is Relationshipset?
It is an applied predicate calculus specifically tailored for relational databases proposed
by E.F. Codd. E.g. of languages based on it are DSL, ALPHA,QUEL.
24. What isnormalization?
It is a process of analyzing the given relation schemas basedontheir
Functio
Dept. Of ISE, DSATM 2023-2024 Page 33
DBMS Lab Manual BCS403
nal Dependencies (FDs) and primary key to achieve theproperties
 Minimizingredundancy
 Minimizing insertion, deletion and updateanomalies.
25. What is FunctionalDependency?
A Functional dependency is denoted byX Y between two sets of attributes X
andYthat are subsets of R specifies a constraint on the possible tuple that can form a relation
state r of
R. The constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] =
t2[Y]. This means the value of X component of a tuple uniquely determines the value of
componentY.
26. When is a functional dependency F said to beminimal?
 Every dependency in F has a single attribute for its right handside.
 We cannot replace any dependencyX A in F with a dependencyY A where
Yisa proper subset of X and still have a set of dependency that is equivalent toF.
 We cannot remove any dependency from F and still have set of dependency that is
equivalent toF.
27. What is Multivalueddependency?
Multivalued dependency denoted by X Y specified on relation schema R, where X
and Y are both subsets of R, specifies the following constraint on any relation r of R: if two
tuples t1 and t2 exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the
followingproperties
 t3[x] = t4[X] = t1[X] = t2[X]
 t3[Y] = t1[Y] and t4[Y] = t2[Y]
 t3[Z] = t2[Z] and t4[Z] = t1[Z]
where [Z = (R-(X U Y)) ]
28. What is Lossless joinproperty?
It guarantees that the spurious tuple generation does not occur with respect to relation
schemas after decomposition.

29. What is 1 NF (NormalForm)?

The domain of attribute must include only atomic (simple, indivisible) values.
30. What is Fully Functionaldependency?
It is based on concept of full functional dependency. A functional dependency X Y is

Dept. Of ISE, DSATM 2023-2024 Page 34

DBMS Lab Manual BCS403

fully functional dependency if removal of any attribute A from X means that the dependency
does not hold anymore.
31. What is2NF?
A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully
functionally dependent on primary key.
32. What is3NF?
A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the
following istrue
 X is a Super-key ofR.
 A is a prime attribute ofR.
In other words, if every non prime attribute is non-transitively dependent on primary key.
33. What is BCNF (Boyce-Codd NormalForm)?
A relation schema R is in BCNF if it is in 3NF and satisfies additional constraints that for
every FD X A, X must be a candidate key.
34. What is4NF?
A relation schema R is said to be in 4NF if for every Multivalued dependency X Y
that holds over R, one of following istrue
 X is subset or equal to (or) XY =R.
 X is a superkey.
35. What is5NF?
A Relation schema R is said to be 5NF if for every join dependency {R1, R2, ...,Rn} that
holds R, one the following is true
 Ri = R for somei.
 The join dependency is implied by the set of FD, over R in which the left side is key ofR.
36. What is Domain-Key NormalForm?
A relation is said to be in DKNF if all constraints and dependencies that should hold on the
constraint can be enforced by simply enforcing the domain constraint and key constraint on the
relation.

Dept. Of ISE, DSATM 2023-2024 Page 35