CY-405 Database 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
   aIllustrate the entity-relationship model with a simple example.
   How to answer

   • Define entity, attributes, relationship and ER symbols
   • Explain components: strong/weak entities, keys, cardinality
   • Give simple example e.g. Student-Enrolls-Course with attributes
   • Show illustrative ER diagram
2. 7 Marks
   bAnalyze the benefits of using DBMS over traditional file storage systems.
   How to answer

   • Define DBMS vs file system approach
   • Compare on redundancy, integrity, security, concurrency, sharing
   • Explain data independence and recovery
   • Conclude benefits with examples
3. 7 Marks
   aPropose a set of rules to improve data integrity and consistency across a multi-user DBMS.
   How to answer

   • Define integrity and consistency in multi-user environment
   • Propose rules: constraints, transactions, concurrency control
   • Include authorization, locking and recovery rules
   • Give example of enforcement
4. 7 Marks
   bExplain the role of keys in ensuring the integrity and uniqueness of data in a relational database.
   How to answer

   • Define primary, candidate, foreign, super keys
   • Explain uniqueness and entity integrity
   • Explain referential integrity via foreign keys
   • Give example table with keys
5. 7 Marks
   aUtilize the UNION and INTERSECTION operations in SQL to solve a query combining data from two tables.
   How to answer

   • Define UNION and INTERSECTION syntax and conditions
   • Give two sample tables with compatible schemas
   • Write example queries using UNION and INTERSECTION
   • Explain output and use case
6. 7 Marks
   bDetermine the impact of views on database performance and justify their usage in specific scenarios.
   How to answer

   • Define views: virtual tables, materialized vs simple
   • Discuss performance: overhead, optimization, indexing limits
   • Justify usage: security, simplification, reusability
   • Give specific scenarios with recommendation
7. 7 Marks
   aSummarize the concept of lossless join decomposition and its importance in database normalization.
   How to answer

   • Define decomposition and lossless join property
   • Explain test via functional dependencies / chase
   • Explain importance: no spurious tuples, normalization
   • Give example of lossy vs lossless
8. 7 Marks
   bExamine a relational schema for functional dependencies and identify any violations of 3NF or BCNF.
   How to answer

   • Define functional dependencies, 3NF and BCNF conditions
   • Steps to find keys and check FD violations
   • Identify transitive / non-superkey dependencies
   • Suggest decomposition if violated
9. 7 Marks
   aDesign a normalized schema for a university database ensuring BCNF and lossless join decomposition.
   How to answer

   • Identify university entities: Student, Course, Faculty, Department
   • Define FDs, keys and normalize to BCNF
   • Ensure lossless join and dependency preservation
   • Present final tables with keys
10. 7 Marks
    bConstruct a concurrency control mechanism using timestamp-based techniques for a multi-user database system.
    How to answer

    • Define timestamp ordering: W-timestamp, R-timestamp
    • Give Thomas write rule and abort/rollback steps
    • Explain handling of concurrent read/write
    • Discuss advantages for multi-user system
11. 7 Marks
    aEvaluate the effectiveness of the two-phase locking protocol in ensuring concurrency control and preventing deadlocks.
    How to answer

    • Define 2PL: growing and shrinking phases, strict/rigorous
    • Explain how it ensures conflict serializability
    • Evaluate deadlock issue: detection, prevention, starvation
    • Give example schedule
12. 7 Marks
    bPropose a methodology for testing serializability in large transaction schedules effectively.
    How to answer

    • Define conflict and view serializability
    • Propose precedence graph method steps
    • Explain handling large schedules efficiently
    • Mention tools/algorithms and complexity
13. 7 Marks
    aPropose a case study of a contemporary DBMS, analyzing its storage structures, indexing methods, and query optimization techniques.
    How to answer

    • Select contemporary DBMS e.g. MySQL/PostgreSQL/Oracle
    • Analyze storage structures: heap, pages, segments
    • Explain indexing: B+ tree, hash and query optimizer
    • Summarize findings
14. 7 Marks
    bDistinguish between primary and secondary indices, and evaluate their impact on query performance.
    How to answer

    • Define primary vs secondary index: ordering, uniqueness
    • Compare on structure: dense, sparse, multilevel
    • Evaluate impact on search, insert, update cost
    • Give example use cases
15. 14 Marks
    Write a short note on any two:

    1. i)Secondary Storage Devices
    2. ii)Shadow paging
    3. iii)Schemas and Instances
    4. iv)Views and Stored procedures
    How to answer

    • Select any two topics from the four given
    • Define each with key characteristics and purpose
    • Give example/diagram of working
    • Mention advantages and applications