IT-405 Data Base 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
   aDifferentiate between File System and DBMS. Discuss the advantages of a database system over a file-based system.
   How to answer

   • Definition of File System vs DBMS
   • Parameters of difference: data redundancy, consistency, security, sharing, integrity
   • Advantages of DBMS: data independence, concurrent access, recovery, integrity constraints
2. 7 Marks
   bDraw and explain an E-R diagram for a University database system. Consider entities like students, courses and faculty.
   How to answer

   • Identify entities: Student, Course, Faculty and attributes
   • Identify relationships: Enrolls, Teaches, Offers with cardinalities
   • Neat E-R diagram with symbols
   • Explanation of entities, relationships and constraints
3. 7 Marks
   aExplain the concept of weak entity sets. How do generalization, specialization and aggregation extend the E-R model.
   How to answer

   • Definition of weak entity set with example and identifying relationship
   • Concept of discriminator/partial key and double rectangle notation
   • Generalization, specialization and aggregation definitions
   • How they extend E-R with ISA hierarchy and aggregation abstraction
4. 7 Marks
   bConsider the following relational schema : Student(Roll_no, Name, Dept, Year, Marks) write relational algebra queries to :

   1. i)Retrieve names of students in the CS department
   2. ii)Find students who scored more than 80 marks
   How to answer

   • Recall relational schema Student(Roll_no, Name, Dept, Year, Marks)
   • Use selection $\sigma_{Dept='CS'}$ and projection $\pi_{Name}$ for (i)
   • Use selection $\sigma_{Marks>80}$ for (ii)
   • Write correct relational algebra expressions
5. 7 Marks
   aCompare sorting, join and selection operations in query processing.
   How to answer

   • Role of selection, join, sorting in query processing
   • Algorithms/cost factors for each operation
   • Comparison on basis of I/O cost, memory, optimization impact
6. 7 Marks
   bWhy is query optimization necessary in large databases? Discuss with an example. Explain the measures of query cost.
   How to answer

   • Need for optimization in large databases: cost, time, resources
   • Example showing equivalent plans with different costs
   • Measures of query cost: I/O, CPU, memory, communication
7. 7 Marks
   aDiscuss the types of anomalies that arise in an un-normalized database. Explain how normalization helps to eliminate these anomalies.
   How to answer

   • Define insertion, deletion, update anomalies
   • Examples of anomalies in unnormalized relation
   • How 1NF/2NF/3NF/BCNF remove redundancy
   • Benefits of normalization for consistency
8. 7 Marks
   bConsider the following relation. Employee (Emp_ID, Name, Dept, Salary, Project_ID, Project_Name, Manager_ID)

   1. i)Identify functional dependencies
   2. ii)Convert it to 3NF and BCNF.
   How to answer

   • List attributes and infer FDs: Emp_ID -> Name, Dept, Salary etc.
   • Identify keys and partial/transitive dependencies
   • Decompose to 3NF preserving dependencies
   • Further decompose to BCNF and verify lossless join
9. 7 Marks
   aDefine a transaction in a database. Explain the ACID properties of transactions with examples.
   How to answer

   • Definition of transaction as unit of work
   • ACID: Atomicity, Consistency, Isolation, Durability
   • Example e.g. fund transfer for each property
   • Significance for reliability
10. 7 Marks
    bWhy is concurrency control important in database systems? Discuss the impact of concurrency control on transaction performance.
    How to answer

    • Need for concurrency: multi-user access, throughput
    • Problems without control: lost update, dirty read
    • Impact on performance: overhead vs increased concurrency
11. 7 Marks
    aWhat is data independence? Explain logical and physical data independence with an example.
    How to answer

    • Definition of data independence
    • Logical independence: schema change without app change
    • Physical independence: storage change without logical change
    • Example of each
12. 7 Marks
    bHow does relational algebra help in database query processing? Explain with an example.
    How to answer

    • Relational algebra as procedural query language
    • Operators: select, project, join etc. for query formulation
    • Example query translation
    • Role in optimization and processing
13. 7 Marks
    aExplain the importance of decomposition in database design. What is lossless and dependency-preserving decomposition?
    How to answer

    • Need for decomposition to remove redundancy
    • Lossless-join condition and dependency preservation
    • Examples of lossy vs lossless decomposition
    • Impact on database design quality
14. 7 Marks
    bWhat is deadlock in a database? Explain deadlock prevention and deadlock detection mechanisms.
    How to answer

    • Definition of deadlock with wait-for graph example
    • Prevention schemes: wait-die, wound-wait, timeouts
    • Detection: wait-for graph, recovery by victim selection
15. 14 Marks
    Write short notes on (any two)

    1. a)Two-Phase Locking (2PL) protocol
    2. b)Distributed databases : Concepts and Challenges
    3. c)Serializability in transactions
    4. d)Multi-valued dependency
    How to answer

    • Select any two topics from (a)-(d)
    • Definition, working and key points for each chosen topic
    • Examples/diagrams where applicable
    • Advantages, challenges or applications