FACULTY OF ENGINEERING AND TECHNOLOGY

DEPARTMENT OFCOMPUTER SCIENCE AND ENGINEERING

QUESTIONBANK

Degree & Branch : B.TECH- CSE

Semester : III/VI
Sub Code & Subject Name : 18CSC303J- DATABASE MANAGEMENT SYSTEMS
Regulation : 2018
Academic Year : 2021-2022
 QUESTION BANK

SUBJECT : Subject Code: 18CSC303J - Subject Name : Database Management Systems

SEM/YEAR: VI/III

Q. Course Competence
Questions Outcome BT Level
No.
1 In any hierarchy of data organization, what is the smallest
entity to be processed as a single unit is called?
(A) Data field CO1 BT1
(B) Data record
(C) Data file
(D) Database
2 There are certain packages that allow people to define
data items, place these items in particular records, combine
the records into designated files and then manipulate and
retrieve the stored data. What are they called?
CO1 BT1
(A) Data storage system
(B) Database management system (DBMS)
(C) Batch processing system
(D) Data communication package
3 Which of the following is true?- A database
management system
(A) Allows simultaneous access to multiple files
(B) Can do more than a record management system CO1 BT1
(C) Is a collection of programs for managing data in a single
file
(D) None of the above
4 Which of the following in not a function of DBA?
(A) Network Maintenance
(B) Routine Maintenance CO1 BT1
(C) Schema Definition
(D) Authorization for data access
5 What refers to the correctness and completeness of the data
in a database?
(A) Data security
CO1 BT1
(B) Data integrity
(C) Data constraint
(D) Data independence
6 Which of the following is a serious problem of
file management systems?
(A) Difficult to update
CO1 BT1
(B) Lack of data independence
(C) Data redundancy
(D) All of the above
7 Which of the following is Database Language?
(A) Data Definition Language
(B) Data Manipulation Language CO1 BT1
(C) Query Language
(D) All of the above
8 Who created the first DBMS?
(A) Edgar Frank Codd
(B) Charles Bachman CO1 BT1
(C) Charles Babbage
(D) Sharon B. Codd
9 What is scheme describes?
(A) Data elements
(B) Records and files CO1 BT1
(C) Record relationships
(D) All of the above
10 Which of the following is/are disadvantages of Database
Management System?
(A) Cost of Hardware and Software
(B) Reduce data redundancy
(C) Remove data inconsistency CO1 BT1
a) A
b) B
c) C
d) B & C
11 In the architecture of a database system what is the
external level
(A) Physical level
CO1 BT1
(B) Logical level
(C) Conceptual level
(D) View level
12 What is Data independence?
(A) Data is defined separately and not included in programs
(B) Programs are not dependent on the physical attributes of
data
(C) Programs are not dependent on the logical attributes of CO1 BT1
data
(D) Both programs are not dependent on the physical
attributes of data & programs are not dependent
on the logical attributes of data
13 What is Concurrent access?
(a) Accessing system by only single user at a time
(b) Accessing System by more than one user one by one
(c) Accessing system by more than one user at a CO1 BT1
time (simultaneously)
(d) Accessing system by single only

14 Concurrency control is important for which of the following

reasons?
(A) To ensure data integrity when updates occur to
the database in a multiuser environment
(B) To ensure data integrity when updates occur to the database
CO1 BT1
in a single-user environment
(C) To ensure data integrity while reading data occurs to
the database in a multiuser environment
(D) To ensure data integrity while reading data occurs to
the database in a single-user environment
15 What is the main purpose of DBMS is to provide
view of data to user.
(A) Abstract
CO1 BT1
(B) Complete
(C) Partial
(D) None of these
16 means to hide certain details of how data is
stored and maintain.
(A) Data isolation
CO1 BT1
(B) Data integrity
(C) Data Abstraction
(D) Data binding
17 How many levels in data abstraction?
(A) 2
(B) 4 CO1 BT1
(C) 1
(D) 3
18 In data abstraction which is lowest level of abstraction?
(A) Conceptual level
(B) View level CO1 BT1
(C) Physical level
(D) None of these
19 What is also called as Hierarchical model?
a. Tree structure
b. Plex Structure
CO1 BT1
c. Normalize Structure
d. Table Structure

20 "What data is stored ?" is described by level of

abstraction in DBMS.
a. Physical
b. View CO1 BT1
c. Conceptual
d. None of the above

21 Which of the following represents a relationship among

a set of values?
a. A Row
b. A Table CO1 BT1
c. A Field
d. A Column

22 If user doesn't know anything about the complexity of

database application then that user is called as
.
a. Naive User
CO1 BT1
b. Database Manager
c. Database Operator
d. Database Administrator

23 Data Model is collection of conceptual tools for describing

a. Data
b. Data Schema
Consistency CO1 BT1
c. Constraints
d. All of these
24 Which of the following is example of Object based logical
model ?
a. Entity Relationship Model
b. Hierarchical Model CO1 BT1
c. Relational Model
d. Network Model

25 In enterprise data modeling, which is incorrect?

a. You review current systems.
b. You implement the new database.
CO1 BT1
c. You describe the data needed at a very high level of
abstractio
d. You plan one or more database development projects.
26 Which is true for the following statement?: Ability to
modify schema of database in one level without affecting
the schema definition in higher level is called as .
a. Data Independence
CO1 BT1
b. Data Isolation
c. Data Migration
d. Data Abstraction

27 Logical Data independence is ability to modify

without causing application program to
rewrite.
a. Physical Schema
CO1
b. Logical Schema
c. Conceptual Schema
d. None of the above

28
If both data and database administration exist in an
organization, the database administrator is responsible
for which of the following?
CO1 BT1
(A) Data modeling
(B) Database design
(C) Meta data
(D) All the above
29 refers to the correctness and completeness of
the data in a database?
a. Data security
b. Data integrity CO1
c. Data constraint
d. Data independence
30 Which of the following is not Modification of the Database?
a. Deletion
b. Insertion
CO1 BT1
c. Sorting
d. Updating

31 An entity set that does not have sufficient attributes to

form a primary key is a
(A) strong entity set.
CO1
(B) weak entity set.
(C) simple entity set.
(D) primary entity set.
32 The language which has recently become the defacto
standard for interfacing application programs with
relational database system is
(A) Oracle. CO1
(B) SQL.
(C) DBase.
(D) 4GL.
33 A subschema expresses
(A) the logical view.
(B) the physical view. CO1
(C) the external view.
(D) all of the above.
34 Which one of the following statements is false?
(A) The data dictionary is normally maintained by the database
administrator.
(B) Data elements in the database can be modified by
changing the data dictionary. CO1
(C) The data dictionary contains the name and description of
each data element.
(D) A data dictionary is a tool used exclusively by the database
administrator.
35 Select the correct statement.

A. With the DDL commands, any structural changes

can be made to the table, including creation,
deletion, and alteration.
B. With the DML commands, any structural changes can
be made to the table, including creation, deletion, and
CO1
alteration.
C. With the DCL commands, any structural changes can
be made to the table, including creation, deletion, and
alteration.
D. With the TCL commands, any structural changes can
be made to the table, including creation, deletion, and
alteration.
36 The three language components of a database management
system (DBMS) like DDL, DCL, DML. Two different types of
people (users and practitioners) are concerned with them. Which
of them do users of a DB
CO1
A. DDL
B. DML
C. DDL And DCL
D. DCL And DML
37 Which of the following statement removes database including
its related components?
A. DROP DATABASE
CO1
B. DELETE DATABASE
C. REMOVE DATABASE
D. None of the mentioned
38 Which of the following statement removes Sales and suppliers
database?
A. DROP DATABASE Sales, NewSales;
CO1
B. DROP DATABASE Sales, suppliers;
C. DROP DATABASE Sales and suppliers;
D. DROP DATABASE Sales-suppliers;
39 . Which of the following statements are TRUE?
A. Integrity constraint can be added to a table even if table
data is in violation
B. A Unique constraint allows multiple rows to have
CO1
NULL value
C. A PRIMARY KEY allows a single row to
contain NULL
D. Both A and B
40 Which statement would add a column CGPA to a table Student
which is already created
A. ALTER TABLE Student ADD COLUMN (CGPA
NUMBER(3,1)); CO1
B. ALTER TABLE Student CGPA NUMBER(3,1);
C. ALTER TABLE Student ADD (CGPA NUMBER(3,1));
D. Both A and C
41 Which command allows the removal of all rows from a table but
flushes a table more efficiently since no rollback information is
retained:
A. TRUNCATE command CO1
B. Create command
C. Drop table command
D. Alter table command
42 Point out the correct statement.
A. When a database is dropped, the master database
should be backed up
B. You can drop a database currently being used CO1
C. Dropping a database snapshot does not delete the database
snapshot from an instance of SQL Server
D. None of the mentioned
43 Which of the following statements are False about DISTINCT
keyword?
A. DISTINCT removes duplicates based on all the columns
in the SELECT clause
B. DISTINCT keyword can be used in SELECT CO1
and WHERE clauses
C. Usage of DISTINCT should be avoided as far as possible due
to performance issues
D. None of the above
44 A type of query that is placed within a WHERE or HAVING
clause of another query is called
A. Master query
CO1
B. Sub query
C. Super query
D. Multi-query
45 Which of the following columns in a table cannot be updated?
A. DATE type columns in the table
B. Columns which allows NULL values in the table
CO1
C. A primary key column which also serves as foreign key
reference in another table
D. All of the above
46 Which one of these is used with SELECT clause to fetch all
columns from a table?
A. ALL
CO1
B. *
C. DISTINCT
D. AS
47 A data manipulation command the combines the records from
one or more tables is called
A. SELECT
CO1
B. PROJECT
C. JOIN
D. PRODUCT
48 Select the sequence for how the query mechanism works?
A. Authentication-> DDL->DML->query optimizer->output
B. DDL->DML->query optimizer-> Authentication->output CO1
C. DML->query optimizer-> Authentication-> DDL-> output
D. All of the mentioned
49 What is TRUE about SAVEPOINT?
A. Following the completion of a transaction, it must be
executed to save all the operations performed in the
transaction. CO1
B. A transaction can be rolled back to its last saved state.
C. A specific part of a transaction can be given a name
D. None of the above
50 Which one of the following commands is used to restore the
database to the last committed state?
A. Savepoint Remembe r
CO1
B. Rollback
C. Commit
D. Both A & B
PART B (4 Marks)
1 What are the disadvantages of file processing system? Remember
The disadvantages of file processing systems are
a) Data redundancy and inconsistency b) Difficulty in accessing data
c) Data isolation
d) Integrity problems
e) Atomicity problems
f) Concurrent access anomalies
2 List out the applications of DBMS. Remember
a) Banking
b) Airlines
c) Universities
d) Credit card transactions e) Tele communication
f) Finance g) Sales
h) Manufacturing
i) Human resources
3 What are the purposes of DBMS? Remembe r
The purpose of DBMS is to transform the following −
● Data into information.
● Information into knowledge.
● Knowledge to the action.
Uses of DBMS
The main uses of DBMS are as follows −
● Data independence and efficient access of data.
● Application Development time reduces.
● Security and data integrity.
● Uniform data administration.
● Concurrent access and recovery from crashes.
4 Compare instances and schemas of database? Understan
Instances : d
Instances are the collection of information stored at a particular
moment. The instances can be changed by certain CRUD operations
as like addition, deletion of data. It may be noted that any search
query will not make any kind of changes in the instances.
Example –
Let’s say a table teacher in our database whose name is School,
suppose the table has 50 records so the instance of the database has
50 records for now and tomorrow we are going to add another fifty
records so tomorrow the instance have total 100 records. This is
called an instance.
2. Schema :
Schema is the overall description of the database. The basic structure
of how the data will be stored in the database is called schema.
5 What are the various types of Databases? Remember
1) Centralized Database. It is the type of database that stores data at
a centralized database system. ...
2) Distributed Database. ...
3) Relational Database. ...
4) NoSQL Database. ...
5) Cloud Database. ...
6) Object-oriented Databases. ...
7) Hierarchical Databases. ...
8) Network Databases.
6 Explain the classification of data models? Understan d
1. Relational model
2. ER model
3. Object based data model
4. Semistructured data model
7 Outline the concept of Data Abstraction in DBMS Understan
Database systems are made-up of complex data structures. To ease d
the user interaction with database, the developers hide internal
irrelevant details from users. This process of hiding irrelevant
details from user is called data abstraction.
Three levels of abstraction:
Physical level: This is the lowest level of data abstraction. It
describes how data is actually stored in database. You can get
the complex data structure details at this level.
Logical level: This is the middle level of 3-level data abstraction
architecture. It describes what data is stored in database.
View level: Highest level of data abstraction. This level
describes the user interaction with database system.
8 Explain Data Independence in DBMS? Understan
d
Data Independence is defined as a property of DBMS that helps you
to change the Database schema at one level of a database system
without requiring to change the schema at the next higher level. Data
independence helps you to keep data separated from all programs
that make use of it.
You can use this stored data for computing and presentation.
In many systems, data independence is an essential function
for
components of the system.
Types of Data Independence
In DBMS there are two types of data independence
1. Physical data independence
2. Logical data independence.
9 What are the different components of DBMS? Remember
The term database system refers to an organization of components
that define and regulate the collection, storage, management, and use
of data within a database environment.
The five major components of database management system are
i. Hardware,
ii. Software,
iii. People,
iv. Procedures, and
v. Data.
10 What are the main tasks performed by DBA? Remember
The following are the functions of a Database administrator.
● Schema Definition
● Storage structure and access method definition
● Schema and physical organization modification.
● Granting authorization for data access.
● Routine Maintenance
11 Outline the Types of Database Users in DBMS Understan
d
1. Application Programmers – They are the developers who
interact with the database by means of DML queries. These
DML queries are written in the application programs like
C, C++, JAVA, Pascal, etc.
2. Sophisticated Users – They are database developers, who
write SQL queries to select/insert/delete/update data.
They do not use any application or programs to request
the database.
3. Specialized Users – These are also sophisticated users, but
they write special database application programs. They are
the developers who develop the complex programs to the
requirement.
4. Stand-alone Users – These users will have a stand-alone
database for their personal use. These kinds of the database
will have readymade database packages which will have
menus and graphical interfaces.
5. Native Users – these are the users who use the existing
application to interact with the database.
12 Summarize the Data Definition Language Understan
● DDL stands for Data Definition Language. It is used to d
define database structure or pattern.
● It is used to create schema, tables, indexes, constraints, etc. in
the database.
● Using the DDL statements, you can create the skeleton of
the database.
● Data definition language is used to store the information of
metadata like the number of tables and schemas, their
names, indexes, columns in each table, constraints, etc.
Here are some tasks that come under DDL:
● Create: It is used to create objects in the database.
● Alter: It is used to alter the structure of the database.
● Drop: It is used to delete objects from the database.
● Truncate: It is used to remove all records from a table.
● Rename: It is used to rename an object.
● Comment: It is used to comment on the data dictionary.
13 Explain about Data Manipulation Language Understand
DML stands for Data Manipulation Language. It is used for
accessing and manipulating data in a database. It handles user
requests.
Here are some tasks that come under DML:
● Select: It is used to retrieve data from a database.
● Insert: It is used to insert data into a table.
● Update: It is used to update existing data within a table.
● Delete: It is used to delete all records from a table.
● Merge: It performs UPSERT operation, i.e., insert or
update operations.
● Call: It is used to call a structured query language or a
Java subprogram.
● Explain Plan: It has the parameter of explaining data.
● Lock Table: It controls concurrency.
14 Outline about Data Control Language Understan
● DCL stands for Data Control Language. It is used to retrieve d
the stored or saved data.
● The DCL execution is transactional. It also has
rollback parameters.
(But in Oracle database, the execution of data control
language does not have the feature of rolling back.)
Here are some tasks that come under DCL:
● Grant: It is used to give user access privileges to a database.
● Revoke: It is used to take back permissions from the user.
There are the following operations which have the authorization of
Revoke:
CONNECT, INSERT, USAGE, EXECUTE, DELETE, UPDATE
and SELECT.
15 What is Transaction Control Language? Remember
TCL is used to run the changes made by the DML statement.
TCL can be grouped into a logical transaction.
Here are some tasks that come under TCL:
● Commit: It is used to save the transaction on the database.
● Rollback: It is used to restore the database to original since
the last Commit.
PART C (12 Marks)
1 Illustrate with neat sketch the views of data. Understan
d
2 How does DBMS provide data abstraction? Explain the concept of Remember
data independence.
3 Explain about the reasons brings you to choose the data base than Understan d
file system.
4 With a neat diagram show the overall system structure of DBMS. Understan
d
5 Explain Database Administrator’s responsibilities. Understan
d
6 What is Data modeling and explain different types of data Understan d
modeling in brief.
7 Outline DDL and DML commands. Understan
d
8 Draw and explain three-tier schema architecture of database Understan d
system.
9 Design any two database applications by describing their features. Create
10 Explain different languages that are supported to manage the data in Understan d
DBMS

UNIT-2
QUESTION
BANK

SUBJECT : Subject Code: 18CSC303J- Database Management Systems

SEM/YEAR: VI/III
Q. Course
Compete
Questions Outcom nceBT
No. e Level
A relational database consists of a collection of
A. Tables
B. Fields
C. Records
1 D. Keys CO2 BT1
A in a table represents a relationship among a set CO2
of values.

A. Column

2 B. Key BT2

C. Row

D. Entry
 The term attribute refers to a of a table. CO2

A. Record
B. Column
3 BT1
C. Tuple
D. Key

A is a set of entities of the same type that share the same CO2
properties, or attributes.

A. Entity set

B. Attribute set
4 BT1
C. Relation set

D. Entity model

The attribute name could be structured as an attribute consisting of CO2

first name, middle initial, and last name. This type of attribute is
called

A. Simple attribute
5 B. Composite attribute BT1

C. Multivalued attribute

D. Derived attribute

The attribute AGE is calculated from DATE_OF_BIRTH. The CO2

attribute AGE is

A. Single valued

6 B. Multi valued BT1

C. Composite

D. Derived
 In a relation between the entities the type and condition of the CO2
relation should be specified. That is called as attribute.

A. Descriptive

7 B. Derived BT2

C. Recursive

D. Relative

An entity in A is associated with at most one entity in B, and an CO2

entity in B is associated with at most one entity in A. This is called
as

A. One-to-many
8 BT3
B. One-to-one

C. Many-to-many

D. Many-to-one

An entity in A is associated with at most one entity in B. An entity CO2

in B, however, can be associated with any number

(zero or more) of entities in A.

A. One-to-many
9 BT1
B. One-to-one

C. Many-to-many

D. Many-to-one

Which of the following can be addressed by enforcing a referential CO2

integrity constraint?

A. All phone numbers must include the area code

B. Certain fields are required (such as the email address, or

phone number) before the record is accepted
10 BT1
C. Information on the customer must be known before anything
can be sold to that customer

D. Then entering an order quantity, the user must input a

number and not some text (i.e., 12 rather than ‘a
dozen’)
 is a special type of integrity constraint that relates two CO2
relations & maintains consistency across the relations.

A. Entity Integrity Constraints

11 B. Referential Integrity Constraints BT1

C. Domain Integrity Constraints

D. Domain Constraints

Which one of the following uniquely identifies the elements in the CO2
relation?

A. Secondary Key

12 B. Primary key BT2

C. Foreign key

D. Composite key

is preferred method for enforcing data integrity CO2

A. Constraints

B. Stored Procedure BT1

13
C. Triggers

D. Cursors

Let us consider phone - number, which can take single or several CO2
values. Treating phone - number as a permits instructors
to have several phone numbers (including zero) associated with
them.

A. Entity
14 BT1
B. Attribute

C. Relation

D. Value
 Given the basic ER and relational models, which of the following is CO2
INCORRECT?

A. An attribute of an entity can have more than one value

B. An attribute of an entity can be composite

15 BT1
C. In a row of a relational table, an attribute can have more
than one value

D. In a row of a relational table, an attribute can have exactly

one value or a NULL value

In E-R diagram generalization is represented by

A. Ellipse

B. Dashed ellipse
16 CO2 BT1
C. Rectangle

D. Triangle

What is a relationship called when it is maintained between two

entities?

A. Unary
17 CO2 BT1
B. Binary
C. Ternary
D. Quaternary
A primary key is combined with a foreign key creates CO2

A. Parent-Child relationship between the tables that

connect them

B. Many to many relationship between the tables that

18 BT1
connect them

C. Network model between the tables that connect them

D. An attribute of an entity can be composite

 There are similarities between the instructor entity set and the CO2
secretary entity set in the sense that they have several attributes that
are conceptually the same across the two entity sets: namely, the
identifier, name, and salary attributes. This process is called

A. Commonality
19 BT1
B. Specialization

C. Generalization

D. Similarity

Consider the employee work-team example, and assume that CO2

certain employees participate in more than one work team. A given
employee may therefore appear in more than one of the team entity
sets that are lower level entity sets of employee. Thus, the
generalization is

A. Overlapping
20 BT1
B. Disjointness

C. Uniqueness

D. Relational

The completeness constraint may be one of the following: Total

generalization or specialization, Partial generalization or
specialization. Which is the default?

A. Total

21 B. Partial CO2 BT1

C. Should be specified

D. cannot be determined
 The operation allows the combining of two relations by CO2
merging pairs of tuples, one from each relation, into a single
tuple.

A. Select
22 BT1
B. Join

C. Union

D. Intersection
The operation performs a set union of two “similarly CO2
structured” tables

A. Union

23 B. Join BT1

C. Product

D. Intersect

Which of the following is another name for a weak entity? CO2

A. Child

B. Owner
24 BT1
C. Dominant

D. Parent

The subset of a super key is a candidate key under what condition? CO2

A. No proper subset is a super key

B. All subsets are super keys BT1

25
C. Subset is a super key

D. Each subset is a super key

 In a relational model, relations are termed as

A. Tuples.
26 B. Attributes CO2 BT1
C. Tables.
D. Rows.
In an E-R diagram an entity set is represent by a

A. Rectangle.
B. Ellipse.
27 CO2 BT1
C. Diamond box.
D. Circle.

The relational model feature is that there

A. Is no need for primary key data.

B. Is much more data independence than some other database
models.
28 C. Are explicit relationships among records. CO2 BT1

D. Are tables with many dimensions.

What is a relationship called when it is maintained between two

entities?

A. Unary
B. Binary
29 CO2 BT1
C. Ternary
D. Quaternary

Degree of ternary relationship is

A. 2
30 CO2 BT1
B. 4
C. 5
D. 3
 Let us consider first name, middle initial, and last name as a
subparts of name attribute, then Name is a
attribute

31 A. Simple CO2 BT1

B. Composite
C. Single
D. Derived

An entity set which does not have any key attributes is called as a

A. Strong entity set

32 B. weak entity set CO2 BT1
C. partially Strong entity set
D. partially weak entity set

The relationship may also be referred to as

a superclass-subclass relationship.

A. Generalization
33 B. Partialization CO2 BT1
C. Specialization
D. Aggregation

Consider a relation student course details with attributes rno,

sname, semester, subjects and rno. be the primary key subjects
in this relation can be attribute

34 A. Single CO2 BT1

B. Simple
C. Composite
D. Multivalued

Which of the following is not valid in case of ER diagram creation.

A. Identify all the entities in the system.

B. Identify relationships between entities
35 CO2 BT1
C. Add attributes for entities
D. Remove attributes for entities
 relationship involves the same entity type more
than once

A. Recursive
B. Many to many
36 C. One to many CO2 BT3
D. Many to one

key identifies the tuples uniquely in

relation.

A. Primary key
37 B. Super key CO2 BT3
C. Candidate key
D. None of these

Relations obtained from an E-R model will always be in

normal form

A. Fourth
B. Third
38 CO2 BT2
C. BCNF
D. First

E-R modeling technique is a:

A. Top-down approach
39 B. Bottom-up approach CO2 BT2
C. Left-right approach
D. Right- Left approach

is a bottom-up approach in which two lower level

entities combine to form a higher level entity.

A. Specialization
40 B. Aggregation CO2 BT2
C. Initialisation
D. Generalization
 key is a candidate key not used for primary key

A. Alternate
41 B. Foreign CO2 BT2
C. Super
D. Unique

Which of the following commands is used to get all the columns in

a table?

42 A. # CO2 BT3
B. *
C. %
D. @
The set of permitted values for each attribute is called its :

A. Attribute set
B. Attribute range
43 CO2 BT3
C. Domain
D. Group

key provides the basic tuple-level addressing

mechanism in a relational System

A. Unique
44 B. Alternative CO2 BT2
C. Candidate
D. Primary

The of Primary keys of the related entity

sets becomes a Super Key of the relation.

A. Union
45 B. Intersection CO2 BT2
C. Minus
D. Aggregation
 Which of the following is not a set operation?

A. Union

B. Intersection

46 C. Minus CO2 BT3

D. Aggregation

Which of the following is not an aggregation operation?

A. Max

B. Min
47 CO2 BT3
C. Minus

D. Sum

In E-R diagram generalization is represented by

A. Ellipse
B. Dashed ellipse
48 C. Rectangle
D. Triangle

CO2 BT2
In an E-R, Y is the dominant entity and X is a subordinate entity.
Then which of the following is incorrect?

A. operationally, If Y is deleted, so is X
49 B. X existence is dependent on Y CO2 BT2
C. operationally, if X is deleted, so is Y
D. operationally, if X is deleted, Y remains the same

----------denote derived attributes.

A. Double ellipse
50 B. Dashed ellipse CO2 BT3
C. Squared ellipse
D. Ellipse with attribute name underlined
PART B (4 Marks)
 What is the difference between a database schema and a database state?

The following points explain the main differences between database and
schema:

● The fundamental difference between them is that the database is an

organized collection of interrelated data or information about the
considered object. In contrast, the schema is a logical
representation or description of an entire database.

● Once we declare a database schema, we must not change it often as

it would disturb the organization of data stored in the database. In
contrast, we can update the database regularly.

● The database is a collection of schema, records, and constraints for

the tables. On the other hand, a schema contains the structure of
tables, attributes, their types, constraints, and how they relate to
other tables.

● The DDL statement is used to generate and modify the schema. On

1. the other hand, DML statements are used to create and modify the CO2 BT1
data inside the database.

● Each database uses the memory to store the data, whereas the
schema can use a logical structure to store data.
 What is an entity type? What is an entity set? Explain the differences CO2
among an entity, an entity type, and an entity set.

The entity type refers to the collection of entity that share a common
definition.

Entity set is the set of entities of the same type that share the same
attributes.

E.g. Set of all people who are customer at a particular bank can be
defined as the entity customer, while, Entity set of entities of the
same entity type.
2 BT1
Entity: It is something which has real existence.

Entity Type: It is collection of entity having common attribute. Also

each entity type in a database is described by a name and a list of
attribute. So we may say a table is an entity type

Entity SET: It is a set of entities of same entity type. So a set of one

or more entities of an Entity type is an Entity Set.
 When is the concept of a weak entity used in data modeling? Define CO2
the terms owner entity type, weak entity type, identifying relationship
type, and partial key.

Concept of weak entity types is used in data modeling when we want

to specify a new entity type similar to the one we already have, but
we want this new entity type to have specific attributes and to be able
to participate in specific relationships.

If the newly made entity type doesn't have key attributes, it is called
weak entity type and it needs to be related to a strong entity type
(directly or indirectly) that has: key attributes of its own.

Owner (or identifying) entity type is a title for entity type that
identifies weak entity types related to it. Weak entity types include
3 one of their attributes when combining with owner entity type. BT1

Weak entity type is an entity type that does not have key attributes of
its own.

Identifying relationship type is a title for relationship type that relates

one weaker entity type to one owner entity type. In this relationship
type, weak entity has a total participation constraint, meaning it
cannot exist without its owner entity type that identifies it.

Partial key (or discriminator) is an attribute or set of attributes of a

weak entity type that distinguishes weak entities related to the same
owner entity type.

Can an identifying relationship of a weak entity type be of a degree CO2

greater than two? Give examples to illustrate your answer.

An identifying relationship of a weak entity type can be of a degree

4 BT2
greater than two. An example of this would be the ternary
relationship of Supply. Supply is a weak entity type that has three
identifying relationships.

Draw an ER schema diagram for the COMPANY database. CO2

5 BT1
 Describe the two alternatives for specifying structural constraints CO2
on relationship types. What are the advantages and disadvantages
of each?

The two alternatives for specifying structural constraints on

relationship types are :

● Cardinality ratio and

● Participation constraint

Cardinality ratio

Cardinality ratio is a simple way of specifying designs, it is

specifies the maximum number of relationship a body/entity can
be engaged in.

Advantage of Cardinality ratio

It is used to simply complex designs

Disadvantage of cardinality ratio

No specific number can be said to be maximum

6 BT2
Participation constraint

Participation constraint specifies the dependency of an entity

on another entity based on the relationships existing between the
entities

Advantage of participation constraint

It specifies the minimum number of relationship an entity can

engage

It shows the dependency of an entity on another

Disadvantage

It is a more expensive alternative

Hence we can conclude that The two alternatives for specifying

structural constraints on relationship types are : Cardinality ratio
and

and Participation constraint

 CO2
Discuss the two main types of constraints on specializations and
generalizations.

Specialization: constraints are generally used to provide limitations

7 on data which can be used in table for database. Not null, unique are BT2
two constraints used in specialization.
Generalization: it is a bottom up approach and in this constraints are
used to create limitations in data. Check and default are two
constraints used to limit data in generalization.
What is the difference between specialization and generalization?
Why do we not display this difference in schema diagrams?

Generalization is process of extracting common characteristics from

two or more classes and combining them into a generalized
8 superclass. CO2 BT1
A Specialization is the reverse process of Generalization means
creating new sub classes from an existing class.

Design a database to keep track of information for an art museum.

Assume that the following requirements were collected:
■ The museum has a collection of ART_OBJECTS. Each
ART_OBJECT has a unique Id no, an Artist (if known), a Year
(when it was created, if known), a Title, and a Description. The art
objects are categorized in several ways, as discussed below.
9 CO2 BT1
■ ART_OBJECTS are categorized based on their type. There are
three main types: PAINTING, SCULPTURE, and STATUE, plus
another type called OTHER to accommodate objects that do not fall
into one of the three main types.
 Discuss about Entity Types, Entity Sets, Keys, and Value Sets with
examples.

The entity type is a collection of the entity having similar attributes.

In the above Student table example, we have each row as an entity
and they are having common attributes i.e. each row has its own
value for attributes Roll no, Age, Student name and Mobile no. So,
we can define the above STUDENT table as an entity type because it
is a collection of entities having the same attributes. So, an entity
type in an ER diagram is defined by a name (here, STUDENT) and a
set of attributes (here, roll no, Student name, Age, Mobile no).
Types of Entity type

● Strong Entity Type

● Weak Entity Type
Strong Entity Type: Strong entity are those entity types which has a
key attribute. The primary key helps in identifying each entity
uniquely. It is represented by a rectangle.

Weak Entity Type: Weak entity type doesn't have a key attribute.
Weak entity type can't be identified on its own. It depends upon some
10 other strong entity for its distinct identity. CO2 BT2

Entity Set is a collection of entities of the same entity type. In the

above example of STUDENT entity type, a collection of entities
from the Student entity type would form an entity set. We can say
that entity type is a superset of the entity set as all the entities are
included in the entity type.

A key could either be a combination of more than one attribute (or

columns) or just a single attribute. The main motive of this is to give
each record a unique identity.

There are broadly seven types of keys in DBMS:

1. Primary Key
2. Candidate Key
3. Super Key
4. Foreign Key
5. Composite Key
6. Alternate Key
7. Unique Key
 What are the Constraints on Binary Relationship Types?

The participation constraint specifies whether the existence of an

entity depends on its being related to another entity via the
relationship type. This constraint specifies the minimum number of
relationship instances that each entity can participate in, and is
sometimes called the minimum cardinality constraint. There are two
types of participation constraints—total and partial—that we
illustrate by example. If a company policy states that every employee
must work for a department, then an employee entity can exist only if
it participates in at least one WORKS_FOR relationship instance.
11 Thus, the participation of EMPLOYEE in WORKS_FOR is called CO2 BT2
total participation, meaning that every entity in the total set of
employee entities must be related to a department entity via
WORKS_FOR. Total participation is also called existence
dependency. We do not expect every employee to manage a
department, so the participation of EMPLOYEE in the MANAGES
relationship type is partial, meaning that some or part of the set of
employee entities are related to some department entity via
MANAGES, but not necessarily all.
 Define Weak entity and its types.

● A weak entity is an entity set that does not have sufficient

attributes for Unique Identification of its records.
Example 1 – A loan entity cannot be created for a customer if the
customer doesn’t exist
Example 2 – A dependents list entity cannot be created if the
employee doesn’t exist
● Simply a weak entity is nothing but an entity that does not
have a primary key attribute
● It contains a partial key called a discriminator which helps
in identifying a group of entities from the entity set BT2
12 CO2
● A discriminator is represented by underlining with a dashed
line
Representation
●
o A double rectangle is used for representing a weak
entity set
o The double diamond symbol is used for representing
the relationship between a strong entity and a weak
entity which is known as identifying relationship
 Give a Summary of the notation for ER diagrams with neat diagram
representation

13 CO2 BT2
 Explain about the Constraints on Ternary (or Higher-Degree)
Relationships.
There are two notations for specifying structural constraints on n-ary
relationships, and they specify different constraints. They should
thus both be used if it is important to fully specify the structural
constraints on a ternary or higher-degree relationship. The first
notation is based on the cardinality ratio notation of binary
relationships. The second notation is based on the (min, max)
14 notation. A (min, max) on a participation here specifies that each CO2 BT2
entity is related to at least min and at most max relationship
instances in the relation-ship set. These constraints have no bearing
on determining the key of an n-ary relationship, where n > 2, but
specify a different type of constraint that places restrictions on how
many relationship instances each entity can participate in.

Explain the difference between an attribute and a value set.

The difference between an attribute and a value set is:

An attribute holds values, while a value set are those things which are
contained in a class.
An attribute is the name that is given to the property of a class. On
the other hand, a value set are those things that are used to represent
an object in a given class.
For example, an attribute can have the name "Apples" and can hold
15 the value of "abc" while the value set are those things that can CO2 BT2
be attributed to a class.
An attribute is a characteristic.
For example, Attributes in the invoice can be price, number, date etc.
A value set specifies the set of values that may be assigned to that
attribute for each individual entry.

PART C (12 Marks)

Discuss the role of a high-level data model in the database design CO2
1 process. BT2

Define the following terms: entity, attribute, attribute value, CO2

relationship instance, composite attribute, multivalued attribute,
2 derived attribute, complex attribute, key attribute, and value set BT2
(domain).
 When is the concept of a weak entity used in data modeling? Define CO2
the terms owner entity type, weak entity type, identifying relationship
3 BT2
type, and partial key.

A database is being constructed to keep track of the teams and games CO2
of a sports league. A team has a number of players, not all of whom
participate in each game. It is desired to keep track of the players
participating in each game for each team, the positions they played in
4 BT2
that game, and the result of the game. Design an ER schema diagram
for this application, stating any assumptions you make. Choose your
favorite sport (e.g., soccer, baseball, football).

Discuss the main categories of data models. What are the basic
differences
5 between the relational model, the object model, and the XML CO2 BT2
model?

Discuss the naming conventions used for ER schema diagrams.

6 CO2 BT2

Explain in detail about the Conversion of ER to Relational Table

7 with CO2 BT2
relevant examples.
Discuss in detail about Key Constraints and Constraints on NULL
8 Values with examples. CO2 BT2

UNIT-3
QUESTION BANK

SUBJECT : Subject Code: 18CSC303J- Database Management Systems

SEM/YEAR: VI/III

Q.No. Questions Course Competence

Outcome BT Level

1 Full form of DDL is – CO3 BT1

A. Data Describe Language

B. Definition Data Language
C. Data Definition Language
D. Data Distinct Language
2 Commands that comes under DDL is/are – CO3 BT2

A. CREATE
B. DROP
C. TRUNCATE
D. Al of the above

3 Full form of DML is – CO3 BT1

A. Data Multiplication Language

B. Data MaLanguagenipulation
C. Data Modify Language
D. Data Mapping Language

4 Command that comes under DML is/are – CO3 BT1

A. ROLLBACK
B. GRANT
C. UPDATE
D. Al of the above
5 Select the correct statement. CO3 BT1

A. With the DDL commands, any structural changes can

be made to the table, including creation, deletion,
and alteration.
B. With the DML commands, any structural changes can
be made to the table, including creation, deletion,
and alteration.
C. With the DCL commands, any structural changes can
be made to the table, including creation, deletion,
and alteration.
D. With the TCL commands, any structural changes can
6 be made to the table, including creation, deletion, BT1
and alteration.

Full form of DCL is - CO3

A. Data Control Language

B. Data Commit Language
C. Data Common Language
D. Data Concatenate Language

7 Full form of TCL is - CO3 BT2

A. Transaction Common Language

B. Transaction Commit Language
C. Transaction Concatenate Language
D. Transaction Control Language

8 What is TRUE about SAVEPOINT? CO3 BT3

A. Following the completion of a transaction, it must

be executed to save al the operations performed
in the transaction.
B. A transaction can be rolled back to its last saved state.
C. A specific part of a transaction can be given a name
D. None of the above
9 Following the completion of a transaction, it must be CO3 BT1
executed to save all the operations performed in the
transaction. Here we are talking about which
command?

A. REVOKE
B. COMMIT
C. ROLLBACK
D. SAVE

CO3
10 Difference between GRAND & REVOKE command is/are? BT1

A. The GRANT command can be used to grant a user

accessto databases and tables whereas The
REVOKE commandcan be used to revoke al
access privileges already assigned to the user.
B. The REVOKE command can be used to grant a
user access to databases and tables whereas The
GRANT command can be used to revoke al access
privileges already assigned to the user.
C. A transaction can be rolled back to its last saved state.
D. None of the above
11 The table records can be retrieved using which command? CO3 BT1

A. RETRIEVE
B. SELECT
C. CREATE
D. ALTER

12 Which command will remove the records from the table, but CO3 BT2
not affect the structure of the table?

A. REMOVE
B. DELETE
C. DROP
D. TRUNCATE

13 The records and structure of a table may be CO3 BT1

removed or deleted from the database using which
 command?
A. REMOVE
B. DELETE
C. DROP
D. TRUNCATE

14 BT1
Select the correct statement. CO3

A. DDL consist of 4 commands

B. DCL consist of 2 commands
C. TCL consist of 5 commands
D. DML consist of 3 commands

15 Which of the following is TRUE about TCL? CO3 BT1

A. Transactions can be saved to the database and

rolled backwith the help of TCL commands in
SQL.
B. There wil be certain privileges that each user has;
consequently, the data can be accessed by them
using TCL.
C. Our data is stored in a table that is described by
the schema, thus TCL commands deal with the
schema.

D. SQL TCL commands can be used to perform any kind

of retrieval or manipulation of the data present in SQL
tables.

16 In how many parts are the SQL functions are divided into? CO1 BT1

A. 1
B. 2
C. 3
D. 4

17 value is returned by the SQL Aggregate functions? CO1 BT1

 A. Single
B. Twice CO3
C. NULL
D. Infinite
18 BT1

What does AVG() function returns?

A. First value of the column

CO3
B. Last value of the column
C. Sum of rows of the table
D. Average value of the column
19 BT1

Which function returns the largest value of the column?

A. MIN()
B. MAX()
C. LARGE()
D. AVG()
20 By constraining a SQL statement, we limit the CO3 BT1
according to certain conditions
or restrictions.

A. Row
B. Column
C. Table
D. Database
2 What is TRUE about NOT NULL Constraint? CO3 BT1
1
A. In columns that are subject to the NOT NULL
constraint, duplicate values are not alowed.
B. When a table's column is declared as NOT NULL, no
record in the table can have an empty value for that
column.
C. By applying the NOT NULL constraint, we wil always
ensure that the column contains a unique value and won't
alow nuls.
D. The value wil first be checked for certain conditions
before inserting it into the column when a NOT
NULL constraint applies to a column in the table.

22 You can also the existing tables by CO3 BT1

using the UNIQUE constraint.

A. Change
B. Delete
C. Modify
D. Drop

2 A Sub query is an SQL expression that is CO3 BT1

3 placed another SQL statement.

A. Before
B. After
C. Inside
D. Outside

24 Which of the following clause cannot be used in SQL sub CO3 BT1
queries?

A. GROUP BY
B. ORDER BY
C. DELETE
D. FROM
25 In order to prevent multiple records from being CO3 BT1
returned bythe sub query, must be used before
the sub query.

A. Many Value Operators

B. Multiple Value Operators
C. Single Value Operator
D. Unique Value Operator

26 Which of the following exception is globaly available? CO1 BT1

A. Internal, User-defined and Pre-defined exceptions

B. Pre-defined exceptions only
C. Internal and pre-defined exceptions
D. User defined exceptions only

27 Where are exceptions used in PL/SQL? CO1 BT1

A. Only in an anonymous block

B. Only in the body of a subprogram
C. Only in a package
D. Only in an anonymous block and the body
of a subprogram

28 Which of the following function gives the error code of CO1 BT1
the recently occurred exception?

A. SQLERRCODE
B. SQLERROR
C. ERRCODE
D. SQLCODE

29 Can the PL/SQL block process more than one exception at a CO1 BT1
time?

A. Yes
B. No
C. Depends upon
30 What is the output for SELECT SAL INTO V_SAL FROM CO1 BT1
EMP;

A. Al rows selected
B. First record only printed
C. Error as “exact fetch returns more than requested
number of rows”
D. Al columns selected

3 Point out the correct statement. CO1 BT1

1 A) Triggers are database object
B) Three types of triggers are present in SQL Server C) A
DDL trigger is an action programmed to execute when a data
manipulation language (DML) event occurs in the databaseserver
D) ) Two types of triggers are present in SQL Server

32 How many types of triggers are present in SQL Server? CO1 BT1
a) 4
b) 5
c) 8
d) 9
3 AFTER trigger in SQL Server can be applied to CO1 BT1
3
a) Table
b) Views
c) Table and Views
d) Function

34 Which of the following is not a limitation of view? a) CO1 BT1

ORDER BY Does Not Work
b) Index Created on View Used Often
c) Cross Database Queries Not Alowed in Indexed View
d) Adding Column is Expensive by Joining Table Outside
View
35 Point out the wrong statement. CO1 BT1
a) We can have an INSTEAD OF insert/update/delete
trigger on a table that successfuly executed
b) DML Triggers are used to evaluate data after data
manipulation using DML statements
c) INSTEAD OF triggers cause their source DML operation to
skip
d) AFTER triggers cause their source DML operation to skip
36 SQL Server alows for Transact-SQL stored procedures, CO3 BT3
triggers, and batches to refer to tables that don't exist at compile
time. This ability is caled?

A. Indeferred Name Resolution

B. Deferred Name Permissions
C. Deferred Name Resolution
D. Indeferred Name Permissions

37 Temporary stored procedures are stored in CO3 BT3

database.
a) Master
b) Model
c) User specific
d) Tempdb
What command use to see the errors from a recently created
stored procedure?
38 CO4 BT2
A. SHOW MISTAKES;
B. DISPLAY MISTAKES;
C. DISPLAY ERRORS;
D. SHOW ERRORS;

In the PL/SQL, the package specification contains

39 .......................declarations. CO4 BT2

A) Public

B) Private

C) Friend

D) Protected
40 ......................contain a pointer that keeps track of current row CO3 BT2
being accessed, which enables your program to process the
rows at a time.

A) Tracker

B) Cursor

C) Accesser

D) Trigger

4 ................provide a way for your program to select multiple CO4 BT2

1 rows of data from the database and then process each row
individual y.

A) PL/SQL Cursors

B) PL/SQL Trigger

C) PL/SQL Select

D) PL/SQL Process

42 Which option in view is to ensure that al UPDATE and CO3 BT3

INSERTs satisfy the condition(s) in the view definition?

A. Uncheck
B. With Check
C. Check
D. With
43 CO3 BT3
views help to keep the database up-to-date.

A. View materialization
B. View isolation
C. View updating
44 D. View maintenance CO4 BT2

Temporary stored procedures are stored in

database. a) Master
b) Model
c) User specific
d) Tempdb
45 Which of the following exception raised when an arithmetic, CO4 BT2
conversion, truncation, or size constraint error occurs?

A. ZERO_DIVIDE
B. VALUE_ERROR
C. TOO_MANY_ROWS
D. SELF_IS_NULL

46 Which statements can be checked for handling errors? CO4 BT3

A. DDL
B. TCL
C. DML
D. TTL

47 When creating a function, in which section wil you typicaly CO4 BT3
find a return key word?

A. Header Only
B. Declarative
C. Executable and Header
D. Executable and exception handling

48 Exception handling is possible in SQL Server using CO3 BT2

A. FINAL
B. FINALLY
C. THROW
D. THROWS

49 The variables in the triggers are declared using CO3 BT2

a) –
b) @
c) /
d) /@
50 Triggers enabled or disabled CO4 BT3
a) Can be
b) Cannot be
c) Ought to be
d) Always

PART B (4 Marks)
1 What is SQL? BT1
SQL is a database language designed for the retrieval and management of data
in a relational database.
CO3
SQL is the standard language for database management. All the RDBMS
systems like MySQL, MS Access, Oracle, Sybase, Postgres, and SQL Server
use SQL as their standard database language. SQL programming language uses
various commands for different operations. We will learn about the like DCL,
TCL, DQL, DDL and DMLcommands in SQLwith examples.

2 CO3 BT1
What is DDL?

Data Definition Language helps you to define the database structure or

schema. Let’s learn about DDL commands with syntax.

CREATE

DROP

ALTER

TRUNCATE:
3 BT1

CO3
What is Data Manipulation Language?

Data Manipulation Language (DML) allows you to modify the database

instance by inserting, modifying, and deleting its data. It is responsible for
performing all types of data modification in a database.

There are three basic constructs which allow database program and user to
enter data and information are:

Here are some important DML commands in SQL:

● INSERT
● UPDATE
● DELETE
4 CO3 BT2
What is DCL?

DCL (Data Control Language) includes commands like GRANT and REVOKE,
which are useful to give “rights & permissions.” Other permission controls
parameters of the database system.

Examples of DCL commands:

Commands that come under DCL:

● Grant
● Revoke

5 CO3 BT1
What is DQL?

Data Query Language (DQL) is used to fetch the data fromthe database. It
uses only one command:
6 CO3 BT2
What are SQL Constraints?
SQL Constraints are used to specify the rules for the data in a table.
These are used to limit which type of data must be stored in the
database, and aims to increase the accuracy and reliability of the
data stored in the database.

So, constraints make sure that there is no violation in terms of a

transaction of the data, yet there is any violation found; the action
gets terminated.

There are two types of constraints which can be applied:

1. Column-level constraints – These constraints are applied to

a single column
2. Table-level constraints – These constraints are
the application to the complete table

7 BT2
List out Aggregate functions:
CO3
These functions are used to do operations from the values of the
column and a single value is returned.

1. AVG()
2. COUNT()
3. FIRST()
4. LAST()
5. MAX()
6. MIN()

7. SUM()
8 List out Scalar functions: BT2
CO3
These functions are based on user input; these too returns single
value.

1. UCASE ()
2. LCASE ()
3. MID ()
4. LEN ()
5. ROUND ()
6. NOW ()
7. FORMAT ()

9 Write about trigger. BT1

A trigger is a special type of stored procedure that
automatically runs when an event occurs in the database
server. DML triggers run when a user tries to modify data through a
data
CO1
manipulation language (DML) event.
DML events are INSERT, UPDATE, or DELETE statements on a table
or view.
Write an example for nested queries.
A nested query is a query that has another query embedded within it.
The embedded query is caled a subquery.
10 BT1
A subquery typical y appears within the WHERE clause of a query.
It can sometimes appear in the FROM clause or HAVING clause.
Let’s learn about nested queries with the help of an example. CO1
Find the names of employee who have regno=103
The query is as follows −

select E.ename from employee E where E.eid IN (select S.eid from

salary S where S.regno=103);

11 what are the transaction control language commands? BT2

● COMMIT. This command is used to make a
transaction permanent in a database.
CO1
● ROLLBACK.
● SAVEPOINT.

PART C (12 Marks)

 1 Explain in detail in structure creation with example. CO3 BT2

2 Explain in detail about constraints. CO3 BT2

3 Discuss in details about functions. CO3 BT2

4 Explain in detail about Sub Queries, correlated sub queries CO3 BT2

5 Explain in detail about Nested Queries, Views and its Types CO3 BT2

6 Explain Query processing with neat diagram CO1 BT2

UNIT-4
QUESTION BANK

Subject Code/Subject Name: 18CSC303J- Database Management Systems

SEM/YEAR: VI/III

Course Competence
Q.No. Questions Outcome BT Level
A __________expression forms a new relation after applying a
number of algebraic operators to an existing set of relations

A. relational expression CO5 BT1

1
B. relational algebra
C. relational calculus
D. relational query

__________ is used to change the values of some attributes in

existing tuples.

A. Update BT1
2
B. Drop CO5
C. Truncate
D. Select
 Which can be violated if a key value in the new tuple t already
exists in another tuple in the relation r(R).

A. Domain constraints BT1

3
B. Key constraints CO5
C. Integrity constraints
D. Rule constraints

The relational algebra is a __________ Query language.

A. Structured
4 B. Logical CO5 BT1
C. Procedural
D. Relational

The relational calculus is considered to be __________________.

A. a nonprocedural language
5 B. a procedural language CO5 BT1
C. a structured language
D. a unstructured language

Which of the following can be violated by delete operation.

A. primary key
6 B. referential integrity CO5 BT1
C. alternate key
D. super key

Relationship can be created between

A. two tables only

7 B. one table only CO5 BT1
C. two or more tables
D. none of the above

The value of the atom which evaluates either condition is TRUE or

FALSE for particular combination of tuples is classified as

A. Intersection Value
8 BT1
B. Union Value CO5
C. Deny Value
D. Truth Value
 _____________can be violated if the value of any foreign key in t
refers to a tuple that does not exist in the referenced relation.

A. Super Key
9 CO5 BT1
B. Referential integrity
C. Primary Key
D. Candidate Key

The relational calculus is important for two reasons. Which of the

following is true?

A. It has a firm basis in mathematical logic and the SQL for

RDBMSs has some of its foundations in the tuple relational
calculus. CO5
10 BT1
B. It is in relational algebra and the values of some attributes are
in existing tuples.
C. It is a tuple relational calculus expression and It satisfy
Functional dependency.
D. None of the above

A functional dependency X → Y is _____________if removal of CO5

any attribute A from X means that the dependency does not hold
any more.

11 A. a partial functional dependency BT2

B. a multivalued functional dependency
C. a full functional dependency
D. a transitive dependency

____________ can be violated if an attribute value is given that CO5

does not appear in the corresponding domain.

12 A. Domain constraints BT1

B. Referential integrity constraints
C. Key constraints
D. Check constraints

A tuple relational calculus expression may generate a/an CO5

A. Finite Relation
13 B. Infinite Relation BT1
C. Invalid Relation
D. Composite Relation
 Which of the following statements about normal forms is FALSE? CO5

A. BCNF is stricter than 3NF

B. Lossless,dependency-preserving decomposition into 3NF is
14 always possible. BT1
C. Lossless,dependemcy-preserving decomposition into BNF is
always possible.
D. Any relation with two attributes is in BCNF.

Third normal form is based on the concept of__________. CO5

A. transitive dependency
15 B. partial dependency BT1
C. multivalued dependency
D. full functional dependency

The only attribute values permitted by 1NF are

___________values.

A. Divisible
16 CO5 BT1
B. Single atomic
C. Multiple
D. Numeric

A relational query language L is considered relationally complete

if we can express in L any query that can be expressed
in__________.

17 A. Relational Algebra CO5 BT1

B. Structured Language
C. Relational calculus
D. Logical Language

Anomalies are avoided by splitting the offending relation into

multiple relations,is also known as_______.

A. Accupressure
18 BT1
B. Decomposition CO5
C. Precomposition
D. Both decomposition & precomposition
E.
 Consider the relation(ABCDEF)
FDs:
AFC
CD
BE
Find the 3NF relations:
19 BT3
CO5
A. ACDF,BE,AB
B. ACDF,BE,AB,CD
C. CD,ACF,BE,AB
D. ACF,CDF,AB,BE

The tuple relational calculus is based on specifying a number of

__________.

A. String Variables
20 BT1
B. Column Variables. CO5
C. Relation Variables
D. Tuple Variables.

Consider the relation (ABCDEF)

AFC
CD
BE
Find the 2NF relations
21 CO5 BT3
A. ACDF,AE
B. ACDF,BE,AB
C. BE,AB
D. ACD,BE,AB

The relation X(ABCDEF) with functional dependency set

F={ABCD,CCA,BE,DB,EF}. The number of candidate
keys of a relation R is ______.

22 A. 3 CO5 BT3
B. 4
C. 2
D. 5

First normal form disallows ___________.

A. indivisible values
23 B. divisible values CO5 BT1
C. single atomic values
D. multivalued attributes
 In a functional dependency X --> Y, if Y is functionally dependent
on X, but not on X's proper subsets, then we would call the
functional dependency as

24 A. Full Functional Dependency CO5 BT2

B. Partial Functional Dependency
C. Multivalued Functional Dependency
D. None of the above

A functional dependency X → Y is a ___________if some attribute

A ε X can be removed from X and the dependency still holds.

A. Partial Dependency
25 BT2
B. Multivalued Dependency CO5
C. Transitive Dependency
D. Full Functional Dependency.

An Multi Valued Dependency X →→ Y in R is called a _________

if (a) Y is a subset of X, or (b) X ∪ Y = R.

26 A. Total Multi Valued Dependency CO5 BT2

B. Trivial Multi Valued Dependency
C. Non-trivial Multi Valued Dependency
D. Partial Multi Valued Dependency
Consider a Relation R(ABCDE) with Functional Dependency
ABCDE,BCADE,DE
The Decomposition of R in 3NF will be

27 A. R1(ABCE) and R2(DE) CO5 BT3

B. R1(ADE) and R2(BC)
C. R1(ABDE) and R2(BDE)
D. R1(ABCD) and R2(DE)

A large number of commercial applications running against

relational databases is called as _____________________

A. Data Control Language

28 CO5 BT1
B. Structured Query Language
C. Online Transaction Processing
D. MongoDataBase
 A simple tuple relational calculus query is of the form:

A. {p | COND(t)}
29 B. {t | COND(t)} CO5 BT1
C. {p | COND(p)}
D. {p| P(t)}

Which of the following is the result of bad database design?

A. Repetition of Information
30 B. Inability to represent some information CO5 BT1
C. Inconsistent database state due to some transaction
D. All of the above

Third normal form (3NF) is based on the concept of

______________.

A. Partial Dependency
31 CO5 BT1
B. Multivalued Dependency
C. Transitive Dependency
D. Join Dependency

Second normal form (2NF) is based on the concept of

A. Partial Dependency
32 B. Multivalued Dependency CO5 BT1
C. Transitive Dependency
D. Full Functional Dependency.

An attribute is called _________,if it is not a member of any

candidate key.

A. Prime
33 CO5 BT1
B. Non-prime
C. Composite
D. Derived

Consider F1 and F2 as two sets of functional dependencies. If

every functional dependency in F2 can be inferred from the
functional dependencies of F1 using inference rules, then F1 is
_________ of F2.
34 CO5 BT2
A. Cover Set
B. Closure Set
C. Minimal Set
D. None of the above
 Two special symbols called quantifiers can appear in formulas;
they are :

A. universal quantifier (∀) and the existential quantifier (∃).

35 CO5 BT2
B. universal quantifier (A) and the existential quantifier (E).
C. conditional quantifier (∀A) and the generalized quantifier (∃A).
D. None of the above.

_______________denoted by JD(R1, R2, ..., Rn), specified on

relation schema R, specifies a constraint on the states r of R.

36 A. Partial Dependency CO5 BT2

B. Join Dependency
C. Join Decomposition
D. Join Database

The normalization process, as first proposed by

A. Edgar F. Codd
37 B. Peter Landin CO5 BT1
C. Mark Edward
D. Sandford
Assume a relation R(A, B, C, D) with set of functional
dependencies F={C→D,C→A,B→C}. Use this setup to answer
the following questions; Which of the following is the candidate
keys of R?
38 CO5 BT3
A. C
B. BC
C. B
D. Both (b) and (c)

An attribute of relation schema R is called ___________of R if it is

a member of some candidate key of R.

A. a non-prime attribute
39 CO5 BT2
B. a prime attribute
C. Composite attribute
D. Derived attribute

Consider the relation schema R={E,F,G,H,I,J,K,L,M,N} and the

set of functional dependencies EFG,FIJ,EHKL,KM,LN
ON r. What is the key for R?

40 A. {E,F} CO5 BT3

B. {E,F,H}
C. {E,F,H,K,L}
D. {E}
 If X --> Y is a functional dependency and X and Y are sets of
attributes, what is the relationship between X and Y?

A. One-to-Many
41 CO5 BT1
B. Many-to-One
C. One-to-One
D. Many-to-Many

A ________of a relation schema R = {A1, A2, ... , An} is a set of

attributes S ⊆ R with the property that no two tuples t 1 and t 2 in
any legal relation state r of R will have t 1[S] = t2[S].

42 A. super key CO5 BT1

B. foreign key
C. candidate key
D. alternate key

For a functional dependency X --> Y, it is said to be _________ if

Y is the subset or equal to X.

E. Total functional dependency

43 CO5 BT1
F. Trivial functional dependency
G. Non-trivial functional dependency
H. Partial functional dependency

A functional dependency set

F={AB,BCE,EDA,EFG,EF}
Find out the closure of (AC)

44 A. {A,B,C,D,E,F,G} CO5 BT3

B. {A,B,D,E,F}
C. {A,B,C,E}
D. {A,B,C,E,F,G}

Let R(A,B,C,D) be a relation schema and F{ABC,ABD,BC}

Be the set of functional dependencies define over R. Which of the
following represents the closure of the attribute set{B}?

45 A. {A,C,D} CO5 BT3

B. {B,C}
C. {A,B,C}
D. {B}
 A table is in 2NF if it is in 1NF and if:

A. no column that is not a part of the primary key is dependent on

only a portion of the alternate key.
B. no column that is not a part of the primary key is
46 CO5 BT2
dependent on only a portion of the primary key.
C. no column that is not a part of the primary key is dependent on
only a portion of the foreign key.
D. none of these

______________is the process of storing the join of higher normal

form relations as a base relation, which is in a lower normal form.

A. Denormalization
47 CO5 BT1
B. Normalization
C. Dependency
D. Relational Algebra

R(A,B,C,D) is a relation,which of the following does not have a

lossless join dependency preserving BCNF decomposition.

A. AB,BCD
48 CO5 BT3
B. AB,BC,CD
C. ABC,CAD
D. ABCD

Which normal form is considered adequate for relational database

design?

A. 2NF
49 CO5 BT1
B. 3NF
C. 4 NF
D. BCNF

A functional dependency of the form x —> y is trival if

__________.

A. y⊆x
50 CO5 BT2
B. y⊂ x
C. x⊂y
D. x ⊂y and y⊂ x

PART B (4 Marks)
 Explain select and project operation in relational algebra.

Ans:

Select operation

It displays the records that satisfy a condition. It is denoted by sigma

(σ) and is a horizontal subset of the original relation.

Syntax:
1 CO5 BT1
σcondition(table name)

Projection operation

It displays the specific column of a table. It is denoted by pie (∏). It is

a vertical subset of the original relation. It eliminates duplicate tuples.

Syntax:

∏condition(table name)

Consider the following schema:

Suppliers(sid: integer, sname: string, address: string)
Parts(pid: integer, pname: string, color: string)
Catalog(sid: integer, pid: integer, cost: real)
The key fields are underlined, and the domain of each field is
listed after the field name. Thus sid is the key for Suppliers, pid is
the key for Parts,and sid and pid together form the key for
Catalog. The Catalog relation lists the prices charged for parts by
Suppliers. Write the following queries in relational algebra.
a) Find the names of suppliers who supply some red part
b) Find the sids of suppliers who supply some red or green part.
c) Find the sids of suppliers who supply some red part or are at
221 Packer Ave
d) Find the sids of suppliers who supply some red part and some
2 BT2
green CO5
part

Ans:
 What are the Pitfalls in Relational database design?
Ans:
Relational database design requires that we find a “good”
collection of relational schemas. A bad design may lead to
 Repetition of information
 Inability to represent certain information
Design Goals for Relational Database:
1. Avoid redundant data
2. Ensure that relationships among attributes are represented.
3. Facilitate the checking of updates for violation of database integrity
constraints
Example:
3 CO5 BT1
Consider the relational schema Lending-schema = (branch-name,
branch-city, assets, customer-name, loan- number, amount)
Redundancy:
 Data for branch name, branch city, assets are repeated for each
loan that a branch makes.
 Wastes space and complicates updating.
Null Values:
 cannot store information about a branch if no loan exists.
 can use null values, but they are difficult to handle.
In the given example the database design is faulty which makes the
above pitfalls in database. So, in relational database design if the
design is not good then there will be faults in databases.
Write short note on normalization.

Ans:
Database Normalization is a design technique. Using this we can
design or re-design schemas in the database to reduce redundant data
and the dependency of data by breaking the data into smaller and more
4 relevant tables. BT1
CO5

The primary purpose of the normalization is to reduce the data

redundancy i.e. the data should only be stored once. This is to avoid
any data anomalies that could arise when we attempt to store the same
data in two different tables, but changes are applied only to one and
not to the other.
 Explain 1NF with suitable example.
Ans:
First Normal Form (1NF):
o A relation will be 1NF if it contains an atomic value.
o It states that an attribute of a table cannot hold multiple values.
It must hold only single-valued attribute.
o First normal form disallows the multi-valued attribute,
composite attribute, and their combinations.

5 Example: CO5 BT1

Relation STUDENT in table 1 is not in 1NF because of multi-valued
attribute STUD_PHONE. Its decomposition into 1NF has been shown
in table 2.

Explain Insert Anomalies with example.

Ans:
Insertion anomaly:
If a tuple is inserted in referencing relation and referencing attribute
value is not present in referenced attribute, it will not allow inserting in
referencing relation. For Example, If we try to insert a record in
STUDENT_COURSE with STUD_NO =7, it will not allow.

6 BT2
CO5
 Illustrate functional dependency with example?
Ans:
Functional Dependency (FD) is a constraint that determines the
relation of one attribute to another attribute in a Database Management
System (DBMS). Functional Dependency helps to maintain the quality
of data in the database.
A functional dependency is denoted by an arrow “→”. The functional
dependency of X on Y is represented by X → Y.
Example:

Employee
Employee Name Salary City
number
7 BT2
CO5
1 Dana 50000 San Francisco

2 Francis 38000 London

3 Andrew 25000 Tokyo

In this example, if we know the value of Employee number, we can

obtain Employee Name, city, salary, etc. By this, we can say that the
city, Employee Name, and salary are functionally depended on
Employee number.
 Demonstrate transitive dependency? Give an example?
Ans:
A functional dependency is said to be transitive if it is indirectly
formed by two functional dependencies. For Eg: X -> Z is a transitive
dependency if the following three functional dependencies hold true:
X->Y
Y does not ->X
Y->Z
Example:

Book Author Author_age

Game of Thrones George R. R. Martin 66
8 BT2
Harry Potter J. K. Rowling 49 CO5
Dying of the Light George R. R. Martin 66
{Book} ->{Author} (if we know the book, we knows the author name)

{Author} does not ->{Book}

{Author} -> {Author_age}

Therefore as per the rule of transitive dependency: {Book} ->

{Author_age} should hold, that makes sense because if we know the
book name we can know the author’s age.
 Define Armstrong axioms for FD’s?
Ans:
1. Reflexive Rule (IR1)
In the reflexive rule, if Y is a subset of X, then X determines Y.
If X ⊇ Y then X → Y
2. Augmentation Rule (IR2)
The augmentation is also called as a partial dependency. In
augmentation, if X determines Y, then XZ determines YZ for any Z.
3. Transitive Rule (IR3)
In the transitive rule, if X determines Y and Y determine Z, then X
must also determine Z.
If X → Y and Y → Z then X → Z
4. Union Rule (IR4)
Union rule says, if X determines Y and X determines Z, then X must
9 CO5 BT2
also determine Y and Z.
If X → Y and X → Z then X → YZ
5. Decomposition Rule (IR5)
Decomposition rule is also known as project rule. It is the reverse of
union rule.
This Rule says, if X determines Y and Z, then X determines Y and X
determines Z separately.
If X → YZ then X → Y and X → Z
6. Pseudo transitive Rule (IR6)
In Pseudo transitive Rule, if X determines Y and YZ determines W,
then XZ determines W.
7.Composition (IR7)
If A → B and X → Y then AX → BY

Explain about BCNF?

Ans:
 BCNF is the advance version of 3NF. It is stricter than 3NF.
 A table is in BCNF if every functional dependency X → Y, X
is the super key of the table.
 For BCNF, the table should be in 3NF, and for every FD, LHS
10 is super key. CO5 BT2
 BCNF decomposition does not always satisfy dependency
preserving property.
 After BCNF decomposition if dependency is not preserved
then we have to decide whether we want to remain in BCNF or
rollback to 3NF. This process of rollback is called
denormalization.
 Let F = {A → B, AB → E, BG → E, CD → I, E → C}. Find the
closures, A+, (AE)+ and (ADE)+ .
Ans:
To find A+:
result := A
If you know A, then you would know AB from the functional
dependency (FD) A → B.
result := AB
If you know AB, then you would know ABE from the FD AB → E.
result := ABE
If you know ABE, then you would know ABEC from the FD E → C.
result := ABCE
We have included all the LHS of given functional dependencies. No
FDs of left hand that has ABCE in it. Hence, our algorithm stops at
this point. And the closure is ABCE.

To find (AE)+:
11 CO5 BT3
result := AE
result := ABE from the FD A → B
result := ABCE from the FD E → C.
We cannot move further. Hence, the closure is ABCE.

To find (ADE)+:
result := ADE
result := ABDE from the FD A → B
result := ABCDE from the FD E → C
result := ABCDEI from the FD CD → I
We cannot move further. Hence, the closure is ABCDEI.

The closures are;

A+ = ABCE
(AE)+ = ABCE
(ADE)+ = ABCEDI

What is Denormalization? Give it’s advantages and disadvantages.

Denormalization is a database optimization technique where
we add redundant data in the database to get rid of the complex join
operations. This is done to speed up database access speed.
Denormalization is done after normalization for improving the
performance of the database. The data from one table is included in
another table to reduce the number of joins in the query and hence
helps in speeding up the performance.
12 CO5 BT2
Advantages of Denormalization:
Query execution is fast since we have to join fewer tables.
Disadvantages of Denormalization:
 As data redundancy is there, update and insert operations are
more expensive and take more time. Since we are not
performing normalization, so this will result in redundant data.
 Data Integrity is not maintained in denormalization. As there is
redundancy so data can be inconsistent.
 PART C (12 Marks)

1 Define Functional Dependencies. Discuss about different functional CO5 BT2

dependencies
What are the problems caused by Redundancy? Explain about CO5
2 BT1
Normalization and need for normalization.
Define Normalization. Explain about 1NF, 2NF with relevant CO5
3 BT2
examples.
4 Explain about 3NF and BCNF with relevant table structure. CO5 BT2
Explain about Multi-valued dependencies and Fifth Normal Form with
5 CO5 BT2
suitable examples.

UNIT-V

UNIT V
Transaction concepts, properties of transactions, Serializability of transactions, testing for serializability, System
recovery, Concurrency Control, Two- Phase Commit protocol, Recovery and Atomicity, Log-based recovery,
Concurrent executions of transactions and related problems, Locking mechanism, solution to concurrency related
problems, Deadlock, Two-phase locking protocol, Isolation, Intent locking.
PART-A (Multiple Choice Questions)
Q. Questions Course Competen Page
Outcom ce Numb
No e BT Level er
1 What is a collection of operations that form a single logical unit of
work is defined as?
a) Views
CO6 BT1 625
b) Networks
c) Units
d) Transaction
Answer:
d) Transaction
2 The “all-or-none” property is commonly referred to as _________
a)Isolation
b)Durability
CO6 BT1 628
c)Atomicity
d) Consistency
Answer:
c)Atomicity
3 Which of the following is the property of transaction that protects
data from system failure?
a) Consistency
CO6 BT2 628
b)Durability
 c)Atomicity
d)Isolation
Answer:
b)Durability
4 Identify the property that the database system must provide to
isolate transactions from the effects of other concurrently
executing transactions.
CO6 BT2 628
a)Consistency
b)Durability
c)Atomicity
d) Isolation
Answer:
d) Isolation
5 Which of the following is a unit of program execution that
accesses and possibly updates various data items?
a) Schedule
CO6 BT2 628
b) View
c) Transaction
d) block
Answer:
c)Transaction
6 Identify the statements used to delimit a transaction.
a) begin transaction and end transaction
b) start transaction and stop transaction
CO6 BT3 629
c) get transaction and post transaction
d) read transaction and write transaction
Answer:
a) begin transaction and end transaction
7 Highlight which property of the database is preserved when
execution of a transaction is in isolation.
a)Concurrency
CO6 BT1 629
b)Durability
c)Atomicity
d) Consistency
Answer:
d) Consistency
8 What are the ACID properties of Transactions?
a)Atomicity,Consistency,Isolation,Datacentric
b)Atomicity,Consistency,Isolation,Durability
CO6 BT2 628
c)Atomicity,Concurrency,Inconsistent,Durability
d) Automatically, Concurrency, Isolation, Durability
Answer:
b)Atomicity,Consistency,Isolation,Durability
9 Choose who has the responsibility of ensuring consistency for an
individual transaction.
a)Applicationprogrammer
CO6 BT3 630
b)Databasedesigners
c)Naïveusers
d) System Analyst
Answer:
a) Application programmer
10 Determine which state of the system no longer reflects a real state
of the world that the database is supposed to capture because of a
failure.
CO6 BT3 633

a) valid state
b) inconsistent state
c) failed state
d) waiting state
Answer:
b)inconsistentstate
11 Find out to which type of file the transaction information is
written as the database system keeps track on disk of the old
values of any data.
CO6 BT1 632
a) block
b) record
c) log
d) backup
Answer:
c)log
12 Select which component of the database handles atomicity of the
database system.
a) storage engine
CO6 BT1 633
b) log manager
c) query processor
d) recovery system
Answer:
d) recovery system
13 Choose the component of the database system that ensures the
isolation property.
a) concurrency-control system
CO6 BT3 636
b) Optimization engine
c) query processor
d) recovery system
Answer:
a)concurrency-control system
14 When a transaction may not always complete its execution
successfully it is termed as_____
a) committed
CO6 BT1 634
b) aborted
c) rollback
d) active
Answer:
a)aborted
15 To which state does the transaction move to once the changes
caused by an aborted transaction have been undone?
a) committed
CO6 BT2 634
b) aborted
c) rollback
d) active
 Answer:
c)rollback

16 Which of the following is not a state in transaction?

a) Active
b) Terminated
CO6 BT3 634
c) Aborted
d) Partially committed
Answer:
b)Terminated
17 In which of the below states a transaction is said to have
terminated?
a) active
CO6 BT2 634
b) Terminated
c) either committed or aborted
d) Partially committed
Answer:
c)either committed or aborted
18 Find out the good reason for allowing concurrency?
a) Improved throughput and reduced waiting time
b) improved response time
CO6 BT1 636
c) reduce throughput loss
d) increase execution time
Answer:
a)Improved throughput and reduced waiting time

19 When the number of transactions executed increases in a given

amount of time, it is defined as_____
a) average response time
CO6 BT1 636
b) throughput
c) disk utilization
d) latency
Answer:
b)throughput

20 Performing concurrent execution of transaction reduces_____

a) waiting time
b) buffer time
CO6 BT3 639
c) queue time
d) evaluation time
Answer:
a)waiting time

21 Serializability of schedules can be ensured through a mechanism

called_____
a) evaluation control policy
CO6 BT1 641
b) concurrency control policy
c) execution control policy
d) cascading control policy
Answer:
 b) concurrency control policy

22 A schedule can be tested against the conflict serializability by

constructing a _______
a) histogram
CO6 BT3 641
b) gantt chart
c) precedence graph
d) bar graph
Answer:
c)precedence graph

23 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
a) Non conflict equivalent
CO6 BT2 641
b) Equal
c) Conflict equivalent
d) Isolation equivalent
Answer:
c)Conflict equivalent

24 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.
CO6 BT2 641
a) Conflicting
b) Overwriting
c) Isolated
d) Durable
Answer:
a)Conflicting

25 Identify the process in which a serializability order of the

transactions can be obtained by finding a linear order consistent
with the partial order of the precedence graph.
CO6 BT1 644
a) Selection sorting
b) Topological sorting
c) Heap sorting
d) Insertion sort
Answer:
b) Topological sorting

26 If a transaction has obtained a __________ lock, it can read but

cannot write on the item
a) Shared mode
CO6 BT4 644
b) Exclusive mode
c) Read only mode
d) Write only mode
Answer:
a) Shared mode

27 On obtaining which type of lock a transaction can both read and

write on the item?
 a) Shared mode CO6 BT1 647
b) Exclusive mode
c) Read only mode
d) Write only mode
Answer:
b) Exclusive mode
28 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 ________
CO6 BT1 647
a) Concurrent
b) Equivalent
c) Compatible
d) Executable
Answer:
c) Compatible

29 Which protocol indicates when a transaction may lock and unlock

each of the data items?
a) Locking protocol
CO6 BT2 651
b) Unlocking protocol
c) Granting protocol
d) Conflict protocol
Answer:
a) Locking protocol

30 Choose which state a transaction is in if it may obtain locks but

may not release any locks.
a) Growing phase
CO6 BT1 651
b) Shrinking phase
c) Deadlock phase
d) Starved phase
Answer:
a) Growing phase

31 What is the situation where no transaction can proceed with

normal execution?
a) Road block
CO6 BT1 665
b) Deadlock
c) Execution halt
d) Abortion
Answer:
b) Deadlock
32 A transaction can proceed only after the concurrency control
manager ________ the lock to the transaction
a) Grants
CO6 BT2 670
b) Requests
c) Allocates
d) deny
Answer:
a) Grants
33 Choose which phase a transaction is in if it may release locks but
 may not obtain any locks.
a) Growing phase
b) Shrinking phase CO6 BT1 667
c) Deadlock phase
d) Starved phase

Answer:
b)Shrinking phase
34 If transaction Ti gets an explicit lock on the file Fc in exclusive
mode, then it has an __________ on all the records belonging to
that file.
CO6 BT3 679
a) Explicit lock in exclusive mode
b) Implicit lock in shared mode
c) Explicit lock in shared mode
d) Implicit lock in exclusive mode
Answer:
d) Implicit lock in exclusive mode
35 Who is responsible for assigning, policing and managing the locks
used by the transactions?
a) Scheduler
CO6 BT1 667
b) DBMS
c) Lock manager
d) Locking agent
Answer:
c) Lock manager
36 Which type of errors causes a transaction to fail?
a) logical and system error
b) logical and process error
CO6 BT1 634
c) instance and system error
d) media and system error
Answer:
a)logical and system error
37 The assumption that hardware errors and bugs in the software
bring the system to a halt, but do not corrupt the nonvolatile
storage contents is referred as ______
CO6 BT2 722
a) point based assumption
b) fail-stop assumption
c) interval based assumption
d) fail-abort assumption
Answer:
b)fail-stop assumption
38 The log is a sequence of _________ recording all the update
activities in the database.
a) Log records
CO6 BT1 632
b) Records
c) Entries
d) Redo
Answer:
Log records
39 In the ___________ scheme, a transaction that wants to update the
 database first creates a complete copy of the database.
a) Shadow copy CO6 BT2 727
b) Shadow Paging
c) Update log records
d) Delete log records

Answer:
a) Shadow copy
40 The ____________ scheme uses a page table containing pointers
to all pages; the page table itself and all updated pages are copied
to a new location.
CO6 BT1 727
a) Shadow copy
b) Shadow Paging
c) Update log records
d) Delete log records
Answer:
b) Shadow Paging
41 If a transaction does not modify the database until it has
committed, it is said to use the ___________ technique.
a) Deferred-modification
CO6 BT1 729
b) Late-modification
c) Immediate-modification
d) Undo
Answer:
a) Deferred-modification
42 If database modifications occur while the transaction is still active,
the transaction is said to use the ___________technique.
a) Deferred-modification
CO6 BT2 729
b) Late-modification
c) Immediate-modification
d) Undo
Answer
a) Immediate-modification
43 ___________ using a log record sets the data item specified in the
log record to the old value.
a) Deferred-modification
CO6 BT2 670
b) Late-modification
c) Immediate-modification
d) Undo
Answer:
a)Undo
44 In the __________ phase, the system replays updates of all
transactions by scanning the log forward from the last checkpoint.
a) Repeating
CO6 BT1 728
b) Redo
c) Replay
d) Undo
Answer:
a)Redo
45 A special redo-only log record < Ti, Xj, V1> is written to the log,
where V1 is the value being restored to data item Xj during the
rollback. What are these log records sometimes called as?
CO6 BT1 736
a) Log records
b) Records
c) Compensation log records
d) Compensation redo records
Answer:
c)Compensation log records

46 When the actions are played in the order while recording it is

called ______________ history.
a) Repeating
CO6 BT3 737
b) Redo
c) Replay
d) Undo
Answer:
a) Repeating
47 The deadlock state can be changed back to stable state by using
which of the following statement?
a) Commit
CO6 BT1 648
b) Rollback
c) Savepoint
d) Deadlock
Answer:
a)Rollback
48 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 older than Tj). Otherwise, Ti is rolled back (dies).
CO6 BT2 675
this is defined as_______
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait
Answer:
a)Wait-die
49 When transaction Ti requests a data item currently held by Tj, Ti is
allowed to wait only if it has a timestamp larger than that of Tj
(that is, Ti is younger than Tj ). Otherwise, Tj is rolled back (Tj is
CO6 BT2 675
wounded by Ti), this is termed as_____
a) Wait-die
b) Wait-wound
c) Wound-wait
d) Wait
Answer:
a)Wound-wait
50 The situation where the lock waits only for a specified amount of
time for another lock to be released is referred as_____
a) Lock timeout
CO6 BT1 676
 b) Wait-wound
c) Timeout
d) Wait
Answer:
a)Lock timeout

PART B (4 Marks)

1 List the ACID properties. Explain the usefulness of each.

The database transaction system maintains the following CO6 BT2 628
four properties. These properties are often called the ACID
properties; the acronym is derived from the first letter of each of
the four properties.
● Atomicity
● Consistency
● Isolation
● Durability
Atomicity: Either all operations of the transaction are
reflected properly in the database, or none should be updated.
Consistency: Execution of a transaction in isolation
preserves the consistency of the database.
Isolation: Even though multiple transactions may execute
concurrently, the system guarantees that, for every pair of
transactions Ti and Tj , it appears to Ti that either Tj finished
execution before Ti started or Tj started execution after Ti finished.
Thus, each transaction is unaware of other transactions executing
concurrently in the system.
Durability: After a transaction completes successfully, the
changes it has made to the database persist, even if there are
system failures.
2 Explain the distinction between the term’s serial schedule and
serializable schedule.
CO6 BT1 636
T 1 T 2
 read(A)
A := A
50
write(A)
read(B)
B := B + 50
write(B)
commit
read(A)
temp := A *
0.1
A := A temp
write(A)
read(B)
B := B + temp
write(B)
commit
Fig 3.2. Schedule 1 - A serial schedule in which T is followed by T
1 2

If the transactions are executed one at a time in the order

T followed by T , then the corresponding execution sequence is
2 1

that of fig 3.3.

Again, as expected, the sum A + B is preserved, and the
final values of accounts A and B are Rs. 850 and Rs. 2150,
respectively.

T 1 T 2

read(A)
temp := A *
0.1
A := A temp
write(A)
read(B)
B := B + temp
write(B)
read(A)
commit
A := A
50
write(A)
read(B)
B := B + 50
write(B)
commit

Fig 3.3. Schedule 2 - A serial schedule in which T is followed by T

2 1

The execution sequences which represent the

chronological order, in which instructions are executed in the
system, are called schedules.
 Schedule 1 and schedule 2 are serial schedules. Several
execution sequences are possible, since the various instructions
from both transactions may now be interleaved. Given two
transactions are executed concurrently.
3 What benefit does rigorous two-phase locking provide?

● In strict two phase locking protocol all exclusive mode

CO6 BT1 651
locks taken by a transaction is held until that transaction
commits.

● Rigorous two phase locking protocol requires that all locks

be held until the transaction commits.

4 If deadlock is avoided by deadlock-avoidance schemes, is

starvation still possible? Explain your answer.
There are two approaches for deadlock prevention: CO6 BT1 665

One approach ensures that no cyclic waits can occur by

ordering the request for locks, or requiring all locks to be acquired
together.
This approach required that each transaction locks all data
items before it begins execution. It is required that, either all data
items should be locked in one step, or none should be locked.
In a system where the selection of victims is based primarily on
cost factors, it may happen that the same transaction is always
picked as a victim. As a result, this transaction never completes its
designated task, thus there is starvation. We must ensure that a
transaction can be picked as a victim only a finite number of
times. The most common solution is to include the number of
rollbacks in the cost factor.
5 Explain the purpose of the checkpoint mechanism. How often
should checkpoints be performed?
When a system crash occurs, we must consult the log to determine those
CO6 BT2 734
transactions that need to be redone and those that need to be undone. In
principle, we need to search the entire log to determine this information.
There are two major difficulties with this approach:

● The search process is time-consuming.

● Most of the transactions that, according to our algorithm, need to

be redone have already written their updates into the database.
Although redoing them will cause no harm, it will nevertheless
cause recovery to take longer.

6 What is locking and explain two phase locking protocol.

 ● A transaction T must either commit at all sites, or it must CO6 BT1 667
abort at all sites. To ensure this property, the transaction
coordinator of T must execute a commit protocol.

Two-Phase Commit
● Consider a transaction T initiated at site S , where thei

transaction coordinator is C . i

● When T completes its execution—that is, when all the sites

at which T has executed inform C that T has completed C
i i

starts the 2PC protocol.

7 Define a transaction. Then discuss the following with relevant
examples: (i) read only transaction (ii) A read write
transaction. CO6 BT1 732
The order in which updates are carried out by redo is important; when
recovering from a system crash, if updates to a particular data item are
applied in an order different from the order in which they were applied
originally, the final state of that data item will have a wrong value.

Most recovery algorithms do not perform redo of each transaction

separately; instead they perform a single scan of the log, during which
redo actions are performed for each log record as it is encountered. This
approach ensures the order of updates is preserved, and is more efficient
since the log needs to be read only once overall, instead of once per
transaction.

8 When do you say that the system is in deadlock? Explain.

CO6 BT3 665

A system is in a deadlock state if there exists a set of transactions such that every transaction in the set is waiting for anoth

PART C (12 Marks)

1 Show that the two-phase locking protocol ensures conflict
serializability, and that transactions can be serialized according to CO6 BT5 667
their lock points.
2 Under what conditions is it less expensive to avoid deadlock than
to allow deadlocks to occur and then to detect them? CO6 BT4 674

3 Explain testing for Serializability with respect to concurrency

control schemes. How will you determine whether a schedule is CO6 BT1 681
serializable or not.
4 What is concurrency Control? How is it implemented in DBMS?
CO6 BT2 651
5 Explain the properties of transactions. Illustrate the states of
transactions. CO6 BT6 627

Note:

1. BT Level – Blooms Taxonomy Level

2. CO – Course Outcomes

BT1 –Remember BT2 – Understand BT3 – Apply BT4 – Analyze BT5 – Evaluate BT6 – Create