AD-402 Data Base Management System - Jun 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 in brief architecture of DBMS with a neat sketch.
   How to answer

   • Define 3-level ANSI/SPARC architecture: external, conceptual, internal levels
   • Explain components: users, DBA, DDL/DML, query processor, storage manager
   • Describe data independence: logical and physical
   • Provide neat labelled sketch of DBMS architecture
2. 7 Marks
   bList and explain the advantages of DBMS.
   How to answer

   • List advantages: data sharing, redundancy control, consistency, integrity
   • Explain security, concurrent access, backup and recovery
   • Explain data independence and reduced development time
   • Give examples for each advantage
3. 7 Marks
   aState and explain various features of E-R Models.
   How to answer

   • Define E-R model: entities, attributes, relationships
   • Explain features: entity sets, keys, mapping cardinalities
   • Explain weak/strong entities, generalization, specialization, aggregation
   • Draw illustrative E-R diagram example
4. 7 Marks
   bDiscuss the usage of 'group by' and 'having' clauses in SQL.
   How to answer

   • Define GROUP BY: grouping rows with aggregate functions
   • Define HAVING: filtering groups vs WHERE filtering rows
   • Give SQL syntax and execution order
   • Give example query using COUNT/SUM with GROUP BY and HAVING
5. 7 Marks
   aExplain division operation in relational algebra with a suitable example.
   How to answer

   • Define division operation $R \div S$ for universal quantification
   • Explain schema requirement and result schema
   • Show steps with example relations R and S
   • Give result table and SQL equivalent
6. 7 Marks
   bList out the properties of relation algebra.
   How to answer

   • List operators: select, project, union, set difference, cartesian product, rename
   • Explain closure, commutativity and associativity properties
   • Explain idempotence and distributive properties
   • Give examples of equivalences
7. 7 Marks
   aDefine closure of F. Where F is the set of functional dependencies. Explain computing F+ with suitable examples.
   How to answer

   • Define closure $F^+$ as set of all FDs implied by $F$
   • Explain Armstrong's axioms: reflexivity, augmentation, transitivity
   • Show iterative algorithm to compute $F^+$
   • Work out suitable example with $A \to B$ type FDs
8. 7 Marks
   bDiscuss the conditions required for a relation to be in 1NF and 2NF with a suitable example.
   How to answer

   • Define 1NF: atomic values, no repeating groups
   • Define 2NF: 1NF plus no partial dependency on key
   • Give unnormalized table example and conversion to 1NF
   • Show 1NF to 2NF decomposition removing partial dependency
9. 7 Marks
   aList and explain the steps for optimizing the Query.
   How to answer

   • List steps: parsing, translation, optimization, evaluation
   • Explain heuristic vs cost-based transformation
   • Explain equivalence rules and plan selection
   • Give example of query tree optimization
10. 7 Marks
    bDiscuss in detail time stamp based concurrency control protocol.
    How to answer

    • Define timestamps $TS(T)$ and read/write timestamps
    • Explain Thomas write rule and abort/rollback conditions
    • Explain timestamp ordering for read and write operations
    • Discuss advantages and starvation issues
11. 7 Marks
    aDiscuss the procedure to test serializability of a schedule with an example.
    How to answer

    • Define conflict and view serializability
    • Explain precedence graph construction steps
    • Explain cycle detection for non-serializable schedule
    • Work out example schedule and its test result
12. 7 Marks
    bDifferentiate between OODBMS and DBMS.
    How to answer

    • Define OODBMS with objects, classes, inheritance
    • Compare on data model, query language, relationships
    • Compare on applications, performance, standards
    • Present differences in tabular form
13. 7 Marks
    aExplain in brief the procedure for data extraction from single and multiple tables.
    How to answer

    • Explain SELECT-FROM-WHERE for single table extraction
    • Explain joins, subqueries and set operations for multiple tables
    • Give syntax and example queries
    • Mention filtering, sorting and aggregation
14. 7 Marks
    bDiscuss in detail stored procedures.
    How to answer

    • Define stored procedure and its purpose
    • Explain creation with parameters, execution and advantages
    • Explain control flow, error handling and security
    • Give example CREATE PROCEDURE syntax
15. 14 Marks
    Explain in brief any two of the following:

    1. a)Object oriented data Model
    2. b)Referential integrity
    3. c)Cost estimation based optimization method
    4. d)Multiple granularity
    How to answer

    • Select any two topics from a-d for detailed notes
    • Give definition, key features and purpose for each
    • Give example or diagram where applicable
    • Mention advantages and applications