IT-405 Data Base Management System - Jun 2022 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 various disadvantages of file system compare to Data base management system.
   How to answer

   • Define file-processing systems and DBMS
   • Explain data redundancy and inconsistency
   • Explain difficulty in accessing data and data isolation
   • Explain atomicity of updates and concurrent-access anomalies
   • Explain security and integrity constraints enforcement
2. 7 Marks
   bDifferentiate specialization and generalization with help of example.
   How to answer

   • Definition of generalization (bottom-up approach)
   • Definition of specialization (top-down approach)
   • Key differences tabulated
   • Real-world example with ER notation (e.g., Person -> Employee, Customer)
3. 7 Marks
   aExplain in detail about various key constraints used in database system.
   How to answer

   • Definition of key and constraints in relational model
   • Types of keys: Super key, Candidate key, Primary key, Alternate key, Foreign key
   • Explanation of each key constraint with syntax/examples
   • Importance of key constraints in data integrity
4. 7 Marks
   bExplain the components of DBMS with a neat diagram.
   How to answer

   • Draw DBMS architecture diagram showing Query Processor and Storage Manager
   • Explain Query Processor components (DDL interpreter, DML compiler, Query evaluation engine)
   • Explain Storage Manager components (Authorization & integrity manager, Transaction manager, File manager, Buffer manager)
   • Explain data structures used (Data files, Data dictionary, Indices)
5. 7 Marks
   aDefine and explain following terms :- Levels of data abstraction, Instances, Schema, Physical data independence, Logical data independence.
   How to answer

   • Define and illustrate 3 levels of data abstraction: Physical, Logical, and View levels
   • Define Schema (overall design/structure) vs Instance (actual data at a specific moment)
   • Explain Data Independence concept
   • Differentiate and define Physical Data Independence and Logical Data Independence
6. 7 Marks
   bConsider the following relational schema: Doctor (DName, Reg_no) Patient (PName, Disease) Assigned To (Pname, Dname) Give expression in both SQL and relational algebra for each of the queries: i) Get the names of patients who are assigned to more than one doctor. ii) Get the names of doctors who are treating patients with 'Polio'.
   How to answer

   • Query (i) SQL: `SELECT Pname FROM Assigned_To GROUP BY Pname HAVING COUNT(DISTINCT Dname) > 1;`
   • Query (i) Relational Algebra: Self-join on Assigned_To ($A_1, A_2$) where $A_1.Pname = A_2.Pname \land A_1.Dname \neq A_2.Dname$, then project $Pname$
   • Query (ii) SQL: `SELECT DISTINCT Dname FROM Assigned_To NATURAL JOIN Patient WHERE Disease = 'Polio';`
   • Query (ii) Relational Algebra: $\Pi_{Dname}(\sigma_{Disease='Polio'}(Patient) \bowtie Assigned\_To)$
7. 7 Marks
   aState the Armstrong inference rules. Provide suitable examples to describe each.
   How to answer

   • State the primary axioms: Reflexivity ($Y \subseteq X \implies X \rightarrow Y$), Augmentation ($X \rightarrow Y \implies XZ \rightarrow YZ$), Transitivity ($X \rightarrow Y \land Y \rightarrow Z \implies X \rightarrow Z$)
   • State secondary/derived rules: Union, Decomposition, Pseudo-transitivity
   • Provide a clear example for each primary and derived rule
8. 7 Marks
   bDescribe the concept of Referential Integrity.
   How to answer

   • Definition of Referential Integrity and foreign key relationship
   • Conditions under which referential integrity is satisfied
   • Actions on violation: CASCADE, SET NULL, SET DEFAULT, RESTRICT/NO ACTION
   • Illustrative example schema and foreign key constraint in SQL
9. 7 Marks
   aWhat do you mean by Normalization? Explain BCNF, 3NF and 2NF with a suitable example.
   How to answer

   • Define Normalization and its purpose (reducing redundancy, avoiding anomalies)
   • Explain 2NF: relation in 1NF and no non-prime attribute is partially dependent on any candidate key, with example
   • Explain 3NF: relation in 2NF and no non-prime attribute transitively dependent on candidate key ($X \rightarrow A$ implies $X$ is superkey or $A$ is prime attribute), with example
   • Explain BCNF: strictly $X$ must be a superkey for every non-trivial $FD$ $X \rightarrow A$, with example
10. 7 Marks
    bGiven a set of FDs $$A \rightarrow B, ABCD \rightarrow E, EF \rightarrow G$$ Is $ACDF \rightarrow G$ implied by the set of given FDs? Justify your answer.
    How to answer

    • Compute the closure of attribute set $(ACDF)^+$ using the given set of FDs
    • Start: $(ACDF)^+ = \{A, C, D, F\}$
    • Since $A \rightarrow B$, add $B$: $(ACDF)^+ = \{A, B, C, D, F\}$
    • Since $ABCD \subseteq (ACDF)^+$ and $ABCD \rightarrow E$, add $E$: $(ACDF)^+ = \{A, B, C, D, E, F\}$
    • Since $EF \subseteq (ACDF)^+$ and $EF \rightarrow G$, add $G$: $(ACDF)^+ = \{A, B, C, D, E, F, G\}$
    • Since $G \in (ACDF)^+$, conclude that $ACDF \rightarrow G$ is indeed implied by the given FDs
11. 7 Marks
    aWhat is Transaction? Explain its four important Properties.
    How to answer

    • Define Transaction as a logical unit of database processing
    • Explain Atomicity (all-or-nothing execution)
    • Explain Consistency (preservation of database consistency constraints)
    • Explain Isolation (concurrent transactions execute independently without interference)
    • Explain Durability (committed changes persist even during system crashes)
12. 7 Marks
    bWhat are the problems encountered in DDBMS while considering concurrency control and recovery.
    How to answer

    • Define Distributed Database Management Systems (DDBMS)
    • Discuss concurrency control challenges: distributed locking, multiple copies/data replication, global deadlocks
    • Discuss recovery challenges: failure of individual sites, loss of messages, network partitioning
    • Explain protocols needed to resolve these issues (e.g., Two-Phase Commit protocol)
13. 7 Marks
    aExplain in detail about timestamp based concurrency control techniques.
    How to answer

    • Explain timestamps: generation via system clock or logical counter
    • Define Read timestamp ($R\text{-timestamp}(Q)$) and Write timestamp ($W\text{-timestamp}(Q)$)
    • State rules for read operations $read(Q)$ and handling conflicts/rollbacks
    • State rules for write operations $write(Q)$
    • Mention Thomas' Write Rule as an optimization
14. 7 Marks
    bWrite and explain optimistic concurrency control algorithm.
    How to answer

    • Principle of optimistic concurrency control (validation-based protocol)
    • Three phases of transaction execution: Read phase, Validation phase, Write phase
    • Validation test conditions for serializability
    • Advantages and scenarios where optimistic concurrency control performs best (read-heavy workloads)
15. 14 Marks
    Write short note on any two: a) Multi Valued Dependency b) Expression Evaluation Plan c) Object Oriented Data Base System.
    How to answer

    • For (a) Multi Valued Dependency: definition $\alpha \twoheadrightarrow \beta$, properties, relationship to 4NF, example
    • For (b) Expression Evaluation Plan: relational algebra trees annotated with evaluation algorithms, cost estimation, pipelining vs materialization
    • For (c) Object Oriented Data Base System: integration of OOP concepts (classes, encapsulation, inheritance, object identity) with database capabilities