CS-502 Database Management Systems - 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
   aDiscuss the role of following in representing information about the real world in a database?

   1. i)DDL and DML
   2. ii)The buffer manager
   3. iii)The data model
   How to answer

   • Define DDL for schema definition and DML for data manipulation with examples
   • Explain buffer manager role in bringing disk pages to memory and caching
   • Explain data model as abstraction for structure, constraints and operations
   • Relate each to representing real-world information
2. 7 Marks
   bDescribe the structure of a DBMS. If your operating system is upgraded to support some new functions on OS files (e.g., the ability to force some sequence of bytes to disk), which layer(s) of the DBMS would you have to rewrite to take advantage of these new functions?
   How to answer

   • Describe layered DBMS architecture: query, storage, buffer, OS interface
   • Identify disk manager / storage manager as layer interacting with OS files
   • Argue only low-level storage layer needs rewrite to use new force-to-disk primitive
   • Note upper layers unchanged due to data independence
3. 7 Marks
   aConsider the following ER Diagram with the relations $E1(\underline{a}, b)$ and $E2(\underline{c}, d)$. R1 and R2 denote relationships between E1 and E2. Illustrate the table schema of the entities E1, E2 while converting ER Diagram to Relational tables.

   

   How to answer

   • Interpret double lines as total participation / key constraints from diagram
   • Identify R1 and R2 cardinalities between E1 and E2
   • Derive relational tables with primary and foreign keys
   • Show merged tables if many-to-one or separate table if many-to-many
4. 7 Marks
   bDraw an ER diagram that captures the following information. "A company database needs to store information about employees (identified by ssn, with salary and phone as attributes), departments (identified by dno, with dname and budget as attributes), and children of employees (with name and age as attributes). Employees work in departments; each department is managed by an employee; a child must be identified uniquely by name when the parent (who is an employee; assume that only one parent works for the company) is known. We are not interested in information about a child once the parent leaves the company."
   How to answer

   • Identify entities Employee(ssn,salary,phone), Department(dno,dname,budget), Child(name,age) as weak entity
   • Show Works_in and Manages relationships with cardinalities
   • Show identifying relationship for Child dependent on Employee
   • Include keys, participation and arrow for one-to-many
5. 7 Marks
   aConsider the following Employee relation with E_ID as the primary key. Discuss the anomalies that may arise during Insert, Delete and Updating a record in the above relation.

   

   How to answer

   • Describe given Employee(E_ID,Ename,Dname) table with sample rows
   • Explain insertion anomaly due to null / duplicate department
   • Explain deletion anomaly losing department info
   • Explain update anomaly and redundancy
6. 7 Marks
   bConsider the following Employee Relation. Write SQL Queries for the following:

   1. i)Fetch the Dept_ID whose average salary is higher than 4000.
   2. ii)Fetch the Dept_ID whose average salary of male employees is higher than 2000.
   3. iii)Fetch the Name of all employees whose Manager_ID is '1'.
   4. iv)Fetch the Name of all employees of Dept_ID as '103' and their Manager_ID is '1'.
   5. v)Fetch the name, salary, and Dept_ID of employee getting second highest salary.
   6. vi)Fetch the E_ID and Name of employees who are getting salary higher than the salary of employee 'F'.
   7. vii)Fetch the D_ID of employees who are getting salary higher than the salary of employee 'E'.

   

   How to answer

   • Use GROUP BY Dept_ID with HAVING AVG(Salary) for i and ii
   • Use simple WHERE on Manager_ID and Dept_ID for iii and iv
   • Use subquery with MAX and NOT IN / ORDER BY LIMIT for second highest
   • Use scalar subquery comparing to salary of F and E for vi and vii
7. 7 Marks
   aDiscuss the major steps in query optimization. What do you understand by sigma cascade operation?
   How to answer

   • List parsing, translation, optimization, evaluation steps
   • Explain cost-based plan selection and statistics
   • Define sigma cascade as splitting combined selection
   • Give example sigma_c1^c2 = sigma_c1(sigma_c2)
8. 7 Marks
   bDiscuss various cost components for query execution.
   How to answer

   • Define query execution cost as resource estimate
   • Explain I/O, CPU and communication costs
   • Discuss buffer, storage and index access components
   • Mention selectivity and statistics influence
9. 7 Marks
   aIdentify the candidate keys and highest normal form of the relation $R(ABCDEF)$. The functional dependencies of the relation R. $$A \to BC$$ $$C \to E$$ $$E \to F$$ $$F \to AB$$
   How to answer

   • Compute closures to find candidate keys D and F
   • Check FDs for partial and transitive dependencies
   • Determine highest normal form as 1NF/2NF
   • Justify with key and non-prime attributes
10. 7 Marks
    bConsider a relation $R(WXYZ)$ with the following set of functional dependencies. $$X \to W$$ $$WZ \to XY$$ $$Y \to WXZ$$ Compute the minimal (canonical) cover of the above relation R.
    How to answer

    • Split RHS into single attributes
    • Remove extraneous LHS attributes from WZ->XY
    • Eliminate redundant FDs by closure test
    • Present final canonical cover
11. 7 Marks
    aConsider the following actions taken by transaction T1 on database objects X and Y $$R(X), W(X), R(Y), W(Y)$$ Give an example of another transaction T2 that, if run concurrently to transaction T without some form of concurrency control, could interfere with T1.
    How to answer

    • State T1 operations R(X),W(X),R(Y),W(Y)
    • Construct T2 with conflicting read-write on same items
    • Show lost update / dirty read / unrepeatable read scenario
    • Explain need for concurrency control
12. 7 Marks
    bExplain the concept of recoverability in database management system. Show any recoverable schedule consisting of two transactions.
    How to answer

    • Define recoverable schedule: commit after reading committed values
    • Give two-transaction example with W and R and commits
    • Show commit order satisfies recoverability
    • Contrast with non-recoverable / cascading case
13. 7 Marks
    aConsider the following transactions with two data items A and B. Show the precedence graph for the given concurrent schedule and find equivalent serial schedule.

    

    How to answer

    • List conflicting operations on A and B between T1 and T2
    • Draw precedence graph nodes T1,T2 with edges
    • Check for cycle to test conflict serializability
    • Derive equivalent serial order
14. 7 Marks
    bConsider a schedule S using two phase locking mechanism for concurrency control. Convert the given schedule to strict and rigorous two-phase locking protocol. $$Lock\ Shared(A)$$ $$Read(A)$$ $$Lock\ Exclusive(B)$$ $$Read(B)$$ $$B = B + A$$ $$Write\ (A)$$ $$Write\ (B)$$ $$Unlock\ (A)$$ $$Unlock\ (B)$$ $$Commit$$
    How to answer

    • Recall basic 2PL lock/unlock sequence given
    • Hold all exclusive locks till commit for strict 2PL
    • Hold all shared and exclusive locks till commit for rigorous 2PL
    • Rewrite schedule with Unlocks after Commit
15. 14 Marks
    Write short notes on (any two):

    1. a)Oracle exception handling mechanism
    2. b)Functions of DBA
    3. c)SQL Aggregate Functions
    4. d)Distributed databases
    How to answer

    • Define chosen topics with key terms and purpose
    • Explain working with example / architecture
    • Mention advantages, uses in DBMS
    • Conclude with summary for each of the two notes