2024 question paper
Database Management System
26 questions
Q1a. Which of the following is *not* a feature of the relational database model? (i) Tables with rows and columns (ii) Use of SQL (iii) Pre-defined data access paths (iv) Data independence20242m
Module 1: Database System Architecture
View this question on its own page →Which of the following is not a feature of the relational database model?
(i) Tables with rows and columns
(ii) Use of SQL
(iii) Pre-defined data access paths
(iv) Data independenceQ1b. Which SQL command is used to remove a table from a database? (i) REMOVE (ii) DELETE (iii) DROP (iv) CLEAR20242m
Module 2: Relational Query Languages & Design
View this question on its own page →Which SQL command is used to remove a table from a database?
(i) REMOVE
(ii) DELETE
(iii) DROP
(iv) CLEARQ1c. Which of the following is used for indexing in database systems? (i) Transaction log (ii) B-tree (iii) Views (iv) Triggers20242m
Module 3: Storage Strategies
View this question on its own page →Which of the following is used for indexing in database systems?
(i) Transaction log
(ii) B-tree
(iii) Views
(iv) TriggersQ1d. In ACID properties, which component ensures that once a transaction is committed, it remains so? (i) Atomicity (ii) Durability (iii) Isolation (iv) Consistency20242m
Module 4: Transaction Processing
View this question on its own page →In ACID properties, which component ensures that once a transaction is committed, it remains so?
(i) Atomicity
(ii) Durability
(iii) Isolation
(iv) ConsistencyQ1e. SQL injection attacks can be prevented by: (i) Dynamic SQL (ii) Input trimming (iii) Prepared Statements (iv) Using DELETE statements20242m
Module 5: Database Security
View this question on its own page →SQL injection attacks can be prevented by:
(i) Dynamic SQL
(ii) Input trimming
(iii) Prepared Statements
(iv) Using DELETE statementsQ1f. Which access control model assigns permissions to roles instead of individual users? (i) DAC (ii) MAC (iii) RBAC (iv) Firewall20242m
Module 5: Database Security
View this question on its own page →Which access control model assigns permissions to roles instead of individual users?
(i) DAC
(ii) MAC
(iii) RBAC
(iv) FirewallQ1g. Which of the following is an example of a DML command in SQL? (i) CREATE (ii) SELECT (iii) DROP (iv) ALTER20242m
Module 2: Relational Query Languages & Design
View this question on its own page →Which of the following is an example of a DML command in SQL?
(i) CREATE
(ii) SELECT
(iii) DROP
(iv) ALTERQ1h. Which of the following ensures no data is lost after a system crash? (i) Commit log (ii) View (iii) Recovery manager (iv) Trigger20242m
Module 4: Transaction Processing
View this question on its own page →Which of the following ensures no data is lost after a system crash?
(i) Commit log
(ii) View
(iii) Recovery manager
(iv) TriggerQ1i. 2NF removes which kind of dependency? (i) Transitive (ii) Partial (iii) Functional (iv) Multivalued20242m
Module 2: Relational Query Languages & Design
View this question on its own page →2NF removes which kind of dependency?
(i) Transitive
(ii) Partial
(iii) Functional
(iv) MultivaluedQ1j. Which of the following is true about object-oriented databases? (i) They use tables only (ii) They allow encapsulation (iii) No support for complex data (iv) Only support numeric data20242m
Module 6: Advanced Topics
View this question on its own page →Which of the following is true about object-oriented databases?
(i) They use tables only
(ii) They allow encapsulation
(iii) No support for complex data
(iv) Only support numeric dataQ2a. Consider the relation R (P, Q, S, T, X, Y, Z, W) with the following functional dependencies. PQ → X; P → YX; Q → Y; Y → ZW Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes. D1 : R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)] D2 : R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)] Identify whether it is a lossless or lossy decomposition and justify your answer.20247m
Module 2: Relational Query Languages & Design
View this question on its own page →Consider the relation R (P, Q, S, T, X, Y, Z, W) with the following functional dependencies.
PQ → X; P → YX; Q → Y; Y → ZW
Consider the decomposition of the relation R into the constituent relations according to the following two decomposition schemes.
D1 : R = [(P, Q, S, T); (P, T, X); (Q, Y); (Y, Z, W)]
D2 : R = [(P, Q, S); (T, X); (Q, Y); (Y, Z, W)]
Identify whether it is a lossless or lossy decomposition and justify your answer.Q2b. Employee (ename, street, city) Worksfor (ename, company_name, salary) Company (Company_name, city) Construct the SQL statements for the following (i) Create the above given tables with suitable data types and size. (ii) Find the name of employees who live in the same city where they work. (iii) Find the name of employees who have salary more than Rs. 50000. (iv) Find the name of employees who don't work in "tcs" company. (v) Find all employees whose name has second letter 'A'. (vi) Find the employee's name having second highest salary.20247m
Module 2: Relational Query Languages & Design
View this question on its own page →Employee (ename, street, city)
Worksfor (ename, company_name, salary)
Company (Company_name, city)Construct the SQL statements for the following
(i) Create the above given tables with suitable data types and size.
(ii) Find the name of employees who live in the same city where they work.
(iii) Find the name of employees who have salary more than Rs. 50000.
(iv) Find the name of employees who don't work in "tcs" company.
(v) Find all employees whose name has second letter 'A'.
(vi) Find the employee's name having second highest salary.Q3a. Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price), Collection (Title, Author, Catalog_no) with in the following functional dependencies: I. Title Author → Catalog_no II. Catalog_no → Title, Author, Publisher, Year III. Publisher Title Year → Price Analyze and find the highest normal form of the relation Book and Collection.20247m
Module 2: Relational Query Languages & Design
View this question on its own page →Consider the following relational schemes for a library database: Book (Title, Author, Catalog_no, Publisher, Year, Price), Collection (Title, Author, Catalog_no) with in the following functional dependencies:
I. Title Author → Catalog_no
II. Catalog_no → Title, Author, Publisher, Year
III. Publisher Title Year → PriceAnalyze and find the highest normal form of the relation Book and Collection.
Q3b. Consider the following schema for institute library: Student (RollNo, Name, Father_Name, Branch) Book (ISBN, Title, Author, Publisher) Issue (RollNo, ISBN, Date-of-Issue) Write the following queries in SQL and relational algebra: (i) List roll number and name of all students of the branch 'CSE'. (ii) Find the name of student who has issued a book published by 'ABC' publisher. (iii) List title of all books and their authors issued to a student 'RAM'. (iv) List title of all books issued on or before December 1, 2020. (v) List all books published by publisher 'ABC'20247m
Module 2: Relational Query Languages & Design
View this question on its own page →Consider the following schema for institute library:
Student (RollNo, Name, Father_Name, Branch)
Book (ISBN, Title, Author, Publisher)
Issue (RollNo, ISBN, Date-of-Issue)Write the following queries in SQL and relational algebra:
(i) List roll number and name of all students of the branch 'CSE'.
(ii) Find the name of student who has issued a book published by 'ABC' publisher.
(iii) List title of all books and their authors issued to a student 'RAM'.
(iv) List title of all books issued on or before December 1, 2020.
(v) List all books published by publisher 'ABC'Q4a. Explain schedule and transaction. Define the concepts of recoverable, cascade less, and strict schedules, and compare them in terms of their recoverability.20247m
Module 4: Transaction Processing
View this question on its own page →Explain schedule and transaction. Define the concepts of recoverable, cascade less, and strict schedules, and compare them in terms of their recoverability.
Q4b. Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below. State whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s). T1: r1 (X); r1 (Z); w1 (X); T2: r2 (Z); r2 (Y); w2 (Z); w2 (Y); T3: r3 (X); r3 (Y); w3 (Y); S1: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); w3 (Y); r2 (Y); w2 (Z); w2 (Y); S2: r1 (X); r2 (Z); r3 (X); r1 (Z); r2 (Y); r3 (Y); w1 (X); w2 (Z); w3 (Y); w2 (Y);20247m
Module 4: Transaction Processing
View this question on its own page →Consider the three transactions T1, T2, and T3, and the schedules S1 and S2 given below. State whether each schedule is serializable or not. If a schedule is serializable, write down the equivalent serial schedule(s).
T1: r1 (X); r1 (Z); w1 (X);
T2: r2 (Z); r2 (Y); w2 (Z); w2 (Y);
T3: r3 (X); r3 (Y); w3 (Y);
S1: r1 (X); r2 (Z); r1 (Z); r3 (X); r3 (Y); w1 (X); w3 (Y); r2 (Y); w2 (Z); w2 (Y);
S2: r1 (X); r2 (Z); r3 (X); r1 (Z); r2 (Y); r3 (Y); w1 (X); w2 (Z); w3 (Y); w2 (Y);Q5a. Describe Armstrong's axioms in detail. What is the role of these rules in database development process?20247m
Module 2: Relational Query Languages & Design
View this question on its own page →Describe Armstrong's axioms in detail. What is the role of these rules in database development process?
Q5b. Describe the term MVD in the context of DBMS by giving an example. Discuss 4NF and 5NF also.20247m
Module 2: Relational Query Languages & Design
View this question on its own page →Describe the term MVD in the context of DBMS by giving an example. Discuss 4NF and 5NF also.
Q6a. What are indices in database systems? Explain the need and advantages of indexing. Describe various types of indexing techniques including primary index, secondary index, clustered and non-clustered indexes with the help of suitable diagrams.20247m
Module 3: Storage Strategies
View this question on its own page →What are indices in database systems? Explain the need and advantages of indexing. Describe various types of indexing techniques including primary index, secondary index, clustered and non-clustered indexes with the help of suitable diagrams.
Q6b. What is a B-tree? Explain its structure, order, and properties. How is a B-tree used in database indexing? Demonstrate insertion and search operations in a B-tree with a proper example. Also compare B-tree with binary search tree in context of database performance.20247m
Module 3: Storage Strategies
View this question on its own page →What is a B-tree? Explain its structure, order, and properties. How is a B-tree used in database indexing? Demonstrate insertion and search operations in a B-tree with a proper example. Also compare B-tree with binary search tree in context of database performance.
Q7a. Discuss 2 phase commit (2PC) protocol and time stamp-based protocol with suitable example. How the validation-based protocols differ from 2PC?20247m
Module 4: Transaction Processing
View this question on its own page →Discuss 2 phase commit (2PC) protocol and time stamp-based protocol with suitable example. How the validation-based protocols differ from 2PC?
Q7b. Discuss the procedure of deadlock detection and recovery in transaction?20247m
Module 4: Transaction Processing
View this question on its own page →Discuss the procedure of deadlock detection and recovery in transaction?
Q8a. Explain the concept of database recovery in detail. Discuss various recovery techniques such as log-based recovery, shadow paging, checkpointing, and recovery using deferred and immediate update strategies with suitable examples.20247m
Module 4: Transaction Processing
View this question on its own page →Explain the concept of database recovery in detail. Discuss various recovery techniques such as log-based recovery, shadow paging, checkpointing, and recovery using deferred and immediate update strategies with suitable examples.
Q8b. What is DAC, MAC, and RBAC models in databases? Compare and contrast these models based on their policy mechanisms, user flexibility, and real-world applicability. Also explain which model is most suited for large enterprise systems and why.20247m
Module 5: Database Security
View this question on its own page →What is DAC, MAC, and RBAC models in databases? Compare and contrast these models based on their policy mechanisms, user flexibility, and real-world applicability. Also explain which model is most suited for large enterprise systems and why.
Q9a. Explain the concepts of Authentication, Authorization, and Access Control in database systems. How do these mechanisms work together to ensure database security? Support your answer with real-world examples and discuss how they are implemented in modern DBMS platforms.20247m
Module 5: Database Security
View this question on its own page →Explain the concepts of Authentication, Authorization, and Access Control in database systems. How do these mechanisms work together to ensure database security? Support your answer with real-world examples and discuss how they are implemented in modern DBMS platforms.
Q9b. Explain the concepts of Object-Oriented Databases (OODBMS) and Object-Relational Databases (ORDBMS).20247m
Module 6: Advanced Topics
View this question on its own page →Explain the concepts of Object-Oriented Databases (OODBMS) and Object-Relational Databases (ORDBMS).