DBMS CHAPTER WISE QUESTONS COLLECTION

---

Unit 1: Database and Database Users

This unit introduces the fundamental concepts of the database approach and the various people involved.

• What are the advantages of using a Database Management System (DBMS) over a traditional file system? 

  [Repeated: 2076, 2080]

• What are the characteristics of the database approach? 

  (2080)

• Who are the different types of database users ("actors on the scene") and what are their roles? 

  (2078)

---

Unit 2: Database System – Concepts and Architecture

Here, you'll find questions about the structure of a DBMS, including its architecture and the models it uses.

• Explain the ANSI/SPARC three-schema architecture with a suitable diagram. ^44444444444

  [Repeated: 2079, 2080, 2081]

• Define data independence and explain its types (logical and physical). ^{5555555555555555}

  [Repeated: 2078, 2079, 2080]

• Define the terms schema and instance in a DBMS with examples. ⁶⁶⁶⁶

  [Repeated: 2079, 2080]

• What is data abstraction? Explain the three levels of data abstraction. ⁷⁷⁷⁷

  [Repeated: 2076, 2080]

• Classify or explain different types of data models. ⁸

  [Repeated: 2076, 2081]

• Define the terms data, database, DBMS, database system, and database catalog. ⁹

  (2081)

• Differentiate between Centralized and Client/Server Architectures for DBMS. ¹⁰

  (2080)

• What are the functions of a database administrator (DBA)? ¹¹

  (2080)

---

Unit 3: Data Modeling Using the Entity-Relational Model

This section focuses on designing databases using the ER model, a crucial conceptual design tool. 📈

• Explain specialization and generalization, including constraints like the disjoint constraint. ^{121212121212121212}

  [Repeated: 2080, 2081]

• What do you mean by entity type and entity set? Explain with an example. ¹³¹³¹³

  [Repeated: 2076, 2081]

• Construct an ER diagram for an airline ticket booking system. The system should provide discounts based on the number of tickets bought, keep records of buyer visit frequency, and filter unwanted visitors. ¹⁴

  (2081)

• Construct an E-R diagram for an online course registration system where students register for courses online. ¹⁵

  (2080)

• What is a conceptual data model? Explain the different types of attributes used in an ER diagram. ¹⁶

  (2080)

• Write a short note on the Weak entity type. ¹⁷

  (2080)

• Explain how to reduce different types of entities and relationships of an ER diagram into tables. ¹⁸

  (2079)

• Explain aggregation with an example. ¹⁹

  (2079)

• What are the components of an ER diagram? Explain the function of various symbols used and construct an ER diagram for a college library. ²⁰

  (2078)

• Explain Relationship and Relationship sets with an example. ²¹

  (2078)

---

Unit 4: The Relational Data Model and Relational Database Constraints

Questions here cover the concepts of the relational model, which is the foundation for most modern databases like SQL.

• What do you mean by referential integrity? Why is it needed? ^22222222

  [Repeated: 2080, 2081]

• Explain the fundamental characteristics of a relation and define the terms domain, attribute, tuple, and relation. ^23232323

  [Repeated: 2080]

• Explain different types of database integrity. ²⁴

  (2078)

• Explain the use of primary and foreign keys in a DBMS and the role of a foreign key. ²⁵

  (2076)

• Differentiate between Integrity and Security with an example. ²⁶

  (2076)

---

Unit 5: The Relational Algebra and Relational Calculus

This unit covers the formal, procedural (Algebra) and declarative (Calculus) query languages that underpin SQL.

• What is tuple relational calculus? Explain with an example. ^27272727

  [Repeated: 2080]

• Explain the relational algebra natural join (*) operation with an example. ^28282828

  [Repeated: 2080, 2081]

• Explain the six fundamental operations of Relational Algebra with suitable examples. ²⁹

  (2080)

• Given the schema:

  TEACHER(TID, TName), SCHOOL(SID, SName), and SCHOOL_TEACHER(SID, TID, No_of_Period), write a Relational Algebra query to retrieve the TName and No_of_period of teachers who teach in "ABC" school. ^30303030

  (2079)

• Explain the difference between the "Join" and "Natural Join" operations with an example. ³¹

  (2078)

• Note: Many questions require writing queries in both Relational Algebra and SQL. Those are listed in the next unit for convenience.

---

Unit 6: SQL

The most practical unit, focusing on writing queries using the standard database language, SQL. 💻

• For the Library schema: USER(UserID, User_name), USES(UserID, BookID), BOOK(BookID, Book_name, Author, PublisherID), PUBLISHER(PublisherID, Publisher_name), write SQL and Relational Algebra queries to:

  • Retrieve names of all books issued to the user "Hari". ³²

    (2081)

  • Retrieve the names of all books published by "ABC publication". ³³

    (2081)

  • Count the number of books published by the author "Ram". ³⁴

    (2081)

• For the Banking (Loan) schema: Customer(CustomerID, CustomerName, Address), Borrows(CustomerID, LoanNumber), Loan(LoanNumber, LoanType, Amount), write SQL and Relational Algebra queries to:

  • Display the names of customers in "Lalitpur" in ascending order. ³⁵

    (2080)

  • Count the total number of customers who have a loan. ³⁶

    (2080)

  • Find the names of customers with a loan amount >= 500,000. ³⁷

    (2080)

  • Find the average loan amount for each loan type. ³⁸

    (2080)

• For the Purchase schema: Customer(Cno, Cname), Purchase(Cno, Pid), Product(Pid, Pname, price, quantity), write SQL queries to:

  • Find the names of all products with a price of 1000. ³⁹

    (2080)

  • Find the names of customers who purchased a 'Dell Laptop'. ⁴⁰

    (2080)

  • Find the total number of products purchased by customer 'Ram'. ⁴¹

    (2080)

  • Increase the price of all products by 5%. ⁴²

    (2080)

• For the Banking (Account) schema: Customer(CustomerID, CustomerName, Address), Owns(CustomerID, AccountNumber), Account(AccountNumber, AccountType, Balance), write SQL and Relational Algebra queries to:

  • Display the names of customers in "Kathmandu". ⁴³

    (2080)

  • Count the total number of customers. ⁴⁴

    (2080)

  • Find the names of customers with a balance >= 100,000. ⁴⁵

    (2080)

  • Find the average balance for each account type. ⁴⁶

    (2080)

• Explain Assertion and Triggers with an example. ⁴⁷

  (2079)

• Given the schema

  TEACHER(TID, TName), SCHOOL(SID, SName, SPhone), and SCHOOL_TEACHER(SID, TID), write an SQL query to retrieve the TName, SName, and SPhone for "ABC" school. ⁴⁸

  (2078)

• Create two tables

  Courses(CID, Course, Dept) and HoD(Dept, Head) using SQL, including all constraints (Primary key, Foreign key, and Referential Integrity). ⁴⁹

  (2076)

---

Unit 7: Relational Database Design

This unit covers the theory behind good database design, focusing on functional dependencies and normalization to reduce redundancy.

• What is normalization? Why is it required? Explain 1NF, 2NF, and 3NF with examples. ^{50505050505050505050505050505050}

  [Repeated: 2079, 2080]

• What are informal design guidelines for relational schemas? ^515151515151

  [Repeated: 2080, 2081]

• What is a functional dependency? Explain why we need them, their types (trivial/non-trivial), and the role of inference rules. ^{52525252525252525252525252525252}

  [Repeated: 2078, 2079, 2080]

• Define Boyce-Codd Normal Form (BCNF) with an example. How is it different from 3NF? ⁵³

  (2078)

• What is a normal form? Explain their types and discuss loss-less join decomposition. ⁵⁴

  (2076)

---

Unit 8: Introduction to Transaction Processing Concepts and Theory

This unit introduces transactions and the theory of scheduling them correctly to maintain data consistency.

• What is a schedule? Explain serializability and how you can test for it. ^55555555

  [Repeated: 2076, 2078]

• Define a transaction and explain its desirable ACID properties. ^{5656565656565656}

  [Repeated: 2080, 2081]

• Explain conflict-serializability with an example. ^57575757

  [Repeated: 2079, 2081]

• Explain schedules based on recoverability and serializability. ⁵⁸

  (2080)

• Write a short note on Transaction processing. ⁵⁹

  (2080)

• Draw the states of a transaction and explain them. ⁶⁰

  (2079)

---

Unit 9: Concurrency Control Techniques

These questions are about the techniques used to manage simultaneous access to the database by multiple users. 🚦

• Why do we need concurrency control? ^{616161616161616161616161}Discuss the two-phase locking (2PL) protocol, including its types (basic, conservative, strict, rigorous). ^{62626262626262}

  [Repeated: 2076, 2080]

• Explain deadlock with an example. Discuss different deadlock prevention protocols, deadlock detection, and starvation. ^{6363636363636363}

  [Repeated: 2078, 2080, 2081]

• Explain how the timestamp ordering protocol is used for concurrency control. ^64646464

  [Repeated: 2078, 2080]

• What is the Granularity of data items and how does it affect concurrency control? ⁶⁵

  (2076)

• Differentiate between a Binary lock and a Shared/Exclusive lock. ⁶⁶

  (2076)

---

Unit 10: Database Recovery Techniques

This final unit covers how a DBMS recovers from failures (like system crashes or disk failures) to ensure data is not lost. 🛡️

• Why is database recovery essential? ^676767676767Explain the recovery technique based on immediate update. ^68686868

  [Repeated: 2080]

• Why do we need database recovery? ⁶⁹⁶⁹Discuss the shadow paging technique. ^70707070

  [Repeated: 2080]

• Explain the deferred update approach in database recovery. ⁷¹

  (2081)

• What is Buffer Management in DBMS? Explain. ⁷²

  (2079)

• What are Checkpoints in database recovery? How do they help? ⁷³

  (2078)

• What are the different approaches to Database recovery? ⁷⁴What should a log file maintain in log-based recovery? ⁷⁵

  (2076)