CS-502 Database Management Systems - Nov 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
   aA woman artist designs and sells jewellery through mail orders or at craft shows. Currently she has a mailing list in a word processing file that she uses for sending catalogs. She also keeps both supplier and customer names and addresses in word processing files and prepares bills using a spreadsheet and keeps her income and expenses on another spreadsheet. What would be the advantages for her to design and use a DBMS for all her records.
   How to answer

   • Define DBMS vs file system (word processor + spreadsheets).
   • Advantages: data independence, reduced redundancy, integrity, security, concurrent access.
   • Advantages: querying, reporting, backup/recovery, catalog management for mailing list/suppliers/customers/bills.
2. 7 Marks
   bDesign a possible schema for a doctor's office. The doctors want immediate access to patient medical information. The records clerk needs to be sure all insurance companies are billed as per patient details.
   How to answer

   • Identify entities: Patient, Doctor, Visit, Diagnosis, Bill, Insurance Company.
   • Attributes and keys plus relationships.
   • Schema in 3NF supporting quick patient history access and billing by insurance details.
3. 7 Marks
   aConsider the relation Emp_Manager with Employee_ID as the primary key and Manager_ID as the foreign key referencing Employee_ID with on-delete cascade. Determine all the tuples that will be deleted with the following SQL Command $Delete\ From\ Emp\_Manager$ $Where\ Employee\_ID = '2'$

   How to answer

   • Initial delete: Employee_ID = '2'.
   • Apply ON DELETE CASCADE on self-referencing Manager_ID.
   • List all cascaded tuples: 2, 8, 14, 16 and further via 8->14 etc.
4. 7 Marks
   bConsider three relations R(a, b), S(b, c), and T(c, d) with following tuple values $R(a, b) = \{(0, 1), (4, 5), (8, 9)\}$ $S(b, c) = \{(1, 2), (5, 2), (5, 6), (5, 10), (13, 10)\}$ $T(c, d) = \{(2, 3), (6, 7), (10, 11), (10, 3)\}$ Find the result of the following, where $\bowtie$ denotes natural join

   1. i)R $\bowtie$ S
   2. ii)(R $\bowtie$ S) $\bowtie$ T
   How to answer

   • Apply natural join condition on common attribute b for R bowtie S.
   • List resulting (a,b,c) tuples.
   • Further join with T on c to get (a,b,c,d) tuples.
5. 7 Marks
   aConsider the following Employee relation with E_ID as the primary key Discuss the issues that may arise with the following SQL operations in the above relation

   1. i)Insert into Employee (E-ID Ename dno) Values (NULL, 'T', 104)
   2. ii)Delete From Employee Where E_ID = 'E3'
   3. iii)Update Table Employee Set dno = 109 Where dno = 104

   How to answer

   • State primary key / entity integrity and NOT NULL constraints.
   • Issue with NULL insert for key, delete of referenced key.
   • Issue with update on dno and foreign-key / NULL handling.
6. 7 Marks
   bConsider the following Employee Relation Write SQL Queries for the following:

   1. i)Fetch the E_ID and Name of employees who are getting salary higher than the salary of employee 'P'
   2. ii)Fetch the D_ID of employees who are getting salary higher than the salary of employee 'N'
   3. iii)Fetch the E_ID and Name of employees who are getting salary higher than anyone in the Dept_ID '102'
   4. iv)Fetch the E_ID and Name of employees who are getting salary higher than all employees in the Dept_ID '103'
   5. v)Fetch the E_ID and Name of employees who are getting salary same as the employees of Dept_ID '103'
   6. vi)Fetch the name, salary, and Dept_ID of employee getting second highest salary
   7. vii)Fetch the Dept_ID in which the average salary is greater than 3000

   How to answer

   • Use subquery with salary of 'P' / 'N' for i) and ii).
   • Use ANY / ALL and IN for Dept 102 / 103 comparisons.
   • Use ORDER BY LIMIT / MAX, GROUP BY HAVING AVG for second-highest and average.
7. 7 Marks
   aDiscuss the three major steps in query optimization.
   How to answer

   • Explain query optimization phases: parsing/translation, cost-based plan enumeration.
   • Describe transformation, cost estimation, execution plan selection.
   • Give example of equivalent expressions and cost model.
8. 7 Marks
   bDiscuss the various exception handling mechanisms in database platforms.
   How to answer

   • Define exceptions in stored procedures/triggers.
   • Mechanisms: handlers, propagation, predefined vs user-defined exceptions.
   • Example in Oracle/PL-SQL with EXCEPTION block.
9. 7 Marks
   aIdentify the candidate key and highest normal form of the relation R (E, F, G, H). Following functional dependencies exists for the relation R $E \to F$ $F \to G$ $H \to G$
   How to answer

   • Compute closures to find candidate key EH.
   • Check FDs for partial/transitive dependencies.
   • Conclude highest normal form (1NF/2NF) and justify.
10. 7 Marks
    bConsider a relation R(ABCD) with the following set of functional dependencies $A \to B$ $B \to C$ $C \to D$ $D \to A$ The relation R has been decomposed into three sub relations as R1(AB), R2(BC), and R3(CD). Determine the given decomposition satisfies the dependency preservation property or not?
    How to answer

    • List FDs preserved in R1, R2, R3.
    • Check if C->D, D->A etc. are entailed by union.
    • Conclude dependency preservation holds or fails with reason.
11. 7 Marks
    aConsider the transaction T as following begin 1. Read (A) 2. Read (B) 3. t = A -10% of A 4. B = B + t 5. Write (A) 6. Write (B) commit Discuss the transaction states active, partially commit, commit, failure and terminated possible with reference to the above example.
    How to answer

    • Define Active, Partially Committed, Committed, Failed, Terminated states.
    • Map Read/Write steps of T to state transitions.
    • Explain failure points before/after commit with example.
12. 7 Marks
    bDiscuss the issues that may arise with the interleaved execution of the transactions T1 and T2.

    How to answer

    • Describe interleaved execution of aggregate read and insert.
    • Issues: non-repeatable read, phantom, inconsistent analysis.
    • Explain with avg(balance) before/after insert of (222,40000).
13. 7 Marks
    aDraw the precedence graph for the given concurrent schedule and determine if it is conflict serializable or not?

    How to answer

    • List conflicting operations on A and B across T1,T2,T3.
    • Draw precedence edges T1->T2, T2->T3 etc.
    • Check for cycle to decide conflict serializability.
14. 7 Marks
    bIllustrate the concept of two-phase locking protocol and its variants with an example.
    How to answer

    • Define growing and shrinking phases with shared/exclusive locks.
    • Variants: basic, strict, rigorous 2PL.
    • Illustrate with example ensuring serializability and deadlock discussion.
15. 14 Marks
    Write short note on (any two)

    1. a)Oracle Application Express
    2. b)Specialization vs Generalization
    3. c)Temporal vs Multimedia Databases
    4. d)Triggers
    How to answer

    • Select any two topics with definition and purpose.
    • Explain key features, architecture or example.
    • Add diagram/example and applications/limitations.