AL-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
   aDescribe the architecture of DBMS. Explain each component briefly.
   How to answer

   • Define 3-level ANSI/SPARC architecture: external, conceptual, internal levels
   • Explain components: users, query processor, storage manager, transaction manager
   • Describe mappings between levels and data independence
   • Brief function of each component with diagram
2. 7 Marks
   bDefine E-R model with example. How to convert an E-R model into Tables? Explain.
   How to answer

   • Define E-R model: entities, attributes, relationships with example
   • Explain symbols for strong/weak entities, keys, cardinality
   • Give example E-R diagram e.g., Student-Course
   • Steps to convert E-R to tables for entity, relationship, multivalued attributes
3. 7 Marks
   aDefine database system. Summarize the advantages of database system.
   How to answer

   • Define database system: database + DBMS + users/applications
   • List advantages: redundancy control, sharing, integrity, security, independence
   • Brief explanation of each advantage
   • Contrast with file system
4. 7 Marks
   bWhat is database schema? Describe Physical schema and Logical schema.
   How to answer

   • Define database schema: structure/blueprint of database
   • Describe physical schema: physical storage details
   • Describe logical schema: logical structure, tables, relations
   • Mention subschemas/views and differences
5. 7 Marks
   aList out the various join operations and explain briefly with suitable examples.
   How to answer

   • List join types: inner, left/right/full outer, cross, natural, equi, self
   • Define each with relational algebra notation
   • Give suitable example tables and result
   • Mention condition vs natural join
6. 7 Marks
   bDiscuss the following Relational algebra operations with examples. SELECT, PROJECT, DIVISION
   How to answer

   • Define SELECT (sigma), PROJECT (pi), DIVISION operators
   • Give syntax and purpose of each
   • Illustrate with example relations and queries
   • Show result tables for each operation
7. 7 Marks
   aState BCNF. How does it differ from 3 NF? Explain.
   How to answer

   • State BCNF definition: every determinant is candidate key
   • State 3NF definition and allowable transitive dependency
   • Differentiate with example violating BCNF but in 3NF
   • Explain stricter nature of BCNF
8. 7 Marks
   bWhat is Functional Dependency? Describe the properties of FD's.
   How to answer

   • Define functional dependency X -> Y
   • Explain Armstrong axioms: reflexivity, augmentation, transitivity
   • Describe additional properties: union, decomposition, pseudotransitivity
   • Give examples of FDs
9. 7 Marks
   aDefine optimization. Discuss the steps involved in query optimization.
   How to answer

   • Define query optimization and its objectives
   • Outline steps: parsing, transformation, cost estimation, plan selection
   • Explain heuristic vs cost-based optimization
   • Mention role of query execution plans
10. 7 Marks
    bExplain different types of Advanced Recovery Techniques.
    How to answer

    • Define recovery and need for advanced techniques
    • Explain log-based, shadow paging, ARIES
    • Describe checkpoints and media recovery
    • Compare techniques on overhead and speed
11. 7 Marks
    aList and explain various types of specialized locking techniques used in DBMS.
    How to answer

    • Define locking and need for specialized locks
    • Explain shared, exclusive, intention, SIX locks
    • Describe multiple granularity and tree locks
    • Give compatibility matrix example
12. 7 Marks
    bWhat do you understand by distributed databases? Give the various advantages and disadvantages of distributed database management system.
    How to answer

    • Define distributed database and its architecture
    • List advantages: reliability, availability, local autonomy, performance
    • List disadvantages: complexity, cost, integrity control
    • Conclude with use cases
13. 7 Marks
    aDescribe the usage of parameters in procedures.
    How to answer

    • Define stored procedures and parameters
    • Explain IN, OUT, INOUT parameter modes
    • Give example syntax with parameters
    • Describe usage benefits: reusability, performance
14. 7 Marks
    bDiscuss the limitations of user defined functions.
    How to answer

    • Define user defined functions (UDFs)
    • List limitations: no DML/DDL, single return, performance
    • Explain restrictions on side effects and transactions
    • Contrast with stored procedures
15. 14 Marks
    Write a short note on any two of the following.

    1. a)Single level and multilevel indices.
    2. b)Referential integrity constraints.
    3. c)Problems with null valued and dangling tuples.
    4. d)Triggers
    How to answer

    • Select any two topics from a-d
    • For each: definition, purpose, example/syntax
    • Explain key points and use cases
    • Mention advantages/limitations