AL-502 Database Management Systems - Dec 2025 Question Paper

Under each question, How to answer lists the points an answer should cover, outlined by CampusPrep from the syllabus. Check the details against your textbook.

1. 7 Marks
   aWhat is a data model? Explain object-oriented and relational data models with suitable examples.
   How to answer

   • Define data model as collection of concepts to describe structure, operations and constraints
   • Explain object-oriented model: objects, classes, inheritance, OID with example
   • Explain relational model: tables, tuples, attributes, keys with example table
   • Comparison / conclusion on usage
2. 7 Marks
   bDefine database languages in DBMS. Describe DDL, DML, DCL and TCL along with their commands and examples.
   How to answer

   • Define database languages as means to define, manipulate and control data
   • Describe DDL with CREATE, ALTER, DROP, TRUNCATE examples
   • Describe DML with SELECT, INSERT, UPDATE, DELETE examples
   • Describe DCL (GRANT, REVOKE) and TCL (COMMIT, ROLLBACK, SAVEPOINT) with examples
3. 7 Marks
   aWhat is ER Diagram? Explain different Components of an ER Diagram with their Notation. Draw an ER Diagram for Employee Project Management System.
   How to answer

   • Define ER diagram and its purpose in conceptual design
   • Explain components: entity, attributes, relationship, keys, cardinality with notations
   • Draw ER diagram for Employee Project Management System with Employee, Project, Department entities
   • Show attributes, relationships, participation and cardinality
4. 7 Marks
   bExplain Relational Algebra in DBMS. Illustrate Theta Join, Equi Join and Natural Join with suitable examples.
   How to answer

   • Define relational algebra as procedural query language with operators
   • Explain fundamental and additional operators briefly
   • Illustrate Theta Join with condition example
   • Illustrate Equi Join and Natural Join with examples and differences
5. 7 Marks
   aWhat are keys in the relational model? Explain super key, candidate key, primary key, alternate key and foreign key with suitable examples.
   How to answer

   • Define key and its role in relational model
   • Explain super key, candidate key, primary key with example table
   • Explain alternate key and foreign key with example
   • Summarize differences and selection criteria
6. 7 Marks
   bDescribe integrity constraints in DBMS. Explain domain integrity, entity integrity and referential integrity with suitable examples.
   How to answer

   • Define integrity constraints and need for consistency
   • Explain domain integrity with data type/check example
   • Explain entity integrity (primary key NOT NULL, unique) with example
   • Explain referential integrity (foreign key) with example
7. 7 Marks
   aExplain indexing in relational databases. Discuss different types of indexes and their role in improving query performance.
   How to answer

   • Define indexing as auxiliary access structure
   • Discuss types: primary, secondary, clustered, non-clustered, B-tree/B+ tree, hash
   • Explain how index avoids full table scan
   • Discuss role in query optimization and trade-offs
8. 7 Marks
   bConsider the relation R (A,B,C,D,E) with the following functional dependencies : $A \rightarrow B$, $B \rightarrow C$, $A \rightarrow D$, $D \rightarrow E$ Answer the following :

   1. i)Find all candidate keys of R.
   2. ii)Decompose the relation into 3NF.
   How to answer

   • Compute attribute closures to find candidate key $A$
   • Check partial and transitive dependencies violating 3NF
   • Decompose into $R1(A,B)$, $R2(B,C)$, $R3(A,D)$, $R4(D,E)$ or equivalent lossless 3NF
   • Verify lossless join and dependency preservation
9. 7 Marks
   aConsider the relation : STUDENT (Roll No, Name, Dept, Course, Instructor) with Functional Dependencies: RollNo $\rightarrow$ Name, Dept Course $\rightarrow$ Instructor Answer the following :

   1. i)Identify whether the relation is in 1NF, 2NF or 3NF.
   2. ii)Normalize the relation up to 3NF.
   How to answer

   • Determine candidate key (RollNo, Course) using closures
   • Test 2NF: partial dependencies RollNo -> Name, Dept and 3NF: transitive Course -> Instructor
   • Conclude relation is in 1NF only
   • Decompose to 3NF: Student(RollNo, Name, Dept), CourseInstructor(Course, Instructor), Enrollment(RollNo, Course)
10. 7 Marks
    bWhat is serializability? Explain testing of serializability and discuss conflict serializable and view serializable schedules with suitable examples.
    How to answer

    • Define serializability and serial vs non-serial schedules
    • Explain testing via precedence/graph for conflict serializability
    • Explain conflict serializable with example schedule
    • Explain view serializable with example and relation to conflict
11. 7 Marks
    aDiscuss recovery from transaction failures. Explain log-based recovery techniques and the role of checkpoints in recovery.
    How to answer

    • List transaction failure types: system, media, application
    • Explain log-based recovery: deferred and immediate update
    • Explain UNDO/REDO using log records
    • Explain checkpoint role in reducing recovery time
12. 7 Marks
    bWhat is deadlock in DBMS? Explain deadlock detection, prevention and recovery techniques.
    How to answer

    • Define deadlock as circular wait for locks
    • Explain detection via wait-for graph
    • Explain prevention: wait-die, wound-wait, timeout, ordering
    • Explain recovery by victim selection and rollback
13. 7 Marks
    aExplain Concurrency Control in DBMS. Discuss locking techniques including shared locks, exclusive locks and two-phase locking protocol.
    How to answer

    • Define concurrency control and need for consistency
    • Explain shared (S) and exclusive (X) locks with compatibility matrix
    • Explain two-phase locking: growing and shrinking phases
    • Discuss strict/rigorous 2PL ensuring serializability
14. 7 Marks
    bExplain the concept of a transaction system in DBMS. Discuss transaction states and ACID properties of a transaction.
    How to answer

    • Define transaction as atomic unit of work
    • Explain states: active, partially committed, committed, failed, aborted
    • Explain ACID: Atomicity, Consistency, Isolation, Durability with examples
    • Show state diagram transitions
15. 7 Marks
    aWhat do you understand by database security? Explain authentication and authorization mechanisms in DBMS by explaining how do they ensure database security.
    How to answer

    • Define database security: confidentiality, integrity, availability
    • Explain authentication: passwords, multi-factor, verification of identity
    • Explain authorization: privileges, GRANT/REVOKE, roles, access control
    • Explain how together they prevent unauthorized access
16. 7 Marks
    bExplain cursor management in PL/SQL. Discuss explicit cursors, nested cursors and parametrized with examples.
    How to answer

    • Define cursor and need in PL/SQL for multi-row queries
    • Explain explicit cursor lifecycle: DECLARE, OPEN, FETCH, CLOSE with example
    • Explain nested cursors with example
    • Explain parameterized cursors with example