CS-502 Database Management Systems - Dec 2024 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
   aDraw and explain the detailed system architecture of DBMS.
   How to answer

   • Three-level architecture: external, conceptual, internal levels
   • Components: query processor, storage manager, transaction manager
   • Neat labelled block diagram of DBMS system architecture
   • Brief function of each module
2. 7 Marks
   bHow to represent generalization, specialization and aggregation using ER Diagrams? Explain with suitable ER diagram.
   How to answer

   • Define generalization, specialization and aggregation
   • ER notation for each: triangle for generalization/specialization, diamond-in-box for aggregation
   • Suitable ER diagram example
   • Brief explanation of inheritance and part-of relationship
3. 7 Marks
   aHow does Tuple-oriented relational calculus differ from domain-oriented relational calculus?
   How to answer

   • Define tuple relational calculus with tuple variables
   • Define domain relational calculus with domain variables
   • Differences in syntax, variables, quantifiers and examples
   • Comparison table summary
4. 7 Marks
   bExplain the importance of Null values in Relational Model.
   How to answer

   • Meaning of NULL as unknown/missing/inapplicable value
   • Three-valued logic effect on queries and comparisons
   • Importance in outer joins, integrity and flexibility
   • Problems and handling precautions
5. 7 Marks
   aExplain in detail about various key constraints used in database system.
   How to answer

   • Define primary key, candidate key, super key, foreign key
   • Uniqueness and referential integrity constraints
   • Alternate key, composite key with examples
   • Role in entity integrity
6. 7 Marks
   bDiscuss the mechanism of attribute relationship inheritance. How is it useful?
   How to answer

   • Concept of attribute inheritance in EER / generalization hierarchy
   • Mechanism of inheriting attributes and relationships by subclasses
   • Example hierarchy diagram
   • Usefulness: reuse, reduced redundancy, constraint enforcement
7. 7 Marks
   aDiscuss in detail the operators SELECT, PROJECT and UNION with suitable examples.
   How to answer

   • Definitions and symbols for SELECT, PROJECT, UNION
   • Suitable relational example tables and result
   • Properties: commutativity, compatibility for UNION
   • Illustration of each operator output
8. 7 Marks
   bGive a brief note on Query optimization.
   How to answer

   • Definition and need for query optimization
   • Steps: parsing, cost estimation, plan selection
   • Heuristic vs cost-based optimization
   • Benefits in performance
9. 7 Marks
   aExplain the role of functional dependencies in normalization with suitable examples.
   How to answer

   • Definition of functional dependency and Armstrong axioms
   • How FDs identify redundancy and candidate keys
   • Role in deriving 1NF, 2NF, 3NF decompositions
   • Example showing anomaly removal
10. 7 Marks
    bExplain about Boyce Codd normal form with an example.
    How to answer

    • Definition of BCNF: every determinant is a candidate key
    • Difference from 3NF with example relation
    • Decomposition steps to achieve BCNF
    • Example illustrating BCNF satisfaction/violation
11. 7 Marks
    aExplain Timestamping protocols for concurrency control.
    How to answer

    • Concept of timestamps and ordering of transactions
    • Basic timestamp ordering protocol rules for read/write
    • Thomas write rule and handling of conflicts
    • Advantages and disadvantages
12. 7 Marks
    bWhat is 2-phase locking protocol? Compare 2PL with Strict 2PL protocol.
    How to answer

    • Definition and growing/shrinking phases of 2PL
    • Strict 2PL: exclusive locks held till commit
    • Comparison table on serializability, deadlock, recoverability
    • Examples illustrating difference
13. 7 Marks
    aWhat is Transaction? Discuss about transaction recovery techniques.
    How to answer

    • Definition and ACID properties of transaction
    • Log-based, shadow paging and checkpoint recovery
    • Undo/redo operations during recovery
    • Example of failure recovery
14. 7 Marks
    bExplain briefly:

    1. i)Distributive Databases
    2. ii)Triggers
    3. iii)Join operations
    How to answer

    • Brief definition of distributed databases
    • Concept and use of triggers with example
    • Types of join operations with example
    • Summary of applications
15. 7 Marks
    aWhat is Cursor Management? Explain nested and parameterized cursors.
    How to answer

    • Definition and need for cursor management
    • Nested cursor concept and working
    • Parameterized cursor with example syntax
    • Comparison of types
16. 7 Marks
    bWrite short notes :

    1. i)Oracle Exception Handling Mechanism
    2. ii)Branching and looping Constructs in ANSI SQL.
    How to answer

    • Oracle exception handling: predefined vs user-defined exceptions
    • Syntax with example block
    • Branching constructs: IF, CASE in ANSI SQL
    • Looping constructs: LOOP, WHILE, FOR