CY-405 Database Management System - Dec 2024 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 the architecture of DBMS and describe the main components.
   How to answer

   • Definition of 3-level ANSI-SPARC architecture: external, conceptual, internal levels
   • Main components: DDL, DML, query processor, storage manager, transaction manager
   • Roles of DBA, data dictionary/catalogue and overall system diagram
2. 7 Marks
   bDifferentiate between DBMS and traditional file systems. Explain how DBMS offers data independence with examples.
   How to answer

   • Table of differences: data redundancy, consistency, security, sharing, integrity, access
   • Meaning of physical and logical data independence
   • Examples e.g. change in storage structure or adding field without changing application
3. 7 Marks
   aDraw and explain an ER diagram for a Hospital Management System with entities like Patients, Doctors and Appointments, including relationships and constraints.
   How to answer

   • Entities Patients, Doctors, Appointments with key attributes
   • Relationships: Doctor-Patient via Appointment, cardinality and participation constraints
   • Neat ER diagram with keys, weak entity if any, and explanation of constraints
4. 7 Marks
   bDescribe different types of relational keys. What is the importance of primary and candidate keys in relational databases?
   How to answer

   • Types: primary, candidate, super, alternate, foreign, composite, unique keys
   • Definitions with example for each
   • Importance of primary key for identity/integrity and candidate keys as alternatives
5. 7 Marks
   aWrite SQL queries to perform the following tasks on a student database:

   1. i)Retrieve all students who scored above 85 in Mathematics.
   2. ii)Find the names of students who are enrolled in both Math and Science courses.
   How to answer

   • Assumed Student table schema with Marks/Course attributes
   • Query i) SELECT with WHERE Mathematics > 85
   • Query ii) INTERSECT / IN / JOIN to get enrolled in both Math and Science
6. 7 Marks
   bDefine triggers in SQL. Write a trigger that updates the total order amount in an "Orders" table whenever a new order is added in an "OrderItems" table.
   How to answer

   • Definition of trigger, row vs statement, BEFORE/AFTER, events
   • Trigger logic: AFTER INSERT on OrderItems to UPDATE Orders total
   • SQL CREATE TRIGGER code with NEW quantity/price
7. 7 Marks
   aDefine functional dependencies with examples. Explain the importance of functional dependencies in achieving database normalization.
   How to answer

   • Definition $X \rightarrow Y$ with examples e.g. SSN -> EmpName
   • Types: trivial, non-trivial, Armstrong axioms
   • Role in identifying keys and driving 1NF to BCNF normalization
8. 7 Marks
   bNormalize the following relation to 2NF and 3NF: Employee(SSN, EmpName, DeptID, DeptName, DeptLocation, ProjectID, ProjectName)
   How to answer

   • Identify FDs: SSN->EmpName, DeptID->DeptName,DeptLocation, ProjectID->ProjectName
   • Decompose to 2NF removing partial dependencies
   • Further decompose to 3NF removing transitive dependencies
9. 7 Marks
   aDefine deadlock in the context of databases. Describe one technique each for deadlock prevention, detection and resolution. https://www.rgpvonline.com
   How to answer

   • Definition of deadlock with wait-for cycle example
   • Prevention e.g. wait-die/wound-wait ordering; Detection e.g. wait-for graph
   • Resolution e.g. victim selection and rollback
10. 7 Marks
    bDiscuss two-phase locking protocol. Explain its role in maintaining serializability in con-currrent transactions.
    How to answer

    • Rules of growing and shrinking phases, lock types
    • How 2PL guarantees conflict-serializability
    • Strict 2PL variant and limitation e.g. deadlock
11. 7 Marks
    aDifferentiate between serial and non-serial schedules. Test if the following schedule is serializable or not: T1 : R(X), W(X), R(Y), W(Y) T2 : R(X), R(Y), W(Y) T3 : R(Y), W(X)
    How to answer

    • Differences: order equivalence to serial order vs interleaved execution
    • Precedence graph construction for T1, T2, T3 operations
    • Conclusion on cycle / serializability of given schedule
12. 7 Marks
    bExplain the purpose of indexing in database. How does a primary index differ from a clustering index?
    How to answer

    • Purpose of indexing: faster search, ordering, without full scan
    • Primary index on ordered key field vs clustering on non-key ordered field
    • Example tables showing index structure differences
13. 7 Marks
    aDescribe the different phases of query processing in DBMS. How does query optimization contribute to the efficiency of database systems?
    How to answer

    • Phases: parsing, translation, optimization, evaluation
    • Cost-based vs heuristic optimization
    • Contribution: reduced I/O, choice of best plan, scalability
14. 7 Marks
    bGiven a database of student records, explain how a hash function can be used for quick data retrieval.
    How to answer

    • Hash function mapping key e.g. RollNo to bucket address
    • Example $h(key)$ calculation and collision handling
    • O(1) average retrieval vs sequential search
15. 14 Marks
    Write short notes on (any two):

    1. i)Relational integrity constraints and their importance in databases
    2. ii)Entity-Relationship (ER) model
    3. iii)Lossless Join and Dependency Preserving Decomposition
    4. iv)Serializability and its types
    How to answer

    • Any two topics with definition and key points
    • Relevant example/diagram e.g. ER diagram, serializability graph
    • Importance/applications in DBMS