CY-405 Database Management System - Nov 2023 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
   aExplain the architecture of Database Management Systems with neat diagram?
   How to answer

   • Define DBMS and need for layered architecture
   • Explain 3-schema architecture: external, conceptual, internal levels with mappings
   • Describe components: users, query processor, storage manager
   • Neat labelled diagram showing levels and interfaces
2. 7 Marks
   bWhat is Null Attribute? With suitable diagram explain Weak and Strong Entity Set.
   How to answer

   • Define NULL and NULL attribute
   • Define entity set, strong vs weak entity set with keys
   • Explain discriminator, identifying relationship
   • ER diagram showing strong and weak entity sets
3. 7 Marks
   aDiscuss about File Organization and Access methods.
   How to answer

   • Define file organization and access methods
   • Explain sequential, heap, hashed, clustered organization
   • Explain sequential, direct, indexed access
   • Compare advantages and use cases
4. 7 Marks
   bHow to write the queries in Relational Algebra? Explain with examples.
   How to answer

   • Define relational algebra and query structure
   • List basic operators: select, project, join, set operators
   • Show steps to translate English query to expression
   • Illustrate with 2-3 example queries
5. 7 Marks
   aWhat do you understand by Tuples and Attributes? Explain with examples.
   How to answer

   • Define relation, tuple as row and attribute as column
   • Explain degree, cardinality, domain with example table
   • Give student/employee table example marking tuples and attributes
   • Mention properties of tuples and attributes
6. 7 Marks
   bDiscuss about Joins with suitable examples.
   How to answer

   • Define join and need in relational algebra/SQL
   • Explain inner, left/right/full outer, equi, natural joins
   • Give example tables and result of each join
   • Mention syntax and use cases
7. 7 Marks
   aExplain normalization using (1NF, 2NF, 4NF) with suitable examples?
   How to answer

   • Define normalization and need to remove anomalies
   • Explain 1NF: atomic values with example
   • Explain 2NF: no partial dependency with example
   • Explain 4NF: no multi-valued dependency with example
8. 7 Marks
   bWhat are the steps to be followed to convert a relation in 3NF to BCNF?
   How to answer

   • Define 3NF and BCNF and difference in determinants
   • Check each FD where determinant is superkey
   • Decompose violating relation preserving dependencies if possible
   • Verify lossless join and final BCNF form
9. 7 Marks
   aCompute the closer of the following set of functional dependencies for a relation scheme. $R(A, B, C, D, E), F = \{A \rightarrow BC, CD \rightarrow E, B \rightarrow D, E \rightarrow A\}$ List out the candidate keys of R.
   How to answer

   • Compute attribute closures using given FDs
   • Identify attributes that determine all attributes
   • List minimal candidate keys e.g. A, E, BC, CD
   • Justify keys by closure and minimality test
10. 7 Marks
    bState and explain shadow paging with suitable example.
    How to answer

    • Define shadow paging and shadow page table concept
    • Explain write using current and shadow tables
    • Illustrate with example of transaction update
    • Mention advantages and disadvantages
11. 7 Marks
    aDiscuss about Timestamp Based Concurring Control.
    How to answer

    • Define concurrency and need for timestamp ordering
    • Explain W-timestamp, R-timestamp and Thomas write rule
    • Describe protocol steps for read/write with example schedule
    • Mention advantages and handling of conflicts
12. 7 Marks
    bWhat do you understand by Deadlocks, Explain Avoidance and Prevention in deadlock?
    How to answer

    • Define deadlock with wait-for graph example
    • Explain deadlock prevention schemes: wait-die, wound-wait
    • Explain avoidance using safe state and Banker's algorithm
    • Compare prevention vs avoidance
13. 7 Marks
    aWrite an algorithm to implement project and join operation of relational Algebra.
    How to answer

    • Define project and join operators
    • Give stepwise algorithm for projection with duplicate elimination
    • Give nested-loop / sort-merge algorithm for join
    • Mention complexity and example
14. 7 Marks
    bCompare Single and Multilevel indices.
    How to answer

    • Define single-level vs multilevel indices
    • Compare structure, access time, overhead
    • Give example of primary index vs B-tree like levels
    • Tabular comparison and use cases
15. 14 Marks
    Write short notes on any two:

    1. i)Complexity Measures
    2. ii)Components of DBMS
    3. iii)Triggers
    4. iv)Recoverability
    How to answer

    • Select any two topics from list
    • Give definition and key points for each
    • Add example/diagram where relevant
    • Mention applications and significance