DBMS Question Bank - Endsem PDF

DBMS Question Bank Unit 1: Introduction to Databases 2 Marks Questions: 1. Define Database Management System (DBMS)? 2. Define Data 3. Define information 4. Define Database 5. List two types of data models. 6. Mention two advantages of data management...

DBMS Question Bank Unit 1: Introduction to Databases 2 Marks Questions: 1. Define Database Management System (DBMS)? 2. Define Data 3. Define information 4. Define Database 5. List two types of data models. 6. Mention two advantages of data management in organizations. 7. Name any two types of DBMS. 8. List any two differences between Relational and Object-Oriented data models. 5 Marks Questions: 1. Explain the evolution of Database Management Systems (DBMS) with examples. 2. Discuss the differences between relational and object-oriented database models. 3. Describe the features of hierarchical and network database models. 4. Explain the importance of data management and its impact on modern systems. 5. Describe the Entity-Relationship model and its components. 6. Discuss the advantages and disadvantages of object-oriented databases. Unit 2: Relational Database Management System (RDBMS) 2 mark Questions: 1. What is data model? 2. Define primary and foreign keys. 3. Name the types of keys in RDBMS. 4. Name any two types of SQL joins. 5. Write any two aggregate functions with syntax and exmaples. 6. What is the purpose of a primary key in a relational database? 7. Define a foreign key and its role in relationships between tables. 8. What is the difference between a candidate key and a super key? 9. Name the three normal forms. 10.What is the purpose of the GROUP BY clause in SQL?  11.What is a subquery in SQL? 12.What are the basic SQL data types? 13.Write a simple SQL SELECT statement to retrieve all columns from a table named "customers". 14.Define weak entity 15.Define indexing 16.List the types of indexing 5-Mark Questions 1. Describe the Entity-Relationship (ER) model and its components. How is it used to design relational databases? 2. Explain Table, rows and columns in Relational Model. 3. Differentiate between weak and strong entity. 4. What is relational data model? Explain with different terminologies. 5. Give example of following relationships : a. Many-to-One b. One-to-One c. One-to-Many d. Many-to-Many 6. What is an Entity, Entity type, and Entity set? 7. Explain the process of normalization in detail, Give an example to illustrate each step. 8. Explain the different types of keys (Primary, Foreign, Candidate, Super) with examples. 9. Describe the SQL data types and their uses with examples. 10. How do SQL subqueries work? Give an example. 11.Explain the concept of relational data model and its advantages. 12. Explain basic SQL queries (SELECT, INSERT, UPDATE, DELETE) with example. 13. Write a note on SQL Constraints. 14. Write a note on SQL Commands. 15. Explain various types of clauses used in SQL. Unit 3 Database Design 2 Marks Questions 1. Define conceptual database design 2. Define logical database design 3. Define physical database design. 4. What is the difference between a schema and an instance? 5. Explain the importance of data integrity in database design. 6. Define indexing in a database and its purpose.  7. What is query optimization in database management? 8. What is the role of constraints in maintaining data integrity? 5 Marks Questions 1. Describe the three levels of database design: conceptual, logical, and physical, with examples. 2. Explain the difference between schema and instance with an example. 3. Discuss the various types of data integrity constraints in databases. 4. What is query optimization? Discuss techniques used for optimizing database queries. 5. Describe the process of denormalization and its impact on database performance. 6. How do primary keys and foreign keys help maintain referential integrity in a database? Unit 4 Transaction Management 2 Marks Questions 1. Define the ACID properties in transaction management. 2. What does consistency mean in transaction management? 3. What is the purpose of isolation in database transactions? 4. Define durability in transaction processing. 5. What is concurrency control in a database system? 6. What is schedule. 7. List types of schedule. 8. What is serializability. 9. Explain the concept of deadlock prevention in databases. 10.Define the term "deadlock" in DBMS. 11.What is transaction processing? 12.List two concurrency control techniques. 13.list are the types of operations used in transaction. 5 Marks Questions 1. Explain the ACID properties with examples for each. 2. Explain Transaction State in detail. 3. Discuss the importance of transaction processing in database systems. 4. Describe different concurrency control techniques used in databases. 5. What are the causes of deadlocks in transaction management, and how can they be avoided? 6. What is deadlock? Explain deadlock prevention, avoidance, and detection.  7. How do database systems ensure durability in case of system failure? 8. Describe a scenario where a lack of concurrency control leads to data inconsistency, and explain how to resolve it. 9. What is the role of precedence graphs in determining if a schedule is serializable? 10. Define serializability and explain its importance in transaction scheduling Unit 5: Database Security and Authentication 2 Marks Questions 1. Define database security and its importance. 2. What is user authentication in a database? 3. Differentiate between authentication and authorization. 4. Define Role-Based Access Control (RBAC). 5. List any two common database security threats. 6. What is data privacy in database systems? 7. What is SQL injection? 8. Name two countermeasures to protect a database from unauthorized access. 5 Marks Questions 1. Explain the Role-Based Access Control (RBAC) model with an example. 2. Describe the process of user authentication and its mechanisms in database systems. 3. What is a Database Management System and how is it different from a File System? 4. Discuss the different levels of access control in database security. 5. What are the primary goals of data security in a database? 6. What are the common security threats faced by databases? Discuss their countermeasures. 7. Describe the impact of SQL injection attacks and how to prevent them. Draw an ERD Questions: 1. Create an ERD for a hotel booking system with entities: Guests, Rooms, and Reservations. give relationship in tables Guests Table ( Guest_ID (Primary Key),Name, Contact_Number,Email) Rooms Table (Room_ID (Primary Key),Room_Type, Room_Rate, Availability_Status)  Reservations Table (Reservation_ID (Primary Key), Check_In_Date, Check_Out_Date, Guest_ID (Foreign Key referencing Guests), Room_ID (Foreign Key referencing Rooms) Guests and Reservations : One-to-Many Relationship Rooms and Reservations: One-to-Many Relationship 2. Develop an ERD for an e-commerce platform including Customers, Orders, and Products entities. Customers Table (Customer_ID (Primary Key) ,Name, Email, Phone_Number, Address) Products Table ( Product_ID (Primary Key), Product_Name, Price, Stock_Quantity) Orders Table( Order_ID (Primary Key), Order_Date, Total_Amount, Customer_ID (Foreign Key referencing Customers) Customers and Orders: One-to-Many Relationship: Orders and Products : Many-to-Many Relationship: Products and OrderDetails: One-to-Many Relationship: 3. Draw an ERD for a university database system, including Students, Courses, Instructors, and Departments. Students Table (Student_ID (Primary Key),Name,Email,Phone_Number, Department_ID (Foreign Key referencing Departments) Courses Table (Course_ID (Primary Key), Course_Name, Credits, Department_ID (Foreign Key referencing Departments) Instructors Table (Instructor_ID (Primary Key), Name, Email, Department_ID (Foreign Key referencing Departments) Departments Table (Department_ID (Primary Key) , Department_Name , Location) Departments and Students: One-to-Many Relationship Departments and Courses : One-to-Many Relationship. Departments and Instructors: One-to-Many Relationship Students and Courses: Many-to-Many Relationship 4. Design an ERD for a car rental system with entities: Cars, Customers, and Rentals. Cars Table ( Car_ID (Primary Key), Make, Model, Year, Availability_Status, Rental_Rate_Per_Day) Customers Table( Customer_ID (Primary Key), Name, Contact_Number  Email , Address) Rentals Table( Rental_ID (Primary Key), Rental_Date, Return_Date, Total_Cost, Customer_ID (Foreign Key referencing Customers), Car_ID (Foreign Key referencing Cars)) Relationships: Cars and Rentals: One-to-Many Relationship Customers and Rentals : One-to-Many Relationship Transaction Management Problems: 1. Consider the following schedule, determine if the schedule is conflict-serializable. (Draw precedence graph) Schedule S T1 T2 T3 Read(A) Read(A) Write(A) Write(A) Read(B) Read(B) Write(B) Write(B) 2. Check whether the following schedule is conflict-serializable: (Draw precedence graph) Schedule S T1 T2 T3 Read(A) Read(A) Read(B) Write(B) Write(A) Write(A) Read(B) Write(B) 3. Construct a precedence graph for the following schedule and determine if it is serializable: (Draw precedence graph)  Schedule S T1 T2 Read(A) Read(A) Read(B) Write(A) Write (B) Non Serial Schedule Examples: 1. Consider the given schedule and covert it in to two non-serial schedule which is serializable. Schedule S1 T1 T2 Read(A) Read(A) A=A-10 Temp=2*A Write(A) Write(A) Read(B) Read(B) B=B+10 B=B+Temp Write(B) Write(B) 2. Consider the given schedule and covert it in to two non-serial schedule which is serializable. Schedule S1 Transaction T1 Transaction T2 Read(A) Read(A) A=A-50 A=A-100 Write(A) Write(A) Read(B) Read(B) B=B+50 B=B+100 Write(B) Write(B) 3. Consider the given schedule and covert it in to two non-serial schedule which is serializable.  Schedule S1 Transaction T1 Transaction T2 Read(A) Read(B) A=A-1000 B=B-100 Write(A) Write(B) Read(B) Read( C) B=B+100 C=C+50 Witre(B) Write(C) SQL Questions: 1. Consider the following entities and their relationships. Create a RDB in 3 NF for the following and answer the queries: Emp(eno ,ename ,designation ,salary,DOJ) Dept(dno,dname ,loc) The relationship between Dept & Emp is one-to-many. a) Insert at least five records into the tables. b) Display the names of employees who are working in “Quality Department”. c) Display the name of employee who is ‘Manager’ of “Purchase Department”. d) Display the name of department whose location is “Baramati” e) Display the names of employees whose salary is greater than 50000 and department is “Quality”. 2. Consider the following entities and their relationships. Create a RDB in 3 NF for the following and answer the queries: Hospital(hno ,hname , city, Est_year) Doctor(dno , dname , addr, Speciality) The relationship between Hospital and Doctor is one - to – Many a) Insert at least 5 records into the tables. b) Display the names of hospitals which are located at “Pimpri” city.  c) Display the names of doctors who are working in “Birla” Hospital and city name is “Chinchwad”. d) Display the specialty and name of doctor who is working in “Ruby” hospital. e) Display the names of doctors whose speciality is “medicine” 3. Consider the following Entities and Relationships Employee (emp_no, name, skill, payrate) Position (posting_no, skill) Relation between Employee and Position is Many to Many with day and shift as descriptive attribute. Constraint: Primary key, payrate should be > 0. Create a Database in 3NF & write queries for following. a) Find the names and rate of pay all employees who allocated a duty. b) Give employee number who are working at posting_no. 201. c) Display a list of names of employees who have skill of chef and who has assigned a duty. d) Display emp_no and dates for all employees who are working on Tuesday. e) Display shiftwise employee details.