L.

J Institute of Engineering and Technology

Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Which is the false statement ? A Database A Database DBMS Data helps in making
is ordered is systematic manages the decisions.
1 1 D 1 collection of
compilation database
data. of records in
a computer
In traditional file system ,Two files may be combined into a third file if They have a They have a They have no Both (b) and (c)
row in field in records with
2 1 B 1 common common the same value
in the
common field
3 1 Data Model is collection of conceptual tools for describing - D 1 Data Data schema Consistency All of these
____ Data refers to describing the data description, representing data semantics, and describing the consistency constraints that apply to the data. Adding Modifying Modeling Refining
4 1 C 1
Which of the following is not an example of DBMS? MySQL Microsoft IBM DB2 Google
5 1 D 1
Acess
Which of the following is a feature of DBMS? Minimum High Level Mulitple-user All of these
Duplication of Security Access
6 1 D 1 and
Redundancy
of Data
Which of the following is not a function of the database? Managing Security for Analysing code
7 1 D 1 stored data Manipulating stored data
data
8 1 What is information about data called? C 1 Hyper data Tera data Meta data Relations
What is a database? Organized Collection Organized Organized collection
collection of of data or collection of of data that cannot be
information information data or updated
that cannot without information
9 1 C 1 be accessed, organizing that can be
updated, and accessed,
managed updated, and
managed
The same information may be duplicated in several places (files). For example, if a student has a double major (say, TRUE FALSE
music and mathematics) the address and telephone number of that student may appear in a file that consists of student records of students in the
Music
10 1 A 1
department and in a file that consists of student records of students in the Mathematics department. This redundancy leads to higher storage and
access
cost. In addition, it may lead to data inconsistency.
Implementation of the simple structures at the logical level may involve complex physical-level structures, the physical data Logical Data Local Data None of these
11 user of the logical level does not need to be aware of this complexity. This A 1 independence Independenc Independence
is referred to as ________________________. . e
Which is the type of Data Independence? Physical Logical Data Both A & B None of these
Data Independenc
12 1 C 1
Independenc e
e
__________, which is responsible for fetching data from disk storage into Buffer Authorizatio Transaction None of these
main memory, and deciding what data to cache in main memory. The buffer manager n and manager
13 1 A 1
manager is a critical part of the database system, since it enables the database integrity
to handle data sizes that are much larger than the size of main memory manager
Authorization and integrity manager, which tests for the satisfaction of TRUE FALSE
14 1 A 1
integrity constraints and checks the authority of users to access data.
____________ means to hide certain details of how data is stored and maintain. Data Data Data None of these
15 1 C 1
Isolation Integrity Abstraction
_______ of abstraction explains how data is actually stored and describes the Data Structure and Access methods used by database. Conceptual Physical View Level None of these
16 1 B 1
Level Level
17 1 Procedural DMLs require a user to specify what data are needed and how to get those data. A 1 TRUE FALSE
Declarative DMLs (also referred to as nonprocedural DMLs) require a user to specify what data are needed without specifying how to get those TRUE FALSE
18 1 A 1
data.
Data Manipulation Languages are used for - Insert update Delete All of these
Information Information Information
19 1 D 1
into Database

The Statement that requests a retrieval of information from database is called as ________. Control Query Manipulation None of these
20 1 B 1
Statement Statement
The Users who use "easy to use menu" are called _______________________________ Sophisticated Naïve user Stand alone Casual end users
21 1 B 1
end user users
Schema Definition is written by __________. Database Application Naïve user Casual end users
22 1 A 1 Administrato Programmer
r
User who interacts with the system using database query language is called as ___________. Sophisticated Naïve user Stand alone Casual end users
23 1 A 1
end user users
Farmer goes to ATM Center to withdraw an amount of Rs.300/-. Which type of user farmer is ? Sophisticated Unophisticat Application Specialized User
24 1 B 1
end user ed end user Programmer
25 2 The number of tuples in a relation is known as D 1 degree relation attribute cardinality
Which of the following aspects of data is the concern of a relationaldatabase model? data data integrity data structure all of these
26 2 B 1
manipulation
27 2 A relational database consists of a collection of A 1 Tables Fields Records Keys
Which of the following is used to denote the selection operation in relational algebra? Pi (Greek) Sigma Lambda Omega (Greek)
28 2 B 1
(Greek) (Greek)
For select operation the ________ appear in the subscript and the ___________ argument appears in the paranthesis after the sigma. Predicates, Relation, Operation, Relation, Operation
29 2 A 1
relation Predicates Predicates
The ___________ operation, denoted by −, allows us to find tuples that are in one relation but are not in another. Union Set- Difference Intersection
30 2 B 1
difference
31 2 In domain relaional calculus “there exist” can be expressed as D 1 (P1(x)) (P1(x)) Э x V x (P1(x)) Э x (P1(x))
An expression in the domain relational calculus is of the form {P(x1, x2, . . {x1, x2, . . . , { x1, x2, . . . , {< x1, x2, . . . , xn > |
. , xn) | < x1, xn | < x1, x2, xn | x1, x2, . . . P(x1, x2, . . . , xn)}
32 2 D 1
x2, . . . , xn > . . . , xn > } , xn}
}
33 2 Which of the following is the comparison operator in tuple relational calculus B 1 ⇒ = ε All of the mentioned
34 2 “Find all students who have taken all courses offered in the Biology department.” The expressions that matches this sentence is : B 1 Э t ε r (Q(t)) ∀ t ε r (Q(t)) ¬ t ε r (Q(t)) ~ t ε r (Q(t))
35 2 A Set of Possible data value is called ______________________. D 1 Attribute Degree Tuple Domain

Page 1 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Consider the relational database given below. Give an expression in the relational algebra to express each of the following statements:
Customer(Cust_name, Cust_street, Cust_city)
Branch(Branch_name, Branch_city, Assets)
Account (Branch_name, Account_number, Balance)
Loan(Branch_name, Loan_number, Amount)
Depositor(Cust_name, Account_number)
Borrower(Cust_name, Loan_number)
36 2 Player relation (Player Id, Team Id, Country, Age, Runs, Wickets) 5

1.Find the names of all the customers who have taken a loan from the bank and also have an account at the bank.
2. Find all tuples from player relation for which country is India.
3.Select all the tuples for which runs are greater than or equal to 15000.
4.Select all the players whose runs are greater than or equal to 6000 and age is less than 25.
5.List all the countries in Player relation.
Consider the relational database given below. Give an expression in the relational algebra to express each of the following queries:
Employee (person-name, street, city) , Works (person-name, company-name, salary)
Company (company-name, city) , Manages (person-name, manager-name)
37 2 2
1) Find the names of all employees in this database who live in the same city as the
company for which they work.
2) Find the names, street address, and cities of residence of all employees who work
for HCL and earn more than $10,000 per annum.
Consider the following relational database, where the primary keys are underlined. Give an expression in the relational algebra to express each
of the following queries:

employee (ssn, name, dno, salary, hobby, gender)

department (dno, dname, budget, location, mgrssn)
works_on (ssn, pno)
38 2 project (pno, pname, budget, location, goal) 4

1. List all pairs of employee names and the project numbers they work on.
2. List out department number, department name and department budget.
3. List all projects that Raj Yadav works on by project name.
4. List the names of employees who supervise themselves.
Solve the queries for the following database using Relational Algebra.

branch (branch-name, branch-city, assets)

customer (customer-name, customer-street, customer-only)
account (account-number, branch-name, balance)
loan (loan-number, branch-name, amount)
depositor (customer-name, account-number)
borrower (customer-name, loan-number)
39 2 7
1)Find all loans of over $1200
2)Find the loan number for each loan of an amount greater than $1200
3)Find the names of all customers who have a loan, an account, or both, from the bank
4)Find the names of all customers who have a loan and an account at bank.
5)Find the names of all customers who have a loan at the Perryridge branch.
6)Find the names of all customers who have a loan at the Perryridge branch but do not have an account at any branch of the bank.
7)Find the names of all customers who have a loan & an account at the Perryridge branch.
The relational database schema is given below.
employee (person-name, street, city)
works (person-name, company-name, salary)
company (company-name, city)
manages (person-name, manager-name)

40 2 Write the relational algebra expressions for the given queries. 4

1.Find the names of all employees who work for First Bank Corporation.
2.Find the names and cities of residence of all employees who work for First Bank Corporation.
3.. Find the names, street address, and cities of residence of all employees who work for First Bank Corporation and earn more than $10,000 per
annum.
4. Find the names of all employees in this database who do not work for First Bank Corporation.
Write Relational algebra statement for following.

Student( Enrno, name, courseId, emailId, cellno)

Course(courseId, course_nm, duration)
41 3
1) Find out list of students who have enrolled in “computer” course.
2) List name of all courses with their duration.
3) List emailId and cellno of all mechanical engineering students.
Let R1 (A, B, C) and R2 (D, E) be two relation schema, where the primary keys are shown underlined, and let C be a foreign key in R1 referring
42 2 to R2. Suppose there is no violation of the above referential integrity constraint in the corresponding relation instances r1 and r2. write relational 1
algebra expressions that would necessarily produce an empty relation.
Write Tuple and Domain Relational Calculus query for below mentioned statements:

1. “Find all tuples T field such that T is a tuple in the FACULTY relation and the value of DeptId field is ’CS’. Return a tuple with a single field
43 2 3
name which is equivalent to the name field of one such T tuple”.
2.Find all students who have taken all the courses required by ’CSCI4380’.
3.Find all students who have never taken a course from ’Prof. Amit. Return the name of the student.
Consider following schema and represent given statements in Tuple and Domain Relational Calculus form.

Branch(branch_name,branch_city)
Account(branch_name, acc_no, balance)
44 2 Depositor(Customer_name, acc_no) 3

( i ) Find out list of customer who have account at ‘abc’ branch.

( ii ) Find out all customer who have account in ‘Ahmedabad’ city and balance is greater than 10,000.
(iii) find out list of all branch name with their maximum balance.
An Entity is a ___________________ Relationship Relational Object having Object in the real
Model Model same values world distinguishable
45 3 D 1
from all other objects

Page 2 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Which of the following is incorrect about an ER Model. An attribute An attribute In a row of a In a row of a
of an Entity of an entity relational relational table an
can have can be table an attribute can have
46 3 C 1 more than composite. attribute can exactly one value or
one value. have more NULL.
than one
value.
ER Model stands for? Entity Entity Entity None of these
47 3 C 1 Recursive Recursion Relationship
Model Method Model
48 3 An Entity is represented by? B 1 Square Rectangle Ellipse Circle
Double diamond symbol is used for? Entity Attribute Weak entity Tuple
49 3 C 1 relationship relationship
type type
50 3 Dotted oval is used for Composite type attribute, true or false? A 1 FALSE TRUE
51 3 Dotted oval is used for derived type attribute, true or false? A 1 TRUE FALSE
52 3 Draw the E-R diagram of Library Management System. 5
Construct E-R diagram of the bank Management System. It provides different kinds of bank accounts and loans. It operates number of branches.
53 3 5
54 3 Draw E–R Diagram for the College Management System. 5
55 3 Draw E-R diagram for Hospital management system 5
56 3 Reduce E-R diagram of Hospital management system into ER Database Schema 7
Consider the Following database & draw ER diagram and convert entities and relstionships to relation table for given scenario
SCHOOL DATABASE STUDENT(ROLLNO,NAME,PHONE,GENDER)
57 3 CLASS(ROLLNO,GRNO) SUBJECT(SUB_NAME,SUB_CODE) 7
MARKS(ROLLNO,SUB_CODE_TEST1,TEST2,FINALMARK)

Construct an E-R diagram for a car insurance company whose customers own one or more cars each. Each car has associated with it zero to any
58 3 number of recorded accidents. Each insurance policy covers one or more cars, and has one or more premium payments associated with it. Each 5
payment is for a particular period of time and has an associated due date and the date when the payment was received
The function that an entity plays in a relationship is called that entity’s _____________ Participation Position Role Instance
59 3 C 1
Address is which kind of the attribute ? Composite Single Simple Derived
60 3 A 1
Following attributes belong to which entity Email-ID Net Banking Netflix Paytm
61 3 1.) AccountType 2.)Account_ExpirationDate 3.) No. of users 4.) Video Quality C 1

The similarities between the entity set can be expressed by which of the following features? Specializatio Generalizatio Uniquation Inheritance
62 3 B 1
n n
63 3 Higher level entity sets are designated by the term _________ B 1 Sub class Super class Parent class Root class
________ is an abstraction through which relationships are treated as higher level entities Creation Superseding Attribute Aggregation
64 3 D 1
separation
________ splits an entity to form multiple new entities that inherit some feature of the splitting entity Generalizatio Specializatio both A & B None of the Above
65 3 B 1
n n
66 3 Which relationship is used to represent a specialization entity? A 1 ISA AIS ONIS WHOIS
In E-R diagram generalization is represented by Ellipse Dashed Rectangle Triangle
67 3 D 1
ellipse
The total participation by entities is represented in E-R diagram as Dashed line Double line Double Circle
68 3 B 1
rectangle
Given the basic ER and relational models, which of the following is INCORRECT? An attribute An attribute In a row of a In a row of a
of an entity of an entity relational relational table, an
can have can be table, an attribute can have
69 3 C 1 more than composite attribute can exactly one value or a
one value have more NULL value
than one value

70 3 What is a relationship called when it is maintained between two entities? B 1 Unary Binary Ternary Quaternary
Key to represent relationship between tables is called Primary key Secondary Foreign Key None of the
71 3 C 1
Key mentioned
We indicate roles in E-R diagrams by labeling the lines that connect ___________ to __________ Diamond , Rectangle, Rectangle, Diamond, rectangle
72 3 D 1
diamond diamond rectangle
An entity set that does not have sufficient attributes to form a primary key is termed a __________ Strong entity Variant set Weak entity Variable set
73 3 C 1
set set
The descriptive property possessed by each entity set is _________ Entity Attribute Relation Model
74 3 B 1
The Relationship sets that involve two entities is known as ________ Relationship set Unary Binary Ternary Many to Many
75 3 B 1

In a many to one relationship, the primary key of one entity acts as foreign key on which side? On the side On the side On both the None of the Above
where single where many sides
76 3 B 1 (one) relationship
relationship is defined
is defined
________ is a set of entities of the same type that share the same properties, or attributes. Attribute set Relation set Entity set Entity model
77 3 C 1

78 3 How many type of mapping cardinalities are there ? D 1 1 2 3 4

79 3 Attributes of entity A are associated with at most one entity in B, This is called as C 1 One to many One to one Many to one Many to many
80 3 If you were collecting and storing information about your music collection, an album would be considered as____________ B 1 Relation Entity Instance Attribute
81 3 What term is used to refer to a specific record in your music database; for eg; information stored about a specific album? B 1 Relation Instance Table Column
Let E1 and E2 be two entities in an E/R diagram with simple single-valued attributes. R1 and R2 are two relationships between E1 and E2, where 2 3 4 5
82 3 R1 is one-to-many and R2 is many-to-many. R1 and R2 do not have any attributes of their own. What is the minimum number of tables required to B 1
represent this situation in the relational model?
What is the min and max number of tables required to convert an ER diagram with 2 entities and 1 relationship between them with partial Min 1 and
participation constraints of both entities? max 2 Min 1 and Min 2 and Min 2 and max 2
83 3 C 1
max 3 max 3

Which of the following statement is False about Week Entity set? Week Entity A week Week entity Tuples in a Week
can be entity set has set avoid data entity set are not
deleted no primary duplication partitioned according
automatically keys unless and to their relationship
when their attributes of consequent with tuples in a strong
84 3 D 1 strong entity strong entity possible entity set
is deleted set on which inconsistency
it dependes caused by
are included duplicating
the key.
An E-R Diagram includes i. An E-R Diagram Portraying Entity types I,II &III are only I is II & III are None of the above
85 3 A 1
ii.Attributes for each Entity Type iii.Relationship among Entity Types Page 3 of 13 correct correct correct
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Draw ER diagram for university database consisting
four entities Student, Department, Class and
Faculty.
Student has a unique id, the student can enroll for
multiple classes and has a most one major. Faculty
86 3 must belong to department and faculty can teach 7
multiple classes. Each class is taught by only
faculty. Every student will get grade for the class
he/she has enrolled Also Find out the minimum number of tables required to represent the
given ER diagram in relational model.
87 4 Types of SQL Commands are _____ D 1 DDL DML DCL All of the above
Which of the following is not a type of SQL statement? Data Data Data Control Data Communication
Manipulation Definition Language Language (DCL)
88 4 D 1
Language Language (DCL)
(DML) (DDL)
89 4 Commands that comes under DDL is/are – D 1 DROP CREATE TRUNCATE All of the above
90 4 Command that comes under DML is/are – C 1 ROLLBACK GRANT UPDATE All of the above
Command that comes under DCL is/are - GRANT REVOKE Both A. and None of the above
91 4 C 1
B.
Which of the following is generally used for performing tasks like creating the structure of the relations, deleting relation? DML(Data Query Relational DDL(Data Definition
92 4 D 1 Manipulation Schema Language)
Language)
Which of the following statements contains an error? Select * from Select empid Select empid Select empid where
emp where from emp from emp; empid = 1009 and
93 4 D 1
empid = where empid lastname =
10003; = 10006; ‘GELLER’;
94 4 You can add a row using SQL in a database with which of the following? C 1 ADD CREATE INSERT MAKE
Which of the following syntax is true for adding a new record in table ? INSERT INSERT INSERT INSERT IN
INTO INTO INTO table_name
table_name (column1, table_name (column1, column2,
(column1, column2, (column1, column3, ...)
column2, column3, ...) column2, VALUES (value1,
95 4 A 1 column3, ...) VALUES column3, ...) value2, value3, ...);
VALUES (value1, (value1,
(value1, value2, value2,
value2, value3, ...); value3, ...);
value3, ...);
The FROM SQL clause is used to______? specify what specify range specify search None of these
table we are for search condition
selecting or condition
96 4 A 1
deleting data
FROM

97 4 Select the correct constraint in SQL? D 1 NOT NULL CHECK DEFAULT All of the above
98 4 The table records can be retrieved using which command? B 1 RETRIEVE SELECT CREATE ALTER
___________ and __________constraints form the core of the PRIMARY KEY constraint. NOT NULL , NOT NULL NOT NULL , NOT NULL ,
99 4 D 1 CHECK , DEFAULT FOREIGN UNIQUE
KEY
To modify the students table and to add a primary key on the student_id Column, Which statement must be used to accomplishes this task? alter table Alter table Alter table A&C BOTH
Note: The table is currently empty. students students add students add
modify constraint constraint
100 4 D 1
(student_id primary key stud_id_pk
primary key); (student_id); primary key
(student_id);
Which of the following is TRUE about UNIQUE constraint? In columns Unique A single table All of the above
that are values will can have more
subject to the always be than one
UNIQUE present in the unique
101 4 D 1 constraint, column constraint,
duplicate containing since it can be
values are not the unique applied to
allowed. constraint. more than one
column.
102 4 What default value gets stored in columns of the table? A 1 NULL 0 1 -1
103 4 ____________ is an attribute, or set of attributes, that uniquely identifies a tuple within a relation B 1 Foreign key Superkey Matching key None of above
Which of the following set of operations is a valid set of aggregate operations in SQL? COUNT, MAX, AVG, UNION, AVG, MIN, MAX,
104 4 A 1 MAX, AVG, SUM, COUNT, ASC
SUM SELECT MIN, DESC
Aggregate functions are functions that take a ___________ as input and return a single value. Collection of Single value Aggregate Both Collection of
105 4 A 1 values value values & Single value

All aggregate functions except _____ ignore null values in their input collection. Count(attribu Count(*) Avg Sum
106 4 B 1
te)
What will be the output of following statement? 9001 9000 8999 9000.6
107 4 Suppose salary = 9000.67 A 1
SELECT ROUND(salary) from emp;
What will be the output of following Query? Tom TOM tom None of above
108 4 B 1
select upper('Tom') from NAMES
109 4 Which clause should be used to exclude group results? B 1 WHERE HAVING RESTRICT GROUP BY
110 4 The intersection operator is used to get the _____ tuples. B 1 Different Common All Repeating
The union operation automatically __________ unlike the select clause. Adds tuples Eliminates Adds common Eliminates duplicate
111 4 D 1 unique tuples tuples

112 4 If we want to retain all duplicates, we must write ________ in place of union. A 1 Union all Union some Intersect all Intersect some
The _____________ is essentially used to search for patterns in target string. Like Null In Predicate Out Predicate
113 4 A 1
Predicate Predicate
114 4 We can test for the nonexistence of tuples in a subquery by using the _____ construct. B 1 Not exist Not exists Exist Exists
Which of the following statements are TRUE regarding subqueries? A subquery A subquery There is no Both A and B
can retrieve can appear limit on the
zero or more on either side number of
rows of a subquery
115 4 D 1 comparison levels in the
operator WHERE
clause of a
SELECT
statement

Page 4 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
What is a correlated sub-query? An A sub-query A sub-query A sub-query that does
independent that uses the that not depend on its
query that correlation substitutes the outer query’s
uses the name of an names of the correlation names
116 4 B 1 correlation outer query outer query
name of
another
independent
query.
What is the significance of “ORDER BY” in the following MySQL statement? Data of Data of Data will not None of these
SELECT emp_id, fname, lname emp_id will emp_id will be sorted
117 4 FROM person A 1 be sorted in be sorted in
ORDER BY emp_id; ascending descending
order order
Which join refers to join records from the Right table that have no matching key in the left table are include in the result set: Left outer Right outer Full outer join Half outer join
118 4 B 1
join join
Which is true fro ANY and ALL keyword in SQL? ANY and ANY and Both A. and None of mentioned
ALL ALL operate B.
operators are on
119 4 C 1 used with subqueries
WHERE or that return
HAVING. multiple
values.
Syntax for creating views is __________ CREATE CREATE DROP VIEW CREATE VIEW AS
120 4 A 1 VIEW AS VIEW AS AS SELECT UPDATE
SELECT UPDATE
121 4 Which of the following command makes the updates performed by the transaction permanent in the database? B 1 ROLLBACK COMMIT TRUNCATE DELETE
122 4 select substr(‘123456789’, 4,3) from dual; C 1 6789 2345 456 456789
123 4 What is the difference between DELETE and TRUNCATE statements? 2
124 4 What is the difference between TRUNCATE and DROP statements? 2
Write query for the following: employee(id,name,salary,address)
(1)To create a table from a table.
125 4 (2) To eliminate duplicate rows using distinct Keyword. 4
(3) To add a new column in the table
(4) To sort data in a table
Write query for the following.
employee(id,name,salary,address)
department(d_id,d_name,id)
126 4 (1) Create a table from a table. 3
(2) To join two existing tables using Natural Join.
(3) To drop a view.

127 4 What is the main difference between ‘BETWEEN’ and ‘IN’ condition operators? 2
128 4 Write with example various built in string functions. 4
129 4 Explain given below aggregate function with example-AVG(),COUNT() 2
130 4 Explain given aggregate function with example-MAX(),MIN(),SUM() 3
131 4 Explain given below set operations with example-UNION,UNION ALL,INTERSECTION,MINUS 4
132 4 Explain ORDER BY clause with example. 2
133 4 Explain GROUP BY and HAVING clause with example. 4
we have following relations:
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
Answer the following queries in SQL.
(i) Find name of supplier for city = ‘Delhi’.
134 4 (ii) Find suppliers whose name start with ‘AB’ 7
(iii) Find all suppliers whose status is 10, 20 or 30.
(iv) Find total number of city of all suppliers.
(v) Find s# of supplier who supplies ‘red’ part.
(vi) Count number of supplier who supplies ‘red’ part.
(vii) Sort the supplier table by sname.?
We have following relations:
EMP(empno, ename, jobtitle, managerno, hiredate, sal, comm, deptno)
DEPT(deptno, dname, loc)
Answer the following queries in SQL.
i) Find the Employees working in the department 10, 20, 30 only.
135 4 ii) Find Employees whose names start with letter A or letter a. 5
iii) Find Employees along with their department name.
iv)Insert data in EMP table.
v) Find the Employee no ,name and salary whose salary is in range 10000 to 30000.

Consider following schema and write SQL for given statements.

Student(Rollno, Name, Age,Sex,City)
Student_marks(Rollno,Sub1,Sub2,Sub3,Total,Average)
Write query to
136 4 4
(i) Calculate and store total and average marks from Sub1, Sub2& Sub3.
(ii) Display name of students who got more than 60 marks insubject Sub1.
(iii) Display name of students with their total and average marks.
(iv) display name of students who got equal marks in subject Sub2.
we have following relations
EMP(empno, ename, jobtitle, managerno, hiredate, sal, comm, deptno)
DEPT(deptno, dname, loc)
Answer the following queries in SQL.
137 4 4
i) Find Employees whose manager is KING.
ii Find the Employees who are working in Smith's department
iii) Find the Employees who get salary more than Allen’s salary.
iv)Display employees who are getting maximum salary in each department.
Consider following schema and write SQL for given statements.
Student(RollNo, Name, Age, Sex, City)
Student_marks(RollNo, Sub1, Sub2,Sub3,Total,Average)
Write query to
138 4 4
(i) Display name and city of students whose total marks are greater than 225.
(ii) Display name of students who got more than 60 marks in each subject.
(iii) Display name of city from where more than 10 students come from.
(iv) Display a unique pair of male and female students.

Page 5 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
For Supplier – Parts database
Supplier(S#,sname,status,city)
Parts(P#,pname,color,weight,city)
SP(S#,P#,quantity)
139 4 Answer the following queries in SQL. 4
1. Display the name of supplier who lives in ‘Ahmedabad’.
2. Display the part name which is not supplied yet.
3. Count how many times supplier ‘S1’ has supplied part ‘P1’.
4. Find all suppliers whose status is either 20 or 30.
Write queries for the following tables:
T1 ( Empno, Ename , Salary, Designation)
T2 (Empno, Deptno.)
(1) Display all the details of the employee whose salary is lesser than 10K.
(2) Display the Deptno in which Employee Seeta is working.
140 4 (3) Add a new column Deptname in table T2. 7
(4) Change the designation of Geeta from ‘Manager’ to ‘Senior Manager’.
(5) Find the total salary of all the employees.
(6) Display Empno, Ename, Deptno and Deptname.
(7) Drop the table T1.
Consider the following Hotel database:
hotel (hotel-no, name, address)
room (room-no, hotel-no, type, price)
booking (hotel-no, guest-no, date-from, date-to, room-no)
guest (guest-no, name, address)
Give an expression in SQL for each of the following queries:
141 4 1. List the names and addresses of all guests in London, alphabetically ordered by name. 7
2. List out hotel name and total number of rooms available.
3. List the details of all rooms at the Grosvenor Hotel, including the name of the guest staying in the room, if the room is occupied.
4. List all guests currently staying at the Grosvenor Hotel.
5. List the rooms that are currently unoccupied at the Grosvenor Hotel.
6. List the number of rooms in each hotel in London.
7. List out all guests who have booked room for three or more days.
Consider following schema and write SQL for given statements.
Client_master(clientno, name, address, city, pincode, state, baldue)
Product_master(productno, name, profitpercent, unitmeasure, sellprice, costprice)
Salesman_master (Salesmanno,name,address,city,pincode, state,salary,tgtotget, remarks)
1) Find out the names of all clients.
142 4 2) List all the clients who are located in Mumbai. 7
3) Delete all salesmen from salesman_master whose salaries are equal to Rs.3500.
4) Destroy the table client_master along with data.
5) List the name of all clients having ‘a’ as the second letter in their names.
6) Count the number of products having cost price is less than or equal to 500.
7) Calculate the average, minimum and maximum sell price of product.
Consider following schema and write SQL for given statements.
Student (RollNo, Name, DeptCode, City)
Department (DeptCode, DeptName)
Result (RollNo, Semester, SPI)
143 4 1. Display the name of students with RollNo whose name ends with ‘sh’. 5
2. Display department wise total students whose total students are greater than 500.
3. List out the RollNo, Name along with CPI of Student.
4. Display student name who got highest SPI in semester 1.
5. Display the list of students whose DeptCode is 5, 6,7,10.
Consider the relation Database.
Person(SSN, name, city)
Car(license_no, year, model, SSN)
Accident(drive_no, SSN, license_no, accidentyear, damage_amt)

144 4 1) Find out total no of cars that has accident in 1988. 5

2) Find the name of driver who did not have an accident in 'Delhi'
3) Find the car, who don't have total damage of more than 1000rs.
4) Find the cars sold in 2006 and whose owner are from vadodara.
5) How many different models of car are used by Mr.abc.
145 4 Explain difference between simple view & complex view. 2
146 4 What are the types of join and explain each? 7
147 4 State the differences between COMMIT and ROLLBACK commands 4
148 5 How would you describe Functional Dependency 1
149 5 What are the uses of Functional Dependency ? 1
150 5 A functional dependency is denoted by ….... symbol C 1 & * → %
151 5 A functional dependency is a relationship between or among ……. D 1 Tables Rows Relations Attributes
Which of the following is not Armstrong’s Axiom? Reflexivity Transitivity None of these Augmentation rule
152 5 C 1
rule rule
We can use the following three rules to find logically implied functional dependencies. This collection of rules is called Axioms Armstrong’s Armstrong Closure
153 5 B 1
axioms
There are two functional dependencies with the same set of attributes on the left side of the arrow: A->BC A->B B->C None of the
A→BC mentioned
154 5 A 1
A→B
This can be combined as
There is a relationship AC →B, A →D, and D→B. Here A is alone capable of determining B, which means B is …....................dependent on AC Partially Fully Medium Short
155 5 A 1
156 5 Differentiate trivial and non-trivial FD with example. 3
Functional dependency which also known as a nontrivial dependency occurs when A →B holds true where C is a subset B is a subset B is not a None of these
157 5 C 1
….................................................... of B of A subset of A
Given relation R with attributes A,B, C,D,E,F and set of FDs as A → BC, E → CF,
158 5 3
B->E and CD-> EF. Find out closure {A, B}+, {C,D}+ of the set of attributes.
Compute the closure of the following set of functional dependencies for relation
159 5 4
schema R = (ABCDE),F = {A → C, E → D, B → C} List the candidate keys for R.
Compute the closure of R (A, B, C, D, E) with the following set of functional
160 5 dependencies A → BC , CD → E ,B → D E → A 3
List the candidate keys of R.
If A→B, A→ C then which of the following is true? A→BC A→B A→C All of the mentioned
161 5 D 1
Given relation R with attributes A,B, C,D and set of FDs as {B → E , {B → AF , {B → A , None of there
162 5 B → A , AD → C , C → ABD . Canonical cover for given FDs C 1 C → BD, C → BD, C → BD,
AD → C} AD → C} AD → C}
Suppose a relational schema R(P, Q, R, S), and set of functional dependency as following
163 5 F : { P→ QR , Q →R, P→ Q , PQ →R } 7
Find the canonical cover Fc .

Page 6 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Suppose a relational schema R(w, x, y, z), and set of functional dependency as following
164 5 F : { x → w, wz → xy, y → wxz } 7
Find the canonical cover Fc
165 5 In the __________ normal form, a composite attribute is converted to individual attributes. A 1 First Second Third Fourth
Functional Dependencies are the types of constraints that are based on______ Key Key revisited Superset key None of the
166 5 A 1
mentioned
167 5 Prove the statement “Every relation which is in BCNF is in 3NF but the converse is not true 4
Consider a relation R with five attribute A,B,C,D,E having following dependencies :
A → B, BC→ E and ED→A
168 5 4
a)List all Keys for R
b) In which normal form table is, justify your answer.
Consider table R(A,B,C,D,E) with FDs as A → B, BC→E and ED→ A.
169 5 3
Rrlation is in which normal form? Justify your answer
Compute the closure of the following set F of functional dependencies for relation schema
170 5 4
R = (ABCDE),F = {A → C, E → D, B → C} List the candidate keys for R. The relation R is in which normal form.
Third Normal Form is …......................... 2NF and no 2NF or no 4NF or no None of these
transitive transitive transitive
171 5 A 1 dependencies dependencies
dependencies

A relation is in 2NF when it is in 1 NF and …...................... No Partial No partial None of these

Transitivity functional functional
172 5 C 1
dependencies dependencies

In which normal form conversion of composite attribute to individual attribute happens, First NF Second NF Third NF None of these
173 5 A 1
Normalization is used to design ________________ Join Multi-valued Cyclic Relational database
dependencies dependencies dependencies
174 5 D 1

In a schema with attributes A, B, C, D and E following set of functional dependencies are given CD → AC BD → CD BC → CD AC → BC
175 5 {A → B, A → C, CD → E, B → D, E → A} B 1
Which of the following functional dependencies is NOT implied by the above set?
F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional dependencies The relation R is in 1NF, but in BCNF in 3NF, but in 2NF, but not in
176 5 A 1
not in 2NF. not in BCNF. 3NF.
AB→CD, AF→D, DE→F, C→G , F→E, G→A CF+ = BG+ = AF+ = (d)AB+ =
177 5 Which one of the following options is false? C 1 {ACDEFG} {ABCDG} {ACDEFG} {ABCDFG}

178 5 Which normal form is considered adequate for normal relational database design? B 1 1NF 3 NF 2 NF 4 NF
179 5 Every BCNF is in D 1 1 NF 2 NF 3 NF All of the above
A table has fields F1, F2, F3, F4, and F5, with the following functional dependencies: 3 NF 2 NF 4 NF 1`NF
180 5 D 1
F1→F3 , F2→F4 , (F1,F2)→F5 in terms of normalization, this table is in
Which of the following is TRUE? Every Every No relation A relation R is in
relation in relation in can be in 3NF
2NF BCNF both BCNF if every non-prime
181 5 B 1 is also in is also in and 3NF attribute of R is fully
BCNF 3NF functionally
dependent
on every key of R
Consider the following functional dependencies in a database. in second in third in BCNF None of these
Date_of_Birth->Age Age->Eligibility normal form normal form
Name->Roll_number Roll_number->Name but not in but not in
182 5 Course_number->Course_name Course_number->Instructor D 1 third normal BCNF
(Roll_number, Course_number)->Grade form
The relation (Roll_number, Name, Date_of_birth, Age) is

183 5 Differentiate between 4NF and 5NF with suitable example . 5

Every 5NF is in in 1 NF but in 2 NF but in 3 NF but None of these
184 5 D 1
not in 2NF not in 3NF not in 1NF
185 5 Multivalued dependencies should ________ be eliminated. B 1 Commonly Always Sometimes Never
Consider the relation R(A,B,C) for functional dependency set ( A->B and B->C) which is decomposed into two relations R1(A,C) and R2(B,C).
186 5 4
Check this decomposition is dependency preserving or not.
S1: Every table with two single-valued attributes is in 1NF, 2NF, 3NF and BCNF. Both S1 and S1 is FALSE Both S1 and S1 is TRUE and S2
S2: AB→C, D→E, E→C is a minimal cover for the set of functional dependencies S2 are and S2 are is FALSE.
187 5 D 1
AB→C, D→E, AB→E, E→C. FALSE. S2 is TRUE. TRUE.
Which one of the following is CORRECT?
For a database relation R(a,b,c,d), where the domains a, b, c, d include only atomic values, only the following functional dependencies and those in first in second in third None of the above
that can be inferred from them hold: normal form normal form normal form
{ a → c, b → d } but not in but not in
188 5 A 1
This relation is second first normal
normal form form

F = {CH → G, A → BC, B → CFH, E → A, F → EG} is a set of functional dependencies.How many candidate keys does the relation R have? 3 6 4 None of these
189 5 C 1

A transaction is delimited by statements (or function calls) of the form __________ Begin Start Get Read transaction and
transaction transaction transaction write transaction
190 6 A 1
and end and stop and post
transaction transaction transaction
191 6 Identify the characteristics of transaction: D 1 Atomicity Durability Isolation All of the mentioned
192 6 Each modification done in database transaction are first recorded into the ______. B 1 Harddrive Log Disk Datamart
When the transaction finishes the final statement, in which state the transaction enters into? Active state Committed Partially Abort state
193 6 C 1 state committed
state
194 6 A transaction may not always complete its execution successfully. Such a transaction is termed as _______. A 1 Aborted Terminated Closed All of the mentioned
195 6 Which of the following is not a state in transaction? B 1 Active Terminated Aborted Partially committed
196 6 Collection of operations that form a single logical unit of work are called __________ A 1 Transactions Units Network Views
With regards to transaction processing, any DBMS should be capable of: Ensuring that Parts of a Transactions All of the above.
transactions transaction do not make
are free from are not lost the database
197 6 D 1 interference due to a inconsistent.
from other failure.
users.

198 6 Which of the following is an atomic sequence of database actions? A 1 Transaction Concurrency Relations All of the mentioned
If the state of the database no longer reflects a real state of the world that the database is supposed to capture, then such a state is called _____. Consistent Parallel state Atomic state Inconsistent state
199 6 D 1
state

Page 7 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Transaction processing is associated with everything below except ____. Producing Recording a Confirming an Maintaining a data
detail business action or
200 6 C 1 summary or activity triggering a
exception response
reports
201 6 A transaction completes its execution is said to be ______. D 1 Saved Loaded Rolled Committed
Consider the given two scinarios: (1) money is transferred from account-A to account-B and (2)money is debited from account-A . Which of the Only 1 Only 2 Both 1 and 2 None of the
202 6 C 1
following forms a transaction? individually mentioned
______ means that the data used during the execution of a transaction cannot be used by a second transaction until the first one is completed. Consistency Durability Isolation Atomicity
203 6 C 1
What is ACID properties of Transaction? Atomicity, Atomicity, Atomicity, Automatically,
Consistency, Consistency, Concurrency, Concurrency,
204 6 B 1
Isolation, Isolation, Isolation, Isolation, Durability
Database Durability Durability
205 6 Which of the following has “all-or-none” property? A 1 Atomicity Durability Isolation Consistency
Which of the given property of transaction ensures that, The database system must take special actions to ensure that transactions operate properly Atomicity Durability Isolation Consistency
206 6 C 1
without interference from concurrently executing database statements?
207 6 The property of a transaction that persists all the crashes is called ______. B 1 Atomicity Durability Isolation Consistency
208 6 Which of the listed transaction property states that only valid data will be written to the database? A 1 Consistency Durability Isolation All of the mentioned
209 6 Execution of transaction in isolation preserves the _________ of a database. C 1 Atomicity Durability Consistency All of the mentioned
Which of the following systems is responsible for ensuring durability? Concurrency Atomic Compiler Recovery system
210 6 D 1 control system system
system
Which of the following systems is responsible for ensuring isolation? Concurrency Atomic Compiler Recovery system
211 6 A 1 control system system
system
A schedule is serialized if it is equivalent to a ______ schedule. Non-serial serial View None of the
212 6 B 1
mentioned
A schedule is __________ if it is conflict equivalent to a serial schedule. Conflict Conflicting Non None of the
213 6 A 1
serializable serializable mentioned
If a schedule S can be transformed into a schedule S’ by a series of swaps of non-conflicting instructions, then S and S’ are: Non conflict Equal Conflict Isolation equivalent
214 6 C 1
equivalent equivalent
215 6 I and J are _________ if they are operations by different transactions on the same data item, and at least one of them is a write operation. A 1 Conflicting Overwriting Isolated Durable
When one transaction nullifies the updates of another transaction, it is called: Inconsistent Phantom Dirty Read Lost Update
216 6 D 1
retrievals Read
Reading the data written by an uncommitted transaction is called _______. Lost Update Dirty Read Phantom Read Unrepeatable Read
217 6 B 1
When a transaction reads some variable from the buffer and when it reads the same variable later, it finds that the variable does not exist. It is Unrepeatable Lost Update Dirty Read Phantom Read
218 6 D 1
called ____. Read
219 6 To synchronize the concurrent accessing of database items, we use : C 1 Transactions States Locks Tables
Consider the given scinario and label the problem in it: (1) T1 reads the value of X (= 10 say). (2) T2 reads the value of X (= 10). (3) T1 updates Unrepeatable Lost Update Dirty Read Phantom Read
220 6 A 1
the value of X (from 10 to 15 say) in the buffer. (4) T2 again reads the value of X (but = 15). Read
221 6 The most widely used structure for recording database modification is called as _______ D 1 List Queue Stack Log
The value of the data item prior to the write is called as _________ Transaction Data-item Old value New value
222 6 C 1
identifier identifier
If the database modifications occur while the transaction is still active, the transaction is said to use the __________ modification technique Deferred Immediate Log None of the
223 6 B 1
mentioned
Which of the following protocols ensures conflict serializability and safety from deadlocks? Time-stamp Two-phase Graph Based None of the
224 6 A 1 ordering locking Protocol mentioned
protocol protocol
225 6 We say that a transaction has been ________ when its commit log record has been output to stable storage. C 1 Locked Completed Committed Released
Database locking concept is used to solve the problem of: Lost Update Uncommitte Inconsistent All of the above
226 6 D 1 d Data
Dependency
If a transaction has obtained a __________ lock, it can read but cannot write on the item Shared mode Exclusive Read only Write only mode
227 6 A 1
mode mode
If a transaction has obtained a ________ lock, it can both read and write on the item Shared mode Exclusive Read only Write only mode
228 6 B 1
mode mode
The two phase locking protocol consists which of the following phases? Growing Shrinking Both Growing None of the
229 6 C 1 phase phase and Shrinking mentioned
Phase
If a transaction may release locks but may not obtain any locks, it is said to be in ______ phase Growing Shrinking Deadlock Starved phase
230 6 B 1
phase phase phase
A transaction can proceed only after the concurrency control manager ________ the lock to the transaction Grants Requests Allocates None of the
231 6 A 1
mentioned
If a transaction can be granted a lock on an item immediately in spite of the presence of another mode, then the two modes are said to be Concurrent Equivalent Compatible Executable
232 6 C 1
________
233 6 A deadlock exists in the system if and only if the wait-for graph contains a ___________ A 1 Cycle Direction Bi-direction Rotation
What are the ways of dealing with deadlock ? Deadlock Deadlock Deadlock All of the mentioned
234 6 D 1
prevention recovery detection
A system is in a ______ state if there exists a set of transactions such that every transaction in the set is waiting for another transaction in the set. Idle Waiting Deadlock Ready
235 6 C 1
236 6 The deadlock state can be changed back to stable state by using _____________ statement. B 1 Commit Rollback Savepoint Locking
When transaction Ti requests a data item currently held by Tj , Ti is allowed to wait only if it has a timestamp smaller than that of Tj (that is, Ti is Wait-die Wait-wound Wound-wait Wait
237 6 A 1
older than Tj ). Otherwise, Ti is rolled back (dies). This is
238 6 The situation where the lock waits only for a specified amount of time for another lock to be released is A 1 Lock timeout Wait-wound Timeout Wait
Consider the following two transactions:
T1: read(A);
read(B);
if A = 0 then B := B + 1;
write(B).
T2: read(B);
239 6 read(A); 7
if B = 0 then A := A + 1;
write(A).
Add lock and unlock instructions to transactions T1 and T2, so that they observe the two-phase locking protocol. Can the execution of these
transactions
result in a deadlock?
Test Serializability for the following schedule: 1.r1(X);r3(X);w1(X);r2(X);w3(X) Conclude that the given Schedule is
240 6 5
Serializable or not.
Consider the following transaction involving two bank accounts x and y. Atomicity Consistency Isolation Durability
241 read (x); x := x – 50 ; write (x) ; read (y) ; y≔ y + 50 ; write (y) B 1
6 The constraint that the sum of the accounts x and y should remain constant is that of _______.
Let ri(z) and wi(z) denote read and write operations respectively on a data item z by a transaction Ti. Consider the following two schedules. S1 is conflict S1 is not Both S1 and Neither S1 nor S2 is
S1 : r1(x) r1(y) r2(x) r2(y) w2(y) w1(x) serializable conflict S2 are conflict conflict serializable
S2 : r1(x) r2(x) r2(y) w2(y) r1(y) w1(x) and S2 is not serializable serializable
242 B 1
Which one of the following options is correct? conflict and S2 is
serializable conflict
6 serializable
Page 8 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Consider the following transaction with data items P and Q initialized to zero: A serializable A conflict A schedule for A schedule that is not
T1 : read (P) schedule serializable which a conflict serializable
read(Q) schedule precedence
if P = 0 then Q : = Q + 1; graph cannot
write (Q) ; be drawn
243 T2 : read (Q) ; D 1
read (P) ;
if Q = 0 then P : = P + 1 ;
write (P) ;
6 Any non-serial interleaving of T1 and T2 for concurrent execution leads to ___.
Consider the following log sequence of two transactions on a bank account, with initial balance 12000, that transfer 2000 to a mortgage payment We must We must We need not We can apply redo
and then apply a 5% interest. redo log undo log redo log and undo operations
1) T1 start record 6 to record 6 to records 2 and in arbitrary order
2) T1 B old =12000 new =10000 set B to set B to 3 because because they are
3) T1 M old =0 new =2000 10500 10000 and transaction T1 idempotent
244 4) T1 commit B 1 then redo log has committed
5) T2 start records 2
6) T2 B old =10000 new =10500 and 3
7) T2 commit
Suppose the database system crashes just before log record 7 is written. When the system is restarted, which one statement is true of the recovery
6 procedure?
In ordered indices the file containing the records is sequentially ordered, a ___________ is an index whose search key also defines the sequential Clustered Structured Unstructured Nonclustered index
245 7 A 1
order of the file. index index index
Indices whose search key specifies an order different from the sequential order of the file are called ___________ indices. Nonclustered Secondary All of the None of the
246 7 C 1
mentioned mentioned
247 7 An ____________ consists of a search-key value and pointers to one or more records with that value as their search-key value. A 1 Index entry Index hash Index cluster Index map
In a _______ clustering index, the index record contains the search-key value and a pointer to the first data record with that search-key value and Dense Sparse Straight Continuous
248 7 A 1
the rest of the records will be in the sequential pointers.
249 7 In a __________ index, an index entry appears for only some of the search-key values. A 1 Dense Sparse Straight Continuous
Incase the indices values are larger, index is created for these values of the index. This is called Pointed index Sequential Multilevel Multiple index
250 7 C 1
index index
251 7 A search key containing more than one attribute is referred to as a _________ search key. B 1 Simple Composite Compound Secondary
While inserting the record into the index, if the search-key value does not appear in the index. The system The system The system None of the
adds a places the inserts an mentioned
pointer to the record being index entry
new record in inserted after with the
the index the other search-key
252 7 C 1 entry records with value in the
the same index at the
search-key appropriate
values position

If the file containing the records is sequentially ordered, a ___________ is an index whose search key also defines the sequential order of the file. Clustering Cluttering Clustering Clustering number
253 7 B 1
data index index
254 7 State true or false: Clustering indices are also called as primary indices A 1 TRUE FALSE
255 7 If an index entry appears for every search key value in the file, it is called as ________ B 1 Dense key Dense index Sparse key Sparse index
256 7 If an index entry appears for only some of the search key values in the file, it is called as ________ D 1 Dense key Dense index Sparse key Sparse index
Indices with two or more levels are called as? Multiple Multilevel Bi- tri- Indices None of the
257 7 B 1
Indices indices mentioned
258 7 Differentiate primary index and clustered index. 5
259 7 Differentiate single level indexing and multi level indexing. 5
260 7 Explain dense index and sparse index. 7
261 7 Explain the node structure of B+ tree. 4
262 7 Construct B+ tree for following data: 30,31,23,32,22,28,24,29 where number of pointers that fit in one node are 5. 7
263 7 Construct B+ tree to insert the following(order of the tree is 3) 26,27,28,3,4,7,9,46,48,51,2,6. 7
Construct B+ tree for the following set of key values (2,3,5,7,11,17,19,23,29,31). assume that the tree is initially empty and values are added in
264 7 asceding order. construct B+ tree for the cases where the number of pointers that fit one node is four.after creation of B+ tree perform following 7
series of operations: a) insert 9 b) insert 10 c) insert 8 d) delete 23 e) delete 19
265 7 Insert the following key values 6, 16, 26, 36, 46 on a B+ tree with order = 3. 5
Construct a B+ tree for the following set of key values: 2, 3, 5, 7, 11, 17, 19, 23, 29, 31. Assume that the tree is initially empty and values are
added in ascending order. Construct B+ trees for the cases where the number of pointers that will fit in one node (i.e., number of children per
node) is as follows:4
266 7 7
M=4
M=6
M=8
Construct a B+ tree for (1, 4, 7, 10, 17, 21, 31, 25, 19, 20, 28, 42)
267 7 with n=4. 7

268 7 Construct B+ tree for following data: 30,31,23,32,22,28,24,29 where number of pointers that fit in one node are 4. 7
269 7 In a B+ tree, both the internal nodes and the leaves have keys. B 1 TRUE FALSE
Which of the following is true? B + tree B + tree B + tree B + tree allows rapid
allows only allows only allows rapid random access and
the rapid the rapid random access slower sequential
270 7 C 1 random sequential as well as access
access access rapid
sequential
access
271 7 A B+ tree can contain a maximum of 7 pointers in a node. What is the minimum number of keys in leaves? B 1 6 3 4 7
Which of the following is false? A B+ -tree A B+ -tree is In a B+ -tree, B+ -tree is shallower
grows balanced the sibling than B-tree
272 7 A 1 downwards pointers allow
sequential
searching
Which of the following is false? Compared to Deletion in B- B+ -tree has Both B-tree and B+ -
B-tree, B+ - tree is more greater depth tree have same search
tree has complicated than and insertion
273 7 C 1
larger fanout than in B+ - corresponding efficiencies
tree B-tree

274 7 Which one of the following data structures are preferred in database-system implementation? C 1 AVL tree B-tree B+ -tree Splay tree
1.) Statement 1: When a node is split during insertion, the middle key is promoted to the parent as well as retained in right half-node. Statement 1 Statement 2 Both the Both the statements
Statement 2: When a key is deleted from the leaf, it is also deleted from the non-leaf nodes of the tree. is true but is true but statements are are false
275 7 A 1
statement 2 is statement 1 true
false is false
276 7 What is the maximum number of keys that a B+ -tree of order 3 and of height 3 have? D 1 3 80 27 26
In B+ tree the node which points to another node is called Leaf node External Final node Internal node
277 7 D 1
node

Page 9 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
The lengths The lengths The number of The number of
of the paths of the paths children of records in any two
B+ Trees are considered BALANCED because. from the root from the root any two non- leaf nodes differ by at
to all leaf to all leaf leaf sibling most 1.
278 A 1
nodes are all nodes differ nodes differ
equal. from each by at most 1.
other by at
7 most 1.
279 7 Consider a B+-tree in which the maximum number of keys in a node is 5. What is the minimum number of keys in any non-root node? B 1 1 2 3 4
The following key values are inserted into a B+-tree in which order of the internal nodes is 3, and that of the leaf nodes is 2, in the sequence given 2 3 4 5
below. The order of internal nodes is the maximum number of tree pointers in each node, and the order of leaf nodes is the maximum number of
280 C 1
data items that can be stored in it. The B+-tree is initially empty. 10, 3, 6, 8, 4, 2, 1. The maximum number of times leaf nodes would get split up
7 as a result of these insertions is
281 8 Write a PL/SQL block to print the sum of even numbers from 1 to 100. 5
282 8 Write a PL/SQL block to print the sum of odd numbers from 1 to 100. 5
283 8 Define PL/SQL. Write PL/SQL block to print sum of even numbers between 1 to 20. 5
284 8 Write A PL/SQL block to print the sum of Numbers from 1 to 50. 5
285 8 Write A PL/SQL block to print the given number is Odd or Even 5
286 8 Differentiate Implicit Cursor & Explicit Cursor.. 4
287 8 Write a PL/SQL cursor to display the names and branch of all students from the STUDENT relation. 5
Write a PL/SQL block using explicit cursor that will display the customer
name, the fixed deposit number and the fixed deposit amount of the first 5 customers holding the highest amount in fixed deposits.
Use following database:
288 8 5
cust_mstr ( custno, name, occupation)
fd_dtls ( fd_ser_no, fd_no, type, period, opndt, duedt, amt, dueamt)
acct_fd_cust_dtls ( acct_fd_no, custno)
A stored function is created to perform the acct_no check operation.
f_ChkAcctNo() is the name of function which accepts a variable acct_no from the user and returns value 0 if acct_no does not exist or 1 if
289 8 acct_no exists. Write a PL/SQL block that performs transaction(i.e., deposit/withdrawal)on account. If account exists, change balance depending 5
on the transaction amount to be deposited or withdrawal. Assume account table with fields – account number, name, type and balance.

290 8 Write a PL/SQL program for inserting even numbers in EVEN table and odd number in ODD table from number 1 to 50. 5
291 8 Write PL/SQL block to print whether the given number is Armstrong number or not. 5
292 8 Write a Stored procedure to Insert Data in Student_Detail [Student_id,Name,Age,Class] table. 5
293 8 Which of the following is used to input the entry and give the result in a variable in a procedure? D 1 Put and get Get and put Out and In In and out
The format for compound statement is Begin ……. Begin Begin ……. Both Begin ……. end
294 8 D 1 end atomic……. repeat and Begin
end atomic……. end
A stored procedure in SQL is a___________ Block of Group of Group of None of the
functions Transact- distinct SQL mentioned
SQL statements.
statements
295 8 B 1
compiled
into a single
execution
plan.
296 8 Temporary stored procedures are stored in _________ database. D 1 Master Model User specific Tempdb
A __________ is a special kind of a store procedure that executes in response to certain action on the table like insertion, deletion or updation of Procedures Triggers Functions None of the
297 8 B 1
data. mentioned
298 8 Triggers are supported in C 1 Delete Update Views All of the mentioned
What are the after triggers? Triggers These These triggers All of the mentioned
generated triggers run run after an
after a after an insert, views,
299 8 B 1 particular insert, update update or
operation or delete on a delete on a
table table

300 8 The variables in the triggers are declared using B 1 - @ / /@

301 8 Which of the following is NOT an Oracle-supported trigger? B 1 BEFORE DURING AFTER INSTEAD OF
What of the following are types of triggers? Define, Drop, Insert, Update, All of the mentioned
302 8 C 1 Create Comment Delete

303 8 Triggers ________ enabled or disabled A 1 Can be Cannot be Ought to be Always

304 8 What is the symbol for assignment operator in PL/SQL? C 1 == = := _=
305 8 What is the symbol for the operator used to compare equality between two variables in PL/SQL? B 1 == = := _=
What is the output of the following PL/SQL block? DECLARE 100 200 300 400
a number(3) := 100;
b number(3) := 200;
BEGIN
IF( a = 100 ) THEN
306 8 IF( b <> 200 ) THEN A 1
dbms_output.put_line(b);
END IF;
END IF;
dbms_output.put_line(a);
END;
What will be printed by this procedure? 5 10 25 50
DECLARE
a number;
PROCEDURE squareNum(x IN OUT number) IS
BEGIN
x := x * x;
307 8 END; C 1
BEGIN
a:= 5;
squareNum(a);
dbms_output.put_line(a);
END;
What is the difference between a PL/SQL function and a PL/SQL Procedure? There is no A procedure A function Both B and C
difference can return can be called
more than through an
variable SQL SELECT
308 8 D 1
whereas a statement but
function can a procedure
return only cannot.
one variable
309 8 THE _____ clause specifies the table name on which the trigger is to be attached. A 1 For On None In

Page 10 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
What are AFTER triggers? Triggers These These triggers Triggers that run after
generated triggers run run after an other triggers are over
after a after an insert, views,
310 8 B 1 particular insert, update update or
operation or delete on a delete on a
table table

PL/SQL function must contain a – Follow GOTO Return Null Statement

311 8 C 1
Statement statement Statement
In order to remove a PL/SQL function, which statement is used? REMOVE DELETE ERASE DROP FUNCTION
312 8 D 1
FUNCTION FUNCTION FUNCTION
313 8 An SQL __________ refers to a program that retrieves and processes one row at a time, based on the results of the SQL statement. A 1 Cursor Function Procedure View
314 8 Which of the following PL/SQL cursors is automatically created? A 1 Implicit Explicit Exaggerate Oversplit
Which of the following is/are implicit cursor/s in PL/SQL? %FOUND %ROWCOU %ISOPEN All of the mentioned
315 8 D 1
NT
316 8 Which cursor statement is used to place the content of the current row into variables? A 1 fetch get open close
_________ refers to the range of activities involved in extracting data from a database Query Query Parsing Query Evaluation
317 9 B 1
Optimization Processing
The steps involved in processing __________ Parsing and Optimization Evaluation All of Given
318 9 D 1
translation
In query processing, system must first translate given query into which form? Internal form Physical high level New form
319 9 A 1
form form
In generating the internal form of the query, the parser checks ___________ Syntax of Verifies None of given Both of Given
320 9 D 1 query relation name

Match the following: A-2, B-3, C- A-1, B-3, C- A-3, B-1, C-2 A-3, B-2, C-1
A) evaluation primitive 1 2
B) query-execution plan
c) query-execution engine
321 9 A 1
1) takes a query-evaluation plan, executes that plan, and returns the answers to the query
2) A relationalalgebra operation annotated with instructions on how to evaluate it
3)A sequence of primitive operations that can be used to evaluate a query
Which statement(S) is/are true? Only A,B Only A,C,D Only D All A,B,C,D
A) A query could be expressed in SQL several different ways.
B) SQL query can be translated into a relationalalgebra expression in one of several ways.
322 9 D 1
C) There are several ways to evaluate relational-algebra expressions
D) We can execute each relational-algebra operation by one of several different algorithms.

____________ is the process of selecting the most efficient query-evaluation Query Regulary Query SQL
323 9 plan from among the many strategies usually possible for processing a given C 1 Processing Expression Optimization
query, especially if the query is complex.
Query-evaluation plan that minimizes the cost of query evaluation is a duty of ________ System User Database Programmer
324 9 A 1
Admin
___________ defines exactly what algorithm should be used for each operation, and how the execution of the operations should be coordinated Query Evaluation Query Expression tree
325 9 B 1 Engine plan Optimizer
Engine
Given a relational-algebra expression, it is the job of __________ to come up with _________ that computes the same result as the given query query query query evaluation
expression, and is the least-costly way of generating the result optimizer, evaluation optimizer, engine, query
326 9 C 1 expression engine, query- evaluation plan
tree expression evaluation
tree plan
The optimizer needs to generate alternative plans that produce the ___________ as the given expression, and to choose the least-costly one. partially rows as columns as same result
327 9 D 1 different result result
result
328 9 An _________ rule says that expressions of two forms are equivalent. A 1 equivalence query optimization annotation
Which of the following is/are commutative? Only B Only A,B Only A All A,B,C
A) Theta
329 9 B 1
B) Selection
C) Projection
The projection operation distributes over the _______ operation. union, intersection union set- difference
intersection, operations
330 9 C 1 and set-
difference
operations
The projection operation distributes over the _______ operation. union, intersection union set- difference
intersection, operations
331 9 A 1 and set-
difference
operations
Natural-join operations are ? commutative commutative associative not commutative
332 9 A 1 and
associative
A good ordering of join operations is important for reducing the size of _____ . final result query result temporary main memory
333 9 C 1
results
_____ used in database systems usually record the number of distinct Graph Histogram Table Log
334 9 values in each range, in addition to the number of tuples with attribute values in B 1
that range.
The size estimate of the result of a selection operation depends on? selection algorithm optimizer query procesor
335 9 A 1
predicate.
336 9 Estimating the size of _______ is more complicated than estimating the size of a selection or of a Cartesian product. B 1 projection natural join group by sum
A single query in DBMS can be executed through different algorithms or re-written in different forms and structures. The most optimal pathway select select select The given query is
of getting the correct output among all these algorithms can be obtained by the process of query optimization. columnA columnA columnA from already in optimized
from from Employee; form.
Consider the following query: Employee Employee
where where
With emp_table as select columnA from Employee where columnB> 3000; columnB columnB>30
Emp_table intersect (select columnA from Emp_table where columnC<3000); >3000 and 00 intersect
337 A 1 columnC<30 select
00; columnA
Identify the correct optimized SQL query. from
Employee
where
columnC<30
9 00;

Page 11 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Assume the following tables:
instructor(ID, name, dept_name, salary)
teaches(ID, course_id, sec_id, semester, year)
338 course(course_id, title, dept_name, credits) 2
Write Optimized Query to find the names of all instructors in the Music department, along with the titles of the courses that they teach is ?
9
Assume the following tables:
instructor(ID, name, dept_name, salary)
teaches(ID, course_id, sec_id, semester, year)
course(course_id, title, dept_name, credits)
339 1
Find the names of all instructors in the CSE department who have taught a course in 2009, along with the titles of the courses that they taught :

Optimize given query.

σ𝑑𝑒𝑝𝑡 𝑛𝑎𝑚𝑒 σ
= ′𝐶𝑆𝐸′( 𝑦𝑒𝑎𝑟 = 2009(
𝑖𝑛𝑠𝑡𝑟𝑢𝑐𝑡𝑜𝑟⨝𝑡𝑒𝑎𝑐ℎ𝑒𝑠))
_ ⬚
9
Given the following SQL query:

Student (sid, name, age, address)

Book(bid, title, author)
Checkout(sid, bid, date)

SELECT S.name
FROM Student S, Book B, Checkout C
WHERE S.sid = C.sid
340 AND B.bid = C.bid 3
AND B.author = ’Olden Fames’
AND S.age > 12
AND S.age < 20;

Show a physical query plan for this query, assuming there are no indexes and data is not sorted on any attribute.

9
For the following SQL query, give two equivalent logical plans in relational algebra such that one is likely to be more efficient than the other.
Indicate which one is likely to be more efficient.

341 SELECT R.a 5

FROM R, S
WHERE R.b = S.b
9 AND S.c = 3
Consider the SQL query

select *
from employee, department
342 2
where employee.dept_id = department.dept_id;

What evaluation plan would a query optimizer likely choose to get the least estimated
9 cost?
Grants privileges on SQL authorization mechanism works on____________ Entire Specified Specified Both A and B
343 10 D 1 relation tuples attributes

which statement is used to revoke an authorization? Revoke Modify Alter Define

344 10 A 1
345 10 Collections of operations that form a single logical unit of work are called __________ D 1 Views Networks Units Transactions
Authentication refers to : methods of Establishing controlling the all of the above
restricting Identity operation on
346 10 D 1 user access to the data
system

347 10 Prevention of access to database by Unauthorized Users is refered as C 1 Integrity Productivity Security Reliability
___________ may be transmissible from system to system Authenticatio Access Security None of the Above
348 10 A 1
n Control
In authorization graph if DBA provides authorization to u1 which in turn gives to u2 which of the following is correct ? If DBA If u1 revokes If DBA & u1 If u2 revokes
revokes authorization revokes authorization then u1
authorization from u2 then authorization authorization is
from u1 then u2 from u1 then revoked
u2 authorization u2
349 10 C 1 authorization is revoked authorization
is also is also
revoked revoked
.

350 10 _______________ is a technique of granting permission to authorized user to carry out particular transaction D 1 Integrity Productivity Security Authorization
Which form of Authorization allows the user only to read data? Update Read Access Insert Access Both A and B
351 10 B 1
Access
Which of the following is the Form of Authorization Access? Read Access Update Delete Access All of the above
352 10 D 1
Access
Which Role in Authorization can execute some data administration command like restore or Upgrade a database? System System Both A & B System Monitor
353 10 A 1 Administrato Control
r
With the Help of _______________,User can check his Credentials by him self by entering login id & Password in any Examination System. Authenticatio Productivity Security Authorization
354 10 A 1
n

Page 12 of 13
 L.J Institute of Engineering and Technology
Database Management System Question Bank

Note :
This question bank is only for reference purpose . L.J.U Test question paper may not be completely set from this question bank.

Unit MCQ
Sr Mark
Numb Question_Text Answ Option A Option B Option C Option D
No s
er er
Using Which ,the user can take snapshots of database? System System System Both A & B
355 10 B 1
Control Monitor Maintenance
Which is the lower level of System Control Authority? System System System System Monitor
356 10 A 1 Maintenance Administrato Control
r
The Usual way of supplying access control to a database is depending on ______________&_____________ priviledges within database Granting & Logging & Update & Both A & B
357 10 A 1
Revoking Logging off Tampering
What is used if any tampering with the database is suspected? Database Database Database Database Prevention
358 10 B 1
Locking Audit Updation
We can recover the lost transaction using___________ System System Authorization Audit trail
359 10 D 1
Control Monitor
Which form of access control enables data owners to extend access rights to other logons? MAC DAC Role-based Rule-based (RBAC)
360 10 B 1
(RBAC)
A security manager is setting up resource permissions in an application. The security manager has discovered that he can establish objects that Access Mandatory Discretionary Role based access
contain access permissions, and then assign individual users to those objects. The access control model that most closely resembles this is: matrix access access control control (RBAC)
361 10 D 1 control (DAC)
(MAC)

What is Used to define Access Permission? Access DAC System Authorization

matrix Control
362 10 A 1

______________is the default access Control mechanism for most desktop operating system MAC Role based DAC Rule-based (RBAC)
access
363 10 C 1
control
(RBAC)
Which Security Strategy is generally used in government and military services? MAC Role based DAC Rule-based (RBAC)
access
364 10 A 1
control
(RBAC)
Role Based Access Control is also known as___________ Rule-based Non discretionary None of the above
(RBAC) discretionary access control
365 10 B 1
access
control
Which one of the Following is True for DAC ? i. Users can transfer their object ownership to another user. I and II I and IV I,II and III I,II,III and IV
ii. The access type of other users can be determined by the user.
366 10 D 1
iii. Authorization failure can restrict the user access after several failed attempts.
iv. Unauthorized users will be blind to object characteristics called file size, directory path, and file name.
_______ has an enforced operating system that can label and delineate incoming application data. MAC Role based DAC Rule-based (RBAC)
access
367 10 A 1
control
(RBAC)
Under ____________access is allowed or denied to resource objects based on a set of rules defined by a system administrator MAC Role based DAC Rule-based (RBAC)
access
368 10 D 1
control
(RBAC)
When a particular account or group attempts to access a resource, the operating system checks the rules contained in the _______ for that object. System Mandatory Access Authorization List
Control List access Control Lists
369 10 C 1
control List (ACL)
(MACL)
Role Based Access Control grants access based on a user’s role and implements key security principles such as _____ and_____ least position and central assigning access
privilege and location authority rights and user-
separation of regulations specified rules
370 10 A 1
privilege and user-
specified rules

Page 13 of 13