PANIMALAR ENGINEERING COLLEGE
(An Autonomous Institution, Affiliated to Anna University Chennai)
QUESTION BANK
Details of the Course
Name of the Department :Department of Computer Science and
Engineering
Name of the Course :Database Management Systems
Course Code :23CS1303
Semester :03
Common To Programme(s) :B.E.(CSE), [Link](IT)
Instructions
Blooms Level: Blooms Level 1 & 2 is Lower Order (LO) Cognitive type, Blooms Level 3 & 4 is Intermediate
Order Cognitive Type (IO) and Blooms Level 5 & 6 is Higher Order (HO) cognitive type.
2 Marks: For each unit Three questions should be of lower order (LO) cognitive type and Two Questions
should be of Intermediate order (IO) cognitive type.
13 /15 /16 Marks: For each Unit Two questions should be of lower order (LO) cognitive type i.e. remembrance
type questions, Two should be of intermediate order (IO) cognitive type i.e. understanding type questions
and One Question should be on Higher Order (HO) Application / Design / Analysis / Evaluation / Creativity /
Case study questions.
* HO Order is not applicable if the Question Pattern does not have Part C. In Such cases consider HO as IO.
** If the Mark for Part B &C is less than the maximum mark of the Question, Sub Divisions shall be added.
Course Outcome: (List the Course Outcomes of the Course)
CO1: Identify and Recognize the Structure of a Database
CO2: Understand an Entity Relational Model for a database
CO3: Apply Relational and Non-Relational database concepts to design a database
CO4: Analyze the importance of normalization and functional dependencies in
database design
CO5: Evaluate the working principles of indexing and hashing.
CO6: Create a database design using both Relational and Non- Relational models
Bloom’s Level: BL1 - Remembering, BL2 - Understanding, BL3 - Applying, BL4 - Analyzing, BL5 – Evaluating, BL6 -
Creating.
UNIT- I - Database Fundamentals
Course Marks
PART A ( 2 Marks) Bloom’s Level
Outcome Allotted
1. What is Database? Give some applications of Database [BL2] [CO1 [2]
]
2. List the three levels of data abstraction. [BL1] [CO1 [2]
]
3. What is weak entity set? Give Example [BL2] [CO1 [2]
]
4. Differentiate Instance and Schema. [BL4] [CO1 [2]
]
5. Compare the features of file system with database system. [BL4] [CO1 [2]
]
Descriptive Questions ( 13 /15/16 Marks)
6. Explain Basic architecture of DBMS with its neat block diagram. [BL2] [CO1 [13]
]
7. Write short notes on [BL1] [CO1 [13]
(i) Purpose of Database systems(5) ]
(ii) Views of data (4)
(iii) Applications of database systems(4)
8. Illustrate with an example, Extended E-R Diagram and its notations. [BL3] [CO1 [13]
railway ]
Couse Course Head of the
Instructor Coordinator Department
9. Illustrate in detail about Relational Data model and ER model for [BL3] [CO1 [13]
employee database application. ]
10. Draw ER Diagram for “ Restaurant menu ordering system”, which will [BL6] [CO1 [13]
facilitatethe food items ordering and services within restaurant. The ]
entire scenario is detailed below. The customer is able to view the food
items menu, call the waiter, place orders and obtain final bill through the
computer kept in their table. The waiters through their wireless tablet PC
are able to initialize a table for customers , control the table functions to
assist customers , orders, send orders to food preparation staff(chef)
and finalize the customers bill. The chef with their touch display interface
to the system, are able to view orders sent to the kitchen by waiters.
During preparation they are able to let the waiter know the status of
each item , and can send notifications when items are completed. The
system should have full accountability and logging facilities and should
support supervisor actions to account for exceptional circumstances
such as meal being refunded or walked out on.
UNIT- II – Relational Database
Course Marks
PART A ( 2 Marks) Bloom’s Level
Outcome Allotted
1. List the steps involved in Query Processing [BL2] [CO3 [2]
]
2. What is primary key and foreign key. Give example for each. [BL2] [CO3 [2]
]
3. What are aggregate Functions? List the aggregate functions used in [BL2] [CO2 [2]
SQL. ]
4. Write SQL query to get the department and department wise total (sum) [BL3] [CO2 [2]
salary, display it in ascending order according to salary. ]
5. Compare Dynamic SQL and Static SQL. [BL4] [CO2 [2]
]
Descriptive Questions ( 13 /15/16 Marks)
6. Explain select, project ,Cartesian product operation and Join [BL3] [CO2 [13
operation types in relational algebra. (operators) ] ]
7. Give syntax and explain DDL, DML, DCL commands for the given [BL3] [CO2 [13
student’s database which contains student details: name, id, DOB, ] ]
branch, DOJ, and course details: Coursename, Course id, Stud Id,
Faculty name, id, marks.
8. Consider the following relational schema: [BL4] [CO2 [13
[Link](bookid,title,publisher_name) ] ]
b.Book_author(bookid,author_name)
c.Book_copies(bookid,branched,No_of_copies).Write the following
queries inSQL.
i. Retrieve the author name of book having title
‘Operating System’.
ii. Retrieve the total number of titles of each
publisher.
iii. Retrieve the total number of titles.
iv. Retrieve title, publishername&authorname of the
bookid ‘B101’.
Retrieve the number of copies with bookid=’B101’ and
branched=’BR001’
9. Develop queries and show how a SQL relation can be defined using [BL4] [CO2 [13
various constraints and types of keys? Also, Explain different data ] ]
definition Language statements in detail.
10. Perform the following clauses of SQL with example queries. [BL4] [CO2 [13
Couse Course Head of the
Instructor Coordinator Department
1. FROM clause ] ]
ii. GROUP BY clause
iii. HAVING clause
iv. ORDER BY clause.
UNIT- III – Database Design
Course Marks
PART A ( 2 Marks) Bloom’s Level
Outcome Allotted
1. Define Functional Dependency. [BL1] [CO4 [2]
]
2. List the Properties of Decomposition. [BL1] [CO4 [2]
]
3. State Anomalies in NF. [BL1] [CO4 [2]
]
4. Why 4NF in normal form is more desirable than BCNF? [BL4] [CO4 [2]
]
5. Suppose you are given a relation Student(Stud_no, [BL4] [CO4 [2]
Stud_Name,Stud_phone, Stud_State, Stud_Country, Stud_Age). Find ]
all Functional Dependencies.
Descriptive Questions ( 13 /15/16 Marks)
6. Explain the concept of functional dependencies and its [BL2] [CO4 [13
types with example. ] ]
7. State the need for Normalization .Construct 1NF,2NF,3NF with [BL2] [CO4 [13
example ] ]
8. List the Properties of Decomposition. Distinguish between [BL4] [CO4 [13
lossless decomposition and dependency preserving ] ]
decomposition with example.
9. How do you Convert 3NF to BCNF. Explain BCNF, 4NF and [BL3] [CO4 [13
5NF with example. ] ]
10. i) Consider the given relation R(A,B,C,D) and functional dependency [BL6] [CO4 [13
FD= (ABC, CBCD) .Determines the keys, key of prime or non- ] ]
prime attributes.
ii) Compute the Closure of the following set F of functional
dependencies for relation R={A,B,C,G,H,I}F={A->B, A->C, CG-
>H,CG->I,B->H}
UNIT- IV – Transaction Management
Bloom’s Level Course Marks
PART A ( 2 Marks) Outcome Allotted
1. Write the ACID Properties of Transaction. (13) [BL1] [CO3 [2]
]
2. Define Deadlock. [BL2] [CO3 [2]
]
3. Define Strict And Rigorous Two Phase Locking Protocol [BL1] [CO3 [2]
]
4. Consider the following transaction T consisting of T1 and T2: [BL3] [CO3 [2]
Transfer of 100 from account X to account Y. ]
Before:X:500 Y:200
Transaction T
T1 T2
Read (X) Read(Y)
S:=x-100 Y:=Y+100
Couse Course Head of the
Instructor Coordinator Department
Write(X) Write(Y)
After:X:400 Y:300
If the transaction fails after completion of T1 but before
completion of T2. (say, after write(X) but before write(Y)), then
amount has been deducted from X but not added to Y. Which
ACID property must be used to avoid inconsistency?
5. What are the operations in schedule are said to be conflict? [BL4] [CO3 [2]
]
Descriptive Questions ( 13 /15/16 Marks)
6. Why Concurrency control is needed? State and Explain [BL2] [CO3 [13
Three Concurrency Problems. ] ]
7. State and explain lock based concurrency control and [BL2] [CO3 [13
two phase locking with suitable example. ] ]
8. Explain testing for Serializability with respect to concurrency [BL3] [CO3 [13
control scheme. How will you determine whether the schedule ] ]
is serializable or not?
9. (i)What is deadlock and how does it occur? (6) [BL4] [CO3 [13
(ii) Explain the deadlock prevention methods with an example? (7) ] ]
10. (i) Consider the following two transactions [BL6] [CO3 [13
T1 : READ(A); ] ]
READ(B);
IF A=0 THEN B:=B+1;
WRITE(B)
T2 : READ(B);
READ(A);
IF B=0 THEN A:=A+1;
READ(A);
WRITE(A);
Add lock and unlock instructions to transactions T1 and T2, so that they
observe the two –phase locking protocol. Can the execution of these
transactions result in a deadlock? (6)
(ii) Consider the following, the actions are listed in the order they are
scheduled and prefixed with the transaction name.
T3:W(X), T1:R(X), T1:W(Y), T2:R(Z), T2:W(Z), T3:R(Z)
(1)What is the precedence graph for the above schedule?
(2)Is the schedule conflict-serializable? If so what are all the conflict
equivalent serial schedules? (7)
UNIT- V - Implementation Techniques
and Non-relational Model
Course Marks
PART A ( 2 Marks) Bloom’s Level
Outcome Allotted
1. What are ordered indices? List the types. [BL2] [CO5 [2]
]
2. What is NoSQL database? [BL2] [CO5 [2]
]
3. What are the advantages of B tree over B+ tree? [BL2] [CO5 [2]
]
4. Differentiate static hashing and dynamic hashing. [BL4] [CO5 [2]
]
5. Differentiate between MangoDB and RDBMS. [BL4] [CO6 [2]
]
Descriptive Questions ( 13 /15/16 Marks)
6. What is RAID? List and Explain Various RAID levels and features [BL2] [CO5 [13
in detail. ] ]
Couse Course Head of the
Instructor Coordinator Department
7. How records are represented in a file and how to organize them [BL4] [CO5 [13
in a file? ] ]
8. How Indexing mechanisms is used for accessing files? Explain [BL4] [CO5 [13
the various indexing schemes used in database environment. ] ]
9. Why to use MangoDB? Give the key components of MangoDB [BL3] [CO6 [13
architecture . ] ]
10. Construct a B+ tree for the following set of key values: [BL6] [CO5 [13
(2,3,5,7,11,17,19,23,29,31) ] ]
Assume that the tree is initially empty and values are added in
ascending order.
Construct B +trees for the cases where the number of pointers that will
fit in one node is as follows.
a) Four
b) Six
c) Eight
Couse Course Head of the
Instructor Coordinator Department