NALLA MALLA REDDY ENGINEERING COLLEGE

AUTONOMOUS INSTITUTION
R22(2023-24) – B. TECH II Year I Sem

SUBJECT: DATABASE MANAGEMENT SYSTEMS (22CS305PC) Branch: CSE- A/B/C

Question Bank
UNIT-I
1-Mark Questions (20 Questions)

1. Which of the following best describes a database management system (DBMS)?

A) A software application used to manage computer hardware
B) A collection of interrelated data and programs used to access that data
C) A system to manage electronic files on a computer
D) A program for editing documents

2. Which of the following is the primary function of the conceptual level in a DBMS?
A) To define how data is stored on disk
B) To define the overall logical structure of the database
C) To create user-specific views of the database
D) To provide security and access control

3. Which of the following statements is true about file systems and DBMS?
A) File systems offer better data consistency than DBMS.
B) File systems are generally preferred over DBMS for managing large datasets.
C) DBMS supports data sharing and concurrency control, while file systems do not.
D) File systems offer better security features than DBMS

4. Which of the following is NOT a level of data abstraction in a DBMS?

A) Physical level
B) Conceptual level
C) User view level
D) Application level

5. In an ER diagram, an attribute that uniquely identifies each entity in an entity set is called a:
A) Primary key
B) Foreign key
C) Unique key
D) Relationship key

6. In an ER diagram, a relationship that involves three entity sets is called a:

A) Unary relationship
B) Binary relationship
C) Ternary relationship
D) N-ary relationship

7. What is the purpose of a foreign key in a relational database?

A) To uniquely identify records within a table
B) To establish and enforce a link between tables
C) To delete records automatically when referenced data is deleted
D) To optimize query processing

8. An entity set that contains a set of attributes used to uniquely identify each entity in the set is said
to have:
A) Referential integrity
B) Key constraint
C) Domain constraint
D) Partial dependency
9. In database design, the process of mapping entities and relationships from a real-world scenario to
a conceptual schema is known as:
A) Logical modeling
B) Physical modeling
C) Data independence
D) Conceptual modeling

10. In the ER model, a weak entity set is:

A) An entity that can be identified without any additional data
B) An entity that cannot exist without being associated with another entity
C) An entity that has only optional attributes
D) An entity with no primary key

11. Define the terms data and information?

12. Define (i) Database (ii)DBMS
13. List the advantages and applications of DBMS?
14. Define instances and schemas of database?
15. What is data model? List the types of data models?
16. List DDL and DML Commands ?
17. Discuss Data Independence?
18. What is an entity relationship model?
19. Define (i) Entity (ii) Attribute
20. Define Relationship and Relationship set?

5-Mark Questions (10 Questions)

1. Outline key milestones in database history, the problems associated with file systems, and the
improvements that DBMS introduced.
2. Compare and Contrast file Systems with database system?
3. Describe the three levels of data abstraction in a DBMS and explain how they contribute to data
independence.
4. Define data independence in the context of a DBMS and discuss its significance. How do logical and
physical data independence differ?
5. Discuss about different types of Data models?
6. Explain different types of database users and write the functions of DBA?
7. Discuss about the Conceptual Design with the ER-Model?
8. Outline the steps from identifying entities and relationships to defining attributes and constraints in
an ER model.
9. Differentiate between relationships and relationships sets in an ER model and discuss how they are
represented in an ER diagram.
10.Discuss additional features of the ER-Models?

10-Mark Questions (5 Questions)

1. Explain the concept of a DBMS and analyze the limitations of file systems. Discuss how a DBMS
effectively addresses these challenges.
2. Explain the architecture of a DBMS in detail. Describe the role and interactions of each component,
including the storage layer, query processor, and transaction manager.
3. Illustrate how ER diagrams are used to represent database structure. Discuss the various symbols and
conventions used to represent entities, relationships, attributes, and constraints.
4. Describe the various components of an ER model and explain in detail how they are represented?"
5. Examine the additional features of the ER model, such as generalization, specialization, weak entities,
and aggregation. Discuss how these features enhance the flexibility of the ER model in database design.
UNIT-II
1-Mark Questions (20 Questions)

1. What is an integrity constraint in the relational model?

A) A rule that allows data redundancy in tables.
B) A rule that enforces data accuracy and consistency within tables.
C) A query operation that retrieves specific data from tables.
D) A view that restricts access to certain rows in a table.

2. Which of the following is NOT a type of integrity constraint?

A) Entity Integrity
B) Domain Integrity
C) Foreign Key Integrity
D) Data Encryption Integrity

3. In SQL, which keyword is used to create a view?

A) CREATE VIEW
B) DEFINE VIEW
C) SELECT VIEW
D) NEW VIEW

4. In relational algebra, what does the "SELECT" (σ) operation do?

A) Combines tuples from different relations.
B) Projects specific attributes from a relation.
C) Retrieves tuples that satisfy a certain condition.
D) Renames a relation or attributes.

5. Which operation in SQL is used to permanently remove a table or view from a database?
A) DROP
B) DELETE
C) REMOVE
D) ALTER

6. Which relational algebra operation is used to combine tuples from two relations?
A) Union
B) Intersection
C) Projection
D) Selection

7. What does the "DELETE" operation do in a relational database?

A) Removes specific rows from a table.
B) Removes a table and all its data from the database.
C) Alters the structure of a table.
D) Removes an entire database schema.

8. The "PROJECT" (π) operation in relational algebra is used to:

A) Retrieve all rows that meet a certain condition.
B) Retrieve specific columns from a relation.
C) Combine rows from two relations.
D) Delete certain rows from a relation

9. In Tuple Relational Calculus, a query is expressed as:

A) A series of ordered pairs.
B) A description of the data to retrieve using tuples.
C) A sequence of relational algebra operations.
D) A command in SQL syntax.
10. In Domain Relational Calculus, which of the following symbols is commonly used to represent
variables?
A) ∃ (Exists)
B) ∀ (For All)
C) π (Projection)
D) Both A and B
11. What is a primary key in a relational database?
12. Define a foreign key.
13. What does the term 'referential integrity' mean in a database?
14. What does the SELECT (σ) operation do in relational algebra?
15. What is the purpose of the PROJECT (π) operation in relational algebra?
16. What does the JOIN operation do in relational algebra?
17. What is the purpose of a view in a database?
18. Name one type of integrity constraint in relational databases.
19. Define nested queries?
20. 16 Define correlated nested queries?

5-Mark Questions (10 Questions)

1. Explain about different types of integrity constraints?
2. Compare querying relational data using SQL and relational algebra.
3. Outline the steps in logical database design.
4. What are views in a relational database, and what are their benefits and limitations?
5. Illustrate different operations in Relational algebra with an example?.
6. Compare and Contrast Domain Relational with Tuple Relational Calculus.
7. Explain the role of primary and foreign keys in maintaining relationships between tables.
8. How does the JOIN operation in relational algebra help in combining tables?
9. Explain different types of Join Operations in relational algebra.
10. How and why would you alter or destroy tables and views in a database?

10-Mark Questions (5 Questions)

1. Explain how the integrity constraints are specified and enforced.
2. What are the benefits and challenges of using views in a relational database? Include examples of
scenarios where views are especially useful.
3. How does relational algebra support logical database design and query optimization? Discuss with
examples of its use in forming complex queries.
4. Explain the role of the SELECT, PROJECT, and JOIN operations in relational algebra. Provide
examples of how each operation is used in querying data.
5. What is the importance of the DELETE CASCADE and UPDATE CASCADE options in maintaining
referential integrity? Give examples.
UNIT-III
1-Mark Questions (20 Questions)

1. What does the following SQL query do? SELECT * FROM Students WHERE Age > 21;
A) Retrieves all columns from the Students table where Age is less than 21.
B) Retrieves all columns from the Students table where Age is greater than 21.
C) Retrieves only the Age column from the Students table.
D) Retrieves all students who are exactly 21 years old.

2. Which of the following SQL commands is used to combine results from two queries while excluding
the duplicate rows?
A) INTERSECT
B) UNION
C) EXCEPT
D) UNION ALL

3. In SQL, which of the following statements is used to return common rows between two queries?
A) UNION
B) INTERSECT
C) EXCEPT
D) JOIN

4. What is a nested query in SQL?

A) A query that retrieves data from multiple tables.
B) A query inside another query.
C) A query that only uses aggregate functions.
D) A query that modifies the structure of a table.

5. Which SQL operator is used to remove rows from the result of a query that appear in another
query's result?
A) UNION
B) INTERSECT
C) EXCEPT
D) SELECT

6. Which of the following SQL functions is used to count the number of rows returned by a query?
A) SUM()
B) COUNT()
C) AVG()
D) MAX()
E)
7. In the context of database normalization, which of the following is the main issue with a table that
has redundant data?
A) Loss of integrity
B) High storage requirement
C) Inefficient queries
D) All of the above

8. Which of the following normal forms addresses the problem of partial dependency in a relational
schema?
A) First Normal Form (1NF)
B) Second Normal Form (2NF)
C) Third Normal Form (3NF)
D) Boyce-Codd Normal Form (BCNF)

9. What is the purpose of the TRIGGER in SQL?

A) To enforce integrity constraints
B) To automatically update a table when data changes
C) To combine multiple queries
D) To remove duplicates from query results
10. Which of the following is the primary goal of lossless join decomposition?
A) Reduce redundancy
B) Preserve data consistency
C) Ensure that no data is lost during decomposition
D) Create a smaller number of tables

11. Which SQL operator combines the result sets of two queries and removes duplicate rows?
12. What does the INTERSECT operator in SQL do?
13. How does the EXCEPT operator in SQL differ from the UNION operator?
14. What is the purpose of the COUNT() function in SQL?
15. In SQL, what is the result of a comparison between a value and NULL?
16. Define a foreign key constraint in SQL.
17. What is the role of a trigger in an SQL database?
18. Which normal form resolves the issue of partial dependency in relational schemas?
19. Explain the concept of lossless join decomposition in database design.
20. Which normal form is designed to eliminate transitive dependencies in a relational schema?

5-Mark Questions (10 Questions)

1. Explain the different types of SQL operators, including UNION, INTERSECT, and EXCEPT, with
examples.
2. What are nested queries in SQL? Explain how they are used with an example.
3. Discuss the role of aggregation operators in SQL and provide examples of their usage.
4. Explain the significance of handling NULL values in SQL. How do you query for NULL values in a
database?
5. Define complex integrity constraints in SQL. Provide examples of primary key, foreign key, and
check constraints.
6. What are triggers in SQL? Discuss how they contribute to maintaining data integrity in an active
database.
7. Discuss the problems caused by redundancy in databases and how schema refinement addresses
these issues.
8. Explain the concepts of First, Second, and Third Normal Forms (1NF, 2NF, and 3NF) with examples.
9. What is Boyce-Codd Normal Form (BCNF)? How does it differ from Third Normal Form (3NF)?
10. What is Lossless Join Decomposition? Explain its significance with an example.

10-Mark Questions (5 Questions)

1. Discuss schema refinement in databases, focusing on redundancy issues and how functional
dependencies and normal forms address them.
2. Compare 1NF, 2NF, and 3NF with examples to explain how they handle redundancy and dependency
issues.
3. Explain BCNF and how it differs from 3NF. Provide an example of a relation that needs
decomposition to BCNF.
4. What are SQL triggers? Discuss their role in enforcing business rules and data integrity with
examples.
5. Explain the usage of UNION, INTERSECT, and EXCEPT in SQL. Provide examples and highlight
their differences.
UNIT-IV
1-Mark Questions (20 Questions)

1. Which of the following is NOT a valid transaction state?

A) Active
B) Partially committed
C) Aborted
D) Finished

2. What property of a transaction ensures that either all operations are completed, or none
are?
A) Durability
B) Isolation
C) Atomicity
D) Consistency

3. Which protocol uses timestamps to order transactions?

A) Two-Phase Locking Protocol
B) Timestamp-Based Protocol
C) Validation-Based Protocol
D) Multiple Granularity

4. What ensures that a transaction’s changes are permanent in the database after it commits?
A) Serializability
B) Isolation
C) Durability
D) Recoverability

5. In lock-based protocols, which type of lock allows both read and write operations?
A) Shared Lock
B) Exclusive Lock
C) Read Lock
D) Write Lock

6. A schedule is considered serializable if it is equivalent to

A) A concurrent schedule
B) A serial schedule
C) A recoverable schedule
D) A durable schedule

7. Which type of recovery mechanism uses logs to undo or redo operations?

A) Shadow Paging
B) Checkpointing
C) Log-Based Recovery
D) Validation Protocol

8. What is the main purpose of the two-phase locking protocol?

A) Ensure durability
B) Ensure atomicity
C) Ensure recoverability
D) Ensure serializability

9. In multiple granularities locking, which lock mode is used to lock a node explicitly for both read
and write operations?
A) Shared Lock (S)
B) Exclusive Lock (X)
C) Intention-Exclusive Lock (IX)
D) Intention-Shared Lock (IS)
10. Which of the following is true about validation-based protocols?
A) They use locks to prevent conflicts.
B) They validate transactions before they are executed.
C) They validate transactions at the end of their execution.
D) They rely on timestamps for conflict resolution.

11. Define a Transaction? List the properties of transaction

12. Discuss different phases(states) of transaction?
13. What is shadow copy technique?
14. List the advantages of concurrent execution?
15. Define Schedule? What is a serial schedule?
16. Discuss the Procedure to test Serializability?
17. Demonstrate Conflict Serializability?
18. Define Deadlock?
19. Explain about locking protocols?
20. Explain different timestamps used by validation protocol?

5-Mark Questions (10 Questions)

1. Explain ACID properties and illustrate them through examples?

2. Illustrate Concurrent execution of transaction with examples
3. Discuss Serializability in detail?
4. Discuss two phase locking protocol and strict two-phase locking protocols?
5. Describe Times tamp based locking protocols?
6. Describe Validation-based locking protocols?
7. Discuss in detail Multiple Granularity?
8. Explain in detail storage structure
9. Discuss how do you recover from failure?
10. Explain different types of advanced recovery techniques

10-Mark Questions (5 Questions)

1. Define a transaction and its states. Explain how atomicity and durability are implemented to ensure
reliability.
2. What is serializability in database systems? Describe how precedence graphs are used to test for
serializability.
3. Explain the principles of lock-based protocols, focusing on two-phase locking and its role in
maintaining isolation.
4. Discuss log-based recovery in databases, including its process during concurrent transactions and
the importance of checkpoints.
5. How does recovery with concurrent transactions handle transaction failures? Illustrate the process
with an example.
UNIT-V
1-Mark Questions (20 Questions)

1. What is the main purpose of indexing in a database?

A) To reduce redundancy
B) To enforce data integrity
C) To speed up data retrieval
D) To ensure transaction atomicity

2. Which of the following is NOT a type of file organization?

A) Sequential file organization
B) Heap file organization
C) Clustered file organization
D) D. Dynamic file organization

3. In a cluster index, data is stored:

A) In a random order
B) Sequentially in relation to the clustering key
C) Based on a hash function
D) Without any specific order

4. Which type of index is created on a non-primary key column?

A) Cluster index
B) Primary index
C) Secondary index
D) Unique index

5. Which index structure uses a hash function to map keys to data locations?
A) B+ Trees
B) ISAM
C) Hash-based indexing
D) Clustered index

6. What is the main advantage of B+ Trees over Binary Search Trees for indexing?
A) Supports dynamic indexing
B) Reduces redundancy
C) Requires less memory
D) Simplifies transaction processing

7. Indexed Sequential Access Methods (ISAM) are most suitable for:

A) Static database
B) Dynamic databases
C) Real-time applications
D) Unstructured data

8. In a B+ Tree, leaf nodes are:

A) Connected to each other sequentially
B) Unconnected and independent
C) Organized in a hash table
D) Always empty

9. Which file organization is best for searching data based on a range of values?
A) Heap file organization
B) Hash-based indexing
C) Sequential file organization
D) Indexed file organization
10. What does the term "fan-out" in a B+ Tree refers to?
A) The number of child pointers per node
B) The height of the tree
C) The number of leaf nodes
D) The size of each node

11. Discuss about data on External storage?

12. What is indexing and what are the different kinds of indexing?
13. Explain Clustered Indexes?
14. Discuss the Primary and Secondary indexes?
15. Define Tree Indexing?
16. Explain how search is performed in B+ tree?
17. Define static Hashing?
18. Explain extendible hashing?
19. Define linear hashing?
20. Differentiate between linear and extensible hashing?

5-Mark Questions (10 Questions)

1. Write in detail about hash-based indexing and Tree based indexing

2. Compare I/O costs for all file organizations
3. Explain in detail about ISAM
4. Explain about B+ tree index file?
5. Illustrate insertion of an element in B+ Tree with example
6. Illustrate deletion of an element in B+ Tree with example
7. Explain in detail about Extendible hashing
8. Explain in detail about Linear hashing
9. Describe the key differences between ISAM and dynamic indexing structures.
10. How does multiple-level indexing improve database performance?

10-Mark Questions (5 Questions)

1. Differentiate between primary and secondary indexes with examples.

2. Compare sequential, heap, and clustered file organizations.
3. Compare and contrast Extendible hashing With Linear hashing
4. Demonstrate searching a given element in B+ trees? Explain with example?
5. Explain the Indexed Sequential Access Method (ISAM) and compare it with B+ Trees.