AD-402 Data Base Management System - Jun 2025 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
   aDefine the following.

   1. i)Data
   2. ii)Data base
   3. iii)Data base management
   How to answer

   • Define Data as raw facts/values
   • Define Database as organized collection of related data
   • Define DBMS as software to create, manage and query database
2. 7 Marks
   bWhat are the responsibilities of DBA? Explain in detail.
   How to answer

   • Define DBA role
   • List responsibilities: schema design, security, backup, performance
   • Explain each responsibility in detail
3. 7 Marks
   aWrite the differences between Database approach v/s Traditional file accessing approach.
   How to answer

   • Define database approach with DBMS features
   • Define traditional file system characteristics
   • Compare on redundancy, sharing, integrity, security in table form
4. 7 Marks
   bFind the Cartesian product of the following P and Q.

   

   How to answer

   • Define Cartesian product $P \times Q$
   • Combine every tuple of P with every tuple of Q
   • List resulting schema and 9 tuples
5. 7 Marks
   aDefine primary key and foreign key constraint and how these constraints are expressed in SQL?
   How to answer

   • Define primary key: unique, not null, one per table
   • Define foreign key: references primary key of another table
   • Show SQL syntax with PRIMARY KEY and FOREIGN KEY REFERENCES example
6. 7 Marks
   bDescribe about tuple relational calculus with suitable example.
   How to answer

   • Define tuple relational calculus form {t | P(t)}
   • Explain tuple variable and predicate
   • Give suitable example query with result
7. 7 Marks
   aIllustrate insertion, deletion and modification anomalies with suitable examples.
   How to answer

   • Define anomaly due to redundancy in unnormalized relation
   • Illustrate insertion anomaly with example
   • Illustrate deletion and modification anomalies with examples
8. 7 Marks
   bGiven below are the two sets of FD's for a relation $R(A, B, C, D, E)$, are they equivalent? Explain.

   1. i)$A \to B, AB \to C, D \to AC, D \to E$
   2. ii)$A \to BC, D \to AE$
   How to answer

   • Compute closures under set i) to derive A->BC and D->AE
   • Compute closures under set ii) to derive A->B, AB->C, D->AC, D->E
   • Conclude equivalence if both covers imply each other
9. 7 Marks
   aWhat is Normalization? Explain about 4NF with an example.
   How to answer

   • Define normalization and need to remove anomalies
   • Define 4NF: BCNF plus no non-trivial multivalued dependency
   • Give example relation violating 4NF and its decomposition
10. 7 Marks
    bHow does the granularity of data items affect the performance of concurrency control? Explain.
    How to answer

    • Define granularity: database, table, page, tuple, field level
    • Explain trade-off: coarse reduces overhead but less concurrency
    • Explain fine increases concurrency but locking overhead
11. 7 Marks
    aWrite the advantages and disadvantages of validation protocol.
    How to answer

    • Define validation / optimistic concurrency protocol phases
    • List advantages: no locking, high concurrency for low conflict
    • List disadvantages: restarts, starvation, overhead
12. 7 Marks
    bConsider the two transactions T1 and T2, both the transactions subtracts 100 from A and adds to B and the time stamps of T1 and T2 are 5:15pm and 5:16 pm respectively. Illustrate a schedule if the entire system clock is set to zero.
    How to answer

    • State T1 and T2 operations: read A, A=A-100, write A, read B, B=B+100, write B
    • Use timestamps 5:15pm and 5:16pm with clock zero for ordering
    • Draw time-ordered schedule illustrating execution
13. 7 Marks
    aExplain RDBMS architecture with all components.
    How to answer

    • Define RDBMS layered architecture
    • Explain components: client, query processor, storage manager
    • Show block diagram of components and interaction
14. 7 Marks
    bWhat are the key characteristics of ANSI SQL? Explain in detail.
    How to answer

    • Define ANSI SQL standardization purpose
    • List characteristics: portability, standard DDL/DML, data integrity
    • Explain each characteristic in detail
15. 14 Marks
    Write a short note on any two of the following:

    1. a)Schemas and Instances
    2. b)Triggers
    3. c)Query Optimization
    4. d)Serializability
    How to answer

    • Select any two topics from four options
    • Give definition, purpose and key points for each
    • Add example/diagram where applicable