CY-405 Database Management System - Jun 2022 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
   aMention the drawbacks of traditional file-based systems that make a database management system a better option.
   How to answer

   • Define traditional file-based system
   • List drawbacks: data redundancy, inconsistency, isolation, integrity, security, concurrency issues
   • Explain how DBMS overcomes each
   • Conclude why DBMS is better option
2. 7 Marks
   bDefine data, database, and database management system. Discuss the advantages of DBMS?
   How to answer

   • Define data, database, DBMS
   • Explain DBMS components briefly
   • List and explain advantages: redundancy control, sharing, integrity, security, concurrency
   • Give examples where applicable
3. 7 Marks
   aWhat is meant by an entity-relationship (E-R) model? Explain the terms Entity, Entity Type and Entity Set in DBMS.
   How to answer

   • Define E-R model and its purpose
   • Define Entity with example
   • Define Entity Type with example
   • Define Entity Set with example
   • Explain relationships, attributes briefly
4. 7 Marks
   bDraw E-R Diagram of Library Management System.
   How to answer

   • Identify entities: Book, Member, Librarian, Issue
   • Identify attributes and keys
   • Identify relationships: Issues, Manages
   • Draw E-R diagram with standard symbols
   • Explain diagram briefly
5. 7 Marks
   aWhat is the difference between a relation and a relation schema? Discuss their relationship.
   How to answer

   • Define relation (instance/table with tuples)
   • Define relation schema (structure: name + attributes + domains)
   • Compare on definition, example, change over time
   • Explain relationship: schema defines structure, relation is instance
6. 7 Marks
   bWhat is a Foreign key? List the properties of a foreign key. Explain with the help of an example.
   How to answer

   • Define foreign key and its purpose
   • List properties: references primary key, allows duplicates/null, maintains referential integrity
   • Give example of two related tables
   • Explain with referential actions
7. 7 Marks
   aWrite a query and explain with examples that JOIN two tables so that all rows from the first table are in the result.
   How to answer

   • Define LEFT JOIN concept
   • Give syntax for LEFT JOIN / LEFT OUTER JOIN
   • Create two sample tables with data
   • Show query and result containing all left table rows
   • Explain NULL for non-matching right side
8. 7 Marks
   bWhat is the difference between a LEFT JOIN and a FULL JOIN? Explain with the help of an example.
   How to answer

   • Define LEFT JOIN with syntax and result
   • Define FULL JOIN with syntax and result
   • Compare on rows retained and NULL handling
   • Illustrate with same example tables and outputs
9. 4 Marks
   aExplain aggregate function with syntax and give an example.
   How to answer

   • Define aggregate functions in SQL
   • Give general syntax
   • List types: COUNT, SUM, AVG, MIN, MAX
   • Give example query and result
10. 10 Marks
    bWrite the SQL query for the followings:

    1. i)Fetch the EmpId and FullName of all the employees working under Manager with id - '121' from Employee table.
    2. ii)Fetch the different projects available from the Employee Salary table.
    3. iii)Fetch the count of employees working in project 'P1' from Employee Salary table.
    4. iv)Find the maximum, minimum, and average salary of the employees from Employee Salary table.
    5. v)Find the employee id whose salary lies in the range of 9000 and 15000 from Employee Salary Table.
    How to answer

    • Use SELECT with WHERE for Manager id 121
    • Use DISTINCT for different projects
    • Use COUNT with WHERE for project P1
    • Use MAX, MIN, AVG for salary
    • Use BETWEEN 9000 AND 15000 for salary range
11. 7 Marks
    aWhat is difference between Lossless join decomposition and lossy less join decomposition? Explain with examples.
    How to answer

    • Define lossless and lossy join decomposition
    • Explain condition for losslessness
    • Give example of lossless decomposition
    • Give example of lossy decomposition with spurious tuples
    • Compare consequences
12. 7 Marks
    bGiven a relation R(A, B, C, D) and Functional Dependency set FD = $\{ AB \rightarrow CD, B \rightarrow C \}$, determine whether the given R is in 2NF? If not convert it into 2 NF.
    How to answer

    • Find candidate key from FD $AB \rightarrow CD, B \rightarrow C$
    • Check partial dependency $B \rightarrow C$ on key
    • Conclude R is not in 2NF (1NF only)
    • Decompose to remove partial dependency
    • Verify resulting relations in 2NF
13. 7 Marks
    aWhat is Log? How is it maintained? Discuss the salient features of deferred database modification and immediate database modification strategies in brief.
    How to answer

    • Define log and its purpose in recovery
    • Explain how log is maintained (records, LSN)
    • Define deferred modification strategy
    • Define immediate modification strategy
    • Compare features: UNDO/REDO needs
14. 7 Marks
    bWhat is Recoverable schedule? Why is recoverability of schedules desirable? Are there any circumstances under which it would be desirable to allow non-recoverable schedules? Explain your answer
    How to answer

    • Define recoverable schedule with example
    • Explain why recoverability is desirable (no dirty read issues)
    • Discuss cascading rollback problem
    • Analyze when non-recoverable may be allowed and risks
    • Give conclusion with justification
15. 7 Marks
    aWhy query Optimization is needed? What is the difference between query processing and query optimization?
    How to answer

    • Define query optimization need: cost, performance
    • Define query processing steps
    • Define query optimization role
    • Compare processing vs optimization
    • Give example of equivalent plans
16. 7 Marks
    bWhat are the phases of Query Processing? How are query processing and query optimization related?
    How to answer

    • List phases: parsing, translation, optimization, evaluation
    • Explain each phase briefly
    • Explain optimizer generating optimal plan
    • Relate processing uses optimization for efficient execution