CS-502 Database Management Systems - Dec 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
   aExplain the Database approach and compare it with the traditional file processing system. Discuss advantages of database systems.
   How to answer

   • Define database approach vs file system
   • Points of comparison: data redundancy, sharing, independence, integrity
   • Advantages: reduced redundancy, data sharing, security, integrity, concurrent access
2. 7 Marks
   bExplain Data models, schemas, and instances. What is data independence?
   How to answer

   • Define data model, schema, instance
   • Types of schemas and data models
   • Define physical and logical data independence
3. 7 Marks
   aDescribe the ER data model. Explain entities, attributes, and relationships with examples.
   How to answer

   • Define ER model components
   • Explain entity, weak entity, attributes types
   • Explain relationship, cardinality, participation with examples
4. 7 Marks
   bDraw an ER diagram for a library system and transform it into relational tables.
   How to answer

   • Identify entities: Book, Member, Librarian, Issue
   • Draw ER diagram with attributes and relationships
   • Map entities and relationships to relational tables with keys
5. 7 Marks
   aExplain relational data model concepts: relations, attributes, tuples, domains, and keys.
   How to answer

   • Define relation, attribute, tuple, domain
   • Explain key types: primary, candidate, foreign, super
   • Give example relation to illustrate concepts
6. 7 Marks
   bWrite SQL queries to :

   1. (i))create a table STUDENT(Roll No, Name, Marks),
   2. (ii))insert two records
   3. (iii))retrieve students with Marks > 60
   How to answer

   • CREATE TABLE STUDENT with appropriate data types
   • INSERT INTO with two records
   • SELECT with WHERE Marks > 60
7. 7 Marks
   aExplain normalization and functional dependency. Discuss first, second, and third normal forms.
   How to answer

   • Define normalization and functional dependency
   • Explain 1NF, 2NF, 3NF rules with examples
   • Discuss anomalies removed by normalization
8. 7 Marks
   bNormalize the relation $R(A, B, C, D)$ with dependencies: $A \rightarrow B, B \rightarrow C, C \rightarrow D$.
   How to answer

   • Find closure and key A as candidate key
   • Check partial and transitive dependencies B->C, C->D
   • Decompose into 3NF tables R1(A,B), R2(B,C), R3(C,D)
9. 7 Marks
   aExplain query optimization and its importance. Describe heuristic and cost-based optimization.
   How to answer

   • Define query optimization and need for efficiency
   • Explain heuristic/rule-based optimization
   • Explain cost-based optimization with example
10. 7 Marks
    bFor relations EMP(Eid, Dept) and DEPT(Dept, Dname), write relational algebra expressions to find employee names working in 'Sales'.
    How to answer

    • Select DEPT tuples where Dname='Sales'
    • Join result with EMP on Dept attribute
    • Project employee names: $\Pi$ with $\sigma$ and $\bowtie$
11. 7 Marks
    aExplain transaction processing and serializability. Differentiate between conflict and view serializability.
    How to answer

    • Define transaction and ACID-related processing
    • Define conflict and view serializability
    • Differences between conflict and view serializability with examples
12. 7 Marks
    bDetermine whether the schedule is conflict serializable: $R1(X), W1(X), R2(X), W2(X), R1(Y), W1(Y)$.
    How to answer

    • List conflicting operations on X and Y
    • Draw precedence graph for T1, T2
    • Check for cycle to decide conflict serializable or not
13. 7 Marks
    aExplain Concurrency control techniques: locking and time-stamping protocols.
    How to answer

    • Explain locking: shared, exclusive, 2PL
    • Explain timestamp ordering protocol
    • Compare advantages and drawbacks
14. 7 Marks
    bExplain Log-based recovery and checkpoints with an example.
    How to answer

    • Define log records and recovery steps
    • Explain checkpoints and restart procedure
    • Give example of undo/redo using log
15. 7 Marks
    aExplain Oracle/MySQL architecture and main memory structures.
    How to answer

    • Describe Oracle/MySQL architecture layers
    • Explain memory structures: SGA, PGA, buffer cache
    • Mention background processes and storage
16. 7 Marks
    bWrite SQL queries to demonstrate:

    1. (i))equi-join,
    2. (ii))outer join, and
    3. (iii))a nested query using EXISTS.
    How to answer

    • Write equi-join query using equality condition
    • Write outer join (LEFT/RIGHT/FULL) example
    • Write nested query with EXISTS clause