AL-502 Data Base 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
   aDescribe basic architecture of Database Management System in detail.
   How to answer

   • Define 3-level ANSI-SPARC architecture: external, conceptual, internal
   • Explain components: users, query processor, storage manager, transaction manager
   • Describe data independence: logical and physical
   • Include block diagram of DBMS architecture
2. 7 Marks
   bExplain the Functions of database Administrator.
   How to answer

   • Define Database Administrator (DBA)
   • List administrative functions: schema definition, storage, authorization, routine maintenance
   • Explain backup, recovery, performance tuning role
   • Give example of DBA tasks
3. 7 Marks
   aWhat is ER-model? Explain the basic symbols used of entities, attributes and relationships.
   How to answer

   • Define ER-model and its purpose in conceptual design
   • Explain symbols for entity, weak entity, relationship
   • Explain symbols for attributes and keys, cardinality
   • Draw ER diagram example with symbols
4. 7 Marks
   bWhat is an Attribute? Explain various types of attribute with examples.
   How to answer

   • Define attribute as property of entity
   • Classify: simple/composite, single/multi-valued, stored/derived, key
   • Give example for each type e.g. Name, Phone, Age
   • Show ER notation for attributes
5. 7 Marks
   aExplain integrity constraint over relations.
   How to answer

   • Define integrity constraints in relational model
   • Explain domain, entity/key, referential integrity
   • Give example of primary key and foreign key constraint
   • Explain enforcement on insert/delete/update
6. 7 Marks
   bHow to alter, destroy and views table? Explain with suitable queries.
   How to answer

   • Define ALTER, DROP and VIEW/CREATE VIEW syntax
   • Explain ALTER ADD/DROP column and DROP TABLE
   • Explain creating and dropping views
   • Give suitable SQL queries for each
7. 7 Marks
   aExplain Fundamental operations in relational algebra with examples.
   How to answer

   • List fundamental operations: select, project, union, set difference, cartesian product, rename
   • Give syntax and definition of each operator
   • Show example relations and result
   • Mention additional operations briefly
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 functional dependency condition
   • Outline steps: identify FDs, keys, violating FDs
   • Explain decomposition into BCNF preserving dependencies if possible
   • Give example decomposition of 3NF to BCNF
9. 7 Marks
   aExplain Optimization methods.

   1. i)Heuristic based
   2. ii)Cost Estimation based
   How to answer

   • Define query optimization purpose
   • Explain heuristic-based: transformation rules, push selection/projection
   • Explain cost-estimation based: statistics, cost model, plan selection
   • Compare advantages with example
10. 7 Marks
    bHow to test Serializability of Schedule? Explain with example.
    How to answer

    • Define serializability: conflict and view
    • Explain precedence graph construction method
    • Explain cycle test for non-serializability
    • Give example schedule and test result
11. 7 Marks
    aExplain log-based recovery Protocol.
    How to answer

    • Define log records: start, update, commit, abort
    • Explain write-ahead logging and stable storage
    • Explain undo/redo recovery using log
    • Give example log and recovery steps
12. 7 Marks
    bExplain Multiple Granularity Concurrency control scheme.
    How to answer

    • Define granularity hierarchy: database, area, file, record
    • Explain intention locks: IS, IX, SIX
    • Explain locking protocol for multiple granularity
    • Give example with lock compatibility
13. 7 Marks
    aWhat is view? Give brief note on view.
    How to answer

    • Define view as virtual table derived from base tables
    • Explain CREATE VIEW syntax and types
    • Explain advantages and update issues
    • Give example query
14. 7 Marks
    bState and explain various File Organization Methods.
    How to answer

    • Define file organization and need
    • Explain heap, sequential, indexed, hashed organization
    • Compare access time and suitability
    • Give example use case for each
15. 14 Marks
    Write short notes on any two:

    1. a)Assertions
    2. b)Web and Mobile database
    3. c)Hierarchical queries
    4. d)Validation based protocols
    How to answer

    • Select any two topics from list
    • Define each term with key features
    • Explain working with example/diagram
    • Mention applications/limitations