BCSE302L – DATABASE SYSTEMS QUESTION BANK

MODULE -1

1. Compare and contrast the hierarchical, network, and relational data models.
Which model is most suitable for modern applications and why?
2. Traditional file systems were widely used before DBMS became popular.
Compare both approaches and explain how the key characteristics of DBMS.
3. Differentiate between a database schema and an instance with suitable
examples. Explain how schema remains constant while instances change over
time. Illustrate your answer with a real-world application - a university or
banking system.
4. Identify and explain the main actors involved in a Database Management
System (DBMS). Describe the roles and responsibilities of each actor with
suitable examples from real-world application - online banking.
5. Explain the three-schema architecture of a DBMS with the help of a neat
diagram. How does this architecture support data abstraction and
independence? Justify your answer with real-world examples for each level.
6. What is Client-Server Architecture in DBMS? Draw a simple diagram to show
how it works. Explain how this architecture helps in handling multiple users,
improves security, and gives better performance.
MODULE -2

1. A university library wants to develop a database system to manage its book

collection, memberships, and borrowing activities. The system should track
information about books (titles, authors, publishers, etc.), students (IDs, names,
majors, etc.), and faculty members (IDs, names, departments, etc.). It also
needs to record borrowing transactions, including the borrower (student or
faculty ID), borrowed book ID, and borrowing date. Design an ER model and
map it into relational data model for this library management system
considering entities, attributes and their relationships that are needed in the
database.
2. Answer the following with the help of the ER diagram.

I. Find the minimum number of tables required.

II. Represent all the tables with their Primary keys, Attributes and Relationships
type.
 III. What does ‘N’ and ‘R2’ represent?
IV. Can I have a valid DBMS without ‘N’ entity? Justify.
V. Identify the derived key in the given diagram, if any.

3. Consider the following ER diagram and translate into a relational schema with all
individual tables.

4. A university maintains the following two tables in its database:

StudentID Name Email DepartmentID

101 Riya riya@[Link] D01
102 Arjun arjun@[Link] D02
DepartmentID DepartmentName HOD
D01 Computer Science Dr. Mehta
D02 Electronics Dr. Sharma

Based on the above scenario, answer the following:

1. Identify the Primary Key in both tables.

2. List all possible Candidate Keys in the STUDENT table.
 3. Identify the Foreign Key and explain its role.
4. State any two Integrity Constraints that are applicable in this scenario.
5. What will happen if a student is added with a DepartmentID that does not exist
in the DEPARTMENT table? Justify your answer based on referential integrity.

5. A company maintains two tables: EMPLOYEE(EmpID, Name, DeptID) and

DEPARTMENT(DeptID, DeptName). Explain how you would apply Primary Key and
Foreign Key constraints in these tables. What happens if a new employee is added
with a non-existent DeptID?

6. Explain different types of integrity constraints in DBMS. For each of the following,
provide an example:
a) Entity Integrity
b) Referential Integrity
c) Domain Constraint
Why are these constraints important in maintaining consistency?

MODULE -3
1.
I. Give an example of a table which is not in First Normal Form. Your table must
be formed from a student’s database with attributes sid, sname, sex, major,
gpa, activity and fee.

II. Give an example of a table which in First Normal Form but not in Second
Normal Form. Use the same student’s database. Provide your own keys and
constraints. Convert your table into Second normal form relations.

III. Give an example scenario in which this student database is in Second Normal
form but not in Third normal form. Convert your table into third normal form
relations. Provide the keys and constraints used by you.

IV. Give an instance of student’s database which is in Third Normal Form but not
in Boyce Codd normal form.

V. What is the need for higher normal forms namely Fourth normal form, fifth
normal form and Domain/Key normal form. Which is the highest normal form
and why?

2. Consider the relation REFRIG( Model#, Year, Price, Manufacturing-plant, Colour),

which is abbreviated as REFRIG(M, Y, P, Mp, C) , and with the following set F of
functional dependencies:

F = { M→Mp, MY→P, Mp→C}

(i) Evaluate each of the following as a candidate key for REFRIG, giving reasons
why it can or cannot be a key: M, MY, MC.
 (ii) Based on the above key determination, state whether the relation REFRIG is in
3NF and provide proper reasons.
(iii) Based on the above key determination, state whether the relation REFRIG is in
BC/NF and provide proper reasons.
(iv) Consider the decomposition of REFRIG into D = (R1(M, Y, P) and R2(M, Mp, C).
Is this decomposition loseless? Show why.
(v) Check whether the decomposition is dependency preserving.

3. Consider the relation R= (ABCDEFG) with the functional dependencies namely F=

{AB → CD, AF → D, DE → F, C → G, F → E and G → A}. The values of R are atomic.

(i) Find the candidate keys of R.

(ii) Apply the closure computation algorithm and find the closure of F.

(iii) Apply attribute closure algorithm and find the minimal cover of F.

(iv) Check whether the given R is in 1NF? If not convert it into 1 NF relations.
Also check whether the given R is in 2NF? If not convert it into 2 NF
relations.

(v) Check whether the given R is in 3 NF? If not convert it into 3 NF relations.
Also check whether R is in BC/NF. If not, convert R into BC/NF relations.

4. Consider the universal relation R (A, B, C, D, E, F, G, H, I, J).

The set of functional Dependencies are FD = { AB → C, A → DE, B→F,

F→GH, D→IJ }.

(i) What is the key for R?

(ii) Check whether it is in 2NF. If not, convert it into 2NF relations.
(iii) Check whether it is in 3NF. If not, convert it into 3NF relations
(iv) Find the closure of the set FD.
(v) Find the closure of the attribute A.

Module -4
1. Create a B+-tree with five pointers and four key values by using the
following data.
8, 15, 11, 17, 13, 65, 29, 22, 16, 12, 29, 35, 38, 21, 9, 44, 5, 76, 81, 92 and
126.
(i). Show the tree after creating the tree with the values 8, 15,11 and 17
(ii). Show the insert operations for other values one by one.
(iii). Search for the value 65.
(iv) Delete the values of 15, 22, and 29.
(v). Update the value 17 with 60.
2. Consider the following tables
Instructor(ID, name, dept_name, salary)
teaches(ID, course_id, sec_id, semester, year)
course(course_id, title, dept_name, credits)
student(sid, sname, major, sex, gpa)
enrollmen(sid, course_id, gpa)
(i). Write the relational algebra expression for finding the names of all
instructors working in the Computer Science department, along with the
titles of the courses that they teach and the department offering the course.
(ii). Draw the Query tree for Question(i) and optimize the query.
(iii). Write the relational algebra and relational calculus expressions for
finding the names of students who have registered for a course under any
of the instructors in the CSE department who have taught a course in 2023
Fall Semester, along with the titles of the courses that they taught.
(iv). Draw the Query tree for Question (iii) and optimize the query.

3. A hospital maintains thousands of digital patient records that include

medical history, prescriptions, and diagnostic reports. These records are
accessed frequently by doctors and nurses, particularly in emergency
situations. Currently, the hospital uses sequential file organization, which
results in slow retrieval of specific records. To improve efficiency, the
hospital is considering the use of indexing and hashing techniques.
(i). Explain how indexing can be utilized to efficiently retrieve patient
records based on attributes such as patient ID, name, or date of visit.
(ii). Describe how hashing can provide direct access to patient records, and
discuss the importance of hash functions in this process.
(iii). Compare the performance of indexing and hashing in the context of
this hospital system.

4. You are given the following Schemas:

Suppliers (Supplier-Number, Supplier-Name, Status, City)
Parts (Part-Name, Colour, Weight, City)
Shipments(Supplier-Number, Part-Number, Quantity)
Customers(Cust-no, Cust-name, City, Supplier-Number, Part-name)
 Write the relational algebra and relational calculus expressions for all the
queries given below. Draw the query trees and perform the query
optimization for the following queries through tree operations. Write the
optimized SQL Queries by deriving them from the final query trees.
Explain the Query optimization techniques used in each of these queries.

(i). Get Supplier Numbers for Suppliers who supply at least one of the parts
supplied by supplier S2.
(ii). Get all pairs of supplier numbers such that the suppliers concerned are
located in the same city and supplied more than a quantity of 500 in any
one of the transactions.
(iii). Get supplier names for suppliers who either do not supply part P2 or
supplied red parts to Mumbai customers.
(iv). Get supplier numbers and names for suppliers with a status lower than
that of supplier S1 and supplied parts Chennai customers.
(v). Get supplier numbers for suppliers who are supplying at least one part
that is supplied by any other supplier who supplies red parts to Bangalore
customers.

5. Create a B-tree with three pointers and two key values by using the
following data.
18, 15, 11, 17, 13, 112, 19 and 16.
(i). Show the tree after creating the tree with the values 18, 15 and 11
(ii). Show the insert operations for other values one by one.
(iii). Search for the value 112.
(iv) Delete the value 13.
(v). Update the value 11 with 50.
MODULE -5

1. Break down the role of each ACID property and analyze the consequences of
their violation.
2. Justify the need for serializability in concurrent transaction processing with real-
life consequences of ignoring it.
3. Illustrate log-based recovery using undo and redo operations for a system crash
situation.
4. Analyze the differences between recoverable, cascadeless, and strict
schedules using appropriate examples.
5. Differentiate between shadow paging and log-based recovery protocols and
identify scenarios best suited for each.
 6. Recommend a suitable recovery strategy for a distributed database system
based on the nature of updates and failures.
7. Design a transaction schedule that is recoverable but not cascadeless, and
explain the implications.
8. Assess the pros and cons of using immediate update recovery methods in
mission-critical systems.

MODULE -6

1. Implement a concurrency control mechanism using time-stamp ordering

protocol for two conflicting transactions.
2. Illustrate the Thomas Write Rule with a practical example and show how it
differs from basic time-stamp protocol.
3. Analyze the difference between lock-based and timestamp-based concurrency
control techniques.
4. Compare the working of two-phase locking protocol with the tree protocol in
terms of transaction safety and performance.
5. Assess the trade-offs between pessimistic and optimistic concurrency control
methods.
6. Devise your own deadlock prevention strategy using ordering of resource
allocation and explain how it works.
7. Create a complete locking protocol that includes lock conversion, compatibility
checks, and deadlock avoidance.
8. Critically evaluate how multi-granularity locking can help optimize database
performance.
MODULE -7

1. Explain how the CAP theorem influences the design of distributed NoSQL
systems.
2. Compare the features of Document databases and Graph databases with real-
world use cases.
3. Write short notes on:
a) Column-family databases
b) Key-value stores
4. Explain the different types of NoSQL databases in detail. Highlight their data
models, use cases, and examples.
5. Discuss in detail how NoSQL databases support Big Data analytics and give
examples from industry (e.g., e-commerce, IoT, finance).
6. Compare RDBMS and NoSQL databases in terms of schema design, query
languages, scalability, and consistency.